Re: Mysql 5.5 High I/O Wait
Analysis...
( Innodb_buffer_pool_reads ) = 110,051,219 / 121716 = 904 /sec -- InnoDB buffer_pool I/O read rate
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 110,078,055 / 2645596624 = 4.2% -- Read requests that had to hit disk
That is, 96% of the times it wanted to read an InnoDB block, it found it in the buffer_pool. (Increasing buffer_pool_size would not help much.)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 121,716 / 60 * 128M / 15114178560 = 18 -- Minutes between InnoDB log rotations
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.
(Not critical; it is complex to change.)
( Innodb_row_lock_waits ) = 26,822 / 121716 = 0.22 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.
This is moderately high. (But there is no action that can be taken immediately, other than dealing with the queries.)
( innodb_lock_wait_timeout ) = 180 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
50 is the default; you apparently increased it? It would be better to speed up the queries. Otherwise, you are blocking other queries for up to 3 minutes.
( sort_buffer_size ) = 2M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.
( binlog_format ) = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( max_connect_errors ) = 1,000,000 = 1.0e+6 -- A small protection against hackers.
-- Perhaps no more than 200.
( Connections ) = 760,208 / 121716 = 6.2 /sec -- Connections
-- Increase wait_timeout; use pooling?
( thread_cache_size ) = 50 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
( Max_used_connections ) = 199
This is less than the MaxClients, so something else is going on?