[PERFORM] Partial index usage
Hi All, I have these indexes on a table: CREATE INDEX uidx_product_partno_producer_id ON product USING btree (partno, producer_id); CREATE INDEX idx_product_partno ON product USING btree (partno); Can I safely delete the second one? Will postgresql use (partno,producer_id) when it only needs to order by partno? (partno is a text field, producer_id is int4). Index sizes: 172MB and 137MB. I guess if I only had one index, it would save memory and increase performance. Another pair of incides, 144MB and 81MB respecively: CREATE INDEX idx_product_producer_uploads ON product USING btree (producer_id, am_upload_status_id); CREATE INDEX idx_product_producer_id ON product USING btree (producer_id); am_upload_status_id is also an int4. Can I delete the second index without performance drawback? Thanks, Laszlo -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
Arjen van der Meijden acmmail...@tweakers.net writes: When we purchased our Perc 5/e with MD1000 filled with 15 15k rpm sas disks, my colleague actually spend some time benchmarking the PERC and a ICP Vortex (basically a overclocked Adaptec) on those drives. Unfortunately he doesn't have too many comparable results, but it basically boiled down to quite good scores for the PERC and a bit less for the ICP Vortex. IOMeter sequential reads are above 300MB/s for the RAID5 and above 240MB/s for a RAID10 (and winbench99 versions range from 400+ to 600+MB/s). FWIW those are pretty terrible numbers for fifteen 15k rpm drives. They're about what you would expect if for a PCI-X card which was bus bandwidth limited. A PCI-e card should be able to get about 3x that from the drives. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] I/O increase after upgrading to 8.3.5
On Sun, Feb 15, 2009 at 6:35 PM, Greg Smith gsm...@gregsmith.com wrote: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm goes over this topic, with Appendix B: pg_stat_bgwriter sample analysis covering a look at what to do based on a pg_stat_bgwriter snapshot. Wonderful, thank you. Alexander. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Partial index usage
Laszlo Nagy wrote: Hi All, I have these indexes on a table: CREATE INDEX uidx_product_partno_producer_id ON product USING btree (partno, producer_id); CREATE INDEX idx_product_partno ON product USING btree (partno); Can I safely delete the second one? You can safely delete BOTH in that it won't hurt your data, only potentially hurt performance. Deleting the index on (partno) should somewhat improve insert performance and performance on updates that can't be done via HOT. However, the index on (partno, producer_id) is requires more storage and memory than the index on just (partno). AFAIK it's considerably slower to scan. Within a transaction, drop the second index then run the query of interest with EXPLAIN ANALYZE to determine just how much slower - then ROLLBACK to undo the index drop. You'll lock out other transactions while you're doing this, but you won't make any permanent changes and you can cancel it at any time. Will postgresql use (partno,producer_id) when it only needs to order by partno? Yes. I guess if I only had one index, it would save memory and increase performance. Maybe. If they both fit into memory along with the main table data, then you might end up losing instead since the second index is smaller and should be somewhat faster to scan. am_upload_status_id is also an int4. Can I delete the second index without performance drawback? Same answer as above - test it and find out. You may win or lose depending on your workload, table sizes, available memory, etc. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] suggestions for postgresql setup on Dell 2950 , PERC6i controller
BTW our Machine got build with 8 15k drives in raid10 , from bonnie++ results its looks like the machine is able to do 400 Mbytes/s seq write and 550 Mbytes/s read. the BB cache is enabled with 256MB sda6 -- xfs with default formatting options. sda7 -- mkfs.xfs -f -d sunit=128,swidth=512 /dev/sda7 sda8 -- ext3 (default) it looks like mkfs.xfs options sunit=128 and swidth=512 did not improve io throughtput as such in bonnie++ tests . it looks like ext3 with default options performed worst in my case. regds -- mallah NOTE: observations made in this post are interpretations by the poster only which may or may not be indicative of the true suitablity of the filesystem. On Mon, Feb 16, 2009 at 7:01 PM, Gregory Stark st...@enterprisedb.com wrote: Arjen van der Meijden acmmail...@tweakers.net writes: When we purchased our Perc 5/e with MD1000 filled with 15 15k rpm sas disks, my colleague actually spend some time benchmarking the PERC and a ICP Vortex (basically a overclocked Adaptec) on those drives. Unfortunately he doesn't have too many comparable results, but it basically boiled down to quite good scores for the PERC and a bit less for the ICP Vortex. IOMeter sequential reads are above 300MB/s for the RAID5 and above 240MB/s for a RAID10 (and winbench99 versions range from 400+ to 600+MB/s). FWIW those are pretty terrible numbers for fifteen 15k rpm drives. They're about what you would expect if for a PCI-X card which was bus bandwidth limited. A PCI-e card should be able to get about 3x that from the drives. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] TCP network cost
Recently I've been working on improving the performance of a system that delivers files stored in postgresql as bytea data. I was surprised at just how much a penalty I find moving from a domain socket connection to a TCP connection, even localhost. For one particular 40MB file (nothing outragous) I see ~ 2.5 sec. to download w/ the domain socket, but ~ 45 sec for a TCP connection (either localhost, name of localhost, or from another machine 5 hops away (on campus - gigabit LAN) Similar numbers for 8.2.3 or 8.3.6 (on Linux/Debian etch + backports) So, why the 20 fold penalty for using TCP? Any clues on how to trace what's up in the network IO stack? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] TCP network cost
On Feb 17, 2009, at 12:04 AM, Ross J. Reedstrom wrote: Recently I've been working on improving the performance of a system that delivers files stored in postgresql as bytea data. I was surprised at just how much a penalty I find moving from a domain socket connection to a TCP connection, even localhost. For one particular 40MB file (nothing outragous) I see ~ 2.5 sec. to download w/ the domain socket, but ~ 45 sec for a TCP connection (either localhost, name of localhost, or from another machine 5 hops away (on campus - gigabit LAN) Similar numbers for 8.2.3 or 8.3.6 (on Linux/Debian etch + backports) So, why the 20 fold penalty for using TCP? Any clues on how to trace what's up in the network IO stack? Try running tests with ttcp to eliminate any PostgreSQL overhead and find out the real bandwidth between the two machines. If its results are also slow, you know the problem is TCP related and not PostgreSQL related. Cheers, Rusty -- Rusty Conover rcono...@infogears.com InfoGears Inc / GearBuyer.com / FootwearBuyer.com http://www.infogears.com http://www.gearbuyer.com http://www.footwearbuyer.com
Re: [PERFORM] TCP network cost
On Tue, 17 Feb 2009, Rusty Conover wrote: On Feb 17, 2009, at 12:04 AM, Ross J. Reedstrom wrote: Recently I've been working on improving the performance of a system that delivers files stored in postgresql as bytea data. I was surprised at just how much a penalty I find moving from a domain socket connection to a TCP connection, even localhost. For one particular 40MB file (nothing outragous) I see ~ 2.5 sec. to download w/ the domain socket, but ~ 45 sec for a TCP connection (either localhost, name of localhost, or from another machine 5 hops away (on campus - gigabit LAN) Similar numbers for 8.2.3 or 8.3.6 (on Linux/Debian etch + backports) So, why the 20 fold penalty for using TCP? Any clues on how to trace what's up in the network IO stack? Try running tests with ttcp to eliminate any PostgreSQL overhead and find out the real bandwidth between the two machines. If its results are also slow, you know the problem is TCP related and not PostgreSQL related. note that he saw problems even on localhost. in the last couple of months I've seen a lot of discussin on the linux-kernel list about the performance of localhost. unfortunantly those fixes are only in the 2.6.27.x and 2.6.28.x -stable kernels. David Lang -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
