pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres performance problem
36+ messages / 21 participants
[nested] [flat]
* Postgres performance problem
@ 2007-08-27 11:41 Ruben Rubio <ruben@rentalia.com>
0 siblings, 1 reply; 36+ messages in thread
From: Ruben Rubio @ 2007-08-27 11:41 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
Note: I have already vacumm full. It does not solve the problem.
I have a postgres 8.1 database. In the last days I have half traffic
than 4 weeks ago, and resources usage is twice. The resource monitor
graphs also shows hight peaks (usually there is not peaks)
The performarce is getting poor with the time.
Im not able to find the problem, seems there is not slow querys ( I have
log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
Server is HP, and seems there is not hardware problems detected.
Any ideas to debug it?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG0rh5Io1XmbAXRboRAuaTAJ4tDVHUMN5YRBdWxT//kPAfBvYqRACgvLst
rJF3dmxzWHDOWB8yQwTyvpw=
=2ic9
-----END PGP SIGNATURE-----
Attachments:
ruben.vcf (text/x-vcard, 244B) download
begin:vcard
fn:Ruben Rubio
n:Rubio;Ruben
org:Rentalia Holidays S.L
adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain
email;internet:ruben@rentalia.com
tel;work:+34915233104
url:http://www.rentalia.com
version:2.1
end:vcard
* Re: Postgres performance problem
@ 2007-08-27 12:33 Chris Mair <chris@1006.org>
parent: Ruben Rubio <ruben@rentalia.com>
0 siblings, 2 replies; 36+ messages in thread
From: Chris Mair @ 2007-08-27 12:33 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: pgsql-performance
> Hi,
>
> Note: I have already vacumm full. It does not solve the problem.
>
> I have a postgres 8.1 database. In the last days I have half traffic
> than 4 weeks ago, and resources usage is twice. The resource monitor
> graphs also shows hight peaks (usually there is not peaks)
>
> The performarce is getting poor with the time.
>
> Im not able to find the problem, seems there is not slow querys ( I have
> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
>
> Server is HP, and seems there is not hardware problems detected.
>
> Any ideas to debug it?
Hi,
first of all: let us know the exact version of PG and the OS.
If performance is getting worse, there ususally is some bloat
envolved. Not vacuuming aggressivly enough, might be the most
common cause. Do you autovacuum or vacuum manually?
Tell us more...
Bye,
Chris.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Postgres performance problem
@ 2007-08-27 12:52 ruben@rentalia.com
parent: Chris Mair <chris@1006.org>
1 sibling, 0 replies; 36+ messages in thread
From: ruben@rentalia.com @ 2007-08-27 12:52 UTC (permalink / raw)
To: Chris Mair <chris@1006.org>; +Cc: pgsql-performance
SO: CentOS release 4.3 (Final) (kernel: 2.6.9-34.0.1.ELsmp)
Postgres: 8.1.3
I had some problems before with autovacuum. So, Each day I crontab execute:
vacuumdb -f -v --analyze
reindex database vacadb
I saw logs (the output of vacuum and reindex) and there is no errors.
If u need more info, I ll be pleased to tell it here ...
Chris Mair escribió:
>> Hi,
>>
>> Note: I have already vacumm full. It does not solve the problem.
>>
>> I have a postgres 8.1 database. In the last days I have half traffic
>> than 4 weeks ago, and resources usage is twice. The resource monitor
>> graphs also shows hight peaks (usually there is not peaks)
>>
>> The performarce is getting poor with the time.
>>
>> Im not able to find the problem, seems there is not slow querys ( I have
>> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
>>
>> Server is HP, and seems there is not hardware problems detected.
>>
>> Any ideas to debug it?
>
> Hi,
>
> first of all: let us know the exact version of PG and the OS.
>
> If performance is getting worse, there ususally is some bloat
> envolved. Not vacuuming aggressivly enough, might be the most
> common cause. Do you autovacuum or vacuum manually?
> Tell us more...
>
>
> Bye,
> Chris.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Postgres performance problem
@ 2007-08-27 13:29 Bill Moran <wmoran@collaborativefusion.com>
parent: Chris Mair <chris@1006.org>
1 sibling, 2 replies; 36+ messages in thread
From: Bill Moran @ 2007-08-27 13:29 UTC (permalink / raw)
To: Chris Mair <chris@1006.org>; +Cc: Ruben Rubio <ruben@rentalia.com>; pgsql-performance
In response to Chris Mair <chris@1006.org>:
> > Hi,
> >
> > Note: I have already vacumm full. It does not solve the problem.
To jump in here in Chris' defense, regular vacuum is not at all the same
as vacuum full. Periodic vacuum is _much_ preferable to an occasional
vacuum full.
The output of vacuum verbose would have useful information ... are you
exceeding your FSM limits?
Try a reindex on the database. There may be some obscure corner
cases where reindex makes a notable improvement in performance.
> > I have a postgres 8.1 database. In the last days I have half traffic
> > than 4 weeks ago, and resources usage is twice. The resource monitor
> > graphs also shows hight peaks (usually there is not peaks)
Resource monitor graphs? That statement means nothing to me, therefore
I don't know if the information they're providing is useful or accurate,
or even _what_ it is. What, exactly, are these graphs monitoring?
You might want to provide your postgresql.conf.
Have you considered the possibility that the database has simply got more
records and therefore access takes more IO and CPU?
> > The performarce is getting poor with the time.
> >
> > Im not able to find the problem, seems there is not slow querys ( I have
> > log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
> >
> > Server is HP, and seems there is not hardware problems detected.
> >
> > Any ideas to debug it?
>
> Hi,
>
> first of all: let us know the exact version of PG and the OS.
>
> If performance is getting worse, there ususally is some bloat
> envolved. Not vacuuming aggressivly enough, might be the most
> common cause. Do you autovacuum or vacuum manually?
> Tell us more...
>
>
> Bye,
> Chris.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
>
>
>
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Postgres performance problem
@ 2007-08-28 18:36 Anton Melser <melser.anton@gmail.com>
parent: Bill Moran <wmoran@collaborativefusion.com>
1 sibling, 0 replies; 36+ messages in thread
From: Anton Melser @ 2007-08-28 18:36 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Chris Mair <chris@1006.org>; Ruben Rubio <ruben@rentalia.com>; pgsql-performance
Just a random thought/question...
Are you running else on the machine? When you say "resource usage", do
you mean hd space, memory, processor, ???
What are your values in top?
More info...
Cheers
Anton
On 27/08/2007, Bill Moran <wmoran@collaborativefusion.com> wrote:
> In response to Chris Mair <chris@1006.org>:
>
> > > Hi,
> > >
> > > Note: I have already vacumm full. It does not solve the problem.
>
> To jump in here in Chris' defense, regular vacuum is not at all the same
> as vacuum full. Periodic vacuum is _much_ preferable to an occasional
> vacuum full.
>
> The output of vacuum verbose would have useful information ... are you
> exceeding your FSM limits?
>
> Try a reindex on the database. There may be some obscure corner
> cases where reindex makes a notable improvement in performance.
>
> > > I have a postgres 8.1 database. In the last days I have half traffic
> > > than 4 weeks ago, and resources usage is twice. The resource monitor
> > > graphs also shows hight peaks (usually there is not peaks)
>
> Resource monitor graphs? That statement means nothing to me, therefore
> I don't know if the information they're providing is useful or accurate,
> or even _what_ it is. What, exactly, are these graphs monitoring?
>
> You might want to provide your postgresql.conf.
>
> Have you considered the possibility that the database has simply got more
> records and therefore access takes more IO and CPU?
>
> > > The performarce is getting poor with the time.
> > >
> > > Im not able to find the problem, seems there is not slow querys ( I have
> > > log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
> > >
> > > Server is HP, and seems there is not hardware problems detected.
> > >
> > > Any ideas to debug it?
> >
> > Hi,
> >
> > first of all: let us know the exact version of PG and the OS.
> >
> > If performance is getting worse, there ususally is some bloat
> > envolved. Not vacuuming aggressivly enough, might be the most
> > common cause. Do you autovacuum or vacuum manually?
> > Tell us more...
> >
> >
> > Bye,
> > Chris.
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
> >
> >
> >
> >
> >
>
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> ****************************************************************
> IMPORTANT: This message contains confidential information and is
> intended only for the individual named. If the reader of this
> message is not an intended recipient (or the individual
> responsible for the delivery of this message to an intended
> recipient), please be advised that any re-use, dissemination,
> distribution or copying of this message is prohibited. Please
> notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system.
> E-mail transmission cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The
> sender therefore does not accept liability for any errors or
> omissions in the contents of this message, which arise as a
> result of e-mail transmission.
> ****************************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc
This will help you for 99.9% of your problems ...
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: Postgres performance problem
@ 2007-08-30 07:52 ruben@rentalia.com
parent: Bill Moran <wmoran@collaborativefusion.com>
1 sibling, 1 reply; 36+ messages in thread
From: ruben@rentalia.com @ 2007-08-30 07:52 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Chris Mair <chris@1006.org>; pgsql-performance
Bill Moran escribió:
> In response to Chris Mair <chris@1006.org>:
>
>>> Hi,
>>>
>>> Note: I have already vacumm full. It does not solve the problem.
>
> To jump in here in Chris' defense, regular vacuum is not at all the same
> as vacuum full. Periodic vacuum is _much_ preferable to an occasional
> vacuum full.
>
> The output of vacuum verbose would have useful information ... are you
> exceeding your FSM limits?
I think its ok. There is not warning messages on vacuum verbose in the
last year. (I save all logs)
>
> Try a reindex on the database. There may be some obscure corner
> cases where reindex makes a notable improvement in performance.
I do it all days. (i know it is not necessary all days, but I should
avoid problems)
>
>>> I have a postgres 8.1 database. In the last days I have half traffic
>>> than 4 weeks ago, and resources usage is twice. The resource monitor
>>> graphs also shows hight peaks (usually there is not peaks)
>
> Resource monitor graphs? That statement means nothing to me, therefore
> I don't know if the information they're providing is useful or accurate,
> or even _what_ it is. What, exactly, are these graphs monitoring?
I should have explain that. I use "sar" command, I get 1 minute per 5
minutes, and I show the result in a graph. I have been using this one
year, and use to work very well due traffic / resources usage . In last
days started to show thinks that should not.
By the way, the server is two intel dual processor 4Gb ram. It is only
database server.
>
> You might want to provide your postgresql.conf.
>
I have tested these values. It makes sqls faster. I didn't change it in
the last 10 months, The values I changed are:
max_connections = 500
autovacuum = off
shared_buffers = 24576
work_mem = 3072
maintenance_work_mem = 65536
wal_buffers = 1024
checkpoint_segments = 12
checkpoint_warning = 30
effective_cache_size = 225000
random_page_cost = 2
log_min_duration_statement = 1000
> Have you considered the possibility that the database has simply got more
> records and therefore access takes more IO and CPU?
Not possible. All database is in RAM memory. iostat is quiet. And if
that was the problem, it should be in another way. Not in few weeks with
half traffic. (I also have checked a possible attack, automatic "sing
up" or thinks like that ... nothing found)
By other way, there is not "slow sqls". (ok, there is a few slow sqls
but are known slow sqls)
I saw once a postgres sql server 8.1 that had poor performance with sql
s that involve one table because someone change a column data type and
seems it didn't work well. There is not that kind of changes in my
database for 2 months at least, but ... is there any chance that
database is being corrupted? Maybe dump database , delete it and restore
it again may solve the problem ?
>
>>> The performarce is getting poor with the time.
>>>
>>> Im not able to find the problem, seems there is not slow querys ( I have
>>> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
>>>
>>> Server is HP, and seems there is not hardware problems detected.
>>>
>>> Any ideas to debug it?
>> Hi,
>>
>> first of all: let us know the exact version of PG and the OS.
>>
>> If performance is getting worse, there ususally is some bloat
>> envolved. Not vacuuming aggressivly enough, might be the most
>> common cause. Do you autovacuum or vacuum manually?
>> Tell us more...
>>
>>
>> Bye,
>> Chris.
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>>
>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Solved] Postgres performance problem
@ 2007-08-30 09:50 Ruben Rubio <ruben@rentalia.com>
parent: ruben@rentalia.com
0 siblings, 2 replies; 36+ messages in thread
From: Ruben Rubio @ 2007-08-30 09:50 UTC (permalink / raw)
To: ruben@rentalia.com; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Chris Mair <chris@1006.org>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi ...
Seems its solved. But the problem is not found.
As you may know, I do a vacuum full and a reindex database each day. I
have logs that confirm that its done and I can check that everything was
fine.
So, this morning, I stopped the website, I stopped database, started it
again. (I was around 200 days without restarting), then I vacuum
database and reindex it (Same command as everyday) . Restart again, and
run again the website.
Now seems its working fine. But I really does not know where is the
problem. Seems vacuum its not working fine? Maybe database should need
a restart? I really don't know.
Does someone had a similar problem?
Thanks in advance,
Ruben Rubio
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wDLnIW2SfQCfV+Hj
e8PurQb2TOSYDPW545AJ83c=
=dQgM
-----END PGP SIGNATURE-----
Attachments:
ruben.vcf (text/x-vcard, 244B) download
begin:vcard
fn:Ruben Rubio
n:Rubio;Ruben
org:Rentalia Holidays S.L
adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain
email;internet:ruben@rentalia.com
tel;work:+34915233104
url:http://www.rentalia.com
version:2.1
end:vcard
* Re: [Solved] Postgres performance problem
@ 2007-08-30 17:31 Decibel! <decibel@decibel.org>
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 1 reply; 36+ messages in thread
From: Decibel! @ 2007-08-30 17:31 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Chris Mair <chris@1006.org>; pgsql-performance
On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote:
> As you may know, I do a vacuum full and a reindex database each day. I
> have logs that confirm that its done and I can check that everything was
> fine.
>
> So, this morning, I stopped the website, I stopped database, started it
> again. (I was around 200 days without restarting), then I vacuum
> database and reindex it (Same command as everyday) . Restart again, and
> run again the website.
>
> Now seems its working fine. But I really does not know where is the
> problem. Seems vacuum its not working fine? Maybe database should need
> a restart? I really don't know.
No, it sounds to me like you just weren't vacuuming aggressively enough
to keep up with demand.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Solved] Postgres performance problem
@ 2007-08-30 18:07 Richard Huxton <dev@archonet.com>
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 36+ messages in thread
From: Richard Huxton @ 2007-08-30 18:07 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: Ruben Rubio <ruben@rentalia.com>; Bill Moran <wmoran@collaborativefusion.com>; Chris Mair <chris@1006.org>; pgsql-performance
Decibel! wrote:
> On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote:
>> As you may know, I do a vacuum full and a reindex database each day. I
>> have logs that confirm that its done and I can check that everything was
>> fine.
>>
>> So, this morning, I stopped the website, I stopped database, started it
>> again. (I was around 200 days without restarting), then I vacuum
>> database and reindex it (Same command as everyday) . Restart again, and
>> run again the website.
>>
>> Now seems its working fine. But I really does not know where is the
>> problem. Seems vacuum its not working fine? Maybe database should need
>> a restart? I really don't know.
>
> No, it sounds to me like you just weren't vacuuming aggressively enough
> to keep up with demand.
Actually , I think it sounds like a stray long-lived transaction.
Ruben - vacuum can't recover rows if another transaction might be able
to see them. So, if you have a connection that issues BEGIN and sits
there for 200 days you can end up with a lot of bloat in your database.
Now, there's no way to prove that since you've restarted the
database-server, but keep an eye on it.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Solved] Postgres performance problem
@ 2007-08-30 18:08 Mark Lewis <mark.lewis@mir3.com>
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 1 reply; 36+ messages in thread
From: Mark Lewis @ 2007-08-30 18:08 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Chris Mair <chris@1006.org>; pgsql-performance
Perhaps you had a long-running transaction open (probably a buggy or
hung application) that was preventing dead rows from being cleaned up.
Restarting PG closed the offending connection and rolled back the
transaction, which allowed vacuum to clean up all the dead rows.
If you're not running regular VACUUMs at all but are instead exclusively
running VACUUM FULL, then I don't think you would see warnings about
running out of fsm enties, which would explain why you did not notice
the bloat. I haven't confirmed that though, so I might be wrong.
-- Mark Lewis
On Thu, 2007-08-30 at 11:50 +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> Hi ...
>
> Seems its solved. But the problem is not found.
>
> As you may know, I do a vacuum full and a reindex database each day. I
> have logs that confirm that its done and I can check that everything was
> fine.
>
> So, this morning, I stopped the website, I stopped database, started it
> again. (I was around 200 days without restarting), then I vacuum
> database and reindex it (Same command as everyday) . Restart again, and
> run again the website.
>
> Now seems its working fine. But I really does not know where is the
> problem. Seems vacuum its not working fine? Maybe database should need
> a restart? I really don't know.
>
> Does someone had a similar problem?
>
> Thanks in advance,
> Ruben Rubio
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wDLnIW2SfQCfV+Hj
> e8PurQb2TOSYDPW545AJ83c=
> =dQgM
> -----END PGP SIGNATURE-----
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Solved] Postgres performance problem
@ 2007-08-31 19:36 Vivek Khera <vivek@khera.org>
parent: Mark Lewis <mark.lewis@mir3.com>
0 siblings, 0 replies; 36+ messages in thread
From: Vivek Khera @ 2007-08-31 19:36 UTC (permalink / raw)
To: pgsql-performance
On Aug 30, 2007, at 2:08 PM, Mark Lewis wrote:
> If you're not running regular VACUUMs at all but are instead
> exclusively
> running VACUUM FULL, then I don't think you would see warnings about
> running out of fsm enties, which would explain why you did not notice
> the bloat. I haven't confirmed that though, so I might be wrong.
If you run vacuum full, your pages should be full so there should be
very small if any number of pages in the free space map. Thus, there
would be no warnings.
^ permalink raw reply [nested|flat] 36+ messages in thread
* [Again] Postgres performance problem
@ 2007-09-11 06:33 Ruben Rubio <ruben@rentalia.com>
0 siblings, 2 replies; 36+ messages in thread
From: Ruben Rubio @ 2007-09-11 06:33 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I having the same problem I told here a few weeks before. Database is
using too much resources again.
I do a vacumm full each day, but seems it is not working. I am preparing
an update to postgres 8.2.4 (actually I am using at 8.1.3, and tests for
update will need several days)
Last time I had this problem i solved it stopping website, restarting
database, vacuumm it, run again website. But I guess this is going to
happen again.
I would like to detect and solve the problem. Any ideas to detect it?
Thanks in advance,
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG5jbLIo1XmbAXRboRArcpAJ0YvoCT6KWv2fafVAtapu6nwFmKoACcD0uA
zFTx9Wq+2NSxijIf/R8E5f8=
=u0k5
-----END PGP SIGNATURE-----
Attachments:
ruben.vcf (text/x-vcard, 244B) download
begin:vcard
fn:Ruben Rubio
n:Rubio;Ruben
org:Rentalia Holidays S.L
adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain
email;internet:ruben@rentalia.com
tel;work:+34915233104
url:http://www.rentalia.com
version:2.1
end:vcard
* Re: [Again] Postgres performance problem
@ 2007-09-11 07:31 db@zigo.dhs.org
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 1 reply; 36+ messages in thread
From: db@zigo.dhs.org @ 2007-09-11 07:31 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: pgsql-performance
> Last time I had this problem i solved it stopping website, restarting
> database, vacuumm it, run again website. But I guess this is going to
> happen again.
>
> I would like to detect and solve the problem. Any ideas to detect it?
Do you have very long transactions? Maybe some client that is connected
all the time that is idle in transaction?
/Dennis
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-11 07:49 Ruben Rubio <ruben@rentalia.com>
parent: db@zigo.dhs.org
0 siblings, 1 reply; 36+ messages in thread
From: Ruben Rubio @ 2007-09-11 07:49 UTC (permalink / raw)
To: db@zigo.dhs.org; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
db@zigo.dhs.org escribió:
>> Last time I had this problem i solved it stopping website, restarting
>> database, vacuumm it, run again website. But I guess this is going to
>> happen again.
>>
>> I would like to detect and solve the problem. Any ideas to detect it?
>
> Do you have very long transactions? Maybe some client that is connected
> all the time that is idle in transaction?
There should not be long transactions. I ll keep an eye on Idle transactions
I m detecting it using:
echo 'SELECT current_query FROM pg_stat_activity;' |
/usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
>
> /Dennis
>
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG5kiRIo1XmbAXRboRAj3sAKCH21zIhvdvPcmVQG71owiCye96xwCcDPe0
o/aArJF0JjUnTIFd1sMYD+Y=
=6zyY
-----END PGP SIGNATURE-----
Attachments:
ruben.vcf (text/x-vcard, 244B) download
begin:vcard
fn:Ruben Rubio
n:Rubio;Ruben
org:Rentalia Holidays S.L
adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain
email;internet:ruben@rentalia.com
tel;work:+34915233104
url:http://www.rentalia.com
version:2.1
end:vcard
* Re: [Again] Postgres performance problem
@ 2007-09-11 22:52 Decibel! <decibel@decibel.org>
parent: Ruben Rubio <ruben@rentalia.com>
0 siblings, 1 reply; 36+ messages in thread
From: Decibel! @ 2007-09-11 22:52 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: db@zigo.dhs.org; pgsql-performance
On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> db@zigo.dhs.org escribi?:
> >> Last time I had this problem i solved it stopping website, restarting
> >> database, vacuumm it, run again website. But I guess this is going to
> >> happen again.
> >>
> >> I would like to detect and solve the problem. Any ideas to detect it?
> >
> > Do you have very long transactions? Maybe some client that is connected
> > all the time that is idle in transaction?
>
> There should not be long transactions. I ll keep an eye on Idle transactions
>
> I m detecting it using:
>
> echo 'SELECT current_query FROM pg_stat_activity;' |
> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
If you're using VACUUM FULL, you're doing something wrong. :) Run lazy
vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
autovac parameters in half), and make sure your FSM is big enough
(periodic vacuumdb -av | tail is an easy way to check that).
Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
easy for them to seriously bloat.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 07:42 ruben@rentalia.com
parent: Decibel! <decibel@decibel.org>
0 siblings, 1 reply; 36+ messages in thread
From: ruben@rentalia.com @ 2007-09-12 07:42 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: db@zigo.dhs.org; pgsql-performance
Decibel! escribió:
> On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> db@zigo.dhs.org escribi?:
>>>> Last time I had this problem i solved it stopping website, restarting
>>>> database, vacuumm it, run again website. But I guess this is going to
>>>> happen again.
>>>>
>>>> I would like to detect and solve the problem. Any ideas to detect it?
>>> Do you have very long transactions? Maybe some client that is connected
>>> all the time that is idle in transaction?
>> There should not be long transactions. I ll keep an eye on Idle transactions
>>
>> I m detecting it using:
>>
>> echo 'SELECT current_query FROM pg_stat_activity;' |
>> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
>
> If you're using VACUUM FULL, you're doing something wrong. :)
I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
check if vacuum is done properly.(it is)
Run lazy
> vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
> autovac parameters in half), and make sure your FSM is big enough
I checked that there is no warnings about FSM in logs. (also in logs
from vacuum). Is it reliable?
What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
default autovac parameters?
> (periodic vacuumdb -av | tail is an easy way to check that).
I ll keep an eye on it.
>
> Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> easy for them to seriously bloat.
Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
the output averyday and save it into a log, and I can check that it is
done properly.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 18:28 Scott Marlowe <scott.marlowe@gmail.com>
parent: ruben@rentalia.com
0 siblings, 3 replies; 36+ messages in thread
From: Scott Marlowe @ 2007-09-12 18:28 UTC (permalink / raw)
To: ruben@rentalia.com <ruben@rentalia.com>; +Cc: Decibel! <decibel@decibel.org>; db@zigo.dhs.org; pgsql-performance
On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> Decibel! escribió:
> > On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> db@zigo.dhs.org escribi?:
> >>>> Last time I had this problem i solved it stopping website, restarting
> >>>> database, vacuumm it, run again website. But I guess this is going to
> >>>> happen again.
> >>>>
> >>>> I would like to detect and solve the problem. Any ideas to detect it?
> >>> Do you have very long transactions? Maybe some client that is connected
> >>> all the time that is idle in transaction?
> >> There should not be long transactions. I ll keep an eye on Idle transactions
> >>
> >> I m detecting it using:
> >>
> >> echo 'SELECT current_query FROM pg_stat_activity;' |
> >> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
> >
> > If you're using VACUUM FULL, you're doing something wrong. :)
>
> I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
> check if vacuum is done properly.(it is)
Then, like Jim said, you're doing it wrong. Regular vacuum full is
like rebuiling a piece of equipment every night when all it needs is
the filter changed.
> Run lazy
> > vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
> > autovac parameters in half), and make sure your FSM is big enough
>
> I checked that there is no warnings about FSM in logs. (also in logs
> from vacuum). Is it reliable?
>
> What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
> default autovac parameters?
Yep. ( I assume)
> > (periodic vacuumdb -av | tail is an easy way to check that).
>
> I ll keep an eye on it.
>
> >
> > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > easy for them to seriously bloat.
>
> Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> the output averyday and save it into a log, and I can check that it is
> done properly.
Then you're vacuum full is wasted. A reindex accomplishes the same
thing, plus shrinks indexes (vacuum full can bloat indexes).
Just run regular vacuums, preferably by autovacuum, and keep an eye on
the vacuum analyze you run each night to see if your fsm is big
enough.
Occasionally vacuum full is absolutely the right answer. Most the
time it's not.
I'm getting more and more motivated to rewrite the vacuum docs. I
think a rewrite from the ground up might be best... I keep seeing
people doing vacuum full on this list and I'm thinking it's as much
because of the way the docs represent vacuum full as anything. Is
that true for you?
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 18:46 Brian Hurt <bhurt@janestcapital.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 36+ messages in thread
From: Brian Hurt @ 2007-09-12 18:46 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Scott Marlowe wrote:
>I'm getting more and more motivated to rewrite the vacuum docs. I
>think a rewrite from the ground up might be best... I keep seeing
>people doing vacuum full on this list and I'm thinking it's as much
>because of the way the docs represent vacuum full as anything. Is
>that true for you?
>
>
>
It's true for me.
I turned off autovacuum as I was getting occassional hangs, which I
thought were the result of vacuums (and have signifigantly decreased
since I did that), and went nightly vacuum fulls, and vacuum
full/reindex/cluster on the weekends (which I now realize is redundant).
Brian
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 18:56 Mikko Partio <mpartio@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 36+ messages in thread
From: Mikko Partio @ 2007-09-12 18:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: ruben@rentalia.com <ruben@rentalia.com>; Decibel! <decibel@decibel.org>; db@zigo.dhs.org; pgsql-performance
On 9/12/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
> > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > > easy for them to seriously bloat.
> >
> > Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> > the output averyday and save it into a log, and I can check that it is
> > done properly.
>
> Then you're vacuum full is wasted. A reindex accomplishes the same
> thing, plus shrinks indexes (vacuum full can bloat indexes).
Aren't you mixing up REINDEX and CLUSTER?
Regards
MP
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 19:07 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mikko Partio <mpartio@gmail.com>
0 siblings, 2 replies; 36+ messages in thread
From: Scott Marlowe @ 2007-09-12 19:07 UTC (permalink / raw)
To: Mikko Partio <mpartio@gmail.com>; +Cc: ruben@rentalia.com <ruben@rentalia.com>; Decibel! <decibel@decibel.org>; db@zigo.dhs.org; pgsql-performance
On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>
>
> On 9/12/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> > > > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > > > easy for them to seriously bloat.
> > >
> > > Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> > > the output averyday and save it into a log, and I can check that it is
> > > done properly.
> >
> > Then you're vacuum full is wasted. A reindex accomplishes the same
> > thing, plus shrinks indexes (vacuum full can bloat indexes).
>
> Aren't you mixing up REINDEX and CLUSTER?
I don't think so. reindex (which runs on tables and indexes, so the
name is a bit confusing, I admit) basically was originally a "repair"
operation that rewrote the whole relation and wasn't completely
transaction safe (way back, 7.2 days or so I think). Due to the
issues with vacuum full bloating indexes, and being slowly replaced by
regular vacuum, reindex received some attention to make it transaction
/ crash safe and has kind of take the place of vacuum full in terms of
"how to fix bloated objects".
cluster, otoh, rewrites the table into index order.
Either one does what a vacuum full did / does, but generally does it better.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 19:19 Frank Schoep <frank@ffnn.nl>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 2 replies; 36+ messages in thread
From: Frank Schoep @ 2007-09-12 19:19 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote:
> On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>> …
>> Aren't you mixing up REINDEX and CLUSTER?
>
> …
> Either one does what a vacuum full did / does, but generally does
> it better.
On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE
I'd like to ask if CLUSTER is safe to run on a table that is in
active use.
After updating my maintenance scripts from a VACUUM FULL (add me to
the list) to CLUSTER (which improves performance a lot) I noticed I
was getting "could not open relation …" errors in the log while the
scripts ran so I reverted the change. This was on 8.1.9.
Am I hitting a corner case or is it generally not a good idea to
CLUSTER tables which are being queried? I haven't had problems with
the REINDEX / VACUUM FULL combination while CLUSTER / VACUUM ANALYZE
resulted in errors on the first run.
Can the "could not open relation…" error bring down the whole
database server? I'm really interested in using CLUSTER regularly as
it speeds up my system by a factor of two because of more efficient I/O.
Sincerely,
Frank
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 19:27 Scott Marlowe <scott.marlowe@gmail.com>
parent: Frank Schoep <frank@ffnn.nl>
1 sibling, 0 replies; 36+ messages in thread
From: Scott Marlowe @ 2007-09-12 19:27 UTC (permalink / raw)
To: Frank Schoep <frank@ffnn.nl>; +Cc: pgsql-performance
On 9/12/07, Frank Schoep <frank@ffnn.nl> wrote:
> On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote:
> > On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
> >> …
> >> Aren't you mixing up REINDEX and CLUSTER?
> >
> > …
> > Either one does what a vacuum full did / does, but generally does
> > it better.
>
> On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE
> I'd like to ask if CLUSTER is safe to run on a table that is in
> active use.
>
> After updating my maintenance scripts from a VACUUM FULL (add me to
> the list) to CLUSTER (which improves performance a lot) I noticed I
> was getting "could not open relation …" errors in the log while the
> scripts ran so I reverted the change. This was on 8.1.9.
>
> Am I hitting a corner case or is it generally not a good idea to
> CLUSTER tables which are being queried? I haven't had problems with
> the REINDEX / VACUUM FULL combination while CLUSTER / VACUUM ANALYZE
> resulted in errors on the first run.
>
> Can the "could not open relation…" error bring down the whole
> database server? I'm really interested in using CLUSTER regularly as
> it speeds up my system by a factor of two because of more efficient I/O.
No, it won't bring it down. Basically the query lost the relation is
was operating against because it disappeared when the cluster command
runs.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 19:40 Alvaro Herrera <alvherre@commandprompt.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 36+ messages in thread
From: Alvaro Herrera @ 2007-09-12 19:40 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Mikko Partio <mpartio@gmail.com>; ruben@rentalia.com <ruben@rentalia.com>; Decibel! <decibel@decibel.org>; db@zigo.dhs.org; pgsql-performance
Scott Marlowe escribió:
> > Aren't you mixing up REINDEX and CLUSTER?
>
> I don't think so. reindex (which runs on tables and indexes, so the
> name is a bit confusing, I admit) basically was originally a "repair"
> operation that rewrote the whole relation and wasn't completely
> transaction safe (way back, 7.2 days or so I think). Due to the
> issues with vacuum full bloating indexes, and being slowly replaced by
> regular vacuum, reindex received some attention to make it transaction
> / crash safe and has kind of take the place of vacuum full in terms of
> "how to fix bloated objects".
Hmm, REINDEX does not rewrite tables. If there are dead tuples, they
will still be there after REINDEX.
> cluster, otoh, rewrites the table into index order.
... excluding dead tuples, and then rewrites all the indexes.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-12 20:01 Erik Jones <erik@myemma.com>
parent: Frank Schoep <frank@ffnn.nl>
1 sibling, 1 reply; 36+ messages in thread
From: Erik Jones @ 2007-09-12 20:01 UTC (permalink / raw)
To: Frank Schoep <frank@ffnn.nl>; +Cc: pgsql-performance
On Sep 12, 2007, at 2:19 PM, Frank Schoep wrote:
> On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote:
>> On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>>> …
>>> Aren't you mixing up REINDEX and CLUSTER?
>>
>> …
>> Either one does what a vacuum full did / does, but generally does
>> it better.
>
> On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE
> I'd like to ask if CLUSTER is safe to run on a table that is in
> active use.
>
> After updating my maintenance scripts from a VACUUM FULL (add me to
> the list) to CLUSTER (which improves performance a lot) I noticed I
> was getting "could not open relation …" errors in the log while the
> scripts ran so I reverted the change. This was on 8.1.9.
You'd probably see the same behavior on 8.2.x. CLUSTER is not
transactionally safe so you don't want to run CLUSTER on tables that
are actively being used. I believe that's been fixed for 8.3.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 05:58 Greg Smith <gsmith@gregsmith.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 4 replies; 36+ messages in thread
From: Greg Smith @ 2007-09-13 05:58 UTC (permalink / raw)
To: pgsql-performance
On Wed, 12 Sep 2007, Scott Marlowe wrote:
> I'm getting more and more motivated to rewrite the vacuum docs. I think
> a rewrite from the ground up might be best... I keep seeing people
> doing vacuum full on this list and I'm thinking it's as much because of
> the way the docs represent vacuum full as anything.
I agree you shouldn't start thinking in terms of how to fix the existing
documentation. I'd suggest instead writing a tutorial leading someone
through what they need to know about their tables first and then going
into how vacuum works based on that data.
As an example, people throw around terms like "index bloat" and "dead
tuples" when talking about vacuuming. The tutorial I'd like to see
somebody write would start by explaining those terms and showing how to
measure them--preferably with a good and bad example to contrast. The way
these terms are thrown around right now, I don't expect newcomers to
understand either the documentation or the advice people are giving them;
I think it's shooting over their heads and what's needed are some
walkthroughs. Another example I'd like to see thrown in there is what it
looks like when you don't have enough FSM slots.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 07:20 El-Lotso <el.lotso@gmail.com>
parent: Greg Smith <gsmith@gregsmith.com>
3 siblings, 0 replies; 36+ messages in thread
From: El-Lotso @ 2007-09-13 07:20 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On Thu, 2007-09-13 at 01:58 -0400, Greg Smith wrote:
> On Wed, 12 Sep 2007, Scott Marlowe wrote:
>
> > I'm getting more and more motivated to rewrite the vacuum docs. I think
> > a rewrite from the ground up might be best... I keep seeing people
> > doing vacuum full on this list and I'm thinking it's as much because of
> > the way the docs represent vacuum full as anything.
>
> I agree you shouldn't start thinking in terms of how to fix the existing
> documentation. I'd suggest instead writing a tutorial leading someone
> through what they need to know about their tables first and then going
> into how vacuum works based on that data.
I'm new to PG and it's true that I am confused.
As it stands this is a newbie's understanding of the various terms.
cluster -> rewrites a table according to index order so that IO is
ordered/sequential
reindex -> basically, rewrites the indexes adding new records/fixes up
old deleted records
vacuum -> does cleaning
vacuum analyse -> clean and update statistics (i run this mostly)
autovacuum - does vacuum analyse automatically per default setup or some
or cost based parameter
vacuum full -> I also do this frequently (test DB only) as a means to
retrieve back used spaces due to MVCC. (trying lots of different methods
of query/add new index/make concatenated join/unique keys and then
deleting them if it's not useful)
>
> As an example, people throw around terms like "index bloat" and "dead
> tuples" when talking about vacuuming.
I honestly have only the vaguest idea what these 2 mean. (i only grasped
recently that tuples = records/rows)
> The tutorial I'd like to see
> somebody write would start by explaining those terms and showing how to
> measure them--preferably with a good and bad example to contrast. The way
> these terms are thrown around right now, I don't expect newcomers to
> understand either the documentation or the advice people are giving them;
> I think it's shooting over their heads and what's needed are some
> walkthroughs. Another example I'd like to see thrown in there is what it
> looks like when you don't have enough FSM slots.
actually, an additional item I would like is to understand explain
analyse. The current docs written by tom only shows explain and not
explain analyse and I'm getting confuse as to the rows=xxx vs actual
rows=yyy where on some of my queries can be very far apart 1 vs 500x
ratio on some problematic query[1]. And googling doesn't give much doc
on the explain. (the only other useful doc I've seen is a presentation
given from oscon 2003)
[1](See my other post)
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 14:29 Scott Marlowe <scott.marlowe@gmail.com>
parent: Greg Smith <gsmith@gregsmith.com>
3 siblings, 1 reply; 36+ messages in thread
From: Scott Marlowe @ 2007-09-13 14:29 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On 9/13/07, Greg Smith <gsmith@gregsmith.com> wrote:
> On Wed, 12 Sep 2007, Scott Marlowe wrote:
>
> > I'm getting more and more motivated to rewrite the vacuum docs. I think
> > a rewrite from the ground up might be best... I keep seeing people
> > doing vacuum full on this list and I'm thinking it's as much because of
> > the way the docs represent vacuum full as anything.
>
> I agree you shouldn't start thinking in terms of how to fix the existing
> documentation. I'd suggest instead writing a tutorial leading someone
> through what they need to know about their tables first and then going
> into how vacuum works based on that data.
I think both things are needed actually. The current docs were
started back when pg 7.2 roamed the land, and they've been updated a
bit at a time. The technical definitions of vacuum, vacuum full,
analyze etc all show a bit too much history from back in the day, and
are confusing. so, I think that 1: vacuum and analyze should have
their own sections. analyze used to be a subcommand of vacuum but it
no longer is, but the docs still pretty much tie them together. 2:
The definition for vacuum full needs to include a caveat that vacuum
full should be considered more of a recovery operation than a way to
simply get back some space on your hard drives.
Which leads me to thinking that we then need a simple tutorial on
vacuuming to include the free space map, vacuum, vacuum analyze,
vacuum full, and the autovacuum daemon. We can throw analyze in there
somewhere too, I just don't want it to seem like it's still married to
vacuum.
> As an example, people throw around terms like "index bloat" and "dead
> tuples" when talking about vacuuming. The tutorial I'd like to see
> somebody write would start by explaining those terms and showing how to
> measure them--preferably with a good and bad example to contrast.
I agree. I might rearrange it a bit but that's the way I'm looking at it too.
> The way
> these terms are thrown around right now, I don't expect newcomers to
> understand either the documentation or the advice people are giving them;
> I think it's shooting over their heads and what's needed are some
> walkthroughs. Another example I'd like to see thrown in there is what it
> looks like when you don't have enough FSM slots.
OK. Got something to start with. I'm thinking I might work on a
vacuum tutorial first, then the tech docs...
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 15:31 Gavin M. Roy <gmr@ehpg.net>
parent: Ruben Rubio <ruben@rentalia.com>
1 sibling, 1 reply; 36+ messages in thread
From: Gavin M. Roy @ 2007-09-13 15:31 UTC (permalink / raw)
To: Ruben Rubio <ruben@rentalia.com>; +Cc: pgsql-performance
How many backends do you have at any given time? Have you tried using
something like pgBouncer to lower backend usage? How about your IO
situation? Have you run something like sysstat to see what iowait is
at?
On 9/11/07, Ruben Rubio <ruben@rentalia.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Hi,
>
> I having the same problem I told here a few weeks before. Database is
> using too much resources again.
>
> I do a vacumm full each day, but seems it is not working. I am preparing
> an update to postgres 8.2.4 (actually I am using at 8.1.3, and tests for
> update will need several days)
>
> Last time I had this problem i solved it stopping website, restarting
> database, vacuumm it, run again website. But I guess this is going to
> happen again.
>
> I would like to detect and solve the problem. Any ideas to detect it?
>
> Thanks in advance,
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFG5jbLIo1XmbAXRboRArcpAJ0YvoCT6KWv2fafVAtapu6nwFmKoACcD0uA
> zFTx9Wq+2NSxijIf/R8E5f8=
> =u0k5
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 16:03 Erik Jones <erik@myemma.com>
parent: Greg Smith <gsmith@gregsmith.com>
3 siblings, 1 reply; 36+ messages in thread
From: Erik Jones @ 2007-09-13 16:03 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On Sep 13, 2007, at 12:58 AM, Greg Smith wrote:
> On Wed, 12 Sep 2007, Scott Marlowe wrote:
>
>> I'm getting more and more motivated to rewrite the vacuum docs. I
>> think a rewrite from the ground up might be best... I keep seeing
>> people doing vacuum full on this list and I'm thinking it's as
>> much because of the way the docs represent vacuum full as anything.
>
> I agree you shouldn't start thinking in terms of how to fix the
> existing documentation. I'd suggest instead writing a tutorial
> leading someone through what they need to know about their tables
> first and then going into how vacuum works based on that data.
>
> As an example, people throw around terms like "index bloat" and
> "dead tuples" when talking about vacuuming. The tutorial I'd like
> to see somebody write would start by explaining those terms and
> showing how to measure them--preferably with a good and bad example
> to contrast. The way these terms are thrown around right now, I
> don't expect newcomers to understand either the documentation or
> the advice people are giving them; I think it's shooting over their
> heads and what's needed are some walkthroughs. Another example I'd
> like to see thrown in there is what it looks like when you don't
> have enough FSM slots.
Isn't that the point of the documentation? I mean, if the existing,
official manual has been demonstrated (through countless mailing list
help requests) to not sufficiently explain a given topic, shouldn't
it be revised? One thing that might help is a hyperlinked glossary
so that people reading through the documentation can go straight to
the postgres definition of dead tuple, index bloat, etc.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-13 16:39 Scott Marlowe <scott.marlowe@gmail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 36+ messages in thread
From: Scott Marlowe @ 2007-09-13 16:39 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
On 9/13/07, Erik Jones <erik@myemma.com> wrote:
> On Sep 13, 2007, at 12:58 AM, Greg Smith wrote:
>
> > On Wed, 12 Sep 2007, Scott Marlowe wrote:
> >
> >> I'm getting more and more motivated to rewrite the vacuum docs. I
> >> think a rewrite from the ground up might be best... I keep seeing
> >> people doing vacuum full on this list and I'm thinking it's as
> >> much because of the way the docs represent vacuum full as anything.
> >
> > I agree you shouldn't start thinking in terms of how to fix the
> > existing documentation. I'd suggest instead writing a tutorial
> > leading someone through what they need to know about their tables
> > first and then going into how vacuum works based on that data.
> >
> > As an example, people throw around terms like "index bloat" and
> > "dead tuples" when talking about vacuuming. The tutorial I'd like
> > to see somebody write would start by explaining those terms and
> > showing how to measure them--preferably with a good and bad example
> > to contrast. The way these terms are thrown around right now, I
> > don't expect newcomers to understand either the documentation or
> > the advice people are giving them; I think it's shooting over their
> > heads and what's needed are some walkthroughs. Another example I'd
> > like to see thrown in there is what it looks like when you don't
> > have enough FSM slots.
>
> Isn't that the point of the documentation? I mean, if the existing,
> official manual has been demonstrated (through countless mailing list
> help requests) to not sufficiently explain a given topic, shouldn't
> it be revised? One thing that might help is a hyperlinked glossary
> so that people reading through the documentation can go straight to
> the postgres definition of dead tuple, index bloat, etc.
Yes and no. The official docs are more of a technical specification.
Short, simple and to the point so that if you know mostly what you're
doing you don't have to wade through a long tutorial to find the
answer. I find MySQL's documentation frustrating as hell because I
can never find just the one thing I wanna look for. Because it's all
written as a tutorial. I.e. I have to pay the "stupid tax" when I
read their docs.
What I want to do is two fold. 1: fix the technical docs so they have
better explanations of each of the topics, without turning them into
huge tutorials. 2: Write a vacuuming tutorial that will be useful
should someone be new to postgresql and need to set up their system.
I think the tutorial should be broken into at least two sections, a
quick start guide and an ongoing maintenance and tuning section.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-14 07:19 Greg Smith <gsmith@gregsmith.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 36+ messages in thread
From: Greg Smith @ 2007-09-14 07:19 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Thu, 13 Sep 2007, Scott Marlowe wrote:
> I think both things are needed actually. The current docs were
> started back when pg 7.2 roamed the land, and they've been updated a
> bit at a time...
No argument here that ultimately the documentation needs to be updated as
well. I was just suggesting what I've been thinking about as the path of
least resistance to move in that direction. Updating the documentation is
harder to do because of the build process involved. It's easier to write
something new that addresses the deficiencies, get that right, and then
merge it into the documentation when it's stable. After the main new
content is done, then it's easier to sweep back through the existing
material and clean things up.
> Which leads me to thinking that we then need a simple tutorial on
> vacuuming to include the free space map, vacuum, vacuum analyze,
> vacuum full, and the autovacuum daemon.
Right, that's the sort of thing that's missing right now, and I think that
would be more useful to newbies than correcting the documentation that's
already there.
Also: if you don't have a public working area to assemble this document
at, I've set a precedent of sorts that it's OK to put working material
like this onto the PG developer's wiki at http://developer.postgresql.org/
as long as your stated intention is ultimately to move it off of there
once it's complete. In addition to providing a nice set of tools for
working the text (presuming you're comfortable with Wiki syntax) that will
get you a pool of reviewers/contributors who can make changes directly
rather than you needing to do all the work yourself.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-14 14:00 Adam Tauno Williams <adamtaunowilliams@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 36+ messages in thread
From: Adam Tauno Williams @ 2007-09-14 14:00 UTC (permalink / raw)
To: pgsql-performance
> > Isn't that the point of the documentation? I mean, if the existing,
> > official manual has been demonstrated (through countless mailing list
> > help requests) to not sufficiently explain a given topic, shouldn't
> > it be revised?
Or it proves that no one bothers to read the docs.
> > One thing that might help is a hyperlinked glossary
> > so that people reading through the documentation can go straight to
> > the postgres definition of dead tuple, index bloat, etc.
> Yes and no. The official docs are more of a technical specification.
> Short, simple and to the point so that if you know mostly what you're
> doing you don't have to wade through a long tutorial to find the
> answer. I find MySQL's documentation frustrating as hell because I
> can never find just the one thing I wanna look for.
Yes! MySQL documentation is maddening.
This is why, I suspect, for products like Informix and DB2 IBM publishes
two manuals (or roughly equivalent to two manuals): a "guide" and a
"reference".
> written as a tutorial. I.e. I have to pay the "stupid tax" when I
> read their docs.
Yep.
> What I want to do is two fold. 1: fix the technical docs so they have
> better explanations of each of the topics, without turning them into
> huge tutorials. 2: Write a vacuuming tutorial that will be useful
> should someone be new to postgresql and need to set up their system.
> I think the tutorial should be broken into at least two sections, a
> quick start guide and an ongoing maintenance and tuning section.
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-17 12:00 Ruben Rubio <ruben@rentalia.com>
parent: Gavin M. Roy <gmr@ehpg.net>
0 siblings, 0 replies; 36+ messages in thread
From: Ruben Rubio @ 2007-09-17 12:00 UTC (permalink / raw)
To: Gavin M. Roy <gmr@ehpg.net>; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Gavin M. Roy escribió:
> How many backends do you have at any given time? Have you tried using
> something like pgBouncer to lower backend usage? How about your IO
> situation? Have you run something like sysstat to see what iowait is
> at?
backends arround 50 -100 I don't use pgBouncer yet.
Sysstat reports veeery low io.
Right now Im checking out fsm parameter, as Scott recomended. Seems
there is the problem.
>
> On 9/11/07, Ruben Rubio <ruben@rentalia.com> wrote:
>
> Hi,
>
> I having the same problem I told here a few weeks before. Database is
> using too much resources again.
>
> I do a vacumm full each day, but seems it is not working. I am preparing
> an update to postgres 8.2.4 (actually I am using at 8.1.3, and tests for
> update will need several days)
>
> Last time I had this problem i solved it stopping website, restarting
> database, vacuumm it, run again website. But I guess this is going to
> happen again.
>
> I would like to detect and solve the problem. Any ideas to detect it?
>
> Thanks in advance,
>
>
>
>>
>>
- ---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
>>
>>
>>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG7mx7Io1XmbAXRboRAn0VAJ4sGc1KCNlsbrybVbY/WfB+3XWBbwCfb7Z/
WNGyJCRo6zd26uR6FB6SA8o=
=SYzs
-----END PGP SIGNATURE-----
Attachments:
ruben.vcf (text/x-vcard, 244B) download
begin:vcard
fn:Ruben Rubio
n:Rubio;Ruben
org:Rentalia Holidays S.L
adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain
email;internet:ruben@rentalia.com
tel;work:+34915233104
url:http://www.rentalia.com
version:2.1
end:vcard
* Re: [Again] Postgres performance problem
@ 2007-09-17 12:24 Decibel! <decibel@decibel.org>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 36+ messages in thread
From: Decibel! @ 2007-09-17 12:24 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Frank Schoep <frank@ffnn.nl>; pgsql-performance
On Wed, Sep 12, 2007 at 03:01:12PM -0500, Erik Jones wrote:
>
> On Sep 12, 2007, at 2:19 PM, Frank Schoep wrote:
>
> >On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote:
> >>On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
> >>>?
> >>>Aren't you mixing up REINDEX and CLUSTER?
> >>
> >>?
> >>Either one does what a vacuum full did / does, but generally does
> >>it better.
> >
> >On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE
> >I'd like to ask if CLUSTER is safe to run on a table that is in
> >active use.
> >
> >After updating my maintenance scripts from a VACUUM FULL (add me to
> >the list) to CLUSTER (which improves performance a lot) I noticed I
> >was getting "could not open relation ?" errors in the log while the
> >scripts ran so I reverted the change. This was on 8.1.9.
>
> You'd probably see the same behavior on 8.2.x. CLUSTER is not
> transactionally safe so you don't want to run CLUSTER on tables that
> are actively being used. I believe that's been fixed for 8.3.
Actually, that's a bit over-conservative... what happens prior to 8.3 is
that CLUSTER rewrites the table using it's XID for everything. That can
break semantics for any transactions that are running in serializable
mode; if you're just using the default isolation level of read
committed, you're fine with CLUSTER.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
^ permalink raw reply [nested|flat] 36+ messages in thread
* Re: [Again] Postgres performance problem
@ 2007-09-17 12:27 Decibel! <decibel@decibel.org>
parent: Greg Smith <gsmith@gregsmith.com>
3 siblings, 1 reply; 36+ messages in thread
From: Decibel! @ 2007-09-17 12:27 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On Thu, Sep 13, 2007 at 01:58:10AM -0400, Greg Smith wrote:
> On Wed, 12 Sep 2007, Scott Marlowe wrote:
>
> >I'm getting more and more motivated to rewrite the vacuum docs. I think
> >a rewrite from the ground up might be best... I keep seeing people
> >doing vacuum full on this list and I'm thinking it's as much because of
> >the way the docs represent vacuum full as anything.
>
> I agree you shouldn't start thinking in terms of how to fix the existing
> documentation. I'd suggest instead writing a tutorial leading someone
> through what they need to know about their tables first and then going
> into how vacuum works based on that data.
Take a look at the stuff at http://decibel.org/~decibel/pervasive/, it'd
hopefully provide a useful starting point.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
^ permalink raw reply [nested|flat] 36+ messages in thread
* [OT] Re: [Again] Postgres performance problem
@ 2007-09-24 06:58 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
parent: Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 36+ messages in thread
From: Ow Mun Heng @ 2007-09-24 06:58 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: pgsql-performance
On Mon, 2007-09-17 at 07:27 -0500, Decibel! wrote:
> Take a look at the stuff at http://decibel.org/~decibel/pervasive/, it'd
> hopefully provide a useful starting point.
A bit offtrack, but I was reading the articles and noticed this in the
bottom. Is this a typo or ...
Making PostreSQL pervasive© 2005 Pervasive Software Inc
^^^^^^^^^
^ permalink raw reply [nested|flat] 36+ messages in thread
end of thread, other threads:[~2007-09-24 06:58 UTC | newest]
Thread overview: 36+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-27 11:41 Postgres performance problem Ruben Rubio <ruben@rentalia.com>
2007-08-27 12:33 ` Chris Mair <chris@1006.org>
2007-08-27 12:52 ` ruben@rentalia.com
2007-08-27 13:29 ` Bill Moran <wmoran@collaborativefusion.com>
2007-08-28 18:36 ` Anton Melser <melser.anton@gmail.com>
2007-08-30 07:52 ` ruben@rentalia.com
2007-08-30 09:50 ` Ruben Rubio <ruben@rentalia.com>
2007-08-30 17:31 ` Decibel! <decibel@decibel.org>
2007-08-30 18:07 ` Richard Huxton <dev@archonet.com>
2007-08-30 18:08 ` Mark Lewis <mark.lewis@mir3.com>
2007-08-31 19:36 ` Vivek Khera <vivek@khera.org>
2007-09-11 06:33 [Again] Postgres performance problem Ruben Rubio <ruben@rentalia.com>
2007-09-11 07:31 ` db@zigo.dhs.org
2007-09-11 07:49 ` Ruben Rubio <ruben@rentalia.com>
2007-09-11 22:52 ` Decibel! <decibel@decibel.org>
2007-09-12 07:42 ` ruben@rentalia.com
2007-09-12 18:28 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-09-12 18:46 ` Brian Hurt <bhurt@janestcapital.com>
2007-09-12 18:56 ` Mikko Partio <mpartio@gmail.com>
2007-09-12 19:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-09-12 19:19 ` Frank Schoep <frank@ffnn.nl>
2007-09-12 19:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-09-12 20:01 ` Erik Jones <erik@myemma.com>
2007-09-17 12:24 ` Decibel! <decibel@decibel.org>
2007-09-12 19:40 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-09-13 05:58 ` Greg Smith <gsmith@gregsmith.com>
2007-09-13 07:20 ` El-Lotso <el.lotso@gmail.com>
2007-09-13 14:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-09-14 07:19 ` Greg Smith <gsmith@gregsmith.com>
2007-09-13 16:03 ` Erik Jones <erik@myemma.com>
2007-09-13 16:39 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-09-14 14:00 ` Adam Tauno Williams <adamtaunowilliams@gmail.com>
2007-09-17 12:27 ` Decibel! <decibel@decibel.org>
2007-09-24 06:58 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-09-13 15:31 ` Gavin M. Roy <gmr@ehpg.net>
2007-09-17 12:00 ` Ruben Rubio <ruben@rentalia.com>
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