Re: autovacuum excesivo PostgreSQL 9.5

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

> Hellmuth Vargas escribió:
> > Hola Alvaro
> >
> > Muchas gracias por el tiempo.. respondo entre lineas:
>
> Pero no respondiste esta parte:
>
> ¿no tendrás alguna transacción preparada abierta? Mira en
> pg_stat_activity y pg_prepared_xacts.
>
>
Perdon Alvaro

pid backend_start xact_start query_start state_change waiting state
backend_xid backend_xmin query
2329 2016-10-02 11:50:48.211693-05
2016-10-03 16:24:13.078628-05 2016-10-03 16:24:13.080102-05 f idle

1951 2016-10-02 11:47:38.104996-05
2016-10-03 16:24:07.580646-05 2016-10-03 16:24:07.581901-05 f idle

2998 2016-10-02 11:57:18.434209-05
2016-10-03 16:24:11.504117-05 2016-10-03 16:24:11.512482-05 f idle

*1974* *2016-10-03 16:23:59.018115-05* *2016-10-03 16:24:12.994839-05*
*2016-10-03
16:24:12.994839-05* *2016-10-03 16:24:12.99484-05* *f* *active*
*8342722* *autovacuum: VACUUM pg_catalog.pg_attribute*
60412 2016-10-03 15:42:18.497036-05
2016-10-03 16:24:00.295426-05 2016-10-03 16:24:00.295465-05 f idle

COMMIT
62959 2016-10-03 15:58:24.344015-05
2016-10-03 16:12:26.563051-05 2016-10-03 16:12:26.58076-05 f idle

COMMIT
62960 2016-10-03 15:58:24.351477-05
2016-10-03 16:16:24.455809-05 2016-10-03 16:16:24.455825-05 f idle

COMMIT
2244 2016-10-02 11:50:06.527689-05
2016-10-03 16:24:08.550525-05 2016-10-03 16:24:08.550557-05 f idle

COMMIT
2953 2016-10-02 11:56:58.935904-05
2016-10-03 16:24:12.413807-05 2016-10-03 16:24:12.41585-05 f idle

2999 2016-10-02 11:57:18.559647-05
2016-10-03 16:24:11.513935-05 2016-10-03 16:24:11.524073-05 f idle

2290 2016-10-02 11:50:39.419437-05
2016-10-03 16:24:16.042693-05 2016-10-03 16:24:16.057769-05 f idle

*65328* *2016-10-03 16:12:32.051363-05* *2016-10-03
16:13:06.642648-05* *2016-10-03
16:13:06.642648-05* *2016-10-03 16:13:06.642649-05* *f* *active*
*8338139* *autovacuum: VACUUM sac.marcador*
54614 2016-10-03 15:06:17.258045-05
2016-10-03 15:12:29.003785-05 2016-10-03 15:12:29.005669-05 f idle

SELECT column_name FROM information_schema.columns WHERE
table_schema='colpensionessac' AND table_name='tipificacioncorreo' ORDER BY
ordinal_position
51512 2016-10-03 08:06:09.047226-05
2016-10-03 15:59:33.065909-05 2016-10-03 15:59:33.066646-05 f idle

54615 2016-10-03 15:06:17.26081-05
2016-10-03 16:02:23.832531-05 2016-10-03 16:02:23.832585-05 f idle

SET search_path TO "$user", public
51517 2016-10-03 08:06:13.017255-05
2016-10-03 08:07:33.183604-05 2016-10-03 08:07:34.920135-05 f idle

analyze sac.marcador ;
46848 2016-10-03 14:18:36.332516-05
2016-10-03 16:12:26.572828-05 2016-10-03 16:12:26.580269-05 f idle

COMMIT
60004 2016-10-03 15:39:52.918343-05
2016-10-03 16:24:16.06574-05 2016-10-03 16:24:16.065748-05 f idle

COMMIT
46084 2016-10-03 07:32:56.116658-05
2016-10-03 16:12:26.568076-05 2016-10-03 16:12:26.581555-05 f idle

COMMIT
36428 2016-10-03 13:14:09.801684-05
2016-10-03 16:12:19.34997-05 2016-10-03 16:12:19.351039-05 f idle

COMMIT
54857 2016-10-03 15:07:47.494428-05
2016-10-03 15:11:10.2174-05 2016-10-03 15:11:10.217493-05 f idle

28717 2016-10-03 12:26:48.557859-05
2016-10-03 12:26:48.874593-05 2016-10-03 12:26:48.874657-05 f idle

SELECT nspname FROM pg_namespace
59489 2016-10-03 08:55:03.645719-05
2016-10-03 08:57:33.986584-05 2016-10-03 08:57:33.986957-05 f idle

SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR
(t.typtype = 'd' AND t.typnotnull), pg_catalog.pg_get_expr(d.adbin,
d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN
pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t
ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid =
a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 44940 AS oid , 1 AS attnum
UNION ALL SELECT 44940, 2 UNION ALL SELECT 44940, 3 UNION ALL SELECT 44940,
4 UNION ALL SELECT 44940, 5 UNION ALL SELECT 44940, 6 UNION ALL SELECT
44940, 7 UNION ALL SELECT 44940, 8 UNION ALL SELECT 44940, 9 UNION ALL
SELECT 44940, 10 UNION ALL SELECT 44940, 11 UNION ALL SELECT 44940, 12
UNION ALL SELECT 44940, 13 UNION ALL SELECT 44940, 14 UNION ALL SELECT
44940, 15 UNION ALL SELECT 44940, 16 UNION ALL SELECT 44940, 17 UNION ALL
SELECT 44940, 18 UNION ALL SELECT 44940, 19 UNION ALL SELECT 44940, 20
UNION ALL SELECT 44940, 21 UNION ALL SELECT 44940, 22 UNION ALL SELECT
44940, 23 UNION ALL SELECT 44940, 24 UNION ALL SELECT 44940, 25 UNION ALL
SELECT 44940, 26 UNION ALL SELECT 44940, 27 UNION ALL SELECT 44940, 28
UNION ALL SELECT 44940, 29 UNION ALL SELECT 44940, 30 UNION ALL SELECT
44940, 31 UNION ALL SELECT 44940, 32 UNION ALL SELECT 44940, 33) vals ON
(c.oid = vals.oid AND a.attnum = vals.attnum)
59671 2016-10-03 08:56:18.50717-05
2016-10-03 08:56:18.838372-05 2016-10-03 08:56:18.838396-05 f idle

show search_path
28718 2016-10-03 12:26:48.56114-05
2016-10-03 12:29:44.169251-05 2016-10-03 12:29:44.169288-05 f idle

SET search_path TO "$user", public
60006 2016-10-03 15:39:52.925704-05
2016-10-03 16:12:26.544748-05 2016-10-03 16:12:26.544765-05 f idle

COMMIT
65073 2016-10-03 09:29:34.403714-05
2016-10-03 09:29:34.424691-05 2016-10-03 09:29:34.426806-05 f idle

SELECT d.datname, d.oid, pg_get_userbyid(d.datdba) AS owner,
shobj_description(d.oid, 'pg_database') AS comment, t.spcname, d.datacl,
d.datlastsysoid, d.encoding, pg_encoding_to_char(d.encoding) AS
encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON
d.dattablespace=t.oid
48696 2016-10-03 14:30:00.045062-05
2016-10-03 16:20:00.145197-05 2016-10-03 16:20:00.166987-05 f idle

COMMIT
50431 2016-10-03 14:40:30.209803-05
2016-10-03 16:12:26.581205-05 2016-10-03 16:12:26.581221-05 f idle

COMMIT
50432 2016-10-03 14:40:30.396322-05
2016-10-03 16:12:26.617944-05 2016-10-03 16:12:26.617963-05 f idle

COMMIT
60005 2016-10-03 15:39:52.920912-05
2016-10-03 16:22:24.513307-05 2016-10-03 16:22:24.513322-05 f idle

COMMIT
65074 2016-10-03 09:29:34.437695-05
2016-10-03 09:29:34.490376-05 2016-10-03 09:29:34.501869-05 f idle

SELECT c.oid, obj_description(c.oid), c.relhasoids AS hasoids, n.nspname AS
schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS
tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes,
c.relhasrules AS hasrules, c.relhastriggers AS hastriggers, c.relacl,
c.reltuples, ((SELECT count(*) FROM pg_inherits WHERE inhparent = c.oid) >
0) AS inhtable, i2.relname AS inhtablename, c.reloptions AS param,
c.relpersistence AS unlogged FROM pg_class c LEFT JOIN pg_namespace n ON
n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN (pg_inherits i INNER JOIN pg_class c2 ON i.inhparent = c2.oid) i2
ON i2.inhrelid = c.oid WHERE (c.relkind = 'r'::"char") AND n.nspname = 'sac'
65076 2016-10-03 09:29:34.96299-05
2016-10-03 09:29:36.359069-05 2016-10-03 09:29:36.359186-05 f idle

SELECT nspname FROM pg_namespace
65077 2016-10-03 09:29:34.966682-05
2016-10-03 09:29:35.041703-05 2016-10-03 09:29:35.041806-05 f idle

SHOW search_path
65103 2016-10-03 09:29:55.09585-05
2016-10-03 11:26:16.881703-05 2016-10-03 11:26:16.885021-05 f idle

SELECT column_name FROM information_schema.columns WHERE table_schema='sac'
AND table_name='tipificacionguion' ORDER BY ordinal_position
65104 2016-10-03 09:29:55.096433-05
2016-10-03 11:53:10.228129-05 2016-10-03 11:53:10.228154-05 f idle

