Mikael Ronstrom

MySQL Thread Pool: Storage Engines

2011-11-01T16:28:00.000+01:00

I got a question from the NDB folks that are currently adapting MySQL Cluster to
MySQL 5.5 about whether any special developments are needed to adapt the NDB
storage engine for use with the thread pool. Then I realised there are more
people out there that write storage engines that want to know how to optimise
their storage engines for the thread pool.

So first of all any storage engine will work with the thread pool as they are today
without any modifications. It is however possible to improve the performance of the
MySQL Server when using the thread pool by adapting the storage engine to the
thread pool APIs.

The new API that has been added to the MySQL 5.5 server is the thd_wait interface.
This interface makes it possible for storage engines to report to a thread pool
plugin before starting a wait and after finishing a wait.

As an example, we have adapted the InnoDB storage engine by adding the thd_wait
interface calls around row locks in InnoDB and before file IO due to misses in
the InnoDB buffer pool. The InnoDB code have also been changed to make those
callbacks as part of the implementation of the --innodb-thread-concurrency and
when waiting for flushes of the buffer pool as part of checkpoints and other
activities where writes are required to ensure proper operation of InnoDB.

The NDB storage engine has very different reasons for the waits, the NDB storage engine
implements the actual data management in the NDB data nodes (these nodes runs in
separate processes separate from the MySQL Server), thus the only reason for waits
in the MySQL Server is when we're waiting for packets to return from the NDB data nodes.

Most third-party storage engines probably fit fairly well with InnoDB and/or NDB in how
they are integrated with the thread pool plugin. So there are storage engines that
perform all the work inside the MySQL Server. The more advanced such engines are likely
to also have a buffer pool and thus should consider calling the thd_wait interface
when doing IO, these storage engines are also likely to acquire row locks or some
similar level of data lock that sometimes will require an extended wait. There are
also other storage engines that are distributed in nature such as NDB, these
storage engines will want to make the callbacks to the new thread pool API when
waiting for responses on the network.

For storage engines that implement some data structure similar to the THD object in the
MySQL Server, there is one additional thing to consider. When using a thread pool it
makes sense to consider pooling such objects given that the thread pool
will pool threads. As an example, we have such an object called Ndb in the NDB API that
has the potential to be pooled. The benefits of pooling such objects are that it
means less time to create them, less memory usage and thus fewer CPU cache misses
due to their usage.

The thd_wait interface is really simple. It contains two calls thd_wait_begin and
thd_wait_end. Both calls have the THD object as the first parameter. Often the THD
object isn't known in the storage engine code when needed. In this case one simply
uses NULL as the THD object. The thd_wait interface can even handle the case where
the thd_wait interface is used from threads that are private to the storage engine.
The thread pool will discover that there is no THD object attached to the thread
and ignore the call.

The thd_wait_begin call also have a second parameter that specifies the type of
wait that will show up in the thread pool information schema tables. There
will be statistics on waits per type. There are currently 10 wait types.

To see an example of usage of this interface, search for thd_wait in the InnoDB
storage engine source code in the MySQL 5.5 community server.

The MyISAM storage engine does not use this API because MyISAM relies on the
MySQL Server for locking. Also, MyISAM assumes that the OS takes care of caching
of pages. This means that there is a very high probability that writes to the
file system are handled directly in the file system cache without involving any
long waits.

What is effect of not modifying a storage engine to implement the thd_wait
interface? The thread pool operates by trying to always have one thread active
per thread group. If the active thread is blocked and the thread pool is informed
of the block, then the thread pool can start another thread to ensure that the
thread group is being efficiently used. If the storage engine is not modified to
implement the thd_wait interface, the thread pool is not informed of the block.
In this case, the thread group will be blocked for a while until the wait is
completed or until the query is defined as stalled. The throughput of the system
can to some extent be handled in those cases by increasing the number of thread
groups.

So implementing the thd_wait interface means better throughput and also less
variance of the throughput and waiting times.

To use these interfaces in a file, include two header files (the thd_wait interface is
part of the plugin APIs in the MySQL 5.5 community and commercial servers).

#include "mysql/plugin.h"
#include "mysql/service_thd_wait.h"

Below is the most important information in these header files.

typedef enum _thd_wait_type_e {
THD_WAIT_SLEEP= 1,
THD_WAIT_DISKIO= 2,
THD_WAIT_ROW_LOCK= 3,
THD_WAIT_GLOBAL_LOCK= 4,
THD_WAIT_META_DATA_LOCK= 5,
THD_WAIT_TABLE_LOCK= 6,
THD_WAIT_USER_LOCK= 7,
THD_WAIT_BINLOG= 8,
THD_WAIT_GROUP_COMMIT= 9,
THD_WAIT_SYNC= 10,
THD_WAIT_LAST= 11
} thd_wait_type;
void thd_wait_begin(MYSQL_THD thd, int wait_type);
void thd_wait_end(MYSQL_THD thd);

THD_WAIT_SLEEP: For uninterrupted sleeps.
THD_WAIT_DISKIO: For file IO operations that are very likely to cause an actual
disk read.
THD_WAIT_ROW_LOCK: For row locks/page locks in the storage engine.
THD_WAIT_GLOBAL_LOCK: For global locks such as the global read lock in the MySQL
Server.
THD_WAIT_TABLE_LOCK: When waiting for a table lock.
THD_WAIT_META_DATA_LOCK: For waiting on a meta data lock which isn't a table lock.
THD_WAIT_USER_LOCK: For some type of special lock.
THD_WAIT_BINLOG: When waiting for the replication binlog.
THD_WAIT_SYNC: When waiting for an fsync operation.

It's quite likely we will introduce more wait types, such as the wait on the network.

MySQL Thread Pool: Summary

2011-10-27T16:45:00.000+02:00

A number of blogs have been written with the intent of describing how
the thread pool manages to solve the requirements of the thread pool.

These blogs are:
MySQL Thread Pool: Problem Definition
MySQL Thread Pool: Scalability Solution
MySQL Thread Pool: Limiting number of concurrent statement executions
Automated benchmark tool for DBT2, Sysbench and flexAsynch
MySQL Thread Pool: Limiting number of concurrent transactions
MySQL Thread Pool: When to use?
MySQL Thread Pool vs. Connection Pool
MySQL Thread Pool: Optimal configuration
MySQL Thread Pool: Benchmarking

There are some interesting discussions in the comments on the scalability solution blog
and on the blog about limiting number of concurrent statement executions
and finally also on the blog about when to use.

These discussions are around when to use it, what other features might be worth
considering and some remarks on the type of benchmarks that could be used to
evaluate solutions.

The requirements we had on the thread pool solution and the solutions were:
1) Split threads into groups individually handled to avoid making the
solution a problem in itself, aim is to manage one active thread per
group.

Solution:
Connections are put into a thread group at connect time by round robin.
Configurable number of thread groups. This ensures that the thread pool
itself isn't a scalability hog.

2) Wait for execution of a query until the MySQL Server has sufficient
CPU and memory resources to execute it.

Solution:
Each thread group tries to keep the number of executing queries to one or
zero. If a query is already executing in the thread group, put connection
in wait queue.

3) Prioritize queries on connections that have an ongoing transaction.

Solution:
Put waiting connections in high priority queue when a transaction is
already started on the connection.

4) Avoid deadlocks when queries are stalled or execute for a long time.

Solution:
Allow another query to execute when the executing query in the thread
group is declared as stalled (after a configurable time).

MySQL Thread Pool: Benchmarking

2011-10-26T15:04:00.000+02:00

We have executed a number of benchmarks using the thread pool to
see how it operates in various workloads. A thorough study on this
can be found in Dimitri's blog here.

Optimal number of active connections is the number of active connections
needed to achieve the best throughput for the MySQL Server. For an InnoDB
workload this is usually around 32-128 active connections.

From all our benchmarks we've seen that the performance of the thread pool
when operated with less than the optimal number of active connections is
about 1-3% slower than without thread pool since the behaviour is the same
and the thread pool adds a little bit more overhead. More or less all of
this overhead is to handle KILL query correctly.

When operated in the region of the optimal number of active connections
the performance is very similar. We have seen though that the thread pool
benefits very much from locking the MySQL Server to a number of CPUs
equal to the setting of the thread_pool_size configuration parameter.
When not locked to CPUs the performance is similar, when locked to CPUs
the thread pool gives 10-15% higher performance when using the optimal
number of active connections. The MySQL Server operated without thread
pool and locked to CPUs have no significant change of throughput compared
to not locking to CPUs.

When operating above optimal number of connections the thread pool
provides a great benefit, we've seen numbers all the way up to 100x
better performance when operating with a few thousand concurrently
active connections.

MySQL Thread Pool: Information Schema Tables

2011-10-26T10:55:00.000+02:00

The thread pool have three information schema tables. These are TP_THREAD_STATE,
TP_THREAD_GROUP_STATE and TP_THREAD_GROUP_STATS.

The TP_THREAD_STATE table contains one row per thread that is currently
maintained by the thread pool. This row contains only interesting information
if the thread is actively executing a statement. In this case it contains information
how many 10 milliseconds slots the query has consumed, if the thread is blocked by
some event, the event is listed. Both of those information items are current state
and will change for each new query.

The TP_THREAD_GROUP_STATE table contains one row per thread group. It contains
information about number of threads of various types. The first type is consumer
threads, this is a thread not used for the moment, at most 1 such thread will
exist at any point in time. This is the next thread to use if the current threads
used are not enough and a new thread is needed.

The second type of threads are reserved threads, these are also threads not currently
used. They will be used when there is no consumer thread and a new thread needs to be
started.

It contains information about the current number of connections handled in this thread
group. It contains current information about the number of queued low priority
statements (QUEUED_QUERIES) and queued high priority statements (QUEUED_TRANS).

It contains information about configuration, thus state of stall limit, priority
kickup timer, algorithm used. Also information about current number of threads in
the thread group, current number of threads actively executing a statement in the
thread group and current number of stalled statement executions.

Finally it contains some useful information about thread number of a possible
waiter thread (the thread that listens to incoming statements), information about
the oldest query that is still waiting to be executed.

The last table is the TP_THREAD_GROUP_STATS that contains statistics about the
thread group.

There are statistics about number of connections, number of connections closed,
number of queries executed, number of queries stalled, number of queries queued,
number of queries that was kicked up in priority from low priority to high priority.

There is also statistics on threads, how many threads have been started, how many
threads have become consumer threads, become reserve threads, become waiter threads.
How many times the thread that checks for stalled threads decided to start a thread
to handle the possibility of executing a query.

Finally there is statistics about each blocking event coming from the MySQL Server
(meta data locks, row locks, file IO, sleeps and so forth).

One of the most important information here is the number of stalled queries
(STALLED_QUERIES_EXECUTED in TP_THREAD_GROUP_STATS), this counter
gives a good idea if we have many stalled queries, if there are too many such
queries, it is a good indication that one should consider increasing the
thread_pool_stall_limit.

Another very important information is the number of priority kickups
(PRIO_KICKUPS in TP_THREAD_GROUP_STATS). If this counter
grows too quick it is an indication that the thread_pool_prio_kickup_timer
might need to be higher.

It might at times be important to check the number of threads started
(THREADS_STARTED in TP_THREAD_GROUP_STATS) as well.
If the threads are started too often, it's a good indicator that we should
not be so aggressive in stopping threads and thus set thread_pool_max_unused_threads
a bit higher.

The current oldest waiting query might also be a good idea to track to ensure that
we don't get longer waits than what is acceptable. If we get too long waits here,
one can either change some configuration variable, but it might also be an indicator
that the MySQL Server is constantly overloaded and that some action should be done
to remedy this.

MySQL Thread Pool: Optimal configuration

2011-10-25T13:09:00.000+02:00

The thread pool plugin has a number of configuration parameters that will affect
its performance. These are documented in the MySQL manual here.

To configure the thread pool for optimal operation the most important parameter is
the --thread_pool_size. This parameter specifies the number of thread groups that
the thread pool will create.

The default value of thread_pool_size=16 is very often a good starting point. We have
seen that for InnoDB Read Workloads it is sometimes possible to achieve even better
results when it is set to around 30-40. For write intensive InnoDB workloads the
optimum can be in the range 12-30. MyISAM workloads usually have an optimum a bit
lower in the range of 6-8. The default value of 16 will work well also for most
MyISAM workloads.

The next parameter to consider for optimum operation is --thread_pool_stall_limit.
This is set to 6 (=60ms) by default. This number is set very low for good operation
in most cases. In most cases with workloads that don't have very many long queries
it is ok to set this much higher. Setting it to 100 (=1 second) should be ok in most
cases.

In the information schema one can see how many queries are stalled, if there are too
many queries stalled, then it is a good idea to increase this parameter since stalled
queries lead to increased context switching activity and more threads to manage for the
operating system.

The next parameter --thread_pool_prio_kickup_timer is set rather high to 1000
(=1 second). This setting should be ok for most cases, in extremely loaded environments
where thousands of connections want to execute at the same time it's necessary to
increase this variable to ensure that queries aren't moved too early. At the same time
setting it too high means that long-running transactions can block out short transactions
too much. But settings up to 10000 (=10 seconds) should in most cases be ok.

There is a parameter which isn't supported --thread_pool_algorithm. This parameter
makes it possible to use a bit more aggressive scheduling algorithm in the thread pool.
In most cases this has no benefits other than in some cases achieving better results.
It has been left accessible if someone wants to experiment with it and give us feedback
about it.

The last parameter is --thread_pool_max_unused_threads. This parameter specifies the
maximum amount of unused threads we will keep per thread group. It's possible to have
quite a few unused threads and to ensure that we give back memory to the operating
system one can use this parameter. By default it's 0 which means that threads are never
released and kept around for future use. Setting to a nonzero value means that the server
will use less memory but can also attribute to a higher CPU overhead to create new
threads again later on.

MySQL Thread Pool vs. Connection Pool

2011-10-24T17:31:00.001+02:00

Given that thread and connections in the MySQL Server
have been so intertwined, it is easy to confuse the
purpose of the MySQL Thread Pool and the purpose of
a Connection Pool.

The aim of a Connection Pool is that the MySQL
clients should not be forced to constantly do connect and
disconnect. Thus it is possible to cache a connection in
the MySQL client when a user of the connection no longer
needs it. Thus another user that needs a connection to the
same MySQL Server can reuse this cached connection later on.

This saves execution time in both the client and the server.
It does however not change the dynamics of how many queries
are executed in parallel in the MySQL Server. This means that
the likelihood of too many concurrent queries to execute in
the MySQL Server is the same with or without a Connection
Pool.

Also a Connection Pool operates on the client side. This
means that it doesn't see the state of the MySQL Server when
deciding whether to send a query to the MySQL Server or not. Thus
it doesn't have the required information to decide whether to
queue a query or not. Only the MySQL Server have this information
and thus the MySQL Thread Pool has to operate in the MySQL Server.
It cannot perform its task on the client side.

Thus it is easy to see that the MySQL Thread Pool and a
Connection Pool are orthogonal and can be used independent of
each other.

MySQL Thread Pool: When to use?

2011-10-24T13:07:00.000+02:00

The most important variable to monitor is threads_running. This
variable keeps track of the number of concurrent statements
currently executing in the MySQL Server.

If this variable has spikes that put it in a region where the
server won't operate optimally (usually going beyond 40 for
InnoDB workloads) and most particular if it goes well beyond
this into the hundreds or even thousands of concurrent
statements then the thread pool will be something beneficial
to protect the MySQL Server from troubles in overload
situations.

Another indicator that you will benefit from use of the thread
pool is when you already now use the --innodb-thread-concurrency
variable. This variable tries to solve a similar problem and the
thread pool solves it at a better place even before query execution
has started and also provides additional benefits.

Also if your workload is mainly short queries then the thread
pool will be beneficial, long queries isn't bad for the thread
pool but will decrease its positive impact.

MySQL Thread Pool: Limiting number of concurrent transactions

2011-10-21T19:39:00.000+02:00

There are hot spots in the MySQL Server that become hotter when many
transactions are handled concurrently. This means that it is imperative
to avoid having too many concurrent transactions executing in parallel.

The thread pool handles this by prioritizing queued queries according
to whether they have already started executing a transaction or not.
It is also possible for the user to decide that a connection will be of
high priority independent of whether a transaction is started or not.

Such a prioritization can have issues with livelock if there are transactions
that are very long. To avoid this problem a query will be moved to the high
priority queue after a configurable time have expired. This time is set in the
configuration parameter --thread_pool_prio_kickup_timer
(number of milliseconds before a query is kicked up).

However to avoid too many movements in a short time, the thread pool will
at most move one query per 10 milliseconds per thread group.

It is possible for the user to define his connection as always being of
high priority to ensure queries from that connection always moves faster
through the server.

Automated benchmark tool for DBT2, Sysbench and flexAsynch

2011-10-21T17:50:00.000+02:00

A new benchmark tool is available here to enable automated
benchmark runs of DBT2, Sysbench and flexAsynch using MySQL
and MySQL Cluster.

The benchmark tool is based on dbt2-0.37 for DBT2, sysbench-0.4.12
for sysbench benchmarks and a flexAsynch program that is available in
MySQL Cluster source releases (the version needed for the automated
flexAsynch tests requires an updated version of flexAsynch.cpp which
hasn't been included in a MySQL Cluster release yet, a blog post
notifying when it arrives will be written).

The automation scripts are part of the dbt2-0.37.50.tar.gz package.
This package is needed to run all benchmarks. In addition a gzipped
source or binary tarball of MySQL or MySQL Cluster is required to
run the benchmarks. Finally to run sysbench benchmarks one also needs
to download the sysbench-0.4.12.5 tarball.

So assuming you have downloaded all those tarballs, how does one
run a sysbench benchmark on your local machine?

The first step is to create a benchmark directory, I usually use
$HOME/bench or /data1/bench. In this directory create a directory
tarballs. Place all three tarballs in this directory. Go into this
directory and unpack the dbt2 tarball through tar xfz dbt2-0.37.50.tar.gz.
Then copy the benchmark start script into the $HOME/bench directory
through the command:
cp $HOME/bench/tarballs/dbt2-0.37.50/scripts/bench_prepare.sh $HOME/bench/.

Then copy the example configuration file in the same manner using the
command:
cp $HOME/bench/dbt2-0.37.50/examples/autobench.conf $HOME/bench/.

Edit the autobench.conf to be conformant to your file system environment.
The example configuration file assumes the use of /data1/bench as the
directory to use.

Now it is time to prepare to run a benchmark, create a directory under
$HOME/bench for the test run. So for example if you want to call it
test_sysbench then run the command:
mkdir $HOME/bench/test_sysbench

Next step is to copy the autobench.conf file into this directory and
edit it.
cd $HOME/bench
cp autobench.conf test_sysbench/.

Now there are two way to go about editing this configuration file. If
you want to go fast and unsafe, then go ahead and edit the file
directly, there is a fair amount of explanations of the various
parameters in this file. If you want more help, then read the
dbt2-0.37.50/README-AUTOMATED to get more directions about how to
set-up the configuration file properly.

Now everything is ready to run the benchmark, this is done through
the commands:
cd $HOME/bench
./bench_prepare.sh --default-directory $HOME/bench/test_sysbench

If you want to follow the development of the benchmark in real-time
you can do this by issuing tail -f on the proper file. For sysbench
RO benchmarks there will be a file called
$HOME/bench/test_sysbench/sysbench_results/oltp_complex_ro_1.res
for the first test run (you can tell the benchmark to do several
runs). Do tail -f on this while the benchmark is running and you'll
get printouts from the sysbench program written on your console.
Among other things you'll get a string like this:
Intermediate results: 128 threads, 3564 tps
if the current running test uses 128 concurrent connections. By
default the intermediate results are reported every 3 seconds.

The final result will be reported in the file
$HOME/bench/test_sysbench/final_result.txt

An additional note is that if you want to test the flexAsynch
tests, then it is necessary to use a source tarball of the
MySQL Cluster 7.x series. This is simply because the flexAsynch
program isn't distributed in binary tarballs.

The benchmark script will take care of the build process for all
source tarballs, all important parameters you need to handle
is part of the autobench.conf script. You will however need to
install the proper compilers and build tools to enable builds of
MySQL, sysbench and DBT2 programs.

If you want to benchmark a MySQL Server using the thread pool, then
it is necessary to download a MySQL Enterprise Edition of the MySQL
Server. If you already have a commercial license with Oracle, then
simply use this to download the MySQL binary tarball through the
edelivery.oracle.com. If you don't have a commercial license, you
can use the Oracle Software Delivery Cloud Trial License Agreement
which gives you a 30-day trial license. So to get the binary tarball
go to edelivery.oracle.com, register if necessary, log in, answer
all required license agreements.

Next step is to select MySQL Database as the Product Pack and as
Platform select Linux x86-64. Finally download the TAR file for
generic Linux2.6 x86-64 platforms. When this download is completed
then unzip the file and you'll get the gzipped tarball you need
to run the thread pool benchmark.
Linux x86_64.

The sysbench contains a few extra features compared to the
sysbench-0.4.12 version. It contains support for intermediate
result reporting, support for multiple tables in the sysbench
benchmark, support for partitioned tables, support for using
secondary indexes, support for using HANDLER statements instead
of SELECT statements, and also support for running sysbench at
fixed transaction rates with a certain jitter.

DBT2 can in addition to running with a single MySQL Server also
run with multiple MySQL Servers when used with MySQL Cluster.
It contains a few new features here to control partitioning,
possibility to place the ITEM table in each MySQL Server and
so forth.

All scripts and many programs have updated parameters and all
scripts have extensive help outputs to make it easy to understand
what they can do.

It is fairly easy to extend the benchmark scripts. As an example
if you need to change a parameter which isn't included in
autobench.conf then first add it in bench_prepare.sh and then
add code to handle it in start_ndb.sh. Also update the
autobench.conf example file if you want to keep the feature
for a longer time. If you want to suggest changes to the
scripts please report it in My Oracle Support (support.oracle.com)
or in bugs.mysql.com and assign it to Mikael Ronstrom.

MySQL Thread Pool: Limiting number of concurrent statement executions

2011-10-21T13:56:00.000+02:00

The main task of the thread pool is to limit the number of
concurrent statement executions. The thread pool achieves
this by trying to always operate a thread group such that
only one or zero queries are concurrently executed per
thread group.

There is however a livelock issue to consider. A long-running
query in a thread group will in this manner block out all
other queries in this thread group until the query is completed.

To resolve this issue there will be a configurable timer that
decides when a statement execution is declared as stalled. When
a query is declared as stalled, it is allowed to continue
executing until completed. The thread group will handle the
connection as stalled and not count it as an active connection.
Thus new queries can be executed in the thread group again when a
query have been declared as stalled.

Another issue is when a statement execution is blocked for some
reason. Queries can be blocked e.g. by Row Locks, File IO, Table
Locks, Global Read Locks and so forth. If it is likely that the
blockage will continue for at least a millisecond or so, then it
makes sense to start up another statement execution in the thread
group to ensure that we continue to keep the number of concurrent
active connections at the right level.

To enable this the MySQL Server will make callbacks to the thread
pool stating when a block begins and when it ends. The thread
pool will use this to keep track of number of active statement
executions and this is used to decide when to start a new query
and when to allow an incoming query to start.

It is important that the wait is sufficiently long since it is
necessary to immediately continue executing the query when the
blockage ends.

MySQL Thread Pool: Scalability solution

2011-10-21T11:08:00.000+02:00

When implementing a thread pool or any other means of limiting concurrency in the MySQL Server, careful thought is required about how to divide the problem to ensure that we don't create any unnecessary new hot spots. It is very easy to make a design that manages all connections and threads in one pool. This design does however very quickly run into scalability issues due to the need to lock the common data structures every time a connection or thread needs to change its state.

To avoid this issue we decided to implement the thread pool using a set of thread groups. Each of those thread groups are independent of the other thread groups. Each thread group manages a set of connections and threads. It also handles a set of queues and other data structures required to implement the thread group operations. Each thread group will contain a minimum of one thread, connections will be bound to a thread group at connect time using a simple round robin assignment. The thread pool aims to ensure that each thread group either has zero or one thread actively executing a statement. This means that the interactions between threads within one thread group is extremely limited. Also the interactions won't grow as the MySQL Server gets more statements to process. Thus it is very hard to see this model become a scalability issue in itself.

So we solved the scalability problem using a Divide-and-Conquer technique.

MySQL Thread Pool: Problem Definition

2011-10-18T16:03:00.001+02:00

A new thread pool plugin is now a part of the MySQL Enterprise Edition.
In this blog we will cover the problem that the thread pool is solving
and some high-level description of how it solves this problem.

In the traditional MySQL server model there is a one-to-one mapping between
thread and connection. Even the MySQL server has lots of code where thread
or some abbreviation of thread is actually representing a connection.
Obviously this mapping has served MySQL very well over the years, but there
are some cases where this model don't work so well.

One such case is where there are much more connections executing queries
simultaneously compared to the number of CPUs available in the server. The
MySQL Server also have scalability bottlenecks where performance suffers
when too many connections execute in parallel.

So effectively there are two reasons that can make performance suffer in
the original MySQL Server model.

The first is that many connections executing in parallel means that the
amount of data that the CPUs work on increases. This will decrease the
CPU cache hit rates. Lowering the CPU cache hit rate can have a significant
negative impact on server performance. Actually in some cases the amount
of memory allocated by the connections executing in parallel could at times
even supersede the memory available in the server. In this case we enter a
state called swapping which is very detrimental to performance.

