MySQL Bugs: #48229: group by performance issue of partitioned table

Bug #48229	group by performance issue of partitioned table
Submitted:	22 Oct 2009 13:22	Modified:	18 Jun 2010 12:54
Reporter:	Hao Wu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	5.1.37,5.1.38	OS:	Linux (CentOS 4.4)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	partition, performance

Description:
The performance of some sql with "group by" on partitioned table is very bad.

How to repeat:
1.
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 20 */
1 row in set (0.00 sec)

2.
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. insert into t1 and t2 with same 2 million random data

4. key_buffer is large enough to hold all indexes in memory

5. 
mysql> select a, max(b) from t1 where a in (10,100) group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |   2956 |
|  100 |   2982 |
+------+--------+
2 rows in set (0.25 sec)

6.
mysql> select a, max(b) from t2 where a in (10,100) group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |   2956 |
|  100 |   2982 |
+------+--------+
2 rows in set (0.00 sec)

7.
mysql> select a, max(b) from (select a, b from t1 where a in (10,100)) as t  group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |   2956 |
|  100 |   2982 |
+------+--------+
2 rows in set (0.00 sec)

8. In our product server, a table like "t1" has 100 million records, and the sql in step 5 may take several seconds even minutes to finish, but the sql in step 7 takes only milliseconds.

Thank you for the problem report. Please, send the results of:

explain select a, max(b) from t1 where a in (10,100) group by a\G
explain partitions select a, max(b) from t1 where a in (10,100) group by a\G

explain select a, max(b) from t2 where a in (10,100) group by a\G

for your test data.

mysql> explain select a, max(b) from t1 where a in (10,100) group by a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where; Using index for group-by
1 row in set (0.00 sec)

mysql> explain partitions select a, max(b) from t1 where a in (10,100) group by a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p10
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where; Using index for group-by
1 row in set (0.00 sec)

mysql> explain select a, max(b) from t2 where a in (10,100) group by a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where; Using index for group-by
1 row in set (0.00 sec)

See bug: http://bugs.mysql.com/bug.php?id=37252.

Thank you for the feedback. Verified as described.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.41-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2100000 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
|  2100000 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select a, max(b) from t1 where a in (10,100) group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (7.85 sec)

mysql 5.1 >select a, max(b) from t2 where a in (10,100) group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

mysql 5.1 >select a, max(b) from (select a, b from t1 where a in (10,100)) as t  group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.04 sec)

mysql 5.1 >show create table t1;

| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 20 */ |

mysql 5.1 >show create table t2;

| t2    | CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Client to populate tables

Attachment: bug48229.cpp (application/octet-stream, text), 3.74 KiB.

Is this repeatable in 5.1.41+ ?

I could not repeat it in latest mysql-5.1-bugteam or with 5.1.4[12] on mac os x 10.6 running x86_64 binaries.

Running this script on mysql-5.1.41-osx10.5-x86_64 (on ramdisk):
mysql-test$ cat bug48229.sql 
drop view if exists v;
drop view if exists x;
drop table if exists t1;
create view v as select 1 union select 2 union select 3 union select 4;
create view x as select 1 from v,v a,v b,v c,v d,v e;
CREATE TABLE `t1` (
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY HASH (a)
PARTITIONS 1;

select 'One partition';
set @a := 0;
insert t1 select @a, (@a := @a + 1) from x,x a;

select count(*) from t1;
select count(*) from t1;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
DROP TABLE t1;
CREATE TABLE `t1` (
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY HASH (a)
PARTITIONS 20;

select 'Twenty partitions';
set @a := 0;
insert t1 select @a, (@a := @a + 1) from x,x a;

select count(*) from t1;
select count(*) from t1;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
DROP TABLE t1;
CREATE TABLE `t1` (
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

select 'Non partitioned table';
set @a := 0;
insert t1 select @a, (@a := @a + 1) from x,x a;

select count(*) from t1;
select count(*) from t1;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from t1 where a in (10,100) group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
select a, max(b) from (select a, b from t1 where a in (10,100)) as t group by a;
drop view x;
drop view v;
drop table t1;

Gives this result:
mysql-test$ ../client/mysql -u root -h 127.0.0.1 -P 13000 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source bug48229.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

+---------------+
| One partition |
+---------------+
| One partition |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 16777216 rows affected (2 min 33.17 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

+-------------------+
| Twenty partitions |
+-------------------+
| Twenty partitions |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 16777216 rows affected (2 min 24.20 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.01 sec)

+-----------------------+
| Non partitioned table |
+-----------------------+
| Non partitioned table |
+-----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 16777216 rows affected (3 min 14.34 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

+------+--------+
| a    | max(b) |
+------+--------+
|   10 |     11 |
|  100 |    101 |
+------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.41-log |
+------------+
1 row in set (0.00 sec)

I have also made runs on linux both on ram and disk without getting worse performance of partitioned tables.

If this bug still exists, could you please include test data or generation script (preferable perl or any other non platform specific language)?

I can repeat it in 5.1.42, but can not with your sql srcipt.
I think maybe because I use random data.

perl script to populate table

Attachment: bug48229.pl (application/octet-stream, text), 211 bytes.

Using Perl script provided by reporter (changing just # of inserts):

C:\bugs>c:\dbs\51

C:\bugs>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.44-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >select count(*) from t1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >select a, max(b) from t1 where a in (977,10010) group by a;
+-------+---------+
| a     | max(b)  |
+-------+---------+
|   977 | 1960571 |
| 10010 | 1994995 |
+-------+---------+
2 rows in set (0.20 sec)

mysql 5.1 >select a, max(b) from t2 where a in (977,10010) group by a;
+-------+---------+
| a     | max(b)  |
+-------+---------+
|   977 | 1968079 |
| 10010 | 1980286 |
+-------+---------+
2 rows in set (0.00 sec)

mysql 5.1 >show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 20 */
1 row in set (0.00 sec)

mysql 5.1 >show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql 5.1 >

The problem is in different execution plans.

Created by:
CREATE TABLE `t1` (
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY HASH (a)
PARTITIONS 1;
<run the perlscript loading 1'000'000 rows into t1>
create table t2 like t1;
alter table t2 remove partitioning;
insert into t2 select * from t1;

mysql> explain select a, max(b) from t2 where a in (10,100) group by a;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t2    | range | a             | a    | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select a, max(b) from t1 where a in (10,100) group by a;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    1 | Using where; Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

Note the different text in the 'Extra' column.

stats.block_size is not set in ha_partition::info(HA_STATUS_CONSTANT), fixed by:
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc	2009-11-17 19:02:16 +0000
+++ sql/ha_partition.cc	2010-02-04 14:59:15 +0000
@@ -5054,6 +5054,7 @@
     file= m_file[handler_instance];
     file->info(HA_STATUS_CONST);
     stats.create_time= file->stats.create_time;
+    stats.block_size= file->stats.block_size;
     ref_length= m_ref_length;
   }
   if (flag & HA_STATUS_ERRKEY)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99334

3319 Mattias Jonsson	2010-02-04
      Bug#48229: group by performance issue of partitioned table
      
      Problem was block_size on partitioned tables was not set,
      resulting in bad optimizer decision.
      
      Fixed by setting stats.block_size correctly.
     @ mysql-test/r/partition_range.result
        Bug#48229: group by performance issue of partitioned table
        
        Added result
     @ mysql-test/t/partition_range.test
        Bug#48229: group by performance issue of partitioned table
        
        Added test
     @ sql/ha_partition.cc
        Bug#48229: group by performance issue of partitioned table
        
        Added missing assignment of stats.block_size.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102278

3319 Mattias Jonsson	2010-03-04
      Bug#48229: group by performance issue of partitioned table
      
      Problem was block_size on partitioned tables was not set,
      resulting in keys_per_block was not correct which affects
      the cost calculation for read time of indexes (including
      cost for group min/max).Which resulted in a bad optimizer
      decision.
      
      Fixed by setting stats.block_size correctly.
     @ mysql-test/r/partition_range.result
        Bug#48229: group by performance issue of partitioned table
        
        Added result
     @ mysql-test/t/partition_range.test
        Bug#48229: group by performance issue of partitioned table
        
        Added test
     @ sql/ha_partition.cc
        Bug#48229: group by performance issue of partitioned table
        
        Added missing assignment of stats.block_size.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103068

3320 Mattias Jonsson	2010-03-12
      Bug#48229: group by performance issue of partitioned table
      
      Additional result file update

Found an issue when merging to mysql-6.0-codebase trees, I'm #ifdef'ing the fix out in mysql-pe since bug#52092.

pushed to mysql-5.1-bugteam and null-merged to mysql-pe (waiting for bug#52092).

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

Documented bugfix in the 5.5.4 changelog as follows:

        GROUP BY queries performed poorly for some partitioned tables.
        This was due to block_size not being set for partitioned tables,
        with the result that keys_per_block was not correct, which could
        cause such queries to be optimized incorrectly.

Waiting for 5.1 merge.

5.5.4 changelog entry was moved to 5.5.5.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:martin.hansson@sun.com-20100316162138-u9724fhm54cj3or0) (merge vers: 5.1.46) (pib:16)

Also documented fix in the 5.1.46 changelog.

Closed.

Added changelog entry for 6.0.14.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)