Re: index build faster on 8G laptop than 30G server

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 Tue, Apr 19, 2016 at 5:28 PM, Bill Ross <ross@cgl.ucsf.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general