MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

Question

I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem with concurrent connections, with one access to my service, the MySQL are opening thousands connections and growing, the connection number only decreases when I restart the MySQL service, I think that isn't my server hardware because it has 32 cpu cores and 64gb RAM, I don't understand what is happening but I need to solve this quickly. All databases and tables are using InnoDB engine, and my application are closing all connections that it opens.

Anyone had the same problem when migrate databases like this?

Answer 1

You will need to tune InnoDB with the following

innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_buffer_pool_size = 512M
innodb_log_file_size    = 128M
innodb_log_buffer_size  = 32M

You should then download and run mysqltuner.pl

You should get output resembling this

# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 457M (Tables: 1361)
[--] Data in MRG_MYISAM tables: 53M (Tables: 36)
[--] Data in InnoDB tables: 792M (Tables: 1133)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 175

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54s (2K q [37.537 qps], 325 conn, TX: 1M, RX: 354K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 16.2G (874% of installed RAM)
[OK] Slow queries: 0% (9/2K)
[OK] Highest usage of available connections: 1% (13/800)
[OK] Key buffer size / total MyISAM indexes: 128.0M/78.9M
[!!] Key buffer hit rate: 73.9% (1K cached / 297 reads)
[OK] Query cache efficiency: 42.4% (388 cached / 915 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 148 sorts)
[OK] Temporary tables created on disk: 21% (114 on disk / 537 total)
[OK] Thread cache hit rate: 94% (17 created / 325 connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 1% (2K/200K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 792.7M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

You then need to tune the per-connection settings See my post How costly is opening and closing of a DB connection? for the buffer settings to tune and my other post Maximum possible memory usage: 16.2G (874% of installed RAM) for an example of how to tune the settings.

UPDATE 2014-10-02 12:39 EDT

Please look back at Maximum possible memory usage: 16.2G (874% of installed RAM). OP was requesting 874% of RAM, which is similar to your situation (You are requesting 771% of RAM in your pastebin). Perhaps dropping max_connections to 1000 would safeguard mysqld's RAM usage.

Login to mysql and run

mysql> SET GLOBAL max_connections = 1000;

Then, run mysqltuner.pl and the numbers will change and mysqld will no longer hit thje ceiling on memory. Once you are satisfied with the RAM usage (you should get RAM usage under 75%), add this to my.cnf

[mysqld]
max_connections = 1000

or whatever number you find make RAM usage under 75%

GIVE IT A TRY !!!

Answer 2

If you are using transactions, please ensure that you use either a COMMIT or ROLLBACK statement before closing the connection.

If this is already the case, you probably need to increase the thread_cache_size value in order to reuse as much as possible the existing connection threads instead of creating new ones from start.

Please note the following values:

• mysql> SHOW GLOBAL VARIABLES LIKE 'Connections';
• mysql> SHOW GLOBAL VARIABLES LIKE 'Thread_created';
• mysql> SHOW GLOBAL VARIABLES LIKE 'Max_used_connections';

If Thread_created/Connections> 0.01, you have to increase the value of thread_cache_size. The upper limit should be the value of Max_used_connections.