MySQL 5.1.46 With InnoDB Plugin Kicks Butt

We were discussing the recommendations we issue each quarter around MySQL and the question of using InnoDB plugin came up. We usually follow Planet MySQL closely, so we read what the blogs had to say and it was all good, but we decided to provide our users some data of our own. We used our own sysbench tests on to get the information we needed.

A Word About Benchmarks

I don't trust most of the benchmarks that are published online because they really apply to the use case of whomever is writing the article. They are usually many factors that can influence them and I find it difficult to apply them as-is to our environment.

I do trust the benchmarks published online as a reference on how to create and run our own benchmarks. So this article is based on this premise. I recommend you to do your own homework to verify the results for your own use cases.

The Test

Having said that, we use sysbench against the official MySQL RPM with no special adjustments to the configuration file. We run it once with the embedded InnoDB engine and re-ran them with the InnoDB plugin engine. This is the bash shell wrapper we use:

#!/bin/bash
# Sysbench MySQL benchmark wrapper
for nthr in 1 8 16; do
   echo "($(date +%H:%M:%S)) -- Testing $nthr threads"
   sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 prepare
   echo "($(date +%H:%M:%S)) -- Running test for $nthr threads"
   sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 run | tee $(hostname -s)_$nthr.log
   echo "($(date +%H:%M:%S)) -- Cleaning up $nthr threads"
   sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 cleanup
   echo "($(date +%H:%M:%S)) -- done ($nthr)"
done

I like to run a 1 thread test since it gives us an idea of the underlying raw performance. Based on other tests we have done, our systems performance peaks somewhere between 8 and 16 concurrent threads, for this test there was no point in running other configurations. You may replace "1 8 16" with the numbers you think will best represent your systems in production. All the tests are run locally, when testing across the network the numbers will vary based on your network performance.

The Actual Results

So, without further ado, here are the results as reported by sysbench:

Number of threads	No Plugin Trx/sec	Plugin Trx/sec
1	176.32	325.75
8	332.82	742.80
16	334.47	736.40

The results for the No Plugin column are in line with what we got in tests for older 5.1.x releases.

Conclusion

MySQL v5.1.46 using InnoDB plugin kicks ass! I apologize for the language, but the numbers are pretty amazing. I hope you find this post useful.

Edit this page (if you have permission) |

Google Docs -- Web word processing, presentations and spreadsheets.

 

Simple Backup Restore Trick

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;

This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick

Using vi (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big enough (I've seen it happen). Using sed (or similar) might take some time with a big file. The quick and dirty trick I like is:

grep -v "USE \`schema\`" backup.sql | mysql -u user -p new_schema

Adapt the mysql command options to your needs. It's necessary to escape the backticks (`), otherwise the shell might interpret it as your trying to execute schema and use the output as the actual schema name. Also, make sure that new_schema already exists in the server.

This method is quick and dirty and leaves the original backup intact. I hope you find it useful.