The second problem is that the number of parallel queries and transactions
can have a negative impact on the throughput through the "critical sections"
of the MySQL Server (critical section is where mutexes are applied to
ensure only one CPU changes a certain data structure at a time, when such
a critical section becomes a scalability problem we call it a hot spot).
Statements that writes are more affected since they use more critical
sections.

Neither of those problems can be solved in the operating system scheduler.
However there are some operating systems that have attempted solving this
problem for generic applications on a higher level in the operating system.

Both of those problems have the impact that performance suffers more and
more as the number of statements executed in parallel increases.

In addition there are hot spots where the mutex is held for a longer time
when many concurrent statements and/or transactions are executed in
parallel. One such example is the transaction list in InnoDB where each
transaction is listed in a linked list. Thus when the number of concurrent
transactions increases the time to scan the list increases and the time
holding the lock increases and thus the hot spot becomes even hotter
as the concurrency increases.

Current solutions to these issues exist in InnoDB through use of the
configuration parameter --innodb-thread-concurrency. When this parameter
is set to a nonzero value, this indicates how many threads are
able to run through InnoDB code concurrently. This solution have its
use cases where it works well. It does however have the drawback that
the solution itself contains a hot spot that limits the MySQL server
scalability. It does also not contain any solution to limiting the
number of concurrent transactions.

In a previous alpha version of the MySQL Server (MySQL 6.0) a thread
pool was developed. This thread pool solved the problem with limiting
the number of concurrent threads executing. It did nothing to solve
the problem with limiting the number of concurrent transactions.
It was also a scalability bottleneck in itself. Finally it didn't
solve all issues regarding long queries and blocked queries.
This made it possible for the MySQL Server to become completely
blocked.

When developing the thread pool extension now available in the MySQL
Enterprise Edition we decided to start from a clean plate with the
following requirements:

1) Limit the number of concurrently executing statements to ensure
that each statement execution has sufficient CPU and memory resources
to fulfill its task.

2) Split threads and connection into thread groups that are
independently managed. This is to ensure that the thread pool
plugin itself doesn't become a scalability bottleneck. The
aim is that each thread group has one or zero active threads
at any point in time.

3) Limit the number of concurrently executing transactions
through prioritizing queued connections dependent on if
they have started a transaction or not.

4) Avoid deadlocks when a statement execution becomes long or
when the statement is blocked for some reason for an extended
time.

If you are interested in knowing more details of how the new
thread pool solves these requirements there will be a
webinar on Thursday 20 Oct 2011 at 9.00 PDT. Check here
for details on how to access it.

If you want to try out the thread pool go here.

Better than linear scaling is possible

2011-05-26T11:39:00.003+02:00

As part of my research for my Ph.D. thesis, I spent a lot of time
understanding the impact of CPU caches on the performance of a DBMS.
I concluded that in a parallel data server it is actually possible
to get better than linear scaling in certain workloads.

When executing a benchmark with 2 machines consisting of 8 cores where
those 8 cores share a 2 MByte cache has a total of 4 MByte CPU cache.
Assuming that the benchmark executes with a data set of 2 GByte, then
0.1% of the data fits in the CPU cache. As the number of machines grow,
the available CPU caches also grows, this means that when we have
32 machines, we have 64MByte of cache available. This means that we can
now store 1.6% of the data set in the CPU cache.

For benchmarks one mostly tries to scale the data set size when increasing
the number of nodes in the system. This is however not necessarily true in
real-life applications. For real-life applications the working set is
constant, the working set can grow in time as more customers join the service
or for other reasons. But the working set of a real life application doesn't
grow when you grow the number of machines in the database cluster.

It's very well known that there are many things that drives sublinear scaling,
the most important of those is the extra cost of communication in a larger
cluster. The number of communication lanes in a fully connected cluster is
n * (n - 1) / 2. This means that the number of communication lanes grow by
the square of the number of machines, O(n^2). The communication only
increase linearly in number of machines which means that each lane gets
linearly less bytes to communicate in a larger cluster. Given that
communication cost is fixed_cost + #bytes * cost_per_byte, this means
that the cost per byte sent will increase in a larger cluster since there
will be smaller packets and thus fewer bytes to pay for the fixed cost.

The above is one reason why sharding is a good idea, this means that we
partition the problem, thus we only use a subset of the communication lanes
and thus we avoid the increased cost of communication as the number of
machines grows. Obviously sharding also imposes limitations to the type of
queries you can handle efficiently.

Now to some specific facts about MySQL Cluster and why we can obtain
bettter than linear scaling here (reported in earlier blogs here and here).
For reads here we got 1.13M on 8 nodes, 2.13M on 16 nodes and 4.33M
reads on 32 nodes. For updates we got 687k on 4 nodes, 987k on 8 nodes
and finally 2.46M on 16 nodes. All the data in this benchmark was also
replicated.

The data nodes in MySQL Cluster use an architecture where we have up to
4 threads that handle the local database handling. These 4 threads handle
their own partitions. Next we have one thread that handles the transaction
coordinator role, we also have one thread that takes care of the receive
part of the communication. Finally we have a set of threads taking care
of file system communication. What this effectively means is that as we
grow the cluster size and the cost of communication grows, each data node
will consume more CPU power, however the architecture of MySQL Cluster
is done in such a way that this extra CPU power is spent in its own
CPU cores. Thus we simply use a bit more of the CPU cores for
communication when the cluster size grows.

The benefit of this approach is that it is easy to scale the number
of CPU cores used for communication. Given that modern machines often
comes with quite high number of CPU cores, this means that as machines
gets beefier, we can actually deliver better than linear scaling of
the workload one can achieve by growing the number of data nodes in
MySQL Cluster.

In MySQL Cluster each execution thread has its own scheduler.
This scheduler becomes more and more efficient as load grows for
two reasons. The first is that as the load grows, the queue is
longer and thus we need to refill the queue fewer times,
this means that we spend more time executing the same code over
and over again. This means that the instruction cache for that
code will be very hot and we will train the branch predictor
subsystem in the CPUs very well. This benefit we get both in
the code refilling the queue and the code to execute the actual
database workload. Given that the load is high we also avoid
running code that checks for messages and there is no messages
around. Thus as load increases the efficiency increases and
the actual number of instructions to execute per message also
decreases.

So when I presented this theory at the presentation of my
Ph.D. thesis this was only a theory. In the real world it's
very uncommon to see the effect of CPU caches and other effects
being greater than the added burden of a larger cluster. However
I have seen it twice in my career. The first was a benchmark
performed in 2002 on a very large computer where we hosted 32 nodes
(single CPU nodes in those days) and 23 benchmark applications.
Here we scaled from 0.5 million to 1.5 million going from
16 to 32 nodes. Now also in the results presented at the
MySQL Users conference and in my previous blogs we achieved better
than linear scaling in particular for the write benchmark, but also
to some extent for read benchmarks. I am sure the above isn't the
entire explanation of these effects, but the effects of the things
explained above certainly plays a role in it.

MySQL Cluster: Designed for high-scale SQL and NoSQL web applications

2011-04-12T13:17:00.000+02:00

As shown in a number of blogs, the MySQL Cluster SW already uses the type of features found in many NoSQL products. It has an extremely efficient API through which it is possible to shoot millions of reads and writes towards a Cluster per second. It contains partitions of its data similar to shards in NoSQL and supports both high availability of those partitions and also repartitioning of the data when new nodes are added to the Cluster. Advanced replication solutions both providing replication inside a Cluster and between Clusters makes it possible to use MySQL Cluster in an a very large number of replication configurations, even scaling across multiple global data centers.

Finally MySQL Cluster makes it possible for you to choose to stay with your data in relational tables while still using NoSQL-like APIs, supporting on-line changes of partitioning and also adding new fields to tables while still reading and writing data in the tables. Using MySQL Cluster you can use MySQL APIs, the NDB API, Cluster/J, JPA, LDAP API and even more APIs are worked on and will soon be announced.

Most web data requires heavy use for generation of web pages where the use is mostly simple queries, but very many of them. Most of the web data also requires analysis to make intelligent business decisions based on the web generated data. A prototype of parallel query for MySQL Cluster was displayed at the MySQL Users Conference 2010. Tools such as this will also make it possible to analyse data efficiently in MySQL Cluster. Thus MySQL Cluster is a very efficient tool for working with many sorts of web data while retaining ACID compliance and a rich set of tools, expertise and best practices.

MySQL Cluster running 2.46M updates per second!

2011-04-12T13:15:00.002+02:00

In a previous blog post we showed how MySQL Cluster achieved 6.82M reads per second. This is a high number. However what is also very interesting to see is how efficient MySQL Cluster is at executing updating transactions as well. We were able to push through the 1M transactions per second wall and even past the 2M transactions per second and all the way up to 2.46M transactions per second.

MySQL Cluster doing 6.82M reads per second

2011-04-11T21:18:00.000+02:00

We ran a number of tests to see how many reads per second we could get from MySQL Cluster. We used a modified version of flexAsynch (as shown in previous blog), where each record read was 100 bytes in size.

With a cluster of 4 data nodes operating on 2 machines we were able to process 1.15M reads per second. On a cluster consisting of 8 data nodes executing on 4 machines we were able to process 2.13M reads per second. On a 16-data node cluster with 8 machines used for data nodes, we were able to process 4.33M reads per second and finally a cluster with 32 data nodes distributed on 16 machines we executed 6.82M reads per second. The tests were run on MySQL Cluster 7.1, we're confident that similar numbers can be achieved with MySQL Cluster 7.0 and also with the new beta version MySQL Cluster 7.2.

This benchmark will give you a good idea what can be achieved with direct usage of the NDB API, and using other APIs like Cluster/J, mod-ndb, NDB-memcached.

MySQL Cluster Benchmark

2011-04-11T14:59:00.000+02:00

We had the opportunity to use a fair amount of machines to run a benchmark to see what throughput MySQL Cluster can achieve on a bit bigger clusters. The benchmark we use is a benchmark we developed for internal testing many years ago and shows very well the performance aspects of MySQL Cluster as discussed in some previous blogs of mine.

The benchmark is called flexAsynch, it's part of an internal series of benchmark we call the flex-series of benchmarks. It's first member was flexBench, this benchmark consisted of the following simple set of operations. First create the table with the set of attributes and the size of the attributes as specified by the startup options. Next step is to create a set of threads as specified by the startup options. Next step is that each thread will execute a number of transactions, the number which is configurable and each transaction can also run one or more operations as configured (one operation is either an insert of one record, update of one record, read of one record or delete of one record). The flexBench benchmark always starts by doing a set of inserts, then reading those, updating each record, reading it again and finally deleting all records. The flexBench benchmark also consisted of a verify phase such that we could also verify that the cluster actually read and updated the records as they should.

The flexAsynch benchmark was a further development of this benchmark, flexBench uses the synchronous NDB API, where each transaction is sent and executed per thread. This means that we can have as many outstanding transactions to the cluster as we have threads. flexAsynch uses the asynchronous NDB API, this API provides the possibility to define multiple transactions and send and execute those all at once. This means that we can get a tremendous parallelism in the application using this API. The manner in which MySQL Cluster is designed, it is actually no more expensive to update 10 records in 10 different transactions compared to updating 10 records in 1 transaction using this API. Jonas Oreland showed in his blog post how one API process using this API can handle 1 million operations per second.

The main limitation to how many operations can be executed per second is the processing in the data nodes of MySQL Cluster for this benchmark. Thus we wanted to see how well the cluster scales for this benchmark as we add more and more data nodes.

A data node in MySQL Cluster operates best when threads are locked to CPUs as shown in a previous blog of mine. Currently the main threads that operates in a data nodes is the thread handling local database operations (there are up to four of those threads), the thread doing the transaction synchronisation and finally the thread handling receive of messages on sockets connected to other data nodes or API nodes. Thus to achieve best operation one needs at least 6 CPUs to execute a data node. Personally I often configure 8 CPUs to allow for the other threads to perform their action without inhibiting query performance. Other threads are handling replication, file system interaction and cluster control.

To our disposal when running this benchmark we had access machines with dual Intel Xeon 5670 @2.93 GHz. This means 12 CPUs per socket. One thing to consider when running a benchmark like this is that the networking is an important part of the infrastructure. We had access to an Infiniband network here and used IP-over-Infiniband as communication media. It's most likely even better to use the Sockets Direct Protocol (SDP) but we had limited time to set things up and the bandwidth of IPoIB was quite sufficient. This made it possible to have more than one data node per machine.

In order to run flexAsynch on bigger clusters we also needed to handle multiple instances of flexAsynch running in parallel. In order to handle this I changed flexAsynch a little bit to enable one process to only create a table or only delete a table. I also made it possible to run the flexAsynch doing only inserts, only reads or only updates. To make it easier to get proper numbers I used a set of timers for read and update benchmarks. The first timer specified the warmup time, thus operations were executed but not counted since we're still in the phase where multiple APIs are starting up. The next timer specifies the actual time to execute the benchmark and finally a third timer specifies the cooldown time where again transactions are run but nor counted since not all APIs start and stop at exactly the same time. Using this manner we will get accurate numbers of read and update operations. For inserts we don't use timers and thus the insert numbers are less accurate.

The results of those benchmarks will be posted in blogs soon coming out.

MySQL Cluster and Sharding

2011-04-11T13:56:00.000+02:00

Sharding is here defined as the ability to partition the data into partitions defined by a condition on a set of fields. This ability is central to the workings of MySQL Cluster. Within a Cluster we automatically partition the tables into fragments (shards in the internet world). By default there is a fixed amount of fragments per node. As mentioned we also use replication inside a Cluster, the replication happens per fragment. We define the number of replicas we want in the Cluster and then the MySQL Cluster SW maintains this number of fragment replicas per fragment. These fragment replicas are all kept in synch. Thus for MySQL Cluster the sharding is automatic and happens inside the Cluster even using commodity hardware.

One of the defining features of MySQL Cluster is to keep the fragments up and running at all times and that they are restored after a Cluster crash. However MySQL Cluster also supports adding nodes to the Cluster while it is operational, this means that we can add nodes on a running Cluster and repartition the tables during normal operation. This is part of the normal MySQL Cluster and is used in operation by many users and customers to increase the size of the Clusters in production clusters.

MySQL Cluster API, the NDB API

2011-04-11T09:26:00.000+02:00

As mentioned in a previous blog the programming API on the client side is a very important part of the performance of MySQL Cluster. Every API that is used to access the Data Server in MySQL Cluster uses the NDB API. The NDB API is used in the NDB storage handler to make it possible to access data from MySQL APIs which is residing in MySQL Cluster.

The base of the good performance of the programming API is the ability to batch operations in various manners. In early MySQL Cluster history the MySQL Storage Engine API had very few interfaces that allowed for handling multiple records at a time. As we progressed, the Storage Engine API have added several APIs that can handle multiple records at a time. There is even some development work which has been presented at the UC 2010 where the Storage Engine API now can push entire queries down to the storage engine, even join queries. This has also been presented at a recent webinar with engineers.

The NDB API uses a model where one first defines the operation to issue towards the database. The calls to build an operation doesn't interact with the actual database. The actual message is sent to the data node only after the execute method have been called. The NDB API is designed to handle batching of operations in two levels. The first level is that it is possible to batch inside one thread. This means that one can open several transactions in parallel within the same thread and execute them in parallel with one execute call. In addition it is also possible to have several threads working in parallel and it is possible for every one of those threads to also be executing multiple transactions in parallel.

So the possibilities for parallelism using the NDB API is tremendous. Much of the cost of accessing a database is paid in the networking, so by using the parallel transactions inside a thread (called Asynchronous NDB API) and by using the multithreaded capabilities of the NDB API, it is possible to decrease the networking cost greatly by making TCP/IP packets larger. Mostly the cost of sending a TCP/IP packet is Fixed_cost + #Bytes * Byte_cost. The fixed cost was in the past about the same cost as sending 60 bytes. This extra cost of small messages have to be paid both in the server part and in the client part. Thus it pays off very well to send larger messages. When the message sizes goes towards 1 kByte, the extra cost is down in the range of 6-7% extra cost compared to infinite-sized messages whereas a 200 byte message have an additional 30% added cost.

An additional benefit of batching is that there will be less context switches since handling of several messages in parallel can be handled without context switches.

You can learn more about performance optimization of your own applications by reading this whitepaper.

MySQL Cluster performance aspects

2011-04-09T08:51:00.000+02:00

MySQL Cluster was designed for high performance from the very beginning. To achieve high performance one has to understand many aspects of computing. As an example the protocol is very important. In the original design work in 1994 we had a master thesis student build a prototype using a protocol which was based on BER encoding and other standard parts of many telecom protocols. After seeing the code in this prototype which was several thousands of lines of code just to handle the protocol, I realised that this type of protocol will simply cost too much on both the client side as well as on the server side. So this type of prototypes in early design work is extremely useful since it would have been very difficult to change this protocol once we started down the path of developing the Data Server.

Based on this work we instead opted for a protocol where almost everything in the protocol was of fixed size and entirely based on sending 32-bit words. We didn't want a protocol which transferred bytes to avoid the extra computational complexity this would require. So the NDB protocol which is used for query processing uses a message called TCKEYREQ, this message has about 10 32-bit words describing various fixed parameters such as TableId, ConnectionId, PartitionId and so forth. There is also a 32-bit word that contains a set of bits that is used to interpret the message. Actually reading this protocol can be done, completely avoiding branches since the bits can be used to address the proper words in the protocol message through some arithmetic. The only branching needed happens in taking care of keys and the actual query information which is of variable size.

The next important component of performance is the execution model. The MySQL Cluster Data nodes uses an execution model which is extremely well suited for modern CPUs. The Data nodes uses a set of threads, where each thread implements its own little OS with a scheduler. All communication inside the data nodes is based on messages. From a SW point of view the code to receive internal messages is exactly the same as the handling of messages arriving over the network. When sending a message it's the address which defines the type of message. The address contains three parts, the node id, the thread id and the module id (block number in the code). If the message is sent to a module with the same node id and thread id as the sending thread, then the message is an internal message and it will be sent by putting the message in the local message buffer, if the node id is the same but the thread id differs, then the message will be sent to another thread. The communication between threads is extremely efficient based on shared memory communication and this code is using the most efficient ways to communicate based on the HW and the OS. Finally when the node id differs, the message is sent as a network packet over to another data node or client node. There is a TCP/IP link between all nodes (fully connected mesh) and the data node will use mechanisms to ensure that the packets sent contains as many messages as possible without sacrificing latency (the user can affect the acceptable latency through a config parameter).

Given this model it means that a thread can be actively executing thousands of queries without any need of doing any context switches. This is one reason why MySQL Cluster benefits greatly when threads are locked to certain CPU cores and there is no contention from other programs to use these CPU cores. The data nodes have their own local OS and thus work extremely efficiently when the OS scheduler stays out of the way.

This particular model of executing where each thread of execution executes until it decides to send a message (the unit of execution is always execution of a message) was very popular in the 70s because of its efficiency. It was replaced by the time-sharing model given the simplicity of the time-sharing model. When designing MySQL Cluster we decided that a Data Server to handle millions of queries per second has more requirements on the efficiency of execution compared to the requirements of the simplicity of the design. Another great benefit of this execution model is that as the load on the Data Server increases, the throughput also grows. This is so since the execution threads will execute for longer time before they will look at the sockets for incoming traffic, this means that more messages will be gathered every time and thus the cost of each message byte decreases, the same happens with sending messages that as the number of messages to execute per round grows, the more data will be sent on each send call and thus decreasing the cost of each sent message byte.

The design is extremely modular even though its using a more complex execution model. Each module can only communicate with other modules using messages and the modules share no data. Thus if an error occurs in a module it's either due to bugs in this model or due to bad input data to the module. To debug the data node we trace every important branch, every message executed with it's data. This means that if a crash occurs we have very detailed information about how the crash occurred including the last thousand or so branches taken in the code and a few thousand of the last messages executed in the data node.

The final aspect of performance is the actual implementation of the database algorithms. To cover this in one blog message is obviously not possible but it covers an efficient design of data structures (we implement a hash based index and an ordered index), efficient implementation of the actual record storage with an efficient data structure to contain the record (includes capabilities to handle variable sized data and handling NULLable fields in a storage efficient manner and even being able to add fields to a record by usage of dynamic fields which are NULL when not present in the record). It includes an efficient model for recovery and finally an efficient model for transaction handling. In all of those aspects MySQL Cluster have added additional innovation to the world of databases with a particular focus on the performance aspects.

There is actually one more important part of the performance of MySQL Cluster and this is the programming API on the client side. I will discuss this in my next blog.

MySQL Cluster - NoSQL access with some SQL

2011-04-08T21:09:00.000+02:00

As someone noted in a blog, the NDB API is a NoSQL API that was designed 15 years ago. When I wrote my Ph.D thesis (which is the design document that NDB Cluster is based on) I called it Design and Modelling of a Parallel Data Server for Telecom Applications. The important name I used here is Data Server. It was never intended as a pure SQL DBMS. It was always intended for any needs of Data Storage. The requirements on this Data Server was also written up in my thesis for those who care to read it and included HLR's (the telecom database used to keep track of your mobile phone), News-on-Demand, Multimedia Email, Event Data Services (logging of events in the telco and web applications used for charging, billing and understanding the customers) and a genealogy application.

MySQL Cluster have been very successful in the telecom space and chances are very high that a MySQL Cluster solution is used whenever you place a mobile phone call. Also many ISPs use MySQL Cluster to handle DNS lookups, authentication and many other internet services. As an example here the ISP I use every day and through which I post this blog message is using MySQL Cluster for this type of service. So I invoke services of the MySQL Cluster every time I access the web from my home. In addition, we have seen MySQL Cluster adopted into eCommerce, session management, content delivery, user profile management and on-line gaming applications.

MySQL Cluster was from the very start designed to handle many other applications as well in the web space. Today the internet environment contains quite a few different APIs to use for handling web data. MySQL Cluster already now have a plethora of different APIs that can be used to access the basic Data Server. MySQL Cluster can be used with every possible API that can be used to access a MySQL Server. In addition we have the Cluster/J API which is a low-level Java API with similar characteristics to the NDB API. Based on the Cluster/J API we have a standard JPA interface to MySQL Cluster. We even have an LDAP interface which means that the same data can be accessed through LDAP, SQL, Cluster/J, JPA, NDB API and many other interfaces based upon these of which I am sure I don't know every one. Another interesting interface is mod-ndb which makes it possible to query MySQL Cluster using a REST API and get results in JSON.

We are however not satisfied with the set of APIs we have towards MySQL Cluster so we'll be adding even more as we go to make the Data Server capabilities available to you from even more surroundings, these will be including additional APIs commonly used in the web space. Stay tuned for Tomas Ulin's keynote at the UC and Collaborate next week.

MySQL Server and NUMA architectures

2010-12-21T13:42:00.000+01:00

When you run MySQL on a large NUMA box it is possible to control the memory placement and the use of CPUs through the use of numactl. Most modern servers are NUMA boxes nowadays.

numactl works with a concept called NUMA nodes. One NUMA node contains CPUs and memory where all the CPUs can access the memory in this NUMA node at an equal delay. However to access memory in a different NUMA node will typically be slower and it can often be 50% or even 100% slower to access memory in a different NUMA node compared to the local NUMA node. One NUMA node is typically one chip with a memory bus shared by all CPU cores in the chip. There can be multiple chips in one socket.

With numactl the default option is to allocate memory from the NUMA node the CPU currently running on is connected to. There is also an option to interleave memory allocation on the different parts of the machine by using the interleave option.

Memory allocation actually happens in two steps. The first step is the one that makes a call to malloc. This invokes a library linked with your application, this could be e.g. the libc library or a library containing tcmalloc or jemalloc or some other malloc implementation. The malloc implementation is very important for performance of the MySQL Server, but in most cases the malloc library doesn't control the placement of the allocated memory.

The allocation of physical memory happens when the memory area is touched, either the first time or after the memory have been swapped out and a page fault happens. This is the time that we assign memory to the actual NUMA node it's going to be allocated on. To control how the Linux OS decides on this memory allocation one can use the numactl program.

numactl provides options to decide on whether to use interleaved memory location or local memory. The problem with local memory can be easily seen if we consider that the first thing that happens in the MySQL Server is a recovery of the InnoDB and this recovery is single-threaded so will thus make a large piece of the memory in the buffer pool to be attached to the NUMA node where the recovery took place. Using interleaved allocation means that we get a better spread of the memory allocation.

We can also use the interleave option to specify which NUMA nodes the memory should be chosen from. Thus the interleave option acts both as a way of binding the MySQL Server to NUMA nodes as well as interleaving memory allocation on all the NUMA nodes the server is bound to.

numactl finally also provides an ability to bind the MySQL Server to specific CPUs in the computer. This can be either by locking to NUMA nodes, or by locking to individual CPU cores.

So e.g. on a machine with 8 NUMA nodes one might start the MySQL Server like this:
numactl --interleave=2-7 --cpunodebind=2-7 mysqld ....
This will allow the benchmark program to use NUMA node 0 and 1 without interfering with the MySQL Server program. If we want to use the normal local memory allocation it should more or less be sufficient to remove the interleave option since we have bound the MySQL Server to NUMA node 2-7 there should be very slim risk that the memory is allocated from elsewhere. We could however also use
--memnodebind=2-7 to ensure that the memory allocation happens in the desired NUMA nodes.

So how effective is numactl compared to e.g. using taskset. From a benchmark performance point of view there is not much difference unless you get memory very unbalanced through a long recovery at the start of the MySQL Server. Given that taskset allows the server to be bound to certain CPU cores, it also means effectively that the memory is bound to the NUMA nodes of the CPUs the MySQL Server was bound to by taskset.

