Category Archives: MySQL

Common – where are MySQL impressive numbers?

I always read about blazing performance of MySQL, how many millions of transactions it can do per second when using a cache, SSD, Fusion-IO or whatever. Check out some of these reports:

RAID vs SSD vs FusionIO

FusionIO – time for benchmarks

Testing Fusion-io ioDrive

Fusion-io Tweaks Flash To Speed Up MySQL Databases

I like big numbers and faster this and faster that, who doesn’t. I’ve tried Fusion-IO cards, I’ve tried SSD, I’ve tried normal hard drives with PCI SSD as a cache using Intel CAS. Bottom line, nothing works to improve the performance in any significant way.

If I test with a lab testing tool, or benchmark test it will report an Fusion-IO, SSD or Intel CAS have the potential to provide speed improvements. What about a real world test? That is where I want to see a difference. When I am doing a database restore on of a MySQL server, I would like to see something actually impact the restore times. In reality on a 8 core machine only one core is used since the default restore can only run as a single process.

I read recently about disabling hyper threading on the CPU may actually give MySQL a boost. I also read about innodb_doublewrite in MySQL (dangerous, not recommended to use).

Lets run a few tests under different situations. I am going to restore a 3gb database with about 11.9 million records. This is not benchmark software, I want to see changes in real life.

Each restore is run twice and the average if the two runs used.

Testing with 4 cores (hyper-threading disabled)

Intel CAS disabled – 27.25 minutes

Intel CAS enabled – write through cache 29.10 minutes.
Intel CAS enabled – write back cache 27.18 minutes

So none of this seems to make any major difference. Perhaps the disk is not the bottleneck. Lets try another approach to the testing.

Conduct the same tests, but rather than performing a single restore we will restore four instances of the database simultaneously… Lets see what happens…

Intel CAS disabled – 1 hour 33 minutes

Intel CAS enabled – write through cache 1 hour 34 minutes
Intel CAS enabled – write back cache 43 minutes

Finally some improvement. That represents a 100% speed increase when using Intel CAS software, increasing the stress on the disks does show some improvement as a of the caching.

So the best performance comes from putting some pressure on the disk system, only then does the cache start to show some benefit.

Next, a quick test with SQL’s skip-innodb_doublewrite feature enabled (or I should say disabled).

So disable the innodb_doublewrite feature, leaving the Intel CAS inabled with write back caching and restoring 4 copies of the same database simultaneously… Lets see what happens…

Intel CAS enabled – write back cache 26 minutes

Nice! While turning off innodb_dobulewrite is not safe (at all!) for production, if you are trying to recover from a disaster situation and need to restore a large database (or several large databases) turning it off can certainly reduce your recovery time.

I’m curious, lets see our restore time with innodb_doublewrite turned off, but only restoring a single copy of the database.

Intel CAS enabled – write back cache 19 minutes

Intel Cache Acceleration Software – v3 is out!!

I have been waiting for this, because I had previously read that version 3 would introduce (the dangerous) feature of write-back caching.

It is dangerous because if the server crashes or looses power and the writes in the cache have not been written to the disk you will have inconsistent data…. that’s bad.

So, lets do a few tests.

The Testing Environment

Here is the hardware setup of the server being used for the benchmark tests.

Motherboard: Supermicro
OS: Windows Server 2008 R2
Processor: Dual Intel Xeon L5420 @ 2.50 GHz
System RAM: 32 GB
Storage Controller: Intel ESB2 SATA RAID controller (driver 10.1.5.1001)
Spinning Disks: Seagate Laptop 1tb (ST1000LM014)
Spinning Disk Setup: RAID 10 (4 disks)
Cache Device: Kingston Digital HyperX Predator 240 GB PCIe Gen2 x4 Solid State Drive

FIRST TEST

Restore a 205gb MySQL database, that is a lot of writes to the disk. Lets see if the write cache makes any difference, in theory it should.

#1 – Cache off, source and destination on same disk: 6 hours 22 minutes
#2 – Cache on (write-back enabled), source and destination on same disk: 6 hours 30 minutes
#3 – Cache on (write-back enabled), source SMB network share: 7 hours 7 minutes

The hard drives in RAID 10 can write at less than 200 MB/sec and the cache device can write at more than 650 MB/sec —- yet the performance drops slightly? We should ideally be seeing a massive increase in performance.

How can the results actually be slower when using the cache?

SECOND TEST

Intel provides a tool called Intel IO Assessment Tool, you run it on your system and it will determine if your system can benefit from the cache and what files you should be caching.

The results say I could benefit and the files I should be caching are the MySQL data folder. No surprise since the server is strictly a MySQL server.

IOPS

Lets use Iometer to calculate how many IOPS this hardware setup can produce. The tests are conducted using the OpenPerformanceTest16.icf (16GB) config from http://vmktree.org/iometer/. The specific test used was RealLive-60%Rand-65%Read.

Kingston Digital HyperX Predator (systems cache drive)
I/Os per Second: 8,346
MBs per second: 68.38 MBPS
Average I/O Response Time: 5.25 ms

Seagate Laptop (RAID 10) – Intel CAS enabled: NO
I/Os per Second: 150
MBs per second: 1.23 MBPS
Average I/O Response Time: 350.20 ms

Seagate Laptop (RAID 10) – Intel CAS enabled: YES
I/Os per Second: 1424
MBs per second: 11.67
Average I/O Response Time: 34.22

This test shows that Intel CAS is working, with nearly a 9.5x improvement over going direct to the disk. Yet no measurable improvement in the performance of MySQL?

FINAL RESULTS

The results of all tests I have done with Intel CAS have been disappointing to say the least. The new version 3 has no options to set, so I can’t really be screwing anything up.

I am going to reach out to Intel and see if they can provide any insight as to why I am not seeing any benefit in my real life usage.

Intel Cache Acceleration Software – Performance Test

Intel LogoI am attempting to boost the performance of some spinning disks by using Intel Cache Acceleration Software.

Using a write through process the software can place the contents of folders/files you specify in cache. When the file is next requested it can be quickly pulled from the cache rather than going to the slow spinning disk. This can result in major speed improvements of disk reads. Great for caching spinning disk content or network based storage.

In my specific case I have a fairly small amount of cache, only 124 gigs of PCIe SSD storage. I am allocating 100% of that space to cache the MySQL data folder, which in theory is making 100% of my database available from the cache because my data folder holds only 116 gigs of data.

The software integrates into Windows Performance Monitor so you can easily view how much data is in the cache, the % Cache Read Hits/sec and other good stuff.

While monitoring those stats and doing queries against the database, perf mon was not showing hits to the cache… what’s going on? Time to investigate and benchmark a few things.

There is really no configuration options in the caching software. The only setting is something called 2 Level Cache and you can select on or off – that’s it. There is not much information about what that is for, and it is not intuitive based on the label.

I am going to run three simple tests, which should reveal how much of a performance the cache makes and if I am better off running with this 2 Level Cache on or off.

Test #1
Restore & Backup 96gb Database With No Caching

Restore Time: 5 hours 49 minutes
Backup Time: 1 hour 03 minutes

Test #2
Restore & Backup 96gb Database With Caching – Level 2 Cache Off

Restore Time: 5 hours 56 minutes
Backup Time: 0 hours 53 minutes

Test #3
Restore & Backup 96gb Database With Caching – Level 2 Cache On

Restore Time: 6 hours 07 minutes
Backup Time: 0 hours 53 minutes

The purpose of the restoration before the backup is to give the cache time to populate. In theory all the database data on disk should be available to the cache, however I have no way to verify that it is all in cache or not.

The results? WOW! Can you say disappointed? What is going on here?

2.5 Inch Drives Are Not Quick

What can you do if you need high performance, only have 2.5 inch drive slots and can’t afford SSD?

Last year I built two servers using WD Red drives running in RAID 10. The performance is not that great and also not very consistent.

This year I built two more of the same servers, but wanted to equip them with Seagate Laptop SSHD disks which are a hybrid spinning disk/SSD with 8gb of SSD type memory. They cost a few dollars more, about $10 more at the time of this posting.

Reading benchmarks online before buying them, they should be faster than the WD RED drives… but the downside is they are not specifically designed for RAID based systems (does that matter?).

First up, WD Red:

Maximum Read: 143MB/sec
Maximum Write: 86MB/sec

raid10-bench

Next, Seagate Laptop SSHD:

Maximum Read: 214MB/sec
Maximum Write: 193MB/sec

segate-sshd

Overall, quite a big performance boost. I am really hoping that it helps with the write speed since the workload is just about all write.

I found doing file copies from an SSD over to the Seagate Hybrid drive in Windows was reporting write speeds of 400MB/Sec when coping a 90 gig file. That was very impressive.

To add some extra boost to these old servers I also equipped them with Kingston HyperX Predator 240 GB PCIe sold state drives. Using 100 GB of those drives as a boot device and the balance as a read cache for MySQL data which is stored on the Seagate RAID 10 drives.

How does the HyperX Predator perform in a benchmark test? Lets take a look.

Maximum Read: 1,388MB/sec
Maximum Write: 675MB/sec

HyperX

