[pjc50]

In-memory database on a ramdisk, replicating to an on-disk database. All reads answered from the RAM master, while the replica only has to answer writes.

But I suspect that it's all already in the disk cache. Is the bottleneck reads or writes?

[SwellJoe]

I guess I didn't mention it, but obviously (or at least, obvious to me knowing my system), it is writes that are slow. Reading pages can all come from memory, as the whole database is cached in RAM (I've given MySQL plenty of space to store the whole site in memory). But, writing a new post or page can take up to 30 seconds or so, which is simply absurd.

There is something pathological about our disk subsystem on that particular system, which is another issue, but it has often struck me as annoying that I can't just tell MySQL or MariaDB, "I don't care if you lose a few minutes of data. Just be consistent after a crash."

[acqq]

> writing a new post or page can take up to 30 seconds or so, which is simply absurd.

If it really waits on the disk your problems would be solved by using the SSD. Change your hosting plan. Fitting 1.4GB database isn't expensive. If it remains slow that means you use some very badly programmed solution.

[SwellJoe]

We have dedicated servers in colo. I'm upgrading to new SSDs on a server-by-server basis, as I have the opportunity to do so. This one just hasn't been upgraded yet. That wasn't really the question, though. Whether writes take 30 seconds or .1 seconds, if I don't care, I think there should be an obvious path to make it take a millisecond by letting it just "commit" to RAM and then return.

Which, it seems like there is in the innodb_flush_log_at_trx_commit option, which is awesome, and which I'm trying out, now.

[acqq]

If you try the value 2 please write here if it's better for you. It can be a good compromise: "only an operating system crash or a power outage can erase the last second of transactions." It still doesn't "flush" so I guess it can work really good.