SEO

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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



---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match