0

My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to understand more:

(1) NDB Cluster normally partitions NDBCLUSTER tables automatically. So when I tried to check my table, it shows there are 2 partitions inside

mysql> explain partitions select * from my_request;
+----+-------------+------------+------------+------+
| id | select_type | table      | partitions | type |
+----+-------------+------------+------------+------+
|  1 | SIMPLE      | my_request | p0,p1      | ALL  |
+----+-------------+------------+------------+------+

but when I tried to check which record going to which partition, it gave me error. why?

mysql> select * from my_request partition (p0);
ERROR 1747 (HY000): PARTITION () clause on non partitioned table

(2) Some of my tables will have billions of records, and I (successfully) partitioned it BY RANGE into daily/weekly/monthly. However NDBCLUSTER only allow partition BY (LINEAR) KEY. Since the PRIMARY KEY is a UUID, I kind of lost how to partition the table.

Note: The partition is used not only for optimized query but also to dump and back-up (like dump the data older than 1y). I created an event and procedure to check from partition to do this. ty.

0

You can partition on a subset of the columns in the primary key. NDB currently doesn't support dropping a partition. So to get rid of old data one needs to use DELETE queries with proper WHERE clauses.

  • Yes, I also found that info you can't drop a partition in NDB. DELETE may lock the table and need to be planned very well. TRUNCATE on the other hand is fast and clean. So it is better to either TRUNCATE or DROP a table. Partition by KEY is practically useless and I still cannot find a good strategy on how to work with it in better way. This requirement lead to design my db with dynamically creating a table daily/weekly/monthly. But this really hold down the capability of select since data may span on several tables.. – nyoto arif Nov 23 '18 at 2:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.