MySQL Forums
Forum List  »  Partitioning

Re: How to partition that table?
Posted by: Rick James
Date: July 01, 2011 11:25PM

The immediate problem is the use of "+" with a VARCHAR.

> I've a table which is getting slower when writing or reading by the unique key.
Let's see the actual queries, plus
SHOW TABLE STATUS

> Will partitioning solve this problem?
Probably not.

Read more of the PARTITION documentation. The partition key must be part of every UNIQUE key.

How much RAM do you have?
What is key_buffer_size?

I presume that "path" is rather random. Let's discuss what happens in the table as it stands (without PARTITIONing):
INSERTing a new row will append the record to the end of the data file, plus append to the index for PRIMARY KEY(id) (since it is AUTO_INCREMENT). But then it has to add an entry to the UNIQUE (`protocol`,`path`) -- this will (assuming the index is too big to be cached in the key_buffer) often need a disk hit.

Similarly, a SELECT on `protocol` & `path` will often hit the disk.

If you do SELECT ... WHERE id = ..., and the id value is jumping all around, again, there is likely to be a disk hit.

Now, let's add PARTITIONing, say on a hash of `path`.
INSERT -- First decide which partition to use. Then append the data to the data file, and `id` to the PK index. Finally, randomly add to the other index. Since you will be jumping between partitions all the time, you will be hitting essentially just as many blocks, hence lots of disk hits.
SELECT -- similar.

Since MyISAM has to jump from the Index to the Data, and InnoDB does not (for this type of table -- id:value), you might get better performance using InnoDB. On the other hand, the disk footprint is likely to be larger, thereby hurting the disk hit issue.

Options: ReplyQuote


Subject
Views
Written By
Posted
4705
June 30, 2011 07:49AM
Re: How to partition that table?
2005
July 01, 2011 11:25PM
1912
July 03, 2011 11:42AM


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.