Dne 12.6.2011 21:14, Boszormenyi Zoltan napsal(a):
> We recently had a testcase for exercising FILLFACTOR on indexes.
> Several (15+) GB raw data arrives daily and must be imported into
> the database for analytic purposes, the table is heavily partitioned
> and each partition has 5 or 6 indexes. The importer is heavily threaded
> and uses COPY to insert the data. This is strictly an INSERT-only scenario,
> the analysis comes later. This is where FILLFACTOR=70 helped to
> reduce the index contention.
>
> With the default 90% (and 100% on the table itself) when a lot of data
> arrived in burst that were in the same time interval so 4-8 threads tried
> to push data into the same partition, individual data chunks (about 10-15MB
> each, about 63500 pieces) were imported in about 1.5-2.5 minutes, with
> a few spikes over 4 minutes. This was the result of a few days long
> network outage, the data collectors sent their backlogs.
>
> When all indexes were re-created with FILLFACTOR=70, the import times
> went down considerably. Out of about 63000 raw data chunks, there were
> only 6 or 8 where the import time fell in the 10-25 seconds range, the rest
> of them were imported under 10 seconds and the majority of them (65%)
> were imported under 3 seconds. The system has 24 cores, so we can use
> 8 of them dedicated to importing. This almost 1TB data was imported
> in about 3.5-4 hours after the FILLFACTOR change. Without it, it didn't
> finish in 2 days.
>
> Best regards,
> Zoltán Böszörményi
Great, thanks for this info! So I guess it basically confirms my theory
that lowering fillfactor only helps with a lot of processes accessing
the same pages. Can you report how did the index size change?
regards
Tomas
