index build faster on 8G laptop than 30G server

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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