pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCan anyone explain this: duplicate dbs.
10+ messages / 5 participants
[nested] [flat]
* Can anyone explain this: duplicate dbs.
@ 2005-05-25 02:07 SpaceBallOne <space_ball_one@hotmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: SpaceBallOne @ 2005-05-25 02:07 UTC (permalink / raw)
To: pgsql-performance
Wondering if someone could explain a pecularity for me:
We have a database which takes 1000ms to perform a certain query on.
If I pg_dump that database then create a new database (e.g. "tempdb") and upload the dump file (thus making a duplicate) then the same query only takes 190ms !!
Vacuum, vacuum analyse, and vacuum full analyse does not seem to have an impact on these times.
Can anyone explain why this may be occurring and how I might be able to keep the original database running at the same speed as "tempdb"?
Thanks in advance,
Dave.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 02:21 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: SpaceBallOne <space_ball_one@hotmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Christopher Kings-Lynne @ 2005-05-25 02:21 UTC (permalink / raw)
To: SpaceBallOne <space_ball_one@hotmail.com>; +Cc: pgsql-performance
> Can anyone explain why this may be occurring and how I might be able to
> keep the original database running at the same speed as "tempdb"?
You're not vacuuming anywhere near often enough. Read up the database
maintenance section of the manual. Then, set up contrib/pg_autovacuum
to vacuum your database regularly, or make a cron job to run "vacuumdb
-a -z -q" once an hour, say.
You can fix for the case when you haven't been vacuuming enough by a
once off VACUUM FULL ANALYZE command, but this will lock tables
exclusively as it does its work.
Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 02:22 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: SpaceBallOne <space_ball_one@hotmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Christopher Kings-Lynne @ 2005-05-25 02:22 UTC (permalink / raw)
To: SpaceBallOne <space_ball_one@hotmail.com>; +Cc: pgsql-performance
> If I pg_dump that database then create a new database (e.g. "tempdb")
> and upload the dump file (thus making a duplicate) then the same query
> only takes 190ms !!
> Vacuum, vacuum analyse, and vacuum full analyse does not seem to have an
> impact on these times.
Damn, for some reason I didn't read that you had already tried vacuum
full. In that case, I can't explain it except perhaps you aren't
vacuuming properly, or the right thing, or it's a disk cache thing.
Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 02:39 John A Meinel <john@arbash-meinel.com>
parent: SpaceBallOne <space_ball_one@hotmail.com>
2 siblings, 3 replies; 10+ messages in thread
From: John A Meinel @ 2005-05-25 02:39 UTC (permalink / raw)
To: SpaceBallOne <space_ball_one@hotmail.com>; +Cc: pgsql-performance
SpaceBallOne wrote:
> Wondering if someone could explain a pecularity for me:
>
> We have a database which takes 1000ms to perform a certain query on.
>
> If I pg_dump that database then create a new database (e.g. "tempdb")
> and upload the dump file (thus making a duplicate) then the same query
> only takes 190ms !!
> Vacuum, vacuum analyse, and vacuum full analyse does not seem to have
> an impact on these times.
>
> Can anyone explain why this may be occurring and how I might be able
> to keep the original database running at the same speed as "tempdb"?
>
> Thanks in advance,
>
> Dave.
What version of postgres?
There are a few possibilities. If you are having a lot of updates to the
table, you can get index bloat. And vacuum doesn't fix indexes. You have
to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
FULL has, so you need to be a little careful with it.
Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
your table ends up nicer when you are done.
Also, older versions of postgres had a worse time with index bloat. One
thing that caused a lot of problem is a table that you insert into over
time, so that all the values are incrementing. If you are deleting older
entries, that area won't be re-used because they fall at the back end. I
believe newer versions have been fixed.
By the way, I think doing:
CREATE DATABASE tempdb WITH TEMPLATE = originaldb;
Is a much faster way of doing dump and load. I *think* it would recreate
indexes, etc. If it just does a copy it may not show the dump/restore
improvement.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 02:53 SpaceBallOne <space_ball_one@hotmail.com>
parent: John A Meinel <john@arbash-meinel.com>
2 siblings, 2 replies; 10+ messages in thread
From: SpaceBallOne @ 2005-05-25 02:53 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
> What version of postgres?
8.0.2 ... but I think I've seen this before on 7.3 ...
> There are a few possibilities. If you are having a lot of updates to the
> table, you can get index bloat. And vacuum doesn't fix indexes. You have
> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
> FULL has, so you need to be a little careful with it.
> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
> your table ends up nicer when you are done.
Thanks, will try those next time this problem crops up (i just deleted /
recreated the database to speed things for its users in the office ...
probably should have held off to see if I could find a solution first!).
Yes, the database / table-in-question does have a lot of updates, deletes,
and new rows (relatively speaking for a small business).
Would CLUSTER / REINDEX still have an effect if our queries were done via
sequential scan? This is a old database (as in built by me when i was just
starting to learn unix / postgres) so the database design is pretty horrible
(little normalisation, no indexes).
Have taken Chris's advice onboard too and setup cron to do a vacuumdb hourly
instead of my weekly vacuum.
Cheers,
Dave.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 03:00 John A Meinel <john@arbash-meinel.com>
parent: SpaceBallOne <space_ball_one@hotmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: John A Meinel @ 2005-05-25 03:00 UTC (permalink / raw)
To: SpaceBallOne <space_ball_one@hotmail.com>; +Cc: pgsql-performance
SpaceBallOne wrote:
>> What version of postgres?
>
>
> 8.0.2 ... but I think I've seen this before on 7.3 ...
>
>> There are a few possibilities. If you are having a lot of updates to the
>> table, you can get index bloat. And vacuum doesn't fix indexes. You have
>> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
>> FULL has, so you need to be a little careful with it.
>
>
>> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
>> your table ends up nicer when you are done.
>
>
> Thanks, will try those next time this problem crops up (i just deleted
> / recreated the database to speed things for its users in the office
> ... probably should have held off to see if I could find a solution
> first!).
>
> Yes, the database / table-in-question does have a lot of updates,
> deletes, and new rows (relatively speaking for a small business).
>
> Would CLUSTER / REINDEX still have an effect if our queries were done
> via sequential scan? This is a old database (as in built by me when i
> was just starting to learn unix / postgres) so the database design is
> pretty horrible (little normalisation, no indexes).
Well, my first recommendation is to put in some indexes. :) They are
relatively easy to setup and can drastically improve select performance.
What version of postgres are you using?
What does it say at the end of "VACUUM FULL ANALYZE VERBOSE", that
should tell you how many free pages were reclaimed and how big your free
space map should be.
If you only did 1 VACUUM FULL, you might try another, as it sounds like
your tables aren't properly filled. I'm pretty sure vacuum only removes
empty pages/marks locations for the free space map so they can be
re-used, while vacuum full will move entries around to create free pages.
It sounds like it didn't do it properly.
But even so, CLUSTER is still your friend, as it allows you to "presort"
the rows in your tables.
>
> Have taken Chris's advice onboard too and setup cron to do a vacuumdb
> hourly instead of my weekly vacuum.
>
> Cheers,
>
> Dave.
>
>
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 03:00 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: SpaceBallOne <space_ball_one@hotmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Christopher Kings-Lynne @ 2005-05-25 03:00 UTC (permalink / raw)
To: SpaceBallOne <space_ball_one@hotmail.com>; +Cc: John A Meinel <john@arbash-meinel.com>; pgsql-performance
> Would CLUSTER / REINDEX still have an effect if our queries were done
> via sequential scan?
SELECTS don't write to the database, so they have no effect at all on
vacuuming/analyzing. You only need to worry about that with writes.
> This is a old database (as in built by me when i
> was just starting to learn unix / postgres) so the database design is
> pretty horrible (little normalisation, no indexes).
No indexes? Bloody hell :D
Use EXPLAIN ANALYZE SELECT ... ; on all of your selects to see where
they are slow and where you can add indexes...
Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 03:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-05-25 03:01 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: SpaceBallOne <space_ball_one@hotmail.com>; pgsql-performance
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> If I pg_dump that database then create a new database (e.g. "tempdb")
>> and upload the dump file (thus making a duplicate) then the same query
>> only takes 190ms !!
>> Vacuum, vacuum analyse, and vacuum full analyse does not seem to have an
>> impact on these times.
> Damn, for some reason I didn't read that you had already tried vacuum
> full.
I'm thinking index bloat, and a PG version too old for vacuum full to
recover any index space. But without any information about PG version
or EXPLAIN ANALYZE results, we're all just guessing.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 03:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: John A Meinel <john@arbash-meinel.com>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-05-25 03:02 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: SpaceBallOne <space_ball_one@hotmail.com>; pgsql-performance
John A Meinel <john@arbash-meinel.com> writes:
> By the way, I think doing:
> CREATE DATABASE tempdb WITH TEMPLATE = originaldb;
> Is a much faster way of doing dump and load. I *think* it would recreate
> indexes, etc. If it just does a copy it may not show the dump/restore
> improvement.
CREATE DATABASE just does a physical copy, so it won't do anything at
all for bloat issues.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Can anyone explain this: duplicate dbs.
@ 2005-05-25 03:32 Bruno Wolff III <bruno@wolff.to>
parent: John A Meinel <john@arbash-meinel.com>
2 siblings, 0 replies; 10+ messages in thread
From: Bruno Wolff III @ 2005-05-25 03:32 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: SpaceBallOne <space_ball_one@hotmail.com>; pgsql-performance
On Tue, May 24, 2005 at 21:39:15 -0500,
John A Meinel <john@arbash-meinel.com> wrote:
>
> By the way, I think doing:
>
> CREATE DATABASE tempdb WITH TEMPLATE = originaldb;
>
> Is a much faster way of doing dump and load. I *think* it would recreate
> indexes, etc. If it just does a copy it may not show the dump/restore
> improvement.
You need to be careful when doing this. See section 18.3 of the 8.0 docs
for caveats.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-05-25 03:32 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-25 02:07 Can anyone explain this: duplicate dbs. SpaceBallOne <space_ball_one@hotmail.com>
2005-05-25 02:21 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-05-25 02:22 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-05-25 03:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-25 02:39 ` John A Meinel <john@arbash-meinel.com>
2005-05-25 02:53 ` SpaceBallOne <space_ball_one@hotmail.com>
2005-05-25 03:00 ` John A Meinel <john@arbash-meinel.com>
2005-05-25 03:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-05-25 03:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-25 03:32 ` Bruno Wolff III <bruno@wolff.to>
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