Re: Rather large LA

I think that wal_segments are too low, try 30.

2011/9/5, Andy Colson <andy@squeakycode.net>:
> 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
>>
>>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


--
------------
pasman