From: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Mon, 24 Nov 2008 00:11:40 +0100
Message-ID: <4929E32C.8080400@fuzzy.cz> (raw)
In-Reply-To: <ggcihb$1v26$2@news.hub.org>
References: <gg1sv3$27fr$1@news.hub.org>
<49253FE1.3070706@archonet.com>
<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
<op.ukw669uvcigqcu@soyouz>
<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
<op.ukze24t0cigqcu@soyouz>
<E3AFB54F617D47F4BE652E85ECC67153@andrusnotebook>
<op.uk1nbqcgcigqcu@soyouz>
<AD6DEEFF0CE54A3982082A2CA7C2F722@andrusnotebook>
<4929A533.5030203@fuzzy.cz>
<ggcihb$1v26$2@news.hub.org>
> My test computer has PostgreSql 8.3, 4 GB RAM, SSD disks, Intel X2Extreme
> CPU
> So it is much faster than this prod server.
> No idea how to emulate this environment.
> I can create new db in prod server as old copy but this can be used in late
> night only.
Well, a faster but comparable system may not be a problem - the query
might run 10 times faster, but it still will be slow (say 40 seconds
instead of 8 minutes).
What is a problem is a different I/O system - SSD instead of traditional
drives in this case. I have no direct experience with with SSD yet, but
AFAIK the seek time is much better compared to regular drives (say 0.1ms
instead of 10ms, that is 100-times faster).
So you can't just put on old SATA drive into the test machine?
> Where to find script which clones some database in server? Something like
>
> CREATE DATABASE newdb AS SELECT * FROM olddb;
>
> It would be more convenient to run db cloning script from pgadmin command
> window.
> Only way I found is to use SSH with pg_dup/pg_restore. This requires SSH
> access to server and SSH port opening to public internet.
Yes, using pg_dump | pg_restore is probably the way to clone database.
But it will slow down the system, as it has to do a lot of I/O (and as
it seems to be a bottleneck already, I don't think this is a good idea).
> Or probably try to run CLUSTER command in prod server. Hopefully clustering
> by product id cannot make things slow
> too much.
As already noted, CLUSTER command causes exclusive lock on the database.
So this is an operation you'd like to do on production server ...
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Hash join on int takes 8..114 seconds
In-Reply-To: <4929E32C.8080400@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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