Thread: 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
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
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
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@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.--ScottThanks,
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