However binding to a subset of the NUMA nodes or CPUs in the computer is definitely a good idea. On a large NUMA box one can gain at least 10% performance by locking to a subset of the machine compared to allowing the MySQL Server to freely use the entire machine.

Binding the MySQL Server also improves the stability of the performance. Also binding to certain CPUs can be an instrument in ensuring that different appplications running on the same computer don't interfere with each other. Naturally this can also be done by using virtual machines.

The King is dead, long live the King

2010-11-30T12:52:00.000+01:00

In MySQL 5.5 we introduced a possibility to use alternative malloc implementations for MySQL. In Solaris we have found mtmalloc to be the optimal malloc implementation. For Linux we've previously found tcmalloc to be the optimal malloc implementation. However recently when working on a new MySQL feature I discovered a case where tcmalloc had a performance regression after running a very tough benchmark for about an hour. Actually I found a similar issue with the standard libc malloc implementation. So it seems that many malloc implementations gets into fragmentation issues when running for an extended period at very high load.

So I decided to contact Mark Callaghan to see if he had seen similar issues. He hadn't, but he pointed me towards an alternative malloc implementation which is jemalloc. It turns out that jemalloc is the malloc implementation used in FreeBSD among other things. I found a downloadable tarball of jemalloc, downloaded it and installed it on my benchmark computers. Given that MySQL already supports any generic malloc implementation it was a simple matter of pointing LD_PRELOAD towards jemalloc instead of towards tcmalloc to make this experiment.

The background is that tcmalloc gave about +5-10% better performance than libc malloc on Linux. Both libc malloc and tcmalloc have had performance regressions in certain situations. So the new results for jemalloc was very exciting. I got +15% compared to libc malloc and so far after using it for about a month I have found no performance regressions using jemalloc.

Impact of changes in the run-time environment of MySQL 5.5

2010-10-28T11:49:00.000+02:00

When experimenting with the Google patches a few years ago I found that tcmalloc had a fairly large impact on performance of the MySQL Server. So the question I asked myself was obviously whether the libc malloc have regained some of the lost territory (also had regressions of major drop in performance in certain libc versions). Using tcmalloc used to have a 5-10% positive impact on performance, the matter of the fact is that this gain remains. I lost 8-10% in performance on all thread counts tested (16, 32, 64, 128 and 256) by not using tcmalloc in running Sysbench RW.

The experiments are performed on a fairly high-end x86 box with 4 sockets. I run the sysbench program on the same machine as the MySQL Server runs on. So this means that it's interesting to check whether I get better performance by locking the MySQL Server to 3 of the 4 sockets and let sysbench use its own socket compared to not control CPU usage at all.

What I discovered is a mixed picture. Performance when locking to CPU's was much more stable although top performance was better without locking. Performance at 16 threads improved 3% and at 32 threads it improved 7%. But at higher thread counts the performance was better for the locked scenario, 10% at 64 threads and 4% at 256 threads. I used the Linux feature taskset to lock the MySQL Server and Sysbench to certain CPUs.

So the conclusion is that locking to CPUs gives a more stable environment. When the number of threads increases the scheduler is allowed to use more CPUs than what is beneficial for MySQL execution. I've seen this also in other experiments that making sure that MySQL reuses the CPU caches as much as possible is very important for performance. Thus when MySQL competes with other programs on use of CPUs and there are many concurrent MySQL threads it's usually not beneficial to performance since the CPU caches will be too cold.

Using Unix sockets instead of TCP/IP sockets is very beneficial for MySQL performance still. I haven't made any recent experiments in this area but the difference is definitely significant. I have also seen OS bottlenecks sometimes appear when using TCP/IP sockets. This is an area for further investigation which I have had on my TODO list for a while. It's also interesting to experiment with different communication mechanisms when the Sysbench program and MySQL runs on different computers. However this is for future testing.

Finding the optimum configuration of MySQL 5.5 running Sysbench

2010-10-28T10:24:00.000+02:00

Sysbench is a commonly used benchmark tool to discover ways to improve MySQL performance. It is certainly not representative for every application, but it's still a useful tool for finding bottlenecks in the MySQL code.

In MySQL 5.5 a great number of new scalability improvements have been developed. Some of these will always be active and some of them requires using new configuration parameters.

In order to assist users of MySQL I am performing a fairly extensive benchmark series where I test the various configuration parameters that have an effect on running MySQL/InnoDB using Sysbench.

The parameters can be categorized into:
1) Changes of run-time environment
2) Compile time parameters (including choice of compiler)
3) Configuration parameters for MySQL
4) Configuration parameters for InnoDB

Finally there is also a set of parameters one can use to affect the execution of sysbench itself to have it behave differently. It's possible to have Sysbench use secondary index instead of primary key index, it's possible to let the table be partitioned and it's possible to run sysbench using multiple tables instead of only one (it's very syntetic to only have one table in a system which gets all the queries).

The parameters that I have found to be important to consider for performance of MySQL 5.5 when running sysbench are:
1.1) Use of tcmalloc
1.2) Use of taskset (lock MySQL to certain CPUs)
1.3) Affecting memory allocation by use of numactl
1.4) Connecting to MySQL through socket or using TCP/IP
2.1) Use of gcc compiler with platform-specific optimisations
2.2) Use --with-fast-mutexes flag when compiling MySQL
2.3) Choice of compiler (gcc, Open64, icc, SunStudio, ..)
2.4) Using feedback compilation in compilation
2.5) Compiling sysbench itself with platform specific optimisations
3.1) Choice of transaction isolation (READ COMMITTED or REPEATABLE READ)
3.2) Use of large pages through setting --large-pages=ON
3.3) Compiling with Performance Schema activated
3.4) Running with Performance Schema activated through --perfschema
3.5) Deactivating Query Cache completely through --query_cache_size=0
and --query_cache_type=0
4.1) Using new file format Barracuda or old format Antelope through
--innodb_file_format=x
4.2) Setting --innodb_stats_on_metadata to ON/OFF
4.3) Deactivating InnoDB doublewrite through --skip-innodb_doublewrite
4.4) Setting innodb-change-buffering to none/insert/all and so forth
4.5) Setting number of buffer pool instances using innodb-buffer-pool-instances=x
4.6) Setting InnoDB log file size using innodb-log-file-size=x
4.7) Setting InnoDB log buffer size using innodb-log-buffer-size=x
4.8) Setting InnoDB flush method (default fsync, O_DIRECT, O_DSYNC) using
--innodb_flush_method=x
4.9) Setting InnoDB to use file per table using --innodb_file_per_table
4.10) Deactivating InnoDB adaptive hash index using
--skip-innodb-adaptive-hash-index
4.11) Adapting read-ahead using --innodb_read_ahead=x
4.12) Using InnoDB max purge lag through --innodb_max_purge_lag=x
4.13) Using InnoDB purge thread through --innodb_purge_thread=1
4.14) Changing behaviour of InnoDB spin loops by changing
--innodb_sync_spin_loops=x and --innodb_spin_wait_delay=x
4.15) Changing InnoDB IO capacity by setting --innodb-io-capacity=x
4.16) Setting InnoDB buffer pool size through --innodb-buffer-pool-size=x
4.17) Setting InnoDB dirty pages percent through --innodb_dirty_pages_pct=x
4.18) Setting InnoDB old blocks percentage through --innodb_old_blocks_pct=x
4.19) Activating support for InnoDB XA through --innodb_support_xa=FALSE/TRUE
4.20) Activating InnoDB thread concurrency using
--innodb-thread-concurrency=x
4.21) Setting InnoDB commit mechanism through
--innodb-flush-log-at-trx-commit=x
4.22) Setting number of read and write IO threads through
--innodb-read-io-threads=x and --innodb-write-io-threads=x

This makes for a total of 36 parameters that can be tuned to affect sysbench performance. Most of these parameters have only a limited impact on performance but some of them can have a large impact and there is also a number of them where most reasonable values are ok, but if a bad value is used it can have a major impact on performance.

In addition the problem of finding the optimal values is a multi-dimensional search. So changing one parameter might very well affect the impact of other parameters. So my approach will be to start with a reasonable baseline configuration, then try each parameter and see how it affects the outcome, come to a new baseline by changing the most important parameter. Next step is to restart tests varying all the parameters which have had a measurable impact on performance. Using this method we should be able to find an optimum to a reasonable degree. Finding the absolute optimum is probably more or less practically impossible.

It's important here to understand that the MySQL defaults can sometimes have really bad values for performance. The reason is that MySQL defaults have been choosen mainly to make MySQL run on any HW platform and use very small resources. Remember that the normal use of MySQL isn't running sysbench on a 32-core server :) So it is important to consider all of these parameters to get the optimal performance of the MySQL Server.

I plan to write about these variables and how they affect sysbench performance in a number of upcoming blogs. I might also do a similar run with MySQL using the DBT2 benchmark. So hopefully after finishing these test runs I have found a reasonable optimum configuration to run sysbench and probably also the configuration parameters that needs changing when running DBT2 instead.

How to speed up Sysbench on MySQL Cluster by 14x

2010-09-22T14:23:00.000+02:00

The time period up to the 2010 MySQL Users conference was as usual packed with hard work. The last two conferences have been very focused on getting scalability of the MySQL Server and InnoDB improved. This year had a nasty surprise after the conference in the form of ash cloud from an icelandic volcano. When you're tired from hard work and just want to go home and get some rest then the concept of staying at a hotel room with absolutely no idea of when one could return home is no fun at all. So when I finally returned home I was happy that summer was close by, vacation days were available (swedes have a lot of vacation :)).

Now the summer is gone and I am rested up again, ready to take on new challenges and we've had some really interesting meet-ups in the MySQL team to discuss future developments. The renewed energy also is sufficient to write up some of the stories from the work I did during the summer :)

During the summer I had the opportunity to also get some work done on scalability of the MySQL Cluster product as well. Given that I once was the founder of this product it was nice to return again and check where it stands in scalability terms.

The objective was to compare MySQL Cluster to the Memory engine. The result of the exercise was almost obvious from the start. The memory engine having a table lock will have very limited scalability on any workload that contains writes. It will however have very good scalability on read-only workloads as this isn't limited by the table lock since readers don't contend each other. The Cluster engine should have good and fairly even results on read and write workloads.

Much to my surprise the early results showed a completely different story. The Memory engine gave me a performance of about 1-2 tps to start with. The early results of MySQL Cluster was also very dismaying. I covered the Memory engine in a previous blog, so in this blog I will focus on the MySQL Cluster benchmarks.

So the simple task of benchmarking as usual turned into some debugging of where the performance problems comes from.

In the first experiment I used the default configuration of the Fedora Linux OS, I also used the default set-up of the MySQL Cluster storage engine. It turned out that there are huge possibilities in adapting those defaults.

First the Fedora has a feature called cpuspeed. By default this feature is activated. The feature provides power management by scaling down the CPU frequency on an entire socket. The problem is that when you run the MySQL Server with few threads, it doesn't react to the workload and scales down frequency although there is a lot of work to do. So for the MySQL Server in general this means about half the throughput on up to 16 threads. However the impact on MySQL Cluster is even worse. The performance drops severely on all thread counts. Most likely this impact comes from the very short execution times of the NDB data nodes. It's possible that this small execution times is too short to even reach the radar of the power management tools in Linux.

So a simple sudo /etc/init.d/cpuspeed stop generated a major jump in performance of the MySQL Cluster in a simple Sysbench benchmark (all the benchmarks discussed here used 1 data node and all things running on one machine unless otherwise stated).

The next simple step was to add the MySQL Cluster configuration parameter MaxNoOfExecutionThreads to the configuration scripts and set this to the maximum which is 8. This means that one thread will handle receive on the sockets, one thread will handle transaction coordination and four threads will handle local database handling. There will also be a couple of other threads which are of no importance to a benchmark.

These two configuration together added about ~3.5x in increased performance. Off to a good start, but still performance isn't at all where I want it to be.

In NDB there is a major scalability bottleneck in the mutex protecting all socket traffic. It's the NDB API's variant of the big kernel mutex. There is however one method of decreasing the impact of this mutex by turning the MySQL Server into several client nodes from an NDB perspective. This is done by adding the --ndb-cluster-connection-pool parameter when starting the MySQL Server. We achieved the best performance when setting this to 8, in a bigger cluster it would probably make more sense to set it to 2 or 3 since this resolves most of the scalability issues without using up so much nodes in the NDB cluster.

Changing this from 1 to 8 added another ~2x in performance. So now the performance is up a decent 8x from the first experiments. No wonder I was dismayed by the early results.

However the story isn't done yet :)

MySQL Cluster has another feature whereby the various threads can be locked to CPUs. By using this feature we can achieve two things, the first is that the NDB data nodes doesn't share CPUs with the MySQL Server. This has some obvious benefits from CPU cache point of view for both node types. We can also avoid that the data node threads are moved from CPU to CPU which is greatly advantegous in busy workloads. So we locked the data nodes to 6 cores. The configuration variable we used to achieve this is LockExecuteThreadToCpu which is set to a comma separated list of CPU ids.

I also locked the MySQL Server and Sysbench to different set of CPUs using the taskset program available in Linux.

Using this locking of NDB data node threads to CPUs achieved another 80% boost in performance. So the final result gave us a decent 14x performance improvement.

So in summary things that matters greatly to performance of MySQL Cluster for Sysbench with a single data node.

1) Ensure the Linux cpuspeed isn't activated

2) Make sure to set MaxNoOfExecutionThreads to 8

3) Make sure the --ndb-cluster-configuration-pool parameter to the MySQL Server using around 8 nodes per MySQL Server

4) Lock NDB Data node threads to CPUs by using the LockExecuteThreadToCpu.

5) Lock MySQL Server and Sysbench processes to different sets of CPUs from NDB Data nodes and each other.

Doing this experiments also generated a lot of interesting ideas on how to improve things even further.

How to get Sysbench on Memory engine to perform

2010-09-22T14:15:00.001+02:00

I had the opportunity to test the Memory engine during the summer. What I expected to be a very simple exercise in running the Sysbench benchmark turned out to be a lot more difficult than I expected.

My first experiments with the Memory engine gave very dismaying results. I got 0-2 TPS which is ridiculously bad. So I couldn't really think this was proper results, so I started searching for problems in my benchmarking environment. Eventually I started setting up a normal MySQL client session to the MySQL Server while the benchmark was running and issued some of the queries in the benchmark by hand and I was surprised to see some simple queries take seconds.

EXPLAIN came to my rescue. EXPLAIN showed that the range scans in Sysbench was in fact turned into full table scans. Now this was surprising given that the primary key index in most engines is always ordered, so a range scan should normally be translated to a simple ordered index scan.

What I found is that the Memory engine has a different default compared to the other storage engines. When we designed the NDB handler we decided that any SQL users expects to have an ordered index when they create an index on a table. Since primary key indexes are always hash-based, this meant that in NDB the default primary key index is actually two indexes, one primary hash index and one secondary ordered index on the same fields.

Not so in the Memory engine. The memory engine also uses a hash-based index by default. So when you create a new index on a Memory engine and specify no type, the index wil become a hash index. Hash indexes are not very good at range queries :) so thus the surprise to me when benchmarking the Memory engine in Sysbench.

Fixing this issue required some code changes in the sysbench test. It required the proposed fix from Mark Callaghan to add secondary indexes to sysbench. This was however not sufficient since this patch only added an index by adding KEY xid (id) and didn't specifically specify this index had to be an ordered index. So I changed this to KEY xid (id) USING BTREE and off the performance went.

The Memory engine is still more or less a single threaded engine for any write workload like Sysbench RW which limits performance very much.

However the Sysbench Readonly benchmark for the Memory engine was interesting since it used no limiting locks (concurrent readers don't contend with each other). I decided to see how much scalability was achievable for a storage engine without any concurrency issues internally.

I found that performance of the Memory engine was limited to 8% more than the InnoDB engine in the same benchmark. So my interpretation of this result is that for readonly workloads, the main limiting factor on scalability is the MySQL Server scalability issues and not the InnoDB ones.

So going forward when working on further improving the scalability of the MySQL Server parts there is a perfect benchmark that can be used to see how scalable the MySQL Server part is by using the Memory engine.

I plan on also adding a feature to sysbench making it possible to use more than one Sysbench table to see how much added scalability we get when there are several tables involved in the query mix. Sysbench is a a very syntectic benchmark in this manner that it only uses one table.

Only using one table provokes more bottlenecks than is found in most normal workloads, e.g. the LOCK_open in the MySQL Server, the meta data locks introduced in MySQL 5.5, the index mutex in InnoDB and even some unbalance to the usage of multiple buffer pools come from only using one table in the benchmark.

Multiple Buffer Pools in MySQL 5.5

2010-09-18T20:43:00.000+02:00

In our work to improve MySQL scalability we tested many opportunities to scale the MySQL Server and the InnoDB storage engine. The InnoDB buffer pool was often one of the hottest contention points in the server. This is very natural since every access to a data page, UNDO page, index page uses the buffer pool and even more so when those pages are read from disk and written to disk.

As usual there were two ways to split the buffer pool mutex, one way is to split it functionally into different mutexes protecting different parts. There have been experiments in particular on splitting out the buffer pool page hash table, the flush list. Other parts that have been broken out in experiments are the LRU list, the free list and other data structures internally in the buffer pool. Additionally it is as usual possible to split the buffer pool into multiple buffer pools. Interestingly one can also combine using multiple buffer pools with splitting the buffer pool mutex into smaller parts. The advantage of using multiple buffer pools is that it is very rare that it is necessary to grab multiple mutexes for the buffer pool operation which quickly becomes the case when splitting the buffer pool into multiple mutex protection areas.

After working on scalability improvements in MySQL and InnoDB I noted that all the discussion was around how to split the buffer pool mutex and no dicsussion centered around how to make multiple buffer pools out of the buffer pool. I decided to investigate how difficult it would be to make this change. I quickly realised that it needed a thorugh walk through of the code. It required a code check that required checking about 150 methods and their interaction. This sounds like a very big task, but fortunately the InnoDB code is well structured and have fairly simple dependencies between its methods. After this walk through of the buffer pool code one quickly found that there were 3 different ways of getting hold of the buffer pool, one method was to calculate it using the space id and page id. This is the normal method in most methods used in the external buffer pool interface. However there were numerous occasions where we only had access to the block or page data structure and it would be a bit useless to recalculate the hash value in every method that needed access to the buffer pool data structure. So it was decided to leave a reference to the buffer pool in every page data structure. There were also a few occasions where one needed to access all buffer pools.

The analysis proved that most of the accesses to the buffer pool was completely independent of other accesses to the buffer pool for other pages. InnoDB uses read-ahead and neighbour writes in the IO operations that are started from the buffer pool. These always operate on an extent of 64 pages. Thus it made sense to map the pages of 64 pages into one buffer pool to avoid having to operate on multiple buffer pools on every IO operation.

With these design ideas there were only a few occasions where it was necessary to operate on all buffer pools. One such operation was when the log required knowledge of the page with the oldest LSN of the buffer pool. Now this operation requires looping over all buffer pools and checking the minimum LSN of each buffer pool instance. This is a fairly rare operation so isn't a scalability issue.

The other operation with requirement to loop over all pages needed a bit more care, this operation is the background operation flushing buffer pool pages to disk. A couple of problems needs consideration here. First it is necessary to flush pages regularly from all buffer pool instances, secondly it's still important to flush neighbours. Given that many disks are fairly slow, it can be problematic to spread the load in this manner to many buffer pools. This is an important consideration when deciding how many buffer pool instances to configure.

The default number of buffer pool is one and for most small configurations with less than 8 cores it's mostly a good idea not to increase this value. If you have an installation that uses 8 cores or more one should also pay attention to the disk subsystem that is used. Given that InnoDB often writes up to 64 neighbours in each operation and that the flushing should happen each second, it makes sense to have a disk subsystem capable of having 500 IO operations per second to use 8 buffer pool instances. This can be set in the innodb_io_capacity configuration variable. One SSD drive should be capable of handling this, two fast hard drives or 3 slow ones.

In our experiments we have mostly used 8 buffer pools, more buffer pools can be useful at times. The main problem with many buffer pools is related to the IO operations. It is important to have a balanced IO load in the MySQL server.

Our analysis of using multiple buffer pool instances have shown some interesting facts. First the accesses to the buffer pools is in no way evenly spread out. This is not surprising given that e.g. the root page of an index is a very frequently accessed page. So using sysbench with only one table, there will obviously be much more accesses to certain buffer pool instances. Our experiments shows that in sysbench using 8 buffer pools, the hottest buffer pool receives about one third of all accesses. Given that sysbench is a worst case scenario for the multiple buffer pool case, this means that most applications that tend to use more tables and more indexes should have a much more even load on the buffer pools.

So how much does multiple buffer pools improve the scalability of the MySQL Server. The answer is as usual dependent on application, OS, HW and so forth. But some general ideas can be found from our experiments. In sysbench using a load which is entirely held in main memory, so the disk is only used for flushing data pages and logging, in this system the multiple buffer pools can provide up to 10% improvement of the throughput in the system. In dbStress, the benchmark Dimitri uses, we have seen all the way up to 30% improvement. The reason here is most likely that dbStress uses more tables and have avoided many other bottlenecks in the MySQL Server and thus the buffer pool was a worse bottleneck in dbStress compared to sysbench. From the code it is also easy to see that the more IO operations the buffer pool performs, the more the buffer pool mutex will be acquired and also often held for a longer time. One such example is the search for a free page on the LRU list every time a read is performed into the buffer pool from the disk.

Furthermore the use of multiple buffer pool opens up for many more improvements and also it doesn't remove the possibility to split the buffer pool mutex even more.

Another manner of displaying the importance of using multiple buffer pools is the mutex statistics on the buffer pool mutex. With one buffer pool the buffer pool had about 750k accesses per second in a sysbench test where the MySQL Server had access to 16 cores. 50% of those accesses met a mutex already held, so it's obvious that the InnoDB mutex subsystem is very well aligned with the buffer pool mutex which have very short duration which makes spinning waiting for it very fruitful. Anyways a mutex which is held 50% of the time makes the buffer pool mutex a limiting factor of the MySQL Server. Quite a few threads will often spend time in the queue waiting for the buffer pool mutex. So splitting the buffer pool into 8 instances even in sysbench means that the hottest buffer pool receives about one third of the 750k accesses so should be held about 17% of the time. Our later experiments shows that the hottest buffer pool mutexes are now held up to about 14-15% of the time. So the theory matches the real world fairly well. This means that the buffer pool is still a major factor in the MySQL Scalability equation but is now more on par with the other bottlenecks in the MySQL Server.

The development project of multiple buffer pools happened at a time when the MySQL and InnoDB teams could start working together. I was impressed by the willingness to cooperate and the competence in the InnoDB team that made it possible to introduce multiple buffer pools into MySQL 5.5. Our cooperation has continued since then and this has led to improvements in productivity on both parts. So for you as a MySQL user this spells good times going forward.

Split log_sys mutex in MySQL 5.5

2010-09-18T20:35:00.000+02:00

One important bottleneck in the MySQL Server is the log_sys mutex in InnoDB. Experiments using mutex statistics showed that this mutex was accessed about 250k times per second and that about 75% of those accesses had to queue up to get the mutex. One particular nuisance is that while holding the log_sys mutex it is necessary to grab the buffer pool mutex to put the changed pages to the start of the flush list indicating it is now the youngest dirty page in the buffer pool (this happens as part of the mini commit functionality in InnoDB). To some extent this contention point is decreased by splitting out the buffer flush list from the buffer pool mutex.

We found a simple improvement of this particular problem. The simple solution is to introduce a new mutex, log_flush_order mutex, this mutex is acquired while still holding the log_sys mutex, as soon as it is acquired we can release the log_sys mutex. This gives us the property that the log_sys mutex is available for other operations such as starting a new log write while we still serialise the input of the dirty pages into the buffer pool flush list.

As can be easily seen this solution decrease the hold time of the log_sys mutex while not decreasing the frequency it is acquired.
In our experiments we saw that this very simple solution improved a Sysbench RW test by a few percent.

Pointers to my presentations of MySQL 5.5 Scalability enhancements

2010-04-13T23:46:00.004+02:00

Here is some pointers to my MySQL conference slides.
Here is the presentation on MySQL 5.5 Performance and
Scalability improvements
here

Here is the presentation on the MySQL 5.5 Performance
and Scalability benchmarks
here

Scalability enhancements of MySQL 5.5.4-m3

2010-04-13T17:30:00.007+02:00

The MySQL 5.5.4-m3 beta version contains a number of
interesting new scalability features.

It contains the following InnoDB improvements:
Multiple Buffer Pool instances
- For example if the buffer pool is 8 GByte in size
the buffer pool can be split into 4 buffer pools
each containing 2 GBytes. Each page is mapped into
one and only one of these buffer pools.
Split Log_sys mutex
- We have ensured that the Log mutex and the buffer
pool mutex is more independent of each other. Also
the log_sys mutex is through this split less
contended.
Split out flush list from buffer pool mutex
Split Rollback Segment mutex into 128 instances
Separate Purge Thread from Master Thread
- Splitting out the purge thread from the master thread
is very important to ensure that performance is stable.
Extended Change buffering, now also Deletes and purges are
possible to buffer.

It contains the following MySQL Server improvements:
Split LOCK_open into
- MDL hash mutex
- MDL table lock mutex
- atomic variable refresh_version
- LOCK_open
We also removed some parts not needing mutex
protection from LOCK_open. All these mutexes are taken
independently of each other in almost all places.

Remove LOCK_alarm (used in network handling)
Remove LOCK_thread_count as scalability bottleneck

In addition the InnoDB recovery has been improved by
decreasing recovery time by 10x.

MySQL 5.5.4-m3 scales to 32 cores

2010-04-13T14:01:00.009+02:00

The newly released MySQL 5.5 beta version MySQL 5.5.4-m3
has a large number of significant performance improvements.
These improvements makes it possible for MySQL to scale
well even on 32-core servers. The graph below shows how
MySQL 5.5.4-m3 scales from 12 cores to 32 cores using a
single thread per core. The benchmark used here is
dbStress. dbStress uses a number of tables which spreads
the impact of mutexes and improves scalability.

The graph below shows a similar scalability analysis on a
smaller server where the benchmark used was Sysbench RW.
The red line shows the scalability of MySQL 5.1.45, the
green line shows scalability of MySQL 5.5.3-m3 and the
blue line shows MySQL 5.5.4-m3. So this graph shows that
even with a single table in Sysbench RW we are able to
scale very well to 16 cores. The graph also displays how
our work on scaling MySQL since the release of MySQL 5.1
as GA in december 2008 is paying off in a significant
manner. So MySQL is following very well in the development
of new multi-core CPUs. The major performance enhancement
in MySQL 5.5.3-m3 is the use of the InnoDB plugin with
its inclusion of Google patches and other significant
enhancements to InnoDB. MySQL 5.5.4-m3 contains a large
number of new scalability enhancements that will be
explained more about on this blog.

New threadpool design

2009-12-03T11:41:00.000+01:00

In MySQL 6.0 a threadpool design was implemented based on
libevents and mutexes.

This design unfortunately had a number of deficiences:
1) The performance under high load was constrained due to a global
mutex protecting libevent (see BUG#42288).

2) The design had no flexibility to handle cases where threads were
blocked due to either locking or latches. E.g. a thread held up by a
table lock will be kept in the threadpool as an active thread until
the table lock is released. If all threads are blocked in this state,
it's easy to see that also any query that want to release the table
lock cannot be processed since all threads in the thread pool are
blocked waiting for the table lock (see BUG#34797).

3) The design is intended to support very many connections but
didn't use the most efficient methods to do this on Windows.
libevent uses poll on Windows which isn't a scalable API when
there are thousands of connections.

