☰

MySQL 配置与优化

tag-icon • tag-icon mysql configuration optimization
MySQL 配置与优化

以下是我的服务器 Giga Dedicated 6-Core 的信息：

  • 英特尔酷睿 i7 (6 x 3,20GHz)
  • 24 GB 内存
  • 160 GB SSD + 2000 GB HDD 我安装了 CentOS 5.6（64 位）和 Plesk Panel 10
  • Apache 版本是 2.2.19
  • MySQL 版本是 5.5
  • PHP 5.3.6

我对优化 mysql 服务器很感兴趣。我将有数十个数据库，其中许多数据库的大小为几 GB。

因此我想询问有关my.cnf变量大小的意见。

这是我的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)

我的服务器的最佳配置是什么？您的意见、建议或经验是什么？

更新：

我修改了一些 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

关于key_buffer_size和innodb_buffer_pool_size的查询的答案是：

+---------------------------------------------------+
| recommended_innodb_buffer_pool_size |
+---------------------------------------------------+
| 1129M |
+---------------------------------------------------+
and
+----------------------------------------+
| recommended_key_buffer_size |
+----------------------------------------+
| 4M |
+----------------------------------------+

推荐的 key_buffer_size 是否太小？您认为什么样的设置才足够好？令我担心的是 max_connections=250？这够了吗？

我托管的网站一天的访问量约为 18,000 次，页面浏览量约为 70,000 次。

以下是 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)

非常感谢您的帮助。我知道这对您来说太长了，但我只是想学习一些东西。

答案1

您需要设置 MyISAM 密钥缓存和 InnoDB 缓冲池来容纳您拥有的数据。我在 DBA StackExchange 中发布了两个查询，以计算这些缓存的推荐大小。

对于 MyISAM，由于仅缓存索引页，此查询将根据您的 .MYI 文件总数建议 key_buffer_size 的大小，如果超过 4G，则将其限制为 4G：

SELECT CONCAT(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，由于数据和索引页都被缓存，此查询将根据数据和索引页的总和推荐 innodb_buffer_pool_size 的大小：

选择 CONCAT(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 engine='InnoDB') A,
(SELECT 2 PowerOfTwo) B;

根据 mysqltuner.pl 的显示，您有大约 24GB 的 RAM、530MB 的 InnoDB id、7.2GB 的 MyISAM 索引。无论建议是什么，请根据常识将 innodb_buffer_pool_size 设置为大约 530MB 但低于 1GB 的数字。由于 MyISAM 在某些情况下并没有真正利用内存，您可以将 key_buffer_size 保留为 1024M，因为 mysqltuner.pl 表示 90% 的 RAM 由 DB 连接使用。操作系统也需要 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% 以下，以便操作系统有足够的内存空间。

警告：请原谅名为“PowerOfTwo”的列。它实际上应该称为 PowerOf1024。PowerOfTwo 的目的是以 GB、MB、KB 显示推荐答案

  • （SELECT 0 PowerOfTwo）以字节为单位显示值
  • （SELECT 1 PowerOfTwo）以千字节为单位显示值
  • （选择 2 PowerOfTwo）以兆字节为单位显示值
  • （选择 3 PowerOfTwo）以 GB 为单位显示值

相关内容