slow database, queries accumulating - Mailing list pgsql-performance

Hi

We are experiencing consistent slowness on the database for one application. This is more a reporting type of application, heavy on the bytea data type usage (gets rendered into PDFs in the app server). A lot of queries, mostly selects and a few random updates, get accumulated on the server – with increasing volume of users on the application. Below is a snapshot of top, with about 80 selects and 3 or 4 updates. Things get better eventually if I cancel (SIGINT) some of the oldest queries. I also see a few instances of shared locks not being granted during this time…I don’t even see high iowait or memory starvation during these times, as indicated by top.

-bash-2.05b$ psql -c "select * from pg_locks;" dbname | grep f

          |          |    77922136 | 16761 | ShareLock        | f

We (development) are looking into the query optimization (explain analyze, indexes, etc), and my understanding is that the queries when run for explain analyze execute fast, but during busy times, they become quite slow, taking from a few seconds to a few minutes to execute. I do see in the log that almost all queries do have either ORDER BY, or GROUP BY, or DISTINCT. Does it hurt to up the sort_mem to 3MB or 4MB? Should I up the effective_cache_size to 5 or 6GB? The app is does not need a lot of connections on the database, I can reduce it down from 600.

Based on the description above and the configuration below does any thing appear bad in config? Is there anything I can try in the configuration to improve performance?

The database size is about 4GB.

This is PG 7.4.7, RHAS3.0 (u5), Local 4 spindle RAID10 (15KRPM), and logs on a separate set of drives, RAID10. 6650 server, 4 x XEON, 12GB RAM.

Vacuum is done every night, full vacuum done once a week.

I had increased the shared_buffers and sort_memory recently, which didn’t help.

Thanks,
Anjan

10:44:51  up 14 days, 13:38,  2 users,  load average: 0.98, 1.14, 1.12

264 processes: 257 sleeping, 7 running, 0 zombie, 0 stopped

CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle

           total   14.4%    0.0%    7.4%   0.0%     0.0%    0.0%   77.9%

           cpu00   15.7%    0.0%    5.7%   0.0%     0.1%    0.0%   78.2%

           cpu01   15.1%    0.0%    7.5%   0.0%     0.0%    0.1%   77.0%

           cpu02   10.5%    0.0%    5.9%   0.0%     0.0%    0.0%   83.4%

           cpu03    9.9%    0.0%    5.9%   0.0%     0.0%    0.0%   84.0%

           cpu04    7.9%    0.0%    3.7%   0.0%     0.0%    0.0%   88.2%

           cpu05   19.3%    0.0%   12.3%   0.0%     0.0%    0.0%   68.3%

           cpu06   20.5%    0.0%    9.5%   0.0%     0.0%    0.1%   69.7%

           cpu07   16.1%    0.0%    8.5%   0.0%     0.1%    0.3%   74.7%

Mem:  12081736k av, 7881972k used, 4199764k free,       0k shrd,   82372k buff

                   4823496k actv, 2066260k in_d,    2036k in_c

Swap: 4096532k av,       0k used, 4096532k free                 6888900k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND

16773 postgres  15   0  245M 245M  240M S     0.0  2.0   1:16   7 postmaster

16880 postgres  15   0  245M 245M  240M S     0.1  2.0   0:49   6 postmaster

16765 postgres  15   0  245M 245M  240M S     0.0  2.0   1:16   0 postmaster

16825 postgres  15   0  245M 245M  240M S     0.0  2.0   1:02   5 postmaster

16774 postgres  15   0  245M 245M  240M S     0.1  2.0   1:16   0 postmaster

16748 postgres  15   0  245M 245M  240M S     0.0  2.0   1:19   5 postmaster

16881 postgres  15   0  245M 245M  240M S     0.1  2.0   0:50   7 postmaster

16762 postgres  15   0  245M 245M  240M S     0.0  2.0   1:14   4 postmaster

…

…

max_connections = 600

shared_buffers = 30000  #=234MB, up from 21760=170MB min 16, at least max_connections*2, 8KB each

sort_mem = 2048         # min 64, size in KB

vacuum_mem = 32768              # up from 16384 min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each

#max_fsm_relations = 1000       # min 100, ~50 bytes each

#fsync = true                   # turns forced synchronization on or off

#wal_sync_method = fsync        # the default varies across platforms:

                                # fsync, fdatasync, open_sync, or open_datasync

#wal_buffers = 8                # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 125       # in logfile segments, min 1, 16MB each

checkpoint_timeout = 600        # range 30-3600, in seconds

#checkpoint_warning = 30        # 0 is off, in seconds

#commit_delay = 0               # range 0-100000, in microseconds

#commit_siblings = 5            # range 1-1000

# - Planner Method Enabling -

#enable_hashagg = true

#enable_hashjoin = true

#enable_indexscan = true

#enable_mergejoin = true

#enable_nestloop = true

#enable_seqscan = true

#enable_sort = true

#enable_tidscan = true

# - Planner Cost Constants -

effective_cache_size = 262144   # =2GB typically 8KB each

#random_page_cost = 4           # units are one sequential page fetch cost

#cpu_tuple_cost = 0.01          # (same)

#cpu_index_tuple_cost = 0.001   # (same)

#cpu_operator_cost = 0.0025     # (same)

# - Genetic Query Optimizer -

#geqo = true

#geqo_threshold = 11

#geqo_effort = 1

#geqo_generations = 0

#geqo_pool_size = 0             # default based on tables in statement,

                                # range 128-1024

#geqo_selection_bias = 2.0      # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10 # range 1-1000

#from_collapse_limit = 8

#join_collapse_limit = 8        # 1 disables collapsing of explicit JOINs