以下是我的服务器Giga专用6核心的信息：
我有兴趣优化mysql服务器。 我会有几十个数据库，他们的copule是几个千兆字节的大小。
所以我要求my.cnfvariables的意见。
这是我的my.cnf：
key_buffer_size = 1024M table_cache = 1024 sort_buffer_size = 32M read_buffer_size = 32M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 128M thread_cache_size = 16 query_cache_size= 64M query_cache_limit = 1M thread_concurrency = 8 innodb_buffer_pool_size = 500M innodb_additional_mem_pool_size = 128M max_connections=250
这里是mysqltuner日志：
MySQLTuner 1.2.0 - Major Hayden <[email protected]> Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.14 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 7G (Tables: 104) [--] Data in InnoDB tables: 530M (Tables: 213) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 213 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 4s (9 q [2.250 qps], 6 conn, TX: 10K, RX: 695) [--] Reads / Writes: 100% / 0% [--] Total buffers: 1.7G global + 80.4M per thread (250 max threads) [!!] Maximum possible memory usage: 21.3G (90% of installed RAM) [OK] Slow queries: 0% (0/9) [OK] Highest usage of available connections: 0% (1/250) [!!] Key buffer size / total MyISAM indexes: 1.0G/7.2G [!!] Key buffer hit rate: 50.0% (6 cached / 3 reads) [!!] Query cache efficiency: 0.0% (0 cached / 4 selects) [OK] Query cache prunes per day: 0 [OK] Temporary tables created on disk: 0% (0 on disk / 2 total) [OK] Thread cache hit rate: 83% (1 created / 6 connections) [OK] Table cache hit rate: 78% (26 open / 33 opened) [OK] Open file limit used: 0% (18/2K) [OK] Table locks acquired immediately: 100% (36 immediate / 36 locks) [!!] Connections aborted: 16% [!!] InnoDB data size / buffer pool: 530.1M/500.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance 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 Your applications are not closing MySQL connections properly Variables to adjust: key_buffer_size (> 7.2G) query_cache_limit (> 1M, or use smaller result sets) innodb_buffer_pool_size (>= 530M)
什么是我的服务器的最佳configuration？ 你的意见，build议，经验是什么？
更新：
我已经纠正了一点my.cnf设置。 他们来了：
key_buffer_size = 1024M table_cache = 1024 sort_buffer_size = 10M join_buffer_size = 10M read_buffer_size = 10M read_rnd_buffer_size = 10M myisam_sort_buffer_size = 128M thread_cache_size = 16 query_cache_size= 64M query_cache_limit = 1M -#Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 128M max_connections=250
build议key_buffer_size和innodb_buffer_pool_size的查询答案是：
+---------------------------------------------------+ | recommended_innodb_buffer_pool_size | +---------------------------------------------------+ | 1129M | +---------------------------------------------------+ and +----------------------------------------+ | recommended_key_buffer_size | +----------------------------------------+ | 4M | +----------------------------------------+
是recommended_key_buffer_size太less？ 你觉得这个设置够好吗？ 我担心的是max_connections = 250？ 这够了吗？
我托pipe的网站在一天内约有18000次访问和70000次综合浏览量。
这里是mysqltuner在这些设置上的说明：
MySQLTuner 1.2.0 - Major Hayden Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.14 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1M (Tables: 114) [--] Data in InnoDB tables: 530M (Tables: 219) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 221 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 18h 32m 2s (11K q [0.179 qps], 461 conn, TX: 7M, RX: 1M) [--] Reads / Writes: 80% / 20% [--] Total buffers: 2.2G global + 40.2M per thread (250 max threads) [OK] Maximum possible memory usage: 12.0G (51% of installed RAM) [OK] Slow queries: 0% (0/11K) [OK] Highest usage of available connections: 8% (20/250) [OK] Key buffer size / total MyISAM indexes: 1.0G/3.5M [OK] Key buffer hit rate: 99.9% (688K cached / 510 reads) [OK] Query cache efficiency: 57.3% (4K cached / 7K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234 sorts) [!!] Temporary tables created on disk: 34% (661 on disk / 1K total) [OK] Thread cache hit rate: 95% (20 created / 461 connections) [OK] Table cache hit rate: 98% (611 open / 622 opened) [OK] Open file limit used: 14% (329/2K) [OK] Table locks acquired immediately: 100% (6K immediate / 6K locks) [OK] InnoDB data size / buffer pool: 530.2M/1.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate 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: tmp_table_size (> 16M) max_heap_table_size (> 16M)
我将衷心感谢您的帮助。 我知道这是很多文字供你阅读，但我只是想学习一些东西。
您需要设置MyISAM密钥caching和InnoDB缓冲池以适应您拥有的数据。 我在DBA StackExchange中发布了两个查询来计算这些caching的noth的推荐大小 。
对于MyISAM，由于只有索引页被caching，所以这个查询将基于你的.MYI文件的总和推荐key_buffer_size的大小，如果它超过4G，则将其限制在4G：
selectCONCAT（ROUND（KBS / POWER（1024，IF（PowerOfTwo <0,0，IF（PowerOfTwo> 3,0，PowerOfTwo）））+ 0.4999），SUBSTR（'KMG'，IF（PowerOfTwo <0,0，IF （PowerOfTwo> 3.0，PowerOfTwo））+ 1,1））recommended_key_buffer_size
FROM（SELECT LEAST（POWER（2,32），KBS1）KBS FROM
（SELECT SUM（index_length）KBS1 FROM information_schema.tables
WHERE engine ='MyISAM'AND table_schema NOT IN（'information_schema'，'mysql'））AA
）A，（SELECT 2 PowerOfTwo）B;
对于InnoDB，由于数据和索引页面被caching，所以这个查询将基于数据和索引页面的总和推荐innodb_buffer_pool_size的大小：
selectCONCAT（ROUND（KBS / POWER（1024，IF（PowerOfTwo <0,0，IF（PowerOfTwo> 3,0，PowerOfTwo）））+ 0.49999），SUBSTR（'KMG'，IF（PowerOfTwo <0,0，IF （PowerOfTwo> 3.0，PowerOfTwo））+ 1,1））recommended_innodb_buffer_pool_size
FROM（SELECT SUM（data_length + index_length）KBS FROM information_schema.tablesM
WHERE引擎='InnoDB'）A，
（SELECT 2 PowerOfTwo）B;
根据你的显示mysqltuner.pl，你有大约24GB的内存，530MB的InnoDB，7.2GB的MyISAM索引。 不pipe出现什么build议，只需使用常识，将innodb_buffer_pool_size设置为大约530MB但小于1GB的数字。 由于MyISAM在某些情况下没有真正利用内存，因此可以将key_buffer_size设置为1024M，因为mysqltuner.pl表示DB连接占用90％的内存。 操作系统也需要RAM。
我会降低这些：
[mysqld] join_buffer_size = 4M sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 4M
之后重新启动mysql并运行mysqltuner.pl。 你的目标是获得最大可能的内存使用率低于80％，这样操作系统就有了内存空间。
CAVEAT：请原谅名为“PowerOfTwo”的专栏。 它应该被称为PowerOf1024。 PowerOfTwo的目的是在GB，MB，KB中显示推荐的答案