Also in all of the benchmarking with MySQL it's been clear that
performance of MySQL often drops significantly when there are too
many threads hitting the MySQL Server. We have seen vast
improvements of this the last year and there are some additional
improvements of this in the pipeline for inclusion into the next
MySQL milestone release. However the basic problem is still there,
that too many waiters in the queue can lead to various performance
drop off, one reason for such drop offs can be when mutex waits
starts to timeout in InnoDB.

So actually when we're looking at the threadpool design now, we're
aiming at solving two issues in one. The first is to remove this
scalability dropoff at high thread counts and the second is to
efficiently handle MySQL servers with thousands of connections.
Threadpool also enables us to have more control over on which
CPU threads are scheduled to execute on. We can even dynamically
adapt the CPU usage to optimize for lower power consumption by
the MySQL Server with a clever threadpool design.

We're currently in the phase of experimenting with different
models, however we opted for a design based around usage of epoll
on Linux, eventports on Solaris and kqueue for FreeBSD and
Mac OS X. We will also make a poll-based variant work mostly for
portability reasons although it's scalability won't be so great.
For Windows we're experimenting with some Windows specific
API's such as the IO Completion API.

The code to support thread pooling in MySQL is actually very
small so it's easy to adapt the code for a new experiment.

Last week we found a model that seems to work very fine.
The benchmarks shows that the performance on 1-32 threads is
around 97-103% of one thread per connection performance. When
we go beyond 32 threads the thread pool gains more and more,
it's getting to about 130% at 256 threads and reaches 250%
better performance on 1024 threads. However this model still
have the problem of deadlocks, so there is still some work on
refining this model. The current approach we have is fixing
the deadlock problem but removes about 10-15% of the
performance on lower number of threads. We have however
numerous ideas on how to improve this.

The basic idea with our current approach is to use thread groups,
where each group works indepently of other groups in handling a
set of connections. We're experimenting with the number of
threads per group and also how to handle the situation when the
last thread in the group is getting ready to execute a query.

Compared to maximum performance around 32 threads we reach
about 67% of this performance also on 1024 concurrently active
threads. The drop off 33% is expected since there is some
additional load when we reach an overload situation to ensure
that the proper thread is handling the task. At low number of
threads it's possible to immediately schedule the current worker
thread to work on the query, but in the overload situation there
is some queueing and context switching needed to handle the
situation. However the overhead at overload is constant, so it
doesn't worsen when the number of threads goes to a very high
number.

To handle the problems with blocked threads, we will implement a
new part of the storage engine API and API towards the MySQL
Server where the MySQL Server and the storage engines can
announce that they're planning to go inactive for some reason.
The threadpool will however handle the situation even if a thread
goes to sleep without announcing it, it will simply be more
performant if the announcement comes in those situations.

The new MySQL development model with milestone release is a
vital new injection to the MySQL development leading to the
possibility of making new features available to the MySQL
community users in an efficient manner without endangering the
quality of the MySQL Server. There is a very strict quality model
before approving any new feature into a milestone release.
The 6.0 thread pool design would not meet this strict quality
model. The new design must meet this strict quality model before
being accepted although we have good hopes for this to happen.

Partitioning as performance booster

2009-11-20T08:23:00.002+01:00

When I developed partitioning for MySQL the main goal was
to make it easier for MySQL users to manage large tables
by enabling them to easily add and drop partitions.

It turns out that partitioning can also be used as a manner
to make MySQL more scalable. The reason is that in some
cases the storage engine have internal locks per table or
per index (one such example is the btr_search_latch in InnoDB).

So in this case adding a
PARTITION BY KEY (key_part)
PARTITIONS 4
to the table definition makes a very hot table into 4 tables
from the storage engine point of view.

This would mostly be beneficial in cases where the main
operation is primary key lookups on the table. Dividing the
indexes in cases of scans can be both positive and negative.
So this solution is definitely not a winner for all situations.

I haven't tried this out yet myself in my benchmark suites,
but I plan to make some experiments in this area. It is usable
in sysbench, it's possible to use for DBT2 (have used partitioning
for DBT2 in MySQL Cluster benchmarks a lot already) and it's
possible to use in Dimitri's dbStress benchmark.

Full automation of DBT2 test runs in benchmark scripts

2009-11-20T08:18:00.002+01:00

My benchmark scripts was upgraded once more today.
I fixed all issues pertaining to full automation
of DBT2 runs. So now it is as easy to start a
DBT2 test run as it previously was to start a
sysbench run. Obviously DBT2 was already earlier
supported in the benchmark scripts, so what I did
now was add the final steps to make it fully
automated. This includes also generating the DBT2
load files needed to load the DBT2 database.

See the download section on www.iclaustron.com
for the tarball including some description of
how to configure sysbench and DBT2 test runs.

New version of benchmark scripts also supporting Drizzle

2009-11-17T14:00:00.003+01:00

I updated my benchmark scripts this week. These scripts can now
run:

- Sysbench benchmarks for MySQL and Drizzle
- DBT2 benchmarks for MySQL and MySQL Cluster
- TPC-W benchmark for MySQL Cluster

There is also a number of scripts to start and stop
MySQL, Drizzle and MySQL Cluster nodes.

In this version I added Drizzle support for sysbench and also
added a README-AUTOMATED file that describes the steps needed
to set-up a completely automated sysbench run for MySQL
and Drizzle.

To run a MySQL sysbench benchmark one needs the DBT2 tarball,
the sysbench tarball and a MySQL tarball (gzipped tarballs).

The tarball is found on www.iclaustron.com in the downloads
section and this version is named dbt2-0.37.47.tar.gz.

Improvements by LOCK_* patches

2009-11-12T12:32:00.005+01:00

I have done a long series of tests to verify that
the impact of the LOCK_alarm removal, removing two
variables from LOCK_threadcount protection to being
atomic increments instead and decreasing hold time
of LOCK_open is positive in most if not all cases.

There are a number of test cases needed:
1) With and Without cpuspeed activated
2) With sysbench local and with sysbench on another
server
3) With MySQL Server limited to 2,4,6,8,12,16 cores.
4) With number of threads going from 1 to 256 threads
in fair sized steps.

I've done most of those tests for Sysbench Readonly
and Sysbench Readwrite. The results are positive in
almost all cases towards the baseline which is based
off the MySQL 5.4.3 tree (not exactly 5.4.3 but close
enough).

In the case of networked benchmark with cpuspeed
activated the gain is biggest, top performance goes up
about 10% and also top performance moves from 64 to 128
threads, for 256 threads the performance increases
by about 50%.

When cpuspeed is deactivated and we use networked
benchmarks the network handling becomes a bottleneck, so
the numbers here are less interesting since we need to
resolve the network bottleneck first.

With cpuspeed activated and local communication the top
performance increase by about 8% and there is gain
for all number of threads. The gain is a bit higher
on sysbench readonly than on sysbench readwrite.

With cpuspeed deactivated and local communication we
naturally get the best numbers but also the
smallest gains. Top performance of Sysbench
Readonly increased by 2.5% and for Readwrite
it increased 4%, the top performance for
sysbench readwrite also moved from 16 threads
to 32 threads. The improvement is slightly
better on more threads.

Analysis of InnoDB adaptive hash index parameter for sysbench

2009-11-11T14:14:00.003+01:00

As I mentioned in a previous blog post I was suspicious that the
adaptive hash index in InnoDB added to the scalability issues in
the MySQL Server. So I decided to run a test where I disabled the
adaptive hash index.

The results were fairly much as expected. The adaptive hash
index usage improves performance on low thread counts
(up to about 16) by a few percent. However at 32 threads and
beyond the performance is better without the adaptive hash
index and equal in some cases. In particular the top performance
goes up by about 3% when this is disabled.

This confirms the documentation in the InnoDB manual that the
adaptive hash index improves performance as long as the lock
around it doesn't hurt performance. So for the majority of users
it's a good idea to have it turned on, but for users with high-end
servers it's a good idea to test if turning it off will improve
performance.

For sysbench benchmarks it's clearly a good idea to turn it off.

245% improvement of MySQL performance in 1 year

2009-11-11T12:05:00.005+01:00

When I did sysbench benchmarks 1 year ago I used a
4-socket server, a Linux kernel based on 2.6.18 and
MySQL 5.1. The sysbench readwrite numbers I got then
was around 2700. When I run the same benchmarks
now the numbers I get are 9300.

These improvements obviously comes from a mixture
of HW development, OS development (now using
a 2.6.31 based kernel) and MySQL development.

The machine is still a 4-socket server, the operating
system is still Linux and the database is still MySQL,
but the performance has improved by 245%. Needless
to say this is an extraordinary performance
improvement in just one year and clearly shows that
the both the HW industry and the open source SW
industry is quickly picking up on how to improve
performance using multi-core multi-socket servers.

Effect of CPU Powersave mode on Sysbench benchmarks

2009-11-11T11:32:00.006+01:00

When I started analysing the various patches that I had made
for improving the MySQL Server performance I did by mistake
forget to turn cpuspeed off in Linux. This is a feature that
makes it possible to run the CPU's on a much lower frequency
in cases when they aren't so heavily used.

So at first I considered simply turning it on and forgetting the
data I had produced. Then I realised that actually to have
cpuspeed activated is the default behaviour and for many
servers out there in the world it is actually the best mode since
most servers goes from high load to low load frequently.

So I decided that it would be worthwhile to analyse behaviour
both with and without this feature turned on.

The cpuspeed feature was particularly involved when running
sysbench on a different server, so thus using a socket based
communication. In this case the performance drop off at
64,128 and 256 threads were fairly significant. However the
performance drop off when I added fixes for LOCK_open,
LOCK_alarm and LOCK_threadcount was very significant. I
got 50% better performance with these patches when
cpuspeed was activated and I was running sysbench over the
network.

When I ran sysbench and mysqld on the same host the impact
of the patches and cpuspeed was smaller but still significant.
Turning cpuspeed on decreases the performance of sysbench
readwrite by almost 10% for the baseline (MySQL 5.4.3) whereas
with the patches that fixes the LOCK_* problems the drop in
using cpuspeed is only 1%.

So it seems like having many extra mutexes to pass through
doesn't hurt performance so much when running at full CPU
speed all the time, but as soon as the CPU power save mode
is activated these many extra mutexes to pass through has a
significant negative effect.

GDB stack trace analysis of Sysbench benchmark

2009-11-11T10:48:00.008+01:00

After many unsuccessful attempts to get MySQL to run faster I got
a very simple but effective gdb script courtesy of Intel. The script
is very simple and attaches to the mysqld process and does a
backtrace on all threads in the process and then it gathers
statistics on all stacktraces.

With this script I did some analysis on what goes in sysbench
readonly and sysbench readwrite.

Starting with sysbench readonly I discovered a lot of things I
already knew such that LOCK_open is a major bottleneck.
There were also many other things that popped up such as:
LOCK_alarm, LOCK_threadcount, LOCK_grant,
btr_search_latch and the mutex on the table object which is
used to discover TABLE level locks on the MySQL level. This
last lock is contended mostly because sysbench is only
operating on one table. So most normal benchmarks will
not have any major problems with this mutex since it's rare
with applications that put so heavy weight on a single table.

Interestingly the kernel_mutex in InnoDB wasn't so prevalent
in sysbench readonly. Also I was a bit surprised to find the
btr_search_latch there since it's a RW-lock, but it seemed
like every now and then someone took a X-lock on the
btr_search_latch even in readonly queries. Probably has
something to do with InnoDB adaptive hash index.

One surprising lock here is the LOCK_grant which is also
a RW-lock and this is never taken in anything else than
the Read mode unless one changes the grants which
doesn't happen in a sysbench run. Some discussions
concluded that pthread_rwlock is actually implemented
by using a mutex and this is the cause of the contention
on LOCK_grant. So to resolve that a read-optimised
RW-lock is needed for the MySQL Server code.

To remove LOCK_alarm there is already code in the MySQL
Server prepared to do that so the patch to remove
LOCK_alarm is fairly straightforward.

To remove LOCK_threadcount isn't necessary, it's sufficient
to remove two variables thread_running and global_query_id
from being protected by this mutex and instead using
atomic variables. To handle this one can use the my_atomic
framework and add 64-bit support to it. Then the fix of
LOCK_threadcount is straightforward.

To resolve LOCK_open is obviously a bigger problem but a
first step is to simply remove the hash calculation from
LOCK_open.

The MySQL runtime team is working on a metadata locking
infrastucture that was in MySQL 6.0 but still have some
quality issues. But when this code is ready it will also make
it possible to resolve the LOCK_open problem. Actually the
problem then is both about the new lock LOCK_mdl added
by the metadata locking code and LOCK_open. But the new
structure makes it possible to have more aggressive
solutions on both LOCK_open and LOCK_mdl code.

In analysing the sysbench readwrite benchmark the main
contender together with LOCK_open was not very
surprisingly the kernel_mutex in InnoDB. There are some
ideas on how to improve this as well but first things first.

New partitioning SQL syntax added in next MySQL milestone release treee

2009-11-11T09:36:00.005+01:00

This blogs gives some insights into the new SQL syntax added in WL#3352
and WL#4444 and WL#4571 which all have been included in the
mysql-next-mr tree which is the base for the next milestone release
codenamed Betony.

The purpose of these new changes is to enable improved partition pruning, and
also making it possible to partition on strings. Supporting TRUNCATE on
partitions will improve partition management and the ability to use separate
key caches for different partitions makes MyISAM partitioned tables more
useful. Finally we added a new function TO_SECONDS which makes it
possible to get more fine-grained dates for partitions and subpartitions.

This information will soon find its way into documentation but if you want
to get started right away here it comes.

There are 5 additions effectively:
1) The ability to RANGE partition by column list instead of by function
2) The ability to LIST partition by column list instead of by function
3) A new function TO_SECONDS which can be used in partition functions
and where partition pruning will be used also on ranges.
4) The ability to TRUNCATE a partition
5) The ability to use a keycache in MyISAM per partition

Here's a few examples of how to use these now additions:

1)
CREATE TABLE t1 (a varchar(5), b int)
PARTITION BY RANGE COLUMNS (a,b)
( PARTITION p0 VALUES LESS THAN ("abc", 1),
PARTITION p1 VALUES LESS THAN ("def", 2));

Some things noteworthy here:
The checks for the constants is fairly strict. Thus using "1" for a constant
to b isn't allowed, the constant must be of the same type as the field it
maps to. Also sql_mode will be ignored for those partition constants.
Thus e.g. even if sql_mode specifies that non-existing dates are allowed
they will not be allowed in the partition constants since these constants
will be a part of the table and need to live for longer than the current
session.

Character sets are allowed and the string constants will be interpreted
in the character set their field belongs to. Also character set strings
without mapping are allowed. If one tries to use SHOW CREATE TABLE
on the partition table and the mapping of the partition constants from
field charset to UTF8 fails or if mapping to client charset fails, then the
partition constants will be written in hex string format.

A partition constant can be MAXVALUE, NULL is however not allowed.
There were some considerations to also be able to use MINVALUE
which effectively would make it possible to create partitions where only
the NULL values can go in. However this is still possible if one knows
the minimum value of the field.

It's possible to partition on integer fields, string fields and date fields.
It's not possible to partition on BLOBs, SETs, ENUMs, GEOMETRY fields,
BIT fields.

2)
CREATE TABLE t1 (a varchar(1))
PARTITION BY LIST COLUMNS (a)
( PARTITION p0 VALUES IN ("a","b","c"));

CREATE TABLE t1 (a varchar(1), b int)
PARTITION BY LIST COLUMNS (a,b)
( PARTITION p0 VALUES IN (("a",1),("b",2),("c",3)));

Noteworthy here is that parenthesis are required when more than one
field is in the list of columns partitioned on. It's required to not use
parenthesis when there is only one field.

NULL values are allowed as in MySQL 5.1 but not MAXVALUE.

3)
CREATE TABLE t1 (a datetime)
PARTITION BY RANGE (TO_SECONDS(a))
( PARTITION p0 VALUES LESS THAN (TO_SECONDS("2009-11-11 08:00:00")),
PARTITION p1 VALUES LESS THAN (MAXVALUE));

Same syntax as in MySQL 5.1 but also possible to use TO_SECONDS as a
partition function.

4)
ALTER TABLE t1 TRUNCATE PARTITION p0;
ALTER TABLE t1 TRUNCATE PARTITION p1;
ALTER TABLE t1 TRUNCATE PARTITION ALL;

This delete all rows in the given partitions and resets the given partitions
auto_increment values (if exists) to 0.

The syntax works in the same manner as for ANALYZE, OPTIMIZE and other
commands that can be applied on partitions already in MySQL 5.1.

5)

CACHE INDEX t1 PARTITION p0 IN keycache_fast;
CACHE INDEX t1 PARTITION p1, p2 IN keycache_slow;
LOAD INDEX INTO CACHE t1 PARTITION p0;
LOAD INDEX INTO CACHE t1 PARTITION p0, p1;

This new syntax makes it possible to have separate key caches for different
partitions in a partitioned table using the MyISAM storage engine.

Improvement of MySQL partitioning included in MySQL's next milestone release tree

2009-11-10T16:47:00.013+01:00

It was quite some time since I last blogged. It's not due to
inactivity. For those of you that have followed my blog might
have seen earlier blog posts about a new partitioning feature.

This new partitioning I first blogged about in July 2006 and
that blog is still the 3rd most popular blog of my blogs, even
when looking at the last months views. The work on this started
out in 2005 and so it's nice to now get it in a state where it's
quality is ready for more heavy testing. For those interested
in partitioning I think this feature will enlarge the number of
cases where partitioning is applicable. It's now possible to
partitioning on many more field types and also on multiple fields
in an efficient manner.

This feature described by WL#3352 has now been pushed
to the mysql-next-mr tree. For those of you new to our new
milestone release model this tree is where we push new features
before clone off. After clone off this tree is merged with
mysql-trunk tree and after about 6 months of bug fixing a Milestone
Release is performed. The current mysql-trunk tree is the tree from
which the current MySQL 5.4 releases are produced. A milestone
release is of beta quality and some milestone releases will be
continued towards a GA release. There will be a new milestone
release with about 3-6 months interval. For more information on
the release model see here.

The WL#3352 was pushed into the Milestone which has the
codename Betony in the article referred to above. The current
MySQL 5.4 release series is called Azalea (actually 5.4.0 and
5.4.1 belonged to Summit and 5.4.2 was the first Azalea release).

The major advantages of this new feature is that it makes it
possible to partition on string fields and also to partition on
multiple fields and still get good partition pruning. Previous
partitioning required a partition function that delivered an
integer result and a couple of functions could deliver good
partition pruning.

Now it is possible to partition on most fields and even a set
of them and always get good partition pruning.

The final result of the new syntax is the following:

CREATE TABLE t1 (a varchar(5) character set ucs2, b int)
PARTITION BY RANGE COLUMNS (a,b)
( PARTITION p0 VALUES LESS THAN (_ucs2 0x2020, 1),
PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE));

So the keyword COLUMNS indicates that a list of fields is
used to partition on instead of a function. The new
partitioning applies to RANGE and LIST partitioning.

All the management functions for partitioning still applies.
However the major difference comes when you do a query like:

select * from t1 WHERE a > _ucs2 0x2020;

In this case the partition pruning will discover that only
the partition p1 is possible to find records and will thus
prune away partition p0. In MySQL 5.1 it's only possible
to perform pruning on intervals for single fields and
the partition function must also be of a type that is
safe to always increase such as YEAR or TO_DAYS (actually
this new feature also added the function TO_SECONDS to this
list of functions that can be pruned on efficiently).

So partition pruning on this works very much like an
index. Not surprisingly the partition pruning code
reuses the code for the range optimiser which looks
at what indexes can be used for a certain query.

If you wonder why I am using these _ucs2 constants
as examples it's because I had to learn a lot about the
character set code in MySQL to get everything right with
this feature. Actually even found and fixed a few MySQL
character set bugs in the process :)

New launchpad tree for Column List Partitioning

2009-09-15T17:46:00.008+02:00

I have added a new Launchpad tree for an improved
partitioning feature.

This new tree is based off mysql-trunk which is the base for
the next generation MySQL Server. The tree is now entering QA
and have been extensively tested by development and thus it is
very interesting to get feedback on usability of feature and
feedback on quality issues. This will speed up the delivery of
this new feature.

You can find more description of the feature in a previous blog:
Description of feature

Partition by column_list ready for alpha testers

2009-08-04T15:23:00.005+02:00

I got time to spend on a really old worklog I completed coding
already october 2005. I blogged about it in July 2006 and
interestingly enough it's still the second most read blog
entry on my blog (probably related to search engines in some
way).

I have merged it with the azalea tree (this is an internal code
name for our development tree, name is likely to change). This
tree contains subquery optimisations, Batched join and some more
optimisations.

I have fixed a whole bunch of bugs that always shows up in early
code. The code quality is still alpha but at least you won't find
10 bugs per hour :)

Here you can find the launch pad tree for this code.

There are two important additions made possible by this tree.
1) New function to_seconds that is recognized by range optimiser
to enable partition pruning when partitioning like:
partition by range (to_seconds(time))
2) New partitioning functionality that makes it possible to
perform partition pruning over multiple fields.

Most of the bugs I have fixed had to do with this partition pruning
of multiple fields. The routine to discover which partitions are
needed is called find_used_partitions (in sql/opt_range.cc) and this
function is called recursively over a key tree. A key tree can be
very complex and more or less have AND of key parts using next_key_part
pointer and OR condition using left and right pointers. These left and
right pointers can however show up a little here and there in the tree
so one has to be very careful about how variables are assigned, saved
and restored. I havent' worked so much with recursive functions so this
is an interesting adventure.

Here's my latest addition of a test case to give you an idea of how it
works and also what works right now.

create table t1 (a int, b char(10), c varchar(5), d int)
partition by range column_list(a,b,c)
subpartition by key (c,d)
subpartitions 3
( partition p0 values less than (column_list(1,'abc','abc')),
partition p1 values less than (column_list(2,'abc','abc')),
partition p2 values less than (column_list(3,'abc','abc')),
partition p3 values less than (column_list(4,'abc','abc')));

insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3);
insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3);
insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3);
insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3);
select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR
(a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2))));

So in the above select statement we are performing partition
pruning over 3 fields and subpartition pruning over 2 fields
and there are 5 different ranges in the query.

So please go ahead and try this new tree out and see if it
works for you.

New update to DBT2 clone with automated sysbench runs

2009-07-08T20:03:00.007+02:00

I have made an update to the DBT2 clone where I packed in all my
benchmarking support scripts.

This update adds a new script bench_prepare.sh that should be run
from the benchmark server and uses the input of 3 tarballs, the
DBT2 tarball, the sysbench tarball and a MySQL tarball. It will
automatically build all needed binaries on both the benchmark
server and on the MySQL Server machine (they could be on same
machine or on different machine).

The script only requires one parameter --default-directory where
one configuration file called autobench.conf should be placed.
This directory will also be used to house all result files,
builds and generated configuration files for all involved scripts.

The aim is to continue develop such that we can also benchmark
easily using different Linux versions.

The tarball can be downloaded from here

The script can also handle a MySQL Server which is Windows-based,
but the benchmark server cannot run Windows for the moment.

Follow-up Analysis of Split Rollback Segment Mutex

2009-06-05T12:08:00.003+02:00

I performed a new set of tests of the patch to split the
rollback segment mutex on Linux. All these tests gave
positive results with improvements in the order of 2%.

One could also derive from the results some conclusions.
The first conclusion is that this split mainly improves
things when the number of threads is high and thus
contention of mutexes is higher. At 256 threads a number
of results improved up to 15%.

The numbers on lower number of threads were more timid
although in many cases an improvement was still seen.

What was also noticeable was that the sysbench read-write
with less reads which makes the transactions much shorter
the positive impact was much greater and the positive
impact on long transactions was much smaller (+0.4%
versus +2.5%). The impact on the short transaction test
with less reads was very positive also on lower number
of threads, the result on 32 threads improved 7%.

So the conclusion is that this patch is a useful contribution
to improvements and in particular improves matters on high
number of threads and with short transactions. According to
a comment on the previous blog it is also very positive in
insert benchmarks.

Results of shootout on split page hash in InnoDB

2009-06-04T18:06:00.007+02:00

I have now tried out the buffer split page hash patches on
both a Linux/x86 box and a SPARC/Solaris server (tests done
by Dimitri).

The three variants in short description are:
1) The Google v3 derived patch. This introduces a new array
of mutexes that only protect the buffer page hash. Thus some
extra checking is needed to ensure the page hasn't been
removed from the hash before using it. This is a very simple
and attractive patch from that point of view. The patch uses
an array of 64 mutexes.

2) A variant I developed with some inspiration from the Percona
patches. This patch uses an array of page hashes which each has
its own read-write lock. I've tried this with 1, 4 and 16 page
hashes and 4 is the optimum number. The rw-lock protects the
page hash long enough to ensure that the block hasn't been
possible to remove from the hash before the mutex is acquired.

3) The last variant is a mix of the two first which uses the
simplicity of the Google patch, uses a rw-lock instead and
separate page hashes (to ensure read ahead doesn't have to
go into all mutexes). Used an array of 4 page hashes here.

The conclusion is that the only version that has consistently
improved the MySQL 5.4.0 numbers is the version I originally
developed (2 above).

On sysbench read-write all versions improve numbers compared to
MySQL 5.4.0. 2 and 3 improve 2% whereas the original Google
patch improved with 1%.

On sysbench read-only on Linux it was much harder to beat the
MySQL 5.4.0 version. Only 2) did so and only by 0.5%. This is
not so surprising since this mutex is not a blocker for read-only
workloads. 1) gave -1% and 3) gave -0.3%.

On a write intensive workload on Linux 1) and 3) performed 0.5%
better than MySQL 5.4.0 whereas 2) gave 2% improvement.

Finally on a sysbench read-write with less reads on Linux, all
variants lost to MySQL 5.4.0. 1) by 2%, 2) by 0.1% and 3) by
1%.

Also the numbers from SPARC/Solaris give similar data. The major
difference is that the positive impact on SPARC servers is much
bigger, all the way up to 30% improvements in some cases. The
most likely reason for this is that SPARC servers
have bigger CPU caches and are thus more held back by lack of
concurrency and not so much by increased working set. The x86
box had 512kB cache per core and a 2MB L3 cache and is likely
to be very sensitive to any increase of the working set.

So the likely rationale for worse numbers in some cases is that
more mutexes or rw-locks gives more cache misses.

So given the outcome I will continue to see if I can keep the
simplicity of the Google patch and still maintain the improved
performance of my patch.

Some ideas on InnoDB kernel_mutex

2009-06-03T14:00:00.004+02:00

I've noted that one reason that InnoDB can get difficulties
when there are many concurrent transactions in the MySQL Server
is that the lock time of the kernel_mutex often increases
linearly with the number of active transactions. One such
example is in trx_assign_read_view where each transaction
that does a consistent read creates a copy of the transaction
list to be able to deduce the read view of the transaction or
statement.

This means that each transaction is copied to the local transaction
list while holding the critical kernel_mutex.

Another such case is that most operations will set some kind of
intention lock on the table. This lock code will walk through
all locks on the table to check for compatible locks and the
first time it will even do so twice. Thus if all threads use the
same table (as they do in e.g. sysbench) then the number of locks
on the table will be more or less equal to the number of active
transactions.

Thus as an example when running with 256 threads compared to 16
threads the kernel_mutex lock will be held for 16 times longer
and possibly even more since with more contention the mutex is
needed for even longer time to start up waiting transactions.

So this is an obvious problem, so what is then the solution?
Not extremely easy but one thing one can do is to make the
kernel_mutex into a read-write lock instead of a mutex. Then
many threads can traverse those lists in parallel. It will
still block others needing write access to the kernel_mutex
but it should hopefully improve things.

Another solution that is also going to improve the problem is
to use thread pools. Thread pools ensure that not as many
threads are active at a time. However we still have a problem
that transactions can still be as many active in parallel as
there are connections (although InnoDB has a limit of 1024
concurrent active transactions). So the thread pool needs
to prioritize connections with active transactions in cases
where there are too many threads active at a time.

This type of load regulation is often used in telecom systems
where it is more important to give priority to those that have
already invested time in running the activity. Those that are
newcomer comes in when there are empty slots not taken by
already running activities.

Increasing log file size increases performance

2009-06-02T21:07:00.004+02:00

I have been trying to analyse a number of new patches we've
developed for MySQL to see their scalability. However I've
have gotten very strange results which didn't at all compare
with my old results and most of changes gave negative impact :(
Not so nice.

As part of debugging the issues with sysbench I decided to go
back to the original version I used previously (sysbench 0.4.8).
Interestingly even then I saw a difference on 16 and 32 threads
whereas on 1-8 threads and 64+ threads the result were the same
as usual.

So I checked my configuration and it turned out that I had changed
log file size to 200M from 1300M and also used 8 read and write
threads instead of 4. I checked quickly and discovered that the
parameter that affected the sysbench results was the log file size.
So increasing the log file size from 200M to 1300M increased the
top result at 32 threads from 3300 to 3750, a nice 15% increase.
The setting of the number of read and write threads had no
significant impact on performance.

This is obviously part of the problem which is currently being
researched both by Mark Callaghan and Dimitri.
Coincidentally Dimitri has just recently blogged about this and
provided a number of more detailed comparisons of the
performance of various settings of the log file size in InnoDB.

MySQL 5.4 Webinar

2009-05-20T08:14:00.004+02:00

The quality of MySQL 5.4.0 is very high for a beta product.
Four weeks after we released it as beta we have not had
any real serious bugs reported yet. There are some issues
due to deprecation of features, version numbers and a
bug in the SHOW INNODB STATUS printout and some concerns
with the new defaults when running on low-end machines.
It's also important as usual to read the documentation
before upgrading, it contains some instructions needed to
make an upgrade successful. The upgrade issue comes from
changing the defaults of the InnoDB log file sizes.

For those of you who want to know more about MySQL 5.4.0
and it's characteristics and why you should use it, please
join this webinar where Allan Packer will explain what
has been done in MySQL 5.4.0.

Patches ready for buf page hash split shootout

2009-05-19T14:37:00.006+02:00

Today I created a patch that builds on the Google v3
patch where I added some ideas of my own and some ideas
from the Percona patches. The patch is here.

Here is a reference to the patch derived from the Google
v3 patch.

Here is a reference to my original patch (this is likely to
contain a bug somewhere so usage for other than benchmarking
isn't recommended).

So it will be interesting to see a comparison of all those
variants directly against each other on a number of benchmarks.

Analysis of split flush list from buffer pool

2009-05-19T13:15:00.012+02:00

In the Google v3 patch the buffer pool mutex have been
split into an array of buffer page hash mutexes and a
buffer flush list mutex and the buffer pool mutex also
remains.

I derived the patch splitting out the buffer flush list
mutex from the Google v3 patch against the MySQL 5.4.0
tree. The patch is here.

I derived a lot of prototype patches based on MySQL 5.4.0
and Dimitri tried them out. This particular patch seems
to be the most successful in the pack of patches we
tested. It had a consistent positive impact.

The main contribution of this patch is twofold. It
decreases the pressure on the buffer pool mutex by
splitting out a critical part where the oldest dirty
pages are flushed out to disk. In addition this patch
also decreases the pressure on the log_sys mutex by
releasing the log_sys mutex earlier for the mini-
transactions. In addition it removes interaction
between the buffer pool mutex and the log_sys mutex.
So previously both mutexes had to be held for a
while, this is no longer necessary since only the
flush list mutex is needed, not the buffer pool
mutex.

The new patch is the b11 variant which is red in
the comparison graphs.

As we can see the read-write tests have a pretty significant boost
from this patch, it improves top performance by 5% and by 10-20%
on higher number of threads. It also moves the maximum from 16 to
32 threads.

Even on read-only there are some positive improvements although
it is very possible those are more random in nature.

Finally the above picture shows that this patch also moves the
optimal InnoDB thread concurrency up to 24 from 16 since it
allows for more concurrency inside InnoDB. This is also visible
by looking at the numbers for InnoDB Thread Concurrency set to 0
as seen below.

Shootout of split page hash from InnoDB buffer pool mutex

2009-05-15T09:56:00.006+02:00

One of the hot mutexes in InnoDB is the buffer pool mutex.
Among other things this mutex protects the page hash where
pages reside when they are in the cache.

There is already a number of variants of how to split out
this mutex. Here follows a short description of the various
approaches.

1) Google v3 approach
Ben Hardy at Google took the approach of using an array of
mutexes (64 mutexes) and this mutex only protects the
actual read, insert and delete from the page hash table.
This has the consequence of a very simple patch, it means
also that when the block has been locked one has to check
that the owner of the block hasn't changed since we didn't
protect the block between the read of the hash and the
locking of the block, thus someone is capable of coming in
between and grabbing the block for another page before we
get to lock the block. In addition this patch focuses
mainly on optimising the path in the buf_page_get_gen
which is the routine used to get a page from the page
cache and thus the hot-spot.

2) Percona approaches
Percona has done a series of approaches where the first
only split the page hash as one mutex and still protecting
the blocks from being changed while holding this mutex.
Next step was to change the mutex into a read-write lock.

3) My approach
My approach was inspired by Percona but added two main
things. First it split the page hash into a number of
page hashes and had one RW-lock per page hash (this
number has been tested with 4, 8 and 16 and 4 was the
optimal on Linux at least). In addition to avoid having
to lock and unlock multiple pages while going through
the read ahead code the hash function to decide which
page hash to use decided on the same page hash for all
pages within 1 MByte (which is the unit of read ahead
in InnoDB).

Pros and Cons

The simplest patch is the Google patch which makes for
a very simple patch and also by only focusing on
buf_page_get_gen avoids a lot of possible extra traps
that are likely if one tries to solve too much of the
problem.

Using a RW-lock instead of a mutex seems like at least
a manner of improving the concurrency but could of
course impose a higher overhead as well so here
benchmarking should show which is best here.

When using an array of locks it makes sense to optimise
for read ahead functionality since this is a hot-spot
in the code as has been shown in some blogs lately.

4) Mixed approach
So a natural solution is then to also try a mix of the
Google variant with my approach. So still using an
array of locks (either mutex or RW-locks, whatever
has the optimal performance) but ensuring that the
pages within a read ahead area is locked by the same
lock.

This approach reuses the simplicity of the Google
approach, the total lack of deadlock problems for
the Google approach with the optimised layout from
my approach and the idea of RW-locks from Percona.

We don't have any results of this shootout yet.
This shootout should also discover the optimum number
of areas to split the page cache into, Google has
used 64, but my results so far indicates a number of
4 seems more appropriate.

More analysis of InnoDB Thread Concurrency

2009-05-14T14:53:00.010+02:00

When I worked with Dimitri on the analysis of the
Split Rollback Segment Mutex he came up with numbers
on InnoDB Thread Concurrency set to 16 and 32 and I was curious
to see if 24 was the optimal setting. So he made some new runs and
some new graphs that I found interesting.

The first graph analyses behaviour of MySQL 5.4.0 on a SPARC
Server using InnoDB Thread Concurrency set to 0, 16, 24 and 32.
Interestingly for both readonly and readwrite benchmarks the
optimal setting for concurrency is 16 whereas the top numbers
(at 32 threads) is achieved with concurrency set to 24 or 32.

So on the current MySQL 5.4.0 on this particular benchmark and
platform it seems that 16 is the optimal setting. However Dimitri
also analysed the same thing using the new patch for Splitting the
Rollback Segment Mutex and now the story changes.

So with this patch setting InnoDB Thread Concurrency to 24
is now the optimum setting. So it's clear that as we get more
and more improvements to the scalability of the MySQL Server and
InnoDB it will be optimal with more and more parallel threads
inside InnoDB as well. So this means that this setting is quite
likely to change as development proceeds but for MySQL 5.4.0 a
setting of around 16-24 is often a good one. To actually change
the default setting requires much more testing of various
workloads on many different computer architectures.

Similar testing I have performed on Linux using sysbench implies
that the optimal setting is around 24-28. Also the difference
between setting it to 0 and 24 is much smaller on Linux (15%
on 256 threads as shown in blog yesterday). We haven't analysed
the big difference on these SPARC Servers.

Analysis of Split of Rollback Segment Mutex

2009-05-14T11:33:00.009+02:00

When I read the blog about Split Rollback Segment Mutex,
I was interested to verify those results in the context of MySQL 5.4.0.

The patch can be found here.

We've analysed this patch both on a large SPARC system and on my
benchmark x86/Linux machine. Our results tend to be positive for
readwrite benchmarks but sometimes negative for readonly
benchmarks. Also the gain is much smaller than found in the
blog.

Also this patch has two negative effects, the first is that it
provides an upgrade problem, this can probably be handled in the
InnoDB code, but requires quite some digging. The other is that
instead of writing UNDO results to one UNDO log, we write it to
several UNDO logs, thus decreasing the buffering effect to the
file system.

On Linux I found on readwrite benchmarks up to 7-8%
improvements of the top results. On readonly it sometime dropped
about 1-3%. I also tried with varying numbers of rollback
segments and found 4 and 8 to be better than 16. So from the
above point of view the number of rollback segments set to 4 is
probably best. The patch uses 8 (it's actually set to 9 since
the system rollback segment is a bit special).

Here are some graphs from Dimitri running it on some fat SPARC
server (MySQL-5.Perfb9-gcc43 is 5.4.0 plus the above patch).

The first graph shows the behaviour when InnoDB Thread Concurrency
is 0, here we see a speedup in the range of 3-5%.

The same results for the readonly benchmark shows positive results as well.

When one sets InnoDB Thread Concurrency equal to 16, 24 or 32
the behaviour is different. It turns out that we get worse
performance using 16 but get more positive impact using 24 and
even more using 32. So it seems that this patch requires less
limits to parallelism to get the best behaviour.

So one impact of this patch is that it can sustain a higher
number of concurrent threads and there is a small positive impact
on the performance as well.

More data on InnoDB Thread Concurrency

2009-05-13T18:00:00.006+02:00

Here is the performance graph comparing using
InnoDB Thread Concurrency equal to 0 and
InnoDB Thread Concurrency equal to 24 using
sysbench readwrite with the new InnoDB
Thread concurrency algorithm as introduced
in MySQL 5.4.0.

Analysis of Google patches on 4,8 and 12 cores

2009-05-13T17:35:00.009+02:00

One of the goals we had originally with the MySQL 5.4
development was to improve scaling from 4 cores to
8 cores. So in my early testing I ran comparisons of
the Google SMP + IO + tcmalloc patches on 4, 8 and 12
cores to see how it behaved compared with a stock
MySQL 5.1.28 version (Note the comparison here was
done on a very early version of 5.4, 5.4.0 have a
set of additional patches applied to it).

What we can see here is that the Google SMP patch and use
of tcmalloc makes a difference already on a 4-core server
using 4 threads. On 1 and 2 threads the difference is only
on the order of 1-2% so not really of smaller significance.

An interesting note in the graph is that 8-core numbers using
the Google improvements outperform the 12-core stock MySQL
5.1.28.

So what we concluded in those graphs is that the scaling from 4-cores
to 8-cores had improved greatly and that there also was a good scaling
from 8 cores to 12 cores. This improvement increased even more with
the 5.4 release. The main purpose of showing these numbers is to show
the difference between 4, 8 and 12 cores.

All benchmarks were executed on a 16-core x86 box with 4 cores
dedicated to running sysbench.

Analysis of Google patches in MySQL 5.4

2009-05-13T13:32:00.017+02:00

Early on in the MySQL 5.4 development we tried out the
impact of the Google SMP patch and the Google IO patch.
At first we wanted to see which of the patches that
made most of an impact. The Google patches in MySQL 5.4
have 3 components at least that impact the performance.
1) Replace InnoDB memory manager by a malloc variant
2) Replace InnoDB RW-lock implementation
3) Make InnoDB use more IO threads

When disabling the InnoDB one opens up for a whole array
of potential candidates for malloc. Our work concluded
that tcmalloc behaved best on Linux and mtmalloc was
best on Solaris, see blog posts on Solaris below.

Malloc on Solaris investigation

Battle of the Mallocators on Solaris

I did also do some testing on Linux where I compared 4 different
cases (all variants were based on MySQL 5.1.28):
1) Using the Google SMP patch, Google IO patch (with 4 read and
4 write threads) and using tcmalloc
2) Using tcmalloc and no other Google patches
3) Using plain malloc from libc
4) Using plain MySQL 5.1.28 using InnoDB memory manager

Here are the results:

So as we can see here the replacement of the InnoDB memory manager
by standard malloc had no benefits whereas replacing it with
tcmalloc gave 10% extra performance. The Google SMP patch added
another 10% performance in sysbench readwrite. We have also
tested other OLTP benchmarks where the Google SMP patch added
about 5-10% performance improvement. As shown by Mark Callaghan
there are however other benchmarks where the Google SMP patch
provides much greater improvements.

MySQL 5.4 Patches: InnoDB Thread Concurrency

2009-05-12T19:08:00.003+02:00

When benchmarking MySQL with InnoDB we quickly discovered
that using InnoDB Thread Concurrency set to 0 was an
improvement to performance since the implementation of
InnoDB Thread Concurrency used a mutex which in itself was
a scalability bottleneck.

Given that InnoDB Thread Concurrency is a nice feature that
ensures that one gets good performance also on an overloaded
server I was hoping to find a way to make the implementation
of this more scalable.

I tried out many different techniques using a combination of
mutexes and atomic variables. However every technique fell to
the ground and was less performant than setting it to 0 and not
using the InnoDB Thread Concurrency implementation. So I was
ready to give up the effort and move on to other ideas.

However after sleeping on it an inspirational idea came up.
Why use a mutex at all, let's see how it works by using the
OS scheduler to queue the threads that need to blocked. This
should be more scalable to use than a mutex-based approach.
There is obviously one bad thing about this approach and this
is due to that new arrivees can enter before old waiters. To
ensure we don't suffer too much from this a limit on the wait
was necessary.

So I quickly put together a solution that called yield once
and slept for 10 milliseconds twice at most and every time it
woke up it was checking an atomic variable to see if it was ok
to enter. After those three attempts it would enter without
checking.

I tried it and saw a 1% decrease on low concurrency and 5%
improvement on 32 threads and 10% on 64 threads and 15% on 128
threads. Voila, it worked. Now I decided to search for the
optimal solution to see how many yields and sleeps would be best.
It turned out I had found the optimal number at the first attempt.

The implementation still has corner cases where it provides less
benefits so I kept the possibility to use the old implementation by
adding a new variable here.

So currently the default in MySQL 5.4 is still 0 for InnoDB Thread
Concurrency. However we generally see optimal behaviour using
InnoDB Thread Concurrency set to around 24, setting it higher is
not bringing any real value to MySQL 5.4.0 and setting it lower
decreases the possible performance one can achieve. This seems
to be a fairly generic set-up that should work well in most cases.
We might change the defaults for this later.

Join Executor for MySQL Cluster

2009-04-23T16:19:00.005+02:00

Jonas in the Cluster team reported on his work on executing
joins in the NDB kernel for MySQL Cluster here.

This is a very interesting work we have in progress at MySQL.
We are working on an extension of the Storage Engine API
where the MySQL Server will present an Abstract Query Tree
to the Storage Engine. The Storage Engine can then decide to
execute the query on his own or decide that the MySQL Server
should execute it in the classic manner. In the first prototype
the optimisation will be done as usual and only after the
optimisation phase will we present the join to the storage
engine. However the specification also covers work on
integrating this with the optimiser and also enabling the
possibility for the storage engine to execute parts of the
query and not the entire one. The specification of this
work can be found here.

Jonas is working on the backend part for this interface in
MySQL Cluster.

What is interesting with pushing joins to the NDB kernel is that
it becomes very easy to parallelize the join execution. So what
will happen when this feature is ready is that MySQL Cluster
will shine on join performance and enable very good
performance on all sorts of application using SQL.

The reason that MySQL Cluster can so easily parallelize the query
execution of the join is due to the software architecture of the
NDB kernel. The NDB kernel is entirely developed as a message
passing architecture. So to start a thread of execution in the
NDB kernel one simply sends two messages when executing one
message and to stop a thread one simply doesn't send any messages
when executing a message. The problem then is more on that one
should not parallelize too much to run out of resources in the
system.

So with this development MySQL Cluster will also be shining at
Data Mining in an OLTP database. MySQL Cluster is designed for
systems where you need massive amounts of read and write
bandwidth (the cost of writing your data is close to the cost
of reading the data). So with the new features it will be
possible to do Data Mining on data updated in Real-time. Most
Data Mining is performed on a specialised Data Warehousing
solution. But to achieve this you need to transfer the data to
the Data Warehouse. With MySQL Cluster it will be possible to
both use the database for OLTP applications with heavy updates
always occuring while still querying the data with parallel
queries in parallel. MySQL Cluster is very efficient at
executing individual queries in the NDB kernel and can also
scale to very many machines and CPU cores.

Data on MySQL Performance

2009-04-23T15:55:00.004+02:00

If you like to sift through tons of benchmark data about various
MySQL versions, Dimitri at the Sun Benchmark Labs have published
a serious amount of benchmark data in a report published here.

The report shows that the new MySQL 5.4.0 release
have a very good performance. The report also shows how the day
of a developer of performance improvements and the massive amount
of benchmark data that needs to be analysed and sifted through
to understand the impact of new performance improvements.

I personally met Dimitri the first time in 2002 when I was working
together with him for a couple of weeks on a benchmark on NDB Cluster
(the storage engine of MySQL Cluster). Our goal then was to perform
1 million reads per second on a 72-cpu SPARC box with UltraSparc-III
CPU's @900MHz. We struggled a lot at the time but finally we managed
to achieve the numbers we were hoping for. We actually surpassed the
goal and reached 1.5 million reads per second and we also tried an
update benchmark where we managed to do 340.000 update transactions
per second (generating a disk write bandwidth of 250 MByte per second).

This benchmark was interesting from a scientific point of view. When
I defended my Ph.D thesis I claimed that one could get superlinear
performance increases when adding more CPU's to a problem in the
database world. To achieve this the workload needs to be constant and the
number of CPU's increased. By increasing the number of CPU's and keeping
the workload constant more CPU cache memory is used on the problem.
This means that each CPU will execute more efficiently.

In the above benchmark we managed to verify my claim that I made when
defending my Ph.D thesis which I found very positive. The results we
achieved on a 16-node cluster was 500.000 reads per second and on a
32-node cluster we reached 1.500.000 reads per second.

Dimitri has a background from the past of developing his own
homegrown database, so we have had many interesting discussions both
then and now on how to achieve the best performance of NDB and
the MySQL Server.

MySQL 5.4 Patches: Improvements to spin-loop

2009-04-22T15:37:00.003+02:00

In InnoDB there is an implementation of both mutexes
and RW-locks. The RW-locks implementation have been
improved by the Google SMP patches. Both of these
implementation relies on spin-loops as part of their
implementation. The defaults in InnoDB is to check
the condition, if it's not ok to enter to spin for
about 5 microseconds and then come back to check the
condition again.

If one reads the Intel manual how to do spin-loops
they propose to use a PAUSE instruction and then
check the condition again, so a much more active
checking of the condition. When we tried this out
using the sysbench benchmark we found that using
the Intel approach worsened performance. So instead
we tried an approach of putting the PAUSE instruction
into the InnoDB spinloop instead.

This approach turned out to be a success. Even on
machines with only one thread per core we were able
to get a 3-4% increase in throughput. We also tried
various settings of the defaults of the time of
spinning in the spinloop and found that the original
default values were very close to the optimum values.
We found the optimum about 20% from the old default
values and made this slight change to the default
values of the spinloop.

It's my expectation that as we remove locks and the
mutexes and RW-locks gets less contended and there
are more locks where the threads are waiting that
this optimum value will change. The current best
setting is very likely to be governed by the fact
that the most waiting happens on very hot locks.
So with improvements of the mutexes and RW-locks
we should expect to see better performance with
a shorter time in the spinloop.

On the new SPARC CPU's that Sun has developed, the
CMT boxes, we used the results from the paper:
www.ideal.ece.ufl.edu/workshops/wiosca08/paper2.pdf
which stated that the optimum instruction to use
is a cache miss instruction, however as I don't
know how to program a cache miss instruction we
opted for the second best instruction which was a
dummy test-and-set instruction. So the PAUSE
instruction is replaced by a test-and-set instruction
on SPARC CPU's.

We expect that the improvements due to this small
change is even bigger when there are multiple
threads per core since the contention on the
CPU pipeline is higher in those cases and it is
important that the spinloop stays away as much
as possible from being active executing
instructions.

MySQL 5.4 Scaling to 16 way x86 and 64-way CMT Servers

2009-04-21T16:46:00.004+02:00

The release of the MySQL 5.4 contains patches which
increases the scalability of the MySQL Server. I am planning to blog
about those changes in some detail over the next few days. This blog
will give an introduction and show what the overall results we have
achieved are.

The changes we have done in MySQL 5.4 to improve scalability and
the ability to monitor the MySQL Server are:

1) Google SMP patch
2) Google IO patches
3) Update of many antiquated defaults in the MySQL Server
4) New InnoDB Thread Concurrency algorithm
5) Improved Spinloop in InnoDB mutexes and RW-locks
6) A couple of performance fixes backported from 6.0
7) Operating system specific optimisations
8) Ported the Google SMP patch to Solaris x86 and SPARC and work
underway for Windows and Intel compiler as well
9) Introducing DTrace probes in the MySQL Server
10) A build script to make it easier for community to build an efficient
MySQL Server based on source code

As an example of the improvements made available through this work we
have some benchmarks using sysbench readwrite and readonly.

We have consistently seen improvements in the order of 30-40% of
sysbench top numbers and on large number of threads 5.4.0 drops
much less in performance than 5.1. The new InnoDB Thread Concurrency
patch makes the results on high number of threads even more
impressive where the results have gone up by another 5-15% at the
expense of 1% less on the top results (there are even some DBT2
runs that gave 200% improvement with the new algorithm).

There is also a benchmark on EAStress which shows a 59% increase in
performance from 5.1 to 5.4 using the new 16-way x86 Nehalem servers.

MySQL Cluster 7.0 scales linearly in two dimensions

2009-04-21T16:45:00.001+02:00

As recently reported on my blog we have managed to get MySQL Cluster CGE 6.3 to scale linearly with the addition of more nodes into the system.
In MySQL Cluster CGE 6.3 each node has a single thread handling most of
the work together with a set of file system threads.

In MySQL Cluster 7.0 the data nodes are now multithreaded. The design in
7.0 follows the very efficient design of 6.3 where each thread has absolutely no lock contention with other threads. All communication
between threads happens through messages. This means that scalability
of the data nodes is excellent. The single thread have been split into
up to four local data threads, one transaction handling threads,
and one socket communication thread plus the already existing file
system threads. With this set-up each data node can process 4.6X more
DBT2 transactions compared to 6.3.

This means that a 2-node cluster in 7.0 has the same performance as a
10-node cluster for 6.3 and a 4-node cluster similar performance to a
20-node cluster in 6.3. As earlier blogged each data node can handle
many hundreds of thousands of operations per second, so a cluster of
such nodes can handle many millions of operations per second.

The efficiency of the data node is such that one data node can handle
the traffic from a set of MySQL Servers residing on a 24-core MySQL
Server. So an example of a basic set-up for the MySQL Cluster 7.0 is
to use 2 8-core boxes with lots of memory and lots of disk bandwidth
for the data nodes. To use 2 24-core servers for the MySQL Servers that
mostly require CPU and networking bandwidth.

An important consideration for setting up a MySQL Cluster 7.0 is to
ensure that interrupts from the network stack doesn't kill performance
and also to have separate network infrastructure between the data
nodes in the cluster since it is very easy to overload the network
given the capabilities of the MySQL Cluster software.

MySQL 5.4 Acknowledgements

2009-04-21T16:42:00.004+02:00

The work started when MySQL was acquired by Sun has now started to bear
fruit. Very soon after the acquisition a Sun team was formed to assist
the MySQL performance team on improving the scalability of the MySQL
server. At the same time also Google have been very active in improving
scalability of InnoDB. MySQL 5.4 scalability improvements is very much
the result of the efforts from the MySQL Performance team, the Sun
performance team and the Google efforts.

It's extremely fruitful to work with such a competent set of people. The
Sun team has experience from scaling Oracle, DB2, Informix and so forth
and knows extremely well how the interaction of software and hardware
affects performance. The Google patches have shown themselves to be of
excellent quality. From our internal testing we found two bugs in the
early testing and both those had already been fixed by the Google team
and so turnaround time was a day or two. For the last months we haven't
found any issues. The MySQL performance team have also been able to add
a few small but effective improvements on top of the Google patches.

MySQL 5.4 also introduces DTrace support in the MySQL Server. This code
is a result of a cooperation with the MySQL 6.0 development team, the
original patch was developed for 6.0. We have spent quite some time
on getting the DTrace support working on all variants of Solaris and
Mac OS X platforms. For anyone interested in getting DTrace probes into
their application I think the MySQL example is probably the most
advanced example currently available on user-level DTrace probes and
building such DTrace probes into a complex build system.

Working with competent and motivated people is always great fun, so this
has been a very rewarding project for me personally. I have always liked
to work on performance improvements, in my work on founding
MySQL Cluster we were involved in many such efforts and so far they have
almost always been successful. Actually we're releasing a new version
of MySQL Cluster 7.0 now as well with its own set of extreme performance
improvements which I will mention in a separate blog.

New DBT2 version uploaded with more documentation of new scripts

2009-01-17T19:51:00.003+01:00

I have had a number of request for help on how to use the DBT2
tree I'm maintaining on www.iclaustron.com. There is an extensive
set of scripts used to make it very easy to run DBT2 runs and
to start and stop cluster nodes and MySQL Servers. I personally
use it also to start MySQL Servers and clusters also when not
using DBT2.

However these scripts haven't had an overall description yet
although each component is very thoroughly documented by
using --help on the scripts (I tend to document very
heavily these things since I otherwise forget it myself).

Now I added a new README file README-ICLAUSTRON which
explains which scripts are used and their relation
and which configuration files to set-up and a
pointer to example configuration files.

Hopefully this will make it easier to use DBT2,
particularly DBT2 for MySQL Cluster.

LOCK_open, THE mutex :)

2008-12-03T23:43:00.004+01:00

In all my days at working at MySQL the
LOCK_open mutex have always been a
key mutex to understand, now that I'm
working on scalability improvements of
the server it's as important to change
this mutex into something less contentious.

So last week I finally decided to start
thinking about how we can resolve this
mutex which is at the heart of the MySQL
Server. In principle the idea is that
LOCK_open has been used to protect a
hash table with all the open tables in
the MySQL Server. However it has been
used for many other purposes as well.
So it's not trivial to move around it.

However the main scalability problem
with LOCK_open is the hash lock it
provides. So what to do about it?

My current thinking is that a four-thronged
approach will do the trick.

1) Divide and concquer, perform the hash calculation
outside of the mutex and divide the hash into
e.g. 16 smaller hashes. This creates one problem
which is how to prune the open table cache.
Obviously there is no longer a simple linked
list where I can find the oldest entry. This
problem I'm still contemplating, there's
probably already a number of known good solutions
to this problem since I find it popping up in
almost every similar design. So it's a problem
looking for a solution pattern.

2) Shrink the amount of data it protects
by only allowing it to protect the hash table
and nothing more. This means e.g. that some
counters need to be updated with atomic
instructions instead.

3) Shrink the time it is protected by inserting
the table share into the hash rather than the
table object (this is actually Monty's idea).

4) Use a different technique for the lock that
works better for short-term locks (usually
spinlocks are more successful here).

A combination of these techniques will hopefully
make it possible to decrese the impact of
LOCK_open on the server code.

Recovery features for ALTER TABLE of partitioned tables

2008-12-03T21:12:00.002+01:00

A feature which hasn't been so public about the implementation
of partitioning is the support for atomicity of many ALTER TABLE
statements using partitioned tables.

This atomicity exists for
ALTER TABLE ADD PARTITION ....
ALTER TABLE REORGANIZE PARTITION ...
ALTER TABLE DROP PARTITION ...
ALTER TABLE COALESCE PARTITION

Given that partitioning often works with very large tables it
was desirable to have a higher level of security for ALTER TABLE
of partitioned tables. To support this a DDL log was implemented.
This DDL log will in future versions be used also for many other
meta data statements. The DDL log will record all files added,
renamed and dropped during an ALTER TABLE command as above.

The design is done in such a way that the ALTER TABLE will either
fail and then all temporary files will be removed (even in the
presence of crashes of MySQL Server). Otherwise the ALTER TABLE
will succeed even if not all old files have been removed at
the time of crash. The DDL log will be checked at restart of
MySQL Server and will REDO or UNDO all necessary changes to
complete the ALTER TABLE statement.

Given that MySQL Server crashes isn't likely to happen very often
in customer environments it was also desirable to add error
injection to the MySQL Server for testing purposes.

Here is a short cut from the file sql_partition.cc that displays
what happens here:

