2

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.

3 Answers 3

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.

5
  • 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 at 8:58
2

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

2

If you enable the slow query log in mysql, it will give you a starting point for any SQL statements that take a lot of resources. It won't tell you which PHP script was the culprit, but you will have the database, table and SQL, and the time of the problem;
http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined.

Run the following query;

set global slow_query_log = 1;

By default the slow query log can be accessed like so;

mysqldumpslow /var/log/mysql/mysql-slow.log

here are some examples on parsing the information in the slow query log here;
https://easyengine.io/tutorials/mysql/slow-query-log/

You can combine that with examining the web server log files, to find requests that occurred at the same time as the long running query. For example once you have found a particularly slow query examine the apache or nginx log files;

[root@vps001 www]# vi /var/log/httpd/access_log

will show you which php file corresponds to a particular time request;

185.142.237.8 - - [03/Jul/2016:04:27:14 +0000] "POST /xmlrpc.php HTTP/1.1" 200 370 "-" "Googlebot/2.1 (+http://www.google.com/bot.html)"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .