MySQL Performance Blog

I did not usually go to “Elite” servers on Dell web site but looking at customers system today I went to check Dell Poweredge R900. This monster takes up to 4 Quad Core CPUs and has 32 memory slots, which allows to get 128GB of memory with 4GB of memory chips. This means upgrade to default configuration to 128GB of memory will cost you just $9600 (list price). I’ve been able to configure on a web the system with 8*2.5″ hard drives RAID and 2 CPUs (just as we usually configure PowerEdge 2950) with 128GB of RAM for about $16000. This means talking to Dell Sales rep it can purchases within $15000. This may sounds as a lot but if you’re memory constrained it is cheaper per GB than buying 32GB box for $6000
[read more...]

This article is not meant to malign hosting providers, but I want to point out something you should be aware of if you’re getting someone else to build and host your servers for you.

Most hosting providers — even the big names — continue to install 32-bit GNU/Linux operating systems on 64-bit hardware. This is a serious mistake.

You have to tell them to install a 64-bit operating system. If you don’t then you will come to a point where your needs grow and you want to use more memory — and they will gladly install 8 or 16GB of memory for you, but MySQL can’t use it because it runs in a single process, which is limited to about 2.5GB of memory. And then you have to rebuild the whole operating system from scratch. But you don’t want any downtime, so you have to buy another server, set it up as a slave, switch your site to use it, and then rebuild the old server. That 32-bit OS turned into a pretty expensive mistake.

I do not know why the hosting providers keep doing this. Just yesterday I got a quote from a hosting provider for a medium-high-end system with 8GB of RAM, and forgot to tell them 64-bit OS, and they actually listed 32-bit explicitly on the quote — useless! I would estimate about half of all the hosted systems I’ve seen so far have this mismatch. I don’t know why they do this — maybe there is a reason, but I don’t know it and it looks pretty silly to me. 64-bit hardware and operating systems aren’t new anymore. In fact, 32-bit is hard to find in server-class hardware these days. So it certainly looks like the hosting companies need to change what they’re doing, but maybe there’s a different reason.

Sun is aggressively pushing T2000 as Scalable MySQL Platforms, and indeed it is Scalable in terms of high concurrency workloads - it is able to execute a lot of concurrent threads and so speed gain from 1 thread to say 32 thread will be significant.

But thing a lot of people miss is - Being Scalable is Not Enough - you need to scale from reasonable base to claim the good performance, and this is where T2000 performs subpar in many cases.
[read more...]

Recently I have had a chance to check out MySQL Performance on “Memory Appliance” by Violin Memory which can be used as extremely high speed storage system.

I helped Violin Memory to optimize MySQL for customer workload and Violin memory and also had a chance to do some benchmarks on my own. 2*Quad Core Xeon running CentOS5 was tested using ext2 filesystem and SysBench tool.

Using 16K read sizes (matches Innodb page size) I could get 1.2GB/sec (80K req/sec) for reads and about 500MB/sec writes with 16 concurrent threads. Things scaled well and with 256 threads I got even a bit better performance.

Interesting enough utilization in iostat never went over few percents and load was mostly CPU bound.
[read more...]

MySQL Users Conference is coming and with it my presentation about Innodb Scalability limits. We did bunch of tests but we surely could get benefit of some extra hardware for testing, so if you could provide us with dedicated remote access for benchmarks it would be great.

Here is what we’re looking for in particular:

- More than Dual Core Opteron systems. Would be good to see how they scale
- More than 8 core systems
- Non x86 based systems (Niagara etc)
- Fancy IO subsystem - more than 8 hard drives
- SSD based storage.

If you have something of this sort available let me know.

I’m often asked how one can evaluate IO subsystem (Hard drive RAID or SAN) performance for MySQL needs so I’ve decided to write some simple steps you can take to get a good feeling about it, it is not perfect but usually can tell you quite a lot of what you should expect from the system.

What I usually look for MySQL is performance in random reads and random writes. Sequential reads and writes are rarely the problem for OLTP workloads, so we will not look at them.

I also prefer to look at performance with O_DIRECT flag set to bypass OS cache. This may execute separate code path in kernel and so has a bit different performance pattern compared to buffered IO (even followed by fsync regularly) , but it allows to easily bypass OS cache both for reads and for writes and so does not require creating large working sets for boxes with significant amounts of memory (or reducing amount of usable memory).
[read more...]

Implementing MySQL database in 24/7 environments we typically hope for uniform component performance, or at least would like to be able to control it. Typically this is indeed the case, for example CPU will perform with same performance day and night (unless system management software decides to lower CPU frequency due to overheating).

