pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM problems
23+ messages / 8 participants
[nested] [flat]
* VACUUM problems
@ 2001-08-24 09:07 Martins Zarins <mark@vestnesis.lv>
2001-08-24 13:39 ` Re: VACUUM problems Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Martins Zarins @ 2001-08-24 09:07 UTC (permalink / raw)
To: pgsql-admin
hello, All !
I'm freshman to this list.
Before version 7.1 there was restriction on SQL tuple length 8-32Kb.
Now I'm using 7.1.2 where iz no such limitations. I have created
some documents database. Theare is table with full text of
documents. Some rows are up to 400Kb lenght. The database is
working just fine, but vacuum crashes all the time after procesing
that full text table. When I deleted all the records from that full text
table, vacuum did correctly.
Right now I'm generating the same table but the text is split into
multiple records each ~8K. I think - it will work.
Maybe that's a bug in vacuum or maybe some misconfiguration (I'm
using default config).
If anyone had similar problems, pls share your ideas and
comments about solution.
Mark
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: VACUUM problems
2001-08-24 09:07 VACUUM problems Martins Zarins <mark@vestnesis.lv>
@ 2001-08-24 13:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 23+ messages in thread
From: Tom Lane @ 2001-08-24 13:39 UTC (permalink / raw)
To: mark@vestnesis.lv; +Cc: pgsql-admin
"Martins Zarins" <mark@vestnesis.lv> writes:
> Some rows are up to 400Kb lenght. The database is
> working just fine, but vacuum crashes all the time after procesing
> that full text table.
You're going to have to provide more information than that. What
happens *exactly*? What shows up in the postmaster's stderr log?
Is there a core file, and if so can you get a backtrace from it?
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Vacuum Problems
@ 2008-11-26 13:43 Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Domiciano @ 2008-11-26 13:43 UTC (permalink / raw)
To: pgsql-admin
Hi there,
I need some help or just some hints. I am having problems with vacuum full
in one table only: "clifatura".
That table has today around 7 million rows.
I scheduled on the server a cron job to run VACUUM FULL every day at 23 P.M,
but on the following day, at 8 A.M. vacuum is yet working on "clifatura"; so
I have to kill vacuum.
I read something about maintenance_work_mem, so I increased it to 128MB. My
server is a Intel Xeon Dual-Core 2.8 Ghz with 2 Gb RAM, running Postgres
8.2.4.
In others times recreating the table is Ok, but it can't be the right thing
to do.
I hope anyone have some information.
Thanks,
Rafael Domiciano
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-11-26 14:26 ` Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Martinez @ 2008-11-26 14:26 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
Rafael Domiciano wrote:
Hei
> I need some help or just some hints. I am having problems with vacuum
> full in one table only: "clifatura".
> That table has today around 7 million rows.
>
How big is the database?
> I scheduled on the server a cron job to run VACUUM FULL every day at 23
> P.M, but on the following day, at 8 A.M. vacuum is yet working on
> "clifatura"; so I have to kill vacuum.
>
Do you have any locks in this table/database that prevent the vacuum to
be started?
Why do you use VACUUM FULL everyday? This will lock your database until
it finish. I can count with one hand the times we had to run vacuum full
in our systems in the last 6 years.
Use a normal vacuum analyze if 'autovacuum' is not enough for you.
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
@ 2008-11-26 15:00 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 18:39 ` Re: Vacuum Problems Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
0 siblings, 2 replies; 23+ messages in thread
From: Rafael Domiciano @ 2008-11-26 15:00 UTC (permalink / raw)
To: Rafael Martinez <r.m.guerrero@usit.uio.no>; +Cc: pgsql-admin
The database has around 40 Gb.
If I not use vacuum full everyday the database get very slow.
There is no deadlock on the database.
The vacuum does a clean in the table and in every index of the table
"clifatura". And in the end of the vacuum, seems that vacuum is working hard
on the table (Vacuum is using CPU and memory), but doesn't end.
INFO: limpando "public.clifatura"
INFO: "clifatura": encontrados 63 versões de registros removíveis e 6906667
não-removíveis em 238511 páginas
DETAIL: 0 versões de registros não vigentes não podem ser removidas ainda.
Versões de registros não-removíveis estão no intervalo de 168 a 428 bytes.
Havia 262348 ponteiros de itens não utilizados.
Espaço livre total (incluindo versões de registros removíveis) é 99117652
bytes.
2351 páginas estão ou estarão vazias, incluindo 0 no fim da tabela.
95651 páginas contendo 87437816 bytes livres são destinos potenciais.
CPU 2.86s/1.08u sec elapsed 92.21 sec.
INFO: índice "caeindclf_01" agora contém 6906667 versões de registros em
39824 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.58s/3.18u sec elapsed 26.82 sec.
INFO: índice "caeindclf_03" agora contém 6906667 versões de registros em
43174 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.64s/3.18u sec elapsed 23.62 sec.
INFO: índice "caeindclf_04" agora contém 6906667 versões de registros em
36346 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.50s/2.63u sec elapsed 19.97 sec.
INFO: índice "caeindclf_05" agora contém 6906667 versões de registros em
35965 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.56s/3.06u sec elapsed 12.40 sec.
INFO: índice "caeindclf_06" agora contém 6906667 versões de registros em
32085 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.49s/3.08u sec elapsed 10.68 sec.
INFO: índice "caeindclf_11" agora contém 6906667 versões de registros em
28596 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
867 páginas de índice foram removidas, 867 são reutilizáveis.
CPU 0.39s/2.11u sec elapsed 9.40 sec.
INFO: índice "caeindclf_12" agora contém 6906667 versões de registros em
46721 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
287 páginas de índice foram removidas, 287 são reutilizáveis.
CPU 0.57s/2.59u sec elapsed 21.90 sec.
INFO: índice "caeindclf_13" agora contém 6906667 versões de registros em
28658 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
336 páginas de índice foram removidas, 336 são reutilizáveis.
CPU 0.33s/2.19u sec elapsed 17.31 sec.
INFO: índice "caeindclf_16" agora contém 6906667 versões de registros em
16565 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
399 páginas de índice foram removidas, 399 são reutilizáveis.
CPU 0.18s/1.88u sec elapsed 13.29 sec.
INFO: índice "caeindclf_17" agora contém 6906667 versões de registros em
28676 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
1433 páginas de índice foram removidas, 1433 são reutilizáveis.
CPU 0.26s/2.00u sec elapsed 11.38 sec.
INFO: índice "caeindclf_14" agora contém 6906667 versões de registros em
34523 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
256 páginas de índice foram removidas, 256 são reutilizáveis.
CPU 0.34s/2.31u sec elapsed 14.83 sec.
INFO: índice "caeindclf_15" agora contém 6906667 versões de registros em
31569 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
109 páginas de índice foram removidas, 109 são reutilizáveis.
CPU 0.34s/2.43u sec elapsed 14.05 sec.
INFO: índice "caeindclf_18" agora contém 6906667 versões de registros em
31570 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
451 páginas de índice foram removidas, 451 são reutilizáveis.
CPU 0.31s/2.41u sec elapsed 17.53 sec.
INFO: índice "clifatura_pkey" agora contém 6906667 versões de registros em
15833 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
0 páginas de índice foram removidas, 0 são reutilizáveis.
CPU 0.17s/2.12u sec elapsed 11.04 sec.
INFO: índice "caeindclf_10" agora contém 6906667 versões de registros em
20537 páginas
DETAIL: 63 versões de registros de índices foram apagadas.
1035 páginas de índice foram removidas, 1035 são reutilizáveis.
CPU 0.27s/1.97u sec elapsed 13.14 sec.
2008/11/26 Rafael Martinez <r.m.guerrero@usit.uio.no>
> Rafael Domiciano wrote:
>
> Hei
>
> > I need some help or just some hints. I am having problems with vacuum
> > full in one table only: "clifatura".
> > That table has today around 7 million rows.
> >
>
> How big is the database?
>
> > I scheduled on the server a cron job to run VACUUM FULL every day at 23
> > P.M, but on the following day, at 8 A.M. vacuum is yet working on
> > "clifatura"; so I have to kill vacuum.
> >
>
> Do you have any locks in this table/database that prevent the vacuum to
> be started?
>
> Why do you use VACUUM FULL everyday? This will lock your database until
> it finish. I can count with one hand the times we had to run vacuum full
> in our systems in the last 6 years.
>
> Use a normal vacuum analyze if 'autovacuum' is not enough for you.
>
> --
> Rafael Martinez, <r.m.guerrero@usit.uio.no>
> Center for Information Technology Services
> University of Oslo, Norway
>
> PGP Public Key: http://folk.uio.no/rafael/
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-11-26 15:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-11-26 15:40 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
2008/11/26 Rafael Domiciano <rafael.domiciano@gmail.com>:
> The database has around 40 Gb.
> If I not use vacuum full everyday the database get very slow.
>
> There is no deadlock on the database.
You didn't mention if you were using autovacuum or not. You also
didn't mention whether or not you'd tried regular vacuums if for some
reason you can't use autovacuum. Vacuum full is very intrusive and
can cause index bloat. It's more of a repair operation than a routine
maintenance one.
What do the last 10 or so lines of vacuum verbose run as a super user say?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-11-26 17:21 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 17:33 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 2 replies; 23+ messages in thread
From: Rafael Domiciano @ 2008-11-26 17:21 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
I'm not using autovacuum. Regular vacuum goes ok.To see the last 10 lines of
verbose i will need to run vacuum tonight
If a run a reindex before the vacuum full, increase the "speed" of doing
vacuum? I found something about it googling.
2008/11/26 Scott Marlowe <scott.marlowe@gmail.com>
> 2008/11/26 Rafael Domiciano <rafael.domiciano@gmail.com>:
> > The database has around 40 Gb.
> > If I not use vacuum full everyday the database get very slow.
> >
> > There is no deadlock on the database.
>
> You didn't mention if you were using autovacuum or not. You also
> didn't mention whether or not you'd tried regular vacuums if for some
> reason you can't use autovacuum. Vacuum full is very intrusive and
> can cause index bloat. It's more of a repair operation than a routine
> maintenance one.
>
> What do the last 10 or so lines of vacuum verbose run as a super user say?
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-11-26 17:33 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2008-11-26 17:33 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Wed, Nov 26, 2008 at 10:21 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> I'm not using autovacuum. Regular vacuum goes ok.
> To see the last 10 lines of verbose i will need to run vacuum tonight
> If a run a reindex before the vacuum full, increase the "speed" of doing
> vacuum? I found something about it googling.
You should look into enabling autovac. You can set the sleep
parameter to 10 or 20 to keep it from hogging your I/O bandwidth.
Also, reindex AFTER the vacuum full, not before.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-11-26 19:54 ` Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Matthew T. O'Connor @ 2008-11-26 19:54 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
Rafael Domiciano wrote:
> I'm not using autovacuum. Regular vacuum goes ok.
> To see the last 10 lines of verbose i will need to run vacuum tonight
>
> If a run a reindex before the vacuum full, increase the "speed" of
> doing vacuum? I found something about it googling.
It might help a bit, but by the end of VACUUM FULL you would need to run
reindex again as VACUUM FULL tends to cause a lot of index bloat. It
is normal for tables to have some slack space, so if you do a regular
vacuum every day (or let autovacuum) it's normal for the table to be a
bit bigger than after a VACUUM FULL, but they should ready steady state
and stop growing.
You may also want to look into using CLUSTER it will rewrite the whole
table and is typically much more efficient that VACUUM FULL, but it
requires 2x disk space while running.
Matt
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
@ 2008-11-26 21:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-11-26 21:04 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Rafael Domiciano <rafael.domiciano@gmail.com>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew@zeut.net> wrote:
> Rafael Domiciano wrote:
>>
>> I'm not using autovacuum. Regular vacuum goes ok.
>> To see the last 10 lines of verbose i will need to run vacuum tonight
>>
>> If a run a reindex before the vacuum full, increase the "speed" of doing
>> vacuum? I found something about it googling.
>
> It might help a bit, but by the end of VACUUM FULL you would need to run
> reindex again as VACUUM FULL tends to cause a lot of index bloat. It is
> normal for tables to have some slack space, so if you do a regular vacuum
> every day (or let autovacuum) it's normal for the table to be a bit bigger
> than after a VACUUM FULL, but they should ready steady state and stop
> growing.
But there are certain use cases that would be classified as
pathological in nature, that you can't handle with regular vacuum.
It's only when you've proven that that's your case, and you can't
program around it, that you should start using vacuum full though.
vacuum full is so expensive in terms of time the system is largely
unusable combined with the need to run reindex or replace the whole
thing with cluster, that if regular or autovacuum can handle the load,
then that's what you do.
I've only seen cases where things like large imports were using a
shared table where it would get bloated insanely if three or four
imports were running at the same time with the occasional update with
no where clause. Since you can't truncate the table, because it's
shared with other imports, you have to vacuum it, but if you bloat it
by 10x or 100x normal size in 30 seconds, no amount of regular
vacuuming will help.
So, users need to understand why they're always asked if they're
running autovacuum or not. It's like asking someone with a
nonfunctioning dryer if they've cleaned the lint trap. It's just
something we assume someone should try first unless there's a good
reason not to. Because it does work so well most of the time. I run
autovacuum. I also email myself the output from vacuum verbose every
week, to look through and see how the tables are looking. A quick
look near the end tells you if you're mostly ok, and quick
investigation can find bloated tables pretty fast.
So, for the OP, have you tried autovacuum, and why aren't you using
it. Most of the time people aren't running it it's for erroneous
reasons.
Also, look into updating to 8.3 or above. With its HOT update
mechanism, and autovacuum enabled by default it handles these
situations quite easily.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-08 13:04 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-08 13:04 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
Hello guys,
I tried to modify my vacuum routine, and started to only run vacuum verbose
analyze diary followed by a reindex weekly.
But I still having problems in my database. The uptime database is hard to
stay below 10.
I'm thinking that my hardware is not more good as it was sometime ago.
The machine is a: 2 x Intel Xeon Dual-Core 2.3 GHz, 2 Gb RAM. The load on
machine is about at 10000 transactions / m
Maybe I need more RAM memory?
2008/11/26 Scott Marlowe <scott.marlowe@gmail.com>
> On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew@zeut.net>
> wrote:
> > Rafael Domiciano wrote:
> >>
> >> I'm not using autovacuum. Regular vacuum goes ok.
> >> To see the last 10 lines of verbose i will need to run vacuum tonight
> >>
> >> If a run a reindex before the vacuum full, increase the "speed" of doing
> >> vacuum? I found something about it googling.
> >
> > It might help a bit, but by the end of VACUUM FULL you would need to run
> > reindex again as VACUUM FULL tends to cause a lot of index bloat. It is
> > normal for tables to have some slack space, so if you do a regular vacuum
> > every day (or let autovacuum) it's normal for the table to be a bit
> bigger
> > than after a VACUUM FULL, but they should ready steady state and stop
> > growing.
>
> But there are certain use cases that would be classified as
> pathological in nature, that you can't handle with regular vacuum.
> It's only when you've proven that that's your case, and you can't
> program around it, that you should start using vacuum full though.
> vacuum full is so expensive in terms of time the system is largely
> unusable combined with the need to run reindex or replace the whole
> thing with cluster, that if regular or autovacuum can handle the load,
> then that's what you do.
>
> I've only seen cases where things like large imports were using a
> shared table where it would get bloated insanely if three or four
> imports were running at the same time with the occasional update with
> no where clause. Since you can't truncate the table, because it's
> shared with other imports, you have to vacuum it, but if you bloat it
> by 10x or 100x normal size in 30 seconds, no amount of regular
> vacuuming will help.
>
> So, users need to understand why they're always asked if they're
> running autovacuum or not. It's like asking someone with a
> nonfunctioning dryer if they've cleaned the lint trap. It's just
> something we assume someone should try first unless there's a good
> reason not to. Because it does work so well most of the time. I run
> autovacuum. I also email myself the output from vacuum verbose every
> week, to look through and see how the tables are looking. A quick
> look near the end tells you if you're mostly ok, and quick
> investigation can find bloated tables pretty fast.
>
> So, for the OP, have you tried autovacuum, and why aren't you using
> it. Most of the time people aren't running it it's for erroneous
> reasons.
>
> Also, look into updating to 8.3 or above. With its HOT update
> mechanism, and autovacuum enabled by default it handles these
> situations quite easily.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-08 15:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-12-08 15:35 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello guys,
> I tried to modify my vacuum routine, and started to only run vacuum verbose
> analyze diary followed by a reindex weekly.
> But I still having problems in my database. The uptime database is hard to
> stay below 10.
> I'm thinking that my hardware is not more good as it was sometime ago.
> The machine is a: 2 x Intel Xeon Dual-Core 2.3 GHz, 2 Gb RAM. The load on
> machine is about at 10000 transactions / m
> Maybe I need more RAM memory?
Likely you need more hard drives and / or a quality caching RAID
controller. What's your I/O subsystem look like now?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-08 16:11 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-08 16:11 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
How I do to see my I/O subsystem? vmstat? If so, follow:[root@postgres ~]#
vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id
wa st
0 13 127728 58020 19072 1332076 3 2 3 1 1 7 11 3 52
33 0
1 13 127728 56488 19156 1332456 0 0 644 168 2534 1528 5 3 48
44 0
2 10 127728 55644 19216 1333136 0 0 700 640 1989 1723 5 5 46
44 0
0 12 127728 53252 19268 1334640 0 0 864 796 2351 1833 5 4 47
43 0
0 13 127728 53016 19296 1334208 0 0 2040 452 2277 2126 6 5 40
49 0
0 11 127728 52944 19224 1331508 0 0 1208 484 2306 1421 5 3 21
71 0
1 15 127728 56628 19016 1331012 0 0 1620 944 2410 2499 7 5 24
64 0
1 12 127728 55360 19032 1332500 0 0 1040 592 2423 2074 6 5 7
82 0
2 9 127728 54592 19068 1333344 4 0 1392 604 2695 2431 8 8 22
63 0
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id
wa st
4 8 127728 54632 19084 1334676 0 0 892 1164 2530 2147 5 4 53
38 0
0 10 127728 53256 19064 1333148 0 0 920 208 3119 2765 11 5 15
68 0
0 10 127728 54296 18856 1331404 0 0 756 572 2419 1412 7 3 40
50 0
1 9 127728 57544 18872 1332012 0 0 816 800 2577 2390 8 7 27
59 0
The Hard Drive is a SATA-II 150 Gb, dedicate to postgres service.
2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
> <rafael.domiciano@gmail.com> wrote:
> > Hello guys,
> > I tried to modify my vacuum routine, and started to only run vacuum
> verbose
> > analyze diary followed by a reindex weekly.
> > But I still having problems in my database. The uptime database is hard
> to
> > stay below 10.
> > I'm thinking that my hardware is not more good as it was sometime ago.
> > The machine is a: 2 x Intel Xeon Dual-Core 2.3 GHz, 2 Gb RAM. The load on
> > machine is about at 10000 transactions / m
> > Maybe I need more RAM memory?
>
> Likely you need more hard drives and / or a quality caching RAID
> controller. What's your I/O subsystem look like now?
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-08 16:17 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-12-08 16:17 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Mon, Dec 8, 2008 at 9:11 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> How I do to see my I/O subsystem? vmstat? If so, follow:
No, I mean, how many drives do you have, what kind of RAID controller,
if any, how they're configured, and so on. :) Sorry wasn't really
clear there was I?
The vmstat numbers are horrible by the way, assuming they're in 1k
blocks, you're reading and writing at < 1 Meg a second. It almost
looks like a degraded RAID-5 array.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-08 16:29 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-08 16:29 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
1 Drive Only. This server has no RAID.Do you think that the I/O is very high
and I'm needing a RAID?!
2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Dec 8, 2008 at 9:11 AM, Rafael Domiciano
> <rafael.domiciano@gmail.com> wrote:
> > How I do to see my I/O subsystem? vmstat? If so, follow:
>
> No, I mean, how many drives do you have, what kind of RAID controller,
> if any, how they're configured, and so on. :) Sorry wasn't really
> clear there was I?
>
> The vmstat numbers are horrible by the way, assuming they're in 1k
> blocks, you're reading and writing at < 1 Meg a second. It almost
> looks like a degraded RAID-5 array.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-08 16:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 17:28 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-12-08 16:35 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Mon, Dec 8, 2008 at 9:29 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> 1 Drive Only. This server has no RAID.
> Do you think that the I/O is very high and I'm needing a RAID?!
Not necessarily. Like I said, my laptop currently is about 25 to 30
times faster writing to disk than your server. So, I think something
is wrong. Try doing this, run vmstat 1, and while that's running, in
another window, do something like:
time dd if=/dev/zero of=/mnt/myslowdrive/testfile bs=1000000 count=500
time dd of=/dev/null if=/mnt/myslowdrive/testfile
and see what vmstat says while that's running, and how long it takes.
I'm guessing something is very wrong and you'll get really low
numbers.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-08 17:28 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-09 10:58 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-12-08 17:28 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Here's the output. While the process was running my database get sometime
> without doing anything.
> You said that I probably get low numbers, but what numbers?
We're looking for MB/s and the bi/bo fields in bmstat (blocks in / blocks out)
> [root@postgres banco]# time dd if=/dev/zero of=/banco/testfile bs=1000000
> count=500
> 500+0 records in
> 500+0 records out
> 500000000 bytes (500 MB) copied, 85,4485 seconds, 5,9 MB/s
> real 1m25.451s
> user 0m0.003s
> sys 0m1.617s
> [root@postgres banco]# time dd of=/dev/null if=/banco/testfile
> 976562+1 records in
> 976562+1 records out
> 500000000 bytes (500 MB) copied, 47,5025 seconds, 10,5 MB/s
> real 0m47.543s
> user 0m0.457s
> sys 0m1.470s
> And the vmstat output:
>
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy id
> wa st
> 0 8 111760 56432 19812 1305692 0 0 744 832 2488 1518 5 3 46
> 46 0
Here, the bi/bo numbers are 768 in and 832 out. That's k/second we're
looking at.
Lots of the same numbers cut out.
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy id
> wa st
> 0 6 111760 55452 18528 1296412 0 0 88 25040 1843 1436 2 3 34
> 61 0
> 1 7 111760 52352 18540 1296472 0 0 36 15220 1467 944 3 2 27
> 68 0
> 0 6 111760 54152 18552 1296568 0 0 88 42296 1609 822 1 2 38
> 59 0
> 1 8 111760 52412 18580 1296752 0 0 172 27052 1567 950 2 3 27
> 69 0
> 0 6 111724 53904 18632 1297008 320 0 580 1504 1902 2320 5 6 23
> 66 0
> 1 6 111724 54280 18660 1297200 4 0 200 1060 2014 1783 5 3 44
> 48 0
> 1 7 111676 51388 16340 1302348 8 0 156 1212 1684 848 2 2 53
> 42 0
> 1 6 111668 55040 14864 1301048 0 4 152 46328 1595 5108 1 5 40
> 54 0
Now we're showing that we can write to the disk at 25 to 42 Megs a
second, not too bad. But it looks really bursty, like it can sustain
this throughput for only a few seconds. Try writing a larger file
and run vmstat 10 or 60 as well and see what the average over a longer
time with a larger file is. I get a feeling your machine has a hard
time sustaining throughput for some reason.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 17:28 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-09 10:58 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-16 17:14 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-09 10:58 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
Maybe Could be Check-Points? I have changed the parameters sometime ago.
Follow:
effective_cache_size = 200MB
checkpoint_segments = 40 # in logfile segments, min 1, 16MB
each
checkpoint_timeout = 3min # range 30s-1h
#bgwriter_delay = 200ms # 10-10000ms between rounds
bgwriter_lru_percent = 16.0 # 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages = 170 # 0-1000 buffers max written/round
bgwriter_all_percent = 7.0 # 0-100% of all buffers
scanned/round
bgwriter_all_maxpages = 400 # 0-1000 buffers max written/round
Now I'm having the following problem on vacuum:
vacuumdb: vacuuming of database "Postgres" failed: ERRO: multiple active
vacuums for index "PartInd_replicacao_historico_sl_log_2-node-1".
But there's no anymore vacuum running.
2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano
> <rafael.domiciano@gmail.com> wrote:
> > Here's the output. While the process was running my database get sometime
> > without doing anything.
> > You said that I probably get low numbers, but what numbers?
>
> We're looking for MB/s and the bi/bo fields in bmstat (blocks in / blocks
> out)
>
> > [root@postgres banco]# time dd if=/dev/zero of=/banco/testfile
> bs=1000000
> > count=500
> > 500+0 records in
> > 500+0 records out
> > 500000000 bytes (500 MB) copied, 85,4485 seconds, 5,9 MB/s
> > real 1m25.451s
> > user 0m0.003s
> > sys 0m1.617s
> > [root@postgres banco]# time dd of=/dev/null if=/banco/testfile
> > 976562+1 records in
> > 976562+1 records out
> > 500000000 bytes (500 MB) copied, 47,5025 seconds, 10,5 MB/s
> > real 0m47.543s
> > user 0m0.457s
> > sys 0m1.470s
> > And the vmstat output:
> >
> > procs -----------memory---------- ---swap-- -----io---- --system--
> > -----cpu------
> > r b swpd free buff cache si so bi bo in cs us sy
> id
> > wa st
> > 0 8 111760 56432 19812 1305692 0 0 744 832 2488 1518 5 3
> 46
> > 46 0
>
> Here, the bi/bo numbers are 768 in and 832 out. That's k/second we're
> looking at.
>
> Lots of the same numbers cut out.
>
> > procs -----------memory---------- ---swap-- -----io---- --system--
> > -----cpu------
> > r b swpd free buff cache si so bi bo in cs us sy
> id
> > wa st
> > 0 6 111760 55452 18528 1296412 0 0 88 25040 1843 1436 2 3
> 34
> > 61 0
> > 1 7 111760 52352 18540 1296472 0 0 36 15220 1467 944 3 2
> 27
> > 68 0
> > 0 6 111760 54152 18552 1296568 0 0 88 42296 1609 822 1 2
> 38
> > 59 0
> > 1 8 111760 52412 18580 1296752 0 0 172 27052 1567 950 2 3
> 27
> > 69 0
> > 0 6 111724 53904 18632 1297008 320 0 580 1504 1902 2320 5 6
> 23
> > 66 0
> > 1 6 111724 54280 18660 1297200 4 0 200 1060 2014 1783 5 3
> 44
> > 48 0
> > 1 7 111676 51388 16340 1302348 8 0 156 1212 1684 848 2 2
> 53
> > 42 0
> > 1 6 111668 55040 14864 1301048 0 4 152 46328 1595 5108 1 5
> 40
> > 54 0
>
> Now we're showing that we can write to the disk at 25 to 42 Megs a
> second, not too bad. But it looks really bursty, like it can sustain
> this throughput for only a few seconds. Try writing a larger file
> and run vmstat 10 or 60 as well and see what the average over a longer
> time with a larger file is. I get a feeling your machine has a hard
> time sustaining throughput for some reason.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 17:28 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-09 10:58 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-16 17:14 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-16 21:38 ` Re: Vacuum Problems Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-16 17:14 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
I think that I resolved I my problems with vacuum.I started the autovacuum
and daily runs vacuum verbose analyze, and weekly Reindex.
I'm monitoring the database now.
But, my database continues slow.
Googling a while I started to read something about pdflush and dirty_ratio.
Executing the command (while [ 1 ]; do cat /proc/meminfo | grep Dirty; sleep
1; done) I get:
Dirty: 8912 kB
Dirty: 5092 kB
Dirty: 5912 kB
Dirty: 6024 kB
Dirty: 6472 kB
With the time the Dirty Values increase and then my server does not do any
query, or response, even "startup" stay on queue. So, Dirty values goes down
and the server continues to proccess the queries and give response:
Dirty: 50024 kB
Dirty: 50684 kB
Dirty: 49888 kB
Dirty: 44884 kB
Dirty: 16888 kB
Dirty: 8884 kB
I set the dirty_ratio = 30 and the dirty_background_ratio = 1.
I don't know what more to do to solve this slow down problem.
Somebody have any ideia?
Thnks,
Rafael Domiciano
2008/12/9 Rafael Domiciano <rafael.domiciano@gmail.com>
> Maybe Could be Check-Points? I have changed the parameters sometime ago.
> Follow:
> effective_cache_size = 200MB
>
> checkpoint_segments = 40 # in logfile segments, min 1, 16MB
> each
> checkpoint_timeout = 3min # range 30s-1h
>
> #bgwriter_delay = 200ms # 10-10000ms between rounds
> bgwriter_lru_percent = 16.0 # 0-100% of LRU buffers
> scanned/round
> bgwriter_lru_maxpages = 170 # 0-1000 buffers max written/round
> bgwriter_all_percent = 7.0 # 0-100% of all buffers
> scanned/round
> bgwriter_all_maxpages = 400 # 0-1000 buffers max written/round
>
> Now I'm having the following problem on vacuum:
> vacuumdb: vacuuming of database "Postgres" failed: ERRO: multiple active
> vacuums for index "PartInd_replicacao_historico_sl_log_2-node-1".
>
> But there's no anymore vacuum running.
>
> 2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
>
>> On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano
>>
>> <rafael.domiciano@gmail.com> wrote:
>> > Here's the output. While the process was running my database get
>> sometime
>> > without doing anything.
>> > You said that I probably get low numbers, but what numbers?
>>
>> We're looking for MB/s and the bi/bo fields in bmstat (blocks in / blocks
>> out)
>>
>> > [root@postgres banco]# time dd if=/dev/zero of=/banco/testfile
>> bs=1000000
>> > count=500
>> > 500+0 records in
>> > 500+0 records out
>> > 500000000 bytes (500 MB) copied, 85,4485 seconds, 5,9 MB/s
>> > real 1m25.451s
>> > user 0m0.003s
>> > sys 0m1.617s
>> > [root@postgres banco]# time dd of=/dev/null if=/banco/testfile
>> > 976562+1 records in
>> > 976562+1 records out
>> > 500000000 bytes (500 MB) copied, 47,5025 seconds, 10,5 MB/s
>> > real 0m47.543s
>> > user 0m0.457s
>> > sys 0m1.470s
>> > And the vmstat output:
>> >
>> > procs -----------memory---------- ---swap-- -----io---- --system--
>> > -----cpu------
>> > r b swpd free buff cache si so bi bo in cs us sy
>> id
>> > wa st
>> > 0 8 111760 56432 19812 1305692 0 0 744 832 2488 1518 5
>> 3 46
>> > 46 0
>>
>> Here, the bi/bo numbers are 768 in and 832 out. That's k/second we're
>> looking at.
>>
>> Lots of the same numbers cut out.
>>
>> > procs -----------memory---------- ---swap-- -----io---- --system--
>> > -----cpu------
>> > r b swpd free buff cache si so bi bo in cs us sy
>> id
>> > wa st
>> > 0 6 111760 55452 18528 1296412 0 0 88 25040 1843 1436 2
>> 3 34
>> > 61 0
>> > 1 7 111760 52352 18540 1296472 0 0 36 15220 1467 944 3
>> 2 27
>> > 68 0
>> > 0 6 111760 54152 18552 1296568 0 0 88 42296 1609 822 1
>> 2 38
>> > 59 0
>> > 1 8 111760 52412 18580 1296752 0 0 172 27052 1567 950 2
>> 3 27
>> > 69 0
>> > 0 6 111724 53904 18632 1297008 320 0 580 1504 1902 2320 5
>> 6 23
>> > 66 0
>> > 1 6 111724 54280 18660 1297200 4 0 200 1060 2014 1783 5
>> 3 44
>> > 48 0
>> > 1 7 111676 51388 16340 1302348 8 0 156 1212 1684 848 2
>> 2 53
>> > 42 0
>> > 1 6 111668 55040 14864 1301048 0 4 152 46328 1595 5108 1
>> 5 40
>> > 54 0
>>
>> Now we're showing that we can write to the disk at 25 to 42 Megs a
>> second, not too bad. But it looks really bursty, like it can sustain
>> this throughput for only a few seconds. Try writing a larger file
>> and run vmstat 10 or 60 as well and see what the average over a longer
>> time with a larger file is. I get a feeling your machine has a hard
>> time sustaining throughput for some reason.
>>
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 17:28 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-09 10:58 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-16 17:14 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-16 21:38 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 23+ messages in thread
From: Alvaro Herrera @ 2008-12-16 21:38 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
Rafael Domiciano escribió:
> 2008/12/9 Rafael Domiciano <rafael.domiciano@gmail.com>
>
> > Maybe Could be Check-Points? I have changed the parameters sometime ago.
> > Follow:
> > effective_cache_size = 200MB
> >
> > checkpoint_segments = 40 # in logfile segments, min 1, 16MB
> > each
> > checkpoint_timeout = 3min # range 30s-1h
checkpoint_timeout 3 minutes? I think you want to increase that a lot.
> > Now I'm having the following problem on vacuum:
> > vacuumdb: vacuuming of database "Postgres" failed: ERRO: multiple active
> > vacuums for index "PartInd_replicacao_historico_sl_log_2-node-1".
> >
> > But there's no anymore vacuum running.
Something's very wrong here. This shouldn't ever happen. I haven't
followed this thread but you really should investigate this.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-12-08 15:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2008-12-08 15:35 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello guys,
> I tried to modify my vacuum routine, and started to only run vacuum verbose
> analyze diary followed by a reindex weekly.
Have you tried running autovacuum with a naptime of 10 or 20?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-12-08 16:11 ` Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Rafael Domiciano @ 2008-12-08 16:11 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Rafael Martinez <r.m.guerrero@usit.uio.no>; pgsql-admin
No, I'm gonna to try
2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
> <rafael.domiciano@gmail.com> wrote:
> > Hello guys,
> > I tried to modify my vacuum routine, and started to only run vacuum
> verbose
> > analyze diary followed by a reindex weekly.
>
> Have you tried running autovacuum with a naptime of 10 or 20?
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Vacuum Problems
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-11-26 18:39 ` Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
1 sibling, 0 replies; 23+ messages in thread
From: Jan-Peter Seifert @ 2008-11-26 18:39 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
Rafael Domiciano wrote:
> The database has around 40 Gb.
>
> If I not use vacuum full everyday the database get very slow.
>
> There is no deadlock on the database.
> The vacuum does a clean in the table and in every index of the table
> "clifatura". And in the end of the vacuum, seems that vacuum is working
> hard on the table (Vacuum is using CPU and memory), but doesn't end.
Well - maybe an index on the table is broken? I did once run a script on
a database to insert a rather big amount of data. During reindexing the
db ran out of diskspace, but obviously it was freed after that so I
missed the really important error message. I just wondered why the
server had restarted in the meantime. A vacuum on the table in question
took forever. I'm not sure whether I just did a reindex or a dump and
restore to fix this. Just a suggestion. Good luck.
Peter
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2008-12-16 21:38 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-24 09:07 VACUUM problems Martins Zarins <mark@vestnesis.lv>
2001-08-24 13:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-11-26 13:43 Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 14:26 ` Re: Vacuum Problems Rafael Martinez <r.m.guerrero@usit.uio.no>
2008-11-26 15:00 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 15:40 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 17:21 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 17:33 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-11-26 19:54 ` Re: Vacuum Problems Matthew T. O'Connor <matthew@zeut.net>
2008-11-26 21:04 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 13:04 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:17 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:29 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-08 16:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 17:28 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-09 10:58 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-16 17:14 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-12-16 21:38 ` Re: Vacuum Problems Alvaro Herrera <alvherre@commandprompt.com>
2008-12-08 15:35 ` Re: Vacuum Problems Scott Marlowe <scott.marlowe@gmail.com>
2008-12-08 16:11 ` Re: Vacuum Problems Rafael Domiciano <rafael.domiciano@gmail.com>
2008-11-26 18:39 ` Re: Vacuum Problems Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox