Thread: 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 D2 > > I 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. -- Harry http://www.hjackson.org http://www.uklug.co.uk
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
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-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 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