这实际上是一个关于 MySQL 环境的最佳设置的一般性问题,但是我们当前的 MySQL 服务器安装在具有 16GB RAM 的机器上,我们最近升级到了较新的服务器(功能更强大,较新的 Dell Poweredge 服务器),但具有 128GB 可用 RAM。
可以说,我们已经有了一个新的开始,但我们想知道如何正确地优化一切,以便它能在较新的硬件上发挥最佳性能。关于以下方面的任何建议:
- 文件系统(和块大小/fs 设置)
- my.cnf(查询大小、缓冲区大小和相关设置)
我目前所得到的只是针对最后的服务器进行调整的当前 my.cnf 文件,但我在网上找不到太多指导来说明任何设置应该高于现在的值。
答案1
以下是如何设置 key_buffer_size 和 innodb_buffer_pool_size
设置 MyISAM 的键缓冲区大小
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) Recommended_key_buffer_size FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,(SELECT 3 pw) B;
顺便说一句,正常工作的最大 key_buffer_size 是 4G
因此,将 key_buffer_size 设置为 4G 或上一个查询中推荐的设置(以较小者为准)。
设置 innodb_buffer_pool_size
选择 CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) Recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A,(SELECT 3 pw) B;
您可能还想调整 ib_logfile0 和 ib_logfile1 的大小。方法如下:
步骤 1) service mysql stop
步骤 2)将此行添加到 /etc/my.cnf
innodb_log_file_size=2047M(或 Recommended_innodb_buffer_pool_size 的 25%,以较小者为准)
步骤 3) rm -f /var/log/mysql/ib_logfile[01]
步骤 4) service mysql start
要了解这些设置的意义,请运行此查询
SELECT IFNULL(B.engine,'Total') "存储引擎", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "数据大小", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "索引大小", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "表大小" FROM (SELECT引擎,SUM(数据长度)DSize,SUM(索引长度)ISize,SUM(数据长度+索引长度)TSize FROM information_schema.tables WHERE table_schema NOT IN('mysql','information_schema','performance_schema')AND engine IS NOT NULL GROUP BY engine WITH ROLLUP)B,(SELECT 3 pw)A ORDER BY TSize;
如果你使用 InnoDB,则应启用 innodb_file_per_table并清理你的 InnoDB 存储
答案2
您可能需要运行mysqltuner.pl
以获取有关数据库配置的一些建议。