MySQL Performance Blog

Dear community,

We are pleased to announce the build16 of MySQL server® with Percona patches.

Since the build13 there was a couple of customer specific releases, which explains cutover in numbering and a pause between the builds. Also we prepared build for both 5.0.77 and 5.0.82 versions.

Since that time new patches were added:

• profiling_slow.patch

This patch adds information from SHOW PROFILE to query information in slow.log output.
profiling_server. This variable ( ON / OFF ) enables profiling of all queries (in all connections).
profiling_use_getrusage. This variable ( ON / OFF ) enables usage of getrusage function in profiling.
Be careful, enabling profiling_server may cause performance degradation, especially with profiling_use_getrusage.

• innodb_extra_rseg.patch

innodb_extra_rsegments. The number of extra user rollback segments created when new db is created.
New information_schema table innodb_rseg shows information about all rollback segments

• innodb_thread_concurrency_timer_based.patch

This patch provides the configuration variable backport from MySQL 5.4.

Fixed bugs:

• Bug #382778: mysql-test variables fails on 5.0.77 with –with-debug
• Bug #381430: mysql-test profiling fails on 5.0.77
• Bug #375494: log_slow_rate_limit logs all queries on a connection after a >1s query
• Bug #378761: compilation fails on 5.0.77
• Bug #381900: mysql-test information_schema fails on 5.0.77 with –with-debug
• Bug #382783: compilation fails on 5.0.82

You can download binaries (RPMS x86_64) and sources with patches here
http://www.percona.com/mysql/5.0.77-b16/
http://www.percona.com/mysql/5.0.82-b16/

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

We announce next beta version of our xtrabackup tool.

Changelist includes:

- set innodb_fast_shutdown=1 on xtrabackup shutdown
- set innodb_write_io_threads=10 by default for fast buffer_pool flushing during recovery
- build xtrabackup with –with-extra-charsets=complex
Bug #357653: innobackupex-1.5.1 –copy-back fails
Bug #358194: 2nd-phase is very slow
Bug #358266: xtrabackup-0.5 dies with SEGV on bad command line arguments
Bug #359341: innobackupex still does not read defaults-file

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu and tar.gz for Mac OS / Intel 64bit there:
http://www.percona.com/mysql/xtrabackup/0.6/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+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

Today we glad to announce release 1.0.3-4 of our XtraDB storage engine.

Here is a list of enhancements in this release:

• Move to MySQL 5.1.33
• Move to InnoDB plugin 1.0.3
• more reliable replication, innodb_overwrite_relay_log_info patch now included in the release
• fix assertions for UNIV_DEBUG
• new fix for rw_lock patch

Percona XtraDB 1.0.3-4 (Sakura) available in source and several binary packages.

XtraDB is compatible with existing InnoDB tables (unless you used innodb_extra_undoslots) and we are going to keep compatibility in further releases. We are open for features requests for new engine and ready to accept community patches. You can monitor Percona’s current tasks and further plans on the Percona XtraDB Launchpad project. You can also request features and report bugs there. Also we have setup two maillists for General discussions and for Development related questions.

We added some new features to our backup tool, namely:

• Now we can handle log file taken during backup with size exceeding 4GB, it may happen if backup takes too long and you have a lot of updates on InnoDB tables
• Progress meter during recovery step, now you can see what percentage of log file was handled

Also we did extensive testing and fixed bugs, so now we consider current state as feature complete and with beta-release stability.

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also) and DEB for Debian/Ubuntu there
http://www.percona.com/mysql/xtrabackup/0.4/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+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

We are coming with next version of xtrabackup - online backup solution for MySQL 5.0 / 5.1 and InnoDB standard version, plugin modification and XtraDB. We still consider it as alpha version, though it shows perfect stable results in our tests.

Let me address two often asked question about xtrabackup:
1) Does it work only with XtraDB or with InnoDB also ?
A: xtrabackup is designed to work with standard version of InnoDB in MySQL 5.0. MySQL 5.1 + standard InnoDB / InnoDB-plugin also are supported. It can fully work as drop-in replacement of innodbackup tool and InnoDB Hot Backup online backup.

2) Do we need to run patched MySQL, because the build instruction mentions the patch for MySQL ?
A: xtrabackup can be run with any version of MySQL: community release, enterprise release, percona builds, ourdelta distributions. You do not need to patch MySQL to run backup. The patch for MySQL is needed only to build the binaries of xtrabackup from source code. If you are not comfortable with building from source we provide the binaries for xtrabackup-0.3

New version of xtrabackup contains the feature which is missed in InnoDB Hot Backup - the backup can be produced as stream and copied to remote box or tape, or compressed without needs to store full copy of database on local disks. Very often there is no space to store second copy of database and you need to mount SAN/NAS for extra space. So now you can run it as

and get copy on remote box.
Also new features in 0.3 - it can be compiled against 5.1 and it supports OS X (sponsored feature).

Now we are developing to next feature which is not available for InnoDB users in any form so far - it is incremental and differential backups. Having that you will be able to make copy only of CHANGED data since given backup, there will not be needs to copy whole 250GB datafile if it changed only 1GB of data since yesterday.

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also) and DEB for Debian/Ubuntu there
http://www.percona.com/mysql/xtrabackup/0.3/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+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

We had too many requests for deb builds of Percona releases so I could not ignore that and added scripts to build binaries on our Ubuntu 8.10 box. It's going to be only 64bit releases (32bit is dead, isn't it?), and I tested binaries on Debian Lenny system and it seems working fine.

