Message-ID: <4E64D0FE.6020704@squeakycode.net>
Date: Mon, 05 Sep 2011 08:39:10 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.18) Gecko/20110707 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Richard Shaw <richard@aggress.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Rather large LA
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
In-Reply-To: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/05/2011 05:28 AM, Richard Shaw wrote:
>
> Hi,
>
> I have a database server that's part of a web stack and is experiencing prolonged load average spikes of up to 400+ when the db is restarted and first accessed by the other parts of the stack and has generally poor performance on even simple select queries.
>

Is the slowness new?  Or has it always been a bit slow?  Have you checked for bloat on your tables/indexes?

When you start up, does it peg a cpu or sit around doing IO?

Have you reviewed the server logs?


autovacuum                     | off

Why?  I assume that's a problem.

fsync                          | off

Seriously?


-Andy


> 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 29 that come to 159GB.
>
> I'd really appreciate it if you could review my configurations below and make any suggestions that might help alleviate the performance issues.  I've been looking more into the shared buffers to the point of installing the contrib module to check what they're doing, possibly installing more RAM as the most used db @ 67GB might appreciate it, or moving the most used 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 also ext3.
>
>
>                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
>
>