MySQL Forums
Forum List  »  Partitioning

Re: A first table partitioning exercise.
Posted by: patrick flaherty
Date: December 03, 2010 03:46PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1786
December 02, 2010 11:00PM
Re: A first table partitioning exercise.
1713
December 03, 2010 03:46PM


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.