Обсуждение: Index creation and maintenance_work_mem
Besides maintenance_work_mem, what else can be changed to improve index creation? I just did two tests. One with maintenance_work_mem=128MB and another with maintenance_work_mem=1GB. Out of 3 single column index, 2 took slightly longer with the higher value and a third took almost the same. 12GB of ram in the machine. Redhat 4 revision 6. Postgesql 8.3.3. temp_buffers = 64MB work_mem = 96MB checkpoint_segments = 256 checkpoint_timeout = 10min Indexing 60 million rows. Tests run from a script and nothing else was running on the machine during the tests. maintenance_work_mem = 128MB CREATE INDEX Time: 449626.651 ms 7.4 minutes CREATE INDEX Time: 313004.025 ms 5.2 minutes CREATE INDEX Time: 3077382.941 ms 51.2 minutes maintenance_work_mem = 1GB CREATE INDEX Time: 497358.902 ms 8.2 minutes CREATE INDEX Time: 312316.953 ms 5.2 minutes CREATE INDEX Time: 3236472.630 ms 53.9
On Tuesday 29 July 2008, "Francisco Reyes" <lists@stringsutils.com> wrote: > Besides maintenance_work_mem, what else can be changed to improve index > creation? > Depends where the bottleneck is. 1 CPU core 100% user/system = buy faster CPU cores. System in I/O wait = upgrade disk channel. -- Alan
On 2:53 pm 07/29/08 Alan Hodgson <ahodgson@simkin.ca> wrote: > --sar 2 30 Linux 2.6.9-42.ELsmp (trans03) 07/29/2008 12:58:09 PM CPU %user %nice %system %iowait %idle 12:58:11 PM all 12.44 0.00 0.06 0.00 87.50 12:58:13 PM all 12.44 0.00 0.00 0.06 87.50 12:58:15 PM all 12.49 0.00 0.06 0.00 87.45 12:58:17 PM all 12.43 0.00 0.06 1.62 85.88 12:58:19 PM all 12.38 0.00 0.06 0.00 87.55 12:58:21 PM all 12.43 0.00 0.12 0.00 87.45 12:58:23 PM all 12.50 0.00 0.00 0.00 87.50 12:58:25 PM all 12.42 0.00 0.12 0.00 87.45 Quadcore machine. Not using not even 100% of one core. Disk subsystem doesn't seem to be issue (no iowait). Based on a few links I read I thought that, holding everything else constant, increasing maintenance_work_mem would have helped.
On Tuesday 29 July 2008, "Francisco Reyes" <lists@stringsutils.com> wrote: > On 2:53 pm 07/29/08 Alan Hodgson <ahodgson@simkin.ca> wrote: > > --sar 2 30 > > Linux 2.6.9-42.ELsmp (trans03) 07/29/2008 > > 12:58:09 PM CPU %user %nice %system %iowait %idle > 12:58:11 PM all 12.44 0.00 0.06 0.00 87.50 > 12:58:13 PM all 12.44 0.00 0.00 0.06 87.50 > 12:58:15 PM all 12.49 0.00 0.06 0.00 87.45 > 12:58:17 PM all 12.43 0.00 0.06 1.62 85.88 > 12:58:19 PM all 12.38 0.00 0.06 0.00 87.55 > 12:58:21 PM all 12.43 0.00 0.12 0.00 87.45 > 12:58:23 PM all 12.50 0.00 0.00 0.00 87.50 > 12:58:25 PM all 12.42 0.00 0.12 0.00 87.45 > > Quadcore machine. Not using not even 100% of one core. > Disk subsystem doesn't seem to be issue (no iowait). That looks an awful lot like pegged 1/8th usage to me ... are you sure you only have 4 cores? Hyper-threading? > > Based on a few links I read I thought that, holding everything else > constant, increasing maintenance_work_mem would have helped. Yeah, but it didn't. -- Alan
On 3:19 pm 07/29/08 Alan Hodgson <ahodgson@simkin.ca> wrote: > sure you only have 4 cores? Hyper-threading? It seems Hyperthreading is on in that machine. Going to see if I can have it turned off.
On Tue, 29 Jul 2008, Francisco Reyes wrote: > Besides maintenance_work_mem, what else can be changed to improve index > creation? Very large values there haven't been all that helpful for me. I've gotten better results in this area giving more of the unused memory to shared_buffers (which you didn't mention your setting for) rather than having a gigantic setting for maintenance_work_mem. Last time I went through a similar exercise to yours, but with a much larger data set, I ran a baseline test at maintenance_work_mem=64MB and larger values didn't seem to improve anything significantly over that. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD