MySQL Performance Blog

The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let’s take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.
[read more...]

Dear Community,

The build 17 of MySQL with Percona patches is available.

New features in the release:

• MySQL-5.0.83 is taken as the basis
• The new patch innodb_use_sys_malloc.patch is added
• The new patch innodb_split_buf_pool_mutex.patch is added

This patch splits the single global InnoDB buffer pool mutex into several mutexes for different purposes. This reduces mutex contention. It may help if you suffer performance loss when the working set does not fit in memory. You can detect buffer pool mutex contention by examining the output of SHOW INNODB STATUS and looking at the first section, SEMAPHORES.

• Google’s style IO – innodb_io_patches.patch
• FreeBSD .tar.gz package for amd64 platform is available

You can download binaries and sources with the patches here

http://www.percona.com/mysql/5.0.83-b17/

The Percona patches live on Launchpad : https://launchpad.net/percona-patches and you can report bug to Launchpad bug system:

https://launchpad.net/percona-patches/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona.

Have you ever wondered how optimized your Memcached installation is? There is a common misconception that one doesn’t have to think too deeply about Memcached performance, but that is not true. If your setup is inefficient, you could:

• Burn Memory
• Waste Network Round-Trips
• Store Keys That Never Get Retrieved
• Have a Low Cache Hit Ratio (i.e. query MySQL too much)
• Suffer a fate too horrible to contemplate.

[read more...]

We’ve written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

• We have 2 Servers in a Master-Slave topology.
• The database size is 100 GB (same tables on each).
• The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
• The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn’t take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary logs  it would take an additional 20 hours ((12 * 0.9) + (12 * 0.75) = 19h48m).

If you wanted to do something like setup a new slave with a 24-hour old backup, and apply the binary logs continuously until it catches up, you will be waiting almost 5 days until that happens (each day has 24hr-19h48m = 4h12m “free” capacity, 19h48m/4h12m = 4.7 days).

So what are the solutions?
If you are using all InnoDB tables, an XtraBackup incremental backup should be much faster than using binary logs.  You can understand Vadim’s excitement when he announced this feature a few months ago.

If you are using multiple storage engines, then your options are to either try and time delay slaves (to keep them close to up to date), or hope that it’s not often that you need to restore!  Eventually this problem should be lessened by a MySQL Server feature – parallel execution on slaves.  Lets hope that it can get enough testers so that it makes it into a new release very quickly.

Dear community,

Today we are pleased to announce release 6 of XtraDB – the result of 2 months hard work.

The release includes following new features:

• MySQL 5.1.36 as a base release
• New patch innodb_recovery_patches.patch
• Experimental adaptive checkpoint method estimate
• innodb_stats – the implementation of the fix forMySQL Bug#30423
• expand-import Support of import InnoDB / XtraDB tables from another server
• split-bufpool-mutex-3 New patch to split buffer pool mutex
• g-style-io-thread Google’s fixes to InnoDB IO threads
• dict-size-limit Limit of internal data dictionary

Fixed bugs:

• MySQL Bugs: #39793: Foreign keys not constructed when column has a ‘#’ in a comment or default value
• Bug #395784 in Percona-XtraDB: “main.innodb_xtradb_bug317074 internal check fails on 5.1.36”
• Bug #395778 in Percona-XtraDB: “main.innodb-analyze internal check failed on 5.1.36”
• Bug #391189 in Percona-XtraDB: “main.innodb_bug36172 mysq test fails with innodb_stat.patch applied”
• Bug #388884 in Percona-XtraDB: “patching fails on 5.1.35”

The builds for RedHat4,5 and Debian are located on http://www.percona.com/mysql/xtradb/5.1.36-6/
The latest source code of XtraDB, including development branch you can find on LaunchPAD.

Please report any bugs found on Bugs in Percona XtraDB Storage Engine for MySQL.
For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can’t help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm – files are deleted in this case.

So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.
First one is ext3grep http://code.google.com/p/ext3grep/, with some instruction on this page http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html.
And also there is TestDisk, with wide list of functionality, one of them is to restore deleted files.

I don’t know such tools for xfs, so in this case DROP will be fatal.

But better advise is do your backups

Over last couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or critical enough issue so I could spend time creating repeatable test case and search of the bug in the MySQL database did not find anything. Recently we had this problem again and now discussed it with Monty’s team – this time we found the bug for this issue.

It is no surprise why I could not find the bug easily – the bug is not really related to multiple key caches but to online key cache resize. It is just this code most actively used in case you’re using multiple key caches. It is very rare one would resize single key cache in production and it only triggers crash sometimes, while if you’re using multiple key caches there are often some scripts in place which adjust their size or change mappings of the tables.
[read more...]

Talks are great. I however very much like discussion and opinion share atmosphere of the Birds of a Feather sessions so we host/co-host number of BOFs at the comming OSCON conference.

Future of MySQL Forks, Branches and Patches I guess is the topic a lot of us are interested in. Monty was going to Show Up and we also should see if we can get someone from Drizzle.

Is Enterprise Flash Ready for Prime Time Flash is cool and hot these days. This is the discussion session and I would really like to hear how well flash works for you whenever you’re using it for storage as a cache or as a part of your hybrid storage hierarchy.

Open Source Data Management Is the BOF about open source tools you’re using to deal with your data – storage, caching analytics. I am especially interested hearing on unorthodox use of the software and successes of new technologies.

I also expect there will be number of other BOFs we’ll attend as the time permits. Monty was going to organize BOF on Open Database Alliance though I have not seen it listed yet.

The OSCON 2009 is taking place next week and we have bunch of talks we’re presenting. I am presenting Full Text Search with Sphinx, MySQL Community Patches and Extensions and Goal Driven Performance Optimization.

Vadim and Ryan have a talk XTraDB OpenSource Storage Engine for MySQL.

This month OSCON is taking place in Silicon Valley which is good for me as I do not have to spend the whole week away from home. Though I would also miss Portland which is Green and beautiful in Summer.

There was small delay in our releases, part of this time we worked on features I mentioned before:
– Moving InnoDB tables between servers
– Improve InnoDB recovery time
and rest time we played with performance trying to align XtraDB performance with MySQL 5.4 ® and also port all performance fixes to 5.0 tree.

So basically we made: new split-buffer-mutex patch, which separate global buffer pool mutex into several small mutexes, and ported some Google IO fixes.
Here are results what we have so far. As usually for benchmarks I used our workhorse Dell PowerEdge R900 with 16 cores and 32GB of RAM and RAID 10 on 8 SAS disks. And again as usually our tpcc-mysql scripts with 100W (about 10GB of data) and 16 connection. For in-memory benchmark I set InnoDB buffer pool to 16GB, and for io-bound benchmarks I made it 3GB.
[read more...]