MySQL Forums
Forum List  »  InnoDB

Re: MySQL 5.5 hangs frequently in production environment
Posted by: Rick James
Date: July 05, 2014 12:00PM

> Please note that max_connections is currently set to 4000 in our database.

Yes. And I am suggesting that that is indirectly leading to the "hang". Do you really expect thousands of simultaneous clients? Even if you do, they should be throttled down to (at most) hundreds at the Java level.

STATUS and VARIABLES analysis:

Assuming 64 GB of RAM
You are not running on Windows.
You appear to be running entirely (or mostly) InnoDB.

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (64M / 0.20 + 10737418240 / 0.70) / 65536M = 22.8% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 596,898 * 16384 / 10737418240 = 91.1% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it

The above two comments say that you have a lot of RAM, but not much data. And the buffer_pool setting is sufficient.

( table_open_cache ) = 4,000 -- Number of table descriptors to cache
-- Several hundred is usually good.

( Innodb_row_lock_time_avg ) = 82,452 -- Avg time to lock a row (millisec)
-- Increase innodb_log_buffer_size?

( innodb_lock_wait_timeout ) = 1,000 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks.
-- Fix the cause of timeouts rather than increasing this value.

( innodb_rollback_on_timeout ) = OFF
-- Probably wise to have it ON, for data integrity.

( innodb_additional_mem_pool_size ) = 100M -- (Removed in 4.1.0? VARIABLE deprecated in 5.6.3.)
-- A high value is unnecessary.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( Select_scan ) = 94,128 / 5327 = 18 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 94,128 / 555555 = 16.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( binlog_format ) = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.

( long_query_time ) = 300.000000 = 300 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Com_change_db / Connections ) = 82,208 / 385 = 213 -- Database switches per connection
-- (minor) Consider using "db.table" syntax

( Threads_running - 1 ) = 30 - 1 = 29 -- Active threads (concurrency when data collected)
-- Optimize queries and/or schema

( Threads_created / Connections ) = 377 / 385 = 97.9% -- Rapidity of process creation
( thread_cache_size ) = 0 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
0 is inefficient for non-Windows. This may be a significant factor in your 'hangs' because of the overhead of creating new threads. Suggest 20 for your situation.

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.