Finding reason behind high write IOPS on MySQL (RDS) server

Question

I am having performance issues with an AWS RDS instance (running MySQL 5.6).

This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able to handle at least an order of magnitude more traffic than it currently does. It's very rare that the db is the bottleneck. Usually the Tigase server becomes a bottleneck before the db does.

When looking at our monitoring data in the AWS Management Console we see that it's mainly CPU and Write IOPS that is the issue. Read IOPS is negligible.

Since I haven't written Tigase it's hard for me to know the reason behind the high load. My question is therefore: how should I go about to find the queries and/or config settings that are the root cause behind this? What tools do you recommend that I use? I have fairly extensive DBA experience but I am a newbie when it comes to MySQL.

Below is a screenshot from MySQL Workbench's performance dashboard.

Thanks!

Answer 1

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?".

There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully RDS-compatible). Here you have a video with an introduction to query profiling, with examples like IOPS and temporary table creation monitoring by query pattern, user and table. For a more specific guide (specially for configuration of metrics), you can have a look at the official manual and the sys schema documentation.

For a quick check you can also have a look at your SHOW GLOBAL STATUS like 'com\_%'; and SHOW GLOBAL STATUS like 'Hand%'; at time interval to see if you have an increase on the number of SQL queries per unit of time or on the number of engine row operations per unit of time (the first one does not seem abnormal according to the graphs shown).

Your goal is to detect the offending queries first so that you can modify the SQL, your data structure, the MySQL configuration and/or anything peripheral (like if your RDS instance is still good enough) accordingly. An increase on Write IOPS normally may mean extra SQL load (obviously), but also many other things, such as: too many temporary tables or worse query plans being executed due to a change on the query optimiser plan or on your data cardinality/size. It is critical to identify the underlying cause first before taking any action.

Answer 2

You should change two most important parameters which decide the write IOPS.

1)-innodb_flush_log_at_trx_commit(0 or 2)[Default value is 1 which is relatively slow]

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance. It doesn't create more work.

2)-sync_binlog(0)[Default value is 1]

Running a server with binary logging enabled makes performance slightly slower.Setting sync_binlog to 0 lets the OS flush commit groups to disk (on its own time).