MySQL Performance Blog

One of our customers reported strange problem with MySQL having extremely poor performance when sync-binlog=1 is enabled, even though the system with RAID and BBU were expected to have much better performance.

The problem could be repeated with SysBench as follows:

[read more...]

One typical question which frequently pops up is whenever it is better to use hardware upgrade or optimize software more. I already wrote about it, for example here.

Today I'll look at the same topic from the consultants view. When consultant should suggest hardware upgrade and when it is not in a simple checklist form.
[read more...]

As you may have recently seen there are some articles about scaling MySQL one 256-way system.

I though wow did they really make it work, considering how many bottlenecks remain in MySQL.

What article really tells us ?
[read more...]

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...]