InnoDB compression is getting some traction, and I see quite contradictory opinions. Someone has successful deployments in productions, and someone says that compression in current implementation is useless.
To get some initial impression about performance I decided to run some sysbench with multi-tables benchmarks.
I actually was preparing to do complex research, but even first initial results are quite discouraging.

My setup: Dell PowerEdge R900, running Percona-Server-5.1.57-rel12.8 (will be in public release soon), storage is FusionIO 320GB MLC card, which does not matter a lot in this case of CPU-bound benchmark.

First stage – load data. Scripts for multi-table sysbench allow to load data in …

Some time ago I wrote about MySQL 5.5 semi-synchronous replication. Since then, I have wanted to benchmark the overhead of semi-synchronous replication with a decent server. Now the occasion presented itself, thanks to some related business that I had to benchmark, and thus I did a few simple runs with and without semi-synchronous replication enabled, to see the impact of this feature on performance. If you haven't read the article on semi-synchronous replication, the bottom line is that, with this feature enabled, the master waits until at least one slave has acknowledged receipt for the data before returning a positive result to the client. This means that for each commit there are two network calls between master and slave. My gut feeling was that this feature would be costly in terms of query response time, although I was not prepared to …

For a long time I’ve wanted to know how MySQL scales as you add more memory to the server. Vadim recently benchmarked the effects of increasing memory and CPU core count. He looked for a balance between utilizing the hardware as much as possible, limiting the system complexity, and lowering the price-to-performance ratio.

The outcome of the research, which was sponsored by Virident, is that as you add CPUs and increase memory size, MySQL doesn’t scale as well as we would like, and solid-state storage — specifically, the Virident tachIOn drive — has more bandwidth than MySQL can fully utilize at present. Therefore, to decrease the price-to-performance ratio and increase the utilization of the tachIOn drive, Vadim sharded the database into smaller instances and colocated them on the same machine. It’s not a new approach, but to date I’m not aware of anyone measuring the different configurations the way Vadim has done.

You …

I started going over the slides from talks from MySQL Conference and Expo 2011 to pick set of must see presentations and publish the list, but this is not happening due to lack of time. Instead I’m only going to recommend 1 talk, from list of tutorials I had a chance to review. If you have not check it out yet, take a look at Linux and Hardware Optimizations by Yoshinori Matsunobu. Check out the slides which are very well done with many graphs and verbose enough explanations so you can easily follow slides alone.

Were you excited by some other talk on MySQL Conference and Expo ? Feel free to leave it as a comment to this post.

Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to significantly improve the performance of queries by spreading the work over all available compute resources. In this test, Shard-Query averages a nearly 6x (max over 10x) improvement over the baseline, as shown in the following graph:

One significant advantage of Shard-Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL …

Part of the InnoDB duties, being an MVCC-implementing storage engine, is to get rid of–purge–the old versions of the records as they become obsolete.  In MySQL 5.1 this is done by the master InnoDB thread.  Since then, InnoDB has been moving towards the parallelized purge: in MySQL 5.5 there is an option to have a single separate dedicated purge thread and in MySQL 5.6.2 one can have multiple dedicated purge threads.

Percona Server 5.1 supports multiple purge threads too, although using more than one is considered experimental at the moment. Unfortunately this patch hasn’t been ported to Percona Server 5.5 yet.

Let’s test these two implementations and find out what benefits, if any, do the additional purge threads bring.

The test workload makes a long history list and then lets purge thread(s) work through it while having a regular OLTP load on the server.  The OLTP part of this is provided by …

My previous benchmark on Performance Schema was mainly in memory workload and against single tables.
Now after adding multi-tables support to sysbench, it is interesting to see what statistic we can get from workload that produces some disk IO.

So let’s run sysbench against 100 tables, each 5000000 rows (~1.2G ) and buffer pool 30G.

The scripts and results are on Benchmark Wiki.

If we look on performance overhead it appears rather big in read-only benchmark, and it is well explained in …

We just pushed to sysbench support for workload against multiple tables ( traditionally it used only single table).

It is available from launchpad source tree lp:sysbench .

This is set of LUA scripts for sysbench 0.5 ( it supports scripting), and it works following way:

- you should use --test=tests/db/oltp.lua to run OLTP test
i.e. prepare

./sysbench --test=tests/db/oltp.lua --oltp-tables-count=25 prepare

run:

./sysbench --test=tests/db/oltp.lua --oltp-tables-count=25 --num-threads=5 run

oltp.lua should understand most options that available for regular sysbench –test=oltp

there are couple other scripts, like
oltp_simple
select
insert
delete
update_index
update_non_index

to support different OLTP …

As continuation of my CPU benchmarks it is interesting to see what is scalability limitation in MySQL 5.6.2, and I am going to check that using PERFORMANCE SCHEMA, but before that let’s estimate what is potential overhead of using PERFORMANCE SCHEMA. So I am going to run the same benchmarks (sysbench read-only and read-write) as in previous post with different performance schema options and compare results.

I am going to use Cisco UCS C250
with next settings:

• PERFORMANCE SCHEMA disabled (NO PS)
• PERFORMANCE SCHMEA enabled, with all consumers ON (PS on)
• PERFORMANCE SCHMEA enabled, but only global_instrumentation consumer enabled. It allows to gather table …

Having two big boxes in our lab, one based Intel Nehalem (Cisco UCS C250) and second on AMD Opteron (Dell PowerEdge R815), I decided to run some simple sysbench benchmark to compare how both CPUs perform and what kind of scalability we can expect.

It is hard to make apples to apples comparison, but I think it is still interesting.
Cisco UCS C250 has total 12 cores / 24 threads of Intel Nehalem X5670, and Dell PowerEdge R815 has 48 cores of AMD Opteron 6168.
One of biggest difference is that Cisco is running CentOS 5.5 and Dell R815 is based on RedHat EL 6. I will probably will need to rerun benchmark after upgrade Cisco to CentOS 6 ( will be it even released or it is dead ? ).
For benchmark I took sysbench oltp ( both …