Portal Home > Knowledgebase > Articles Database > High Mysql Usage
High Mysql Usage
| Posted by khansahib, 12-08-2010, 12:03 AM |
| Hi,
My server have 12 GB of Ram and its Xeon dual processor quad core 2.33, while checking the top -c command using ssh, i have noticed that mysql is using 30-70% of cpu almost all the time, i have optimized the databases which are using high mysql but still the load is very high. here is what i have in my.cnf file.
Please suggest what changes I should make.
Thanks.
|
| Posted by krizag, 12-08-2010, 02:43 PM |
| try mysqltuner.pl
|
| Posted by viGeek, 12-08-2010, 03:22 PM |
| Run this in mySQL:
SHOW FULL PROCESSLIST;
How many rows does it return? You may be experiencing issues with queries hanging thus causing high cpu load.
|
| Posted by gone-afk, 12-08-2010, 03:42 PM |
| Start by changing "long_query_time = 2" to "long_query_time = 1" then examine your MySQL slow query log.
Do not edit your my.cnf randomly without know exactly what needs to be set and for what reason (e.g. based on your actual index sizes etc).
|
| Posted by marrtins, 12-08-2010, 04:17 PM |
| Are you using only myisam type tables? What is output of:
You may try to enable slow query log also and watch what goes in there.
|
| Posted by krizag, 12-10-2010, 01:36 AM |
| is there mysql slow queries on your server?
|
| Posted by DenRomano, 12-10-2010, 08:10 AM |
| You did not say what OS you are using but most OSes with TOP show the CPU usage as 100% PER core. So you really have 100% times the number of cores.
So in your case you have 8 cores or 800% maxium cpu percent NOT just 100%.
What is the load adverages?
|
| Posted by srvadmin, 12-11-2010, 03:20 AM |
| Find the location of your php.ini file
you will out put like this:
edit the php configuration file
find directive "mysql.allow_persistent" and turn it off. Then restart apache and after that restart mysql.
This should make some difference in the high usages.
|
| Posted by khansahib, 12-12-2010, 11:22 PM |
| well its not the peak time here is what i got.
|
| Posted by khansahib, 12-12-2010, 11:24 PM |
| I am not sure how to do that, can you please step my step i mean how to examine slow query log?
|
| Posted by khansahib, 12-12-2010, 11:25 PM |
| here is the output i got.
and
|
| Posted by khansahib, 12-12-2010, 11:31 PM |
| its Cent OS.
|
| Posted by khansahib, 12-13-2010, 12:41 AM |
| I have done that, but still mysql is using 30-60% cpu.
|
| Posted by khansahib, 12-18-2010, 07:48 AM |
| Can anybody please help me? I am still getting this issue.
|
| Posted by marrtins, 12-18-2010, 01:50 PM |
| It seems to me you may have a old version of MySQL? Which version do you have?
You are using myisam tables and you have yout key_buffer is only ~25MB and it is full. You have plenty of memory so try to increase key_buffer value. Set it under [mysqld] - it's important.
Let start with key_buffer=256MB and then after a while watch Key_blocks_used and Key_blocks_unused values in SHOW STATUS report and increase/decrease as needed.
sort_buffer 64MB seems to me overkill, try to change it to 1MB and add it under [mysqld] tab also.
You may want to inspect slow query log as well to see some queries which could be optimized.
|
| Posted by khansahib, 12-19-2010, 02:55 AM |
| here is what we have.
MySQL MEMORY USAGE
Max Memory Ever Allocated : 165 M
Configured Max Per-thread Buffers : 7.94 G
Configured Max Global Buffers : 29 M
Configured Max Memory Limit : 7.97 G
Physical Memory : 11.73 G
Max memory limit seem to be within acceptable norms
and
------------------
root@server [~]# mysql -V
mysql Ver 14.12 Distrib 5.0.91, for unknown-linux-gnu (x86_64) using readline 5.1
root@server [~]#
------------------
|
| Posted by marrtins, 12-19-2010, 03:01 AM |
| Did you tried to change those parameters I suggested?
|
| Posted by khansahib, 12-19-2010, 03:15 AM |
| I have done the following to my.cnf
|
| Posted by IDediServer Kevin, 12-19-2010, 06:28 PM |
| Why are you adjusting things before even figuring out what queries are causing the CPU spike as that would put you into the proper direction in regards to adjusting anything.
[mysqld]
log-slow-queries
long_query_time=1
restart and pay attention to your slow log file
|
| Posted by marrtins, 12-20-2010, 02:00 AM |
| I don't see you did anything I was suggesting.
|
| Posted by khansahib, 12-20-2010, 02:33 AM |
| Actually I am new to such things, can you please suggest me the exact number that I should increase.
I am sorry for all this, but I am still learning.
|
| Posted by khansahib, 12-20-2010, 07:16 AM |
| Here is the result of mysqltuner.pl
|
| Posted by x86brandon, 12-20-2010, 06:09 PM |
| I would also add
log-queries-not-using-indexes
To your my.cnf so that you find out if there are queries that are not using an index properly.
|
Add to Favourites 
Print this Article
Also Read
