Hi. I've been patient. PG is 9.0.17, updated via Yum yesterday.
One of my large tables (101 GB on disk, about 1.1 billion rows) used
to take too long to vacuum. Not sure if it's an index corruption
issue. But I tried VACUUM FULL ANALYZE as recommended in another
thread yesterday, which took 5 hours on the two times I tried, without
finishing.
Now the REINDEX TABLE has taken over 6 hours as I decided to be
patient and just let something finish. Not sure this is normal though!
How do production level DBAs do this if it takes so long?
If I open another SSH window to my server and try "select * from
pg_stats_activity" it just hangs there, as the REINDEX I presume is
taking up all the memory? I basically can't do anything else on this
server.
Just in case it helps, a segment of my postgresql.conf is below. Would
appreciate any tips on what I can do.
(I did a pg_dump of just this table, which also took about 2 hours,
then I renamed the original table in the database, and tried to
pg_restore just the table, but it gave me an error message about the
archive being in the wrong format !!! So REINDEX or something like it
seems to be the only idea?)
Thanks for any help!
PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf
and TOP output during the running of the REINDEX are below..
----POSTGRESQL.CONF-----
max_connections = 180
superuser_reserved_connections = 5
shared_buffers = 512MB
effective_cache_size = 1200MB
temp_buffers = 32MB
maintenance_work_mem = 320MB
work_mem = 128MB
wal_buffers = 20MB
fsync = on
checkpoint_segments = 128
checkpoint_timeout = 1000
enable_indexscan = on
# AUTOVAC
autovacuum = on
autovacuum_max_workers = 5 # max number of autovacuum subprocesses
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size
before vacuum
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 350
...
------TOP OUTPUT (db name changed for privacy, with the word "MYDOMAIN") -------
top - 21:18:51 up 22 days, 7:43, 2 users, load average: 1.20, 1.17, 1.18
Tasks: 214 total, 3 running, 211 sleeping, 0 stopped, 0 zombie
Cpu(s): 25.1%us, 1.6%sy, 0.0%ni, 71.9%id, 1.1%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 4046644k total, 4022324k used, 24320k free, 9880k buffers
Swap: 2096440k total, 177144k used, 1919296k free, 2526536k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21044 postgres 25 0 1102m 513m 76m R 97.7 13.0 432:03.46 postgres:
MYDOMAIN_MYDOMAIN MYDOMAIN [local] REINDEX
8812 root 18 0 1403m 53m 3344 S 2.3 1.4 377:33.38
./jre/bin/java -Djava.compiler=NONE -cp /usr/StorMan/RaidMan.jar com.
8319 named 24 0 317m 37m 1860 S 1.3 0.9 319:11.26
/usr/sbin/named -u named -4 -t /var/named/chroot
14184 nobody 15 0 266m 15m 5156 S 1.0 0.4 4:13.43 nginx:
worker process
14181 nobody 15 0 279m 34m 5160 S 0.7 0.9 4:13.93 nginx:
worker process
30285 root 15 0 12760 1188 820 R 0.7 0.0 0:00.03 top
282 root 10 -5 0 0 0 S 0.3 0.0 184:37.48 [kswapd0]
25093 nobody 16 0 334m 15m 5124 S 0.3 0.4 0:01.00
/usr/local/apache/bin/httpd -k restart -DSSL
25095 nobody 15 0 334m 15m 5256 S 0.3 0.4 0:00.94
/usr/local/apache/bin/httpd -k restart -DSSL
25102 nobody 15 0 334m 15m 5120 S 0.3 0.4 0:00.93
/usr/local/apache/bin/httpd -k restart -DSSL
25106 nobody 15 0 334m 15m 5416 S 0.3 0.4 0:00.99
/usr/local/apache/bin/httpd -k restart -DSSL
25109 nobody 15 0 334m 15m 5424 S 0.3 0.4 0:00.94
/usr/local/apache/bin/httpd -k restart -DSSL
25113 nobody 16 0 334m 15m 4980 S 0.3 0.4 0:00.93
/usr/local/apache/bin/httpd -k restart -DSSL
25115 nobody 16 0 334m 15m 5192 S 0.3 0.4 0:00.95
/usr/local/apache/bin/httpd -k restart -DSSL
25117 nobody 16 0 334m 15m 4988 S 0.3 0.4 0:00.97
/usr/local/apache/bin/httpd -k restart -DSSL
25119 nobody 16 0 334m 15m 5028 S 0.3 0.4 0:00.96
/usr/local/apache/bin/httpd -k restart -DSSL
31759 root 15 0 0 0 0 S 0.3 0.0 0:35.37 [pdflush]
1 root 15 0 10368 592 556 S 0.0 0.0 0:04.29 init [3]
2 root RT -5 0 0 0 S 0.0 0.0 0:06.24
[migration/0]
3 root 34 19 0 0 0 S 0.0 0.0 0:08.72
[ksoftirqd/0]
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00
[watchdog/0]
5 root RT -5 0 0 0 S 0.0 0.0 0:05.27
[migration/1]
6 root 34 19 0 0 0 S 0.0 0.0 3:49.89
[ksoftirqd/1]
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/1]
