Shard-Query blog

The only open source MPP database engine for MySQL

Monthly Archives: May 2013

Bulk insert into tables in sorted order to avoid deadlocks

Shard-Query inserts data into a “coordinator” table when answering queries.   When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes.   Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.

For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.

Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the coordinator table, which serializes queries at the bulk insert stage.  Having to insert the rows more than once due to the deadlock is not efficient.

I am going to test removing ORDER BY NULL which will distribute the sort.  The deadlocks should go away and concurrency should go up. I’ll have to test it out and if it improves performance it will be included in beta 2 of Shard-Query 2.0.

Fix for INFORMATION_SCHEMA.PARTITIONS losing table stats

Here is a fix for the MySQL/TokuDB/MariaDB bug I reported earlier today.  I think this fix is correct (it is only one line) but I don’t delve into the storage engine very often (and particularly not into so I think it would be good to wait for Oracle (or Percona, MariaDB, or Tokutek) to validate that it is correct before using it.

diff -u /tmp/ 
--- 2013-04-05 05:27:18.000000000 -0700
+++ /tmp/ 2013-05-27 02:45:01.680676228 -0700
@@ -6455,9 +6455,11 @@
void ha_partition::get_dynamic_partition_info(PARTITION_STATS *stat_info,
uint part_id)
handler *file= m_file[part_id];
DBUG_ASSERT(bitmap_is_set(&(m_part_info->read_partitions), part_id));
stat_info->records= file->stats.records;

Shard-Query 2.0 performance on the SSB with InnoDB on Tokutek’s MariaDB distribution

Scaling up a workload to many cores on a single host

Here are results for Shard-Query 2.0 Beta 1* on the Star Schema Benchmark at scale factor 10.  In the comparison below the “single threaded” response times for InnoDB are the response times reported in my previous test which did not use Shard-Query.

Shard-Query configuration

Shard-Query has been configured to use a single host.  The Shard-Query configuration repository is stored on the host.  Gearman is also running on the host, as are the Gearman workers.  In short, only one host is involved in the testing.

The Shard-Query response times are for 6 gearman workers.  There are six physical cores in my test machine.  In my testing I’ve found that Shard-Query works best when the number of  workers is equal to the number of physical cores.

Why partitions?

As in the previous test the lineorder table is partitioned.  This allows Shard-Query to automatically take advantage of multiple cores without changing any of the queries.

How?  Well Shard-Query transforms a single expensive query into smaller “tasks”.  Each task is a query which examines a small amount of data.  Shard-Query takes a “divide and conquer” approach, where the data is divided into small chunks, and the chunks are operated on in parallel.  Shard-Query treats each partition as a chunk.  Future versions of Shard-Query will support subpartitions and hash partitions in MySQL 5.6.


In general, Shard-Query is faster than MySQL both cold and hot.  There are a few cases where the speed is about the same, or where Shard-Query is slower.  I believe this is due to MySQL bug #68079: queries may not scale linearly on MySQL.  Star schema optimization is not turned on for these queries, so each of the sub-tasks is joining many rows.









Shard-Query 2.0 Beta 1 (patched for I_S.partitions)
Tokutek MariaDB 5.5.30-7.0.1

MySQL bug 69179 – INFORMATION_SCHEMA.PARTITIONS causes query plan changes

Shard-Query examines INFORMATION_SCHEMA.PARTITIONS to determine if a table is partitioned.  When a table is partitioned, Shard-Query creates multiple background queries, each limited to a single partition.  Unfortunately, it seems that examining INFORMATION_SCHEMA.PARTITIONS causes query plans to change after the view is accessed.

I have reported bug 69179 to MySQL AB  Oracle Corporation(old habits die hard).

Be careful: If you have automated tools (like schema management GUI tools) then make sure they don’t examine INFORMATION_SCHEMA.PARTITIONS or you may get bad plans until you analyze your tables or restart the database, even if using persistent stats.

I can only get the bug to happen when a WHERE clause is issued that limits access to a single partition.  It may be that the per partition statistics get wiped out when the I_S table is accessed, but the per table stats remain unchanged.

In order to get stable and good query plans I implemented a workaround in my local Shard-Query branch.  I replaced access to INFORMATION_SCHEMA.PARTITIONS with a copy of the table stored in shard_query.partitions.  This ensures that the query plans don’t change between the cold run and the hot run.  This bug affects MySQL 5.5.31, 5.6.11, TokuDB 5.5.30, mariadb-5.5.30-tokudb-7.0.1 and possibly others.

TokuDB vs Percona XtraDB using Tokutek’s MariaDB distribution

Following are benchmark results comparing Tokutek TokuDB and Percona XtraDB at scale factor 10 on the Star Schema benchmark. I’m posting this on the Shard-Query blog because I am going to compare the performance of Shard-Query on the benchmark on these two engines. First, however, I think it is important to see how they perform in isolation without concurrency.

Because I am going to be testing Shard-Query, I have chosen to partition the “fact” table (lineorder) by month. I’ve attached the full DDL at the end of the post as well as the queries again for reference.

I want to note a few things about the results:
First and foremost, TokuDB was configured to use quicklz compression (the default) and InnoDB compression was not used. No tuning of TokuDB was performed, which means it will use up to 50% of memory by default. Various InnoDB tuning options were set (see the end of the post) but the most important is that the innodb_buffer_pool_size which was set to 20G (all data fits in buffer pool for hot test). Read more of this post

Shard-Query 2.0 Beta 1 released

It is finally here.  After three years of development, the new version of Shard-Query is finally available for broad testing.

This new version of Shard-Query is vastly improved over previous versions in many ways.  This is in large part due to the fact that the previous version of Shard-Query (version 1.1) entered into production at a large company.  Their feedback during implementation was invaluable in building the new Shard-Query features.   The great thing is that this means that many of the new 2.0 features have already been tested in at least one production environment.

This post is intended to highlight the new features in Shard-Query 2.0.  I will be making posts about individual features as well as posting benchmark results.

Configuration Repository
Shard-Query 1.x stored the configuration in a .ini file.  In an installation with many nodes, keeping the .ini file in sync was difficult.  There was no way to guarantee that all of Shard-Query saw the state of the cluster (in terms of node composition) as an entire unit.  Shard-Query 2.0 solves this problem and makes configuration and bootstrapping of new nodes simpler.  Shard-Query 2.0 stores the configuration inside of a database schema, rather than in a flat file.  Each node is made aware of the configuration repository through the node setup process.  The cluster configuration is then read from the repository each time Shard-Query initializes. Read more of this post

A new blog for Shard-Query

Instead of posting things about Shard-Query on my personal blog, I decided to get a dedicated space on the web for blogging about Shard-Query.

Oh, and Shard-Query 2.0 beta 1 is out today:

I’ll do a blog post with a quick overview of the new features and changes soon.