SET search_path TO "$user", public
19642 2016-10-03 11:30:54.515326-05
2016-10-03 16:24:01.519914-05 2016-10-03 16:24:01.519928-05 f idle

COMMIT
60007 2016-10-03 15:39:52.934727-05
2016-10-03 16:24:11.283945-05 2016-10-03 16:24:11.283983-05 f idle

COMMIT
58001 2016-10-03 15:27:18.504868-05
2016-10-03 16:24:16.065576-05 2016-10-03 16:24:16.065585-05 f idle

COMMIT
60008 2016-10-03 15:39:52.936082-05
2016-10-03 16:12:26.649554-05 2016-10-03 16:12:26.649576-05 f idle

COMMIT
30620 2016-10-03 12:38:25.722087-05
2016-10-03 12:48:48.370266-05 2016-10-03 12:48:48.370317-05 f idle

SET search_path TO "$user", public
50436 2016-10-03 14:40:31.307481-05
2016-10-03 16:12:26.354475-05 2016-10-03 16:12:26.354485-05 f idle

COMMIT
30619 2016-10-03 12:38:25.720364-05
2016-10-03 12:38:26.27987-05 2016-10-03 12:38:26.279934-05 f idle

SELECT nspname FROM pg_namespace
50437 2016-10-03 14:40:31.445229-05
2016-10-03 16:12:26.548248-05 2016-10-03 16:12:26.576816-05 f idle

COMMIT
54858 2016-10-03 15:07:47.497042-05
2016-10-03 15:48:44.280895-05 2016-10-03 15:48:44.280934-05 f idle

SET search_path TO "$user", public
60831 2016-10-03 15:44:58.624063-05 2016-10-03 16:24:16.082138-05 2016-10-03
16:24:16.082138-05 2016-10-03 16:24:16.08214-05 f active
8342733 select pid,backend_start,xact_start,
query_start,state_change,waiting,state, backend_xid,backend_xmin,query from
pg_stat_activity where datname='bd'
61553 2016-10-03 15:49:26.62522-05
2016-10-03 16:24:13.747356-05 2016-10-03 16:24:13.747648-05 f idle

SELECT nsp.nspname, cl.relname, array_upper(conkey,1) as pkeycount FROM
pg_class cl LEFT JOIN pg_namespace nsp ON nsp.oid = cl.relnamespace LEFT
JOIN pg_constraint con ON con.conrelid = cl.oid WHERE con.contype = 'p' AND
cl.oid = 43649
61552 2016-10-03 15:49:26.622689-05
2016-10-03 16:22:53.816596-05 2016-10-03 16:22:53.926185-05 f idle

SELECT column_name FROM information_schema.columns WHERE table_schema='sac'
AND table_name='rpm' ORDER BY ordinal_position
62963 2016-10-03 15:58:24.397757-05
2016-10-03 16:12:26.549778-05 2016-10-03 16:12:26.580346-05 f idle

COMMIT
62964 2016-10-03 15:58:24.407165-05
2016-10-03 16:16:24.450662-05 2016-10-03 16:16:24.450677-05 f idle

COMMIT
62965 2016-10-03 15:58:24.415248-05
2016-10-03 16:12:26.645868-05 2016-10-03 16:12:26.645892-05 f idle

COMMIT
62966 2016-10-03 15:58:24.436917-05
2016-10-03 16:12:26.567731-05 2016-10-03 16:12:26.580825-05 f idle

COMMIT
63003 2016-10-03 15:58:27.199151-05
2016-10-03 16:24:01.524736-05 2016-10-03 16:24:01.524747-05 f idle

COMMIT
63008 2016-10-03 15:58:28.530445-05
2016-10-03 16:12:26.569753-05 2016-10-03 16:12:26.580495-05 f idle

COMMIT
63034 2016-10-03 15:58:31.801155-05
2016-10-03 16:23:05.561203-05 2016-10-03 16:23:05.561218-05 f idle

COMMIT
63035 2016-10-03 15:58:31.804076-05
2016-10-03 16:12:26.549443-05 2016-10-03 16:12:26.580622-05 f idle

COMMIT
63489 2016-10-03 16:01:04.640733-05
2016-10-03 16:23:08.520233-05 2016-10-03 16:23:08.520243-05 f idle

COMMIT
63490 2016-10-03 16:01:04.674869-05
2016-10-03 16:23:43.304316-05 2016-10-03 16:23:43.304325-05 f idle

COMMIT
64104 2016-10-03 16:05:00.211731-05
2016-10-03 16:24:10.059857-05 2016-10-03 16:24:10.059894-05 f idle

COMMIT

bd=# \x
Expanded display is on.
bd=# select * from pg_prepared_xacts;
(0 rows)

bd=#

>

--
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