Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow database creation

Slow database creation

From
"Rob Richardson"
Date:
Greetings!
 
My company has an application that runs on one machine and reads a PostgreSQL database that may be located on another.  I have a test version of this application and a copy of their database on my computer.  A problem that the application solves on my machine in under 10 seconds takes over 20 minutes on the customer's system.  I believe the reason is that data transfer between machines is extremely slow, but I don't know why.  This is not a large database.  The largest queries return 300 records with 50 fields per record, and 1700 records from a table with 6 fields per record.
 
In order to test without disturbing the customer's production, I created a copy of their production database on the production server.  I often create test databases, and I've never seen the CREATE DATABASE command take longer than five seconds.  On the customer's production machine, the command took 167 seconds. 
 
Can anyone explain why it would take 167 seconds to create a database?  I am hoping that it's the same reason that data access is slowing our application by a factor of about 200.
 
The customer's machines run Windows Server 2003.  My machine runs Windows XP Professional.  The application is written in C++.

Rob Richardson
Product Engineer Software

RAD-CON, Inc.
TECHNOLOGY: Innovative & Proven
Phone : +1.216.706.8905
Fax:  +1.216.221.1135
Website:  www.RAD-CON.com
E-mail:  rob.richardson@RAD-CON.com

 
Attachment

Re: Slow database creation

From
Richard Huxton
Date:
Rob Richardson wrote:
>
> Can anyone explain why it would take 167 seconds to create a database?
> I am hoping that it's the same reason that data access is slowing our
> application by a factor of about 200.
>
> The customer's machines run Windows Server 2003.  My machine runs
> Windows XP Professional.  The application is written in C++.

I can think of three possible reasons:

1. Machine is very busy (check the performance monitoring, and perhaps
grab some sysinternals tools).
2. Locks on system tables (I'm guessing creating a database requires an
exclusive lock on various bits and pieces). Joining pg_locks to pg_class
via oid will show what's locked.
3. System tables / indexes are horribly bloated and need
vacuuming/reindexing. Try a vacuum verbose and see if it mentions lots
of unremovable rows on pg_database or pg_class or some such.

If it's none of the above then it'll require some proper investigation.

--
  Richard Huxton
  Archonet Ltd

Re: Slow database creation

From
Andrej
Date:
2009/2/11 Rob Richardson <Rob.Richardson@rad-con.com>:
>  [... snipped ...]
> In order to test without disturbing the customer's production, I created a
> copy of their production database on the production server.  I often create
> test databases, and I've never seen the CREATE DATABASE command take longer
> than five seconds.  On the customer's production machine, the command took
> 167 seconds.
>  [... snipped ...]
> The customer's machines run Windows Server 2003.  My machine runs Windows XP
> Professional.  The application is written in C++.

Now I'm not a windows-expert by any stretch of the imagination,
but could there be some anti-virus products involved at the customer
site?


> Rob Richardson
> Product Engineer Software

Cheers,
Andrej

Re: Slow database creation

From
Alban Hertroys
Date:
On Feb 10, 2009, at 3:09 PM, Rob Richardson wrote:

> In order to test without disturbing the customer's production, I
> created a copy of their production database on the production
> server.  I often create test databases, and I've never seen the
> CREATE DATABASE command take longer than five seconds.  On the
> customer's production machine, the command took 167 seconds.
>
> Can anyone explain why it would take 167 seconds to create a
> database?  I am hoping that it's the same reason that data access is
> slowing our application by a factor of about 200.

Maybe someone accidentally restored a large database into template1?
That's the default database that gets copied to create a new database.

 From my experience it's not unusual for someone to create a database
from the psql prompt and then forgets to switch to the new database
before restoring data into it, and thus it ends up in template1... oops!

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4991c5e6747031805728238!