On Fri, Feb 1, 2013 at 11:01 AM, Pavan Deolasee <pavan.deolasee@xxxxxxxxx> wrote:
I'm pretty sure the io is from the autovacuum on master table because it's last_autovacuum stats update almost every minute and iotop shows something like:
Total DISK READ: 5.80 M/s | Total DISK WRITE: 115.85 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
7681 be/4 postgres 5.93 M/s 0.00 B/s 0.00 % 63.62 % postgres: autovacuum worker process fleet
15837 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 4.98 % postgres: fleet fleet 127.0.0.1(53537) idle
16683 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 2.68 % postgres: fleet fleet 127.0.0.1(53978) idle
18599 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 1.25 % postgres: fleet fleet 127.0.0.1(59529) idle
15608 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 0.04 % postgres: fleet fleet 127.0.0.1(53349) idle
10253 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(50639) idle
16524 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(53939) idle
10248 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(50634) idle
18606 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(59535) idle
14849 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(52832) idle
30308 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: wal writer process
21528 be/4 www-data 0.00 B/s 3.86 K/s 0.00 % 0.00 % nginx: worker process
30307 be/4 postgres 0.00 B/s 38.62 K/s 0.00 % 0.00 % postgres: writer process
I just checked and our config only sets:
max_connections = 210
superuser_reserved_connections = 10
wal_buffers = 8MB
checkpoint_segments = 16
checkpoint_completion_target = 0.9
random_page_cost = 3.0
shared_buffers = 9GB
effective_cache_size = 28GB
work_mem = 88MB
maintenance_work_mem = 1GB
We're running on a server with 40GB RAM and a RAID 10 over 6 10k HDDs.
Stats show only 3 tables get frequently autovacuumed
Do you know for sure that its the master table that generating all the
vacuum traffic ? What about the partition tables ? Do they get any
updates/deletes ? It might be useful if you could turn autovacuum
logging ON and see which tables are getting vacuumed and correlate
that with the increased IO activity that you're seeing.
Did you change any other parameters like checkpoint timeout/segments
etc ? It might be worthwhile to log checkpoint activities as well to
be doubly sure.
Thanks,
Pavan
I'm pretty sure the io is from the autovacuum on master table because it's last_autovacuum stats update almost every minute and iotop shows something like:
Total DISK READ: 5.80 M/s | Total DISK WRITE: 115.85 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
7681 be/4 postgres 5.93 M/s 0.00 B/s 0.00 % 63.62 % postgres: autovacuum worker process fleet
15837 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 4.98 % postgres: fleet fleet 127.0.0.1(53537) idle
16683 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 2.68 % postgres: fleet fleet 127.0.0.1(53978) idle
18599 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 1.25 % postgres: fleet fleet 127.0.0.1(59529) idle
15608 be/4 postgres 0.00 B/s 23.17 K/s 0.00 % 0.04 % postgres: fleet fleet 127.0.0.1(53349) idle
10253 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(50639) idle
16524 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(53939) idle
10248 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(50634) idle
18606 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(59535) idle
14849 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: fleet fleet 127.0.0.1(52832) idle
30308 be/4 postgres 0.00 B/s 7.72 K/s 0.00 % 0.01 % postgres: wal writer process
21528 be/4 www-data 0.00 B/s 3.86 K/s 0.00 % 0.00 % nginx: worker process
30307 be/4 postgres 0.00 B/s 38.62 K/s 0.00 % 0.00 % postgres: writer process
I just checked and our config only sets:
max_connections = 210
superuser_reserved_connections = 10
wal_buffers = 8MB
checkpoint_segments = 16
checkpoint_completion_target = 0.9
random_page_cost = 3.0
shared_buffers = 9GB
effective_cache_size = 28GB
work_mem = 88MB
maintenance_work_mem = 1GB
We're running on a server with 40GB RAM and a RAID 10 over 6 10k HDDs.
Stats show only 3 tables get frequently autovacuumed
| Table Name | Tuples inserted | Tuples updated | Tuples deleted | Tuples HOT updated | Live Tuples | Dead Tuples | Last vacuum | Last autovacuum | Last analyze | Last autoanalyze | Vacuum counter | Autovacuum counter | Analyze counter | Autoanalyze counter | Size |
| vehicle_position | 4689127 | 0 | 4689127 | 0 | 0 | 1985 | 2013-02-01 11:46:46.441227+02 | 2013-01-31 14:48:00.775864+02 | 2013-02-01 11:46:46.494234+02 | 0 | 3761 | 1 | 3754 | 4016 kB | |
| vehicle | 2124 | 934640 | 0 | 924003 | 2124 | 390 | 2013-02-01 11:47:46.067695+02 | 2013-02-01 11:47:46.325444+02 | 0 | 1293 | 0 | 2038 | 2448 kB | ||
| input_current | 10032 | 1649206 | 0 | 1635902 | 10032 | 1728 | 2013-02-01 11:45:46.0681+02 | 2013-02-01 11:45:46.215048+02 | 0 | 442 | 0 | 1294 | 2336 kB | ||
