Re: Mysql super slow - Help me analyze my MySQL installation
Posted by:
Rick James
Date: September 01, 2013 10:46AM
> These cause long running queries of 5-15 seconds.
Let's see SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT.
Top recommendations:
* You appear to be running entirely (or mostly) InnoDB. If so, innodb_buffer_pool_size should be set to about 70% of RAM; that is more like 20G instead of 3G. That would help some of the items below...
* innodb_log_file_size is lower than it ought to be. Caution: changing the value is complex.
* Turn off the Query cache (type=OFF, size=0).
Details of metrics supporting those suggestions, plus other suggestions...
( Question ) = 346/sec
( Innodb_buffer_pool_reads ) = 50,414,245,778 / 8961459 = 5625 /sec -- InnoDB buffer_pool I/O read rate
-- That's a huge read rate. Do you have an array of SSDs?
( Opened_files ) = 218,509,101 / 8961459 = 24 /sec -- Frequency of opening Files
-- table_open_cache is already high (1024); do you have a huge number of tables? (That is usually a poor design.)
This may require increasing the ulimit -n value in the OS.
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 87,182,366,357 / 2589498330994 = 3.4% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size.
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 238,086,928 / 1280450349 = 18.6% -- Write requests that had to hit disk
-- Check innodb_buffer_pool_size
( Innodb_buffer_pool_reads ) = 50,414,245,778 / 8961459 = 5625 /sec -- Reads
( innodb_log_buffer_size ) = 1M -- Suggest 4MB-64MB, and at least as big as biggest blob
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 177,279,212,032 / (8961459 / 3600) / 2 / 5M = 6.79 -- Ratio
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 8,961,459 / 60 * 5M / 177279212032 = 4.42 -- Minutes between InnoDB log rotations
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.)
-- Suggest adjusting innodb_log_file_size to 100M.
( innodb_file_per_table ) = OFF -- Put each file in its own tablespace
-- (Mildly recommended)
( Innodb_row_lock_time_avg ) = 15,759 -- Avg time to lock a row (millisec)
-- Increase innodb_log_buffer_size (currently 1M)?
( read_buffer_size ) = 524,288 = 0.5MB -- Bigger may be slower
( local_infile ) = ON
-- local_infile = ON is a potential security issue
( Qcache_lowmem_prunes ) = 828,236,185 / 8961459 = 92 /sec -- Query Cache spilling -- Prune rate (per sec)
-- increase query_cache_size
( Qcache_hits / Qcache_inserts ) = 1,131,383,165 / 1467374969 = 0.771 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.
( Qcache_free_blocks / Qcache_total_blocks ) = 1,688 / 53757 = 0.0314 -- Fragmentation in Query Cache.
-- Various things.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (64M - 5690480) / 25911 / 8192 = 0.289 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size
( Created_tmp_disk_tables ) = 50,635,504 / 8961459 = 5.7 /sec -- Frequency of creating _disk_ "temp" tables as part of complex SELECTs
-- Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM.
( tmp_table_size ) = ( max_heap_table_size ) = 64M -- Limit on size of temp tables used to support a SELECT
-- While increasing these might help Created_temp_disk_tables, it is already a rather large value.
( Select_full_join ) = 167,419,523 / 8961459 = 19 /sec -- joins without index
-- Add suitable index(es) to tables used in JOINs.
( Select_full_join / Com_select ) = 167,419,523 / 1751973799 = 9.6% -- % of selects that are indexless join
-- Add suitable index(es) to tables used in JOINs.
( Select_scan ) = 350,527,793 / 8961459 = 39 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( Select_scan / Com_select ) = 350,527,793 / 1751973799 = 20.0% -- % of selects doing full table scan
-- Add indexes / optimize queries
( Sort_merge_passes ) = 3,061,715 / 8961459 = 0.34 /sec -- Heafty sorts
-- increase sort_buffer_size
( sort_buffer_size ) = 2,097,144 = 2MB -- One per thread, malloced at full size.
-- This may be eating into available RAM; recommend no more than 2M.
( long_query_time ) = 5.000000 = 5 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( Slow_queries ) = 0.063 / sec
-- Increasing long_query_time may show other interesting queries. Meanwhile, check the slowlog and try to optimize the queries there.
( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections
-- increase max_connections and/or decrease wait_timeout
( Connections ) = 29,274,640 / 8961459 = 3.3 /sec -- Connections
-- Increase wait_timeout; use pooling?