我怎么能改善我的服务器内存使用情况：安装mysqltuner后，服务器内存= 1GB，我有这样的结果：
[root@server ~]# perl mysqltuner.pl >> MySQLTuner 1.5.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.44 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 18M (Tables: 185) [--] Data in InnoDB tables: 19M (Tables: 271) [--] Data in MEMORY tables: 0B (Tables: 5) [!!] Total fragmented tables: 22 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] User 'admin_jmu@%' hasn't specific host restriction. [!!] User 'admin_mcstd@%' hasn't specific host restriction. [!!] User 'admin_melody@%' hasn't specific host restriction. [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 9h 21m 32s (322K q [9.576 qps], 68K conn, TX: 663M, RX: 16M) [--] Reads / Writes: 87% / 13% [--] Binary logging is disabled [--] Total buffers: 208.0M global + 4.5M per thread (400 max threads) [OK] Maximum reached memory usage: 244.0M (24.49% of installed RAM) [!!] Maximum possible memory usage: 2.0G (201.52% of installed RAM) [OK] Slow queries: 0% (1/322K) [OK] Highest usage of available connections: 2% (8/400) [OK] Aborted connections: 0.00% (3/68352) [!!] Key buffer used: 19.1% (6M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/959.0K [OK] Read Key buffer hit rate: 99.8% (166K cached / 273 reads) [OK] Write Key buffer hit rate: 99.2% (237 cached / 2 writes) [OK] Query cache efficiency: 91.1% (96K cached / 105K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) [!!] Temporary tables created on disk: 60% (765 on disk / 1K total) [OK] Thread cache hit rate: 99% (8 created / 68K connections) [OK] Table cache hit rate: 98% (351 open / 358 opened) [OK] Open file limit used: 3% (320/8K) [OK] Table locks acquired immediately: 99% (10K immediate / 10K locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/19.2M [OK] InnoDB buffer pool instances: 1 [!!] InnoDB Used buffer: 20.19% (1654 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.96% (4013636 hits/ 4015282 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 356 writes) -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** tmp_table_size (> 16M) max_heap_table_size (> 16M) 和my.cnf文件有这个内容：
[mysqld] max_connections = 400 key_buffer = 32M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1024 thread_cache_size = 286 interactive_timeout = 25 wait_timeout = 1000 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 tmp_table_size = 16M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M local-infile=0
我怎样才能改变my.cnf？ 谢谢
你可以在这里看到你的主要问题：
[--] Total buffers: 208.0M global + 4.5M per thread (400 max threads)
根据您的configuration，每个线程最多可以占用4.5 MB内存，最多可以有400个线程：
max_connections = 400
将max_connections降至100或更低，以降低最大内存使用量。
你想达到什么目的？
在SQL方面，你应该给它尽可能多的内存，它会用它来caching索引，对于InnoDB来说，数据页也是这样，这意味着更好的性能，更less的磁盘读取重复查询。
我不是100％确定的，但是我认为MyISAM引擎只会将索引caching到内存中，在这种情况下，如果想要快速获取MySQL数据库，InnoDB通常是更好的select。