Re: Rather large LA

I think that wal_segments are too low, try 30.

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

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