Skip site navigation (1) Skip section navigation (2)

posix advises ...

From: Hans-Juergen Schoenig <postgres(at)cybertec(dot)at>
To: pgsql-patches <pgsql-patches(at)postgresql(dot)org>, Zoltan Boszormenyi <zb(at)cybertec(dot)at>
Subject: posix advises ...
Date: 2008-05-11 09:53:04
Message-ID: 4826C200.8060807@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
hello everybody,

recently we had a bit of a nightmare with some kernels and concurrent 
seq scans.
the thing we encountered was the following: a single "SELECT COUNT(*) 
FROM table" on a big table (50 gb) gave us constant 350 mb / sec I/O. as 
soon as a second scan dropped in speed dropped to 2 mb / sec. first i 
thought that some random I/O dropped in but synchronous scans worked 
fine. we found out that there is some madness in some linux kernel / 
controller combinations causing this issue.
it did some tests on my local boxes which was clearly not affected by 
this problem and I have seen a single SATA disks dropping from 65 mb / 
sec to around 45. this is not good.
i found a patch by grep stark implementing posix_fadvise for bitmap 
scans. i quickly hacked in suggestions to issue the same advises when a 
seq scan is done.
the impact was surprisingly high. single scans went up from 65 mb / sec 
to something around 70. concurrent scans are basically at steady, high 
speed - no dropping I/O speed anymore until something like 16 scans or so.
even the broken controller when up from "350mb -> 2mb" to "350 -> 50mb". 
by replacing the kernel and the driver we see steady behavior here as 
well now.

maybe it is worth to discuss posix_fadvise.
we hacked up a simple patch based on greg's work which nicely fixed the 
problem for us (brute force).
we also made some simple autoconf hack to check for broken posix_fadvise.
maybe people want to test if they see similar performance differences.
if a patch like that is likely to be accepted we would hack up some more 
clean implementation.

    many thanks,

       hans

-- 
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com


Attachment: preread-seq-tunable.diff.gz
Description: application/x-gzip (96.3 KB)

Responses

pgsql-hackers by date

Next:From: Hans-Juergen SchoenigDate: 2008-05-11 10:05:44
Subject: XIDs and big boxes again ...
Previous:From: Tino WildenhainDate: 2008-05-11 09:13:05
Subject: Re: Setting a pre-existing index as a primary key

pgsql-patches by date

Next:From: davegDate: 2008-05-11 11:30:47
Subject: pg_dump lock timeout
Previous:From: NikhilsDate: 2008-05-11 07:57:49
Subject: Re: [badalex@gmail.com: Re: [BUGS] Problem identifying constraints which should not be inherited]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group