Re: A first table partitioning exercise.

[patrick flaherty]

I’m running on an ideapad y550p bought in Jan of this yr (2010). Core i7 processor; 6 GB of memory (I added 2 to the 4 that it came with); 7200 rpm 500 GB disk (divided into various hardware partitions).

Windows 7 Ultimate 64 bit.

This is the MySql build (I named the directory where I put it with the same version as in the kit I downloaded):

d:\Projects\mysql-5.5.5-m3-winx64

Here’s how I CREATEd the two tables, which should be verbatim from off of

http://dev.mysql.com/tech-resources/articles/performance-partitioning.html

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 );


create table no_part_tab
      (c1 int(11) default NULL,
      c2 varchar(30) default NULL,
      c3 date default NULL) engine=myisam;

I noticed that both tables are myisam and wondered about that. I believe MyIsam (Index structured access method) is the non-transactional engine while InnoDb is the transactional.

mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| bulk_insert_buffer_size      | 8388608   |
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 134217728 |
| innodb_change_buffering      | all       |
| innodb_log_buffer_size       | 8388608   |
| join_buffer_size             | 131072    |
| key_buffer_size              | 268435456 |
| myisam_sort_buffer_size      | 67108864  |
| net_buffer_length            | 16384     |
| preload_buffer_size          | 32768     |
| read_buffer_size             | 1048576   |
| read_rnd_buffer_size         | 4194304   |
| sort_buffer_size             | 1048576   |
| sql_buffer_result            | OFF       |
+------------------------------+-----------+
14 rows in set (0.06 sec)

mysql> SHOW TABLE STATUS LIKE '%part_tab';
+-------------+--------+---------+------------+---------+----------------+------
-------+-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+-------------------+----------+--
--------------+---------+
| Name        | Engine | Version | Row_format | Rows    | Avg_row_length | Data_
length | Max_data_length | Index_length | Data_free | Auto_increment | Create_ti
me         | Update_time         | Check_time | Collation         | Checksum | C
reate_options | Comment |
+-------------+--------+---------+------------+---------+----------------+------
-------+-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+-------------------+----------+--
--------------+---------+
| no_part_tab | MyISAM |      10 | Dynamic    | 8000000 |             31 |   255
999996 | 281474976710655 |         1024 |         0 |           NULL | 2010-11-2
7 10:58:56 | 2010-11-27 10:59:51 | NULL       | latin1_swedish_ci |     NULL |
              |         |
| part_tab    | MyISAM |      10 | Dynamic    | 8000000 |             31 |   255
999996 |               0 |        12288 |         0 |           NULL | 2010-11-2
6 15:06:25 | 2010-11-26 18:24:17 | NULL       | latin1_swedish_ci |     NULL | p
artitioned    |         |
+-------------+--------+---------+------------+---------+----------------+------
-------+-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+-------------------+----------+--
--------------+---------+
2 rows in set (0.30 sec)

mysql>

There’s probably more that I need to do, but this message is already long.

Thanx for your help - pat