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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx
web: www.askesis.nl 




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux