MySQL Forums
Forum List  »  Partitioning

Re: A first table partitioning exercise.
Posted by: Mattias Jonsson
Date: December 04, 2010 06:00AM

Really strange, I have tested both 5.5.5-m3-winx64 and 5.5.7-rc on a windows machine (Server 2008, Optron 2.8 GHz x 2, 4GB RAM) with consistent results like the ones I reported before.

I cannot understand how your query on the partitioned myisam table can take ~3 seconds when the non partitioned myisam takes ~5 seconds. Is this repeatable on any other machine or setup? If you can help me repeat this bad performance I can look into it and probably fix it, but currently I cannot even repeat it.

Could you also do a 'SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';' to show that the rows are distributed like this:
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';
+-------------+----------------+------------+----------------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION |
+-------------+----------------+------------+----------------------+
| no_part_tab | NULL | 8000000 | NULL |
| part_tab | p0 | 0 | year(c3) |
| part_tab | p1 | 798458 | year(c3) |
| part_tab | p2 | 801766 | year(c3) |
| part_tab | p3 | 799590 | year(c3) |
| part_tab | p4 | 799553 | year(c3) |
| part_tab | p5 | 799538 | year(c3) |
| part_tab | p6 | 801742 | year(c3) |
| part_tab | p7 | 799579 | year(c3) |
| part_tab | p8 | 799586 | year(c3) |
| part_tab | p9 | 799557 | year(c3) |
| part_tab | p10 | 800631 | year(c3) |
| part_tab | p11 | 0 | year(c3) |
+-------------+----------------+------------+----------------------+
13 rows in set (0.00 sec)

This is the output of my script (note that I'm loading into the non partitioned table first to have more realistic data):
mysql> CREATE TABLE part_tab
-> ( c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL
-> ) engine=myisam
-> PARTITION BY RANGE (year(c3))
-> (PARTITION p0 VALUES LESS THAN (1995),
-> PARTITION p1 VALUES LESS THAN (1996),
-> PARTITION p2 VALUES LESS THAN (1997) ,
-> PARTITION p3 VALUES LESS THAN (1998),
-> PARTITION p4 VALUES LESS THAN (1999) ,
-> PARTITION p5 VALUES LESS THAN (2000),
-> PARTITION p6 VALUES LESS THAN (2001) ,
-> PARTITION p7 VALUES LESS THAN (2002),
-> PARTITION p8 VALUES LESS THAN (2003) ,
-> PARTITION p9 VALUES LESS THAN (2004),
-> PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> create table no_part_tab
-> (c1 int(11) default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> delimiter //
mysql> CREATE PROCEDURE load_no_part_tab()
-> begin
-> declare v int default 0;
-> while v < 8000000
-> do
-> insert into no_part_tab
-> values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod
3652));
-> set v = v + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call load_no_part_tab();
Query OK, 1 row affected (5 min 6.92 sec)

mysql> insert into part_tab select * from no_part_tab;
Query OK, 8000000 rows affected (9.96 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

mysql>
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.88 sec)

mysql>
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.43 sec)

mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.43 sec)

mysql>
mysql> select count(*) from no_part_tab where c3 > '1995-01-01' and c3 < '1995-1
2-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.91 sec)

mysql> select count(*) from no_part_tab where c3 > '1995-01-01' and c3 < '1995-1
2-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.92 sec)

mysql>
mysql> select count(*) from part_tab where c3 > '1995-01-01' and c3 < '1995-12-3
1';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.44 sec)

mysql> select count(*) from part_tab where c3 > '1995-01-01' and c3 < '1995-12-3
1';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.44 sec)

mysql>
mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01' and
c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions select count(*) from part_tab where c3 > date '1995-01
-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)

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

mysql>

Options: ReplyQuote


Subject
Views
Written By
Posted
1748
December 02, 2010 11:00PM
Re: A first table partitioning exercise.
2843
December 04, 2010 06:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.