I think that wal_segments are too low, try 30. 2011/9/5, Andy Colson <andy@xxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- ------------ pasman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
