Re: Building Indexes across partitioned tables longer each day
Posted by:
Rick James
Date: December 22, 2009 10:17PM
Think about what happens when a row is inserted (MyISAM)...
* Insert the data at the end of the .MYD (quick, easy, efficient, effectly no disk I/O)
* For each index, do: Drill down (BTree-wise) in the .MYI file to find the block (1KB) where the index entry needs to be added. The upper layers of the BTree are likely to be cached (no I/O), but for huge tables, the bottom layer (or two) are likely to require reads from disk. And, eventually, a write back to disk.
That index description applies to "random" keys. Some keys are as well behaved as the data. Examples: AUTO_INCREMENT and DATETIME/TIMESTAMP (if rows are coming in chronologically). In these cases, the leaf node of the index BTree is almost always the same, last, one in the index.
Partitioning has very little impact on the above discussion, unless
* You have enough partitions (watch out--1024 limit), and
* You fill the partitions one at a time, and
* Most the index blocks of ONE partition will fit in key_buffer_size (see the size of one .MYI)
Splitting the files across drives is almost useless during the bulk insert. (I am assuming you are filling the partitions one at a time.) The MYD has very few writes. The MYI is getting lots of reads/writes -- but that's hitting one drive.
Better is to use RAID striping. This way, the accesses are more evenly spread across the multiple drives all the time.
Another hardware speedup is a RAID controller with Battery Backed Write Cache. This makes the writes feel like they are instantaneous.
The best way to deal with large datasets is to insert them with only an AUTO_INCREMENT. Then build summary table(s) with all the indexes you need.