Those are certainly some big numbers, looking forward to the price coming down so we can get a few TB of speeds like that.

Another cheap rack of compute power

I think it was about 1 year ago that I setup a rack at a datacenter, filled with servers that had come off lease. You can get them cheap, real cheap vs. a new server.

I read the other day that Intel has not made any ‘major’ improvements to their processors since 2011. Sure there have been some improvements to SATA, SSD’s etc. But when you can buy a server for 10% – 20% of the price of purchasing new, new just does not seem worthwhile.

Last year we used 1U twin Supermicro servers using the the X7DWT-INF motherboard. They came equipped with 2x Intel Xeon L5420 quad core processors and 16GB ram. I looked up the price paid last year, they were $450.

They work fine, way more ram than we need. The only downside is the IPMI management is not always the greatest but we have managed. We even bought extra servers that just sit in the rack, to be used as parts in the event of a failure of any of the old servers. So far the parts machines are just sitting there, no issues with parts.

Now 2015, we want to build another rack – at another datacenter (additional redundancy). Would like to find computers with X8 based motherboards as the IPMI is supposed to be better.

Unfortunately they are still too costly, so we are looking at the exact same model of server that we bought last year. The good news is the price has dropped for $450 per 1U down to $250. Imagine, there are two full servers in 1U for $250. That is really $125 per server, since there are two per 1U. Simply blows my mind, since a new machine would cost you $2,000+ for a server and you don’t get anywhere near the price/performance boost.

Say we put 45 1U units in a rack (that is 90 servers) for a cost without hard drives of $11,250. If we could find new servers, twin models for $2000 (without hard drives) the cost would be $90,000. I doubt you could find servers for $2000 new.

There are no hard drives included with the servers, so SSD will be purchased for most.

A couple servers will be used as database servers, last year we used WD Red drives and attempted to implement read caching using Fusion-IO cards. The caching concept did not work very well, the performance improvement seen was not worth the effort.

Seagate Laptop SSHDSo this year, rather than WD Red (currently $69.99) we are going to try using Seagate Laptop SSHD (currently $79.68).

Now according to benchmarks over at UserBenchmark these 2.5 inch drives do not perform well vs. 3.5 inch drives or SSD (of course). However, if you benchmark them WD Red 2.5 vs Seagate Laptop SSHD they actually perform 58% better overall than WD Red and 161% better on 4K random writes.

Since the workload on the database servers are 90% write, we are going to give these laptop drives a chance.

We still have a Fusion-IO card sitting here unused as well from last year. So we can stick that in one of the DB servers to increase the read side of things. Would not go out and buy one just for this purpose but since it is just sitting here on the shelf, might as well put it in.

Boosting Old Technology – Can Caching Help?

I have written a few times about some older Supermicro servers I am working with recently.

Since the setup I built has 4 x 1tb hard drives (spinning kind), and no space for an SSD to use as cache I bought a used 160gb Fusion-IO ioDrive on eBay.

I had been running Debian on the server. The drivers/software for the Fusion-IO under Linux were a bit restrictive, requiring a specific distro and kernel. I could not find any free/cheap caching software to use under Linux that actually worked. I found some open source stuff, it just did not work.

The Hardware/Software Setup

Why the switch from Debian to Windows?

  1. Fusion-IO setup under Windows is much easier
  2. Availability of Intel’s caching software
  3. Being able to easily run any version of mySQL

The server getting the upgrade powers a database – nothing else. The hard drives use the onboard Intel RAID controller, operating in RAID 10, which based on my previous testing gave the best write performance while providing redundancy.

The workload is 90% write, with no users sitting waiting for writes to complete. Write performance still needs to be quick to keep up with the transaction volume. Users do not want slow reads either – of course.

Ideally I would have a Fusion ioDrive big enough to hold all the database files, but that is not financially possible.

Currently the live data on the database is about 110+ gigs. Technically that would fit on the 160gb Fusion ioDrive. As the database grows, there could be issues.

Intel LogoThe Intel Cache Acceleration Software provides read caching only, it operates in write-through mode to populate the cache – write-back is not available in the software but I would probably not use it anyway due to the increased risk of data loss in the event of a crash or power failure.

Lets find out what a PCI based SSD can do with some caching of those spinning disks. All tests were done on the server, with no users connected. The database was restored for each test, automatically populating the cache if caching enabled.

110+ Gig Database Restore

  • 2 hours, 57 minutes – Restore to RAID 10 (no caching)
  • 3 hours, 10 minutes – Restore to RAID 10 (with caching)
  • 2 hours, 7 minutes – Restore to Fusion-IO storage (no caching)

Sample Reads

