From: | Bill Ross <ross(at)cgl(dot)ucsf(dot)edu> |
---|---|
To: | Scott Mead <scottm(at)openscg(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: index build faster on 8G laptop than 30G server |
Date: | 2016-04-19 22:01:18 |
Message-ID: | 5716AAAE.2090800@cgl.ucsf.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks for the fast response!
Server was completely idle except the one client (and one doing a slow
update that I forgot). Updating maintenance_work_mem to 8G I see more
memory now in use:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4531 ec2-user 20 0 10.1g 3.4g 1.7g R 99.8 11.4 2:02.17 postgres
When restarting postgres I noticed that I had a background process
trying to update the table, which might have locked it and would also
explain why postgres was running at 100%.
It now takes ~5min to build the index.
Bill
On 4/19/16 2:36 PM, Scott Mead wrote:
>
>
> On Tue, Apr 19, 2016 at 5:28 PM, Bill Ross <ross(at)cgl(dot)ucsf(dot)edu
> <mailto:ross(at)cgl(dot)ucsf(dot)edu>> wrote:
>
> I've been running an index build for almost an hour on my 30G
> server that takes ~ 20 mins on my puny old macbook.
>
> It seems like I've tuned all I can.. what am I missing?
>
>
> Concurrent traffic on the server ? Locks / conflicts with running traffic?
>
> From a parameter perspective, look at maintenance_work_mem.
>
> --Scott
>
> Thanks,
> Bill
>
> Records to index: 33305041
>
> --- Server:
>
> PostgreSQL 9.2.15 on x86_64-redhat-linux-gnu, compiled by gcc
> (GCC) 4.8.3 20140
> 911 (Red Hat 4.8.3-9), 64-bit
>
> shared_buffers = 8GB # min 128kB
> temp_buffers = 2GB # min 800kB
> work_mem = 8GB # min 64kB
> checkpoint_segments = 256 # in logfile segments, min 1,
> 16MB each
> seq_page_cost = 1.0 # measured on an arbitrary scale
> random_page_cost = 1.0 # same scale as above
> effective_cache_size = 20GB
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
> COMMAND
> 4069 ec2-user 20 0 8596m 1.7g 1.7g R 99.8 5.6 67:48.36 postgres
>
> Macbook:
> PostgreSQL 9.4.4 on x86_64-apple-darwin14.3.0, compiled by Apple
> LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit
>
> shared_buffers = 2048MB # min 128kB
> temp_buffers = 32MB # min 800kB
> work_mem = 8MB # min 64kB
> dynamic_shared_memory_type = posix # the default is the first
> option
> checkpoint_segments = 32 # in logfile segments, min 1, 16MB
> each
>
> PID COMMAND %CPU TIME #TH #WQ #PORTS MEM PURG
> CMPRS PGRP
> 52883 postgres 91.0 02:16:14 1/1 0 7 1427M+ 0B
> 622M- 52883
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-04-19 22:23:49 | Function PostgreSQL 9.2 |
Previous Message | Peter Devoy | 2016-04-19 21:37:00 | Re: index build faster on 8G laptop than 30G server |