MySQL Performance Blog

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-sata-256gb-830-not-all-ssd-created-equal/

[2] http://www.behardware.com/articles/881-7/components-returns-rates-7.html

p.s. the RAM benefits the article mentions are real and potentially huge. My query and insert…

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

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…

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!

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

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

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-on-intel-320-ssd/

--

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 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-functions-in-mysql-5-6-for-geo-enabled-applications/