How far can you go with MySQL or MariaDB? | Hacker News Reader

How far can you go with MySQL or MariaDB?

jfg-mysql.blogspot.com

80 pointsCharlesW53 comments

The elephant in the room is that there is no mention of TokuDB here.

My personal experience is that InnoDB performance drops off a cliff as tables grow and parts of tables stop fitting in RAM. TokuDB just keeps on going.

TokuDB also compresses your data really well.

My own stats: https://williamedwardscoder.tumblr.com/post/160628887798/com...

InnoDB performance drops off if you don't have hotspots in your data access, and is still really good otherwise.

This doesn't take away from TokuDB though - which is pretty awesome in any case.

> My personal experience is that InnoDB performance drops off a cliff as tables grow and parts of tables stop fitting in RAM. TokuDB just keeps on going.

Yes but at least in my experience as things fit in RAM, InnoDB is still the correct choice.

TokuDB is great once you run out of RAM-related solutions but given the price point RAM is at these days, we are talking 100GB+ tables which probably shouldn't be one a single machine anyway.

Ha! A few gb ram and a few tb disk is real cheap in the cloud.

If your dataset fits in ram, and when it doesn't you have to buy more boxes with more ram rather than just buying more disk, then I think you are making a very uneconomical trade off.

> Ha! A few gb ram and a few tb disk is real cheap in the cloud.

> If your dataset fits in ram, and when it doesn't you have to buy more boxes with more ram rather than just buying more disk, then I think you are making a very uneconomical trade off.

It is all in what lower latency is worth to the bottom line and how well you scale horizontally.

My link described performance too. A cluster of innodb boxes is going to be slower than a single tokudb box.

(Src: have run each, and stuff like shard-query to parallelise queries and scale them out)

Why wouldn't 100GB tables be in a single machine? It's not that much data. I'm comfortably running a 3TB table on PostgreSQL, which is growing at a rate of about 75GB a month.

The machine isn't even that beefy, 128 GB of RAM, dual mid range Xeons, and prosumer SSDs in RAID 10. I would rather spend money in PCI-E SSDs, 300+ GB of RAM, and high end CPUs if it meant I didn't have to implement sharding at an application level.

we are talking 100GB+ tables which probably shouldn't be one a single machine anyway.

You can comfortably handle multi-tera tables on single machines these days. Adding more machines isn't "scaling", its admitting "we can't scale in software so make it a hardware problem"

TokuDB doesn't come enabled in the MariaDB package that's shipped with most distributions. I wanted to try it out on openSuSE 42.2 but it wasn't there. It ships with MariaDB 10.0.31 I think.

I think we are ultimately going to switch to PostgresSQL as it has better tooling and features that we need (json, jsonb, postgis) and performance is also good when properly tuned.

I manage a multi-TB system on PostgreSQL. There are no performance issues other than making sure you have good indexes and make sure the queries use those indexes.

Backup isn't that bad either as this run on ZFS that support blazing fast atomic snapshots of all related filesystems like pg_xlog and tablespaces.

Any possibility to here details about your setup?

I'd love to hear more about your tuning around both PG and ZFS. I assume pg_xlog is on its own fs with 8K records, but beyond that, anything interesting, or any guides that you found especially useful?

pg_xlog has 8k records with lz4 compression. You can disable full-page writes, though I've never had any issues with it on either.

For tablespaces the recordsize can be anything from 8k to dynamic / 128k. They all have pro/cons. Greater record size improve compression and sequential read performance but small random updates becomes a lot more expensive. I feel a recordsize of 16k or 32k with lz4 compression is a safe setting for optimal performance with good compression.

Other useful topics related to performance are: Use multi-column/functional/partial indexes, as this will help you design a database where most of your 'hot' indexes can fit in RAM. If you have too many indexes, you will do index scan on disk, which is painfully slow. I many cases a full table scan (sequential read) would be faster instead.

BRIN indexes are great for large tables that can be sorted on for example on timestamp.

Common Table Expressions, for recursive queries.

The query planner sometimes needs help as it will start doing full table scan on a table when it could just have used an index. This is related to table statistics which you need to alter. You can read more about this here: https://www.postgresql.org/docs/9.5/static/planner-stats.htm...

Adjusting the seq_page_cost and random_page_cost can also help the query plannner.

There is probably a lot more, but you learn as you go :)

