How to reduce MySQL/InnoDB disk writes

gb flag

I'm stress testing an application that uses MySQL. The bottleneck is the MySQL disks (separate for main data and redo log), which are written to quite much. atop indicates their usage is near 100%. If I disable the redo logs (ALTER INSTANCE DISABLE INNODB REDO_LOG), then the disk usage becomes negligible (2% for the main disk and zero—naturally—for the redo log disk).

I need to reduce disk usage (it doesn't need to be negligible, but it has to be considerably less) without disabling the redo logs. It's OK to lose several seconds (even minutes) of data in case of a crash. I've tried this:

innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_timeout = 5

but I don't see any difference.

Is there anything else I can do?

This is MySQL 8.0.29 on Ubuntu 20.04.

(Note: the application is Nextcloud and apparently it makes many database write requests.)

Wilson Hauck avatar
jp flag
There are more GV to change to reduce write frequency. Additional information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; for server workload tuning analysis to provide suggestions.
Wilson Hauck avatar
jp flag
Which Nextcloud Partner are you hosted by?
ua flag
We can explain disk usage for particular queries. Also, if the "stress tests" don't reflect your application, conclusions from them may be misleading.
djdomi avatar
za flag
I would suggest that you take the question to dba imho
gb flag

I figured out that this was more a Nextcloud issue than a MySQL issue.

By examining the Innodb_os_log_written part of SHOW GLOBAL STATUS's output, I verified that it was writing 1M/s or more to the redo logs. Apparently it was the sheer amount of data that was causing the problem on my slow disk rather than the details of when and how it was being synced.

By setting general_log='ON' and examining the queries, I figured out, in the end, that it was a Nextcloud bug, where logging in causes too many queries. After the workaround of truncating the oc_authtoken table, the rate of writing to the redo logs fell to less than a tenth of what it was.

Wilson Hauck avatar
jp flag
Great, discovery and corrective action. Next step would be to provide additional information requested May 31, 2022 at 15:22 to allow workload analysis and some suggestions to reduce read/write activity will be posted as an Answer..

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.