Re: [PERFORM] Best OS for Postgres 8.2
On Tue, 8 May 2007, [EMAIL PROTECTED] wrote: one issue with journaling filesystems, if you journal the data as well as the metadata you end up with a very reliable setup, however it means that all your data needs to be written twice, oncce to the journal, and once to the final location. the write to the journal can be slightly faster then a normal write to the final location (the journal is a sequential write to an existing file), however the need to write twice can effectivly cut your disk I/O bandwidth in half when doing heavy writes. worse, when you end up writing mor ethen will fit in the journal (128M is the max for ext3) the entire system then needs to stall while the journal gets cleared to make space for the additional writes. if you don't journal your data then you avoid the problems above, but in a crash you may find that you lost data, even though the filesystem is 'intact' according to fsck. That sounds like an ad for FreeBSD and UFS2+Softupdates. :) Metadata is as safe as it is in a journaling filesystem, but none of the overhead of journaling. Charles David Lang Steve Atkins wrote: On May 7, 2007, at 2:55 PM, David Levy wrote: Hi, I am about to order a new server for my Postgres cluster. I will probably get a Dual Xeon Quad Core instead of my current Dual Xeon. Which OS would you recommend to optimize Postgres behaviour (i/o access, multithreading, etc) ? I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone help with this ? Well, all three you mention are much the same, just with a different badge on the box, as far as performance is concerned. They're all going to be a moderately recent Linux kernel, with your choice of filesystems, so any choice between them is going to be driven more by available staff and support or personal preference. I'd probably go CentOS 5 over Fedora just because Fedora doesn't get supported for very long - more of an issue with a dedicated database box with a long lifespan than your typical desktop or interchangeable webserver. I might also look at Solaris 10, though. I've yet to play with it much, but it seems nice, and I suspect it might manage 8 cores better than current Linux setups. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards Ian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SCSI vs SATA
On Fri, 6 Apr 2007, [EMAIL PROTECTED] wrote: On Fri, 6 Apr 2007, Scott Marlowe wrote: Based on experience I think that on average server drives are more reliable than consumer grade drives, and can take more punishment. this I am not sure about I think they should survey Tivo owners next time. Perfect stress-testing environment. Mine runs at over 50C most of the time, and it's writing 2 video streams 24/7. What more could you do to punish a drive? :) Charles David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Writting a search engine for a pgsql DB
On Tue, 27 Feb 2007, Dave Page wrote: Magnus Hagander wrote: Just as a datapoint, we did try to use mnogosearch for the postgresql.org website+archives search, and it fell over completely. Indexing took way too long, and we had search times several thousand times longer than with tsearch2. That said, I'm sure there are cases when it works fine :-) There are - in fact before your time the site did use Mnogosearch. We moved to our own port of ASPSeek when we outgrew Mnogo's capabilities, and then to your TSearch code when we outgrew ASPSeek. At risk of pulling this way too far off topic, may I ask how many documents (mail messages) you were dealing with when things started to fall apart with mnogo? We're looking at it for a new project that will hopefully get bigger and bigger. We will be throwing groups of mailing lists into their own mnogo config/tables... If we should save ourselves the pain and look at something more homebrew, then we'll start investigating Tsearch. Thanks, Charles When we outgrow PostgreSQL Tsearch2, then, well, we'll need to stop pretending to be Google... /D ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Writting a search engine for a pgsql DB
On Mon, 26 Feb 2007, Madison Kelly wrote: Hi all, I'd really like to come up with a more intelligent search engine that doesn't take two minutes to return results. :) I know, in the end good indexes and underlying hardware will be important, but a sane as possible query structure helps to start with. I'm not a programmer, so I can't comment on how good of an example this is, but I've been pretty happy with mnogosearch: http://www.mnogosearch.com/ The *nix versions are free. Looking at the db structure gave me a bit of an idea of what I'm guessing is the right way to search a huge amount of documents. Charles Thanks all!! Madison ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] recommended benchmarks
Hi all, I still have an dual dual-core opteron box with a 3Ware 9550SX-12 sitting here and I need to start getting it ready for production. I also have to send back one processor since we were mistakenly sent two. Before I do that, I would like to record some stats for posterity and post to the list so that others can see how this particular hardware performs. It looks to be more than adequate for our needs... What are the standard benchmarks that people here use for comparison purposes? I know all benchmarks are flawed in some way, but I'd at least like to measure with the same tools that folks here generally use to get a ballpark figure. Thanks, Charles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Benchmarks
Hi all, I'm really glad to see all the test results people are posting here. In fact, I used info from the archives to put together our first big database host: -Tyan dual-core/dual-cpu mainboard ( -One Opteron 270 2.0GHz (although our vendor gave us two for some reason) -Chenbro 3U case (RM31212B) - OK, but not very well thought-out -8 Seagate SATA drives (yes, we stuck with our vendor of choice, WD Raptors may have been a better choice) -3Ware 9550SX-12MI -2GB RAM (we'll get more when we need it) So this thing is sitting next to my desk and I'd like to see just how this compares to other hardware. We already know that it will blow away our normal dual-xeon 1Us with just two U320 drives on Adaptec 2120s ZCR cards. We also know that for what this box will be doing (mailing list archives with msgs stored in Postgres) it's going to be more than enough for the next few years... So what are people using to get a general feel for the bang/buck ratio? I've toyed with Bonnie, IOZone and simple dd writes. I'd like to go a little further and actually hit Postgres to see how the entire system performs. My reasons are, in no particular order: -to learn something (general and pgsql tuning) -to help guide future database server builds -to take the benchmark data and share it somewhere The first one is obvious. Matching software to hardware is really hard and there aren't too many people that can do it well. The second is a pretty big deal - we've been doing all 1U builds and currently spread our load amongst individual db servers that also do the web front end for mailing list management. This has worked OK, but we may want to peel off the db section and start moving towards two large boxes like this with one replicating the other as a backup. That last one is a stickler. I've seen so much data posted on this list, is there any project in the works to collect this? It seems like some RAID hardware just totally sucks (cough *Adaptec* cough). Having a site that listed results for the more common benchmarks and sorting it out by hardware would help reduce the number of people that get burned by buying overpriced/underperforming RAID controllers/SANs. Any thoughts on all this? I'll be throwing in some quick stats on the box described above later today... At first glance, the 3Ware controller is really looking like an excellent value. Thanks, Charles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig
On Fri, 28 Jul 2006, Mikael Carneholm wrote: Luke, Yeah, I read those results, and I'm very disappointed with my results from the MSA1500. I would however be interested in other people's bonnie++ and benchmarksql results using a similar machine (2 cpu dual core opteron) with other off the shelf storage systems (EMC/Netapp/Xyratex/../). Could you run benchmarksql against that machine with the 16 SATA disk and 3Ware 9550SX SATA RAID adapters? It would be *very* interesting to see how the I/O performance correlates to benchmarksql (postgres) transaction throughout. FWIW, once our vendor gets all the pieces (having some issues with figuring out which multilane sata cables to get), I'll have a dual-core opteron box with a 3Ware 9500SX-12MI and 8 drives. I need to benchmark to compare this to our xeon/adaptec/scsi build we've been using. I've also got a 1U with a 9500SX-4 and 4 drives. I like how the 3Ware card scales there - started with 2 drives and got drive speed mirroring. Added two more and most of the bonnie numbers doubled. This is not what I'm used to with the Adaptec SCSI junk. These SATA RAID controllers 3Ware is making seem to be leaps and bounds beyond what the old guard is churning out (at much higher prices). Charles /Mikael -Original Message- From: Luke Lonergan [mailto:[EMAIL PROTECTED] Sent: den 28 juli 2006 11:17 To: Mikael Carneholm; Kjell Tore Fossbakk; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig Mikael, -Original Message- From: Mikael Carneholm [mailto:[EMAIL PROTECTED] Sent: Friday, July 28, 2006 2:05 AM My bonnie++ results are found in this message: http://archives.postgresql.org/pgsql-performance/2006-07/msg00164.php Apologies if I've already said this, but those bonnie++ results are very disappointing. The sequential transfer rates between 20MB/s and 57MB/s are slower than a single SATA disk, and your SCSI disks might even do 80MB/s sequential transfer rate each. Random access is also very poor, though perhaps equal to 5 disk drives at 500/second. By comparison, we routinely get 950MB/s sequential transfer rate using 16 SATA disks and 3Ware 9550SX SATA RAID adapters on Linux. On Solaris ZFS on an X4500, we recently got this bonnie++ result on 36 SATA disk drives in RAID10 (single thread first): 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 thumperdw-i-1 32G 120453 99 467814 98 290391 58 109371 99 993344 94 1801 4 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 + +++ + +++ + +++ 30850 99 + +++ + +++ Bumping up the number of concurrent processes to 2, we get about 1.5x speed reads of RAID10 with a concurrent workload (you have to add the rates together): 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 thumperdw-i-1 32G 111441 95 212536 54 171798 51 106184 98 719472 88 1233 2 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 26085 90 + +++ 5700 98 21448 97 + +++ 4381 97 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 thumperdw-i-1 32G 116355 99 212509 54 171647 50 106112 98 715030 87 1274 3 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 26082 99 + +++ 5588 98 21399 88 + +++ 4272 97 So that's 2500 seeks per second, 1440MB/s sequential block read, 212MB/s per character sequential read. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Huge Data sets, simple queries
On Sun, 29 Jan 2006, Luke Lonergan wrote: In fact, in our testing of various host-based SCSI RAID adapters (LSI, Dell PERC, Adaptec, HP SmartArray), we find that *all* of them underperform, most of them severely. [snip] The important lesson we've learned is to always test the I/O subsystem performance - you can do so with a simple test like: time bash -c dd if=/dev/zero of=bigfile bs=8k count=400 sync time dd if=bigfile of=/dev/null bs=8k I'm curious about this since we're shopping around for something new... I do want to get some kind of baseline to compare new products to. Areca sent me stats on their SCSI-SATA controller and it looks like it maxes out around 10,000 IOPS. I'd like to see how our existing stuff compares to this. I'd especially like to see it in graph form such as the docs Areca sent (IOPS on one axis, block size on the other, etc.). Looking at the venerable Bonnie, it doesn't really seem to focus so much on the number of read/write operations per second, but on big bulky transfers. What are you folks using to measure your arrays? I've been considering using some of our data and just basically benchmarking postgres on various hardware with that, but I cannot compare that to any manufacturer tests. Sorry to meander a bit off topic, but I've been getting frustrated with this little endeavour... Thanks, Charles - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN/NAS options
Following up to myself again... On Wed, 14 Dec 2005, Charles Sprickman wrote: Hello all, Supermicro 1U w/SCA backplane and 4 bays 2x2.8 GHz Xeons Adaptec 2015S zero channel RAID card I don't want to throw away the four machines like that that we have. I do want to throw away the ZCR cards... :) If I ditch those I still have a 1U box with a U320 scsi plug on the back. I'm vaguely considering pairing these two devices: http://www.areca.us/products/html/products.htm That's an Areca 16 channel SATA II (I haven't even read up on what's new in SATA II) RAID controller with an optional U320 SCSI daughter card to connect to the host(s). http://www.chenbro.com.tw/Chenbro_Special/RM321.php How can I turn that box down? Those people in the picture look very excited about it? Seriously though, it looks like an interesting and economical pairing that gives me most of what I'm looking for: -a modern RAID engine -small form factor -remote management of the array -ability to reuse my current db hosts that are disk-bound Disadvantages: -only 1 or 2 hosts per box -more difficult to move storage from host to host (compared to a SAN or NAS system) -no fancy NetApp features like snapshots -I have no experience with Areca SATA-SCSI RAID controllers Any thoughts on this? The controller looks to be about $1500, the enclosure about $400, and the drives are no great mystery, cost would depend on what total capacity I'm looking for. Our initial plan is to set one up for storage for a mail archive project, and to also have a host use this storage to host replicated copies of all Postgres databases. If things look good, we'd start moving our main PG hosts to use a similar RAID box. Thanks, Charles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN/NAS options
On Sat, 14 Jan 2006, Luke Lonergan wrote: Charles, On 1/14/06 6:37 PM, Charles Sprickman [EMAIL PROTECTED] wrote: I'm vaguely considering pairing these two devices: http://www.areca.us/products/html/products.htm That's an Areca 16 channel SATA II (I haven't even read up on what's new in SATA II) RAID controller with an optional U320 SCSI daughter card to connect to the host(s). I'm confused - SATA with a SCSI daughter card? Where does the SCSI go? Bad ASCII diagram follows (D=disk, C=controller H=host): SATA D ---|| SCSI D ---| C ||H | D ---|||| D ---|| (etc. up to 16 drives) The drives and the controller go in the Chenbro case. U320 SCSI from the RAID controller in the Chenbro case to the 1U server. C ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] SAN/NAS options
Hello all, It seems that I'm starting to outgrow our current Postgres setup. We've been running a handful of machines as standalone db servers. This is all in a colocation environment, so everything is stuffed into 1U Supermicro boxes. Our standard build looks like this: Supermicro 1U w/SCA backplane and 4 bays 2x2.8 GHz Xeons Adaptec 2015S zero channel RAID card 2 or 4 x 73GB Seagate 10K Ultra 320 drives (mirrored+striped) 2GB RAM FreeBSD 4.11 PGSQL data from 5-10GB per box Recently I started studying what we were running up against in our nightly runs that do a ton of updates/inserts to prep things for the tasks the db does during the business day (light mix of selects/inserts/updates). While we have plenty of disk bandwidth (according to bonnie), we are really dying on IOPS. I'm guessing this is a mix of a rather anemic RAID controller (ever notice how adaptec doesn't publish any real performance specs on raid cards?) and having only two or four spindles (effectively 1 or 2 on writes). So that's where we are... I'm new to the whole SAN thing, but did recently pick up a few used NetApp shelves and a Fibre Channel RAID HBA (Mylex ExtremeRAID 3000, also used) to toy with. I started wondering if I could put something together to both get our storage on one set of boxes and allow me to get data striped across more drives. Our budget is not huge and we are not adverse to getting used gear where appropriate. What do you folks recommend? I'm just starting to look at what's out there for SANs and NAS, and from what I've seen, our options are: NetApp Filers - the pluses with these are that if we use NFS, we don't have to worry about either large filesystem support in FreeBSD (2TB practical limit), or limitation on growing partitions as the NetApp just deals with that. I also understand these make backups a bit simpler. I have a great, trusted, spare-stocking source for these. Apple X-Serve RAID - well, it's pretty cheap. Honestly, that's all I know about it - they don't talk about IOPS numbers, and I have no idea what lurks in that box as a RAID controller. SAN box w/integrated RAID - it seems like this might not be a good choice since the RAID hardware in the box may be where I hit any limits. I also imagine I'm probably overpaying for some OEM RAID controller integrated into the box. No idea where to look for used gear. SAN box, JBOD - this seems like it might be affordable as well. A few big shelves full of drives a SAN switch to plug all the shelves and hosts into and a FC RAID card in each host. No idea where to look for used gear here either. You'll note that I'm being somewhat driven by my OS of choice, FreeBSD. Unlike Solaris or other commercial offerings, there is no nice volume management available. While I'd love to keep managing a dozen or so FreeBSD boxes, I could be persuaded to go to Solaris x86 if the volume management really shines and Postgres performs well on it. Lastly, one thing that I'm not yet finding in trying to educate myself on SANs is a good overview of what's come out in the past few years that's more affordable than the old big-iron stuff. For example I saw some brief info on this list's archives about the Dell/EMC offerings. Anything else in that vein to look at? I hope this isn't too far off topic for this list. Postgres is the main application that I'm looking to accomodate. Anything else I can do with whatever solution we find is just gravy... Thanks! Charles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN/NAS options
On Wed, 14 Dec 2005, Charles Sprickman wrote: [big snip] The list server seems to be regurgitating old stuff, and in doing so it reminded me to thank everyone for their input. I was kind of waiting to see if anyone who was very pro-NAS/SAN was going to pipe up, but it looks like most people are content with per-host storage. You've given me a lot to go on... Now I'm going to have to do some research as to real-world RAID controller performance. It's vexing (to say the least) that most vendors don't supply any raw throughput or TPS stats on this stuff... Anyhow, thanks again. You'll probably see me back here in the coming months as I try to shake some mysql info out of my brain as our pgsql DBA gets me up to speed on pgsql and what specifically he's doing to stress things. Charles I hope this isn't too far off topic for this list. Postgres is the main application that I'm looking to accomodate. Anything else I can do with whatever solution we find is just gravy... Thanks! Charles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] SAN/NAS options
Hello all, It seems that I'm starting to outgrow our current Postgres setup. We've been running a handful of machines as standalone db servers. This is all in a colocation environment, so everything is stuffed into 1U Supermicro boxes. Our standard build looks like this: Supermicro 1U w/SCA backplane and 4 bays 2x2.8 GHz Xeons Adaptec 2015S zero channel RAID card 2 or 4 x 73GB Seagate 10K Ultra 320 drives (mirrored+striped) 2GB RAM FreeBSD 4.11 PGSQL data from 5-10GB per box Recently I started studying what we were running up against in our nightly runs that do a ton of updates/inserts to prep things for the tasks the db does during the business day (light mix of selects/inserts/updates). While we have plenty of disk bandwidth (according to bonnie), we are really dying on IOPS. I'm guessing this is a mix of a rather anemic RAID controller (ever notice how adaptec doesn't publish any real performance specs on raid cards?) and having only two or four spindles (effectively 1 or 2 on writes). So that's where we are... I'm new to the whole SAN thing, but did recently pick up a few used NetApp shelves and a Fibre Channel RAID HBA (Mylex ExtremeRAID 3000, also used) to toy with. I started wondering if I could put something together to both get our storage on one set of boxes and allow me to get data striped across more drives. Our budget is not huge and we are not adverse to getting used gear where appropriate. What do you folks recommend? I'm just starting to look at what's out there for SANs and NAS, and from what I've seen, our options are: NetApp Filers - the pluses with these are that if we use NFS, we don't have to worry about either large filesystem support in FreeBSD (2TB practical limit), or limitation on growing partitions as the NetApp just deals with that. I also understand these make backups a bit simpler. I have a great, trusted, spare-stocking source for these. Apple X-Serve RAID - well, it's pretty cheap. Honestly, that's all I know about it - they don't talk about IOPS numbers, and I have no idea what lurks in that box as a RAID controller. SAN box w/integrated RAID - it seems like this might not be a good choice since the RAID hardware in the box may be where I hit any limits. I also imagine I'm probably overpaying for some OEM RAID controller integrated into the box. No idea where to look for used gear. SAN box, JBOD - this seems like it might be affordable as well. A few big shelves full of drives a SAN switch to plug all the shelves and hosts into and a FC RAID card in each host. No idea where to look for used gear here either. You'll note that I'm being somewhat driven by my OS of choice, FreeBSD. Unlike Solaris or other commercial offerings, there is no nice volume management available. While I'd love to keep managing a dozen or so FreeBSD boxes, I could be persuaded to go to Solaris x86 if the volume management really shines and Postgres performs well on it. Lastly, one thing that I'm not yet finding in trying to educate myself on SANs is a good overview of what's come out in the past few years that's more affordable than the old big-iron stuff. For example I saw some brief info on this list's archives about the Dell/EMC offerings. Anything else in that vein to look at? I hope this isn't too far off topic for this list. Postgres is the main application that I'm looking to accomodate. Anything else I can do with whatever solution we find is just gravy... Thanks! Charles ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