if (write_log_drop_shadow_frm(lpt) ||
ERROR_INJECT_CRASH("crash_drop_partition_1") ||
mysql_write_frm(lpt, WFRM_WRITE_SHADOW) ||
ERROR_INJECT_CRASH("crash_drop_partition_2") ||
write_log_drop_partition(lpt) ||
ERROR_INJECT_CRASH("crash_drop_partition_3") ||

At each ERROR_INJECT_CRASH it is possible to prepare
MySQL Server such that it will crash at this point in
the next statement using dbug statements that can
be issued also as SQL statements now.

So here one can see that we first log preparatory
actions, insert a test point, continue with the
next step of ALTER TABLE, insert a new test point,
write the next log entry, insert new test point,
and so forth.

With this recovery mechanism the new ALTER TABLE
statements should not cause problems with the
partitioned table after the ALTER TABLE even in
the presence of crashes in the middle of the
ALTER TABLE statement.

Impressive numbers of Next Gen MySQL Cluster

2008-11-25T22:26:00.006+01:00

I had a very interesting conversation on the phone with Jonas
Oreland today (he also blogged about it on his blog at
http://jonasoreland.blogspot.com).

There is a lot of interesting features coming up in MySQL Cluster
version 6.4. Online Add Node is one of those, which can be done
without any downtime and even with almost no additional memory
needed other than the memory in the new machines added into the
cluster. This is a feature I started thinking almost 10 years ago
so it's nice to see the fourth version of the solution actually be
implemented and it's a really neat solution to the problem,
definitely fitting the word innovative.

The next interesting feature is to use a more efficient protocol
for handling large operations towards the data nodes. This makes it
use less bits on the wire, but even more it saves a number of copy
stages internally in the NDB data nodes. So this has a dramatic
effect on performance of reads and writes of large records. It
doubles the throughput for large records.

In addition the new 6.4 version also adds multithreading to the
data nodes. Previously the data nodes was a very efficient single
thread which handled all the code blocks and also the send and
receive handling. In the new 6.4 version the data nodes are split
into at least 4 threads for database handling, one thread for send
and receive and the usual assistance threads for file writes and
so forth. This means that a data node will fit nicely into a 8-core
server since also 1-2 cpu's are required for interrupt handling and
other operating system activity.

Jonas benchmarked using a benchmark from our popular flex-series
of benchmark. It started with that I developed flexBench more
than 10 years ago, it's been followed by flexAsynch, flexTT and a
lot more variants of the same type. It can vary the number of
threads, the size of the records, the number of operations per
batch per thread and a number of other things. flexAsynch is
really good at generating extremely high loads to the database
without doing anything useful itself :)

So what Jonas demonstrated today was a flexAsynch run where he
managed to do more than 1 million reads per second using only
one data node. MySQL Cluster is a clustered system so you can
guess what happens when we have 16, 32 or 48 of those nodes
tied together. It will do many tens of millions of reads per
second. An interesting take on this is an article in
Datateknik 3.0 (a magazine no longer around) where I was
discussing how we had reached or was about to reach 1 million
reads per second. I think
this was sometime 2001 or 2002. I was asked where we
were going next and I said that 100 million reads per
second was the next goal. We're actually in range of
achieving this now since I also have a patch lying
around which can increase the number of data nodes
in the cluster to 128 data nodes whereby with good
scalability a 100 million reads per second per
cluster is achievable.

When Jonas called he had achieved 950k reads and then I told
him to try out using the Dolphin DX cards which were also
available on the machines. Then we managed to increase the
performance to inch over 1 million upto 1.070.000.
Quite nice. Maybe even more impressive that it also was
possible to do more 600.000 write operations per second
(these are all transactional).

This run of flexAsynch was focused on seeing how many operations
per second one could get through. I then decided I was
interested in seeing also how much bandwidth we could handle
in the system. So we changed the record size from 8 bytes to
2000 Bytes. When trying it out with Gigabit Ethernet we reached
60,000 reads and 55.000 inserts/updates per second. A quick
calculation shows that we're doing almost 120 MBytes of reads
and 110 MBytes of writes to the data node. This is obviously
where the limit of Gigabit Ethernet goes so an easy catch of
the bottleneck.

Then we tried the same thing using the Dolphin DX cards. We got
250.000 reads per second and more than 200.000 writes per
second. This corresponds to almost 500 MBytes per second of
reads from the database and more than 400 MBytes of writes to
the data nodes.

I had to check whether this was actually the limit of the set-up
I had for the Dolphin cards (they can be set-up to use either
x4 or x8 on the PCI Express). Interestingly enough after working
in various ways with Dolphin cards for 15 years it's the first
time I really cared about the bandwidth it could chunk through.
The performance of MySQL Cluster have never been close to
saturating the Dolphin links in the past.

However today we managed to saturate the links. The maximum
bandwidth achievable by a microbenchmark with a single process
was 510 MBytes per second and we achieved almost 95% of this
number. Very impressive indeed I think. What's even more
interesting is that the Dolphin card used the x4 configuration
so it can actually do 2x the bandwidth in the x8 setting and
the CPU's were fairly lightly loaded on the system so it's
likely that we could come very close to saturating the load
even using a x8 configuration of the Dolphin cards. So that's
a milestone to me, that MySQL Cluster have managed to
saturate even the bandwidth of a cluster interconnect with
very decent bandwidth.

This actually imposes an interesting database recovery
solution problem into the MySQL Cluster architecture. How
does one handle 1 GBytes of writes to each data node in
the system when used with persistent tables which has
to be checkpointed and logged to disk. This requires
bandwidth to the disk subsystem in multiple GBytes per
second. It's only reasonable to even consider doing this
with the upcoming new high-performance SSD drives. I
heard an old colleague nowadays working for a disk
company mention that he had demonstrated 6 GBytes
per second to local disks, so this actually is a
very nice fit. Turns out that this problem can also be
solved.

Actually SSD drives is also a very nice fit with also
the disk data part of MySQL Cluster. Here it makes all
the sense in the world to use SSD drives as the place
to put the tablespaces for the disk part of MySQL
Cluster. This way also the disk data becomes part of
the real-time system and you can fairly easy build a
terabyte database with an exceedingly high
performance. Maybe this is to some extent a reply
Mark Callaghans request for a data warehouse based
on MySQL Cluster link. Not that we really focused so
much on it, but the parallelism and performance
available in a large MySQL Cluster based on 6.4 will
be breathtaking even to me with 15 years of thinking
into this behind me. A final word on this is that
we are actually also working on a parallel query
capability towards MySQL Cluster. This is going to
based on some new advanced additions to the storage
engine interface we're currently working on
(Pushdown Query Fragment for those that joined the
storage engine summit at Google in April this year).

A nice thing with being part of Sun is that they're
building the HW which is required to build these
very large systems and are very interested in doing
showcases for them. So all the technology to do
what has been discussed above is available within
Sun.

Sorry for writing a very long blog. I know it's
better to write short and to the point blogs,
however I found so many interesting tilts on the
subject.

Poll set to handle poll, eventports, epoll, kqueue and Windows IO Completion

2008-11-24T11:57:00.003+01:00

This blog describes background and implementation of a poll
set to monitor many sockets in one or several receive
threads. The blog is intended as a description but also to
enable some feedback on the design.

I've been spending some time working out all the gory details
of starting and stopping lots of send threads, connection
threads and receive threads over the last few months.

The receive threads will be monitoring a number of socket
connections and as soon as data is available ensure that
the data is received and forwarded to the proper user
thread for execution.

However listening on many sockets is a problem which needs
a scalable solution. Almost every operating system on the
planet has some solution to this problem. The problem is
that they all have different solutions. So I decided to
make a simple interface to those socket monitoring solutions.

First my requirements. I will handle a great number of socket
connections from each thread, I will have the ability to
move socket connections from receive thread to another thread
to dynamically adapt to the usage scenarios. However mostly
the receive thread will wait for events on a set of socket
connections, handle them as they arrive and go back waiting
for more events. On the operating system side I aim at
supporting Linux, OpenSolaris, Mac OS X, FreeBSD and Windows.

One receive thread might be required to listen to socket
connections from many clusters. So there is no real limit
to the number of sockets a receive thread can handle. I
decided however to put a compile time limit in there since
e.g. epoll requires this at create time. This is currently
set to 1024. So if more sockets are needed another receive
thread is needed even if not needed from a performance
point of view.

The implementation aims to cover 5 different implementations.

epoll
-----
epoll interface is a Linux-only interface which uses
epoll_create to create an epoll file descriptor, then
epoll_ctl is used to add/drop file descriptors to the epoll
set. Finally epoll_wait is used to wait on the events to
arrive. Socket connections remain in the epoll set as
long as they are not explicitly removed or closed.

poll
----
Poll is the standard which is there simply to make sure it
works also on older platforms that have none of the other
mechanisms supported. Here there is only one system call,
the poll-call and all the state of the poll set needs to
be taken care of by this implementation.

kqueue
------
kqueue achieves more or less the same thing as epoll, it does
so however with a more complex interface that can support a
lot more things such as polling for completed processes and
i-nodes and so forth. It has a kqueue-method to create the
kqueue file descriptor and then a kevent call which is used
both to add, drop and listen to events on the kqueue socket.
kqueue exists in BSD OS:s such as FreeBSD and Mac OS X.

eventports
----------
eventports is again a very similar implementation to epoll which
has the calls port_create to create the eventport file descriptor.
It has a port_associate call to add a socket to the eventport set.
It has a port_dissociate call to drop a socket from the set.
It has a port_getn call to wait on the events arriving. There is
however a major difference in that after an event arriving in a
port_getn call the socket is removed from the set and has to be
added back. From an implementation point of view this mainly
complicated my design of error handling.

Windows IO Completion
---------------------
I have only skimmed this yet and it differs mainly in being a tad
complex and also in that events from the set can be distributed to
more than one thread. However this feature will not be used in this
design, also I have currently not implemented this yet, I need to
get all the bits together on building on Windows done first.

Implementation
--------------
The implementation is done in C but I still wanted to have
a clear object-oriented interface. To achieve this I
created two header files ic_poll_set.h which declares all
the public parts and ic_poll_set_int.h which defines the
private and the public data structures used. This means that
the internals of the IC_POLL_SET-object is hidden from the
user of this interface.

Here is the public part of the interface (the code is GPL:ed
but it isn't released yet):

Copyright (C) 2008 iClaustron AB, All rights reserved
struct ic_poll_connection
{
int fd;
guint32 index;
void *user_obj;
int ret_code;
};
typedef struct ic_poll_connection IC_POLL_CONNECTION;

struct ic_poll_set;
typedef struct ic_poll_set IC_POLL_SET;
struct ic_poll_operations
{
/*
The poll set implementation isn't multi-thread safe. It's intended to be
used within one thread, the intention is that one can have several
poll sets, but only one per thread. Thus no mutexes are needed to
protect the poll set.

ic_poll_set_add_connection is used to add a socket connection to the
poll set, it requires only the file descriptor and a user object of
any kind. The poll set implementation will ensure that this file
descriptor is checked together with the other file descriptors in
the poll set independent of the implementation in the underlying OS.

ic_poll_set_remove_connection is used to remove the file descriptor
from the poll set.

ic_check_poll_set is the method that goes to check which socket
connections are ready to receive.

ic_get_next_connection is used in a loop where it is called until it
returns NULL after a ic_check_poll_set call, the output from
ic_get_next_connection is prepared already at the time of the
ic_check_poll_set call. ic_get_next_connection will return a
IC_POLL_CONNECTION object. It is possible that ic_check_poll_set
can return without error whereas the IC_POLL_CONNECTION can still
have an error in the ret_code in the object. So it is important to
both check this return code as well as the return code from the
call to ic_check_poll_set (this is due to the implementation using
eventports on Solaris).

ic_free_poll_set is used to free the poll set, it will also if the
implementation so requires close any file descriptor of the poll
set.

ic_is_poll_set_full can be used to check if there is room for more
socket connections in the poll set. The poll set has a limited size
(currently set to 1024) set by a compile time parameter.
*/
int (*ic_poll_set_add_connection) (IC_POLL_SET *poll_set,
int fd,
void *user_obj);
int (*ic_poll_set_remove_connection) (IC_POLL_SET *poll_set,
int fd);
int (*ic_check_poll_set) (IC_POLL_SET *poll_set,
int ms_time);
const IC_POLL_CONNECTION*
(*ic_get_next_connection) (IC_POLL_SET *poll_set);
void (*ic_free_poll_set) (IC_POLL_SET *poll_set);
gboolean (*ic_is_poll_set_full) (IC_POLL_SET *poll_set);
};
typedef struct ic_poll_operations IC_POLL_OPERATIONS;

/* Creates a new poll set */
IC_POLL_SET* ic_create_poll_set();

struct ic_poll_set
{
IC_POLL_OPERATIONS poll_ops;
};

DTrace, opensolaris and MySQL Performance

2008-11-21T02:31:00.002+01:00

Currently I'm working hard to find and remove scalability
bottlenecks in the MySQL Server. MySQL was acquired by Sun
10 months ago by now. Many people have in blogs wondered what
the impact has been from this acquisition. My personal
experience is that I now have a chance to work with Sun
experts in DBMS performance. As usual it takes time when
working on new challenges before the flow of inspiration
starts flowing. However I've seen this flow of inspiration
starting to come now, so the fruit of our joint work is
starting to bear fruit. I now have a much better understanding
of MySQL Server performance than I used to have. I know fairly
well where the bottlenecks are and I've started looking
into how they can be resolved.

Another interesting thing with Sun is the innovations they have
done in a number of areas. One such area is DTrace. This is a
really interesting tool which I already used to analyse some
behaviour of MySQL Cluster internals with some success. However
to analyse other storage engines inside MySQL requires a bit more
work on inserting DTrace probes at appropriate places.

To work with DTrace obviously means that you need to work with
an OS that supports DTrace. Solaris is such a one, I actually
developed NDB Cluster (the storage engine for MySQL Cluster) on
Solaris the first 5-6 years. So one would expect Solaris to be
familiar to me, but working with Linux mainly for 6-7 years means
that most of the Solaris memory is gone.

So how go about developing on Solaris. I decided to install a virtual
machine on my desktop. As a well-behaved Sun citizen I decided to
opt for VirtualBox in my choice of VM. This was an interesting
challenge, very similar to my previous experiences on installing
a virtual machine. It's easy to get the VM up and running, but how
do you communicate with it. I found some instructions on how to
set-up IP links to a virtual machine but to make life harder I
have a fixed IP address on my desktop so this complicated life
quite a bit. Finally I learned a lot about how to set-up virtual
IP links which I already have managed to forget about :)

The next step is to get going on having a development environment
for opensolaris. I soon discovered that there was a package
manager in opensolaris which could be used to get all the needed
packages. However after downloading a number of packages I
stumbled into some serious issues. I learned from this experience
that usage of Developer Previews for OS's is even worse than newly
released OS's which I already know by experience isn't for the
fainthearted.

So I decided to install a released opensolaris version instead
(the OpenSolaris2008.05 version). After some googling I discovered
a very helpful presentation at opensolaris developer how-to
which explained a lot about how to install a development
environment for opensolaris.

After installing opensolaris 2008.05, after following the
instructions on how to install a development environment
I am now equipped to develop DTrace probes and scripts and
try them out on my desktop.

I definitely like the idea that opensolaris is looking more
like yet another Linux distribution since it makes it a
lot simpler to work with it. I would prefer GNU developer
tools to be there from scratch but I have the same issue
with Ubuntu.

That the system calls are different don't bother me as a
programmer since different API's to similar things is
something every programmer encounters if he's developing
for a multi-platform environment. I even look forward to
trying out a lot of Solaris system calls since there are
lots of cool features on locking to CPU's, controlling
CPU's for interrupts, resource groups, scheduling
algorithms and so forth. I recently noted that most of
these things are available on Linux as well. However
I am still missing the programming API's to these
features.

CACHE INDEX per partition for MyISAM

2008-10-30T19:45:00.004+01:00

The newest development in the partitioning code
is WL#4571.

This new feature makes it possible to tie a
partition using MyISAM to a specific cache index.
The syntax for how to do is available in the
above worklog entry.

We found this feature to be useful for enabling
higher performance of parallel ALTER TABLE
(WL#2550). When adding
a primary key to a MyISAM table the key cache in
MyISAM limited scalability of Parallel ALTER TABLE
severely, so adding several key caches, essentially
one per partition we can ensure that the ALTER TABLE
can be fully parallelised (all other ALTER TABLE
on MyISAM already scales perfectly).

We also have some ideas on how to solve the base
problem in how to make the key cache more scalable
by dividing the mutex on the key cache into one
mutex per a range of key cache pages.

New launchpad tree for PARTITION BY RANGE COLUMN_LIST(a,b)

2008-10-30T19:26:00.003+01:00

A colleague of mine at Sun/MySQL showed me how to get
statistics from my blog. This was an interesting read
of all statistics. I noted that there was a great
interest in partitioning related information and that
the new partitioning feature mentioned in my blog
2 years ago still attracts a lot of attention.

So I thought it was a good idea to blog a bit more
about what's going on in the partitioning
development. I decided to check out how easy it is
to externalize my development trees on launchpad.
It turned out to be really easy so I simply
put up the development tree for the new partitioning
feature which I described in my last blog.

Launchpad tree

I also externalized the Worklog entry for this
development, unfortunately not a very long
description but I'll try to work on that.
There is a new test case in the mysql-test/t
directory called partition_column.test which
shows how to use these new features (it might
take some time before this link works).

Worklog description

Further development on new partitioning feature

2008-10-07T19:17:00.005+02:00

As mentioned in a blog 2 years ago I worked on a new
partitioning feature:

I've been busy with many other things but now I've taken this
work a step forward and will most likely set-up a preview tree
of this feature in a short time.

The new feature adds the possibility to perform partitioning
on any type of column also for range and list partitioning
(has been possible for KEY partitioning all the time). It also
adds a new function to the MySQL Server and this function is
also a monotonic function which means it gets a nice treatment
of the partition pruning. This new function is TO_SECONDS which
works very similarly to TO_DAYS.

So here are couple of new cases of what one will be able to do:

create table t1 (d date)
partition by range column_list(d)
( partition p0 values less than (column_list('1999-01-01')),
partition p1 values less than (column_list('2000-01-01')));

create table t1 (a date)
partition by range(to_seconds(a))
(partition p0 values less than (to_seconds('2004-01-01')),
partition p1 values less than (to_seconds('2005-01-01')));

select * from t1 where a <= '2003-12-31';

This select will be discovered to only find values in p0 by
the partition pruning optimisation step.

create table t1 (a int, b int)
partition by range column_list(a,b)
(partition p2 values less than (column_list(99,99)),
partition p1 values less than (column_list(99,999)));

insert into t1 values (99,998);
select * from t1 where a = 99 and b = 998;

This select statement will discover that it can only
be any records in the p1 partition and avoid
scanning the p0 partition. Thus partitioning works
in very much the same manner as a first step index.

dbt2-0.37.37 uploaded and various other stuff

2008-10-02T23:36:00.003+02:00

There was a small bug in the dbt2-0.37.36 version I uploaded which
I have now fixed in the new dbt2-0.37.37 version.

There has also been some interesting benchmark tests done where
we have run DBT2 on a T5220 box (Niagara II chips). We can show
the scalable performance benefits here as well. We've been able
to run with 20 data nodes on 1 box (these boxes can run up to
64 threads at a time) with scalable performance increase from
4 nodes.

We had a developer meeting a few weeks ago and there were lots of
activities. Personally I had most fun seeing the demo of
Parallel ALTER TABLE. We loaded a table with 10 million 70-80 byte
rows. We had access to a machine with 64 GB of memory and
16 cores. It was very interesting to run one SQL command and
see the load in top of mysqld go to 1600%. Altering a 10 million
row table in 2.5 seconds I thought was pretty good.

Another cool demo was to see the online add node in MySQL Cluster.
This is an interesting feature which I started thinking about
in 1999, had a first design then, changed to a second variant
in 2001 and changed again around 2005 and the final version that
was implemented was the fourth version of the design. The nice
thing is that the fourth version actually contains some nice
innovations that neither of the earlier designs had. So cooking
an idea for a long time can be really beneficial some times.
For a very brief description of this work see Jonas Oreland's
blog.

Jonas and Pekka is also working on another cool optimisation
of MySQL Cluster where the data node will become multithreaded.
There will be up to 6 threads in the first released version of
this. Jonas measured in a test today that one could do 370.000
inserts per second on 1 8-core box with this feature (and this
is still a fairly unstable version where there are still some
performance issues remaining). We're getting close to measuring
computer speed in MDO (MegaDatabaseOperations per second)
instead of in MHz.

Jonas and myself is also working on removing from MySQL Cluster
"the single transporter mutex" which will improve the scalability
of MySQL Servers using MySQL Cluster. We're working on this in
parallel using the same basic design but with small variations
on the details. Will be interesting to see which variant that
works best.

Finally Frazer has optimised the handling of large records in
the data node to the extent that inserts of 5k records gets
twice the speed. The interesting thing is that the benchmark
for this hits the limit of Gigabit Ethernet already with 1
CPU working at 80% which is quite interesting.

So as you can see there is a lot of interesting things cooking
at MySQL and then I haven't even mentioned the work we're
doing together with other Sun folks on optimising MySQL. More
on that later.

Linear Scalability of MySQL Cluster using DBT2

2008-09-09T00:38:00.003+02:00

To achieve linear scalability of MySQL Cluster using the DBT2
benchmark has been a goal of mine for a long time now. Last
week I finally found the last issue that limited the scalability.
As usual when you discovered the issue it was trivial (in this
case it was fixed by inserting 3 0's in the NDB handler code).

We can now achieve ~41k TPM on a 2-node cluster, ~81k on a
4-node cluster and ~159k TPM on a 8-node cluster giving roughly
97% improved performance by doubling number of nodes. So there
is nothing limiting us now from achieving all the way up to
1M TPM except lack of hardware :)

I've learned a lot about what affects scalability and what
affects performance of MySQL Cluster by performing those
experiments and I'll continue writing up those experiences on
my blog here. I have also uploaded a new DBT2 version where I
added a lot of new features to the DBT2, improved performance
of the benchmark itself and also ensured that running with many
parallel DBT2 drivers do still provide correct results when
adding the results together. It can be downloaded from
www.iclaustron.com

Some food for thoughts: How to make use of new SSD devices

2008-08-21T11:55:00.002+02:00

The hardware guys are presenting new storage devices called
SSD's based on flash memory. At the moment I think they are
about 3-4 times cheaper than DRAM memory and the gap seems
to be increasing. They're still far from the price of hard
drives but also here the gap seems to be closing.

So as I'm now an employee of Sun that actually puts together
systems with this type of HW in it I get questioned what I
as a DBMS developer can do with those devices.

First some comments on performance. These new devices will be
able to perform reads and writes of a few kilobytes large pages
in about 25-100 microseconds compared to hard drives which
takes about 3-10 milliseconds for the same thing.

An obvious use is obviously to use them to speed up database
logging, particularly in commit situations. However this
doesn't really require any significant changes to the SW
already out there. So I won't spend any more time on this use.

Another use is for MySQL Cluster. MySQL Cluster stores most data
in memory and can store non-indexed data on disk. So how can
SSD devices be used to improve this.

First some facts about performance of MySQL Cluster. In the data
node where the data actually resides it takes about 10
microseconds of processing time to perform a key lookup and a
scan has about 20 microseconds of start-up costs whereafter each
record takes 1-2 microseconds to fetch.

So now for the idea. Let's assume we'll use an SSD device as swap
memory. We would then purposely set the swap to be e.g. 10x
larger than the memory. For this to work we need to be able to
allocate memory from different swap pools, memory used for
transaction state and things like this we don't want swapped out
(working for Sun has an advantage since we can work with the OS
guys directly, but naturally I hope Linux developers also take the
same opportunity).

So during a key lookup we need to get one page from the hash index
and one page with the record in it. Guestimating a 90% hit rate in
the hash index and 80% hit rate on the data page we find that we
will about 0.3 swap misses per key lookup. If we assume 50
microseconds for this it means that mean key lookup will increase
from 10 microseconds to 25 microseconds. This should be
acceptable, given that we can increase data size by a factor of
about 10.

A similar analysis can be made for scans as well, but I'm lazy so
will leave it to you to perform :)

So given todays sizes of memories and SSD's it should be possible
to use systems with 64 GBytes of memory and 640 GB of SSD memory
and clustering 8 of those with replication gives us a main memory
based system for a reasonable price providing 2.5 TByte of user
data in a highly available system with high degrees of parallelism
in the system.

New partitioning features

2008-08-21T11:35:00.002+02:00

As burtonator pointed out parallelism is an important
feature that partitioning makes possible. So I thought
it might be a good idea to mention a little bit what
we're doing in the area of partitioning.

It's quite correct that parallelism is one of the main
advantages of partitioning (not the only one though since
also partition pruning and dividing large indexes and
being able to add and drop partitions efficiently are
important as well). In 5.1 we focused on the maintenance
features of partitioning but the intention to move on
to parallelisation was more or less the main goal from
the very start.

This is why it's such extra fun to actually get going on
this when one has worked on the foundation for this work
for almost 4 years (partitioning development started out
2004 H2 and most of the partitioning code in 5.1 was ready
about two years later).

There are also ideas to introduce parallelism for scans of
large partitioned tables and also a few more maintenance
features that are still missing.

Another feature in the works for partitioning is the
ability to use partition pruning on several fields. This
will be possible for PARTITION BY RANGE and LIST. The
syntax will look something like this:

CREATE TABLE t1 (a varchar(20), b int)
PARTITION BY RANGE (COLUMN_LIST(a,b))
(PARTITION p0 VALUES LESS THAN (COLUMN_LIST("a", 1)),
PARTITION p1 VALUES LESS THAN
(COLUMN_LIST(MAXVALUE, 4)));

In this case it is possible to partition on any field type
and it is also possible to do partition pruning on multiple
fields in much the same way as it is for indexes.

E.g.
select * from t1 where a = "a";
select * from t1 where a = "a" and b = 2;

will both be able to use for partition pruning with the
second obviously able to do more pruning then the first one.

Multi-threaded ALTER TABLE

2008-08-20T23:00:00.003+02:00

Today I achieved something which is a first in the MySQL
server as far as I'm aware of. I managed to run a query
with multiple threads. The query was:
ALTER TABLE t1 ADD COLUMN b int;
and the table had 4 partitions in it. So it used 4 threads
that each thread handled the copying of data from old
table to new table of one partition.

Currently it's designed for use by partitioned tables but
it should be very straightforward to do minor parallelisation
also of non-partitioned tables by e.g. breaking up in a scan
thread and a write thread.

It's nice to get started on this track and see how one can
make use of modern computers with a great deal of CPU power
if one can parallelise the applications. As an example a
dual socket box T5220 (2 Niagara II CPU's) can handle 128
threads in parallel.

3: Thoughts on a new NDB API: Adaptive send algorithm

2008-08-01T20:40:00.003+02:00

I thought a bit more on the adaptive send algorithm and kind of like
the following approach:

Keep track of how many sends we are at maximum allowed to wait
until we send in any ways. This is the state of the adaptive send
algorithm which is adapted through the following use of statistics
(we call this state variable max_waits):

For each send we calculate how long time has passed since the
send that was sent max_waits sends ago. We also do the same for
max_waits + 1. At certain intervals (e.g. every 10 milliseconds) we
calculate the mean wait that a send would have to do, if this lies
within half the desired maximum wait then we accept the current
state, if also the mean value using max_waits + 1 is acceptable
then we increase the state by one. If the state isn't acceptable
we decrease it by one.

In the actual decision making we will always send as soon as we
notify that more than the maximum wait time has occurred so this
means that the above algorithm is conservative. However the user
should have the ability to control how long he accepts a wait
through a configuration variable, thus increasing or decreasing
send buffering at the expense of extra delays.

This algorithm is applied on each socket and the actual decision
making is done within the critical section and also the statistics
calculation and from coding this it seems like the overhead should
be manageable.

1: Making MySQL Cluster scale perfectly in the DBT2 benchmark: Initial discussion

2008-07-31T20:27:00.003+02:00

Since 2006 H1 I've been working on benchmarking MySQL
Cluster using the DBT2 test suite. Initially this meant
a fair amount of work on the test suite itself and also
a set of scripts to start and stop NDB data nodes, MySQL
Servers and all the other processes of the DBT2 test.
(These scripts and the DBT2 tests I'm using is available
for download at www.iclaustron.com)

Initially I worked with an early version of MySQL Cluster
based on version 5.1 and this meant that I hit a number
of the performance bugs that had appeared there in the
development process. Nowadays the stability is really good
so in the most case I've spent my time focusing on what
is required to use in the operating system and the
benchmark application for optimum scalability.

Early on I discovered some basic features that were required
to get optimum performance of MySQL Cluster in those cases.
One of them is to simply use partitioning properly. In the
case of DBT2 most tables (everyone except the ITEM table) can
be partitioned on the Warehouse id. So the new feature I
developed as part of 5.1 came in handy here. It's possible to
use both PARTITION BY KEY (warehouse_id) or PARTITION BY
HASH (warehouse_id). Personally I prefer PARTITION BY HASH
since it spreads the warehouses perfectly amongst the data
nodes. However in 5.1 this isn't a fully supported so one has
to start the MySQL Server using the flag --new to use this
feature with MySQL Cluster.

The second one was the ability to use the transaction
coordinator on the same node as the warehouse the
transaction is handling. This was handled by a new
feature introducted in MySQL Cluster Carrier Grade
Edition 6.3 whereby the transaction coordinator is
started on the node where the first query is targeted.
This works perfectly for DBT2 and for many other
applications and it's fairly easy to change your
application if it doesn't fit immediately.

The next feature was to ensure that sending uses as
big buffers as possible and also to avoid wake-up
costs. Both those features meant changes to the
scheduler in the data nodes of the MySQL Cluster.
These changes works very well in most cases where
there is sufficient CPU resources for the data nodes.
This feature was also introduced in MySQL Cluster CGE
version 6.3.

Another feature which is very important to achieve
optimum scalability is to ensure that the MySQL Server
starts scans only on the data nodes where it will
actually find the data. This is done through the use
of partition pruning as introduced in MySQL version
5.1. Unfortunately there was a late bug introduced
which I recently discovered which gave decreased
scalability for DBT2 (this is bug#37934 which contains
a patch which fixes the bug, it hasn't been pushed yet
to any 6.3 version).

With these features there were still a number of scalability
issues remaining in DBT2. One was the obvious one that the
ITEM table is spread on all data nodes and thus reads of the
ITEM table will use network sockets that isn't so "hot".
There are two solutions to this, one is that MySQL Cluster
implements some tables as fully replicated on all data nodes.
This might arrive some time in the future, the other variant
uses standard MySQL techniques. One places the table in
another storage engine, e.g. InnoDB, and uses replication to
spread the updates to all the MySQL Servers in the cluster.
This technique should be a technique that can be applied to
many web applications where there are tables that need to be
in MySQL Cluster to handle availability issues and that the
data is required to be updated through proper transactions, but
there are also other tables which can be updated in a lazy
manner.

Finally there is one more remaining issue and this is when the
MySQL Server doesn't work on partitioned data. That is in the
case of DBT2 if all MySQL Servers can access data in a certain
node group then the data nodes will have more network sockets to
work with which will increase cost of networking. This limits
scalability as well.

In the case of DBT2 this can be avoided by using a spread
parameter that ensures that a certain MySQL Server only uses a
certain node group in the MySQL Cluster. In a generic application
this would be handled by an intelligent load balancer that
ensures that MySQL Servers works on different partitions of
the data in the application.

What I will present in future blogs is some data on how much the
effects mentioned above have on the scalability of the DBT2
benchmark for MySQL Cluster.

What is more surprising is that there is also a number of other
issues related to the use of the operating system which aren't
obvious at all. I will present those as well and what those mean
in terms of scalability for MySQL Cluster using DBT2.

Finally in a real application there will seldom be a perfect
scalability occuring, so in any real application it's also
important to minimize the impact of scalability issues. The
main technology to use here is cluster interconnects and I
will show how the use of cluster interconnects affects
scalability issues in MySQL Cluster.

Note numbers from these DBT2 are merely used to be used here to
compare different configurations of MySQL Cluster.

2: Thoughts on a new NDB API: Send part

2008-07-30T17:02:00.003+02:00

In the current API when sending one takes the Transporter mutex and
then sends all the signals generated towards one or many nodes.
There is also some handling of adaptive sends, however this adaptive
algorithm takes care of all nodes, thus waiting for sending is global
on all nodes.

The new design uses one mutex for the sending, however this mutex only
controls the sending part of one socket. Also the time for holding the
mutex is just enough to check the state, no send operations are done
while holding the mutex.

The new adaptive algorithm will keep track of the last sent messages on
this socket and in principle the idea is that if it's at least a 90-99%
probability that it is a good idea to wait, then it will wait (unless
the application has provided the force send flag). It will do so by
keeping track of the last few messages sent.

So in principle the data structure protected by the mutex is:
struct ic_send_node_mutex
{
IC_SEND_THREAD_MUTEX *send_thread_mutex;
Mutex mutex;
boolean send_active;
IC_COMM_BUFFER *first_cb;
IC_COMM_BUFFER *last_cb;
uint32 queued_bytes;
Timer first_buffered_timer;
Timer last_sent_timers[8];
uint32 last_sent_timer_index;
}

For each socket there is a specific send thread, this thread is mostly
sleeping, waiting for someone to wake it up from its sleep. One reason
to wake it up is if one thread has started sending and other threads
have provided so much work that it needs to offload this sending to
a specific thread (the idea is that the sending is normally done by
an application thread which is involved in user activity and we cannot
keep this thread for longer than a few sends, thus we need to make it
possible to offload send activity to a specific send thread when a high
load appears. The send thread could also be awakened to send buffered
messages that has timed out.

The flag send_active is true whenever a thread is actively sending,
and thus a thread that needs to send when this flag is set can
simply return immediately, if it's not true then it can set the flag
and start sending.

It would probably be possible to handle this without a mutex, but the
contention on this mutex should be small enough and also there is some
wakeup logic that makes sense for a mutex.

The application thread can prepare the NDB Protocol messages completely
before acquiring the mutex, the only activity which sometimes happens
inside the mutex is reading the time for handling of the adaptive
algorithm.

Sends normally goes to a NDB Data node but could also go to another
Client node and could even go to another thread in the same process.
This is important to handle parallelisation, thus to parallelise it
is sufficient to send a number of messages to other nodes and/or
threads. Each message can kick of at least one new thread.

1. Thoughts on a new NDB API, Baseline thoughts

2008-07-29T20:45:00.004+02:00

I spent some time during my vacation thinking about some
new ideas. I designed the first version of the NDB API
about 10 years ago and obviously in those days the maximum
number of CPU's in most systems was 2 so it wasn't a big
problem having a single mutex protecting send and receive
in the NDB API (The NDB API is the low level API used by the
storage engine NDB which is the storage engine in MySQL
Cluster).

Another design criteria I made when designing the NDB API
was that most developers want to use a synchronous API.
Thus the asynchronous API was made afterwards and didn't
cover all operations. Most developers still develop using
synchronous API's, however most of the use for the NDB
API is for specialised applications such as Telco servers,
storage engine code, LDAP servers. Also I'm thinking in
even using it inside an operating system kernel to design
a clustered file system.

Thus today it seems like a better idea to use an asynchronous
API as the base and then put the synchronous API on top of
this.

When designing the original NDB API it was sufficient to think
of simple key lookups, later it was advanced with also handling
scans of tables and indexes. However current design problems
are related to parallelising SQL queries and also there are
implementations of things such as BLOB's that actually require
multiple sequential and parallel operations. Thus in the new
design it's necessary to consider the possibility of starting
complex operations involving multiple threads (sometimes even
multiple processes), multiple operations in sequence and in
parallel.

These ideas will be fed into the existing NDB API. It will also
be used in the iClaustron project where I aim to build something
that can be used as a clustered file system. iClaustron is both
designed with the aim to at some point in time be a useful thing,
but at the same time I use it as my personal playground where I
can test new ideas and see how my ideas turns out when turned
into source code.

The original NDB API was designed in C++ as all the rest of the
MySQL Cluster code. Within the data nodes I think we've found
a good compromise of what to use in the C++ language and what
not to use. However in general I found that debates around what
should be used in C++ tends to take an improportionate amount of
time compared to the value of those discussions. So for that
reason I decided to use C as the language of choice for iClaustron.
Actually there was more reasons for this, it makes it a lot easier
to use the code inside an operating system kernel such as Linux
or FreeBSD and second it makes it easier to write layers to other
languages such as Python, Perl,...

Most of the thoughts on this new NDB API has been in my mind for
more than 2 years (actually some of the thoughts have already been
implemented in NDB already), however during my vacation I had
some fun in designing out all the details I hadn't considered
previously.

It's my view of a nice vacation to relax on a beach or walking in the
mountains while inventing some new ideas based on an interesting
problems. I cheated by solving a Sudoku as well this vacation but in
general I like mind games that are related to what I do for a living.
Inventing the idea is the fun part of innovation, then comes the
hard part of actually doling out all the details, writing code and
testing code and selling the ideas. This is the work part.

I will follow this posting with a high level view on the ideas as far
they've been developed so far. In parallel I'll also "dump" the ideas
into code format. I like to think of my coding as a "brain dump", I
have a fairly unusual way of writing code. I think about the problem
for a long time and when I'm satisfied I write the code for it. I then
write all the code with only a very minimal set of compilation and
test cases. The main idea of coding in this phase is still design, so
in principal I write a design in the form of code. This also means
that I try to write as much comments as possible since I know that I
will otherwise forget my ideas. Working for MySQL has made me much
more aware of software engineering issues as well, so today I do also
a bit of thinking on software engineering as well in the design.

An architecture for the design is obviously very important, and the
architecture has borrowed heavily from the way the Linux kernel is
designed with lots of interfaces similar to the VFS interface in
Linux using a struct of a set of function pointers.

DTrace probes in MySQL and MySQL Cluster

2008-06-06T16:30:00.002+02:00

I've worked on DTrace probes for a while now. It's
a really interesting tool. I've worked on MySQL Cluster
code since 1996 but this is the most advanced tool
I've used to see exactly what's going on inside the
MySQL Server and the data nodes.

I'm still at an early stage of using these DTrace probes
and there is still some work before they are publishable
but one can see very well what's going on inside the
processes in real-time.

My first finding was that I quickly discovered that CPU
percentage that is reported at 1% in prstat in Solaris
actually means that it uses 64% of a CPU thread 1% is the
percentage of the total CPU resources, this is different
to what I'm used to from top.

The benchmark I'm analysing is the same DBT2 I've used in
a fairly long line of analysis on MySQL Cluster performance
over the last 2 years. This benchmark can be downloaded
from www.iclaustron.com/downloads.html. It's a DBT2 based
on version 0.37 with lots of additions to make it work with
running multiple MySQL Server instances as is the case
with MySQL Cluster. Currently I'm running this on Solaris so
there will soon be a new release with some fixes needed to
run this benchmark on Solaris.

Visited Hadoop Conference

2008-03-26T03:52:00.004+01:00

NOTE: Any comments in this blog entry is based on my personal thoughts after visiting the Hadoop conference and doesn't represent any current plans within MySQL.

I visited the Hadoop conference today which was a very interesting event. The room was filled to its limit, people were even standing up in lack of chairs. Probably around 300 people or so.

It was interesting to see the wide scope of web-scale problems that could be attacked using Hadoop. The major disruptive feature in Hadoop is the MapReduce solution to solving parallel data analysis problems.

One piece that I started thinking of was how one could introduce the MapReduce into SQL. One presentation of HIVE showed an interesting approach of how to solve this problem. I thought a bit on how one could integrate a MapReduce solution in MySQL and there are certainly a lot of problems to solve but I got a few interesting ideas.

The concept of being able to query both business data stored in a database and web-based logs and other type of massive amounts of data is certainly an interesting problem to consider.

In principle what one can add by introducing MapReduce into MySQL is the ability to handle streaming queries (queries that use dataflows as input table(s) and dataflows as output table).

However the actual implementation of Hadoop and HBase still were very much in their infancies so availability and reliability were far away from always on and also performance wasn't yet a focus.

MySQL Architecture Workshop

2008-03-25T03:38:00.006+01:00

We had a workshop in Stockholm in early March to discuss what can be done to innovate MySQL in a number of areas. Most of the work here will not be useful code in yet a year or two and a lot longer before it'll be used in Enterprise Ready binaries. Obviously there is no guarantee that this early work will reach production binaries. This work is part of an aim at advancing the MySQL Architecture in the next few years.

One interesting topic we discussed was Pushdown of Query Fragments to Storage Engine.

A Query Fragment is a piece of an SQL query, for example in a 3-way join any join of 2 tables in this query is a Query Fragment, also the full query is a Query Fragment. As part of this interface the storage engine can decide to perform its own optimising using a new interface or it could rely on the MySQL Server to handle this optimisation. If the Storage Engine decides it can handle the Query Fragment and the optimiser decides to use this Query Fragment then the execution of this Query Fragment will be executed using the traditional Storage Engine API as if the Query Fragment was a normal table.

There are many engines that could make use of this new interface. Another interesting use of this interface is to implement parallel query support for the MySQL Server using this new interface. We hope to build a prototype of this sometime this year.

Please provide comments on this development on this blog, the development is in such an early phase that input is very welcome.

Visiting Internal Sun Technology Conference

2008-03-25T03:05:00.004+01:00

My first real chance to meet up with my new colleagues at Sun was an internal technology conference at Sun. It was interesting to listen to what's cooking within Sun.

We got a presentation of Data Centers and their impact on the environment and it immediately triggered me to start thinking of how we can interact with power save functions from the MySQL Code. It was also interesting to see slides on how computer architecture is developing, this can be put into thinking about how the MySQL architecture should progress over the next few years.

Visiting Family History Technology Workshop at BYU

2008-03-25T02:48:00.002+01:00

On the 13th of March I attended an interesting workshop on techhnology for Genealogy. My interest in this is based on interest in genealogy itself (my family tree contains currently about 3000 persons from various parts of Sweden down to some farmers in northern Sweden born around 1400) and my interest in technology and in particular how MySQL and MySQL Cluster can be used for genealogy applications. Being an LDS myself also adds to my interest in the subject.

The LDS church has developed a Web API FamilySearchAPI where genealogists through their genealogy software can work on a common database where they can add, edit information about our ancestors. The system handling this system currently contains 2.2 PB of data and is going to grow significantly as images and more genealogy information is added.

There were quite a few interesting discussions on how to link information between the source information (scanned images of historical documents), transscribed information from sources and derived family trees. The most complex problem in this application is the fuzziness of the base data and that different genealogists can have many different opinion about how to interpret the fuzzy base data. Thus in order to solve the problem one has to handle quality of genealogists somehow in the model.

From a database point of view this application requires a huge system with large clusters of information, it contains one part which is the base data (the scanned images) and this is typically stored in a large clustered file system containing many petabytes of data. Then the derived data is smaller but given that all versions need to be stored will still be a really huge data set and this is a fairly traditional relational database with large amounts of relations between data.

So what I take home from the workshop is ideas on what MySQL and MySQL Cluster should support in 3-5 years from now to be able to work in applications like this one.

Speeding up MySQL by 36% on the T2000

2008-03-25T02:29:00.006+01:00

This post will focus on the performance tuning work that we've been working on since December 2007 on the Sun T2000 server. We got a nice speedup of 36% with fairly small efforts and we've got good hope we can improve performance a great deal more. This effort is part of a new effort at MySQL to improve performance both on Solaris and Linux platforms and to some extent Windows as well. This report focuses on T2000 using Solaris.

T1000 and T2000 are the first CoolThreads servers from Sun with the UltraSPARC T1 processors. The T1 is very energy efficient, which is extremely important to modern datacenters. On the other hand, leveraging the massive amount of thread-level parallelism (32 concurrent threads) provided by the CoolThreads servers is the key to getting good performance. As the CoolThreads servers are used by many Sun customers to run web facing workloads, making sure that MySQL runs well on this platform is important to Sun and MySQL customers, and also to the success of the CoolThreads servers and MySQL.

Note: This work was started long before it was known that MySQL was to be acquired by Sun Microsystems. The actual work done for this tuning was done by Rayson in the performance team at MySQL.

The workload that we used was sysbench, which is a very simple benchmark. In particular, we only ran read-only OLTP sysbench to perform this tuning work. The reason behind this is that if MySQL does not scale well with a simple read-only OLTP workload, then it would not scale well with more complex workloads, yet using a more complex workload would need more time to setup and run.

This is a list of things that we tried.

1) Hardware setup and software versions used
============================================
The compiler version:
> cc -V
cc: Sun C 5.9 SunOS_sparc Build47_dlight 2007/05/22
usage: cc [ options] files. Use 'cc -flags' for details

Solaris version:
> cat /etc/release
Solaris 10 11/06 s10s_u3wos_10 SPARC
Copyright 2006 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 14 November 2006

For each run, 5 results were collected, and we discarded the best and the worst results, and then averaged the remaining 3, and sysbench was invoked as follow:
> ./sysbench --test=oltp --num-threads=32 --max-time=60 --max-requests=0 --oltp-read-only=on run

Using default configuration of MySQL 5.0.45 and read-only OLTP sysbench 0.4.8 on a Sun T2000 running at 1GHz, the throughput measured was 1209 transactions per second.

2) Compiling with -fast
=======================
Since the workload is CPU intensive with very few I/O operations, we knew that compiler optimizations would be very beneficial to performance. As Sun used the -fast flag for compiling other CPU intensive benchmarks (e.g. SPEC CPU), using -fast was the first thing we tried; this was done by setting CFLAGS and CXXFLAGS to -fast before we ran the configure script.

The throughput measured was 1241 transactions per second, or an improvement of 2.6%.

3) Fixing headers for Sun Studio
================================
As using a higher optimization level gave us a small but nice improvement, we then looked for other opportunities from compiler optimizations. The first thing we noticed was that there were compiler directives that were not recognized by Sun Studio. And inlining was disabled as well.

