Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVOMD-0001Ka-44 for pgsql-admin@postgresql.org; Mon, 05 Nov 2012 15:12:57 +0000 Received: from moutng.kundenserver.de ([212.227.126.171]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVOM9-0006YD-JI for pgsql-admin@postgresql.org; Mon, 05 Nov 2012 15:12:56 +0000 Received: from obelix.localnet (AToulouse-556-1-211-174.w86-217.abo.wanadoo.fr [86.217.163.174]) by mrelayeu.kundenserver.de (node=mreu0) with ESMTP (Nemesis) id 0M6hWa-1T849C3bM4-00x6kk; Mon, 05 Nov 2012 16:12:47 +0100 From: =?utf-8?q?C=C3=A9dric_Villemain?= Reply-To: cedric@2ndquadrant.com Organization: 2ndQuadrant To: pgsql-admin@postgresql.org Subject: Re: Autoanalyze of the autovacuum daemon ... Date: Mon, 5 Nov 2012 16:12:42 +0100 User-Agent: KMail/1.13.7 (Linux/3.2.0-4-amd64; KDE/4.8.4; x86_64; ; ) Cc: Baptiste LHOSTE , Sylvain CAILLET References: <216101410.8465931.1352123453261.JavaMail.root@alaloop.com> In-Reply-To: <216101410.8465931.1352123453261.JavaMail.root@alaloop.com> X-KMail-Markup: true MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart35607372.t8rqTxIAD0"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201211051612.45865.cedric@2ndquadrant.com> X-Provags-ID: V02:K0:wrRYwCUMIK/5vaq36Y14QXbnZtVD/z9jE/zJ1FIHUCF Y2YeZIQU6wmHz20eIS/cARhUgNRxk6gJGIOiMIlqY0NmJiYNMU UA6upEsCYsYUadH87/885tgVkNbDELCZdoaSc5RkBRmMKb/WVa U6UwfK4+1aE5TcWzTpITjUFVtfefs+1AL7RtWzuFl6SaImsaUK b0AfQb8jAy5AdoLtP9Dq+KmNPFL9pLJzVZOGP/U3F7Y5LoAoNN A4oWhKTtKDTFOfgl6h/dN0jpS+MTvVo9QGAZxBn/yMpInqp23X Eihu81aK5FfGRWAKwqduxqWMF8ucVT/JknRoC5BZ273kfB9hz1 8Mnpq0SeK2+Xf7iNM44iaCTiB0tPmWR26WOGVM4nn X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/22 X-Sequence-Number: 40267 --nextPart35607372.t8rqTxIAD0 Content-Type: multipart/alternative; boundary="Boundary-01=_qd9lQg9N1B/Q2rM" Content-Transfer-Encoding: 7bit --Boundary-01=_qd9lQg9N1B/Q2rM Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable > relname | last_autoanalyse | reltuples | relpages | > n_tup_ins + n_tup_upd + n_tup_del >=20 > | (from pg_stat_all_tables) | | | (from > | pg_stat_all_tables) >=20 > table1 | 2012-10-15 21:12:04.362647+02 | 2.53296e+06 | 50789 | 285424= 83 > table2 | 2012-10-15 18:27:30.181583+02 | 2.57144e+06 | 54833 | 311277= 37 > table3 | 2012-10-15 13:13:09.569758+02 | 2.58646e+06 | 63208 | 271679= 44 > table4 | 2012-10-15 12:05:39.094652+02 | 2.58655e+06 | 63181 | 271604= 09 > table5 | 2012-10-15 12:05:32.347738+02 | 2.58611e+06 | 57272 | 271716= 61 > table6 | 2012-10-15 10:43:40.558078+02 | 2.57925e+06 | 63145 | 271558= 75 > table7 | 2012-10-15 10:40:25.469625+02 | 2.59551e+06 | 57254 | 271618= 76 > table8 | 2012-10-15 10:25:10.776152+02 | 2.56987e+06 | 63147 | 271779= 07 > table9 | 2012-10-15 09:30:17.407181+02 | 2.5923e+06 | 57231 | 271529= 53 > table10 | 2012-10-15 08:29:25.427207+02 | 2.56237e+06 | 63085 | 317705= 81 > table11 | 2012-10-15 08:26:14.550742+02 | 2.56503e+06 | 63065 | 317696= 09 > table12 | 2012-10-15 06:39:01.737914+02 | 2.57833e+06 | 63023 | 317675= 43 > table13 | 2012-10-15 06:35:36.43683+02 | 2.57217e+06 | 63047 | 317599= 54 > table14 | 2012-10-15 05:08:02.858623+02 | 2.59068e+06 | 57119 | 271545= 55 > table15 | 2012-10-15 04:40:48.695142+02 | 2.57449e+06 | 63002 | 317680= 34 > table16 | 2012-10-10 07:51:49.625226+02 | 2.48584e+06 | 54014 | 221102= 23 > table17 | 2012-10-09 10:03:36.649331+02 | 2.58047e+06 | 63742 | 234232= 57 > table18 | 2012-10-09 10:02:13.549362+02 | 2.57854e+06 | 63585 | 233640= 35 > table19 | 2012-10-09 09:18:55.447895+02 | 2.58851e+06 | 63516 | 233751= 80 So that an auto-ANALYZE should run each 2.5-3 hours.=20 If it does not proceed, check that you do not have some process still doing= =20 manual maintenance or DDL on those tables, they might lock the table and ki= ll=20 autovacuum (check the pg_stat_activity, and set log_autovacuum_min_duration=3D0 to tra= ck=20 all job done by autovacuum) =2D-=20 C=C3=A9dric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - D=C3=A9veloppement, Expertise et Formation --Boundary-01=_qd9lQg9N1B/Q2rM Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

> relnam= e | last_autoanalyse | reltuples | relpages |

> n_tup_= ins + n_tup_upd + n_tup_del

>

> = | (from pg_stat_all_tables) | | | (from

> = | pg_stat_all_tables)

>

> table1= | 2012-10-15 21:12:04.362647+02 | 2.53296e+06 | 50789 | 28542483

> table2= | 2012-10-15 18:27:30.181583+02 | 2.57144e+06 | 54833 | 31127737

> table3= | 2012-10-15 13:13:09.569758+02 | 2.58646e+06 | 63208 | 27167944

> table4= | 2012-10-15 12:05:39.094652+02 | 2.58655e+06 | 63181 | 27160409

> table5= | 2012-10-15 12:05:32.347738+02 | 2.58611e+06 | 57272 | 27171661

> table6= | 2012-10-15 10:43:40.558078+02 | 2.57925e+06 | 63145 | 27155875

> table7= | 2012-10-15 10:40:25.469625+02 | 2.59551e+06 | 57254 | 27161876

> table8= | 2012-10-15 10:25:10.776152+02 | 2.56987e+06 | 63147 | 27177907

> table9= | 2012-10-15 09:30:17.407181+02 | 2.5923e+06 | 57231 | 27152953

> table1= 0 | 2012-10-15 08:29:25.427207+02 | 2.56237e+06 | 63085 | 31770581

> table1= 1 | 2012-10-15 08:26:14.550742+02 | 2.56503e+06 | 63065 | 31769609

> table1= 2 | 2012-10-15 06:39:01.737914+02 | 2.57833e+06 | 63023 | 31767543

> table1= 3 | 2012-10-15 06:35:36.43683+02 | 2.57217e+06 | 63047 | 31759954

> table1= 4 | 2012-10-15 05:08:02.858623+02 | 2.59068e+06 | 57119 | 27154555

> table1= 5 | 2012-10-15 04:40:48.695142+02 | 2.57449e+06 | 63002 | 31768034

> table1= 6 | 2012-10-10 07:51:49.625226+02 | 2.48584e+06 | 54014 | 22110223

> table1= 7 | 2012-10-09 10:03:36.649331+02 | 2.58047e+06 | 63742 | 23423257

> table1= 8 | 2012-10-09 10:02:13.549362+02 | 2.57854e+06 | 63585 | 23364035

> table1= 9 | 2012-10-09 09:18:55.447895+02 | 2.58851e+06 | 63516 | 23375180

&nb= sp;

&nb= sp;

So that an = auto-ANALYZE should run each 2.5-3 hours.

If it does = not proceed, check that you do not have some process still doing manual mai= ntenance or DDL on those tables, they might lock the table and kill autovac= uum

(check the = pg_stat_activity, and set log_autovacuum_min_duration=3D0 to track all job = done by autovacuum)

&nb= sp;

--

C=C3=A9dric= Villemain +33 (0)6 20 30 22 52

http://2ndQ= uadrant.fr/

PostgreSQL:= Support 24x7 - D=C3=A9veloppement, Expertise et Formation

--Boundary-01=_qd9lQg9N1B/Q2rM-- --nextPart35607372.t8rqTxIAD0 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) iQEcBAABAgAGBQJQl9dqAAoJENsgH0yzVBebYVgH/3Mu2mMnHIrfJ7KHlX+RYUEp jnTIi0mmxCUok4XjuUhfAp/i6AWmGJztep1ZHdjjlFbD4gSprn0OsPoxOIbmuWhB QQcAP/FKA3TLEzbXlDj74vZrzJCKMZO/51I7ylfhniktOzbLbWSeYV6K5NRp189T gZoPGZrnGIjZGGgjgjPRaw8kpTbS7/2VrO0dmDxmMMhCeU2YlSvvvTLQ0bfi4zlz zirsgjGKsv4BwnBpnqFFhnAr7wvK/u5g9zqCJQYOP9mMUCQf8yusBPhkUXgJQnMt HMROO1WOuoI38HF7xyLiaxmCeEBOXk2ysy78VnH6ktU3dTQ1jgHzxDQdCMoxF64= =omeV -----END PGP SIGNATURE----- --nextPart35607372.t8rqTxIAD0--