So I prepared binaries for our Build13 (which I actually did not announce to not spam PlanetMySQL a lot, I will figure out another channel for announcements), and you can get it there http://www.percona.com/mysql/5.0.77-b13/deb/, and your feedback is welcome!

As for changes for Build13 it contains InnoDB Data Dictionary restriction patch, which I mentioned before (http://www.mysqlperformanceblog.com/2009/02/11/limiting-innodb-data-dictionary/).

Speaking of deb binaries I tried to keep it compatible with native deb binaries, but I had to make some changes:

1. There was strange configure parameters

which I was not sure how to interpret, so I removed any mentioning of embedded server as 5.0 actually is not supposed to work in embedded mode and our patches can't be compiled in this case.

2. I removed NDB cluster engine from builds, we have not tested combination of our patches with NDB so I really do not know how it works together.

Enjoy!

After some sprint coding and testing I am more than happy to declare Xtrabackup getting into alpha stage. And alpha means more that we have some dirty hacks in code / not fully scripted builds / not fully tested all MySQL versions, but backups work just fine! I am able to backup our production box and restore it!

As there were some questions how xtrabackup works, let me explain it: it is supposed to be online, non-blocking backup solution for InnoDB / XtraDB engines for MySQL 5.0 / 5.1 versions. Currently we tested only for 5.0.75 / 5.0.77 and it works just fine. We also modified innobackup script ( thanks InnoDB for making it GPL), so it can be used to backup of MyISAM tables also.

Beside standard --backup and --prepare options we have implemented --throttle to limit IO activity during backup, so we do not hurt production box by intensive file copying operations.

As for now it is available only in source code version on Launchpad lp:~percona-dev/percona-xtrabackup/alpha-0.2 , after some polishing and more testing we will prepare binary builds. Test it if you can and send us feedback. Enjoy!

Today we announce release 1.0.2-3 of our XtraDB storage engine.

Here is a list of enhancements:

• Move to MySQL 5.1.31
• Scalability fix — ability to use several rollback segments

Increasing the number of rseg may be helpful for CPU scale of write-intentional workloads. See benchmark results.

• Scalability fix — replaced page_hash mutex to page_hash read-write lock. See benchmark results.
• Fix broken group commit in InnoDB

As you know, the group commit of InnoDB doesn't work after MySQL 5.0 with binlog.
We pretend to fix this and added "innodb_enable_unsafe_group_commit=[0|1]" parameter. Note if you set this parameter to "1" record order of binlog and InnoDB transaction log could be not same. But because InnoDB manages transactions in correct order most users may not have problem with this.

• New parameters in the XtraDB io patches:
  • innodb_ibuf_max_size — Restrict innodb-insert-buffer size
  • innodb_ibuf_active_contract — Enabling it makes the each user threads positive to contract the insert buffer as possible in asynchronous
  • innodb_ibuf_accel_rate — This parameter is additional tuning the amount of insert buffer processing by background thread.
  • innodb_flush_neighbor_pages — When you use the storage which don't have "head seek latency" (e.g. SSD or enough Write-Buffered), 0 may show better performance.
• Changed parameter for control read ahead activity. Now it accepts string values:
  • innodb_read_ahead = [none|random|linear|both]
• Deb builds for Ubuntu

Percona XtraDB 1.0.2-3 (Sapporo) available in source and several binary packages.

XtraDB is compatible with existing InnoDB tables (unless you used innodb_extra_undoslots) and we are going to keep compatibility in further releases. We are open for features requests for new engine and ready to accept community patches. You can monitor Percona’s current tasks and further plans on the Percona XtraDB Launchpad project. You can also request features and report bugs there. Also we have setup two maillists for General discussions and for Development related questions.

After several important fixes to our patches we made binaries for build12.

Fixes include:

Control of InnoDB insert buffer to address problems Peter mentioned http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/, also check Bug 41811 to see symptoms of problem with Insert buffer.

http://www.percona.com/docs/wiki/patches:innodb_io_patches

* innodb_flush_neighbor_pages (default 1) - When the dirty page are flushed (written to datafile), this parameter determines whether the neighbor pages in the datafile are also flushed at the same time or not. If you use the storage which don't have “head seek delay” (e.g. SSD or enough Write-Buffered), 0 may show better performance. 0:disable, 1:enable

* innodb_ibuf_max_size (default [the half of innodb_buffer_pool_size](bytes)) - This parameter is startup parameter. If the lower value is set than the half of innodb_buffer_pool_size, it is used as maximum size of insert buffer. To restrict to the too small value (e.g. 0) is not recommended for performance. If you don't like the insert buffer growing bigger, you should use the following parameters instead. (* If you use very fast storage, small value (like several MB) may show better performance.)

* innodb_ibuf_accel_rate (default 100(%)) - This parameter is additional tuning the amount of insert buffer processing by background thread. Sometimes, only innodb_io_capacity is insufficient to tune the insert buffer.

* innodb_ibuf_active_contract (default 0) - By default (same to normal InnoDB), the each user threads do nothing about contracting the insert buffer until the insert buffer reaches its maximum size. 1 makes the each user threads positive to contract the insert buffer as possible in asynchronous.

Second important fix introduces variable use_global_long_query_time, which allows all current threads see change of long_query_time. By default value set in SET GLOBAL long_query_time=N command is visible only on new established connection, which is problem if you have pre-established connection pool, say in Java or Ruby on Rails application. With use_global_long_query_time=true even all current threads will respect SET GLOBAL long_query_time=N. The feature made for EngineYard, hosting provider for Ruby on Rails application.

You can download binaries (RPMS x86_64) and sources with patches here
http://www.percona.com/mysql/5.0.75-b12/