Re: [PERFORM] Restart time
Rajesh Kumar Mallah a écrit : On 12/5/06, *Tom Lane* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Jean Arnaud [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] writes: Is there a relation between database size and PostGreSQL restart duration ? No. Does anyone now the behavior of restart time ? It depends on how many updates were applied since the last checkpoint before the crash. If you're talking about startup of a cleanly-shut-down database, it should be pretty much constant time. Dear Sir, Startup time of a clean shutdown database is constant. But we still face problem when it comes to shutting down. PostgreSQL waits for clients to finish gracefully. till date i have never been able to shutdown quickly (web application scenerio) and i tend to do pg_ctl -m immediate stop mostly. regards, tom lane Hi Thanks everybody for answers ! To be sure SGBD will stop before a certain time, I use function that is a combination of pg_ctl -m fast to stop most of process cleanly, and after few seconds, I send pg_ctl -m immediate to be shut down immediatly the system if not already stoped. This works pretty well in practice and offers a good compromise between clean and fast shutdown. Cheers -- --- Jean Arnaud --- Projet SARDES --- INRIA Rhône-Alpes ---(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] Hardware advice
Hi Alex, Please check out http://www.powerpostgresql.com/PerfList before you use RAID 5 for PostgreSQL. Anyhow, In a larger scale you end up in the response time of the I/O system for an read or write. The read is in modern RAID and SAN environments the part where you have to focus when you want to tune your system because most RAID and SAN system can buffer write. PostgreSQL does use the Linux file system cache which is normally much larger then the RAID or SAN cache for reading. This means whenever a PostgreSQL read goes to the RAID or SAN sub system the response time of the hard disk will become interesting. I guess you can imagine that multiple reads to the same spins are causing an delay in the response time. Alexandru, You should have two XEONs, what every your core count is. This would use the full benefit of the memory architecture. You know two FSBs and two memory channels. Cheers Sven Alex Turner schrieb: The test that I did - which was somewhat limited, showed no benefit splitting disks into seperate partitions for large bulk loads. The program read from one very large file and wrote the input out to two other large files. The totaly throughput on a single partition was close to the maximum theoretical for that logical drive, even though the process was reading and writing to three seperate places on the disk. I don't know what this means for postgresql setups directly, but I would postulate that the benefit from splitting pg_xlog onto a seperate spindle is not as great as it might once have been for large bulk transactions. I am therefore going to be going to a single 6 drive RAID 5 for my data wharehouse application because I want the read speed to be availalbe. I can benefit from fast reads when I want to do large data scans at the expense of slightly slower insert speed. Alex. On 12/5/06, *Alexandru Coseru* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello.. Thanks for the advices.. Actually , i'm waiting for the clovertown to show up on the market... Regards Alex - Original Message - From: Sven Geisler [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] To: Alexandru Coseru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org mailto:pgsql-performance@postgresql.org Sent: Tuesday, December 05, 2006 11:57 AM Subject: Re: [PERFORM] Hardware advice Hi Alexandru, Alexandru Coseru schrieb: [...] Question 1: The RAID layout should be: a) 2 hdd in raid 1 for system and pg_xlog and 6 hdd in raid10 for data ? b) 8 hdd in raid10 for all ? c) 2 hdd in raid1 for system , 2 hdd in raid1 for pg_xlog , 4 hdd in raid10 for data ? Obs: I'm going for setup a) , but i want to hear your thoughts as well. This depends on you data size. I think, option a and c are good. The potential bottleneck may the RAID 1 for pg_xlog if you have huge amount of updates and insert. What is about another setup 4 hdd in RAID 10 for System and pg_xlog - System partitions are normally not in heavy use and pg_xlog should be fast for writing. 4 hdd in RAID 10 for data. Question 2: (Don't want to start a flame here. but here is goes) What filesystem should i run for data ? ext3 or xfs ? The tables have ~ 15.000 rel_pages each. The biggest table has now over 30.000 pages. We have a database running with 60,000+ tables. The tables size is between a few kByte for the small tables and up to 30 GB for the largest one. We had no issue with ext3 in the past. Question 3: The block size in postgresql is 8kb. The strip size in the raid ctrl is 64k. Should i increase the pgsql block size to 16 or 32 or even 64k ? You should keep in mind that the file system has also a block size. Ext3 has as maximum 4k. I would set up the partitions aligned to the stripe size to prevent unaligned reads. I guess, you can imagine that a larger block size of postgresql may also end up in unaligned reads because the file system has a smaller block size. RAID Volume and File system set up 1. Make all partitions aligned to the RAID strip size. The first partition should be start at 128 kByte. You can do this with fdisk. after you created the partition switch to the expert mode (type x) and modify the begin of the partition (type b). You should change this value to 128 (default is 63). All other partition should also start on a multiple of 128 kByte. 2. Give the file system a hint that you work with larger block sizes. Ext3: mke2fs -b 4096 -j
Re: [PERFORM] Hardware advice
If your data is valuable I'd recommend against RAID5 ... see http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt performance aside, I'd advise against RAID5 in almost all circumstances. Why take chances ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Sven Geisler Sent: Wed 12/6/2006 1:09 AM To: Alex Turner Cc: Alexandru Coseru; pgsql-performance@postgresql.org Subject:Re: [PERFORM] Hardware advice Hi Alex, Please check out http://www.powerpostgresql.com/PerfList before you use RAID 5 for PostgreSQL. Anyhow, In a larger scale you end up in the response time of the I/O system for an read or write. The read is in modern RAID and SAN environments the part where you have to focus when you want to tune your system because most RAID and SAN system can buffer write. PostgreSQL does use the Linux file system cache which is normally much larger then the RAID or SAN cache for reading. This means whenever a PostgreSQL read goes to the RAID or SAN sub system the response time of the hard disk will become interesting. I guess you can imagine that multiple reads to the same spins are causing an delay in the response time. Alexandru, You should have two XEONs, what every your core count is. This would use the full benefit of the memory architecture. You know two FSBs and two memory channels. Cheers Sven Alex Turner schrieb: The test that I did - which was somewhat limited, showed no benefit splitting disks into seperate partitions for large bulk loads. The program read from one very large file and wrote the input out to two other large files. The totaly throughput on a single partition was close to the maximum theoretical for that logical drive, even though the process was reading and writing to three seperate places on the disk. I don't know what this means for postgresql setups directly, but I would postulate that the benefit from splitting pg_xlog onto a seperate spindle is not as great as it might once have been for large bulk transactions. I am therefore going to be going to a single 6 drive RAID 5 for my data wharehouse application because I want the read speed to be availalbe. I can benefit from fast reads when I want to do large data scans at the expense of slightly slower insert speed. Alex. On 12/5/06, *Alexandru Coseru* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello.. Thanks for the advices.. Actually , i'm waiting for the clovertown to show up on the market... Regards Alex - Original Message - From: Sven Geisler [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] To: Alexandru Coseru [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org mailto:pgsql-performance@postgresql.org Sent: Tuesday, December 05, 2006 11:57 AM Subject: Re: [PERFORM] Hardware advice Hi Alexandru, Alexandru Coseru schrieb: [...] Question 1: The RAID layout should be: a) 2 hdd in raid 1 for system and pg_xlog and 6 hdd in raid10 for data ? b) 8 hdd in raid10 for all ? c) 2 hdd in raid1 for system , 2 hdd in raid1 for pg_xlog , 4 hdd in raid10 for data ? Obs: I'm going for setup a) , but i want to hear your thoughts as well. This depends on you data size. I think, option a and c are good. The potential bottleneck may the RAID 1 for pg_xlog if you have huge amount of updates and insert. What is about another setup 4 hdd in RAID 10 for System and pg_xlog - System partitions are normally not in heavy use and pg_xlog should be fast for writing. 4 hdd in RAID 10 for data. Question 2: (Don't want to start a flame here. but here is goes) What filesystem should i run for data ? ext3 or xfs ? The tables have ~ 15.000 rel_pages each. The biggest table has now over 30.000 pages. We have a database running with 60,000+ tables. The tables size is between a few kByte for the small tables and up to 30 GB for the largest one. We had no issue with ext3 in the past. Question 3: The block size in postgresql is 8kb. The strip size in the raid ctrl is 64k. Should i increase the pgsql block size to 16 or 32 or even 64k ? You should keep in mind that the file system has also a block size. Ext3 has as maximum 4k. I would set up the partitions aligned to the stripe size to prevent unaligned reads. I guess, you can imagine that a larger block size of postgresql may also end up in unaligned reads because the file system has a smaller block size. RAID Volume and File system set up 1. Make all partitions aligned to
Re: [PERFORM] Locking in PostgreSQL?
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: Apparently I've completely misunderstood MVCC then My understanding is that unless you do a select ... for update then update the rows will not be locked . The discussion was about updates, not selects. Selects do not in general lock (except for ... for update, as you say). To (partially) answer the original question: The number of columns updated does not matter for the locking situation. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Locking in PostgreSQL?
Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Dave On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 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 ---(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] Locking in PostgreSQL?
On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer [EMAIL PROTECTED] wrote: Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. @Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or something like that. Roughly you can say, each UPDATE statement iterates through the affected table and locks the WHERE clause matching records (rows) exclusivly to prevent data inconsistancy during the UPDATE. After that your rows will be updated and the lock will be repealed. You can see this during an long lasting UPDATE by querying the pg_locks with joined pg_stats_activity (statement must be enabled). Dave On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? --Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 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 ---(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 CU, Jens -- ** Jens Schipkowski ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Locking in PostgreSQL?
On 6-Dec-06, at 8:20 AM, Jens Schipkowski wrote: On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer [EMAIL PROTECTED] wrote: Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. @Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or something like that. Roughly you can say, each UPDATE statement iterates through the affected table and locks the WHERE clause matching records (rows) exclusivly to prevent data inconsistancy during the UPDATE. After that your rows will be updated and the lock will be repealed. You can see this during an long lasting UPDATE by querying the pg_locks with joined pg_stats_activity (statement must be enabled). Apparently I've completely misunderstood MVCC then My understanding is that unless you do a select ... for update then update the rows will not be locked . Dave Dave On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? --Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 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 ---(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 CU, Jens -- ** Jens Schipkowski ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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: [GENERAL] [PERFORM] Locking in PostgreSQL?
Hi, Dave Cramer wrote: Apparently I've completely misunderstood MVCC then Probably not. You are both somewhat right. Jens Schipkowski wrote: Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. That almost right, RowExclusiveLock is a table level lock. An UPDATE acquires that, yes. Additionally there are row-level locks, which is what you're speaking about. An UPDATE gets an exclusive row-level lock on rows it updates. Please note however, that these row-level locks only block concurrent writers, not readers (MVCC lets the readers see the old, unmodified row). My understanding is that unless you do a select ... for update then update the rows will not be locked. Also almost right, depending on what you mean by 'locked'. A plain SELECT acquires an ACCESS SHARE lock on the table, but no row-level locks. Only a SELECT ... FOR UPDATE does row-level locking (shared ones here...) The very fine documentation covers that in [1]. Regards Markus [1]: PostgreSQL Documentation, Explicit Locking: http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [GENERAL] [PERFORM] Locking in PostgreSQL?
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. Apparently I've completely misunderstood MVCC then My understanding is that unless you do a select ... for update then update the rows will not be locked . I think it comes down to what you mean by RowExclusiveLock. In MVCC, writers don't block readers, so even if someone executes an update on a row, readers (SELECT statements) will not be blocked. So it's not a lock as such, more a I've updated this row, go find the new version if that's appropriate for your snapshot. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[PERFORM] File Systems Compared
All tests are with bonnie++ 1.03a Main components of system: 16 WD Raptor 150GB 1 RPM drives all in a RAID 10 ARECA 1280 PCI-Express RAID adapter with 1GB BB Cache (Thanks for the recommendation, Ron!) 32 GB RAM Dual Intel 5160 Xeon Woodcrest 3.0 GHz processors OS: SUSE Linux 10.1 All runs are with the write cache disabled on the hard disks, except for one additional test for xfs where it was enabled. I tested with ordered and writeback journaling modes for ext3 to see if writeback journaling would help over the default of ordered. The 1GB of battery backed cache on the RAID card was enabled for all tests as well. Tests are in order of increasing random seek performance. In my tests on this hardware, xfs is the decisive winner, beating all of the other file systems in performance on every single metric. 658 random seeks per second, 433 MB/sec sequential read, and 350 MB/sec sequential write seems decent enough, but not as high as numbers other people have suggested are attainable with a 16 disk RAID 10. 350 MB/sec sequential write with disk caches enabled versus 280 MB/ sec sequential write with disk caches disabled sure makes enabling the disk write cache tempting. Anyone run their RAIDs with disk caches enabled, or is this akin to having fsync off? ext3 (writeback data journaling mode): /usr/local/sbin/bonnie++ -d bonnie -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 78625 91 279921 51 112346 13 89463 96 417695 22 545.7 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 5903 99 + +++ + +++ 6112 99 + ++ + 18620 100 hulk4,64368M, 78625,91,279921,51,112346,13,89463,96,417695,22,545.7,0,16,5903,99,+++ ++,+++,+,+++,6112,99,+,+++,18620,100 ext3 (ordered data journaling mode): /usr/local/sbin/bonnie++ -d bonnie -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 74902 89 250274 52 123637 16 88992 96 417222 23 548.3 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 5941 97 + +++ + +++ 6270 99 + ++ + 18670 99 hulk4,64368M, 74902,89,250274,52,123637,16,88992,96,417222,23,548.3,0,16,5941,97,+++ ++,+++,+,+++,6270,99,+,+++,18670,99 reiserfs: /usr/local/sbin/bonnie++ -d bonnie -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 81004 99 269191 50 128322 16 87865 96 407035 28 550.3 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 + +++ + +++ + +++ + +++ + ++ + + +++ hulk4,64368M, 81004,99,269191,50,128322,16,87865,96,407035,28,550.3,0,16,+,+++,+ ,+++,+,+++,+,+++,+,+++,+,+++ jfs: /usr/local/sbin/bonnie++ -d bonnie/ -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 73246 80 268886 28 110465 9 89516 96 413897 21 639.5 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 3756 5 + +++ + +++ 23763 90 + ++ + 22371 70 hulk4,64368M, 73246,80,268886,28,110465,9,89516,96,413897,21,639.5,0,16,3756,5, +,+++,+,+++,23763,90,+,+++,22371,70 xfs (with write cache disabled on disks): /usr/local/sbin/bonnie++ -d bonnie/ -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --
Re: [PERFORM] File Systems Compared
Brian Wipf wrote: All tests are with bonnie++ 1.03a Thanks for posting these tests. Now I have actual numbers to beat our storage server provider about the head and shoulders with. Also, I found them interesting in and of themselves. These numbers are close enough to bus-saturation rates that I'd strongly advise new people setting up systems to go this route over spending money on some fancy storage area network solution- unless you need more HD space than fits nicely in one of these raids. If reliability is a concern, buy 2 servers and implement Sloni for failover. Brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] File Systems Compared
On Dec 6, 2006, at 16:40 , Brian Wipf wrote: All tests are with bonnie++ 1.03a [snip] Care to post these numbers *without* word wrapping? Thanks. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Bad iostat numbers
On Dec 5, 2006, at 8:54 PM, Greg Smith wrote: On Tue, 5 Dec 2006, Craig A. James wrote: I'm not familiar with the inner details of software RAID, but the only circumstance I can see where things would get corrupted is if the RAID driver writes a LOT of blocks to one disk of the array before synchronizing the others... You're talking about whether the discs in the RAID are kept consistant. While it's helpful with that, too, that's not the main reason a the battery-backed cache is so helpful. When PostgreSQL writes to the WAL, it waits until that data has really been placed on the drive before it enters that update into the database. In a normal situation, that means that you have to pause until the disk has physically written the blocks out, and that puts a fairly low upper limit on write performance that's based on how fast your drives rotate. RAID 0, RAID 1, none of that will speed up the time it takes to complete a single synchronized WAL write. When your controller has a battery-backed cache, it can immediately tell Postgres that the WAL write completed succesfully, while actually putting it on the disk later. On my systems, this results in simple writes going 2-4X as fast as they do without a cache. Should there be a PC failure, as long as power is restored before the battery runs out that transaction will be preserved. What Alex is rightly pointing out is that a software RAID approach doesn't have this feature. In fact, in this area performance can be even worse under SW RAID than what you get from a single disk, because you may have to wait for multiple discs to spin to the correct position and write data out before you can consider the transaction complete. So... the ideal might be a RAID1 controller with BBU for the WAL and something else, such as software RAID, for the main data array? Cheers, Steve ---(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] File Systems Compared
Brian, On 12/6/06 8:02 AM, Brian Hurt [EMAIL PROTECTED] wrote: These numbers are close enough to bus-saturation rates PCIX is 1GB/s + and the memory architecture is 20GB/s+, though each CPU is likely to obtain only 2-3GB/s. We routinely achieve 1GB/s I/O rate on two 3Ware adapters and 2GB/s on the Sun X4500 with ZFS. advise new people setting up systems to go this route over spending money on some fancy storage area network solution People buy SANs for interesting reasons, some of them having to do with the manageability features of high end SANs. I've heard it said in those cases that performance doesn't matter much. As you suggest, database replication provides one of those features, and Solaris ZFS has many of the data management features found in high end SANs. Perhaps we can get the best of both? In the end, I think SAN vs. server storage is a religious battle. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] File Systems Compared
Hi, Alexander Staubo wrote: Care to post these numbers *without* word wrapping? Thanks. How is one supposed to do that? Care giving an example? Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] File Systems Compared
As you suggest, database replication provides one of those features, and Solaris ZFS has many of the data management features found in high end SANs. Perhaps we can get the best of both? In the end, I think SAN vs. server storage is a religious battle. I agree. I have many people that want to purchase a SAN because someone told them that is what they need... Yet they can spend 20% of the cost on two external arrays and get incredible performance... We are seeing great numbers from the following config: (2) HP MS 30s (loaded) dual bus (2) HP 6402, one connected to each MSA. The performance for the money is incredible. Sincerely, Joshua D. Drake - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] File Systems Compared
Luke Lonergan wrote: Brian, On 12/6/06 8:02 AM, Brian Hurt [EMAIL PROTECTED] wrote: These numbers are close enough to bus-saturation rates PCIX is 1GB/s + and the memory architecture is 20GB/s+, though each CPU is likely to obtain only 2-3GB/s. We routinely achieve 1GB/s I/O rate on two 3Ware adapters and 2GB/s on the Sun X4500 with ZFS. For some reason I'd got it stuck in my head that PCI-Express maxed out at a theoretical 533 MByte/sec- at which point, getting 480 MByte/sec across it is pretty dang good. But actually looking things up, I see that PCI-Express has a theoretical 8 Gbit/sec, or about 800Mbyte/sec. It's PCI-X that's 533 MByte/sec. So there's still some headroom available there. Brian
Re: [PERFORM] File Systems Compared
On Wed, Dec 06, 2006 at 05:31:01PM +0100, Markus Schiltknecht wrote: Care to post these numbers *without* word wrapping? Thanks. How is one supposed to do that? Care giving an example? This is a rather long sentence without any kind of word wrapping except what would be imposed on your own side -- how to set that up properly depends on the sending e-mail client, but in mine it's just a matter of turning off the word wrapping in your editor :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] File Systems Compared
* Brian Wipf: Anyone run their RAIDs with disk caches enabled, or is this akin to having fsync off? If your cache is backed by a battery, enabling write cache shouldn't be a problem. You can check if the whole thing is working well by running this test script: http://brad.livejournal.com/2116715.html Enabling write cache leads to various degrees of data corruption in case of a power outage (possibly including file system corruption requiring manual recover). -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(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] File Systems Compared
Anyone run their RAIDs with disk caches enabled, or is this akin to having fsync off? Disk write caches are basically always akin to having fsync off. The only time a write-cache is (more or less) safe to enable is when it is backed by a battery or in some other way made non-volatile. So a RAID controller with a battery-backed write cache can enable its own write cache, but can't safely enable the write-caches on the disk drives it manages. -- Mark Lewis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] VACUUM FULL does not works.......
Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. asif ali icrossing inc. - Have a burning question? Go to Yahoo! Answers and get answers from real people who know.
Re: [PERFORM] VACUUM FULL does not works.......
2006/12/6, asif ali [EMAIL PROTECTED]: Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. try VACUUM FULL VERBOSE and report the result. -- Jean-Max Reymond CKR Solutions Open Source Nice France http://www.ckr-solutions.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] VACUUM FULL does not works.......
On Wed, 2006-12-06 at 11:07, asif ali wrote: Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. More than likely you've got a long running transaction that the vacuum can't vacuum around. ---(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] File Systems Compared
Brian, On 12/6/06 8:40 AM, Brian Hurt [EMAIL PROTECTED] wrote: But actually looking things up, I see that PCI-Express has a theoretical 8 Gbit/sec, or about 800Mbyte/sec. It's PCI-X that's 533 MByte/sec. So there's still some headroom available there. See here for the official specifications of both: http://www.pcisig.com/specifications/pcix_20/ Note that PCI-X version 1.0 at 133MHz runs at 1GB/s. It's a parallel bus, 64 bits wide (8 bytes) and runs at 133MHz, so 8 x 133 ~= 1 gigabyte/second. PCI Express with 16 lanes (PCIe x16) can transfer data at 4GB/s. The Arecas use (PCIe x8, see here: http://www.areca.com.tw/products/html/pcie-sata.htm), so they can do 2GB/s. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] File Systems Compared
Hi, Steinar H. Gunderson wrote: This is a rather long sentence without any kind of word wrapping except what would be imposed on your own side -- how to set that up properly depends on the sending e-mail client, but in mine it's just a matter of turning off the word wrapping in your editor :-) Duh! Cool, thank you for the example :-) I thought the MTA or at least the the mailing list would wrap mails at some limit. I've now set word-wrap to characters (it seems not possible to turn it off completely in thunderbird). But when writing, I'm now getting one long line. What's common practice? What's it on the pgsql mailing lists? Regards Markus ---(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] VACUUM FULL does not works.......
On 12/6/06, asif ali [EMAIL PROTECTED] wrote: Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. It is desirable that PostgreSQL version be reported in problem descriptions. Older versions of pgsql had problem of index bloat. It is interesting to find out why VACUUM FULL does not work in your case(wait for the experts) , but most probably CLUSTERING the table on primary key is going to solve the query performance problem (temporarily) asif ali icrossing inc. Have a burning question? Go to Yahoo! Answers and get answers from real people who know. ---(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] Locking in PostgreSQL?
On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? Know that updating 1 column is actually updating the whole row. So if one transaction updates column A of a row, it will block another concurrent transaction that tries to update column B of the same row. As was mentioned however, neither of these transactions block others reading the row in question, though they see the row as it existed before the updates until those update transactions commit. If you know that your application will suffer excessive update contention trying to update different columns of the same row, you could consider splitting the columns into separate tables. This is an optimization to favor write contention over read performance (since you would likely need to join the tables when selecting) and I wouldn't do it speculatively. I'd only do it if profiling the application demonstrated significantly better performance with two tables. -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] File Systems Compared
Markus Schiltknecht a écrit : What's common practice? What's it on the pgsql mailing lists? The netiquette usually advise mailers to wrap after 72 characters on mailing lists. This does not apply for format=flowed I guess (that's the format used in Steinar's message). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] [offtopic] Word wrapping
On Wed, Dec 06, 2006 at 06:45:56PM +0100, Markus Schiltknecht wrote: Cool, thank you for the example :-) I thought the MTA or at least the the mailing list would wrap mails at some limit. I've now set word-wrap to characters (it seems not possible to turn it off completely in thunderbird). But when writing, I'm now getting one long line. Thunderbird uses format=flowed, so it's wrapped nevertheless. Google to find out how to turn it off if you really need to. What's common practice? Usually 72 or 76 characters, TTBOMK -- but when posting tables or big query plans, one should simply turn it off, as it kills readability. What's it on the pgsql mailing lists? No idea. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Locking in PostgreSQL?
Casey Duncan wrote: On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? Know that updating 1 column is actually updating the whole row. So if one transaction updates column A of a row, it will block another concurrent transaction that tries to update column B of the same row. As was mentioned however, neither of these transactions block others reading the row in question, though they see the row as it existed before the updates until those update transactions commit. If you know that your application will suffer excessive update contention trying to update different columns of the same row, you could consider splitting the columns into separate tables. This is an optimization to favor write contention over read performance (since you would likely need to join the tables when selecting) and I wouldn't do it speculatively. I'd only do it if profiling the application demonstrated significantly better performance with two tables. -Casey Or, come up with some kind of (pre)caching strategy for your updates wherein you could then combine multiple updates to the same row into one update. -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] File Systems Compared
On Wed, Dec 06, 2006 at 06:59:12PM +0100, Arnaud Lesauvage wrote: Markus Schiltknecht a écrit : What's common practice? What's it on the pgsql mailing lists? The netiquette usually advise mailers to wrap after 72 characters on mailing lists. This does not apply for format=flowed I guess (that's the format used in Steinar's message). It would apply to either; format=flowed can be wrapped at the receiver's end, but still be formatted to a particular column for readers that don't understand format=flowed. (Which is likely to be many, since that's a standard that never really took off.) No wrap netiquette applies to formatted text blocks which are unreadable if wrapped (such as bonnie or EXPLAIN output). Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] VACUUM FULL does not works.......
Thanks for the prompt reply... Here is the output of VACUUM FULL VERBOSE The postgres version is 8.0.3. Thanks asif ali icrossing inc INFO: vacuuming public.product_table INFO: product_table: found 0 removable, 139178 nonremovable row versions in 4305 pages DETAIL: 138859 dead row versions cannot be removed yet. Nonremovable row versions range from 152 to 273 bytes long. There were 26916 unused item pointers. Total free space (including removable row versions) is 4507788 bytes. 249 pages are or will become empty, including 0 at the end of the table. 746 pages containing 4286656 free bytes are potential move destinations. CPU 0.04s/0.06u sec elapsed 0.15 sec. INFO: index product_table_client_name_unique now contains 139178 row versions in 3916 pages DETAIL: 0 index row versions were removed. 2539 index pages have been deleted, 2055 are currently reusable. CPU 0.08s/0.02u sec elapsed 0.76 sec. INFO: index product_table_cpc_agent_id_unique now contains 139178 row versions in 1980 pages DETAIL: 0 index row versions were removed. 1162 index pages have been deleted, 950 are currently reusable. CPU 0.04s/0.02u sec elapsed 0.49 sec. INFO: index product_table_pk now contains 139178 row versions in 3472 pages DETAIL: 0 index row versions were removed. 2260 index pages have been deleted, 1870 are currently reusable. CPU 0.08s/0.02u sec elapsed 0.53 sec. INFO: product_table: moved 18631 row versions, truncated 4305 to 4299 pages DETAIL: CPU 0.18s/1.14u sec elapsed 2.38 sec. INFO: index product_table_client_name_unique now contains 157728 row versions in 3916 pages DETAIL: 81 index row versions were removed. 2407 index pages have been deleted, 1923 are currently reusable. CPU 0.04s/0.01u sec elapsed 0.17 sec. INFO: index product_table_cpc_agent_id_unique now contains 157728 row versions in 1980 pages DETAIL: 81 index row versions were removed. 1100 index pages have been deleted, 888 are currently reusable. CPU 0.03s/0.01u sec elapsed 0.16 sec. INFO: index product_table_pk now contains 157728 row versions in 3472 pages DETAIL: 81 index row versions were removed. 2150 index pages have been deleted, 1760 are currently reusable. CPU 0.05s/0.01u sec elapsed 0.30 sec. INFO: vacuuming pg_toast.pg_toast_11891545 INFO: pg_toast_11891545: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index pg_toast_11891545_index now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. Query returned successfully with no result in 5201 ms. Jean-Max Reymond [EMAIL PROTECTED] wrote: 2006/12/6, asif ali : Hi, I have a product table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a product_temp table and run the same query to select all records; and it took 10ms(much faster). I did VACUUM FULL on product table but It did not work. I checked the file size of these two tables. product table's file size is 32mb and product_temp table's file size is 72k. So, it seems that VACUUM FULL is not doing anything. Please suggest. try VACUUM FULL VERBOSE and report the result. -- Jean-Max Reymond CKR Solutions Open Source Nice France http://www.ckr-solutions.com ---(end of broadcast)--- TIP 6: explain analyze is your friend - Want to start your own business? Learn how on Yahoo! Small Business.
Re: [PERFORM] [offtopic] File Systems Compared
On 6-Dec-06, at 9:05 AM, Alexander Staubo wrote: All tests are with bonnie++ 1.03a [snip] Care to post these numbers *without* word wrapping? Thanks. That's what Bonnie++'s output looks like. If you have Bonnie++ installed, you can run the following: bon_csv2html EOF hulk4,64368M, 78625,91,279921,51,112346,13,89463,96,417695,22,545.7,0,16,5903,99,+++ ++,+++,+,+++,6112,99,+,+++,18620,100 EOF Which will prettify the CSV results using HTML. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Problems with an update-from statement and pg-8.1.4
Hello
We are having some problems with an UPDATE ... FROM sql-statement and
pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the
table 'mail', this table is over 6GB without indexes, and when we send
thousands of this type of statement, the server has a very high iowait
percent.
How can we get rid of this Seq Scan?
I send the output of an explain and table definitions:
-
mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m,
mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
'1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
QUERY PLAN
Nested Loop (cost=0.00..932360.78 rows=7184312 width=57)
- Nested Loop (cost=0.00..6.54 rows=1 width=0)
- Index Scan using received_queue_id_index on mail_received
mr (cost=0.00..3.20 rows=1 width=4)
Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
Filter: (mailhost = '129.240.10.47'::inet)
- Index Scan using mail_pkey on mail m (cost=0.00..3.32
rows=1 width=4)
Index Cond: (outer.mail_id = m.mail_id)
- Seq Scan on mail (cost=0.00..860511.12 rows=7184312 width=57)
(8 rows)
mailstats=# \d mail
Table public.mail
Column | Type | Modifiers
+--+
mail_id| integer | not null default
nextval('mail_mail_id_seq'::regclass)
size | integer |
message_id | text | not null
spamscore | numeric(6,3) |
Indexes:
mail_pkey PRIMARY KEY, btree (mail_id)
mail_message_id_key UNIQUE, btree (message_id)
mailstats=# \d mail_received
Table public.mail_received
Column |Type |
Modifiers
---+-+--
reception_id | integer | not null default
nextval('mail_received_reception_id_seq'::regclass)
mail_id | integer | not null
envelope_from | text|
helohost | text|
from_host | inet|
protocol | text|
mailhost | inet|
received | timestamp without time zone | not null
completed | timestamp without time zone |
queue_id | character varying(16) | not null
Indexes:
mail_received_pkey PRIMARY KEY, btree (reception_id)
mail_received_queue_id_key UNIQUE, btree (queue_id, mailhost)
mail_received_completed_idx btree (completed)
mail_received_mailhost_index btree (mailhost)
mail_received_received_index btree (received)
received_id_index btree (mail_id)
received_queue_id_index btree (queue_id)
Foreign-key constraints:
$1 FOREIGN KEY (mail_id) REFERENCES mail(mail_id)
-
Thanks in advance.
regards,
--
Rafael Martinez, [EMAIL PROTECTED]
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(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] Problems with an update-from statement and pg-8.1.4
On Wed, 6 Dec 2006, Rafael Martinez wrote: We are having some problems with an UPDATE ... FROM sql-statement and pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the table 'mail', this table is over 6GB without indexes, and when we send thousands of this type of statement, the server has a very high iowait percent. How can we get rid of this Seq Scan? I send the output of an explain and table definitions: - mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. ---(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] Problems with an update-from statement and pg-8.1.4
Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: We are having some problems with an UPDATE ... FROM sql-statement and pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the table 'mail', this table is over 6GB without indexes, and when we send thousands of this type of statement, the server has a very high iowait percent. How can we get rid of this Seq Scan? I send the output of an explain and table definitions: - mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. ---(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 Worse yet I think your setting spamcore for EVERY row in mail to '-5.026'. The above solution should fix it though. -- Ted * * http://www.blackducksoftware.com ---(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] VACUUM FULL does not works.......
asif ali [EMAIL PROTECTED] writes: INFO: vacuuming public.product_table INFO: product_table: found 0 removable, 139178 nonremovable row versions in 4305 pages DETAIL: 138859 dead row versions cannot be removed yet. So Scott's guess was correct: you've got a whole lot of dead rows in there that will eventually be removable, but not while there's still an open transaction that might be able to see them. Find your open transaction and get rid of it (pg_stat_activity might help). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. Worse yet I think your setting spamcore for EVERY row in mail to '-5.026'. The above solution should fix it though. -- Ted Thanks for the answers. I think the 'problem' is explain in the documentation: fromlist A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROMClause of a SELECT statement. Note that the target table must not appear in the fromlist, unless you intend a self-join (in which case it must appear with an alias in the fromlist). And as you said, we can not have 'mail m' in the FROM clause. I have contacted the developers and they will change the statement. I gave then these 2 examples: --- mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; QUERY PLAN -- Nested Loop (cost=0.00..6.54 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (outer.mail_id = mail.mail_id) (6 rows) mailstats=# explain update mail SET spamscore = '-5.026' where mail_id = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1' and mailhost = '129.240.10.47'); QUERY PLAN - Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) Index Cond: (mail_id = $0) InitPlan - Index Scan using received_queue_id_index on mail_received (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) (6 rows) --- regards, -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] File Systems Compared
On 12/6/06, Luke Lonergan [EMAIL PROTECTED] wrote: People buy SANs for interesting reasons, some of them having to do with the manageability features of high end SANs. I've heard it said in those cases that performance doesn't matter much. There is movement in the industry right now away form tape systems to managed disk storage for backups and data retention. In these cases performance requirements are not very high -- and a single server can manage a huge amount of storage. In theory, you can do the same thing attached via sas expanders but fc networking is imo more flexible and scalable. The manageability features of SANs are a mixed bag and decidedly overrated but they have a their place, imo. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. Worse yet I think your setting spamcore for EVERY row in mail to '-5.026'. The above solution should fix it though. -- Ted Thanks for the answers. I think the 'problem' is explain in the documentation: fromlist A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROMClause of a SELECT statement. Note that the target table must not appear in the fromlist, unless you intend a self-join (in which case it must appear with an alias in the fromlist). And as you said, we can not have 'mail m' in the FROM clause. I have contacted the developers and they will change the statement. I gave then these 2 examples: --- mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; QUERY PLAN -- Nested Loop (cost=0.00..6.54 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (outer.mail_id = mail.mail_id) (6 rows) mailstats=# explain update mail SET spamscore = '-5.026' where mail_id = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1' and mailhost = '129.240.10.47'); QUERY PLAN - Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) Index Cond: (mail_id = $0) InitPlan - Index Scan using received_queue_id_index on mail_received (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) (6 rows) --- Look again at the estimated costs of those two query plans. You haven't gained anything there. Try this out: EXPLAIN UPDATE mail SET spamscore = '-5.026' FROM mail_received mr WHERE mail.mail_id = mr.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' ; -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] File Systems Compared
Luke Lonergan wrote: Brian, On 12/6/06 8:40 AM, Brian Hurt [EMAIL PROTECTED] wrote: But actually looking things up, I see that PCI-Express has a theoretical 8 Gbit/sec, or about 800Mbyte/sec. It's PCI-X that's 533 MByte/sec. So there's still some headroom available there. See here for the official specifications of both: http://www.pcisig.com/specifications/pcix_20/ Note that PCI-X version 1.0 at 133MHz runs at 1GB/s. It's a parallel bus, 64 bits wide (8 bytes) and runs at 133MHz, so 8 x 133 ~= 1 gigabyte/second. PCI Express with 16 lanes (PCIe x16) can transfer data at 4GB/s. The Arecas use (PCIe x8, see here: http://www.areca.com.tw/products/html/pcie-sata.htm), so they can do 2GB/s. - Luke Thanks. I stand corrected (again). Brian
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. Worse yet I think your setting spamcore for EVERY row in mail to '-5.026'. The above solution should fix it though. -- Ted Thanks for the answers. I think the 'problem' is explain in the documentation: fromlist A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROMClause of a SELECT statement. Note that the target table must not appear in the fromlist, unless you intend a self-join (in which case it must appear with an alias in the fromlist). And as you said, we can not have 'mail m' in the FROM clause. I have contacted the developers and they will change the statement. I gave then these 2 examples: --- mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; QUERY PLAN -- Nested Loop (cost=0.00..6.54 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (outer.mail_id = mail.mail_id) (6 rows) mailstats=# explain update mail SET spamscore = '-5.026' where mail_id = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1' and mailhost = '129.240.10.47'); QUERY PLAN - Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) Index Cond: (mail_id = $0) InitPlan - Index Scan using received_queue_id_index on mail_received (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) (6 rows) --- Look again at the estimated costs of those two query plans. You haven't gained anything there. Try this out: EXPLAIN UPDATE mail SET spamscore = '-5.026' FROM mail_received mr WHERE mail.mail_id = mr.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' ; Haven't we? * In the statement with problems we got this: Nested Loop (cost=0.00..932360.78 rows=7184312 width=57) * In the ones I sent: Nested Loop (cost=0.00..6.54 rows=1 width=57) Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) * And in the last one you sent me: -- Nested Loop (cost=0.00..6.53 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (mail.mail_id = outer.mail_id) (5 rows) -- I can not see the different. regards, -- Rafael Martinez, [EMAIL PROTECTED] Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] File Systems Compared
On Wed, Dec 06, 2006 at 18:45:56 +0100, Markus Schiltknecht [EMAIL PROTECTED] wrote: Cool, thank you for the example :-) I thought the MTA or at least the the mailing list would wrap mails at some limit. I've now set word-wrap to characters (it seems not possible to turn it off completely in thunderbird). But when writing, I'm now getting one long line. What's common practice? What's it on the pgsql mailing lists? If you do this you should set format=flowed (see rfc 2646). If you do that, then clients can break the lines in an appropiate way. This is actually better than fixing the line width in the original message, since the recipient may not have the same number of characters (or pixels) of display as the sender. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
Rafael Martinez wrote: On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; I don't think this statement does what you expect. You're ending up with two copies of mail in the above one as mail and one as m. You probably want to remove the mail m in FROM and use mail rather than m in the where clause. Worse yet I think your setting spamcore for EVERY row in mail to '-5.026'. The above solution should fix it though. -- Ted Thanks for the answers. I think the 'problem' is explain in the documentation: fromlist A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROMClause of a SELECT statement. Note that the target table must not appear in the fromlist, unless you intend a self-join (in which case it must appear with an alias in the fromlist). And as you said, we can not have 'mail m' in the FROM clause. I have contacted the developers and they will change the statement. I gave then these 2 examples: --- mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47'; QUERY PLAN -- Nested Loop (cost=0.00..6.54 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (outer.mail_id = mail.mail_id) (6 rows) mailstats=# explain update mail SET spamscore = '-5.026' where mail_id = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1' and mailhost = '129.240.10.47'); QUERY PLAN - Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) Index Cond: (mail_id = $0) InitPlan - Index Scan using received_queue_id_index on mail_received (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) Filter: (mailhost = '129.240.10.47'::inet) (6 rows) --- Look again at the estimated costs of those two query plans. You haven't gained anything there. Try this out: EXPLAIN UPDATE mail SET spamscore = '-5.026' FROM mail_received mr WHERE mail.mail_id = mr.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' ; Haven't we? * In the statement with problems we got this: Nested Loop (cost=0.00..932360.78 rows=7184312 width=57) * In the ones I sent: Nested Loop (cost=0.00..6.54 rows=1 width=57) Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57) * And in the last one you sent me: -- Nested Loop (cost=0.00..6.53 rows=1 width=57) - Index Scan using received_queue_id_index on mail_received mr (cost=0.00..3.20 rows=1 width=4) Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text) - Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1 width=57) Index Cond: (mail.mail_id = outer.mail_id) (5 rows) -- I can not see the different. regards, Ah, sorry, I was just looking at the two that you sent in your last message thinking that they were 'old' and 'new', not both 'new'. My bad... -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Disk storage and san questions (was File Systems Compared)
Joshua D. Drake wrote: I agree. I have many people that want to purchase a SAN because someone told them that is what they need... Yet they can spend 20% of the cost on two external arrays and get incredible performance... We are seeing great numbers from the following config: (2) HP MS 30s (loaded) dual bus (2) HP 6402, one connected to each MSA. The performance for the money is incredible. This raises some questions for me. I just budgeted for a san because I need lots of storage for email/web systems and don't want to have a bunch of local disks in each server requiring each server to have it's own spares. The idea is that I can have a platform wide disk chassis which requires only one set of spares and run my linux hosts diskless. Since I am planing on buying the sanraid iscsi solution I would simply boot hosts with pxelinux and pass a kernel/initrd image that would mount the iscsi target as root. If a server fails, I simply change the mac address in the bootp server then bring up a spare in it's place. Now that I'm reading these messages about disk performance and sans, it's got me thinking that this solution is not ideal for a database server. Also, it appears that there are several people on the list that have experience with sans so perhaps some of you can fill in some blanks for me: 1. Is iscsi a decent way to do a san? How much performance do I loose vs connecting the hosts directly with a fiber channel controller? 2. Would it be better to omit my database server from the san (or at least the database storage) and stick with local disks? If so what disks/controller card do I want? I use dell servers for everything so it would be nice if the recommendation is a dell system, but doesn't need to be. Overall I'm not very impressed with the LSI cards, but I'm told the new ones are much better. 3. Anyone use the sanrad box? Is it any good? Seems like consolidating disk space and disk spares platform wide is good idea, but I've not used a san before so I'm nervous about it. 4. What would be the performance of SATA disks in a JBOD? If I got 8 200g disks and made 4 raid one mirrors in the jbod then striped them together in the sanraid would that perform decent? Is there an advantage splitting up raid 1+0 across the two boxes, or am I better doing raid 1+0 in the jbod and using the sanrad as an iscsi translator? Thats enough questions for now Thanks, schu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] File Systems Compared
At 10:40 AM 12/6/2006, Brian Wipf wrote: All tests are with bonnie++ 1.03a Main components of system: 16 WD Raptor 150GB 1 RPM drives all in a RAID 10 ARECA 1280 PCI-Express RAID adapter with 1GB BB Cache (Thanks for the recommendation, Ron!) 32 GB RAM Dual Intel 5160 Xeon Woodcrest 3.0 GHz processors OS: SUSE Linux 10.1 xfs (with write cache disabled on disks): /usr/local/sbin/bonnie++ -d bonnie/ -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 90621 99 283916 35 105871 11 88569 97 433890 23 644.5 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 28435 95 + +++ 28895 82 28523 91 + ++ + 24369 86 hulk4,64368M, 90621,99,283916,35,105871,11,88569,97,433890,23,644.5,0,16,28435,95,++ +++,+++,28895,82,28523,91,+,+++,24369,86 xfs (with write cache enabled on disks): /usr/local/sbin/bonnie++ -d bonnie -s 64368:8k Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- -- Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec % CP /sec %CP hulk464368M 90861 99 348401 43 131887 14 89412 97 432964 23 658.7 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- -- Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec % CP /sec %CP 16 28871 90 + +++ 28923 91 30879 93 + ++ + 28012 94 hulk4,64368M, 90861,99,348401,43,131887,14,89412,97,432964,23,658.7,0,16,28871,90,++ +++,+++,28923,91,30879,93,+,+++,28012,94 Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~1300MBps respectively along with RAID 0 Sustained Rates of ~900MBps read, and ~850MBps write. Luke, I know you've managed to get higher IO rates than this with this class of HW. Is there a OS or SW config issue Brian should closely investigate? Ron Peacetree ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] File Systems Compared
Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~1300MBps respectively along with RAID 0 Sustained Rates of ~900MBps read, and ~850MBps write. Luke, I know you've managed to get higher IO rates than this with this class of HW. Is there a OS or SW config issue Brian should closely investigate? I wrote 1280 by a mistake. It's actually a 1260. Sorry about that. The IOP341 class of cards weren't available when we ordered the parts for the box, so we had to go with the 1260. The box(es) we build next month will either have the 1261ML or 1280 depending on whether we go 16 or 24 disk. I noticed Bucky got almost 800 random seeks per second on her 6 disk 1 RPM SAS drive Dell PowerEdge 2950. The random seek performance of this box disappointed me the most. Even running 2 concurrent bonnies, the random seek performance only increased from 644 seeks/ sec to 813 seeks/sec. Maybe there is some setting I'm missing? This card looked pretty impressive on tweakers.net. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] VACUUM FULL does not works.......
Thanks Everybody for helping me out. I checked pg_stat_activity/pg_locks, but do not see any activity on the table. How to find a old running transaction... I saw this link, but it did not help.. http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php Thanks asif ali icrossing inc Tom Lane [EMAIL PROTECTED] wrote: asif ali writes: INFO: vacuuming public.product_table INFO: product_table: found 0 removable, 139178 nonremovable row versions in 4305 pages DETAIL: 138859 dead row versions cannot be removed yet. So Scott's guess was correct: you've got a whole lot of dead rows in there that will eventually be removable, but not while there's still an open transaction that might be able to see them. Find your open transaction and get rid of it (pg_stat_activity might help). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org - Check out the all-new Yahoo! Mail beta - Fire up a more powerful email and get things done faster.
Re: [PERFORM] VACUUM FULL does not works.......
On Wed, 2006-12-06 at 15:53, asif ali wrote: Thanks Everybody for helping me out. I checked pg_stat_activity/pg_locks, but do not see any activity on the table. How to find a old running transaction... I saw this link, but it did not help.. http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php Sometimes just using top or ps will show you. on linux you can run top and then hit c for show command line and look for ones that are IDLE Or, try ps: ps axw|grep postgres On my machine normally: 2408 ?S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data 2615 ?S 0:00 postgres: stats buffer process 2616 ?S 0:00 postgres: stats collector process 2857 ?S 0:00 postgres: writer process 2858 ?S 0:00 postgres: stats buffer process 2859 ?S 0:00 postgres: stats collector process But with an idle transaction: 2408 ?S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data 2615 ?S 0:00 postgres: stats buffer process 2616 ?S 0:00 postgres: stats collector process 2857 ?S 0:00 postgres: writer process 2858 ?S 0:00 postgres: stats buffer process 2859 ?S 0:00 postgres: stats collector process 8679 ?S 0:00 postgres: smarlowe test [local] idle in transaction Thar she blows! Also, you can restart the database and vacuum it then too. Of course, don't do that during regular business hours... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Areca 1260 Performance (was: File Systems
The 1100 series is PCI-X based. The 1200 series is PCI-E x8 based. Apples and oranges. I still think Luke Lonergan or Josh Berkus may have some interesting ideas regarding possible OS and SW optimizations. WD1500ADFDs are each good for ~90MBps read and ~60MBps write ASTR. That means your 16 HD RAID 10 should be sequentially transferring ~720MBps read and ~480MBps write. Clearly more HDs will be required to allow a ARC-12xx to attain its peak performance. One thing that occurs to me with your present HW is that your CPU utilization numbers are relatively high. Since 5160s are clocked about as high as is available, that leaves trying CPUs with more cores and trying more CPUs. You've got basically got 4 HW threads at the moment. If you can, evaluate CPUs and mainboards that allow for 8 or 16 HW threads. Intel-wise, that's the new Kentfields. AMD-wise, you have lot's of 4S mainboard options, but the AMD 4C CPUs won't be available until sometime late in 2007. I've got other ideas, but this list is not the appropriate venue for the level of detail required. Ron Peacetree At 05:30 PM 12/6/2006, Brian Wipf wrote: On 6-Dec-06, at 2:47 PM, Brian Wipf wrote: Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~1300MBps respectively along with RAID 0 Sustained Rates of ~900MBps read, and ~850MBps write. Luke, I know you've managed to get higher IO rates than this with this class of HW. Is there a OS or SW config issue Brian should closely investigate? I wrote 1280 by a mistake. It's actually a 1260. Sorry about that. The IOP341 class of cards weren't available when we ordered the parts for the box, so we had to go with the 1260. The box(es) we build next month will either have the 1261ML or 1280 depending on whether we go 16 or 24 disk. I noticed Bucky got almost 800 random seeks per second on her 6 disk 1 RPM SAS drive Dell PowerEdge 2950. The random seek performance of this box disappointed me the most. Even running 2 concurrent bonnies, the random seek performance only increased from 644 seeks/sec to 813 seeks/sec. Maybe there is some setting I'm missing? This card looked pretty impressive on tweakers.net. Areca has some performance numbers in a downloadable PDF for the Areca ARC-1120, which is in the same class as the ARC-1260, except with 8 ports. With all 8 drives in a RAID 0 the card gets the following performance numbers: Card single thread write20 thread write single thread read20 thread read ARC-1120 321.26 MB/s404.76 MB/s 412.55 MB/ s 672.45 MB/s My numbers for sequential i/o for the ARC-1260 in a 16 disk RAID 10 are slightly better than the ARC-1120 in an 8 disk RAID 0 for a single thread. I guess this means my numbers are reasonable. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Areca 1260 Performance
I appreciate your suggestions, Ron. And that helps answer my question on processor selection for our next box; I wasn't sure if the lower MHz speed of the Kentsfield compared to the Woodcrest but with double the cores would be better for us overall or not. On 6-Dec-06, at 4:25 PM, Ron wrote: The 1100 series is PCI-X based. The 1200 series is PCI-E x8 based. Apples and oranges. I still think Luke Lonergan or Josh Berkus may have some interesting ideas regarding possible OS and SW optimizations. WD1500ADFDs are each good for ~90MBps read and ~60MBps write ASTR. That means your 16 HD RAID 10 should be sequentially transferring ~720MBps read and ~480MBps write. Clearly more HDs will be required to allow a ARC-12xx to attain its peak performance. One thing that occurs to me with your present HW is that your CPU utilization numbers are relatively high. Since 5160s are clocked about as high as is available, that leaves trying CPUs with more cores and trying more CPUs. You've got basically got 4 HW threads at the moment. If you can, evaluate CPUs and mainboards that allow for 8 or 16 HW threads. Intel-wise, that's the new Kentfields. AMD-wise, you have lot's of 4S mainboard options, but the AMD 4C CPUs won't be available until sometime late in 2007. I've got other ideas, but this list is not the appropriate venue for the level of detail required. Ron Peacetree At 05:30 PM 12/6/2006, Brian Wipf wrote: On 6-Dec-06, at 2:47 PM, Brian Wipf wrote: Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~1300MBps respectively along with RAID 0 Sustained Rates of ~900MBps read, and ~850MBps write. Luke, I know you've managed to get higher IO rates than this with this class of HW. Is there a OS or SW config issue Brian should closely investigate? I wrote 1280 by a mistake. It's actually a 1260. Sorry about that. The IOP341 class of cards weren't available when we ordered the parts for the box, so we had to go with the 1260. The box(es) we build next month will either have the 1261ML or 1280 depending on whether we go 16 or 24 disk. I noticed Bucky got almost 800 random seeks per second on her 6 disk 1 RPM SAS drive Dell PowerEdge 2950. The random seek performance of this box disappointed me the most. Even running 2 concurrent bonnies, the random seek performance only increased from 644 seeks/sec to 813 seeks/sec. Maybe there is some setting I'm missing? This card looked pretty impressive on tweakers.net. Areca has some performance numbers in a downloadable PDF for the Areca ARC-1120, which is in the same class as the ARC-1260, except with 8 ports. With all 8 drives in a RAID 0 the card gets the following performance numbers: Card single thread write20 thread write single thread read20 thread read ARC-1120 321.26 MB/s404.76 MB/s 412.55 MB/ s 672.45 MB/s My numbers for sequential i/o for the ARC-1260 in a 16 disk RAID 10 are slightly better than the ARC-1120 in an 8 disk RAID 0 for a single thread. I guess this means my numbers are reasonable. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] VACUUM FULL does not works.......
Thanks Scott, It worked!!! We killed an old idle running transaction, now everything is fine.. Thanks Again asif ali icrossing inc Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, 2006-12-06 at 15:53, asif ali wrote: Thanks Everybody for helping me out. I checked pg_stat_activity/pg_locks, but do not see any activity on the table. How to find a old running transaction... I saw this link, but it did not help.. http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php Sometimes just using top or ps will show you. on linux you can run top and then hit c for show command line and look for ones that are IDLE Or, try ps: ps axw|grep postgres On my machine normally: 2408 ?S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data 2615 ?S 0:00 postgres: stats buffer process 2616 ?S 0:00 postgres: stats collector process 2857 ?S 0:00 postgres: writer process 2858 ?S 0:00 postgres: stats buffer process 2859 ?S 0:00 postgres: stats collector process But with an idle transaction: 2408 ?S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D /home/postgres/data 2615 ?S 0:00 postgres: stats buffer process 2616 ?S 0:00 postgres: stats collector process 2857 ?S 0:00 postgres: writer process 2858 ?S 0:00 postgres: stats buffer process 2859 ?S 0:00 postgres: stats collector process 8679 ?S 0:00 postgres: smarlowe test [local] idle in transaction Thar she blows! Also, you can restart the database and vacuum it then too. Of course, don't do that during regular business hours... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - Have a burning question? Go to Yahoo! Answers and get answers from real people who know.
Re: [PERFORM] Areca 1260 Performance
At 06:40 PM 12/6/2006, Brian Wipf wrote: I appreciate your suggestions, Ron. And that helps answer my question on processor selection for our next box; I wasn't sure if the lower MHz speed of the Kentsfield compared to the Woodcrest but with double the cores would be better for us overall or not. Please do not misunderstand me. I am not endorsing the use of Kentsfield. I am recommending =evaluating= Kentsfield. I am also recommending the evaluation of 2C 4S AMD solutions. All this stuff is so leading edge that it is far from clear what the RW performance of DBMS based on these components will be without extensive testing of =your= app under =your= workload. One thing that is clear from what you've posted thus far is that you are going to needmore HDs if you want to have any chance of fully utilizing your Areca HW. Out of curiosity, where are you geographically? Hoping I'm being helpful, Ron ---(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] 8.2rc1 (much) slower than 8.2dev?
On Mon, Dec 04, 2006 at 05:41:14PM +0100, Arjen van der Meijden wrote: Since I'd rather not send the entire list of queries to the entire world, is it OK to send both explain analyze-files to you off list? Can you post them on the web somewhere so everyone can look at them? Also, are you looking at EXPLAIN or EXPLAIN ANALYZE? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Configuration settings for 32GB RAM server
On Mon, Dec 04, 2006 at 09:42:57AM -0800, Jeff Davis wrote: fsm_pages = 200,000 ??? Based this on some statistics about the number of pages freed from a vacuum on older server. Not sure if its fair to calculate this based on vacuum stats of 7.3.4 server? Might as well make it a higher number because you have a lot of RAM anyway. It's better than running out of space in the FSM, because to increase that setting you need to restart the daemon. Increasing this by 1 only uses 6 bytes. That means you could set it to 10 times the number you currently have, and it would still be insignificant. You can also run vacuumdb -av and look at the last few lines to see what it says you need. Or you can get that info out of contrib/pg_freespacemap. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] File Systems Compared
On Wed, 6 Dec 2006, Alexander Staubo wrote: Care to post these numbers *without* word wrapping? Brian's message was sent with format=flowed and therefore it's easy to re-assemble into original form if your software understands that. I just checked with two e-mail clients (Thunderbird and Pine) and all his bonnie++ results were perfectly readable on both as soon as I made the display wide enough. If you had trouble reading it, you might consider upgrading your mail client to one that understands that standard. Statistically, though, if you have this problem you're probably using Outlook and there may not be a useful upgrade path for you. I know it's been added to the latest Express version (which even defaults to sending messages flowed, driving many people crazy), but am not sure if any of the Office Outlooks know what to do with flowed messages yet. And those of you pointing people at the RFC's, that's a bit hardcore--the RFC documents themselves could sure use some better formatting. https://bugzilla.mozilla.org/attachment.cgi?id=134270action=view has a readable introduction to the encoding of flowed messages, http://mailformat.dan.info/body/linelength.html gives some history to how we all got into this mess in the first place, and http://joeclark.org/ffaq.html also has some helpful (albeit out of date in spots) comments on this subject. Even if it is correct netiquette to disable word-wrapping for long lines like bonnie output (there are certainly two sides with valid points in that debate), to make them more compatible with flow-impaired clients, you can't expect that mail composition software is sophisticated enough to allow doing that for one section while still wrapping the rest of the text correctly. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
