Mysql High CPU consumption
Hi all,
I am getting problems in a couple of hosts where mysqld eats a lot of cpu resources up to leave the server unresponsive. We recently increased physical memory to 8gb and updated the kernel to PAE since we are running under 32bits architecture.
Today, I was able to capture this in one of my servers:
As you can see load was very high, I checked mysql process list and there was only one user doing a select sentence. I restarted mysql and load dropped immediately. I am not a an expert, so I would like to have your recommendations to tune mysql on my servers. I'm running version 5.0.95 on RHEL 5.9 on both boxes. Here is my.cnf file:
and here the result of mysqltuner.pl:
Thanks in advance for your help.
root@host [~]# uptime
11:08:16 up 2 days, 1:01, 1 user, load average: 208.70, 206.76, 163.35
root@host [~]# top
top - 11:09:12 up 2 days, 1:02, 1 user, load average: 224.49, 210.20, 166.95
Tasks: 689 total, 6 running, 680 sleeping, 0 stopped, 3 zombie
Cpu(s): 6.4%us, 92.6%sy, 0.1%ni, 0.6%id, 0.0%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 8309024k total, 8016416k used, 292608k free, 387928k buffers
Swap: 8385888k total, 0k used, 8385888k free, 5100948k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6277 mysql 15 0 132m 70m 3632 S 388.9 0.9 1342:25 mysqld
22538 XXXXXX 16 0 91448 10m 5796 S 1.3 0.1 0:00.21 php
21065 XXXXXX 18 0 94936 28m 22m R 1.0 0.3 0:00.73 php
22471 XXXXXX 16 0 97008 15m 6268 S 1.0 0.2 0:01.86 php
As you can see load was very high, I checked mysql process list and there was only one user doing a select sentence. I restarted mysql and load dropped immediately. I am not a an expert, so I would like to have your recommendations to tune mysql on my servers. I'm running version 5.0.95 on RHEL 5.9 on both boxes. Here is my.cnf file:
[mysqld]
skip-bdb
skip-innodb
set-variable = max_connections=500
tmpdir = /dev/shm
safe-show-database
max_allowed_packet=16Mand here the result of mysqltuner.pl:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95-community
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 2549)
[--] Data in MEMORY tables: 0B (Tables: 14)
[!!] Total fragmented tables: 414
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 27m 26s (208K q [39.673 qps], 9K conn, TX: 578M, RX: 25M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (16% of installed RAM)
[OK] Slow queries: 0% (79/208K)
[OK] Highest usage of available connections: 3% (17/500)
[!!] Key buffer size / total MyISAM indexes: 8.0M/347.7M
[!!] Key buffer hit rate: 87.7% (40M cached / 4M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
[!!] Joins performed without indexes: 324
[!!] Temporary tables created on disk: 27% (2K on disk / 9K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 15K opened)
[OK] Open file limit used: 5% (128/2K)
[OK] Table locks acquired immediately: 99% (227K immediate / 227K locks)
[!!] Connections aborted: 68%
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 347.7M)
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)Thanks in advance for your help.
-
Hello :) Please ensure you let MySQL run for at least 24 hours to get the most accurate tuner results. Or, if the load is too much, try troubleshooting the load further to see if you can find out additional information: Troubleshooting High Loads On Linux Systems Thank you. 0 -
Thanks for your response Michael, but I have not applied any tune yet, that is what I am trying to confirm. I know there are a lot of posts about this issue on this forum, I went through many of them, but as you know every environment is different. Basically, I would like to know if all recommendations suggested by mysqltuner will apply to my environment or just some of them. Thanks, 0 -
I moved this thread to our "Optimization" forum. You should receive more user-feedback here. Thank you. 0 -
Thanks Michael. 0 -
First of all I will suggest you to run "watch mysqladmin proc" command It will show you current db which is eating resources. If you find any db then enable slow queries so that you can check which query is taking time to execute. Also, include below variables in my.cnf. Check the correct syntax of that. =========================== key_buffer_size (> 347.7M) query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) tmp_table_size (> 32M) max_heap_table_size (> 16M) thread_cache_size (start at 4) table_cache (> 64) ========================== 0 -
Thanks 24x7 for your reply, I will follow your recommendations and will monitor the server for 24hrs. After that, I will post the results here. Thanks, 0
Please sign in to leave a comment.
Comments
6 comments