From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: FILLFACTOR and increasing index |
Date: | 2011-06-12 22:18:29 |
Message-ID: | 4DF53B35.8020700@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-06-12 22:37:19 | Re: Out of tree build issue |
Previous Message | Щепкин Александр | 2011-06-12 21:59:24 | Reinstalling |