3

We have a 16GB RDS-MySQL Multi-AZ instance. Of late, it has unexpectedly failed over multiple times causing the dependent application to be offline for over 15 minutes.

AWS-Support blamed it on high Swap Usage. However, the instance has failed when the Swap Usage was as low as 10 MB. We have started optimizing the queries.

Is there anything else that we can do to avoid such mishap?

===================================================================

EDIT :

We have made one observation. The CPU utilization of the instance remains fairly low. So, we presumed that the issue might be with disk access. The write-throughput is several times that of read-throughput. To add to it, Sort_merge_passes has a value of 79425(which is crazy, right ?). On going through the slow query log, it was found that there are multiple queries with ORDER BY clause not using indexes. It will take time before workarounds are found for all these queries. Can you suggest some immediate fix to overcome the failover issue ? Should we increase the global value of sort_buffer_size ? Current value is 2MB.

===========================================================

EDIT 2 :

Below is a screenshot of the rate of change of Sort_Merge_Passes(Queries per second) as seen on RDS-Performance Insights.

enter image description here

As can be seen, during periods of load,the value varies from 0.5 to 1(we have observed values higher than 1 as well). A query can cause multiple Sort_merge_passes, 8 being the highest that we have observed.

=======================================================================

EDIT 3 :

We also found that the global status Created_tmp_disk_tables is increasing at the rate of approx 2-3/second. Badly structured queries I guess.

Improve this question
7
  • 1
    Is there anything unusual (in the metrics or elsewhere) that you observe in the period leading up to the failover?
    – Alok Patwal
    Jul 2, 2019 at 14:03
  • @AlokPatwal No, we have not being able to find any repeating patterns in the metrics.
    – Subrata Das
    Jul 3, 2019 at 6:14
  • 2
    Sort_merge_passes - divide by Uptime. 1/second would be "high", but not high enough to be concerned with.
    – Rick James
    Jul 12, 2019 at 4:47
  • @RickJames The load on our server is not uniform. It varies throughout the day. So, Sort_merge_passes - divide by Uptime might not be truely reflective of the state of the database. I have added one more edit showing the change of Sort_merge_passes for the last 24 hours.
    – Subrata Das
    Jul 12, 2019 at 8:11
  • @SubrataDas - If you can spot a particular query that is doing a lot of "sort merge passes", we can discuss it. Please provide EXPLAIN SELECT ... and SHOW CREATE TABLE. "8" slows down the one query, but seems to be infrequent enough not to slow down the "entire system", much less cause a failover.
    – Rick James
    Jul 12, 2019 at 14:11

1 Answer 1

Reset to default
3
  • Swapping is a bad symptom of over-use of RAM.
  • An excessive number of simultaneous connections may lead to swapping.
  • Very complex queries may lead to swapping (due to temp tables). Let's see such queries if you have any.
  • Have you changed any things in my.cnf (or the equivalent in RDS)? If so let's see if you raised some values too much.

  • As a quick fix (and if RDS lets you), decrase innodb_buffer_pool_size by a gigabyte.

  • Sort_merge_passes - divide by Uptime. 1/second would be "high", but not high enough to be concerned with.

  • For deeper analysis, see http://mysql.rjweb.org/doc.php/mysql_analysis -- the GLOBAL STATUS and VARIABLES may find some clues; pt-query-digest will focus on the 'worst' queries.
  • sort_buffer_size = 2M is fine.
  • "ORDER BY clause not using indexes" - show us the queries, plus SHOW CREATE TABLE.
Improve this answer
9
  • A few times, the instance failed over even when the swap usage was low. What are the other possible reasons for MySQL to fail over?
    – Subrata Das
    Jul 3, 2019 at 6:08
  • We also have a large table with FULL-TEXT index. It is queried quite frequently.
    – Subrata Das
    Jul 3, 2019 at 6:12
  • 1
    @SubrataDas - RDS has monitoring, yes? What does it say just before a crash?
    – Rick James
    Jul 3, 2019 at 14:47
  • Yes, RDS has monitoring. The instance becomes unresponsive at the time of failover. There have been times where we have found ReadIOPS to go up just before the failover but no repeating patterns. Can you specify any particular metric?
    – Subrata Das
    Jul 4, 2019 at 7:13
  • 1
    @SubrataDas - Alas, the failovers should not have happened, so I don't know what to look for. Plan A: Present all the graphs during the hour (or so) before a failover. Plan B: grab the VARIABLES and GLOBAL STATUS after it has been up for a day and hope that some clue is building up long before the crash: mysql.rjweb.org/doc.php/mysql_analysis#tuning
    – Rick James
    Jul 4, 2019 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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