Re: autovacuum excesivo PostgreSQL 9.5

Hola Alvaro

Muchas gracias por el tiempo.. respondo entre lineas:

El 3 de octubre de 2016, 16:01, Alvaro Herrera<alvherre(at)2ndquadrant(dot)com>
escribió:

> Hellmuth Vargas escribió:
>
> > bd=# select * from pg_stat_user_tables where relname in ('marcador',
> > 'usuario');
>
> Por alguna razón estas tablas tienen n_dead_tup que no parece decrecer?
> ¿no tendrás alguna transacción preparada abierta? Mira en
> pg_stat_activity y pg_prepared_xacts.

> Si haces un "vacuum verbose usuario", ¿qué dice? Me pregunto si
> autovacuum estará arrojando errores antes que termine. Mira en el log.
>
>
VACUUM FULL analyze verbose sac.marcador;

INFO: vacuuming "bd.sac.marcador"
INFO: "marcador": found 0 removable, 2580480 nonremovable row versions in
104585 pages
DETAIL: 1082081 dead row versions cannot be removed yet.
CPU 0.95s/10.97u sec elapsed 26.32 sec.
INFO: analyzing "sac.marcador"
INFO: "marcador": scanned 104611 of 104611 pages, containing 1498399 live
rows and 1082098 dead rows; 120000 rows in sample, 1498399 estimated total
rows
Query returned successfully with no result in 39.8 secs.

--- log:

_2016-10-03 01:56:26 COT@@@@proc:9348 LOG: automatic vacuum of table
"bd.sac.marcador": index scans: 0
pages: 0 removed, 59776 remain, 0 skipped due to pins
tuples: 0 removed, 2478519 remain, 989963 are dead but not yet
removable
buffer usage: 54441 hits, 70857 misses, 2 dirtied
avg read rate: 4.769 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.13s/0.26u sec elapsed 116.06 sec

_2016-10-03 01:58:46 COT@@@@proc:9421 LOG: automatic vacuum of table
"bd.sac.marcador": index scans: 0
pages: 0 removed, 59776 remain, 0 skipped due to pins
tuples: 0 removed, 2478519 remain, 989963 are dead but not yet
removable
buffer usage: 54441 hits, 70857 misses, 2 dirtied
avg read rate: 4.260 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.15s/0.24u sec elapsed 129.93 sec

_2016-10-03 02:01:32 COT@@@@proc:9581 LOG: automatic vacuum of table
"bd.sac.marcador": index scans: 0
pages: 0 removed, 59776 remain, 0 skipped due to pins
tuples: 0 removed, 2478519 remain, 989963 are dead but not yet
removable
buffer usage: 54473 hits, 70825 misses, 2 dirtied
avg read rate: 4.524 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.13s/0.26u sec elapsed 122.31 sec

--- la tabla usuario:
VACUUM FULL analyze verbose sac.usuario;
INFO: vacuuming "sac.usuario"
INFO: "usuario": found 0 removable, 52298 nonremovable row versions in
2201 pages
DETAIL: 52002 dead row versions cannot be removed yet.
CPU 0.01s/0.20u sec elapsed 0.34 sec.
INFO: analyzing "sac.usuario"
INFO: "usuario": scanned 2209 of 2209 pages, containing 296 live rows and
52002 dead rows; 296 rows in sample, 296 estimated total rows
Query returned successfully with no result in 2.2 secs.

--- log:

_2016-10-03 01:58:49 COT@@@@proc:9421 LOG: automatic vacuum of table
"bd.sac.usuario": index scans: 0
pages: 0 removed, 1587 remain, 0 skipped due to pins
tuples: 0 removed, 47457 remain, 47161 are dead but not yet
removable
buffer usage: 3450 hits, 1061 misses, 5 dirtied
avg read rate: 2.927 MB/s, avg write rate: 0.014 MB/s
system usage: CPU 0.00s/0.01u sec elapsed 2.83 sec

_2016-10-03 02:00:45 COT@@@@proc:9619 LOG: automatic vacuum of table
"bd.sac.usuario": index scans: 0
pages: 0 removed, 1587 remain, 0 skipped due to pins
tuples: 0 removed, 47457 remain, 47161 are dead but not yet
removable
buffer usage: 3185 hits, 1326 misses, 6 dirtied
avg read rate: 3.009 MB/s, avg write rate: 0.014 MB/s
system usage: CPU 0.00s/0.01u sec elapsed 3.44 sec

_2016-10-03 02:01:36 COT@@@@proc:9581 LOG: automatic vacuum of table
"bd.sac.usuario": index scans: 0
pages: 0 removed, 1587 remain, 0 skipped due to pins
tuples: 0 removed, 47457 remain, 47161 are dead but not yet
removable
buffer usage: 3187 hits, 1324 misses, 6 dirtied
avg read rate: 3.064 MB/s, avg write rate: 0.014 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 3.37 sec

Lo particular es que esa campaña solo trabaja en horario hábil.. y el log
corresponde a la madrugada cuando no se esta haciendo nada....y asi se la
pasa tooodo el tiempo

Ademas también se evidencia sobre algunas tablas del sistema

bd=# select * from pg_stat_sys_tables where relname in ('pg_statistic');
-[ RECORD 1 ]-------+------------------------------
relid | 2619
schemaname | pg_catalog
relname | pg_statistic
seq_scan | 38
seq_tup_read | 363762
idx_scan | 4596569
idx_tup_fetch | 2439173
n_tup_ins | 329
n_tup_upd | 78854
n_tup_del | 31
n_tup_hot_upd | 6053
n_live_tup | 4478
n_dead_tup | 77924
n_mod_since_analyze | 79214
last_vacuum | 2016-10-03 00:10:02.477176-05
last_autovacuum | 2016-10-03 16:03:46.93344-05
last_analyze |
last_autoanalyze |
vacuum_count | 15
autovacuum_count | 19499
analyze_count | 0
autoanalyze_count | 0

_2016-10-03 02:00:28 COT@@@@proc:9619 LOG: automatic vacuum of table
"bd.pg_catalog.pg_statistic": index scans: 0
pages: 0 removed, 4145 remain, 0 skipped due to pins
tuples: 0 removed, 82216 remain, 77738 are dead but not yet
removable
buffer usage: 7506 hits, 1110 misses, 1 dirtied
avg read rate: 1.778 MB/s, avg write rate: 0.002 MB/s
system usage: CPU 0.01s/0.01u sec elapsed 4.87 sec

--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate