MySQL can't cope with the load from the web application, logs are needed for further analysis

Question

PHP

Greetings!

I am one of the developers of a software package (PHP) that uses the MySQL database for information storage. Last Sunday, when executing some fairly complex scheduled tasks, MySQL struggled to keep up with the loads, accompanied by a large number of Lock wait timeout exceeded; try restarting transaction and a huge number of simple requests waiting for their queue in the InnoDB queue for about 3- x (!) hours.

The question is this: after a short period of time, namely this weekend, tasks similar in volume and complexity will be completed. There is a need to prepare in order to either understand the cause of the locks on the spot and find architectural miscalculations, or (better) to get the maximum amount of useful information for further analysis, for example: the status of locks at a certain time, queries that block tables, queries that never got the opportunity execute due to blocking. One log of slow requests will not be enough. What tools can be used to collect such information?

Perhaps someone has already encountered similar problems and has an idea how to solve them?

Answer 1

Definitely you should post your my.cnf
and mysql server parameters
and enable mysql slow query log and post it here.
Tips put perkonu here more from the evil one than to help.

Answer 2

Tuning for similar articles helped me:
www.pentarh.com/wp/2011/03/02/mysql-innodb-highload-optimization/
www.opennet.ru/base/dev/mysql_innodb_tune.txt.html
Performance increased several times, before that, there were terrible brakes on a large number of simultaneous requests, and with tables of several hundred thousand rows, I could barely toss and turn. Now everything is flying. In principle, it is enough to add a few directives to my.cnf.

Answer 3

For such purposes, I had a script running in the background: every 5 seconds it counts the number of active MySQL threads, and if the number of requests is more than N, then it dumps the running requests to the log file.
It was in this way that the main blocking requests were identified.
For FreeBSD, run via screen:

#!/bin/sh

HOSTNAME=`hostname -s`
DIR="/var/log/mlog.$HOSTNAME"
pass='testpass'

while :
do
    threads=`mysql -uroot -p$pass -B -e "show status"|grep ^Threads_running|cut -f 2`

# если тредов больше 60, пишем в лог:
    if [ "$threads" -gt 60 ] ; then
        {
            mysqladmin -uroot -p$pass processlist --verbose |grep -v Sleep|sed 's|                   ||g'
            iostat -dNx 2 2            
        } |gzip > "$DIR/`date "+%Y-%m-%d_%H:%M:%S"`.list.gz"
    fi
    sleep 5
done

Answer 4

Definitely put Percona, there are more complete statistics on plans and performance in general than that of a vanilla muscle

Answer 5

In addition to my.cnf, it would also be useful to specify the server configuration (minimum - how much memory and cpu) and see "top" at the time of loading.
I had something similar when the load began to grow on the server. I set it up gradually through my.cnf and adding memory.
I found a lot of interesting tips and ideas here: www.mysqlperformanceblog.com/

Answer 6

Why is one slow query log not enough? After all, MySQL itself especially ... does not give anything for analysis.

Answer 7

Read articles on MySQL.
If you have the default server settings, then I recommend correcting them for the correct ones - there is information on the network how to properly configure InnoBD conf for maximizing server performance.
MySQL also needs fast hardware.
From the first answers you are recommended to install Percona, but I assure you you should not resort to Percona, MySQL InnoBD has already grown and is not bad, like previous versions were.
In general, start with this, and then look at the logs. Good luck.