7 ms·
How far can you go with MySQL or MariaDB?
- idlewords 9y agoThe 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 agoAgreed 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.
- aguynamedben 9y agoI 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 agoSome 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 agoHe probably concluded that the database didn't scale because inserts were so slow :-)
- twothamendment 9y agoYes, 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 agoOr, 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 agoTake 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 agoAs 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 agoRelation 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 agoIt'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
- overcast 9y agoFacebook is how far you can go.
- jimbokun 9y agoRight, but that means writing your own storage engine [1] to get to their scale, right? [1] https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/ https://code.facebook.com/posts/190251048047090/myrocks-a-sp...
- tyingq 9y ago"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.
- felixhandte 9y agoMyRocks is intended to be a general purpose storage engine for MySQL. It was not built specifically for the UDB tier.
- tyingq 9y agoRight, 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.
- evanelias 9y agoIt'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.
- jimbokun 9y agoThis 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.
- falcolas 9y agoUnder 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.
- jimbokun 9y agoAn 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.)
- willvarfar 9y agoThe 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/compressing-mysql-databases https://williamedwardscoder.tumblr.com/post/160628887798/com...
- falcolas 9y agoInnoDB 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.
- fweespeech 9y ago> 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.
- deleted 9y ago[deleted]
- willvarfar 9y agoHa! 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.
- fweespeech 9y ago> 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.
- olavgg 9y agoI 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.
- kakoni 9y agoAny possibility to here details about your setup?
- tsuraan 9y agoI'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?
- olavgg 9y agopg_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.html 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 :)
- brody182 9y agonew change are coming to php, php is now faster than node.js https://www.w3c-lab.com/php-7-1-swoole-v1-9-5-vs-node-js-benchmark-test-php7-swoole-beats-node-js/ https://www.w3c-lab.com/php-7-1-swoole-v1-9-5-vs-node-js-ben...
- ahachete 9y agoIn 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 https://www.pgcon.org/2013/schedule/events/595.en.html
- cdevs 9y agoI 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.
- jjirsa 9y agoMissing 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.
- derekperkins 9y agoVitess 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.