A tale of two similar databases
Hi All,
I am using PostgreSQL 7.3.2 on a server running Red Hat Linux 9.0.
I have two databases ( identical schema and similar data ).
One database, D1 contains the actual data of a Production Application.
The other D2 contains dummy data which is used during development and
testing of the application.
D2 actually contains a recent snapshot of D1 and hence contain almost
the same data.
The strange thing is that D1 is extremely fast whereas D2 is relatively
slow for any given query.
(Thank God, it isnt the other way round :) )
Eg,
An extensive Statistics query returns in a matter of seconds on D1 but
takes close to a minute on D2
I would like to know if anybody can answer why it is so.
Thanks in advance
- Kishore
On Jan 17, 2006, at 18:22 , kishore.sainath@gmail.com wrote:
I am using PostgreSQL 7.3.2 on a server running Red Hat Linux 9.0.
I'd highly recommend upgrading. The current release is 8.1.2. If you
can't upgrade to 8.1, at least upgrade to the latest point release of
7.3, which is 7.3.13. There are a number of security and critical bug
fixes.
The strange thing is that D1 is extremely fast whereas D2 is
relatively
slow for any given query.
(Thank God, it isnt the other way round :) )Eg,
An extensive Statistics query returns in a matter of seconds on D1 but
takes close to a minute on D2
How often do you run ANALYZE? I suspect your statistics are probably
off. Try running EXPLAIN ANALYZE on the queries and compare the
results. If you can provide more information such as relevant table
schema, the queries, and their EXPLAIN ANALYZE output, other list
members may be able to help you. You may also want to ask on the
pgsql-performance list.
Michael Glaesemann
grzm myrealbox com
On 17 Jan 2006 01:22:20 -0800, kishore.sainath@gmail.com
<kishore.sainath@gmail.com> wrote:
Hi All,
I am using PostgreSQL 7.3.2 on a server running Red Hat Linux 9.0.
I have two databases ( identical schema and similar data ).
One database, D1 contains the actual data of a Production Application.
The other D2 contains dummy data which is used during development and
testing of the application.D2 actually contains a recent snapshot of D1 and hence contain almost
the same data.The strange thing is that D1 is extremely fast whereas D2 is relatively
slow for any given query.
(Thank God, it isnt the other way round :) )Eg,
An extensive Statistics query returns in a matter of seconds on D1 but
takes close to a minute on D2I would like to know if anybody can answer why it is so.
I am making the assumption that you have checked your query plan on
both databases to make sure that they are the same i.e.
on DB1
explain "big query";
on DB2
explain "big query"
If these are not almost identical then you need to investigate the
reasons for the difference ie bad stats on the dev database or missing
index's etc. Have you vacuum analyzed D2?
One other possible reason is that D1 is mostly in cache and D2 isn't.
If you run the query twice on D2 immediately after each other is the
second query much faster. If this is the case what you might be seeing
is D1 being in constant use is forcing the D2 data back onto the disk
and out of the cache.
Have you vacuum'd and/or analyzed D2?
http://www.postgresql.org/docs/8.1/static/maintenance.html
http://www.postgresql.org/docs/8.1/static/sql-analyze.html
----
James Robinson
Socialserve.com
Import Notes
Resolved by subject fallback
Hi All,
I haven't vacuum/analysed the D2 database or for that matter D1.
In fact I have never used VACUUM before.
Maybe that is the problem.
What are the benefits of the VACUUM command in PostgreSQL?
Thanks in advance
- Kishore
On Tue, Jan 17, 2006 at 09:17:41PM -0800, kishore.sainath@gmail.com wrote:
I haven't vacuum/analysed the D2 database or for that matter D1.
In fact I have never used VACUUM before.
Maybe that is the problem.What are the benefits of the VACUUM command in PostgreSQL?
See the section on vacuuming in "Routine Database Maintenance Tasks"
in the documentation:
http://www.postgresql.org/docs/7.3/interactive/routine-vacuuming.html
--
Michael Fuhr
On Jan 18, 2006, at 14:17 , kishore.sainath@gmail.com wrote:
What are the benefits of the VACUUM command in PostgreSQL?
The docs have quite a bit of information on this. For a start, take a
look at:
http://www.postgresql.org/docs/current/interactive/
maintenance.html#ROUTINE-VACUUMING
Recently, some people of expressed that the material on autovacuum
may be misleading. The other material should give you enough
background, however.
For some information on ANALYZE, see:
http://www.postgresql.org/docs/current/interactive/sql-analyze.html
Michael Glaesemann
grzm myrealbox com
On Thu, 2006-01-19 at 16:46, Michael Glaesemann wrote:
On Jan 18, 2006, at 14:17 , kishore.sainath@gmail.com wrote:
What are the benefits of the VACUUM command in PostgreSQL?
The docs have quite a bit of information on this. For a start, take a
look at:
http://www.postgresql.org/docs/current/interactive/
maintenance.html#ROUTINE-VACUUMINGRecently, some people of expressed that the material on autovacuum
may be misleading. The other material should give you enough
background, however.For some information on ANALYZE, see:
http://www.postgresql.org/docs/current/interactive/sql-analyze.html
It's not misleading so much as just incomplete and a little hard for the
beginners to wrap their brains around...
On Tue, Jan 17, 2006 at 09:17:41PM -0800, kishore.sainath@gmail.com wrote:
Hi All,
I haven't vacuum/analysed the D2 database or for that matter D1.
In fact I have never used VACUUM before.
Maybe that is the problem.What are the benefits of the VACUUM command in PostgreSQL?
VACUUM is as close as it comes to fast=true. If you don't do it your
database is almost guaranteed to become dog slow after a while.
http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2
will probably be enlightening for you...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461