Why is MySQL writing 5 times more data to disk, than is committed to the database?

Question

I have installed MySQL 8.0.25 on top of Ubuntu 20.04, running on the C5.2xlarge instance.

Then I ran a script that fills 10 tables with data. The test took exactly 2 hours, during which is has created 123146.5MB of data:

That means that on average, 17.1MB/s were written to the database. However, atop is reporting something weird: while it shows disk activity around 18-19MB/s, it also shows that the process mysqld wrote 864MB in the 10 second sample - which translates to 86.4MB/s, about 5 times as much as the amount of data actually committed to the database:

Why such disrepancy?

iotop is also typically showing that MySQL is writing 5x:

Same for pidstat:

I also tried to use pt-diskstats from the Percona toolkit, but it didn't show anything...

I also reproduced the issue on RDS. In both cases (EC2 and RDS), the Cloudwatch statistics also show 5x writes...

The database has 10 tables that were filled. 5 of them have this definition:

CREATE TABLE `shark` (
  `height` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  `name` mediumtext,
  `shark_id` bigint NOT NULL,
  `owner_id` bigint DEFAULT NULL,
  PRIMARY KEY (`shark_id`),
  KEY `owner_id` (`owner_id`),
  CONSTRAINT `shark_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `shark_owners` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

another 5 tables have this definition:

CREATE TABLE `shark_owners` (
  `name` mediumtext,
  `owner_id` bigint NOT NULL,
  PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I could understand if the difference was about 2x - data is first written to a transaction log, and then it is committed to the database, but 5x? Is this a normal behavior for MySQL, or is something in my tables triggering this? And why are there so many "cancelled writes" - about 12% ?

Answer 1

• LOAD DATA runs very fast, with minimal I/O
• Bulk INSERT with at least 100 rows per query runs 10 times as fast as single-row Inserts.
• autocommit causes at least one extra I/O after each SQL (for transactional integrity).
• 50 1-line Inserts, then a COMMIT is something of a compromise.
• FOREIGN KEY requires checking the other table.
• If innodb_buffer_pool_size is too small, there will be disk churn.
• owner_id is a "secondary index". It is done in a semi-optimized way, but may involve both reads and writes, depending on a variety of things.
• The tables would be smaller if you could use smaller datatypes. (Eg, BIGINT takes 8 bytes and is usually overkill.) Smaller would lead to less I/O.
• How big is name? What ROW_FORMAT is used? They conspire to lead to more or less "off-record" storage, hence disk I/O.
• Were you using multiple threads when doing the Inserts?

In other words, a lot more details are needed in order to analyze your problem.

Answer 2

MySQL writes data several times when you use InnoDB tables. Mostly this is worth it to prevent data loss or corruption, but if you need greater throughput you may need to reduce the durability.

If you don't need durability at all, another solution is to use the MEMORY storage engine. That would eliminate all writes except the binary log and query logs.

You already mentioned the InnoDB redo log (aka transaction log). This cannot be disabled, but you can reduce the number of file sync operations. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit for details.

innodb_flush_log_at_trx_commit = 0

You might reduce the number of page flushes, or help MySQL consolidate page flushes, by increasing RAM allocation to the InnoDB buffer pool. Do not overallocate this, because other processes needs RAM too.

innodb_buffer_pool_size = XXX

The binary log is a record of all committed changes. You can reduce the number of file syncs. See https://www.percona.com/blog/2018/05/04/how-binary-logs-and-filesystems-affect-mysql-performance/ for description of how this impacts performance.

sync_binlog = 0

You can also disable the binary log completely, if you don't care about replication or point-in-time recovery. Turn off the binary log by commenting out the directive:

# log_bin

Or in MySQL 8.0, they finally have a directive to explicitly disable it:

skip_log_bin

Or

disable_log_bin

See https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_log-bin for details.

The doublewrite buffer is used to protect against database corruption if your MySQL Server crashes during a page write. Think twice before disabling this, but it can give you some performance boost if you disable it. See https://www.percona.com/blog/2006/08/04/innodb-double-write/ for discussion.

innodb_doublewrite = 0

MySQL also has two query logs: the general query log and the slow query log. Either of these causes some overhead, so disable the query logs if you need top performance. https://www.percona.com/blog/2009/02/10/impact-of-logging-on-mysql’s-performance/

There are ways to keep the slow query log enabled but only if queries take longer than N seconds. This reduces the overhead, but still allows you to keep a log of the slowest queries you may want to know about.

long_query_time = 10

Another strategy is to forget about optimizing the number of writes, and just let them happen. But use faster storage. In an AWS environment, this means using instance storage instead of EBS storage. This comes with the risk that the whole database may be lost if the instance is terminated, so you should maintain good backups or replicas.