Score:0

Server

How to reduce MySQL/InnoDB disk writes

Antonis Christofides

9/30/23, 1:11 PM

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.)

0 + 0

mysql

innodb

nextcloud

Wilson Hauck

9/30/23, 3:22 PM

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 pastebin.com 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

9/30/23, 3:31 PM

Which Nextcloud Partner are you hosted by?

Rick James

9/30/23, 4:42 PM

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

9/30/23, 5:31 PM

I would suggest that you take the question to dba imho

Score:1

Server

Antonis Christofides

10/3/23, 10:56 AM

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.

0 + 0

Wilson Hauck

10/3/23, 8:08 PM

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..

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How to reduce MySQL/InnoDB disk writes

TH: วิธีลดการเขียนดิสก์ MySQL/InnoDB

RO: Cum să reduceți scrierile pe disc MySQL/InnoDB

RU: Как уменьшить количество операций записи на диск MySQL/InnoDB

VI: Cách giảm ghi đĩa MySQL/InnoDB

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.