Re: Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??

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
>