As the Sun Studio compiler supports inlining, we enabled it in InnoDB by modifying the header file: univ.i

The throughput went up 3.1% to 1279 transactions per second.

We also enabled prefetching by using "sparc_prefetch_read_many()" and "sparc_prefetch_write_many()". In fact there was a small performance degradation, the throughput decreased by -0.47% to 1273 transactions per second. Since we do enable prefetching on Linux when gcc is used as the build compiler, we believe that the Niagara has enough MLP (Memory Level Parallelism), which does not need a lot of help from prefetching. However, we will see if this could benefit other SPARC servers (UltraSPARC IV+ and SPARC64 come in mind), or x64 servers running Solaris (when Sun Studio is used as the build compiler).

4) Locks in MySQL
=================
We then use plockstat to locate contented mutex locks in the system. Surprising, memory management in libc was accounted for a lot of the lock contentions. Since the default malloc/free is not
optimized for threaded applications, we switched to mtmalloc. mtmalloc could be used without recompiling or relinking. We simply set the LD_PRELOAD environment variable in the shell that was used to start the MySQL server to interpose malloc/free calls.

> setenv LD_PRELOAD /usr/lib/libmtmalloc.so

The gain was 8.1% to 1376 transactions per second.

5) Caching Memory Inside MySQL
==============================
After we switched to mtmalloc, we still found that there were memory allocation and free patterns that were not efficient. We modified the code so that memory is cached inside MySQL instead of repeatedly allocated and freed. The idea is that we could trade memory usage for performance, but since most memory implementations cache memory when freed by the application instead of returning back to the operating system, with MySQL caching the memory would not only speed up the code, but also would not have impact on memory usage.

Using DTrace, we found that there were over 20 places where malloc and free were called repeatedly. We picked one of the hot spots and modified the code.

The change above gave us 1.5% to 1396 transactions per second.

6) Using Largepages
===================
Using largepages on the UltraSPARC T1 platform can be beneficial to performance, as the TLBs in the T1 processor are shared by the 32 hardware threads.

We use the environment variable MPSSHEAP to tell the operating system that we wanted to use largepages for the memory heap:

> setenv LD_PRELOAD mpss.so.1
> setenv MPSSHEAP 4M

This change gave us a gain of 4.2% in throughput to 1455 transactions per second.

7) Removing strdup() calls
==========================
Later on, we also found that there was an unnecessary strdup/free pattern in the code in mf_cache.c. Since the character string was not modified in the code, we removed the strdup call and simply passed the pointer to the string instead.

This change gave us a gain of 0.34% to 1460 transactions per second.

8) Feedback Profile and Link Time Optimizations
===============================================
We then compiled the MySQL server with feedback profile compiler optimization and link time optimization. We also trained MySQL in a training run, and then we recompile so that the compiler
could use the information (execution behavior) collected during the training run. The compiler flags used: -xipo -xprofile, -xlinkopt -fast

The combination of the compiler flags gave us a gain of 10.5% to 1614 transactions per second.

9) Configuration File Tuning
============================
While tuning values in the configuration file is the most common way to get higher performance for MySQL, we did not spend a lot of time on it, however. The reason is that we were more interested in finding the bottlenecks in the code. Nevertheless, we did use a few flags:

> cat my.cnf
[server]
query_cache_size=0
innodb_thread_concurrency=0
innodb_buffer_pool_size=100M
innodb_additional_mem_pool_size=20M

And the final throughput was 1649 transactions per second.

10) Things That Did Not Work as Expected
========================================
We also tried to use atomic instructions and ISM (Intimate Shared Memory), but both of them did not give us performance improvements.

Conclusion (for now)
====================
This was the initial work done to optimize MySQL on the Sun CoolThreads platform, and we got 36% better throughput than the default installation. As MySQL is now part of Sun, I expect that working with Sun engineers would allow MySQL to get even better performance and throughput.

Currently, caching memory inside MySQL looks promising. We got 1.5% improvement by only modifying one place inside MySQL. Since there are quite a few places that we could apply this optimization, there is still room for further performance improvement!

Finally, I should mention that some of the optimizations above also improved MySQL on x64 Linux Solaris. I will update everyone here in the near future. :-)

Performance Guide for MySQL Cluster@MySQL Users Conference

2008-03-25T02:03:00.007+01:00

A new MySQL Users Conference is coming up again. MySQL was acquired recently by Sun Microsystems and thus innovation within will happen at an even faster rate than previously. The Users Conference will contain a lot of interesting presentations on how to develop your MySQL Applications. So come to Santa Clara 15-17 April to take part of the development and discuss with many MySQLers how MySQL will be developed in the next few years. I've prepared a set of blogs that I will publish over the next few days to give you an idea of what's cooking within MySQL and I hope some of these blogs can persuade you to come there and give your opinion on where the future development should be heading.

Personally I'll add my contribution to the talks at the MySQL Users Conference what to think about when building a high performance application based on MySQL Cluster. MySQL Cluster technology has matured over the last few years and is being used in more and more application categories. I even visited a conference on Family History Technology at BYU where I bumped into Matt Garner from FindMyPast (), he told me about how they had used MySQL Cluster for their Data Mining application and sustained a continous flow of 75.000 queries per second.

In my talk I'm planning to cover how partitioning your application data can improve performance, how the use of cluster interconnects can improve response time by as much as a factor of 8, when to use the native NDB API's and when to use SQL, and how to use some new features recently developed.

The MySQL Cluster development has been very focused on developing a feature set for the Telecom space for a few years, the last year development has started focusing more on general features to ensure we get improved performance also on complex SQL queries. Also development of improved features for usage of computers with high number of cores and execution threads (e.g. Niagara processor from Sun) and a number of other performance improvements are developed.

The talk will be very much focused on how you as an application developer can make use of the enormous performance capabilities a MySQL Cluster provides you with. I also hope to be able to present some impressing benchmark numbers using a large cluster Intel has made available to our use.

Performance White Papers on MySQL Cluster

2007-05-05T10:07:00.000+02:00

I've been working on some very interesting benchmarking using the
DBT2 test suite developed by OSDL for MySQL Cluster. As part of this
work I've made significant additions to the DBT2 test to enable clustered
test runs. I've also developed a set of scripts to enable easy start and
stop of MySQL Cluster processes.

The benchmarks include comparisons of various connect methods using
Ethernet and Dolphin Express cards. It also discusses improvements using
the latest version of the Intel Core2 architecture.

As part of this work I discovered a couple of essential performance
optimisations and scalability optimisations. All these improvements
are currently being integrated in MySQL Cluster Carrier Grade Edition.
To enable those wanting "bleeding edge"-access I've also made the
benchmark version available on www.iclaustron.com

A short white paper and the full white paper can be downloaded from
www.dolphinics.com and a MySQL-focused version of the white paper
can be downloaded from www.mysql.com. www.iclaustron.com contains
the exact links for all material available from various places.

The full white paper contains also recommendations of HW architectures
to use for optimal MySQL Cluster performance and scalability.

Performance Tuning of MySQL Cluster

2007-04-09T23:39:00.000+02:00

As you probably have noticed my blog has been a bit quiet lately. I've
been very busy with some very interesting developments. I've been
working very hard on benchmarking of MySQL Cluster together with
Dolphin and Intel. There will be a lot of material coming out from this
the next couple of weeks. I've prepared a couple of white papers on
how MySQL Cluster can scale to new heights.

I'll have a presentation at the MySQL Users Conference
http://www.mysqlconf.com
where I'll describe all the interesting tidbits of how to tune MySQL
Cluster performance. This will include both choice of HW, use of
configuration parameters, which particular new features to especially
look out for and so forth.

If you want to prepare for this then download the white papers that
will be available from MySQL and from Dolphin
http://dev.mysql.com
http://www.dolphinics.com
They should be available there in about a week or so.

Then come and listen to my session at the Users conference at
5.30 on tuesday 24 april. If you still have questions or want to know
even more then come and talk to me, I'll be around at many of
the MySQL Cluster presentations, at the MySQL and the Dolphin
booths in the exhibition hall.

It's been a really interesting project to work on and it's great to be able
to show all these new results that show how one can make use of
MySQL Cluster in a really scalable way.

For those that wish to try the benchmarks out themselves there will
also be a large number of scripts made available to simplify set-up
of MySQL Cluster for large clusters and a fairly heavily revised version
of DBT2 that can be used to run large benchmarks using MySQL
Cluster. More on this later, check out the blog next week for more
info on this.

For those of you that want to know the latest news on partitioning
as well I will also make a presentation of this at the Users conference.
It will include a description of partitioning in 5.1, how to make use
of partitioning for better scalability in MySQL Cluster and finally also
some notes about some new cool developments that are ready to
be put into the 5.2 version of MySQL.

Webinar on MySQL Cluster using Dolphin SuperSockets

2006-11-23T20:13:00.000+01:00

My blogging hasn't been so active lately. Mostly due to that I've been busy on
other things. One of the things that have kept me busy the last few months
is a project which I highly enjoy. I've been performing a benchmark study
of MySQL Cluster using Dolphin SuperSockets. Performance is one of my
favourite topics and a parallel database like MySQL Cluster has a wide array
of performance challenges that makes it very interesting to optimize it.

I will present the results in two webinars on the 30th Nov and 13 dec. The
webinars will also provide some input to the features in Dolphin
SuperSockets and MySQL Cluster that enables high performance and
real-time characteristics. With these changes to MySQL Cluster and using
the Dolphin SuperSockets MySQL Cluster becomes even more adapted for
all types of real-time applications.
See:
http://www.mysql.com/news-and-events/web-seminars/

Performing this work has been an interesting enterprise in finding out how
to best make use of the Dolphin hardware using MySQL Cluster. I found a
number of interesting ways where 1+1 = 3, meaning I've found optimisations
that can be done in MySQL Cluster that are especially effective if using
Dolphin SuperSockets. So as a result of this some very interesting
achievements have been made.

- A completely interrupt-free execution of ndbd nodes in MySQL Cluster
using Dolphin SuperSockets.
- Real-time features added to MySQL Cluster enabling much faster response
times.
- Possibility to lock threads to CPU's in MySQL Cluster enabling a higher level
of control over the execution environment.
- Possibility to lock pages in main memory removing any risk of swapping
- Possibility to choose between polling and interrupt-driven mechanisms in
ndbd kernel

The combination of MySQL Cluster and Dolphin SuperSockets becomes a truly
amazing real-time machine. With those added features in place and using
Dolphin SuperSockets I've also seen how MySQL Cluster can take yet another
step on its on-line recovery features. Using those real-time features it is
possible to get node failover times down to around 10 milliseconds.
MySQL Cluster was already before market leading in this respect, with this
feature the gap to the competitors is bound to increase.

Most of the benchmark work have been focused on the DBT2 benchmark. Most
benchmarks I've done in the past have been focused on applications written
directly for the NDB API. So it's been interesting to see what one needs to do
to make the MySQL Server be really fast.

In order to run DBT2 with MySQL Cluster at first I had to adapt the DBT2
benchmark for:
- Parallel load of data
- Parallel MySQL Servers while running the benchmark
- Using MySQL Cluster features such as HASH indexes, PARTITIONING and
Disk Data for MySQL Cluster.

I also got tired of remembering all the -i -t -h and so forth in the various
scripts and used more real names for the parameters.

There was also a number of performance bugs in DBT2. DBT2 is implementing
the TPC-C specification and in a number of places the SQL queries were made
such that there was a large number of unnecessary extra record fetches in some
queries.

I will soon upload the changes to DBT2 to SourceForge if anyone wants to use
the same benchmark.

Webinar on Partitioning

2006-11-23T19:46:00.000+01:00

As mentioned in an earlier post the partitioning in 5.1 has reached a level of
stability so that it can now be put to some heavier test. To spread further
insights of the new partitioning feature I'll deliver two webinars next week
and the week after that (29 nov and 5 Dec).

You'll find a reference to both from the MySQL Home Page.
http://www.mysql.com/

The first one will give an introduction to partitioning in MySQL and
describe the variants of partitioning that will be supported, which
management variants that are possible and so forth.

The second webinar is a follow-up that will do some repetition to
ensure it can be viewed stand-alone but will mainly dive a little
deeper into various areas of partitioning amongst other how it
relates to MySQL Cluster.

State of MySQL partitioning

2006-09-23T21:08:00.000+02:00

It's been some time since I last blogged. I've been quite busy erasing all the
bugs from the partitioning implementation in MySQL 5.1. At the moment
there is 1 bug left in review and a few on its way into the main clone. The
rest of the bugs are fixed and already in the 5.1 clone. So the next 5.1
release (5.1.12) will have partitioning ready for tough tests. So if you have
been waiting for partitioning to stabilise it's time to try it out now with your
application and see how it works.

I watched an interesting animation my daughter made about how partition
pruning using dynamic PowerPoint slides. Really interesting to see what can
be done if one knows how to handle these types of tools. She's quickly
becoming our family authority on presentations.

Lately we've also started working on some extensions for the partitioning
hopefully ready for 5.2. We have introduced a new partitioning syntax like this:

CREATE TABLE t1 (a char(10), b date)
PARTITION BY RANGE (COLUMNS(b,a))
(PARTITION p0 VALUES LESS THAN ('1999-01-01', "abc"),
PARTITION p1 VALUES LESS THAN ('2001-01-01', MINVALUE),
PARTITION p2 VALUES LESS THAN ('2003-01-01', MAXVALUE));

The nice thing with this syntax is that it can be handled partition pruning with
ranges in a very efficient manner. So the query:
SELECT * FROM t1 WHERE b <= '1999-06-01' AND b >= '1999-02-01';
can be optimised to only scan partition p1.

We are also working on indexes that are partitioned independent of the base
table and also a couple of other features. As usual what actually goes into the
next release is uncertain.

PARTITION by a date column

2006-07-05T15:14:00.004+02:00

One of the most common usage of partitioning is where one wants to partition
by date. One might want to have one partition per year or per month or per
week or per day. This blog entry shows how to handle this requirement using
MySQL 5.1.

The most common method to partition in this case is by range.
Partitioning in 5.1 uses a function on one or more fields of the table. In 5.1
there are some requirements on these fields if unique indexes or primary keys
also exist in the table. The reason is that 5.1 doesn't have support for global
indexes. Development of support for this have however started so should be in
some future release of MySQL.

In 5.1 functions have to return an integer. There are two functions that has
special support in the MySQL server. These are TO_DAYS() and YEAR(). Both
of these functions take a DATE or DATETIME argument and return an integer.
YEAR() returns the year and TO_DAYS() returns the number of days passed
since a particular start date.

The MySQL optimizer has special support for these two partition functions. It
knows that those functions are strictly increasing and use this knowledge to
discover that queries such as:

SELECT * from t1 WHERE a <= '1991-01-31' AND a >= '1991-01-01';
with a partition function PARTITION BY RANGE (to_days(a)) can be mapped
to a range of partition function values starting at
TO_DAYS('1991-01-01') and ending at TO_DAYS("1999-01-31")

Thus the MySQL Server can map TO_DAYS('1991-01-01') to a starting partition
and TO_DAYS('1991-01-31') to an ending partition. Thus we only need to scan
partitions in a range of partitions.

Most functions don't have this nice mapping from value range to partition
range. The functions TO_DAYS(date) and YEAR(date) are known by the
MySQL optimizer to have this attribute and they will thus be better for range
optimisations. Also a partition function on a field which is an integer field
where the function is the field by itself will have this characteristic. Other
functions won't, theoretically many more can be handled but this requires
special care of overflow handling to be correct and this will be added in
some future MySQL release.

So with this knowledge let's set up a that does partition by month.

CREATE TABLE t1 (a date)
PARTITION BY RANGE(TO_DAYS(a))
(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01'),
PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01'),
PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01'),
PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01'),
PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01'),
PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01'),
PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01'),
PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01'),
PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01'),
PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01'),
PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01'),
PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01'),
PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01'),
PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01'),
PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01'),
PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01'),
PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01'),
PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01'),
PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01'),
PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01'),
PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01'),
PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01'),
PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01'),
PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01'),
PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01'),
PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01'),
PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01'),
PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01'),
PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01'),
PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01'),
PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01'),
PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'));

Then load the table with data. Now you might want to see the
data from Q3 2004. So you issue the query:
SELECT * from t1
WHERE a >= '2004-07-01' AND a <= '2004-09-30';
This should now only scan partition p407, p408, p409. You can
check this by using EXPLAIN PARTITIONS on the query:
EXPLAIN PARTITIONS SELECT * from t1
WHERE a >= '2004-07-01' AND a <= '2004-09-30';

You can also get similar results with more complicated expressions.
Assume we want to summarize on all measured Q3's so far.
SELECT * from t1
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
(a >= '2005-07-01' AND a <= '2005-09-30');

Using EXPLAIN PARTITIONS we'll discover the expected result that this
will only scan partitions p407, p408, p409, p507, p508 and p509.

When july comes to its end it is then time to add a new partition for
august 2006 which we do with a quick command:
ALTER TABLE t1 ADD PARTITION
(PARTITION p608 VALUES LESS THAN (TO_DAYS('2006-09-01'));

EXPLAIN to understand partition pruning

2006-05-31T10:36:00.000+02:00

As part of the partitioning development in MySQL 5.1 we've added the ability to
check which partitions of a table that is actually accessed in a particular query.
As partitions in a sense can be a sort of index this is an important feature to
help understand performance impact of a query.

The method to use this feature is the normal EXPLAIN command with an
added keyword PARTITIONS. So e.g.
EXPLAIN PARTITIONS select * from t1;

So a slightly more useful example would be
CREATE TABLE t1 (a int)
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30));

Now if we do an equal query we should only need to access one partition:
This will be verified by the command:
EXPLAIN PARTITIONS select * from t1 WHERE a = 1;
/* Result in p0 being displayed in the list of partitions */

A range query will also be pruned nicely in this case (a is a function that is
increasing and thus range optimisations can be performed, YEAR(date) and
FUNC_TO_DAYS(date) are two other functions that are known to be
monotonically increasing.

EXPLAIN PARTITIONS select * from t1 WHERE a <= 1 AND a>= 12;
/* Result in the range being mapped to p0, p1 */

LIST partitions will be pruned in the same cases as RANGE for range-pruning
of partitions.

HASH partitioning has no natural concept for ranges since different values
map more or less randomly into partitions. We do however apply an
optimisation for short ranges such that the following will happen.

CREATE TABLE t1 (a int)
PARTITION BY HASH (a)
PARTITIONS 10;

EXPLAIN PARTITIONS select * from t1 WHERE a < 3 AND a > 0;
In this case the range consists of only two values 1 and 2. Thus we simply map
the interval to a = 1 OR a = 2 and here we get p1 and p2 as the partitions to
use.

Information Schemas for Partitions

2006-05-31T08:26:00.000+02:00

As part of the work in developing partitioning support for 5.1 a new
information schema table has been added. This table can be used to
retrieve information about properties of individual partitions.

To query this table you can issue a query like:
SELECT * FROM information_schema.partitions WHERE
table_schema = "database_name" AND table_name = "name_of_table";

The result of this particular query will be one record per partition in
the table with info about the properties of these partitions.

A query on a non-partitioned table will produce a similar output
although most fields will be NULL. The information_schema.partitions
table is not yet implemented for MySQL Cluster so for MySQL Cluster
tables the output will be all NULLs on the partition specific information.

Below follows a short description of the fields in this information
schema table:
1) TABLE_CATALOG: this field is always NULL
2) TABLE_SCHEMA: This field contains the database name of the table
3) TABLE_NAME: Table name
4) PARTITION_NAME: Name of the partition
5) SUBPARTITION_NAME: Name of subpartition if one exists otherwise
NULL
6) PARTITION_ORDINAL_POSITION: All partitions are ordered in the
same order as they were defined, this order can change as management
of partitions add, drop and reorganize partitions. This number is the
current order with number 1 as the number of the first partition
7) SUBPARTITION_ORDINAL_POSITION: Order of subpartitions within a
partition, starts at 1
8) PARTITION_METHOD: Any of the partitioning variants: RANGE, LIST,
HASH, LINEAR HASH, KEY, LINEAR KEY
9) SUBPARTITION_METHOD: Any of the subpartitioning variants: HASH,
LINEAR HASH, KEY, LINEAR KEY
10) PARTITION_EXPRESSION: This is the expression for the partition
function as expressed when creating partitioning on the table through
CREATE TABLE or ALTER TABLE.
11) SUBPARTITION_EXPRESSION: Same for the subpartition function
12) PARTITION_DESCRIPTION: This is used for RANGE and LIST partitions:
RANGE: Contains the value defined in VALUES LESS THAN. This is an
integer value, so if the CREATE TABLE contained a constant expression
this contains the evaluated expression, thus an integer value
LIST: The values defined in VALUES IN. This is a comma-separated list of
integer values.
13) TABLE_ROWS: Although its name indicates that it is the number of
rows in the table, it is actually the number of rows in the partition.
14) AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH,
INDEX_LENGTH, DATA_FREE, CREATE_TIME, UPDATE_TIME, CHECK_TIME,
CHECKSUM:
All these fields are to be interpreted in the same as for a normal table
except that the value is the value for the partition and not for the table.
23) PARTITION_COMMENT: Comment on the partition
24) NODEGROUP: This is the nodegroup of the partition. This is only
relevant for MySQL Cluster.
25) TABLESPACE_NAME: This is the tablespace name of the partition.
This is currently not relevant for any storage engine.

Partition and Scanning

2006-03-27T20:25:00.000+02:00

If a query does a SELECT the optimizer will discover which partitions
that has to be scanned. In 5.1 these scans will still be made
sequentially on one partition at a time. However only those partitions
actually touched will be scanned which can improve performance on
certain queries by a magnitude.

The aim is to support parallel scan in a future release as it is to
support parallel sort on the first table selected by the optimizer.
This is an important long-term goal of partitioning to open up the
MySQL architecture for many performance improvements on
handling large data sizes. In 5.1 we have achieved quite a few of
those goals but expect to see more goals achieved as new versions
of MySQL hits the street burning :)