From: Mario Splivalo <mario.splivalo@megafon.hr>
To: Pierre C <lists@peufeu.com>
Cc: mark.kirkwood@catalyst.net.nz
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
Date: Thu, 02 Dec 2010 09:36:40 +0100
Message-ID: <4CF75A98.3080709@megafon.hr> (raw)
In-Reply-To: <op.vm0055s0eorkce@apollo13>
References: <4CF24103.8030209@megafon.hr>
<4CF3522B.2050203@catalyst.net.nz>
<4CF39CF4.2050506@megafon.hr>
<op.vmxyi50oeorkce@apollo13>
<4CF587B0.5020003@megafon.hr>
<op.vm0055s0eorkce@apollo13>
On 12/01/2010 09:43 AM, Pierre C wrote:
>
> Note that in both cases postgres reports that the FK checks take 92-120
> milliseconds... which is a normal time for about 4000 rows.
> Inserting 4000 lines with just a few fields like you got should take
> quite much less than 1 s...
>
> Where the rest of the time goes, I have no idea. Disk thrashing ? Locks
> ? Gremlins ?
>
> - try it on a fresh copy of all your tables (CREATE TABLE, INSERT INTO
> SELECT)
> - try to put the WAL on a separate physical disk (or do a check with
> fsync=off)
> - try it on another computer
> - try it on another harddisk
> - run oprofile on a debug compile of postgres
> - it could even be the process title updates (I don't think so but...)
> - try a ramdisk tablespace
I'm allready running it with fsync=off. The funny thing is, as I add new
realm it runs fine until the history table grows around 5M rows. After
that the slowdown is huge.
I'm trying this on new hardware this weekend, I'll post here the results.
Mario
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: mario.splivalo@megafon.hr, lists@peufeu.com, mark.kirkwood@catalyst.net.nz
Subject: Re: SELECT INTO large FKyed table is slow
In-Reply-To: <4CF75A98.3080709@megafon.hr>
* 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