Обсуждение: how small to split a table?
I've got one logging table that is over 330 million rows to store 6 months' worth of data. It consists of two integers and a 4-character long string. I have one primary key which is the two integers, and an additional index on the second integer. I'm planning to use inheritance to split the table into a bunch of smaller ones by using a modulo function on one of the integers on which we scan often. My question is how small to make each inherited piece? If I do modulo 10, then each sub-table will be between 32 and 34 million rows today based on current distribution. If I expect to increase traffic 2 times over the next year (thus doubling my logs) what would you recommend?
Вложения
> My question is how small to make each inherited piece? If I do > modulo 10, then each sub-table will be between 32 and 34 million > rows today based on current distribution. You might try this with various sizes. I did some testing lateley and found out that insert performance - even if only inserting into one partition through the master table abould halfed the speed with 4 partitions and made a 50% increase for 2 partitions. Please note: this is not representative in any kind! So while it might be cool in your case to have e.g. one partition per month, this might slow inserts down too much, so that a different number of partitions could be better. The same applies for queries as well (here perhaps in the other direction). -- Heiko W.Rupp heiko.rupp@redhat.com, http://www.dpunkt.de/buch/3-89864-429-4.html
On Mar 20, 2007, at 11:20 AM, Heiko W.Rupp wrote: > partition through the master > table abould halfed the speed with 4 partitions and made a 50% > increase for 2 partitions. > Please note: this is not representative in any kind! I fully intend to build knowledge of the partitions into the insert part of the logging. Only the queries which do joins on the current big table would use the master name. Everything else can be trained to go directly to the proper subtable. Thanks for your note. It implies to me I'm making the right choice to build that knowledge into the system.