August 12, 2013
Posted by on
I checked some updates to Shard-Query into SVN.
Partitioning support has been extended for MySQL 5.6+ to ALL partitioning types.
This includes all previously unsupported types including RANGE LIST/COLUMNS partitioned tables that are partitioned over more than one column, and HASH/KEY/LINEAR variants as well. Shard-Query now exclusively uses the PARTITION hint for partition elimination instead of WHERE clauses in MySQL 5.6. For 5.5 and previous versions, support remains limited to LIST,RANGE, and LIST/RANGE COLUMNS over a single column.
The old mysql interface DAL has been replaced completely by the PDO DAL.
There is no major difference for end users except that you have to check that the return of the query() method is an object with the is_object() function instead of checking that it is a resource with the is_resource() function. I updated bin/run_query, which is the example application.
I made a few bug fixes to the PDO DAL as well. Thanks again to Alex Hurd for contributing the PDO DAL.
May 26, 2013
Posted by on
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.