I conducted a number of read tests, accessing various databases running reports and user queries. The same queries were run for a tests after a fresh database restore. Some queries make full use of indexes, some do not.

  • 22.54 seconds – Read from RAID 10 (no caching)
  • 22.62 seconds – Read from RAID 10 (with caching)
  • 21.57 seconds – Read from Fusion-IO storage (no caching enabled)

Surprised by those numbers?!? I sure am, underwhelming to say the least. Time to do some research into why the performance is so bad when it should be no much better, both when using cache and when direct from the Fusion-IO card.

Testing The Disks

RAID 10

A previous benchmark on this exact same hardware, but with a different version of Windows showed a maximum write speed of 138 MB/sec. Now running the same benchmark software, on the same hardware but a different version of Windows it is maxing out at only 58 MB/sec. Things get more and more strange today.
RAID 10 - Slow

Fusion-IO ioDrive

I have no previous benchmark for this one, but the current graph shows speeds that I would expect from this PCI memory card. An impressive maximum write speed of 811 MB/sec. and a read speed of 847 MB/sec.
fusion-io-benchmark

With a write speed increase of 1360% over the spinning disk, why is the mySQL restore only 39% faster when restoring to the Fusion-IO disk?

MySQL replication woes – error 1236

I have been using MySQL replication for years, specifically multi-master where you can do both read and write to either server.

mySQL_Dock_Icon_by_Presto_XTo make my situation even worse, I do it across the global Internet. The added latency and/or outages between sites can cause ‘issues’ that you might not see if your servers were connected locally with high speed Ethernet.

This weekend one of my servers lost a hard drive. One of the NEW WD red hard drives, I just wrote about a few weeks ago. The drives have only been in production for less than two months and one has failed already.

Using Linux software RAID, the data is OK and the machine is still humming along while we wait for a replacement drive to be installed.

One thing that did not survive the hard drive crash is the MySQL replication. The other server (the one where no hard disk crashed) actually started showing this error after the replication stopped:

Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.006259’ at 2608901, the last event read from ‘/var/log/mysql/mysql-bin.006259’ at 2608901, the last byte read from ‘/var/log/mysql/mysql-bin.006259′ at 2609152.’

They suggest out of disk in the message, but not the case here. The problem was probably from the server being restarted without being properly shut down (a big NO NO).

So if you get this error, or any other replication error that references a binlog and a location how can you find out what the problem is?

If you have been running MySQL replication for any length of time, you have seen times where the replication stops and you need to know why.

To view the transaction that is killing your server, head over to the master server and use the mysqlbinlog utility to view what is going on.

In my case I was greeted with this message.

# Warning: this binlog is either in use or was not closed properly.

Essentially it is saying the file is messed up, with no valid content in that file the replication is stuck. To get it started again you will need to update your slave(s) with a new instruction, telling them to move to the next binglog. I advanced to the next binlog like this:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.006260';
CHANGE MASTER TO MASTER_LOG_POS = 0;
START SLAVE;

With that the slave can start up again, reading from the next binlog which contains valid content.

Crisis diverted, all we need now is a new hard drive and the server should be a happy camper once more.

Tuning MySQL with mySQLTuner

Ok, so I’ve got a MySQL database… it is on a dedicated server – a cheap one. Only has 2gig ram and a low end i3 processor. It is running on a Unix based system and the performance is not very good.

Problem is the dedicated server is paid up for another 1/2 year, so I want run out the contract and move to a bigger box after the contract runs out.

I started doing some Googling (is that a word?) to locate information on tweaking MySQL.

Came across an excellent bit of kit called mysqltuner. Easy to install with apt-get or yum as it is available as a package (mysqltuner). Then just execute mysqltuner and provide the admin login/password for the local server.

I knew the server was not setup correctly, but no idea what needs to be adjusted. The sql server needs to run for at least 24 hours to get a baseline of what needs to be adjusted.

I made a few small adjustments (reducing max connections, which sucks up way too much ram) and have restarted the server. Will run the command again after 24 hours and make a few more of the suggest changes. Nice piece of software, if you are looking to tweak your MySQL – do check it out.

mySQLTuner

You can see in the image that the server has been running for 180 days, 88% of the usage is writing and the maximum number of connections at one time to the server during that time has been 619 simultaneous connections.

However because the performance of the slow performance of the machine we moved most of the clients off the box so we have reduced the maximum connections down to 100 which saves a lot of ram.

The funny part I see is the databases consume 45.7gigs currently and the tools suggests a buffer_pool_size of more than 45gigs. That means the SQL server could run 100% out of RAM, sorry mySQL that isn’t going to happen on this server 🙁