Обсуждение: index build faster on 8G laptop than 30G server

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?

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

On 4/19/2016 2:28 PM, Bill Ross 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?



maintenance_work_mem is the main thing that affects index building
times, I didn't see that in your list of tuning parameters.    I
generally set it to 1GB.

--
john r pierce, recycling bits in santa cruz

On Tue, Apr 19, 2016 at 2:30 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 4/19/2016 2:28 PM, Bill Ross 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?
>
>
>
>
> maintenance_work_mem is the main thing that affects index building times, I
> didn't see that in your list of tuning parameters.    I generally set it to
> 1GB.
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --

I've got some indexes that take well over 7 hours to create on a
256GB machine. Last time I posted for help, I was told they have yet
to really fix the tuple issue and that using a tiny number in work_mem
helped allieviate some of the issues. Still takes a long time but i'm
at 512MB, no slower than 7GB+

Here is what was told to me before

"Yes. There is a hard limit on the number of tuples than can be sorted
in memory prior to PostgreSQL 9.4. It's also the case that very large
work_mem or maintenance_work_mem settings are unlikely to help unless
they result in a fully internal sort.

There is evidence that the heap that tuple sorting uses benefits from
*lower* settings. Sometimes as low as 64MB.

We're working to make this better in 9.6."

Tory

I see you are comparing 9.2 and 9.4 -- if you were not already aware
there is a kernel restriction on shared memory on Linux systems which,
if I recall correctly, must be adjusted in versions before 9.3...

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers

Not saying it's the answer but maybe another thing to look at...

All the best


Peter

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