Hola Alvaro Muchas gracias por el tiempo.. respondo entre lineas:
El 3 de octubre de 2016, 16:01, Alvaro Herrera<alvhe...@2ndquadrant.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