Sample Header Ad - 728x90

MySQL using too much CPU

6 votes
4 answers
43155 views
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?
Asked by Logan Best (197 rep)
Jun 19, 2013, 10:53 PM
Last activity: Feb 22, 2018, 03:45 PM