Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm running a young non-profit organization aiming at providing free and low cost web hosting.

Since few days, our server load is around 1.5, sometimes raising to 7. top shows me that MySQL is the bad boy:

PID  USER   PRI  NI  VIRT   RES   SHR  S CPU%  MEM%  TIME+   Command
26362 mysql 20   0   2757M  762M  5872 S 16.0  7.7   3h54:51 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/shadow.err --open-files-limit=50000 --pid-file=/var/lib/mysql/shadow.pid

Do you have an idea of how can I determine the script running the query ? Thank you for your help.

share|improve this question

2 Answers 2

As mysql root user, try:

 mysql> show processlist;

That will allow you to identify the query causing the load and enable you to trace it back to php script you're looking for.

Note: If the CPU usage of the mysql-process is low (as shown in the top output) but you're experiencing a >1 load it's likely that mysql is causing too much IO.

share|improve this answer
    
Thanks for the advice (I didn't think about it, but I only have the "user", how to get the script ?). Here is what I get (while load = 0.90) Screenshot, so maybe it's related to IO. Is there a way to check that? BTW, while writing this, MySQL bursts during 1 sec (on HTOP) from 1% to 43% and 46% CPU for 2 different processes :/ –  Max13 May 1 '13 at 23:40
    
Finally, I've used the "full processlist" command twice. First one, 43% CPU by MySQL and a second one, 119% by MySQL and apparently, Brian is the winner. What should I do? Optimize MySQL, warn the user, nothing...? –  Max13 May 1 '13 at 23:56
    
If the code wasn't changed recently there are 3 possible reasons for this: 1. php-program usage changed 2. the data has grown over time making the queries slow 3. another process on the server consumes all IO starving out mysql. You'll have to determine the cause and take action accordingly. –  Zabuzzman May 2 '13 at 8:03
    
Actually, I'm a webhosting service, script can change everyday, and data can grow too. So If I understand, I should upgrade the proc of my server, isn't it ? –  Max13 May 2 '13 at 11:22
    
If you can't maintain the code or tune the DBMS then upgrading the hardware is the only option left - but its not clear from the data you've provided where the bottleneck is. You might want to get a copy of the O'Reilly High Performance MySQL by Schwartz, Zaitsev and Tkachenko –  symcbean May 3 '13 at 8:58

SHOW FULL PROCESSLIST;

This will give you currently running queries on your server and time taken by it, copy and append a query with EXPLAIN EXTENDED and evaluate result make approriate changes to the query by adding indexes, changing data-types etc..

You should not use default variable values, you should always change some of the variables to achieve great performance like query_cache_size, key_buffer, table_cache, sort_buffer, read_rnd_buffer_size, thread_cache etc..

Reference : http://www.botskool.com/geeks/how-reduce-server-load-mysql-caching-and-optimization

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.