MySQL my.cnf性能调优建议 [英] MySQL my.cnf performance tuning recommendations
问题描述
我希望有人能够为优化大量mysql数据库服务器的my.cnf文件提供一些帮助.
I am kind of hoping that someone might be able to offer some assistance with optimizing a my.cnf file for an extremely high volume mysql database server.
Our architecture is as follows:
Memory : 96GB
CPUs : 12
OS & Mysql : 64-bit
Disk Space : 1.2 TB
DB Engine : MyISAM
大约300个客户端同时使用我们的Web应用程序.我们需要调整my.cnf以便为该基础结构提供最佳性能.
Our web application is used by roughly 300 client simultaneously. We need our my.cnf tuned to give the best possible performance for this infrastructure.
我完全知道索引和优化查询是其中的主要因素，但是我们想从一个配置正确的系统开始，然后再相应地系统地重新构造查询.
I am fully aware that indexes and optimized queries are a major factor in this, but we would like to start with a system that is configured properly and then follow that up with systematically re-engineering our queries accordingly.
Here is our current my.cnf file content:
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
log-bin=mysql-bin
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1
log-slow-queries = /var/log/mysqld_slow_queries.log
long_query_time = 10
max_connections = 500
key_buffer_size = 32768M
#max_allowed_packet = 2M
#table_open_cache = 128
#sort_buffer_size = 1024K
#net_buffer_length = 64K
#read_buffer_size = 1024K
#read_rnd_buffer_size = 1024K
#myisam_sort_buffer_size = 8M
query_cache_size = 128M
query_cache_limit = 128M
interactive_timeout = 300
wait_timeout = 300
# Added values after load testing
thread_cache_size = 8
#tmp_table_size = 256M
#max_heap_table_size = 256M
#table_cache = 512
#join_buffer_size = 512
log-error=/var/log/mysqld.log
innodb_buffer_pool_size=128M
#innodb_file_per_table
#innodb_log_file_size=250M
##innodb_buffer_pool_size=64M
#innodb_buffer_pool_size=1024M
#innodb_log_buffer_size=4M
##log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[myisamchk]
#key_buffer = 64M
#sort_buffer = 64M
#read_buffer = 16M
#write_buffer = 16M
有什么建议吗? 谢谢大家.
Any suggestions? Thanks folks.
由RolandoMySQLDBA编辑
Edit by RolandoMySQLDBA
由于您所有的数据都是MyISAM，请运行此查询并显示输出
Since all you data is MyISAM, please run this query and show the output
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+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 3 PowerOf1024) B;
@ Rolando-谢谢...该查询的结果是4G.
@ Rolando - Thanks...the results of that query was 4G.
推荐答案
尝试从Percona向导开始，并将它们的建议与您当前的设置一一比较.不用担心，没有像您想的那样适用的设置.
Try starting with the Percona wizard and comparing their recommendations against your current settings one by one. Don't worry there aren't as many applicable settings as you might think.
https://tools.percona.com/wizard
大约2020年更新:很抱歉，此工具已终止使用: https://www.percona.com/blog/2019/04/22/end-of-life-query-analyzer -and-mysql-configuration-generator/
Update circa 2020: Sorry, this tool reached it's end of life: https://www.percona.com/blog/2019/04/22/end-of-life-query-analyzer-and-mysql-configuration-generator/
每个人都首先指向您已解决的key_buffer_size.有了96GB的内存，我会警惕任何微小的默认值(可能只有96M！).
Everyone points to key_buffer_size first which you have addressed. With 96GB memory I'd be wary of any tiny default value (likely to be only 96M!).
这篇关于MySQL my.cnf性能调优建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
