安装运行
wget http://www.day32.com/MySQL/tuning-primer.sh chmod +x tuning-primer.sh运行时你可以选择在用户目录下生成一个保存mysql密码的.my.cnf文件方便下次调用 ~/.my.cnf文件内容应该是这样的 cat ~/.my.cnf
[client] user=test password=123qwe socket=/opt/mysql/mysql.sock./tuning-primer.sh
Using login values from ~/.my.cnf – INITIAL LOGIN ATTEMPT FAILED – Testing for stored webmin passwords: None Found Could not auto detect login info! Found Sockets: /opt/mysql/mysql.sock Using: /opt/mysql/mysql.sock Would you like to provide a different socket?: [y/N] n Do you have your login handy ? [y/N] : y User: test Password: 123qwe Would you like me to create a ~/.my.cnf file for you? [y/N] : y ~/.my.cnf already exists! Replace ? [y/N] : y – FINAL LOGIN ATTEMPT FAILED – Unable to log into socket: /opt/mysql/mysql.sock因为我的mysql是编译安装在/opt/mysql/下,这里找不到执行文件,所以没有连接上. 编辑第219和220行,手动修改文件加上路径 vi tuning-primer.sh
215 if [ -f /etc/psa/.psa.shadow ] ; then 216 mysql=”mysql -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)” 217 mysqladmin=”mysqladmin -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)” 218 else 219 mysql=”/opt/mysql/bin/mysql” 220 mysqladmin=”/opt/mysql/bin/mysqladmin” 221 # mysql=”mysql -S $socket” 222 # mysqladmin=”mysqladmin -S $socket” 223 fi顺带修复生成密码时的错误问题. 第313行”$pass\”后面需加个”n”
313 printf “[client]\nuser=$user\npassword=$pass\nsocket=$socket” > ~/.my.cnf提示:vi中显示行号方法.在命令模式下输入”:set number”
保存后再次运行,在终端上按照问题重要程度分别用黄色/红色字符标记问题 ./tuning-primer.sh
— MYSQL PERFORMANCE TUNING PRIMER — – By: Matthew Montgomery – MySQL Version 5.1.26-rc x86_64 Uptime = 0 days 7 hrs 17 min 18 sec Avg. qps = 286 Total Questions = 7527430 Threads Connected = 6 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL’s Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 29 out of 7527516 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 80 Current threads_cached = 75 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 350 Current threads_connected = 7 Historic max_used_connections = 208 The number of used connections is 59% of the configured maximum. Your max_connections variable seems to be fine. No InnoDB Support Enabled! MEMORY USAGE Max Memory Ever Allocated : 68.78 G Configured Max Per-thread Buffers : 112.17 G Configured Max Global Buffers : 2.12 G Configured Max Memory Limit : 114.29 G Physical Memory : 15.66 G nMax memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 3.00 G Current key_buffer_size = 2.00 G Key cache miss rate is 1 : 3262 Key buffer free ratio = 62 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 128 M Current query_cache_used = 61 M Current query_cache_limit = 2 M Current Query cache Memory fill ratio = 47.81 % Current query_cache_min_res_unit = 2 K Query Cache is 21 % fragmented Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation. MySQL won’t cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 256 M Current read_rnd_buffer_size = 32 M Sort buffer seems to be fine JOINS Current join_buffer_size = 32.00 M You have had 15 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable “log-queries-not-using-indexes” Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 51200 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 3072 tables Current table_definition_cache = 256 tables You have a total of 658 tables You have 1814 open tables. The table_cache value seems to be fine You should probably increase your table_definition_cache value. TEMP TABLES Current max_heap_table_size = 384 M Current tmp_table_size = 1.00 G Of 142450 temp tables, 18% were created on disk Effective in-memory tmp_table_size is limited to max_heap_table_size. Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 5065 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 25 You may benefit from selective use of InnoDB. If you have long running SELECT’s against MyISAM tables and perform frequent updates consider setting ‘low_priority_updates=1’ If you have a high concurrency of inserts on Dynamic row-length tables consider setting ‘concurrent_insert=2’.我的my.cnf
[mysqld] port = 3306 socket = /opt/mysql/mysql.sock skip-locking skip-name-resolve back_log=100 key_buffer_size = 2048M query_cache_size = 128M query_cache_limit = 2M #default=1M query_cache_min_res_unit = 2k #default=4K max_allowed_packet = 16M table_cache = 3072 tmp_table_size = 1024M #default=16M max_heap_table_size = 384M #default=16M read_buffer_size = 8M read_rnd_buffer_size = 32M sort_buffer_size = 256M join_buffer_size = 32M myisam_sort_buffer_size = 256M thread_cache_size = 80 #default=0 thread_stack = 192K #default=192K # Try number of CPU’s*2 for thread_concurrency thread_concurrency = 16 #default=10 connect_timeout = 30 #interactive timeout = 600 max_connection = 350 max_connect_errors = 30 wait_timeout = 30服务配置为5504(4核) *2,16G内存,centos 5.3 64位 大大小小十几个数据库,几十G数据,主要的应用是discuz论坛
根据提示对my.cnf做一些优化,效率更好了点. 关键还是需要优化sql来减少慢查询及锁定的问题.
key_buffer_size = 3096M table_definition_cache = 800 join_buffer_size = 4M low_priority_updates =1
近期评论