This is also almost the case with Hard Drives - there are possible performance differences based on where data is stored on the disk, amount of remapped sectors etc. There is also database and file system fragmentation however these also tend to accumulate in predictable fashion.

If you have RAID controller this well may not be the case - to protect your data RAID controller may implement bunch of algorithms which can affect performance dramatically.
[read more...]

I had found pile of MySQL and PostgreSQL benchmarks on various platforms which I have not seen before. Very interesting reading.
It does not share too much information about how MySQL or PostgreSQL was configured or about queries. Furthermore MySQL and PostgreSQL has a bit different implementations (ie SubQueries avoided for MySQL) so do not just compare it directly.

It also does not mention if Innodb or MyISAM tables are used - it turns out Both are used in the benchmark. This is CPU bound benchmark with working set fitting in memory.

MySQL and PostrgreSQL Scalability on Xeon Woodcrest, Opteron and Niagra
Pretty interesting to see how PostgreSQL scales just as systems should scale in theory - gradually goes up with number of threads about matches number of Cores/Threads and stays at this level at higher concurrency. MySQL with Innodb shows its ugly face and drops pretty quickly as concurrency growths with peak at about number of CPUs. I guess this is lucky case as Innodb may well start to slow down before concurrency reaches number of CPUs.

Yes, Innodb Team has provided the fix for this scalability problem and it is merged into MySQL 5.0.30 “Enterprise” but according to the tests I’ve done so far it is far from full solution yet.

It is also interesting to see CPU comparison in this test. Woodcrest has best performance in this test (and in many other MySQL tests), Opteron comes second and older Intel Xeons as well as Niagra being outsiders.

Niagra scalability is one more interesting story. As you can see MySQL 4.1 actually scaled pretty well with Niagra, suffering slow regression with increased concurrency rather than quick drop. In MySQL 5.0 it is changed dramatically - it climbs to higher peak but it drops down very quickly as well as concurrency growths. It is seen much better on this picture

Linux vs Solaris comparison is also pretty interesting. With MySQL Linux has higher peak but Solaris suffers less with increased concurrency.

Note: I have not validated these benchmarks and as I already mentioned they do not have full disclosure. They however do match my own experience with MySQL so I tend to trust PostgreSQL data points as well.

Yesterday I had fun time repairing 1.5Tb ext3 partition, containing many millions of files. Of course it should have never happened - this was decent PowerEdge 2850 box with RAID volume, ECC memory and reliable CentOS 4.4 distribution but still it did. We had “journal failed” message in kernel log and filesystem needed to be checked and repaired even though it is journaling file system which should not need checks in normal use, even in case of power failures. Checking and repairing took many hours especially as automatic check on boot failed and had to be manually restarted.

Same may happen with Innodb tables. They are designed to never crash, surviving power failures and even partial page writes but still they can get corrupted because of MySQL bugs, OS Bugs or hardware bugs, misconfiguration or failures.
[read more...]

Partha Dutta posted pretty interesting post about iSCSI vs SCSI performance using SysBench.

This is nice to finally see some iSCSI benchmarks done with MySQL - something we were planning to do for a while but never ended up doing, mainly due to lack of hardware available for tests. It is also good to see Sysbench being used, as It is developed by our team, even more I write first version of this tool myself, for performance research (The new version was complete rewrite).

The problem I see in this test however - the benchmark is not fully described which makes it hard to judge what they really mean. Was it OLTP test or some other combination ? Was default table size used (that one is pretty small and would typically keep load CPU bound)

The image with results raises a lot of questions - why load is slower with 2 threads than with one threads in many cases ? Why difference is so small between these configuration even with large number of threads - we’re speaking about 16 SCSI disks on high end and single SATA on the low end.

The explanation could be for example it is log write bound rather than tablespace IO bound, which does not really benefit from multiple hard drives. SCSI drive getting slower than Serial ATA on large numer of connection could actually mean it is faster - regression with many threads often happens with Innodb if load becomes CPU bound rather than Disk bound.

I would also be especially careful making sure iSCSI driver peoperly does fsync() otherwise loosing database content might be the price for improved performance.

In general I’m pretty optimistic about iSCSI technology and think it will find more use with MySQL in years to come. One could use LVM with iSCSI and get some SAN like functionality.

I would not however view iSCSI as performance killer - if talking to your local SCSI drive is slower than talking to the same drive across the network - something is likely to be wrong, on other hands iSCSI allows to get much larger number of hard drives connected + easy roaming of hard drives between nodes while overhead should be limited.

Anyway that would be quite interestig to see more benchmarks.