Shard-Query blog

The only open source MPP database engine for MySQL

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.

2 responses to “MySQL bug 69179 – INFORMATION_SCHEMA.PARTITIONS causes query plan changes

  1. Justin May 26, 2013 at 2:43 PM

    Note: innodb_stats_on_metatdata is not at fault here. The problem happens no matter what the setting of innodb_stats_on_metadata is, and irrespective of the status of the persistent statistics settings in 5.6.

  2. Pingback: Shard-Query 2.0 performance on the SSB with InnoDB | Shard-Query blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 162 other followers

%d bloggers like this: