Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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: [EMAIL PROTECTED]:~$ 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. [EMAIL PROTECTED]:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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 3, and effective cache should be up around 200k In my current 8.1 situation I use shared_buffers = 4, 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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Title: Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Joost, On 11/15/05 8:35 AM, Joost Kraaijeveld [EMAIL PROTECTED] 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=50 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, theres no harm, but Ive 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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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=50” for a 2GB main memory machine, multiply the count by (your mem/2GB). If I understand correctly (I have 4GB ram): [EMAIL PROTECTED]:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Joost Kraaijeveld wrote: If I understand correctly (I have 4GB ram): [EMAIL PROTECTED]:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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 -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote: Joost Kraaijeveld wrote: If I understand correctly (I have 4GB ram): [EMAIL PROTECTED]:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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: [EMAIL PROTECTED] web: www.askesis.nl ---(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
Title: Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Joost, On 11/15/05 11:51 AM, Joost Kraaijeveld [EMAIL PROTECTED] wrote: On Tue, 2005-11-15 at 12:41 -0700, Steve Wampler wrote: Joost Kraaijeveld wrote: If I understand correctly (I have 4GB ram): [EMAIL PROTECTED]:~/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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 isnt 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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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: [EMAIL PROTECTED]:/tmp$ dd if=/dev/zero of=bigfile bs=8k count=100 100+0 records in 100+0 records out 819200 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: [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
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
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.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 ---(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
Hi Dave, On Mon, 2005-11-07 at 08:51 +, 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? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.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: [EMAIL PROTECTED] web: www.askesis.nl ---(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
-Original Message- From: Joost Kraaijeveld [mailto:[EMAIL PROTECTED] 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 ---(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??
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
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, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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: [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
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? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
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.000.000.50 98.510.00 Device: sda sdb rrqm/s 0.000.00 wrqm/s 14.00 611.00 r/s 0.001.00 w/s 3.00201.00 rsec/s 0.0032.00 wsec/s 136.00 6680.00 rkB/s 0.0016.00 wkB/s 68.00 3340.00 avgrq-sz45.33 33.23 avgqu-sz0.00145.67 await 0.67767.19 svctm 0.674.97 %util 0.20100.30 -- 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 6: explain analyze is your friend
