I am happy to announce Yasufumi Kinoshita joins our team as Performance Engineer. Yasufumi is known as InnoDB hacking expert, and there is bunch of patches he made we include in our releases: innodb buffer pool scalability fix, innodb rw_lock fix, control InnoDB IO etc. Actually there is one more patch -
"adaptive flush" in InnoDB, which makes flushing process more uniform and predictable. Yasufumi will post about this patch soon.

Yasufumi also had talk on MySQL Conference & Expo 2007
InnoDB Performance Potential in High-end Environments

Yasufumi's primary tasks as you can guess will be InnoDB performance and scalability fixes, InnoDB improvements and related question, however we are not going to restrict his activity only to InnoDB area but for all MySQL related problems.

So expect more and better patches in …

Google patches V1 contain interesting patch to mirror binary log on slave. Although Google is preparing GlobalTransactionId patch, which going to be replacement for MirroredBinlogs, we still think mirroring binary logs is very useful for:

• Backup of binary logs in real time
• High availability, to switch master load to slave with mirrored binary logs

By request of our friends EngineYard we extracted this patch from Google's patchset and going to include in our next releases. Actually our patch contains features for both MirroredBinlogs and FastMasterPromotion patches, as second provides feature to make slave as master in case of main master failure.

At …

There is patch from Google to improve SMP performance , but for some workloads it showed for us reverse scalability.
E.g. update_key benchmark from sysbench. There are also results with Yasufumi's rw_locks (http://bugs.mysql.com/bug.php?id=26442)

What does that mean? Should we wait two …

A while ago, MySQL developed a Community Contribution Agreement for community contributions to the MySQL source code. While browsing the MySQL Forge Wiki I came across:

http://forge.mysql.com/wiki/Community_Contributions

This page shows that the Community Contribution Agreement has changed — it is no longer the document MySQL AB created. It is now Sun Microsystem’s standard Sun Contributor Agreement, which CEO Mårten Mickos recently explained to me was “more accepted than the agreement MySQL had come up with.”

I am happy to see some of the great Sun practices trickle down to replace some issues that MySQL did not handle smoothly. All in all, I agree with Mårten Mickos and think the Sun Contributor Agreement is much better….

….but what do you think?

Ever wonder what is stored in InnoDB buffer pool at the moment ?

It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content

PLAIN TEXT SQL:

1. mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_CONTENT; 
2. +-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
3. | BLOCK_NUM | SPACE | OFFSET | RECORDS | DATASIZE | FLUSH_TYPE | FIX_COUNT | LRU_POSITION | PAGE_TYPE_ID | PAGE_TYPE      | INDEX_NAME      | TABLE_SCHEMA | TABLE_NAME       |
4. …

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

1. New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)

1. New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user

1. FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

• INFORMATION_SCHEMA tables - USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS
• Access protection - USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information of tables that current user has access to (SELECT privileges required). This is important for hosting providers

Here is example …

Description:
The drupal_lookup_path function unnecessarily counts all rows of the url_alias table to determine if any aliases are defined. This can be expensive on a transactional database such as MySQL with the InnoDB Storage Engine or PostgreSQL when a website has a lot of aliases. This patch modifies the query to always only return one row, possible because any existing pid will be greater than 0. If no aliases are defined, it will return 0.

Status:
This patch has not been merged into any release of Drupal.

Patch

read more

We are going to show the effects of the new patches applied to Percona HighPerf release. As you see from the following graphs, there is significant difference to normal version when the data bigger than buffer pool (right graph shows CPU usage)

The workload emulates TPC-C and has a same characteristic to DBT-2 (it is not DBT-2, but custom scripts, we will publish them eventually). There are no delays between transactions (no thinking time, no keying time), it uses MySQL C API and the server side prepared statement.

The server has 8core CPU and RAID storage (RAID10 / 6 disks). The data population is along to the scale factor 40WH (:=~4GB). It is enough bigger than the data cache of the storage.

main common settings

innodb_buffer_pool_size = 2048M
innodb_thread_concurrency = 0 …

I've written before about how to figure out which connection is holding the InnoDB locks for which other connections are waiting. In other words, how to figure out who's blocking you from getting work done. The short and sweet: turn on the InnoDB lock monitor and use innotop to look at the locks held and waited-for. This has some disadvantages, so I made a patch to solve the issue the way I like it. The result is significantly more ability to find and solve InnoDB lock wait issues.