Re: Partition by threadid or postid?
Posted by:
Rick James
Date: January 12, 2011 10:29AM
Neither.
Partitioning on the first (or only) field in the PRIMARY KEY provides little benefit.
PARTITION requires that the partition key be in all UNIQUE (and PRIMARY) KEYs. Partitioning on threadid would violate this rule.
All 4 of your queries are happy to run against a non partitioned table with the indexes you indicated.
More detail:
1, SELECT postid FROM post WHERE threadid = $threadid ...
This will drill down the BTree index on postid. With 11M rows, the index will be about 4 levels deep. Even on a cold system, that is only 4 disk hits, plus the random fetch into the data. 0-5 disk hits; I would expect 1 is typical, hence 10ms.
Partitioning would not speed this up.
2, SELECT * FROM post WHERE postid = $postid
3, UPDATE post SET ... WHERE postid = $postid
Same logic as for #1. The update will (sooner or later) necessitate an extra write; this will not impact latency (still typically 10ms) but may impact throughput -- sustained update throughput: estimate 50 updates/sec.
Partitioning would not speed this up.
4, INSERT INTO post ...
This will
* append to data file (well cached)
* append to PRIMARY KEY (well cached)
* possibly random hit to update each of the other KEYs. (Can't predict disk hits without knowing distribution of keys and inserts.) Each is a read-modify-write of one block, so 0-2 actual reads and 0-2 actual writes.
Partitioning might speed things up a little, but only if you can arrange for the INSERTs to always be concentrated in one partition, not jumping between partitions. (This could improve cacheability of index blocks some.)
Other comments...
If you have lots of INSERTs, recommend "batching" them:
INSERT INTO x (...) VALUES (...), (...), ...;
Batches of 100 is about optimal, and may give you 10x improvement in latency.
key_buffer_size should be set to about 20% of available RAM. (Often this oversight is the key to MyISAM performance problem.)
`ipaddress` char(15) NOT NULL DEFAULT '',
IPv6 is coming soon! Change it to VARBINARY(39).
"Vertical partitioning" may be beneficial if this is most of the rowsize:
`pagetext` mediumtext,
Move it to a parallel table with
`postid` int(10) unsigned NOT NULL
as the PRIMARY KEY. This will make the main table much smaller, hence more cacheable. JOIN to the new table only when you need pagetext.