1

In my system's database we have a table which stores 3 fields: id int, name(varchar) and description (mediumtext). The table is by far the biggest in our database and the queries on it are exclusively by id, which is indexed.

The problem is, we are unable to delete or archive any data and in addition, there is no way to partition the table in a way that will make sense. Currently the performance is good (we're talking about a 7.5 GB table) but I'm afraid we'll encounter some issues in the future.

A colleague suggested me to experiment with hash partitioning, which should divide the table to equally divided groups of data and keep the index level low.

Now my questions regarding this topic are:

  • Is hash partitioning a good solution to solve the issue I'm talking about, or the index is enough considering the queries are only by id

  • In case that I decided to go for hash partitioning, how many partitions should I go for? To remark, the table is currently 7.5 GB and expected to keep growing.

4
  • Partitioning usually does not help with select performance. The index is stored as a b-tree so accessing a specific row is O(log(n)) operation. If you split the table to as many partitions as to remove one "level" of the tree, then you get O(log(n)-1)+(operations to find the right partition by hash) which I would guess is the same or worse than the original. The b-tree is really effective for id access.
    – jkavalik
    Nov 20 '15 at 12:15
  • Even if a table is expected to grow constantly? What other solution you can think about? Thank you very much for your answer
    – user69153
    Nov 20 '15 at 13:53
  • If you always access records by the ID, then there is probably no single-server solution (and I am not sure you need any - we have tables of millions and some even over a billion of records and PK or other unique index access is really fast). You might use some sharding, (maybe over multiple servers) but it does not seem like you can find the splitting key in this table - you might need some info from tables referencing it (like distributing "customers" or what other uses these record across servers).
    – jkavalik
    Nov 20 '15 at 13:58
  • PARTITIONS can help, only if table split by Your queries logic For example: - if You select/update data just recently added - it may help - but if You select/update data from any parts of table (and can not manage this) - partitions not help You - same if You run query by other column (not used for partitioning) it can and reduce speed
    – a_vlad
    Nov 21 '15 at 12:53
1

jkavalik is right.

I'll say it more strongly: PARTITION BY HASH is possibly useless in any situation for enhancing performance.

Your table is referenced only by the PRIMARY KEY(id), correct? And it is InnoDB, correct? If you have a million rows, the BTree that contains the PK and all the data is about 3 levels deep. For a _trillion_rows, it will be about 6 levels.

The million-fold increase in data size (from 1M to 1T) might slow down a "point query" by a factor of 2. That's all.

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.