1

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

file my.cnf:

[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
  • 2
    Is that 130GB of RAM? Are all of your tables MyISAM? Are the 1500 connections "running" (as opposed to "Sleep" in SHOW PROCESSLIST)? Do the cron jobs all fire off at the top of the hour, or are they staggered? – Rick James Jul 28 '17 at 3:31
  • @RickJames: yes it's 130gb of ram, table are mixed some myisam and others innodb, some connections sleep others running queries, we use persistent connections, we have many cron that run in differents time. – CentosMrqst Jul 28 '17 at 11:24
1

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.

  • do u think that my.cnf is fine? i changed the thread_cache_size and it seems help a bit, maybe some others change could help a bit, i will take a look at sar program, it's easy to use? – CentosMrqst Jul 29 '17 at 21:30
  • I'm using persistent connections in php to connect MySQL, sometime when i make show full processlist, it show many sleep connections, maybe i shouldn't use persisten connections? – CentosMrqst Jul 29 '17 at 22:36
0

With your

innodb_buffer_pool_size = 35G

you should probably consider using

innodb_buffer_pool_instances = 8

to improve concurrency and reduce contention.

0

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.

  • @CentosMrqst How is your server running now? Another tip for you, in your my.cnf-ini [mysqld] section innodb_lru_scan_depth=128 # from 1024 will conserve CPU cycles EVERY SECOND. For additional assistance, check my profile, clk Network Profile for contact info, please. If any of the previous advice helped, please up vote and/or Accept answers. – Wilson Hauck Jun 15 '18 at 19:34
  • @BillThor Another tip for you, in your my.cnf-ini [mysqld] section innodb_lru_scan_depth=128 # from 1024 will conserve CPU cycles EVERY SECOND when you have 8 innodb_buffer_pool_instances. For additional assistance, check my profile, clk Network Profile for contact info, please. – Wilson Hauck Jun 15 '18 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.