Improving on MySQL

[Garp]

In between documentation work recently, I've been looking at MySQL performance stuff.

I seem to be spending more and more of my time doing DBA work (despite not really being a forte of mine) so over the last 3-4 months I've been trying to broaden my knowledge of the area as much as possible.
One of the things I've routinely come across references to are Percona, Facebook and Google who between them are causing serious driving forward of MySQL development and all three have released various patches for MySQL that have resulted in significant performance improvements at their end, usually revolving around InnoDB. There is a group out there called OurDelta which includes Monty Widenius, the original author of MySQL, who produce a release of MySQL with all the patches rolled in and I thought I'd give it a shot by doing a direct swap in test, with no config changes to see the performance boost.

This is rather trivial and far from any absolute guaranteed performance measurement, but it's interesting to see how much faster a standard demo version of Magento can behave just using the OurDelta builds of MySQL:

That's a 35% increase in the throughput of queries per second. The test scenario was the same, using Siege to carry out 5 simultaneous page requests against the localhost requesting 1 of 14 different urls, and left running constantly overnight.
I'm hoping at some point to get to run it against my full barrage of Magento data (100k products) on a dedicated server. Could be interesting to see how much better it handles significant load.

[Daz]

Not bad at all. One of the joys of open source software - we get to stand on the shoulders of giants.

Dont really use MySQL for any performance sensitive work, but that's just because I work for a mostly Windows shop, rather than saying anything about MySQL. Hopefully soon I'll be more OSS focussed, and things like this will benefit the projects I work on

[Garp]

I'm itching for a customer to want to do something interesting with one of the myriad NoSQL products out there. I haven't really got anything internal that could benefit from such at the moment, but current predictions based on dev trends suggest most web apps will be using it to a significant extent over the next couple of years.

[Daz]

There's a lot of love for CouchDB at the moment, at least in the Python world - very popular topic at the conferences I went to, has a lovely RESTful API. Although it's written in Erlang!. Heard some mumblings around MongoDB as well, though I dont know a great deal about that one.

[Garp]

Main ones I'm aware of at the moment: Cassandra (which Digg have migrated to almost exclusively), CouchDB, HBase, MongoDB, Neo4J, Redis, Riak, Scalaris, Tokyo Cabinet & Tyrant, and Voldemort.

Seems barely a month goes past without yet another one being added to that list, all approaching the same thing (key-value pairs) from slightly different angles with advantages and disadvantages each.

[Mark]

I haven't heard of any of them.

I could do with a faster MySQL than MySQL. My DB code rather choked last time I tried it with InnoDB tables (it throws about a million updates at the DB in one humongous splurge), but that was a few years ago.

[Garp]

Originally Posted by Mark

I haven't heard of any of them.

I could do with a faster MySQL than MySQL. My DB code rather choked last time I tried it with InnoDB tables (it throws about a million updates at the DB in one humongous splurge), but that was a few years ago.

The NoSQL movement is still comparitively new, even in tech terms, and requires a fair bit of re-thinking how you tackle various tasks. To my mind RDBMSs are an awful lot easier, but I'm not a programmer.

NoSQL pushes towards "Eventually consistent", and locks you down to key-value pairs. That latter is the hardest thing for me to get used to. To my mind key-value is virtually useless, but I keep finding so many different sites using such that it's forcing me to re-evaluate.

Originally Posted by Beansprout

Interesting!

A huge amount can be gained by ensuring MySQL is tuned correctly too - try this:

wget mysqltuner.pl
perl mysqltuner.pl

Great little script.

I utilise that script extensively, it takes a fair bit of time out of the tuning process, however it's not so good at handling the innodb stuff, but then that really requires evaluating the metrics over a period of time. The author is on twitter: http://www.twitter.com/rackerhacker and is a good follow.

Just started evaluating mycheckpoint on my workstation too. So far it's a bit "meh" but the workload my workstation does is a little bizarre.

*************************** 1. row ***************************
report:
Report period: 2009-11-17 07:00:00 to 2009-11-17 08:00:00. Period is 60 minutes (1.00 hours)
Uptime: 100.0% (Up: 1 days, 01:01:25 hours)

InnoDB:
    innodb_buffer_pool_size: 134217728 bytes (128.0MB). Used: 14.1%
    Read hit: 100.00%
    Disk I/O: 0.00 reads/sec  0.01 flushes/sec
    Estimated log written per hour: 0.0MB
    Locks: 0.00/sec  current: 0

MyISAM key cache:
    key_buffer_size: 67108864 bytes (64.0MB). Used: 48.4%
    Read hit: 100.0%  Write hit: 90.0%

DML:
    SELECT:  2.80/sec  29.3%
    INSERT:  0.10/sec  1.0%
    UPDATE:  0.28/sec  3.0%
    DELETE:  0.72/sec  7.5%
    REPLACE: 0.13/sec  1.4%
    SET:     0.05/sec  0.5%
    COMMIT:  0.07/sec  0.7%
    slow:    0.00/sec  0.0% (slow time: 10sec)

Selects:
    Full scan: 0.33/sec  12.0%
    Full join: 0.00/sec  0.0%
    Range:     0.06/sec  2.0%
    Sort merge passes: 0.00/sec

Locks:
    Table locks waited:  0.00/sec  0.0%

Tables:
    Table cache: 2048. Used: 29.3%
    Opened tables: 0.03/sec

Temp tables:
    Max tmp table size:  33554432 bytes (32.0MB)
    Max heap table size: 33554432 bytes (32.0MB)
    Created:             1.20/sec
    Created disk tables: 1.00/sec  83.4%
Connections:
    Max connections: 400. Max used: 44  11.0%
    Connections: 0.30/sec
    Aborted:     0.00/sec  0.0%

Threads:
    Thread cache: 8. Used: 87.5%
    Created: 0.00/sec

Replication:
    Master status file number: N/A, position: N/A
    Relay log space limit: 0, used: N/A  (N/A%)
    Seconds behind master: N/A
    Estimated time for slave to catch up: N/A seconds (N/A days, N/A hours)  ETA: N/A

I'm going to go and shove it on a production server I deal with shortly to see how it behaves there, should be a lot more realistic results

[LeperousDust]

This is all very interesting yet as usual way over my head!

[Beansprout]

Interesting!

A huge amount can be gained by ensuring MySQL is tuned correctly too - try this:

wget mysqltuner.pl
perl mysqltuner.pl

Great little script.