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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
