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?