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

Bill Ross <ross@xxxxxxxxxxxx> · Tue, 19 Apr 2016 15:01:18 -0700



    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@xxxxxxxxxxxx>
                                  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@xxxxxxxxxxxxxx)

                  To make changes to your subscription:

                  http://www.postgresql.org/mailpref/pgsql-general