Hi,
I have been running PG for quite a while now.
Suddenly, the postmaster either hogs up memory or just croaks and
doesn't respond. The write process has become horribly slow.
Could it be that my data has grown so large that it's becoming an
issue? Even connecting to PGSQL as postgres user in SSH is way too
slow.
I am on a 4GB server with Apache 2.2.6 and PgSql 8.2.3. The same
server runs Apache, PgSQL, and MySQL. The last is not used for much,
just some simple logging and such. It seldom shows up in "TOP" output.
So the main heavy lifters are Apache and PgSQL.
In my PG_LOG files, I have many messages like these:
LOG: could not send data to client: Broken pipe
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
What are these things? My conf file settings (which were working fine
until recently!) are as follows:
max_connections = 150
shared_buffers = 330MB
effective_cache_size = 512000
max_fsm_relations = 100
max_fsm_pages = 300000
work_mem = 100MB
temp_buffers = 4096
authentication_timeout = 10s
ssl = off
autovacuum = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay = 20
autovacuum_naptime = 10
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_scale_factor = 0.01
wal_buffers = 128
checkpoint_segments = 64
checkpoint_timeout = 900
fsync = on
maintenance_work_mem = 512MB
------------------------------------------------
1. VMSTAT -S
~ > vmstat -s
4148844 total memory
3771392 used memory
3232328 active memory
265652 inactive memory
377452 free memory
75044 buffer memory
2890740 swap cache
2096440 total swap
6436 used swap
2090004 free swap
24821214 non-nice user cpu ticks
229660 nice user cpu ticks
14153129 system cpu ticks
566198940 idle cpu ticks
22918768 IO-wait cpu ticks
75387 IRQ cpu ticks
0 softirq cpu ticks
153625372 pages paged in
287656751 pages paged out
175855 pages swapped in
859894 pages swapped out
1844498357 interrupts
889200515 CPU context switches
1201419806 boot time
15735402 forks
------------------------------------------------
2. FREE -M
total used free shared buffers cached
Mem: 4051 4025 26 0 74 2823
-/+ buffers/cache: 1127 2924
Swap: 2047 6 2041
------------------------------------------------
3. VACUUM ANALYE OUTPUT
This is the last few lines of this output:
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "clients": found 1 removable, 3031359 nonremovable row versions
in 199194 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2624366 unused item pointers.
97747 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 235.21 sec.
INFO: vacuuming "pg_toast.pg_toast_50002"
INFO: index "pg_toast_50002_index" now contains 20 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_50002": found 0 removable, 20 nonremovable row
versions in 7 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 12 unused item pointers.
6 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: analyzing "public.clients"
INFO: "clients": scanned 7500 of 199194 pages, containing 114197 live
rows and 1 dead rows; 7500 rows in sample, 3032981 estimated total
rows
INFO: free space map contains 98498 pages in 25 relations
DETAIL: A total of 98720 page slots are in use (including overhead).
98720 page slots are required to track all free space.
Current limits are: 300000 page slots, 100 relations, using 1766 kB.
------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