Unrelated, but the link in your profile to your site Boost AI is HTTPs but the site itself only seems to support HTTP.

Thanks!

No ZFS-specific tuning?

There is no need, default settings works great. We run PostgreSQL on FreeBSD though.

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.

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!

Can you explain why turning up innodb_log_file_size is good?

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.

A good overview indeed; now imagine the figures on the right are 128mb instead of 2gb, and the attendant costs/times scaled up accordingly.

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.

ah that makes sense, is there a innodb_log_file_size equivalent in postgres btw?

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

It doesn't help that devs try to run the database on a t2.small instance with the smallest hard drive possible.

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.

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

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!"

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.

He probably concluded that the database didn't scale because inserts were so slow :-)

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.

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

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.

This is a great resource RE indexing: http://use-the-index-luke.com/

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.

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.

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.

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

In 2013 I co-founded an experiment with a similar goal: test the maximum number of tables (empty tables, btw) that you could create on a PostgreSQL database.

The answer lies in the "Billion Tables Project" :) https://www.pgcon.org/2013/schedule/events/595.en.html

Missing from the article:

- HA

- How to recover some subset of 37TB of data without restoring all of it

- What happens when your write throughout needs to exceed the capacity of a single disk

Nobody gives up features like joins in favor of a distributed shared nothing DB because they feel like they need more space, they do it for HA, more cores, more spindles, fault tolerance and isolation.

I think some of the advice here is good for high traffic sites but my company only sees about 1400 people a day use our data although we do processing of information for each client in millions of jobs a day. In our sceneraio we have one MySQL instance on aws aurora, about 50 tables and 3 terabytes of info total. Most of the size comes from about 10 tables and are mostly used for joins after data is quickly searched on elastic search. I will put down the myisam search we use to use before elasticsearch which was drowning as we reached 100M to 200M rows when things weren't pushed over to their own machine and could hog up ram. When fulltext is planned from the start along with backups and other things it can save a ton of headaches.

Facebook is how far you can go.

Right, but that means writing your own storage engine [1] to get to their scale, right?

[1] https://code.facebook.com/posts/190251048047090/myrocks-a-sp...

"While there are some databases at Facebook that will still use InnoDB, we're in the process of migrating to MyRocks on our user database (UDB) tier"

So, still impressive to me. That custom storage engine is just for the "user database" tier. Given their user count, that doesn't seem unreasonable.

MyRocks is intended to be a general purpose storage engine for MySQL. It was not built specifically for the UDB tier.

Right, but "some databases at Facebook that will still use InnoDB" was a direct quote.

The context being that InnoDB got FB pretty far, and that UDB appeared to be the first area where any pain was notable enough to need something else.

It's not really a question of InnoDB "pain" though; Facebook's primary motivation for MyRocks is substantially better compression rate than InnoDB. It adds up to very compelling cost savings at FB scale.

This comparison is a bit apples to oranges anyway, with respect to the original article :) JFG's post was talking about extreme cases within a single MySQL instance, whereas Facebook is extremely heavily sharded.

"pain" is just shorthand for whatever drove the switch.

Was just trying to reinforce the point that MySQL gets you pretty far.

Vitess deserves more love here on HN. It's incredible the way it gives you the best of both worlds (RDBMS and NoSQL). Most operations end up in a single shard, so you have no performance impact and still have the benefit of built in MySQL joins and relational integrity. It is built to be container-native, so the recommended usage is to split into 100-300 GB chunks, which simultaneously makes schema changes take 1/(n shards) of the time for a normal ALTER TABLE.

This is very impressive, but at some point it becomes more cost effective to rack up more reasonably sized commodity hardware with a "big data" database, no?

(Using "big data" to mean sacrificing low latency joins and full transaction support, with the caveats that many "big data" systems are making progress at getting those things to work.)

Of course you can shard, but there is a significant maintenance cost to that as well, and requires your data model can be easily sharded.

Under 1 TB is still commodity hardware with many of these DBMS. 5-10 TB is commodity hardware plus a few extra drives.

Sharding, or splitting out into "big data" databases has its own cost, a cost which is frequently greater than the $20k worth of hardware.

An example in the article was over 37TB, and another was 205TB containing "hundreds of thousands of uncompressed InnoDB tables".

Maybe that's a system that could have been better served by something like Cassandra? (Depending, of course, on access patterns, complexity of queries, etc. etc.)

Reply on news.ycombinator.com