One of the services we (Sun/MySQL) offer is a Performance Tuning and Optimization consulting package. These consulting engagements generally take several different forms as each client has a specific need that must be resolved. But there is a commonality between all of these assignments, they all require some amount of bottleneck detection as well as benchmarking. The topic of benchmarking and bottleneck detection is huge, and has a very broad audience. I really can not do it justice in a single post or two. In fact we are actually planning to give a Webinar in March ( more details will be forth coming ) talking about how we go about finding bottlenecks while out at client sites, and even in an hour I can barely scratch the surface.

What I thought I would share are a few of the easier cases of finding and fixing a bottleneck I have encountered. I am still amazed that more people have not performed similar steps to eliminate …

I spoke with Brian Aker last week about the issues I found with decrement and increment while working on my book "Developing Web Applications with Apache, MySQL, Memcached, and Perl". He suggested I make sure that I was using the latest libmemcached. I checked, and the version of libmemcached that is included with Memcached::libmemcached is 0.21 - this is the version of Memcached::libmemcached I obtained from using CPAN (perhaps CPAN needs a new bundle...). So, I obtained the most recent version of Memcached::libmemcached from the subversion repository at https://perl-libmemcached.googlecode.com/svn, and it includes the lastest libmemcached, version 0.25. To make a long story short, I re-wrote my test script to test each operation individualy and time that, as well as compare the times to Cache::Memcached. The numbers are great and are reason enough to use …

I'm busy working on my book, "Developing Web Applications with Apache, MySQL, Memcached, and Perl", writing about how Libmemcached (particularly the perl interface to libmemcached, Cache::Memcached::libmemcached/Memcached::libmemcached) is faster. And it is, except when I was writing a test script to compare, I first used Daisuke Maki's script that comes with Cache::Memcached::libmemcached, tools/benchmark.pl (which I modified to only compare Cache::Memcached to Cache::Memcached::libmemcached)

==== Benchmark "Simple get() (scalar)" ====
Rate perl_memcached libmemcached
perl_memcached 6784/s -- -78%
libmemcached 30488/s 349% --
==== Benchmark "Simple get_multi() (scalar)" ====
Rate perl_memcached libmemcached
perl_memcached 1806/s -- -84%
libmemcached 11494/s 537% --
==== Benchmark "Serialization with get()" ====
Rate perl_memcached libmemcached
perl_memcached 6402/s -- …

tl;dr: The MySQL community rocks. Percona, XtraDB, Drizzle, SSD storage, InnoDB IO scalability challenges.

For anyone who lives and dies by MySQL and InnoDB, things are finally starting to heat up and get interesting. I’ve been banging the “MySQL/InnoDB scales poorly” drums for years now, and despite having paid Enterprise licenses, I haven’t been able to get anywhere. I was pretty excited when Sun …

After getting sysbench running properly with a scalable memory allocator (see last post), I can now return to what I was originally testing - what memory allocator is best for the 5.1 server (mysqld).

This stems out of studies I have made of some patches that have been released by Google. You can read about the work Google has been doing here.

I decided I wanted to test a number of configurations based on the MySQL community source, 5.1.28-rc, namely:

• The baseline - no Google SMP patch, default memory allocator (5.1.28-rc)
• With Google SMP patch, mem0pool enabled, no custom malloc (pool)
• With Google SMP patch, mem0pool enabled, linked with mtmalloc (pool-mtmalloc)
• With Google SMP patch, mem0pool disabled, linked with tcmalloc (TCMalloc)
• With Google SMP patch, mem0pool …

After getting sysbench running properly with a scalable memory allocator (see last post), I can now return to what I was originally testing - what memory allocator is best for the 5.1 server (mysqld).

This stems out of studies I have made of some patches that have been released by Google. You can read about the work Google has been doing here.

I decided I wanted to test a number of configurations based on the MySQL community source, 5.1.28-rc, namely:

• The baseline - no Google SMP patch, default memory allocator (5.1.28-rc)
• With Google SMP patch, mem0pool enabled, no custom malloc (pool)
• With Google SMP patch, mem0pool enabled, linked with mtmalloc (pool-mtmalloc)
• With Google SMP patch, mem0pool disabled, linked with tcmalloc (TCMalloc)
• With Google SMP patch, mem0pool …

After getting sysbench running properly with a scalable memory allocator (see last post), I can now return to what I was originally testing - what memory allocator is best for the 5.1 server (mysqld).

This stems out of studies I have made of some patches that have been released by Google. You can read about the work Google has been doing here.

I decided I wanted to test a number of configurations based on the MySQL community source, 5.1.28-rc, namely:

• The baseline - no Google SMP patch, default memory allocator (5.1.28-rc)
• With Google SMP patch, mem0pool enabled, no custom malloc (pool)
• With Google SMP patch, mem0pool enabled, linked with mtmalloc (pool-mtmalloc)
• With Google SMP patch, mem0pool disabled, linked with tcmalloc (TCMalloc)
• With Google SMP patch, mem0pool …

I have created a new tool, called xtstat, for analyzing the performance of the PBXT storage engine.

The way it works is simple. PBXT now counts all kinds of things: transactions committed and rolled back, statements executed, records read and written, tables and indexes scanned, bytes read, written and flushed to various types of files: record, index, data logs, transaction logs, and so on.

A SELECT on the system table PBXT.STATISTICS (or INFORMATION_SCHEMA.PBXT_STATISTICS if PBXT was built inside the MySQL tree) returns the current totals of all these counters. xtstat does a SELECT every second on this table and prints the difference. In this way, you can see how much work PBXT is doing in each area.

There are currently 48 different statistics:


To ensure all this counting does not itself cost any performance, each thread counts for itself, so no locking is required. The SELECT on …

My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …