MySQL Performance Blog

I'm not familiar with scaling Java, but I've worked with the language itself on Android. What makes it so great for scalability as opposed to something like Ruby? If you are using Rails, the database is the problem 99% of the time, not Ruby. MySQL can easily* be scaled / denormalised / sharded then if you need to you can just horizontally scale your app servers. (*As easy as MySQL can. It isn't a black magic though, there is plenty of documentation, e.g. http://www.mysqlperformanceblog.com/)

The authors of the book also have a blog: http://www.mysqlperformanceblog.com/ it has lots of good reads

It's a pretty crappy comparison. If you need transactions and ACID properties then you pick InnoDB and not MyISAM. Now what are the cons of PostgreSQL? 1) It's hard to install and manage, which could explain why Postgre's adoption is fairly low. 2) MySQL is used and proven to scale on very large installments (Yahoo, Facebook, Google - just to name a few). Postgre's usage is much lower (Skype and Reddit from those I know of) 3) Due to the adoption of MySQL it's very easy to get help and to buy help. For example: http://mysqlperformanceblog.com offers great advice on everything MySQL. Due to Postgre's smaller user base it's harder to get or buy help. 4) The development of "MySQL and MySQL tools" is very rapid. Check out Drizzle, Xtradb, xtrabackup, innodb plugin, rethinkdb.com (this is just a few, there are tons of other engines and tools under development). It's unclear to me how rapid Postgre's development is (for example, they still lack a decent replication), but I doubt Postgre's development is as rapid as MySQL's. 5) The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I.e. Postgres has a lot of non-essential features, while it lacks some essential features (such as decent replication). The bottom line is that MySQL is proven, easy to use and install, is under heavy development, is backed up by billion dollar companies and has a very large user base. MySQL is also built…

I recently replaced the SAS drives in all of my database servers with SSDs. Watching IO wait on my usage graphs drop while db latency fell to near-0ms was such an incredibly gratifying feeling. Worth every penny. If you've got heavy random IO on your dataset (90% active, OLTP here) and/or deal with any sort of significant contention or deadlocking across your disks, I would strongly encourage upgrading to SSDs. Of course, if you can fit your dataset into RAM, go that route. But if you're well past that point, might be time to consider SSDs.[1] [1] http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory…

From what I've read, NEVER mix InnoDB and MyISAM tables in the same database. Update: While they don't say never, they don't recommend it. Managing backups gets a lot harder. Also, you should never mix innodb and myisam tables within a transaction, which is probably what I was attaching never to. http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-my…

So, I'm an application developer, for the most part. I write stuff and I don't know a whole lot about databases other than how to create some tables/collections, do some basic indexing, and read the slow query log. Even running the explain commands, I have to spend half a day reading the docs and then I've forgotten everything after a few weeks. I want to know: Can someone tell me how/where to focus my efforts to learn more? In my day job, I use mostly MongoDB these days, but also some MySQL. I have a few resources that I'm aware of: - http://aphyr.com/tags/Jepsen - Have browsed this and found it interesting. - MySQL reference manual http://dev.mysql.com/doc/#manual - MongoDB Manual docs.mongodb.org/manual/ - High Performance MySQL - I purchased this book but never got past the first chapter. Not to say that I can't... - use-the-index-luke.com/ - Rarely read this, but have given it a half-hearted start from time to time. - http://www.mysqlperformanceblog.com/ - ditto I don't know when to use a relational database and when to use NoSQL. I don't know the differences between the various NoSQL databases. I don't know the ins-and-outs of any of the systems and I don't know how to determine what path to go for high availability, geo-distribution, replication, etc. I find it all very overwhelming and would like to learn more. I'd like to be able to pull plugs in things and see my systems stay up (which, I'm sure would require intimate knowledge of at least one system).…

i dont have much experience with NoSQL, but the typical use is for document storage where structures can have varying levels of depth and differing schemas or different schema versions. personally, i think a lot of the NoSQL benefits are hype for most use-cases. relational databases will get you very far when used correctly. a good method of learning is to use some really large dataset and come up with some questions you want answered about the set, then write sql to get that information out. if your queries take a long time, begin indexing and looking at how the queries can be rewritten to return results faster. i cannot recommend a good, large interconnected/relational data source, but something like zipcode databases [1] can get you started. you can also play with geo-spatial indexing to find zipcodes within x miles of each other. [2] [1] http://download.geonames.org/export/zip/ [2] http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-f…

