MySQL Boot Monitor

Question

MySQL

Friends, this is the situation - there is a separate server for the muscle, on which one site is running with relatively low traffic (100k hits per day), the site itself is quite large, it has a lot of logic and separate services, so there are also slow requests, while the database server lives quietly for several weeks, until at one moment it goes down, it helps to stop the web server for a few minutes so that mysql completes all queries, after which it is back in service

, so my question is how to find out due to which requests (or their combination) the server goes down? plz tell me the means for this

view slow query log and show proccesslistespecially does not give anything. there are slow requests and there is no getting away from them, but after all, he can work perfectly with them for months

Answer 1

There is a mysqldumpslow utility that analyzes the slow query log and displays the most frequent slow queries, sorry, I didn’t know about it before, I hope it helps to solve at least part of the problem.

Answer 2

Use the mytop utility.

Answer 3

right now I’m looking at top - mysqld is eating 150-200% of the CPU, I’m looking at SHOW PROCESSLIST (and mytop) - with an update per second, it skips 5-10 queries, then it’s empty, no one hangs for a long time ... while the database frankly slows down even on elementary queries
in what it can be the case?

Answer 4

From your explanation, I understood that the database does not go down, but simply a large queue of requests is collected?
What kind of requests? insert/select/update?
Site traffic has a small role in the load ... It all depends on the structure of the database, its size, the action on the data in it. It is possible to hang up mysql and one request for a very long period.

Answer 5

www.fromdual.com/tools + Zabbix
In general, there was such a question earlier (http://habrahabr.ru/qa/10967/ )

Answer 6

Look, there was an article recently about how the server crashed every 4 weeks due to auto_increment overflow, maybe your case.

Answer 7

Is the hardware (screw or RAID on which the database is spinning) okay?

Answer 8

still a long-standing question to which all hands do not reach to know the answer
we have a query:
SELECT id FROM something WHERE param1 = 1 param2 = 2 param3 = 3
the table structure consists only of these 4 fields participating in the query
question - if the table structure consists of 100 fields, and not 4, will the specified query be executed more slowly?

Answer 9

if there is an index for these fields, then the difference will be noticeable only by devices