Re: [PERFORM] 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 3, 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:tpskB_read/skB_wrtn/skB_readkB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.1323.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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
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 [EMAIL PROTECTED] 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:tpskB_read/skB_wrtn/skB_readkB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.1323.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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
Joost Kraaijeveld [EMAIL PROTECTED] 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