Thanks for commenting! I think DB support is great. I'm especially jealous of postgres, which like you mention, has native UUID types. UUID-OSSP is especially great: http://www.postgresql.org/docs/devel/static/uuid-ossp.html Postgres handles all the gunk for you in terms of efficient storage and display, AND you can cluster by a different field than the UUID one. For those of us not unfortunate enough to be using a DB that has such niceties, things are different :) As for performance I think this will always depend on your what your queries look like. For random reads or for reads when your dataset is not larger than RAM, you won't see any degradation because of caching. But if you need to make tons of range queries on a dataset that doesn't fit in RAM (or even worse, the index doesn't fit in RAM), things can get a bit crazy. I wish I had time to also do a benchmark supporting this, but I've hit this issue before. Other people also seem to have hit it here: http://stackoverflow.com/questions/2365132/uuid-performance-in-mysql (comments and answers) or here: https://news.ycombinator.com/item?id=5310662 or here: http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ So, it really depends on your use case, I guess. Hope this helps!

I love my consumer SSD backed database, but don't get visions of 380,000 IOPS on a real workload quite yet. Like any radical performance increase on just one component it's more likely to just reveal a non-disk latency bottleneck somewhere else in your system. Be aware that the performance characteristics of flash are very unlike spinning disks, and vary widely between models. You will see things like weird stalls, wide latency variance, and write performance being all over the place during sustained operations and depending on disk fullness. I chose Intel 520s because they performed better on MySqlPerformanceBlog benchmarks than the then-current Samsung offering [1] and because of OCZ's awful rep [2]. I hit about 5K write IOPS spread across two SSDs before my load becomes CPU-bound, which is nowhere near benchmark numbers but pretty sweet for a sub-$1k disk investment. It's also my understanding that non-server flash drives like recommended by the article do not obey fsync and are suspect from a ACID standpoint. RAID mirroring does not fix this--if integrity across sudden power loss is critical you might not be able to use these at all and will have to find a more expensive server SSD. [1] http://www.mysqlperformanceblog.com/2012/04/25/testing-samsung-ssd-sat… [2] http://www.behardware.com/articles/881-7/components-returns-rates-7.ht… p.s. the RAM benefits the article mentions are real and potentially huge. My query and insert…

Percona Server has lots of improvements (some small things and others that are pretty essential for me) On the SSD front, the main ones are: * You can put the innodb doublewrite buffer on your spinning disks - that's the best place for it since it's heavy sequential writes. See section 2.2.2 here http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlo… * The ability to tune various parts of innodb that were originally designed to perform well on regular disks - those with fast sequential access, slower random access, and no real wear considerations. See http://www.percona.com/doc/percona-server/5.1/scalability/innodb_io.ht… Here's a recent post from Percona showing MySQL vs Percona server on an Intel 320 SSD: http://www.mysqlperformanceblog.com/2012/02/23/percona-server-vs-mysql… -- MariaDB is pretty nice too. Their team has introduced some sorely needed query optimizations (http://kb.askmonty.org/en/what-is-mariadb-53/#subquery-optimizations) and it does include Percona's XtraDB. I haven't looked at MariaDB 5.5 to see how it compares to Percona Server 5.5. I put Percona Server into production last fall and MariaDB was still at 5.3 at the time.

I use the cheapest and the best tool available: EXPLAIN EXTENDED You cannot do any better than have the DB tell you exactly what it is going to do with your query. Then you can experiment with changing / adding / removing clauses and see how it would affect the query plan produced by EXPLAIN. For example if EXPLAIN says the query would generate a temp table you could often achieve improvement by managing the same temp tables explicitly. Many times you can get a huge performance lift by using a "group by index". You could identify and rewrite un-indexed table scans too. I've found lots of other "unobvious" optimizations that cut down queries that ran for days or hours to minutes or seconds. Here are a few references to get started- 1) http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html 2) http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/ 3) http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-te…

> this option would probably beat most of the simple key-value stores in terms of performance, backup and replication. I wonder why do you think so? Here's a sample test of plain and memcached mysql vs tokyo tyrant (http://www.mysqlperformanceblog.com/2009/10/19/mysql_memcached_tyrant_… It was done without the additional inserts for the index table... I don't see how it can be anywhere close to the TT's performance for example. While mysql's replication is very easy, TT's replication is trivial. Backup is probably comparable - just flush and snapshot... Unless you're comparing it to mysql+MC. Then yes - mysql's performance at 99.8% hits to MC can be ignored. We're left comparing MC with something else. Things might change dramatically if you have a write-heavy application. Or when you have more data than ram. Author's benchmarks will always show great results - let's wait for some real-world data.

You mean InnoDB's gap locks? http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/ http://pooteeweet.org/blog/745 If you actually have that problem in production, it is because you are spreading the writes out to all of your Galera servers and simultaneously doing conflicting updates/transactions. I'm uncertain what exactly you expect any database to do in this situation? Silently discard one?

Given all of the odd default behavior and caveats in InnoDB FTS[1], I'd sooner just continue to use an external search solution, like ElasticSearch. 1: http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-text-search…

As with regular drives, it depends on the device. Early Intel SSDs were known to be particularly prone to this issue: http://www.mysqlperformanceblog.com/2009/03/02/ssd-xfs-lvm-fsync-write… http://www.evanjones.ca/intel-ssd-durability.html

Actually that's what I thought, but it's not always the case. Sometimes a good chunk of the TEXT type column is saved in the row itself (~768 characters), and when the length exceeds that, the remainder is stored in the blob area of the table store. (http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitat… This depends on your row format, an obscure technical issue most never have to deal with, but it can be a problem.

Erm, APC can be used as a memory store, too. http://www.mysqlperformanceblog.com/2006/09/27/apc-or-memcached/

XtraDB is not only the default in Percona, they originally wrote it (forked from InnoDB). http://www.mysqlperformanceblog.com/2008/12/16/announcing-percona-xtra…

jemalloc is the new hotness for MySQL. We are using it at Facebook (and I know percona/oracle use it for benchmarks and testing as well). Good benchmark showing the impact of the different options: http://www.mysqlperformanceblog.com/2012/07/05/impact-of-memory-alloca…

jemalloc is another strong option - http://www.mysqlperformanceblog.com/2012/07/05/impact-of-memory-alloca… http://www.quora.com/Is-tcmalloc-stable-enough-for-production-use

Note that unless your mysql server has more than 4 (physical) cores, you aren't going to see any performance improvements from any variation/fork of mysql. 8 cores is where things start to diverge under load. This website has some benchmarks to prove it: http://vbtechsupport.com/606/ http://vbtechsupport.com/657/2/ Also http://www.mysqlperformanceblog.com/2012/02/18/mariadb-5-3-4-benchmark… But there are other reasons to look at mariadb/percona.

I have some experience with Galera. It's pretty slick, but you probably won't want to split writes. It technically works, though you can run into some unexpected deadlocks due to optimistic locking on remote nodes vs. pessimistic locking on the write node[1]. What you really don't have to worry about is replication being behind and loosing data if you lose a host. On the other hand, your overall throughput is constrained by the network, since all commits must at least ping all of the nodes. Which setup are you referring to? MySQL 5.5, or MySQL NDB cluster? [1]http://www.mysqlperformanceblog.com/2012/08/17/percona-xtradb-cluster-…

Hey thanks for the example, I think your phrasing was a little unclear but clearly you know what you're talking about, so I reversed my downvote into an upvote. [edit: crap now I can't do that the buttons gone] I also tried to look into the behavior and it turns out what we're running into here is a 'gap lock' necessary to support ranges in statement based replication (the default). You can change the behavior if you're using row based replication its just not the default. For instance if the second transaction in your example was "BETWEEN 7 and 10" then both transactions would run concurrently no problem, its acting like row+1 level locking. Here's a much more detailed explanation: http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/

With MySQL (and Oracle before that) it was common to simply move different parts of the database data files to different disks. I don't use Mongo so I can't speak to that, but the concept works pretty much universally. See here for more information about spreading your database around multiple disks: http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-acr…

Note that adding comments to otherwise identical queries will typically cause the MySQL query cache to not realize they are the same. See: http://www.mysqlperformanceblog.com/2008/03/20/mysql-query-cache-white… Otherwise, neat idea, I like it.

Or preferably, Sphinx support. From the announcement I can't see that you can't do it, but not that you can. InnoDB and MyISAM are not good at full text search even on local disk, no reason to make that worse. Somewhat kinda germane post from Percona: http://www.mysqlperformanceblog.com/2009/09/10/what-to-do-with-mysql-f…

In default configuration for mysql and postgresql daemons, these benchmarks don't do anything to make full use of the hardware cited. Here are guides for the respective db configs. http://www.revsys.com/writings/postgresql-performance.html http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-s…

Mind adding in HandlerSocket? http://www.mysqlperformanceblog.com/2011/03/28/whats-up-with-handlerso…

I limit my subs to mostly tech and feeds that post infrequently but well. I go back and forth between enabling Hacker News and disabling it. It's too busy and distracting sometimes when I need to get things done and I don't want to get lost it some lengthy article somewhere. Here are my current feeds sans HN http://abstrusegoose.com http://xkcd.com/ http://cmurphycode.posterous.com http://acmel.wordpress.com http://bsdpunk.blogspot.com/ http://saintaardvarkthecarpeted.com/blog/ http://www.cpeterson.org http://www.codinghorror.com/blog/ http://blogs.iss.net http://ignorethecode.net http://blog.lastinfirstout.net/ http://linuxsysadminblog.com http://www.mysqlperformanceblog.com http://planetsysadmin.com/ http://teddziuba.com/ http://toroid.org/ams/etc

Actual link: http://www.mysqlperformanceblog.com/2011/03/18/video-the-innodb-storag…