pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
22+ messages / 9 participants
[nested] [flat]
* Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
@ 2005-11-06 13:30 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 14:47 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Alex Turner <armtuk@gmail.com>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
0 siblings, 3 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-06 13:30 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am experiencing very long update queries and I want to know if it
reasonable to expect them to perform better.
The query below is running for more than 1.5 hours (5500 seconds) now,
while the rest of the system does nothing (I don't even type or move a
mouse...).
- Is that to be expected?
- Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given
the fact that fsync is off? (Note: with bonnie++ I get write
performance > 50 MB/sec and read performace > 70 MB/sec with > 2000
read/write ops /sec?
- Does anyone else have any experience with the 3Ware RAID controller
(which is my suspect)?
- Any good idea how to determine the real botleneck if this is not the
performance I can expect?
My hard- and software:
- PostgreSQL 8.0.3
- Debian 3.1 (Sarge) AMD64
- Dual Opteron
- 4GB RAM
- 3ware Raid5 with 5 disks
Pieces of my postgresql.conf (All other is default):
shared_buffers = 7500
work_mem = 260096
fsync=false
effective_cache_size = 32768
The query with explain (amount and orderbedrag_valuta are float8,
ordernummer and ordernumber int4):
explain update prototype.orders set amount =
odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber =
odbc.orders.ordernummer;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=50994.74..230038.17 rows=1104379 width=466)
Hash Cond: ("outer".ordernumber = "inner".ordernummer)
-> Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455)
-> Hash (cost=48233.79..48233.79 rows=1104379 width=15)
-> Seq Scan on orders (cost=0.00..48233.79 rows=1104379
width=15)
Sample output from iostat during query (about avarage):
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
hdc 0.00 0.00 0.00 0 0
sda 0.00 0.00 0.00 0 0
sdb 187.13 23.76 8764.36 24 8852
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-06 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2005-11-06 17:17 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> I am experiencing very long update queries and I want to know if it
> reasonable to expect them to perform better.
Does that table have any triggers that would fire on the update?
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-06 19:33 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-06 19:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Sun, 2005-11-06 at 12:17 -0500, Tom Lane wrote:
> Does that table have any triggers that would fire on the update?
Alas, no trigger, constrainst, foreign keys, indixes (have I forgotten
something?)
All queries are slow. E.g (after vacuum):
select objectid from prototype.orders
Explain analyse (with PgAdmin):
Seq Scan on orders (cost=0.00..58211.79 rows=1104379 width=40) (actual
time=441.971..3252.698 rows=1104379 loops=1)
Total runtime: 5049.467 ms
Actual execution time: 82163 MS (without getting the data)
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-06 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 04:25 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2005-11-06 20:26 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> Explain analyse (with PgAdmin):
> ...
> Total runtime: 5049.467 ms
> Actual execution time: 82163 MS (without getting the data)
I'm confused --- where's the 82sec figure coming from, exactly?
We've heard reports of performance issues in PgAdmin with large
result sets ... if you do the same query in psql, what happens?
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-07 04:25 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 04:37 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-07 04:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote:
> I'm confused --- where's the 82sec figure coming from, exactly?
From actually executing the query.
From PgAdmin:
-- Executing query:
select objectid from prototype.orders
Total query runtime: 78918 ms.
Data retrieval runtime: 188822 ms.
1104379 rows retrieved.
> We've heard reports of performance issues in PgAdmin with large
> result sets ... if you do the same query in psql, what happens?
jkr@Panoramix:~/postgresql$ time psql muntdev -c "select objectid from
prototype.orders" > output.txt
real 0m5.554s
user 0m1.121s
sys 0m0.470s
Now *I* am confused. What does PgAdmin do more than giving the query to
the database?
(BTW: I have repeated both measurements and the numbers above were all
from the last measurement I did and are about average)
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 04:25 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-07 04:37 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-07 05:04 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 05:46 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 2 replies; 22+ messages in thread
From: Christopher Kings-Lynne @ 2005-11-07 04:37 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> Now *I* am confused. What does PgAdmin do more than giving the query to
> the database?
It builds it into the data grid GUI object.
Chris
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 04:25 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 04:37 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2005-11-07 05:04 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-07 05:04 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Mon, 2005-11-07 at 12:37 +0800, Christopher Kings-Lynne wrote:
> > Now *I* am confused. What does PgAdmin do more than giving the query to
> > the database?
>
> It builds it into the data grid GUI object.
Is that not the difference between the total query runtime and the data
retrieval runtime (see below)?
-- Executing query:
select objectid from prototype.orders
Total query runtime: 78918 ms.
Data retrieval runtime: 188822 ms.
1104379 rows retrieved.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 04:25 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 04:37 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2005-11-07 05:46 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-07 05:46 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Hi Christopher,
On Mon, 2005-11-07 at 12:37 +0800, Christopher Kings-Lynne wrote:
> > Now *I* am confused. What does PgAdmin do more than giving the query to
> > the database?
>
> It builds it into the data grid GUI object.
But my initial question was about a query that does not produce data at
all (well, a response from the server saying it is finished). I broke
that query off after several hours.
I am now running the query from my initial question with psql (now for
>1 hour, in a transaction, fsyn off).
Some statistics :
uptime:
06:35:55 up 9:47, 6 users, load average: 7.08, 7.21, 6.08
iostat -x -k 1 (this output appears to be representative):
avg-cpu: %user %nice %sys %iowait %idle
1.00 0.00 0.50 98.51 0.00
Device: sda sdb
rrqm/s 0.00 0.00
wrqm/s 14.00 611.00
r/s 0.00 1.00
w/s 3.00 201.00
rsec/s 0.00 32.00
wsec/s 136.00 6680.00
rkB/s 0.00 16.00
wkB/s 68.00 3340.00
avgrq-sz 45.33 33.23
avgqu-sz 0.00 145.67
await 0.67 767.19
svctm 0.67 4.97
%util 0.20 100.30
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-07 14:47 ` Alex Turner <armtuk@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: Alex Turner @ 2005-11-07 14:47 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-performance
Where are the pg_xlog and data directories with respect to each other?
From this IOStat it looks like they might be on the same partition,
which is not ideal, and actualy surprising that throughput is this
good. You need to seperate pg_xlog and data directories to get any
kind of reasonable performance. Also don't use RAID 5 - RAID 5 bites,
no really - it bites. Use multiple RAID 1s, or RAID 10s, you will get
better performance. 50MB/70MB is about the same as you get from a
single disk or a RAID 1.
We use 2x9506S8MI controlers, and have maintained excellent
performance with 2xRAID 10 and 2xRAID 1. Make sure you get the
firmware update if you have these controllers though.
Alex Turner
NetEconomist
On 11/6/05, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
> Hi,
>
> I am experiencing very long update queries and I want to know if it
> reasonable to expect them to perform better.
>
> The query below is running for more than 1.5 hours (5500 seconds) now,
> while the rest of the system does nothing (I don't even type or move a
> mouse...).
>
> - Is that to be expected?
> - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given
> the fact that fsync is off? (Note: with bonnie++ I get write
> performance > 50 MB/sec and read performace > 70 MB/sec with > 2000
> read/write ops /sec?
> - Does anyone else have any experience with the 3Ware RAID controller
> (which is my suspect)?
> - Any good idea how to determine the real botleneck if this is not the
> performance I can expect?
>
> My hard- and software:
>
> - PostgreSQL 8.0.3
> - Debian 3.1 (Sarge) AMD64
> - Dual Opteron
> - 4GB RAM
> - 3ware Raid5 with 5 disks
>
> Pieces of my postgresql.conf (All other is default):
> shared_buffers = 7500
> work_mem = 260096
> fsync=false
> effective_cache_size = 32768
>
>
>
> The query with explain (amount and orderbedrag_valuta are float8,
> ordernummer and ordernumber int4):
>
> explain update prototype.orders set amount =
> odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber =
> odbc.orders.ordernummer;
> QUERY PLAN
> -----------------------------------------------------------------------------
> Hash Join (cost=50994.74..230038.17 rows=1104379 width=466)
> Hash Cond: ("outer".ordernumber = "inner".ordernummer)
> -> Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455)
> -> Hash (cost=48233.79..48233.79 rows=1104379 width=15)
> -> Seq Scan on orders (cost=0.00..48233.79 rows=1104379
> width=15)
>
>
> Sample output from iostat during query (about avarage):
> Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
> hdc 0.00 0.00 0.00 0 0
> sda 0.00 0.00 0.00 0 0
> sdb 187.13 23.76 8764.36 24 8852
>
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-14 23:51 ` Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 22+ messages in thread
From: Dave Cramer @ 2005-11-14 23:51 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
Joost,
I've got experience with these controllers and which version do you
have. I'd expect to see higher than 50MB/s although I've never tried
RAID 5
I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series
I would also suggest that shared buffers should be higher than 7500,
closer to 30000, and effective cache should be up around 200k
work_mem is awfully high, remember that this will be given to each
and every connection and can be more than 1x this number per
connection depending on the number of sorts
done in the query.
fsync=false ? I'm not even sure why we have this option, but I'd
never set it to false.
Dave
On 6-Nov-05, at 8:30 AM, Joost Kraaijeveld wrote:
> Hi,
>
> I am experiencing very long update queries and I want to know if it
> reasonable to expect them to perform better.
>
> The query below is running for more than 1.5 hours (5500 seconds) now,
> while the rest of the system does nothing (I don't even type or move a
> mouse...).
>
> - Is that to be expected?
> - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low,
> given
> the fact that fsync is off? (Note: with bonnie++ I get write
> performance > 50 MB/sec and read performace > 70 MB/sec with > 2000
> read/write ops /sec?
> - Does anyone else have any experience with the 3Ware RAID controller
> (which is my suspect)?
> - Any good idea how to determine the real botleneck if this is not the
> performance I can expect?
>
> My hard- and software:
>
> - PostgreSQL 8.0.3
> - Debian 3.1 (Sarge) AMD64
> - Dual Opteron
> - 4GB RAM
> - 3ware Raid5 with 5 disks
>
> Pieces of my postgresql.conf (All other is default):
> shared_buffers = 7500
> work_mem = 260096
> fsync=false
> effective_cache_size = 32768
>
>
>
> The query with explain (amount and orderbedrag_valuta are float8,
> ordernummer and ordernumber int4):
>
> explain update prototype.orders set amount =
> odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber =
> odbc.orders.ordernummer;
> QUERY PLAN
> ----------------------------------------------------------------------
> -------
> Hash Join (cost=50994.74..230038.17 rows=1104379 width=466)
> Hash Cond: ("outer".ordernumber = "inner".ordernummer)
> -> Seq Scan on orders (cost=0.00..105360.68 rows=3991868
> width=455)
> -> Hash (cost=48233.79..48233.79 rows=1104379 width=15)
> -> Seq Scan on orders (cost=0.00..48233.79 rows=1104379
> width=15)
>
>
> Sample output from iostat during query (about avarage):
> Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
> hdc 0.00 0.00 0.00 0 0
> sda 0.00 0.00 0.00 0 0
> sdb 187.13 23.76 8764.36 24 8852
>
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
@ 2005-11-15 16:35 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-15 16:35 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
Hi Dave,
On Mon, 2005-11-14 at 18:51 -0500, Dave Cramer wrote:
> Joost,
>
> I've got experience with these controllers and which version do you
> have. I'd expect to see higher than 50MB/s although I've never tried
> RAID 5
>
> I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series
OK, than there must be hope.
> I would also suggest that shared buffers should be higher than 7500,
> closer to 30000, and effective cache should be up around 200k
In my current 8.1 situation I use shared_buffers = 40000,
effective_cache_size = 131072 .
> work_mem is awfully high, remember that this will be given to each
> and every connection and can be more than 1x this number per
> connection depending on the number of sorts
> done in the query.
I use such a high number because I am the only user querying and my
queries do sorted joins etc.
> fsync=false ? I'm not even sure why we have this option, but I'd
> never set it to false.
I want as much speed as possible for a database conversion that MUST be
handled in 1 weekend (it lasts now, with the current speed almost 7
centuries. I may be off a millenium). If it fails because of hardware
problem (the only reason we want and need fsync?) we will try next
weekend until it finally goes right.
What I can see is that only the *write* performance of *long updates*
(and not inserts) are slow and they get slower in time: the first few
thousand go relatively fast, after that PostgreSQL crawls to a halt
(other "benchmarks" like bonnie++ or just dd'ing a big file don't have
this behavior).
I did notice that changing the I/O scheduler's nr_request from the
default 128 to 1024 or even 4096 made a remarkable performance
improvement. I suspect that experimenting with other I/O schedululers
could improve performance. But it is hard to find any useful
documentation about I/O schedulers.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-15 18:42 ` Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-16 10:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 2 replies; 22+ messages in thread
From: Luke Lonergan @ 2005-11-15 18:42 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
Joost,
On 11/15/05 8:35 AM, "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> wrote:
> thousand go relatively fast, after that PostgreSQL crawls to a halt
> (other "benchmarks" like bonnie++ or just dd'ing a big file don't have
> this behavior).
With RAID5, it could matter a lot what block size you run your ³dd bigfile²
test with. You should run ³dd if=/dev/zero of=bigfile bs=8k count=500000²
for a 2GB main memory machine, multiply the count by (<your mem>/2GB).
It is very important with the 3Ware cards to match the driver to the
firmware revision.
> I did notice that changing the I/O scheduler's nr_request from the
> default 128 to 1024 or even 4096 made a remarkable performance
> improvement. I suspect that experimenting with other I/O schedululers
> could improve performance. But it is hard to find any useful
> documentation about I/O schedulers.
>
You could try deadline, there¹s no harm, but I¹ve found that when you reach
the point of experimenting with schedulers, you are probably not addressing
the real problem.
>
On a 3Ware 9500 with HW RAID5 and 4 or more disks I think you should get
100MB/s write rate, which is double what Postgres can use. We find that
Postgres, even with fsync=false, will only run at a net COPY speed of about
8-12 MB/s, where 12 is the Bizgres number. 8.1 might do 10. But to get the
10 or 12, the WAL writing and other writing is about 4-5X more than the net
write speed, or the speed at which the input file is parsed and read into
the database.
So, if you can get your ³dd bigfile² test to write data at 50MB/s+ with a
blocksize of 8KB, you should be doing well enough.
Incidentally, we also find that using the XFS filesystem and setting the
readahead to 8MB or more is extremely beneficial for performance with the
3Ware cards (and with others, but especially for the older 3Ware cards).
Regards,
- Luke
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
@ 2005-11-15 19:31 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 19:41 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
1 sibling, 1 reply; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-15 19:31 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
Hi Luke,
On Tue, 2005-11-15 at 10:42 -0800, Luke Lonergan wrote:
> With RAID5, it could matter a lot what block size you run your ´dd
> bigfile¡ test with. You should run ´dd if=/dev/zero of=bigfile bs=8k
> count=500000¡ for a 2GB main memory machine, multiply the count by
> (<your mem>/2GB).
If I understand correctly (I have 4GB ram):
jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
Which looks suspicious: 26308 MB/sec???
> It is very important with the 3Ware cards to match the driver to the
> firmware revision.
OK, I am running 1 driver behind the firmware.
> I did notice that changing the I/O scheduler's nr_request from
> the
> default 128 to 1024 or even 4096 made a remarkable performance
> improvement. I suspect that experimenting with other I/O
> schedululers
> could improve performance. But it is hard to find any useful
> documentation about I/O schedulers.
>
> You could try deadline, thereÿs no harm, but Iÿve found that when you
> reach the point of experimenting with schedulers, you are probably not
> addressing the real problem.
It depends. I/O Schedulers (I assume) have a purpose: some schedulers
should be more appropriate for some circumstances. And maybe my specific
circumstances (converting a database with *many updates*) is a specific
circumstance. I really don't know....
> On a 3Ware 9500 with HW RAID5 and 4 or more disks I think you should
> get 100MB/s write rate, which is double what Postgres can use. We
> find that Postgres, even with fsync=false, will only run at a net COPY
> speed of about 8-12 MB/s, where 12 is the Bizgres number. 8.1 might
> do 10. But to get the 10 or 12, the WAL writing and other writing is
> about 4-5X more than the net write speed, or the speed at which the
> input file is parsed and read into the database.
As I have an (almost) seperate WAL disk: iostat does not show any
significant writing on the WAL disk....
> So, if you can get your ´dd bigfile¡ test to write data at 50MB/s+
> with a blocksize of 8KB, you should be doing well enough.
See above.
> Incidentally, we also find that using the XFS filesystem and setting
> the readahead to 8MB or more is extremely beneficial for performance
> with the 3Ware cards (and with others, but especially for the older
> 3Ware cards).
I don't have problems with my read performance but *only* with my
*update* performance (and not even insert performance). But than again I
am not the only one with these problems:
http://www.issociate.de/board/goto/894541/3ware_+_RAID5_
+_xfs_performance.html#msg_894541
http://lkml.org/lkml/2005/4/20/110
http://seclists.org/lists/linux-kernel/2005/Oct/1171.html
I am happy to share the tables against which I am running my checks....
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-15 19:41 ` Steve Wampler <swampler@noao.edu>
2005-11-15 19:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 22+ messages in thread
From: Steve Wampler @ 2005-11-15 19:41 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance
Joost Kraaijeveld wrote:
> If I understand correctly (I have 4GB ram):
>
> jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
> 1000000+0 records in
> 1000000+0 records out
> 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
>
> Which looks suspicious: 26308 MB/sec???
Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes.
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 19:41 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
@ 2005-11-15 19:51 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-16 06:07 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-15 19:51 UTC (permalink / raw)
To: Steve Wampler <swampler@noao.edu>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance
On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote:
> Joost Kraaijeveld wrote:
> > If I understand correctly (I have 4GB ram):
> >
> > jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
> > 1000000+0 records in
> > 1000000+0 records out
> > 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
> >
> > Which looks suspicious: 26308 MB/sec???
>
> Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes.
Oooops. This calculation error is not typical for my testing (I think ;-)).
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 19:41 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
2005-11-15 19:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-11-16 06:07 ` Luke Lonergan <llonergan@greenplum.com>
2005-11-16 06:57 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 22+ messages in thread
From: Luke Lonergan @ 2005-11-16 06:07 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; Steve Wampler <swampler@noao.edu>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
Joost,
On 11/15/05 11:51 AM, "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> wrote:
> On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote:
>> > Joost Kraaijeveld wrote:
>>> > > If I understand correctly (I have 4GB ram):
>>> > >
>>> > > jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
>>> > > 1000000+0 records in
>>> > > 1000000+0 records out
>>> > > 8192000000 bytes transferred in 304.085269 seconds (26939812 bytes/sec)
>>> > >
>>> > > Which looks suspicious: 26308 MB/sec???
>> >
>> > Eh? That looks more like ~25.7 MB/sec, assuming 1MB = 1024*1024 bytes.
> Oooops. This calculation error is not typical for my testing (I think ;-)).
Summarizing the two facts of note: the write result is 1/4 of what you
should be getting, and you are running 1 driver behind the firmware.
You might update your driver, rerun the test, and if you still have the slow
result, verify that your filesystem isn¹t fragmented (multiple undisciplined
apps on the same filesystem will do that).
WAL on a separate disk, on a separate controller? What is the write
performance there?
Regards,
- Luke
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 19:41 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
2005-11-15 19:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-16 06:07 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
@ 2005-11-16 06:57 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-16 06:57 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Steve Wampler <swampler@noao.edu>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance
Hi Luke,
On Tue, 2005-11-15 at 22:07 -0800, Luke Lonergan wrote:
> You might update your driver,
I will do that (but I admit that I am not looking forward to it. When I
was young and did not make money with my computer, I liked challenges
like compiling kernels and not being able to boot the computer. Not any
more :-)).
>
> WAL on a separate disk, on a separate controller? What is the write
> performance there?
WAL is on a separate disk and a separate controller, write performance:
jkr@Panoramix:/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 166.499230 seconds (49201429 bytes/sec)
The quest continues...
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-14 23:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
@ 2005-11-16 10:17 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-16 10:17 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
Hi Luke,
> It is very important with the 3Ware cards to match the driver to the
> firmware revision.
> So, if you can get your ´dd bigfile¡ test to write data at 50MB/s+
> with a blocksize of 8KB, you should be doing well enough.
I recompiled my kernel, added the driver and:
jkr@Panoramix:~$ dmesg | grep 3w
3ware 9000 Storage Controller device driver for Linux v2.26.03.019fw.
scsi4 : 3ware 9000 Storage Controller
3w-9xxx: scsi4: Found a 3ware 9000 Storage Controller at 0xfd8ffc00,
IRQ: 28.
3w-9xxx: scsi4: Firmware FE9X 2.08.00.005, BIOS BE9X 2.03.01.052, Ports:
8.
jkr@Panoramix:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes transferred in 200.982055 seconds (40759858 bytes/sec)
Which is an remarkable increase in speed (38.9 MB/sec vs 25.7 MB/sec).
Thanks for your suggestions.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
@ 2005-11-07 08:51 Dave Page <dpage@vale-housing.co.uk>
2005-11-07 09:02 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 17:45 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 2 replies; 22+ messages in thread
From: Dave Page @ 2005-11-07 08:51 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Joost Kraaijeveld
> Sent: 07 November 2005 04:26
> To: Tom Lane
> Cc: Pgsql-Performance
> Subject: Re: [PERFORM] Performance PG 8.0 on dual opteron /
> 4GB / 3ware
>
> Hi Tom,
>
> On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote:
> > I'm confused --- where's the 82sec figure coming from, exactly?
> >From actually executing the query.
>
> >From PgAdmin:
>
> -- Executing query:
> select objectid from prototype.orders
>
> Total query runtime: 78918 ms.
> Data retrieval runtime: 188822 ms.
> 1104379 rows retrieved.
>
>
> > We've heard reports of performance issues in PgAdmin with large
> > result sets ... if you do the same query in psql, what happens?
> jkr@Panoramix:~/postgresql$ time psql muntdev -c "select objectid from
> prototype.orders" > output.txt
>
> real 0m5.554s
> user 0m1.121s
> sys 0m0.470s
>
>
> Now *I* am confused. What does PgAdmin do more than giving
> the query to
> the database?
Nothing - it just uses libpq's pqexec function. The speed issue in
pgAdmin is rendering the results in the grid which can be slow on some
OS's due to inefficiencies in some grid controls with large data sets.
That's why we give 2 times - the first is the query runtime on the
server, the second is data retrieval and rendering (iirc, it's been a
while).
Regards, Dave
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-07 08:51 Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Dave Page <dpage@vale-housing.co.uk>
@ 2005-11-07 09:02 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 22+ messages in thread
From: Joost Kraaijeveld @ 2005-11-07 09:02 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Hi Dave,
On Mon, 2005-11-07 at 08:51 +0000, Dave Page wrote:
> > On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote:
> > > I'm confused --- where's the 82sec figure coming from, exactly?
> > >From actually executing the query.
> >
> > >From PgAdmin:
> >
> > -- Executing query:
> > select objectid from prototype.orders
> >
> > Total query runtime: 78918 ms.
> > Data retrieval runtime: 188822 ms.
> > 1104379 rows retrieved.
> >
> >
> > > We've heard reports of performance issues in PgAdmin with large
> > > result sets ... if you do the same query in psql, what happens?
> > jkr@Panoramix:~/postgresql$ time psql muntdev -c "select objectid from
> > prototype.orders" > output.txt
> >
> > real 0m5.554s
> > user 0m1.121s
> > sys 0m0.470s
> >
> >
> > Now *I* am confused. What does PgAdmin do more than giving
> > the query to
> > the database?
>
> Nothing - it just uses libpq's pqexec function. The speed issue in
> pgAdmin is rendering the results in the grid which can be slow on some
> OS's due to inefficiencies in some grid controls with large data sets.
> That's why we give 2 times - the first is the query runtime on the
> server, the second is data retrieval and rendering (iirc, it's been a
> while).
That is what I thought, but what could explain the difference in query
runtime (78 seconds versus 5 seconds) ?
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
2005-11-07 08:51 Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Dave Page <dpage@vale-housing.co.uk>
@ 2005-11-07 17:45 ` Andreas Pflug <pgadmin@pse-consulting.de>
1 sibling, 0 replies; 22+ messages in thread
From: Andreas Pflug @ 2005-11-07 17:45 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Dave Page wrote:
>>
>>
>>Now *I* am confused. What does PgAdmin do more than giving
>>the query to
>>the database?
>
>
> Nothing - it just uses libpq's pqexec function. The speed issue in
> pgAdmin is rendering the results in the grid which can be slow on some
> OS's due to inefficiencies in some grid controls with large data sets.
> That's why we give 2 times - the first is the query runtime on the
> server, the second is data retrieval and rendering (iirc, it's been a
> while).
yrnc.
Query runtime includes data transfer to the client, i.e. until libpq
returns the set, second time is retrieving data from libpq and rendering.
Regards,
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
@ 2005-11-07 09:17 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 22+ messages in thread
From: Dave Page @ 2005-11-07 09:17 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> -----Original Message-----
> From: Joost Kraaijeveld [mailto:J.Kraaijeveld@Askesis.nl]
> Sent: 07 November 2005 09:03
> To: Dave Page
> Cc: Tom Lane; Pgsql-Performance
> Subject: RE: [PERFORM] Performance PG 8.0 on dual opteron /
> 4GB / 3ware
>
> > Nothing - it just uses libpq's pqexec function. The speed issue in
> > pgAdmin is rendering the results in the grid which can be
> slow on some
> > OS's due to inefficiencies in some grid controls with large
> data sets.
> > That's why we give 2 times - the first is the query runtime on the
> > server, the second is data retrieval and rendering (iirc,
> it's been a
> > while).
> That is what I thought, but what could explain the difference in query
> runtime (78 seconds versus 5 seconds) ?
Not in terms of our code - we obviously do a little more than just run
the query, but I can't spot anything in there that should be
non-constant time.
Don't suppose it's anything as simple as you vacuuming in between is it?
Regards, Dave
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2005-11-16 10:17 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-06 13:30 Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian?? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-06 19:33 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-06 20:26 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Tom Lane <tgl@sss.pgh.pa.us>
2005-11-07 04:25 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 04:37 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-11-07 05:04 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 05:46 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 14:47 ` Alex Turner <armtuk@gmail.com>
2005-11-14 23:51 ` Dave Cramer <pg@fastcrypt.com>
2005-11-15 16:35 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 18:42 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-15 19:31 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-15 19:41 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Steve Wampler <swampler@noao.edu>
2005-11-15 19:51 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-16 06:07 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Luke Lonergan <llonergan@greenplum.com>
2005-11-16 06:57 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-16 10:17 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 08:51 Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Dave Page <dpage@vale-housing.co.uk>
2005-11-07 09:02 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-11-07 17:45 ` Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Andreas Pflug <pgadmin@pse-consulting.de>
2005-11-07 09:17 Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Dave Page <dpage@vale-housing.co.uk>
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