L
L
LFFATE2015-08-10 16:08:21
MySQL
LFFATE, 2015-08-10 16:08:21

MySQL memory consumption spikes. Possible reasons?

0473a-clip-15kb.png?nocache=1
Regardless of the day of the week and time of day, the memory consumption of the server is at the level of 350MB, but a sharp jump to a gigabyte has recently been recorded. Which led to the following:
Out of memory: Kill process 11966 (mysqld) score 28 or sacrificial child
At this time, even the simplest queries, which are usually performed in fractions of a second , hung for 6 seconds and over 20 seconds, which was reflected in the slow query log:

# Query_time: 6.889749  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1439063082;
SELECT * FROM currency;

# Query_time: 21.878940  Lock_time: 0.036855 Rows_sent: 0  Rows_examined: 13070
SET timestamp=1439063082;
SELECT DISTINCT pa.text AS value, ad.name AS name, pa.attribute_id AS attribute_id FROM product_attribute pa 
                                    LEFT JOIN product_to_category p2c ON (pa.product_id = p2c.product_id)
                  LEFT JOIN attribute a ON (pa.attribute_id = a.attribute_id)
                  LEFT JOIN attribute_description ad ON (pa.attribute_id = ad.attribute_id)
                  WHERE p2c.category_id = 2078 AND a.attribute_group_id = 9 
                  ORDER BY name;

There are 15-20 such requests, and they all go under the same time
# Time: 150808 23:44:42
. it is likely that for some time the muscle hung, and then the Apache dumped all these requests at once, so one time. This pack is followed by smaller packs, also with an overestimated execution time, mostly for the simplest queries.
Late Saturday evening is not the most visited time, so the issue of load is hardly worth it. Fail2ban banned several addresses at this time, but they are not in the Apache logs during this time.
No resource-intensive scripts were launched, the cron dumped the database only an hour after the incident. If for someone a familiar picture, I'm waiting for options. Thank you.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question