CODEWITHSUNDEEP
mysqlpartitioning
DroidOS
DroidOS

Reputation: 8910

Partitioning a mySQL table

I am considering partitioning a mySQL table that has the potential to grow very big. The table as it stands goes like this

DROP TABLE IF EXISTS `uidlist`;
CREATE TABLE IF NOT EXISTS `uidlist` (
 `uid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, 
 `chcs` varchar(16) NOT NULL DEFAULT '',
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

where

I suspect that the best way to partition this table would be based on the first letter of the uid field. This would give

Partition 1
abcd1234,acbd1234,adbc1234...

Partition 2
bacd1234,bcad1234,bdac1234...

However, I have never done partitioning before I have no idea how to go about it. Is the partitioning scheme I have outlined possible? If so, how do I go about implementing it?

I would much appreciate any help with this.

Upvotes: 2

Views: 1727

Answers (3)

DroidOS
DroidOS

Reputation: 8910

I have upvoted both of the answers here since they both make useful points. @bbozo - a move to TokuDB is planned but there are constraints that stop it from being made right now.

I am going off the idea of partitioning the uidlist table as I had originally wanted to do. However, for the benefit of anyone who finds this thread whilst trying to do something similiar here is the "how to"

DROP TABLE IF EXISTS `uidlist`;
CREATE TABLE IF NOT EXISTS `uidlist` (
`uid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL ,
`chcs` varchar(16) NOT NULL DEFAULT '',
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii

PARTITION BY RANGE COLUMNS(uid)
(
 PARTITION p0 VALUES LESS THAN('f%'),
 PARTITION p1 VALUES LESS THAN('k%'),
 PARTITION p2 VALUES LESS THAN('p%'),
 PARTITION p3 VALUES LESS THAN('u%')
);

which creates four partitions.

I suspect that the long term solution here is to use a key-value store as suggested by @tmcallaghan rather than just stuffing everything into a MySQL table. I will probably post back in due course once I have established what would be the right way to accomplish that.

Upvotes: 0

tmcallaghan
tmcallaghan

Reputation: 1302

The main question you need to ask yourself before partitioning is "why". What is the goal you are trying to achieve by partitioning the table?

Since all the table's data will still existing on a single MySQL server and, I assume, new rows will be arriving in "random" order (meaning the partition they'll be inserted into), you won't gain much by partitioning. Your point select queries might be slightly faster, but not likely by much.

The main benefit I've seen using MySQL partitioning is for data that needs to be purged according to a set retention policy. Partitioning data by week or month makes it very easy to delete old data quickly.

It sounds more likely to me that you want to be sharding your data (spreading it across many servers), and since your data design as shown is really just key-value then I'd recommend looking at database solutions that include sharding as a feature.

Upvotes: 1

bbozo
bbozo

Reputation: 7311

Check out the manual for start :)

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

MySQL is pretty feature-rich when it comes to partitioning and choosing the correct strategy depends on your use case (can partitioning help your sequential scans?) and the way your data grows since you don't want any single partition to become too large to handle.

If your data will tend to grow over time somewhat steadily you might want to do a create-date based partitioning scheme so that (for example) all records generated in a single year end up in last partition and previous partitions are never written to - for this to happen you may have to introduce another column to regulate this, see http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html.

Added optimization benefit of this approach would be that you can have the most recent partition on a disk with fast writes (a solid state for example) and you can keep the older partitions on a cheaper disk with decent read speed.

Anyway, knowing more about your use case would help people give you more concrete answers (possibly including sql code)

EDIT, also, check out http://www.tokutek.com/products/tokudb-for-mysql/

Upvotes: 3

Related Questions