I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:
- CentOS 5.9
- 24gb Ram
- 8 Core CPU
- 50gb FusionIO disk for /var/lib/mysql
-
MySQL 5.5.23
top – 18:40:27 up 14 days, 4:43, 1 user, load average: 19.72, 22.62, 24.04
Tasks: 183 total, 3 running, 180 sleeping, 0 stopped, 0 zombie
Cpu(s): 69.9%us, 0.4%sy, 0.0%ni, 29.2%id, 0.0%wa, 0.1%hi, 0.4%si, 0.0%st
Mem: 24685224k total, 20172096k used, 4513128k free, 343420k buffers
Swap: 2007284k total, 0k used, 2007284k free, 729004k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5446 mysql 15 0 18.4g 17g 6176 R 765.8 76.2 114437:38 mysqld
I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file:
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#innodb
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400
skip-name-resolve
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10
event_scheduler=ON
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements?
Best Answer
You need to unleash two things
I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.
May 26, 2011
: About single threaded versus multithreaded databases performanceSep 12, 2011
: Possible to make MySQL use more than one core?Sep 20, 2011
: Multi cores and MySQL PerformanceApr 26, 2012
: Is the CPU performance relevant for a database server?Give it a Try !!!
UPDATE 2013-06-20 02:39 EDT
Your last comment
I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.
Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:
If you were using RAID 10 SAS or even SSDs for
/var/lib/mysql
, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.SUGGESTION
Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.
Give it a Try !!!