Rather large LA - Mailing list pgsql-performance

From Richard Shaw
Subject Rather large LA
Date
Msg-id F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net
Whole thread Raw
Responses Re: Rather large LA  (Craig Ringer <ringerc@ringerc.id.au>)
Re: Rather large LA  (Andy Colson <andy@squeakycode.net>)
List pgsql-performance
Hi,

I have a database server that's part of a web stack and is experiencing prolonged load average spikes of up to 400+
whenthe db is restarted and first accessed by the other parts of the stack and has generally poor performance on even
simpleselect queries. 

There are 30 DBs in total on the server coming in at 226GB.  The one that's used the most is 67GB and there are another
29that come to 159GB.  

I'd really appreciate it if you could review my configurations below and make any suggestions that might help alleviate
theperformance issues.  I've been looking more into the shared buffers to the point of installing the contrib module to
checkwhat they're doing, possibly installing more RAM as the most used db @ 67GB might appreciate it, or moving the
mostused DB onto another set of disks, possible SSD. 


PostgreSQL 9.0.4
Pgbouncer 1.4.1

Linux 2.6.18-238.9.1.el5 #1 SMP Tue Apr 12 18:10:13 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

CentOS release 5.6 (Final)

4 x Intel Xeon-NehalemEX E7540-HexCore [2GHz] ( 24 physical cores )
32GB DDR3 RAM
1 x Adaptec 5805 Z  SATA/SAS RAID with battery backup
4 x Seagate Cheetah ST3300657SS 300GB 15RPM SAS drives in RAID 10
1 x 500GB 7200RPM SATA disk

Postgres and the OS reside on the same ex3 filesystem, whilst query and archive logging go onto the SATA disk which is
alsoext3. 


              name              |                                                  current_setting
                            

--------------------------------+-------------------------------------------------------------------------------------------------------------------
 version                        | PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704(Red Hat 4.1.2-48), 64-bit 
 archive_command                | tar jcf /disk1/db-wal/%f.tar.bz2 %p
 archive_mode                   | on
 autovacuum                     | off
 checkpoint_completion_target   | 0.9
 checkpoint_segments            | 10
 client_min_messages            | notice
 effective_cache_size           | 17192MB
 external_pid_file              | /var/run/postgresql/9-main.pid
 fsync                          | off
 full_page_writes               | on
 lc_collate                     | en_US.UTF-8
 lc_ctype                       | en_US.UTF-8
 listen_addresses               |
 log_checkpoints                | on
 log_destination                | stderr
 log_directory                  | /disk1/pg_log
 log_error_verbosity            | verbose
 log_filename                   | postgresql-%Y-%m-%d_%H%M%S.log
 log_line_prefix                | %m %u %h
 log_min_duration_statement     | 250ms
 log_min_error_statement        | error
 log_min_messages               | notice
 log_rotation_age               | 1d
 logging_collector              | on
 maintenance_work_mem           | 32MB
 max_connections                | 1000
 max_prepared_transactions      | 25
 max_stack_depth                | 4MB
 port                           | 6432
 server_encoding                | UTF8
 shared_buffers                 | 8GB
 superuser_reserved_connections | 3
 synchronous_commit             | on
 temp_buffers                   | 5120
 TimeZone                       | UTC
 unix_socket_directory          | /var/run/postgresql
 wal_buffers                    | 10MB
 wal_level                      | archive
 wal_sync_method                | fsync
 work_mem                       | 16MB


Pgbouncer config

[databases]
* = port=6432
[pgbouncer]
user=postgres
pidfile = /tmp/pgbouncer.pid
listen_addr =
listen_port = 5432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres
pool_mode = session
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
server_idle_timeout = 5
server_lifetime = 0
max_client_conn = 4096
default_pool_size = 100
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
client_idle_timeout = 30
reserve_pool_size = 800


Thanks in advance

Richard


pgsql-performance by date:

Previous
From: Craig Ringer
Date:
Subject: Re: Embedded VACUUM
Next
From: Craig Ringer
Date:
Subject: Re: Rather large LA