Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WdbVi-0003Gp-MO for pgsql-performance@arkaria.postgresql.org; Fri, 25 Apr 2014 08:29:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WdbVi-0002oI-67 for pgsql-performance@arkaria.postgresql.org; Fri, 25 Apr 2014 08:29:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WdbVh-0002oA-2a for pgsql-performance@postgresql.org; Fri, 25 Apr 2014 08:29:29 +0000 Received: from mail-ie0-x22d.google.com ([2607:f8b0:4001:c03::22d]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WdbVc-0005dD-7Y for pgsql-performance@postgresql.org; Fri, 25 Apr 2014 08:29:27 +0000 Received: by mail-ie0-f173.google.com with SMTP id rl12so3520605iec.4 for ; Fri, 25 Apr 2014 01:29:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=ikD0yKYu7tTRHzgzvlPvhwCcly+xeFePkFGumoa0osM=; b=LJeGMbupRLQVau/nklpitkLmOkfFnkE4xo1Qx5GXzNNVvmXjPob9uBFBDDB+zUk7pm qCNKOwje1izHVcE8/WZ2oU8+NMBmPVtYDk2cIsF2/QyFYhCUkHiMJM2FVBkNVeVNRRm0 tmaYd9jbs0hpqrLppIUiAol8k53tzb2bn0IdNtNt2S5P8lnZt9/mbHpzKppPr2YA49m6 kpz1BSLCShJ5I/kcp3y2wwIeteaNBumzaMiCZuUrEne1eKLvz/KhIooZcKtNwoE5GF52 zVQM9dmIskyItSkIDCoCNP9pXA8Xtb56DwVmGIf0EPDXspEk3qHfRXFdKpIVd4TtJJyC gabw== X-Received: by 10.50.44.115 with SMTP id d19mr3470731igm.1.1398414562427; Fri, 25 Apr 2014 01:29:22 -0700 (PDT) MIME-Version: 1.0 Received: by 10.42.163.8 with HTTP; Fri, 25 Apr 2014 01:29:02 -0700 (PDT) In-Reply-To: References: From: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0KjQsNC70LDRiNC+0LI=?= Date: Fri, 25 Apr 2014 12:29:02 +0400 Message-ID: Subject: Re: Server vacuuming the same table again and again To: ik@postgresql-consulting.com Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=e89a8f8395153e09ff04f7d9c3ec X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --e89a8f8395153e09ff04f7d9c3ec Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable And right now we have a new kind of problem. Previously during load disk was 100% busy; now we have around 100 active state queries, 100% loaded proc, but disk is virtually idle... Normally we have under 10 active queries. Any hints on that? Best regards, Dmitriy Shalashov 2014-04-25 12:22 GMT+04:00 =D0=94=D0=BC=D0=B8=D1=82=D1=80=D0=B8=D0=B9 =D0= =A8=D0=B0=D0=BB=D0=B0=D1=88=D0=BE=D0=B2 : > Hi Ilya! > > > Actually, thise two things are tightly bound and there is no chance to = avoid > vacuum, you can only postpone it, this kind of work eventually supposed > to be done. > > I understand that autovacuum has to be done, but not right after previous > autovacuum? And then again and again. > And after cancelling that first autovacuum I started another one by hand; > from there no autovacuum was cancelled. > > > ionice autovacuum instead of mission critical ckeckpointer or bgwriter > Yeah, that was desperate. I restarted server when I had a chance - to dro= p > my ionice settings back to defaults. > > > Which exact values have you in the following settings: > > autovacuum_analyze_scale_factor =3D 0.1 > autovacuum_analyze_threshold =3D 50 > autovacuum_freeze_max_age =3D 200000000 > autovacuum_max_workers =3D 3 > autovacuum_naptime =3D 60 > autovacuum_vacuum_cost_delay =3D 20 > autovacuum_vacuum_cost_limit =3D -1 > autovacuum_vacuum_scale_factor =3D 0.2 > autovacuum_vacuum_threshold =3D 50 > log_autovacuum_min_duration =3D 0 > > All defaults except last one I believe. > > > Minwhile I noticed in the night logs: > checkpoints are occurring too frequently (138 seconds apart) > Consider increasing the configuration parameter "checkpoint_segments". > > Increased checkpoint_segments to 256 and reloaded config. > > > Best regards, > Dmitriy Shalashov > > > 2014-04-25 12:12 GMT+04:00 Ilya Kosmodemiansky < > ilya.kosmodemiansky@postgresql-consulting.com>: > > Hi Dmitry, >> >> On Fri, Apr 25, 2014 at 9:47 AM, =D0=94=D0=BC=D0=B8=D1=82=D1=80=D0=B8=D0= =B9 =D0=A8=D0=B0=D0=BB=D0=B0=D1=88=D0=BE=D0=B2 >> wrote: >> > cancelled autovacuum and it seems to help. >> >> > In the morning autovacuum was back. And then it finished and I gone to >> work. >> >> Actually, thise two things are tightly bound and there is no chance to >> avoid vacuum, you can only postpone it, this kind of work eventually >> supposed to be done. >> >> What you really need to do as a first thing - configure your >> autovacuum aggressively enough and then mayde ionice autovacuum >> instead of mission critical ckeckpointer or bgwriter. >> >> Which exact values have you in the following settings: >> >> autovacuum_analyze_scale_factor >> autovacuum_analyze_threshold >> autovacuum_freeze_max_age >> autovacuum_max_workers >> autovacuum_naptime >> autovacuum_vacuum_cost_delay >> autovacuum_vacuum_cost_limit >> autovacuum_vacuum_scale_factor >> autovacuum_vacuum_threshold >> log_autovacuum_min_duration >> >> ? >> >> Best regards, Ilya >> > >> > Best regards, >> > Dmitriy Shalashov >> >> >> >> -- >> Ilya Kosmodemiansky, >> >> PostgreSQL-Consulting.com >> tel. +14084142500 >> cell. +4915144336040 >> ik@postgresql-consulting.com >> > > --e89a8f8395153e09ff04f7d9c3ec Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
And right now we have a new kind of problem.
Previousl= y during load disk was 100% busy; now we have around 100 active state queri= es, 100% loaded proc, but disk is virtually idle... Normally we have under = 10 active queries.
Any hints on that?


Best regards,
Dmitriy Shalasho= v


2014-04-25 12:22 GMT+04:00 =D0=94=D0=BC= =D0=B8=D1=82=D1=80=D0=B8=D0=B9 =D0=A8=D0=B0=D0=BB=D0=B0=D1=88=D0=BE=D0=B2 <= span dir=3D"ltr"><skaurus@gmail.com>:
Hi Ilya!

>=C2=A0Actually, thise two= things are tightly bound and there is no chance to=C2=A0avoid vacuum, you can only= postpone it, this kind of work eventually=C2=A0supposed to be done.

I understand th= at autovacuum has to be done, but not right after previous autovacuum? And = then again and again.
And after cancelling that first autovacuum = I started another one by hand; from there no autovacuum was cancelled.

>=C2=A0ionice autovacuum=C2=A0instead of mission critical ckeckpointer or b= gwriter
Yeah, that was desperate. I restarted server when I had a chance - to dro= p my ionice settings back to defaults.

>=C2=A0Which exact values have you in the following settings:

autovacuum_analyze_scale_factor =3D 0.1
autovacuum_analyze_thres= hold =3D 50
autovacuu= m_freeze_max_age =3D=C2=A0200000000
autovacuum_max_workers =3D 3
autovacuum_naptime =3D 6= 0
autovacuum_vacuum_c= ost_delay =3D 20
auto= vacuum_vacuum_cost_limit =3D -1
autovacuum_vacuum_scale_= factor =3D 0.2
autova= cuum_vacuum_threshold =3D 50
log_autovacuum_min_duration =3D 0

All defaults except last one I believe.


Minwhile I noticed in th= e night logs:
checkpoints= are occurring too frequently (138 seconds apart)
Consider increasing the configuration paramete= r "checkpoint_segments".

Increased=C2=A0checkpoint_segments to 256 and reloaded config.


Best regards,
Dmitriy Shalashov


2014-04-25 12:12 GMT+04:00 Ilya Kosmodem= iansky <ilya.kosmodemiansky@postgresql-consult= ing.com>:

Hi Dmitry,

On Fri, Apr 25, 2014 at 9:47 AM, =D0=94=D0=BC=D0=B8=D1=82=D1=80=D0=B8=D0=B9= =D0=A8=D0=B0=D0=BB=D0=B0=D1=88=D0=BE=D0=B2 <skaurus@gmail.com> wrote:
> cancelled autovacuum and it seems to help.

> In the morning autovacuum was back. And then it finished an= d I gone to work.

Actually, thise two things are tightly bound and there is no chance t= o
avoid vacuum, you can only postpone it, this kind of work eventually
supposed to be done.

What you really need to do as a first thing - configure your
autovacuum aggressively enough and then mayde ionice autovacuum
instead of mission critical ckeckpointer or bgwriter.

Which exact values have you in the following settings:

=C2=A0autovacuum_analyze_scale_factor
=C2=A0autovacuum_analyze_threshold
=C2=A0autovacuum_freeze_max_age
=C2=A0autovacuum_max_workers
=C2=A0autovacuum_naptime
=C2=A0autovacuum_vacuum_cost_delay
=C2=A0autovacuum_vacuum_cost_limit
=C2=A0autovacuum_vacuum_scale_factor
=C2=A0autovacuum_vacuum_threshold
=C2=A0log_autovacuum_min_duration

?

Best regards, Ilya
>
> Best regards,
> Dmitriy Shalashov



--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgr= esql-consulting.com


--e89a8f8395153e09ff04f7d9c3ec--