Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
[mysqld] socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock ft_min_word_len=2 datadir=/var/lib/mysql skip-locking skip-innodb old-passwords = 1 key_buffer = 256M key_buffer_size = 1350M table_cache = 2048 sort_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 32M join_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache_size = 256 query_cache_size = 800M query_cache_limit = 384M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 thread_cache_size = 8 # log-bin # server-id=1 max_connections = 500 #Increase max_connect_errors from 10 to 1000 max_connect_errors=1000 #this is to log and find out slow query that slow down the server #log-queries-not-using-indexes #long_query_time = 5 log-slow-queries=/var/log/mysqld/slow.log #start added on Jan 30, 2009 #record_buffer=1M #net_buffer_length=16K #bulk_insert_buffer_size=1M #thread_cache=20 max_heap_table_size=1800M tmp_table_size=384M wait_timeout = 1800 #ended added on Jan 30, 2009 connect_timeout = 10 interactive_timeout = 120 [mysql.server] user=mysql #basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 32M read_buffer = 2M write_buffer = 2M [myisamchk] ft_min_word_len=2 key_buffer = 256M sort_buffer_size = 32M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [client] socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
[(04:46 AM)][(root@alpha)] [(~)] $ ./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 potential sockets: /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock Using: /var/lib/mysql/mysql.sock Would you like to provide a different socket?: [y/N] n Do you have your login handy ? [y/N] : y User: root Password: temp123 Would you like me to create a ~/.my.cnf file for you? [y/N] : y -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.1.58-community-log i686 Uptime = 6 days 14 hrs 52 min 7 sec Avg. qps = 90 Total Questions = 51940860 Threads Connected = 1 Server has been running for over 48hrs. It should be safe to follow 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 enabled. Current long_query_time = 10.000000 sec. You have 18736 out of 51940881 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 = 8 Current threads_cached = 7 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 500 Current threads_connected = 1 Historic max_used_connections = 173 The number of used connections is 34% of the configured maximum. Your max_connections variable seems to be fine. No InnoDB Support Enabled! MEMORY USAGE Max Memory Ever Allocated : 13.45 G Configured Max Per-thread Buffers : 32.80 G Configured Max Global Buffers : 2.09 G Configured Max Memory Limit : 34.90 G Physical Memory : 7.92 G Max memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 1.26 G Current key_buffer_size = 1.31 G Key cache miss rate is 1 : 13667 Key buffer free ratio = 43 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 800 M Current query_cache_used = 159 M Current query_cache_limit = 384 M Current Query cache Memory fill ratio = 19.91 % Current query_cache_min_res_unit = 4 K Query Cache is 26 % 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. Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 32 M Current read_rnd_buffer_size = 32 M Sort buffer seems to be fine JOINS Current join_buffer_size = 1.00 M You have had 1553861 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 8192 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 = 2048 tables Current table_definition_cache = 256 tables You have a total of 700 tables You have 1322 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 = 1.75 G Current tmp_table_size = 384 M Of 196197 temp tables, 13% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 2 M Current table scan ratio = 217 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 177 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'. [(04:48 AM)][(root@alpha)] [(~)] $
Join the community of 500,000 technology professionals and ask your questions.