4 ms·

The limits of properly-tuned MySQL on appropriate hardware are impressive. Note that the size constraint in the examples given here is almost always administrat

The limits of properly-tuned MySQL on appropriate hardware are impressive. Note that the size constraint in the examples given here is almost always administrative (ease of doing backups, replication, archiving) rather than hitting insurmountable performance limits. I have a theory that a lot of people who are dismissive of modern relational databases for 'big data' simply never took the time to learn to tune them properly as the data sets grew in size. It's a painful process, and there is an intimidating number of switches and knobs, some of them in the filesystem and kernel. In particular, running large databases on virtualized hardware like AWS really brings the pain.

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 ago
  Can you explain why turning up innodb_log_file_size is good?
  - ryanworl 9y ago
    https://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 ago
      A 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 ago
    In 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.
    - bulldoa 9y ago
      ah that makes sense, is there a innodb_log_file_size equivalent in postgres btw?
      - anarazel 9y ago
        Yes. Before 9.5 it was named checkpoint_segments, now it's called min_wal_size (reserved, won't go below) and max_wal_size (soft max space limit, will checkpoint more frequently).
- user5994461 9y ago
  It doesn't help that devs try to run the database on a t2.small instance with the smallest hard drive possible.
  - falcolas 9y ago
    All 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.
aguynamedben 9y ago
I agree. The number of engineers who don't understand simple indexing in RDBMSes is tragic. Too many engineers see a slow query and immediately say "this database can't scale, let's switch databases!"
- partisan 9y ago
  Some go to the other extreme and add indexes on all columns without consideration for what is actually causing the performance issue. I had a former coworker who would routinely put an index on every column in a table when creating the table.
  - maxxxxx 9y ago
    He probably concluded that the database didn't scale because inserts were so slow :-)
  - twothamendment 9y ago
    Yes, like always there is a balance. I start off with an index on things I know we'll use and sometimes throw one a column we are likely to use. Other times it isn't until a slow query comes up that an index change is needed. On very rare occasions I've had to hint to mysql to use an index it thought wasn't great but ended up being 10 times faster.
- mrweasel 9y ago
  "switch database" or "buy more hardware". For the large majority of developers, then database is never the bottleneck. The issue arise when you only to late in the development process realise that your design is the root cause of your problems.
  - tracker1 9y ago
    Or, more likely you inherited someone else's problem. Sometimes you inherit a project that's 5+ years in, and the short term solution really is, buy bigger/faster hardware. Of course some DBs are better in some use cases, and price is a factor. As is ease of replication, sharding, etc.
tkyjonathan 9y ago
Take it from me (MySQL performance engineer).. if you are on AWS, the secret sauce is very tight data modelling. AWS is very unforgiving if you use it badly - on physical, you could have gotten away with a lot more. Some config tweaks will take you a bit further and smoothen those peaks, but data modelling(includes indexing, partitioning) is where you will get the most benefit.
inetknght 9y ago
As someone who works with big data in C++ all the time... trying to convince people that the performance problems they have are because of poor design decisions is pretty difficult.
eikenberry 9y ago
Relation databases are the jack-of-all-trades databases requiring tons of "switches and knobs" to adjust their behavior for the specific task. This is why we have DBAs who specialize in this sort of thing. Many of the no-SQL databases are very task specific and, as such, don't have this problem. Probably one of the reasons for their popularity. Learning which no-SQL database is best for each task tends to be easier than learning how to tweak relation DBs for each task. I think part of the reason that relation DBs are coming back a bit is that this communication/documentation issue is starting to be addressed.
jjirsa 9y ago
It's not about tuning - I made a damn good living (talking $1k/hour with 8 hour minimums) as a MySQL guy before I touched nosql. It's about scale, it's about failover, it's about active active active multi DC Masterless HA. All of these things you can build around MySQL, but you end up reinventing something that looks a lot like Cassandra. If you're FB/YouTube, you write that tooling, because it extends your existing investment in MySQL Everyone else needing to scale to multipetabyte, millions of ops per second, always online scale is better off using Cassandra

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 ago
Can you explain why turning up innodb_log_file_size is good?
- ryanworl 9y ago
  https://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 ago
    A 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 ago
  In 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.
  - bulldoa 9y ago
    ah that makes sense, is there a innodb_log_file_size equivalent in postgres btw?
    - anarazel 9y ago
      Yes. Before 9.5 it was named checkpoint_segments, now it's called min_wal_size (reserved, won't go below) and max_wal_size (soft max space limit, will checkpoint more frequently).
user5994461 9y ago
It doesn't help that devs try to run the database on a t2.small instance with the smallest hard drive possible.
- falcolas 9y ago
  All 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.

aguynamedben 9y ago

I agree. The number of engineers who don't understand simple indexing in RDBMSes is tragic. Too many engineers see a slow query and immediately say "this database can't scale, let's switch databases!"

partisan 9y ago
Some go to the other extreme and add indexes on all columns without consideration for what is actually causing the performance issue. I had a former coworker who would routinely put an index on every column in a table when creating the table.
- maxxxxx 9y ago
  He probably concluded that the database didn't scale because inserts were so slow :-)
- twothamendment 9y ago
  Yes, like always there is a balance. I start off with an index on things I know we'll use and sometimes throw one a column we are likely to use. Other times it isn't until a slow query comes up that an index change is needed. On very rare occasions I've had to hint to mysql to use an index it thought wasn't great but ended up being 10 times faster.
mrweasel 9y ago
"switch database" or "buy more hardware". For the large majority of developers, then database is never the bottleneck. The issue arise when you only to late in the development process realise that your design is the root cause of your problems.
- tracker1 9y ago
  Or, more likely you inherited someone else's problem. Sometimes you inherit a project that's 5+ years in, and the short term solution really is, buy bigger/faster hardware. Of course some DBs are better in some use cases, and price is a factor. As is ease of replication, sharding, etc.

tkyjonathan 9y ago

Take it from me (MySQL performance engineer).. if you are on AWS, the secret sauce is very tight data modelling. AWS is very unforgiving if you use it badly - on physical, you could have gotten away with a lot more. Some config tweaks will take you a bit further and smoothen those peaks, but data modelling(includes indexing, partitioning) is where you will get the most benefit.

inetknght 9y ago

As someone who works with big data in C++ all the time... trying to convince people that the performance problems they have are because of poor design decisions is pretty difficult.

eikenberry 9y ago

Relation databases are the jack-of-all-trades databases requiring tons of "switches and knobs" to adjust their behavior for the specific task. This is why we have DBAs who specialize in this sort of thing. Many of the no-SQL databases are very task specific and, as such, don't have this problem. Probably one of the reasons for their popularity. Learning which no-SQL database is best for each task tends to be easier than learning how to tweak relation DBs for each task. I think part of the reason that relation DBs are coming back a bit is that this communication/documentation issue is starting to be addressed.

jjirsa 9y ago

It's not about tuning - I made a damn good living (talking $1k/hour with 8 hour minimums) as a MySQL guy before I touched nosql. It's about scale, it's about failover, it's about active active active multi DC Masterless HA. All of these things you can build around MySQL, but you end up reinventing something that looks a lot like Cassandra. If you're FB/YouTube, you write that tooling, because it extends your existing investment in MySQL Everyone else needing to scale to multipetabyte, millions of ops per second, always online scale is better off using Cassandra