| Bug #40227 | I can't create partitions over 8 number in mysql cluster. | ||
|---|---|---|---|
| Submitted: | 22 Oct 2008 0:45 | Modified: | 28 Oct 2008 5:41 |
| Reporter: | ws lee | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1-telco-6.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.1, 5.1-telco-6.2 bzr, mysql5.1.27-ndb6.3.17 | ||
[22 Oct 2008 5:46]
Jonas Oreland
do you have any benchmark showing that it's a good idea to have many partitions on an 2-node cluster?
[22 Oct 2008 5:55]
Sveta Smirnova
Thank you for the report. Verified as described.
[22 Oct 2008 6:42]
ws lee
To. Jonas Oreland
t_default and t_part8 are same table and have same data.
only partition number is not same.
mysql> select count(*) from t_default;
+----------+
| count(*) |
+----------+
| 1463340 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from t_part8;
+----------+
| count(*) |
+----------+
| 1463340 |
+----------+
1 row in set (0.00 sec)
mysql> select table_name,partition_name from information_schema.partitions where table_schema='test' and table_name in ('t_default','t_part8') order by table_name,partition_name;
+------------+----------------+
| table_name | partition_name |
+------------+----------------+
| t_default | p0 |
| t_default | p1 |
| t_part8 | p0 |
| t_part8 | p1 |
| t_part8 | p2 |
| t_part8 | p3 |
| t_part8 | p4 |
| t_part8 | p5 |
| t_part8 | p6 |
| t_part8 | p7 |
+------------+----------------+
10 rows in set (0.00 sec)
created_t column has not index.
mysql> select max(created_t) from t_default;
+---------------------+
| max(created_t) |
+---------------------+
| 2008-10-22 11:10:38 |
+---------------------+
1 row in set (16.51 sec)
mysql> select max(created_t) from t_default;
+---------------------+
| max(created_t) |
+---------------------+
| 2008-10-22 11:10:38 |
+---------------------+
1 row in set (16.62 sec)
mysql> select max(created_t) from t_part8;
+---------------------+
| max(created_t) |
+---------------------+
| 2008-10-22 11:10:38 |
+---------------------+
1 row in set (5.11 sec)
mysql> select max(created_t) from t_part8;
+---------------------+
| max(created_t) |
+---------------------+
| 2008-10-22 11:10:38 |
+---------------------+
1 row in set (5.15 sec)
Like the upper, 8 partition talbe is faster than 2 partion table, 3 times.
[23 Oct 2008 11:14]
Jonas Oreland
good angle.
[24 Oct 2008 11:15]
Frazer Clement
Hi, It looks like the extra partitions you create have the effect of giving you a bigger result batch size - the number of rows sent from the NDBD nodes to the MySQLD node at a time - this is speeding up some scans. You should be able to get the same/better speedup by keeping the default partitioning, and increasing the batch size configuration for your MySQLD. http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-api-definition.html Did you have any other reason for using partitioning? Having multiple key partitions per node is not generally useful, unless you hit hard limits on the number of rows/partition. It adds memory and some CPU overhead in the data nodes. Frazer
[28 Oct 2008 5:41]
ws lee
To. Frazer Clement http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-api-definition.html BatchByteSize=1M BatchSize=992 MaxScanBatchSize=16M According to upper parameter, Table full scan becamed faster. and upper parameter + 8 paration becamed more and more faster. many partion is useful full scan alomost case.
Description: NoOfReplica=2 and, I have 2 data node. In this configureation, I can't create partitions over 8 number. How to repeat: mysql> create table test.a (id int not null,primary key(id)) engine=ndbcluster -> PARTITION BY KEY() PARTITIONS 8; Query OK, 0 rows affected (1.68 sec) mysql> create table test.a (id int not null,primary key(id)) engine=ndbcluster -> PARTITION BY KEY() PARTITIONS 9; ERROR 1005 (HY000): Can't create table 'test.a' (errno: 1224) mysql> show warnings; +-------+------+----------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------+ | Error | 1296 | Got error 1224 'Unknown error code' from NDB | | Error | 1005 | Can't create table 'test.a' (errno: 1224) | +-------+------+----------------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: Remove limit of 8 partitions per node/table. To increasing partitions only good performance. I don't want add more node. To add node cause normally bad perfromance. I want to increase partitions number only.