| About six months ago, our normally fast postgres server started having performance issues. Queries that should have been instant were taking up to 20 seconds to complete (like selects on the primary key of a table). Running the same query 4 times in a row would yield dramatically different results... 1.001 seconds, 5 seconds, 22 seconds, 0.01 seconds, to complete. At the time we upgraded the hardware and the performance problems went away. But I did not feel like we had solved the underlying problem. Now, six months later, the same thing is happening... and I'm kind of glad because now, I'd like to find out what the real issue is. I'm just starting to diagnose it so I don't know a lot yet, but what I do know, I'll share with you here in the hopes of starting off on the right track. I've already described the main symptom. Here are some other random observations: - The server log shows frequent "archived transaction log file" entries. Usually once every 10 minutes or so, but sometimes 2 or 3 per minute. - The server box seems otherwise to be responsive. CPU sits at about 90% idle. - When queries are especially slow, the server shows a big spike in read/write activity. - This morning I did a VACUUM ANALYZE. It seemed to help for 30 minutes or so, but then it was back to being slowish. I'd hate to schedule these because it feels more like a band-aid. For a long time we've been doing just fine with autovacuum, so why start scheduling vacuums now? Here's info about our configuration. Any advise/pointers would be much appreciated. Thanks! Computer: Mac Pro Dual Core Intel Operating System: Mac OS 10.4.7 Client Memory: 4GB RAM Data Drives: 3 drives in a software RAID (internal) Log/Backup Drive: 1 (the startup disk, internal) Postgres Version: 8.1.4 Data Size: 5.1 GB # of Tables: 60 Size of Tables: Most are under 100,000 records. A few are in the millions. Largest is 7058497. Average Number of Simultaneous Client Connections: 250 max_connections = 500 shared_buffers = 10000 work_mem = 2048 max_stack_depth = 6000 effective_cache_size = 30000 fsync = on wal_sync_method = fsync archive_command = 'cp -i %p /Users/postgres/officelink/wal_archive/%f </dev/null' max_fsm_pages = 150000 stats_start_collector = on stats_row_level = on log_min_duration_statement = 2000 log_line_prefix = '%t %h ' superuser_reserved_connections = 3 autovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_threshold = 150 autovacuum_vacuum_scale_factor = 0.00000001 autovacuum_analyze_scale_factor = 0.00000001 sudo pico /etc/rc sysctl -w kern.sysv.shmmax=4294967296 sysctl -w kern.sysv.shmall=1048576 sudo pico /etc/sysctl.conf kern.maxproc=2048 kern.maxprocperuid=800 kern.maxfiles=40000 kern.maxfilesperproc=30000 Processes: 470 total, 2 running, 4 stuck, 464 sleeping... 587 threads 13:34:50 Load Avg: 0.45, 0.34, 0.33 CPU usage: 5.1% user, 5.1% sys, 89.7% idle SharedLibs: num = 157, resident = 26.9M code, 3.29M data, 5.44M LinkEdit MemRegions: num = 15307, resident = 555M + 25.5M private, 282M shared PhysMem: 938M wired, 934M active, 2.13G inactive, 3.96G used, 43.1M free VM: 116G + 90.1M 1213436(0) pageins, 263418(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 29804 postgres 0.0% 0:03.24 1 9 27 1.27M 245M 175M 276M 29720 postgres 0.0% 0:01.89 1 9 27 1.25M 245M 125M 276M 29714 postgres 0.0% 0:03.70 1 10 27 1.30M 245M 215M 276M 29711 postgres 0.0% 0:01.38 1 10 27 1.21M 245M 107M 276M 29707 postgres 0.0% 0:01.27 1 9 27 1.16M 245M 78.2M 276M 29578 postgres 0.0% 0:01.33 1 9 27 1.16M 245M 67.8M 276M 29556 postgres 0.0% 0:00.39 1 9 27 1.09M 245M 91.8M 276M 29494 postgres 0.0% 0:00.19 1 9 27 1.05M 245M 26.5M 276M 29464 postgres 0.0% 0:01.98 1 9 27 1.16M 245M 88.8M 276M 29425 postgres 0.0% 0:01.61 1 9 27 1.17M 245M 112M 276M 29406 postgres 0.0% 0:01.42 1 9 27 1.15M 245M 118M 276M 29405 postgres 0.0% 0:00.13 1 9 26 924K 245M 17.9M 276M 29401 postgres 0.0% 0:00.98 1 10 27 1.13M 245M 84.4M 276M 29400 postgres 0.0% 0:00.90 1 10 27 1.14M 245M 78.4M 276M 29394 postgres 0.0% 0:01.56 1 10 27 1.17M 245M 111M 276M |
