There have been recent discussions about DROP TABLE performance in InnoDB. (You can refer to Peter’s post http://www.mysqlperformanceblog.com/2011/02/03/performance-problem-with-innodb-and-drop-table/ and these bug reports: http://bugs.mysql.com/bug.php?id=51325 and http://bugs.mysql.com/bug.php?id=56332.) It may not sound that serious, but if your workload often uses DROP TABLE and you have a big buffer pool, it may be a significant issue. This can get especially painful, as during this operation InnoDB holds the LOCK_open mutex, which prevents other queries from executing. So, this is a problem for a server with a large amount of memory, like the one we have in our lab: a …

Last week wasn’t so active in terms of new development, because many of us were at the MySQL conference, of course. However, we did push out the XtraBackup 1.6 release, and began work on backporting some features from MySQL 5.6 to 5.1 for a certain large customer. Several of the features we’ve added in 5.0 and 5.1 are reimplemented in MySQL 5.6, which is great. It means that we can backport Oracle’s code and we don’t have to maintain these features ourselves anymore.

The MySQL conference, and to some extent the Collaborate conference, was a great meeting for a lot of developers. Many meetings had attendees from Oracle, Percona, MariaDB, and Drizzle developers. Some events had participation from senior management in the various companies. There were also a lot of productive meetings with large customers who use MySQL. I take these all together as a sign that the pace of MySQL development could continue to accelerate in the future. …

Percona Server version 5.1.56-12.7 is now available for download. It is based on MySQL 5.1.56 and is the current stable release in the 5.1 series.

New Features

• Expanded the applicability of InnoDB fast index creation to mysqldump, ALTER TABLE and, OPTIMIZE TABLE. (Alexey Kopytov)

Variables Changed

• Variable innodb_stats_method has been implemented in the upstream InnoDB, with the same name and functionality that had previously existed only in XtraDB. (Yasufumi Kinoshita)

Other Changes

• Implemented support for variable innodb_stats_method being implemented in the upstream InnoDB, including adding a column to table INNODB_SYS_STATS. Bug fixed: #733317. (Yasufumi Kinoshita) …

Percona XtraBackup 1.6 is now available for download and is the current stable release version of XtraBackup.

Options Added

• Added option --extra-lsndir to innobackupex. When specified for the backup phase, the option is passed to xtrabackup, and lsn information is stored with the file in the specified directory. This is needed so that lsn information is preserved during stream backup. (Vadim Tkachenko)
• Added option --incremental-lsn to innobackupex. If specified, this option is passed directly to the xtrabackup script and --incremental-basedir is ignored. (Vadim Tkachenko)
• Added option --incremental-dir to innobackupex. This option is passed directly to the xtrabackup binary. (Vadim Tkachenko)
• Added option --safe-slave-backupto innobackupex. (Daniel Nichter)
• Added option --safe-slave-backup-timeout to innobackupex. (Daniel Nichter)

Other Changes

…

Last week was rather less newsworthy in Percona Server land, as we spent a lot of time preparing for the MySQL Conference and Collaborate, and Vadim was on vacation. One interesting tidbit: I wrote some scripts analyze our log files and found that we were massively under-counting the number of downloads for Percona Server, due to failing to count all the variations of package names. In fact, we have over 250k downloads for Percona Server. (I’ve requested the dev team to be very careful with consistent naming in the future.) In Percona Server news, we continued polishing up the 5.5 release, which should be ready to download very soon. In XtraBackup news, we should release XtraBackup 1.6 with a large list of new features and improvements very soon, and we worked more on Drizzle support for XtraBackup, exchanging lots of merge proposals with the Drizzle development team.

I don’t use many tools in my consulting practice but for the ones I do, I try to know them as best as I can. I’ve been using mk-query-digest for almost as long as it exists but it continues to surprise me in ways I couldn’t imagine it would. This time I’d like to share a quick tip on how mk-query-digest allows you to slice your data in a completely different way than it otherwise would by default.

Disclaimer: this only works when persistent connections or connection pools aren’t used and is only accurate when single mysql connection is used during execution of a request.

If you are seeking to reduce the load on the database server and [as a result] increase response time for some random user requests, you are usually interested in queries that are consuming most MySQL time and that’s how mk-query-digest groups and …

Here’s the weekly review of Percona Server and XtraBackup news. Before I begin, let me briefly address a common question. We are sometimes asked about our plans in light of developments in the market such as MySQL 5.5, MariaDB, Drizzle, etc. The short answer is that we’re very happy with Oracle’s GPL’ed MySQL releases as a baseline for Percona Server, we think that it is extremely important to continue developing Percona Server, we don’t see the need for any major changes to how we’re doing that right now, and we plan to stay the course. This question has helped us to see the need for a prominent public roadmap for Percona Server’s future, and we will work on creating that, to provide a longer and more detailed answer to this and other questions. Now, on to the news:

In Percona Server news,

• We released Percona Server 5.5.10-20.1 as a Release Candidate. ( …

I’ve presented at two different venues about HandlerSocket recently and the number one question that always arises is:

Why hasn’t HandlerSocket become more popular than it is?

Considering how fast and awesome HandlerSocket is, it’s not seeing as rapid adoption as some might expect. I theorize that there are five reasons for this:

Bugs, Bugs, Bugs

Up until the beginning of the year, HandlerSocket had a couple of bugs that a lot of people considered deal-breakers, and it’s not widely known that these issues have been fixed.

• Write operations do not invalidate the query cache – Exactly what it sounds like, if you performed a write operation via HandlerSocket, you could potentially read stale data from the MySQL interface.
• …

I am happy to announce that Percona Server and Percona XtraBackup are now available for the Solaris platform!

• You can download the latest Percona Server 5.1.55-12.6 stable release from our download area.
• As for Percona XtraBackup, we made Solaris binaries for the upcoming XtraBackup-1.6 release, and they are available from “pre-release” directory.

There are couple features not available in Solaris build of Percona Server: Response Time Distribution (it depends on GCC platform specific functions) , HandlerSocket ( compilation issues on Solaris), Maatkit UDFs.

I want to say a special thanks to Joyent (a cloud computing provider) for Solaris resources and …

Have you ever seen BIG weird numbers in mk-query-digest report that just seem wrong? I have! Here’s one report I got today:

...
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          5088s     1us    171s     2ms   467us   104ms    28us
# Lock time            76s       0      3s    26us    69us     3ms       0
# Rows sent          9.80M       0   1.05M    3.50    0.99  642.32       0
# Rows examine       5.59G       0  82.56M   2.00k    0.99  97.41k       0
# Rows affecte     457.30k       0   2.62k    0.16    0.99    1.68       0
# Rows read          2.16G       0  82.56M  788.53   21.45  82.91k    0.99
# Bytes sent         2.14T       0   4.00G 781.27k   3.52k  47.84M   84.10
# Merge passes     273.47G       0   4.00G  97.69k       0  10.35M       0
# Tmp tables …