MySQL Performance Tuning Primer Script<\/p>\n
This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
\nto produce sane recommendations for tuning server variables.
\nIt is compatible with all versions of MySQL 3.23 and higher (including 5.1).<\/p>\n
Currently it handles recommendations for the following:
\n# Slow Query Log
\n# Max Connections
\n# Worker Threads
\n# Key Buffer
\n# Query Cache
\n# Sort Buffer
\n# Joins
\n# Temp Tables
\n# Table (Open & Definition) Cache
\n# Table Locking
\n# Table Scans (read_buffer)
\n# Innodb Status<\/p>\n
Recent Changes
\n# Correct awk display error which formats integers > 4294967296 in scientific notation.
\n– Was manifest in MEMORY USAGE section where total system RAM > 4GB.
\n# Fixed rounding error where mysql will lose 4K from the join_buffer_size value.
\n– Other values may have the same issue but are not yet reported
\n# Added support for FreeBSD and MacOS (thanks Sam and Geert)
\n# Added support for Solaris
\n# Changed how system memory is derived on Linux.
\n– Use \/proc\/meminfo vs free -b and avoid inclusion of swap space.
\n# Include note warning of instability when key_buffer_size > 4GB in versions 5.0.51 and lower<\/p>\n
To get the tuning-primer.sh run the following from the command line \t-- MYSQL PERFORMANCE TUNING PRIMER -- MySQL Version 5.0.87-community i686<\/p>\n Uptime = 35 days 4 hrs 24 min 23 sec Server has been running for over 48hrs. To find out more information on how each of these SLOW QUERIES BINARY UPDATE LOG WORKER THREADS MAX CONNECTIONS No InnoDB Support Enabled!<\/p>\n MEMORY USAGE KEY BUFFER QUERY CACHE SORT OPERATIONS JOINS OPEN FILES LIMIT TABLE CACHE TEMP TABLES TABLE SCANS TABLE LOCKING
\nwget -O \/scripts\/tuning-primer.sh http:\/\/day32.com\/MySQL\/tuning-primer.sh<\/code>
\nand then execute the script like so
\n
\nroot@host [~]# sh \/scripts\/tuning-primer.sh <\/p>\n
\n\t - By: Matthew Montgomery -<\/p>\n
\nAvg. qps = 0
\nTotal Questions = 953824
\nThreads Connected = 1<\/p>\n
\nIt should be safe to follow these recommendations<\/p>\n
\nruntime variables effects performance visit:
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/server-system-variables.html
\nVisit http:\/\/www.mysql.com\/products\/enterprise\/advisors.html
\nfor info about MySQL's Enterprise Monitoring and Advisory Service<\/p>\n
\nThe slow query log is NOT enabled.
\nCurrent long_query_time = 10 sec.
\nYou have 0 out of 953845 that take longer than 10 sec. to complete
\nYour long_query_time seems to be fine<\/p>\n
\nThe binary update log is NOT enabled.
\nYou will not be able to do point in time recovery
\nSee http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/point-in-time-recovery.html<\/p>\n
\nCurrent thread_cache_size = 0
\nCurrent threads_cached = 0
\nCurrent threads_per_sec = 1
\nHistoric threads_per_sec = 0
\nYour thread_cache_size is fine<\/p>\n
\nCurrent max_connections = 150
\nCurrent threads_connected = 1
\nHistoric max_used_connections = 13
\nThe number of used connections is 8% of the configured maximum.
\nYou are using less than 10% of your configured max_connections.
\nLowering max_connections could help to avoid an over-allocation of memory
\nSee \"MEMORY USAGE\" section to make sure you are not over-allocating<\/p>\n
\nMax Memory Ever Allocated : 110 M
\nConfigured Max Per-thread Buffers : 234 M
\nConfigured Max Global Buffers : 90 M
\nConfigured Max Memory Limit : 324 M
\nPhysical Memory : 512 M
\nMax memory limit seem to be within acceptable norms<\/p>\n
\nCurrent MyISAM index space = 1 M
\nCurrent key_buffer_size = 16 M
\nKey cache miss rate is 1 : 326
\nKey buffer free ratio = 85 %
\nYour key_buffer_size seems to be fine<\/p>\n
\nQuery cache is enabled
\nCurrent query_cache_size = 64 M
\nCurrent query_cache_used = 715 K
\nCurrent query_cache_limit = 1 M
\nCurrent Query cache Memory fill ratio = 1.09 %
\nCurrent query_cache_min_res_unit = 4 K
\nQuery Cache is 24 % fragmented
\nRun \"FLUSH QUERY CACHE\" periodically to defragment the query cache memory
\nIf you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
\nYour query_cache_size seems to be too high.
\nPerhaps you can use these resources elsewhere
\nMySQL won't cache query results that are larger than query_cache_limit in size<\/p>\n
\nCurrent sort_buffer_size = 512 K
\nCurrent read_rnd_buffer_size = 512 K
\nSort buffer seems to be fine<\/p>\n
\nCurrent join_buffer_size = 132.00 K
\nYou have had 0 queries where a join could not use an index properly
\nYour joins seem to be using indexes properly<\/p>\n
\nCurrent open_files_limit = 4096 files
\nThe open_files_limit should typically be set to at least 2x-3x
\nthat of table_cache if you have heavy MyISAM usage.
\nYour open_files_limit value seems to be fine<\/p>\n
\nCurrent table_cache value = 128 tables
\nYou have a total of 81 tables
\nYou have 128 open tables.
\nCurrent table_cache hit rate is 26%
\n, while 100% of your table cache is in use
\nYou should probably increase your table_cache<\/p>\n
\nCurrent max_heap_table_size = 16 M
\nCurrent tmp_table_size = 32 M
\nOf 35543 temp tables, 36% were created on disk
\nEffective in-memory tmp_table_size is limited to max_heap_table_size.
\nPerhaps you should increase your tmp_table_size and\/or max_heap_table_size
\nto reduce the number of disk-based temporary tables
\nNote! BLOB and TEXT columns are not allow in memory tables.
\nIf you are using these columns raising these values might not impact your
\nratio of on disk temp tables.<\/p>\n
\nCurrent read_buffer_size = 256 K
\nCurrent table scan ratio = 12 : 1
\nread_buffer_size seems to be fine<\/p>\n
\nCurrent Lock Wait ratio = 1 : 1774
\nYou may benefit from selective use of InnoDB.
\nIf you have long running SELECT's against MyISAM tables and perform
\nfrequent updates consider setting 'low_priority_updates=1'
\nIf you have a high concurrency of inserts on Dynamic row-length tables
\nconsider setting 'concurrent_insert=2'.
\n<\/code><\/p>\n