3 ms·
Agreed on all points. They (MySQL and PostgreSQL) were written and optimized to run on constrained hardware. When you remove those constraints (which is where t
by falcolas 9y ago
Agreed on all points. They (MySQL and PostgreSQL) were written and optimized to run on constrained hardware. When you remove those constraints (which is where the configuration pain comes in), they really are capable of absurd volume.
> running large databases on virtualized hardware like AWS really brings the pain.
The pain, I've found, mostly stems from the fact that it is a VM, and the performance costs associated with that. When you assume that your disk is local, and it's actually a network volume with shared network bandwidth, you'll get kicked right in the assumption. Or you assume that you have a static amount of memory, but the balloon drivers punt you into swap, or that you will get reliable interrupts from the OS...
It's gotten better over the years, but it's still a mess.
Oh, and I've said it before, so I'll say it again. If you're running MySQL in RDS, go in and turn up the 'innodb_log_file_size' setting to a bare minimum of 2GB, 15GB if you have the disk space. Go, do it now!
- bulldoa 9y agoCan you explain why turning up innodb_log_file_size is good?
- ryanworl 9y agohttps://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/ https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb... Not who you're responding to, but this is a good overview.
- falcolas 9y agoA good overview indeed; now imagine the figures on the right are 128mb instead of 2gb, and the attendant costs/times scaled up accordingly.
- falcolas 9y agoIn short, the default is 128MB, which results in block-the-world dirty page flushes when it fills due to write activity. By going to 2G (I frequently recommend 15 when you have a lot of memory), you can buffer a lot more writes during times of peak load, and the idle flushing of dirty pages can generally keep up. The practical effect of changing this value: a 2xlarge RDS host goes from 85% CPU utilization to 10%, with faster response times, for a moderate write load. The downside, and why it's not the default for RDS, is that it eats your RDS storage by 2x the value set.
- user5994461 9y agoIt doesn't help that devs try to run the database on a t2.small instance with the smallest hard drive possible.
- falcolas 9y agoAll the more painful since the innodb buffer pool size is 3/4 of the available memory, which in low memory instances means you're going to put something either into swap or killed by the OOM killer. The OS needs about 2GB of memory to do its thing sanely; if your instance only has 4GB of memory, don't assign 3 (closer to 3.5 with the constant memory costs) of it to the DB.
- anarazel 9y ago> The pain, I've found, mostly stems from the fact that it is a VM, and the performance costs associated with that. When you assume that your disk is local, and it's actually a network volume with shared network bandwidth, you'll get kicked right in the assumption. FWIW, if you either do only relatively large transactions, or you're ok with a small loss window, you're often ok - usually the issue is latency not bandwidth. With postgres you can e.g. set synchronous_commit = off, which'll allow COMMIT's to return without an fsync, instead grouping them together. A lot of disk reads with working set >> memory are still a problem, and a good reason to use local storage. But that obviously complicates the durability story a good deal.