I’m having a problem with MySQL. I have a system that runs some cron jobs and these create lots of database connections. When it reaches about 1500 active connections the cpu usage goes to 100% but memory stays fine. My server config is:
CentOS release 6.6 (Final) Model Intel(R) Xeon(R) CPU E5-1660 v3 @ 3.00GHz Cores 16 Speed 1278.984 MHz Cache 20480 KB Memory 130GB
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 max_connect_errors=100 open-files=64 interactive_timeout=60 wait_timeout=10 max_connections=2000 max_allowed_packet=5M tmp_table_size=100M max_heap_table_size=100M #query cache desativado query_cache_type = 0 query_cache_limit = 50M sort_buffer_size= 1M read_buffer_size= 128K read_rnd_buffer_size=1M #join_buffer_size=208M join_buffer_size=8M key_buffer_size=20M myisam_sort_buffer_size=10M thread_cache_size = 30 key_buffer=100M open_files_limit=64 default-storage-engine=MyISAM innodb_file_per_table=1 innodb_buffer_pool_size=35G innodb_additional_mem_pool_size=80M query_cache_size=50M back_log=75 expire_logs_days = 1 max_binlog_size = 30M tmpdir = /var/mysqltmpdir long_query_time=1 log_slow_queries=/var/log/mysql_slow_queries.log [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid bind-address = * port = 3306
You may have encountered a Von Neumann Bottleneck.
CPU usage is expected to increase as the number of connections increases. As the connections increase the number of active queries should also increase. Each query will use some CPU. Eventually, you will have enough queries to use all available CPU.
There are some additional factors that while use more CPU as the number of concurrently running queries increase:
- Time spent waiting for resource locks. This may involve a lock spin which will consume additional CPU.
- Context switching when the O/S switches the currently running query. This is a side effect of the multi-processing that allows you to run more than one query at a time.
Running a program like
sar may provide more details on where the CPU is being used.
innodb_buffer_pool_size = 35G
you should probably consider using
innodb_buffer_pool_instances = 8
to improve concurrency and reduce contention.
With ~1500 active connections, consider changing my.cnf to:
thread_cache_size = 100 # v8 suggested CAP to minimize thread create/destroy.
For more detailed analysis, please add to Original Post the following:
SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; SHOW ENGINE INNODB STATUS;
for up to five specific suggestions, to be applied max of one per day, monitor.