Skip site navigation (1) Skip section navigation (2)

Getting Slow

From: Joe Lester <joe_lester(at)sweetwater(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Getting Slow
Date: 2007-06-07 17:48:43
Message-ID: 14D118F0-20F6-4096-93D7-E6CB2893E895@sweetwater.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Andrew SullivanDate: 2007-06-07 18:37:48
Subject: Re: Getting Slow
Previous:From: Steven FlattDate: 2007-06-07 17:11:44
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group