Re: [PERFORM] How to improve db performance with $7K?
Have you already considered application/database tuning? Adding indexes? shared_buffers large enough? etc. Your database doesn't seem that large for the hardware you've already got. I'd hate to spend $7k and end up back in the same boat. :) On Sat, 2005-03-26 at 13:04 +, Steve Poe wrote: > >Steve, can we clarify that you are not currently having any performance > >issues, you're just worried about failure? Recommendations should be based > >on whether improving applicaiton speed is a requirement ... > > Josh, > > The priorities are: 1)improve safety/failure-prevention, 2) improve > performance. > > The owner of the company wants greater performance (and, I concure to > certain degree), but the owner's vote is only 1/7 of the managment team. > And, the rest of the management team is not as focused on performance. > They all agree in safety/failure-prevention. > > Steve > > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to improve db performance with $7K?
On Mon, 2005-03-28 at 17:36 +, Steve Poe wrote: > I agree with you. Unfortunately, I am not the developer of the > application. The vendor uses ProIV which connects via ODBC. The vendor > could certain do some tuning and create more indexes where applicable. I > am encouraging the vendor to take a more active role and we work > together on this. I've done a lot browsing through pg_stat_activity, looking for queries that either hang around for a while or show up very often, and using explain to find out if they can use some assistance. You may also find that a dump and restore with a reconfiguration to mirrored drives speeds you up a lot - just from the dump and restore. > With hardware tuning, I am sure we can do better than 35Mb per sec. Also > moving the top 3 or 5 tables and indexes to their own slice of a RAID10 > and moving pg_xlog to its own drive will help too. If your database activity involves a lot of random i/o, 35Mb per second wouldn't be too bad. While conventional wisdom is that pg_xlog on its own drives (I know you meant plural :) ) is a big boost, in my particular case I could never get a a measurable boost that way. Obviously, YMMV. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] best use of an EMC SAN
In my sporadic benchmark testing, the only consistent 'trick' I found was that the best thing I could do for performance sequential performance was allocating a bunch of mirrored pair LUNs and stripe them with software raid. This made a huge difference (~2X) in sequential performance, and a little boost in random i/o - at least in FLARE 19. On our CX-500s, FLARE failed to fully utilize the secondary drives in RAID 1+0 configurations. FWIW, after several months of inquiries, EMC eventually explained that this is due to their desire to ease the usage and thus wear on the secondaries in order to reduce the likelihood of a mirrored pair both failing. We've never observed a difference using separate WAL LUNs - presumably due to the write cache. That said, we continue to use them figuring it's "cheap" insurance against running out of space as well as performance under conditions we didn't see while testing. We also ended up using single large LUNs for data, but I must admit I wanted more time to benchmark splitting off heavily hit tables. My advice would be to read the EMC performance white papers, remain skeptical, and then test everything yourself. :D On Wed, 2007-07-11 at 09:03 -0400, Dave Cramer wrote: > Assuming we have 24 73G drives is it better to make one big metalun > and carve it up and let the SAN manage the where everything is, or is > it better to specify which spindles are where. > > Currently we would require 3 separate disk arrays. > > one for the main database, second one for WAL logs, third one we use > for the most active table. > > Problem with dedicating the spindles to each array is that we end up > wasting space. Are the SAN's smart enough to do a better job if I > create one large metalun and cut it up ? > > Dave > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org ---(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] PostgreSQL and Linux 2.6 kernel.
On Sat, 2004-04-03 at 03:50, Gary Doades wrote: > On 2 Apr 2004 at 22:36, [EMAIL PROTECTED] wrote: > > OK, some more detail: > > Before wiping 2.4 off my test box for the second time: Perhaps I missed it, but which io scheduler are you using under 2.6? ---(end of broadcast)--- TIP 3: 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] PostgreSQL and Linux 2.6 kernel.
On Sun, 2004-04-04 at 01:56, Gary Doades wrote: > Unfortunately I don't understand the question! > > My background is the primarily Win32. The last time I used a *nix OS > was about 20 years ago apart from occasional dips into the linux OS > over the past few years. If you can tell be how to find out what you want > I will gladly give you the information. There are two available io schedulers in 2.6 (new feature), deadline and anticipatory. It should show be listed in the boot messages: dmesg | grep scheduler I've seen people arguing for each of the two schedulers, saying one is better than the other for databases. I'm curious which one you're using. :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Moving postgres to FC disks
On Tue, 2004-04-20 at 17:27, Joshua D. Drake wrote: > > -Currently, the internal RAID volume is ext3 filesystem. Any > > recommendations for the filesystem on the new FC volume? Rieserfs? > > > > > XFS What Linux distributions are popular in here for PG+XFS? I'm very disappointed that Redhat Enterprise 3 doesn't appear to support XFS/JFS, or anything else. Suse Server 8 seems very dated, at least from the eval I downloaded. I'm curious as to where other people have gone with the death of RH9. I'd have gone on to Redhat 3 if I wasn't interested in getting some of the benefits of XFS at the same time ... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] fsync vs open_sync
Another possibly useless datapoint on this thread for anyone who's curious ... open_sync absolutely stinks over NFS at least on Linux. :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Some quick Opteron 32-bit/64-bit results
I ran quite a few file system benchmarks in RHAS x86-64 and FC2 x86-64 on a Sun V40z - I did see very consistent 50% improvements in bonnie++ moving from RHAS to FC2 with ext2/ext3 on SAN. On Sun, 2004-11-14 at 23:51 -0800, William Yu wrote: > Greg Stark wrote: > > William Yu <[EMAIL PROTECTED]> writes: > > > > > >>Biggest speedup I've found yet is the backup process (PG_DUMP --> GZIP). > >>100% > >>faster in 64-bit mode. This drastic speed might be more the result of 64-bit > >>GZIP though as I've seen benchmarks in the past showing > >>encryption/compression > >>running 2 or 3 times faster in 64-bit mode versus 32-bit. > > > > > > Isn't this a major kernel bump too? So a different scheduler, different IO > > scheduler, etc? > > > > I'm sure there's some speedup due to the kernel bump. I really didn't > have the patience to even burn the FC2 32-bit CDs much less install both > 32-bit & 64-bit FC2 in order to have a more accurate baseline comparison. > > However, that being said -- when you see huge speed increases like 50% > 100% for dump+gzip, it's doubtful the kernel/process scheduler/IO > scheduler could have made that drastic of a difference. Maybe somebody > else who has done a 2.4 -> 2.6 upgrade can give us a baseline to > subtract from my numbers. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Alternatives to Dell?
Consider Sun's new line of Opterons. They've been around for a couple of years under the Newisys name. I'm using dozens of them for web servers and PG servers and so far both the v20z and v40z have been excellent performers with solid reliability. The pricing was also competitive since Sun is looking to break into the market. On Wed, 2004-12-01 at 14:24 -0800, Josh Berkus wrote: > Folks, > > A lot of people have been having a devilish time with Dell hardware lately. > It seems like the quality control just isn't there on the Dell servers. > > Thing is, some companies are required to use 1st-tier or at least 2nd-tier > vendors for hardware; they won't home-build. For those people, what vendors > do others on this list recommend? What have been your good/bad experiences? > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Alternatives to Dell?
Most of mine I got through a Sun reseller. Some of mine I got off of Ebay. You should be able to get them a lot cheaper than than retail web pricing. :) However, even full retail seems like it was a hell of a lot cheaper for a v40z than a DL585. :) On Fri, 2004-12-03 at 06:30 -0800, Joshua D. Drake wrote: > Cott Lang wrote: > > >Consider Sun's new line of Opterons. They've been around for a couple of > >years under the Newisys name. I'm using dozens of them for web servers > >and PG servers and so far both the v20z and v40z have been excellent > >performers with solid reliability. The pricing was also competitive > >since Sun is looking to break into the market. > > > > > Really? I am not being sarcastic, but I found their prices pretty sad. > Did you go direct or web purchase? I have thought about using them > several times but > > Sincerely, > > Joshua D. Drake > > > > > > > > >On Wed, 2004-12-01 at 14:24 -0800, Josh Berkus wrote: > > > > > >>Folks, > >> > >>A lot of people have been having a devilish time with Dell hardware lately. > >> > >>It seems like the quality control just isn't there on the Dell servers. > >> > >>Thing is, some companies are required to use 1st-tier or at least 2nd-tier > >>vendors for hardware; they won't home-build. For those people, what > >>vendors > >>do others on this list recommend? What have been your good/bad > >>experiences? > >> > >> > >> > > > > > >---(end of broadcast)--- > >TIP 4: Don't 'kill -9' the postmaster > > > > > > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Alternatives to Dell?
We were originally heading towards an IBM deployment, but the 325 was all that was available at the time, and it only supported 12GB. Then when I heard they canceled their rumored quad processor 350, I feared Intel/AMD politics and IBM dropped from the running. :) (IBM now has the 326 that supports 16GB of RAM) On Fri, 2004-12-03 at 06:38 -0800, Joshua D. Drake wrote: > Cott Lang wrote: > > >Most of mine I got through a Sun reseller. Some of mine I got off of > >Ebay. You should be able to get them a lot cheaper than than retail web > >pricing. :) > > > >However, even full retail seems like it was a hell of a lot cheaper for > >a v40z than a DL585. :) > > > > > That's true :) One of the reasons the compaq's are expensive > is they supposedly use a quad board, even for the dual machine. > Which means a different opteron chip as well. > > I don't know this for a fact, it is just what one of their > "ahem" sales guys told me. > > The IBM machines are seem reasonable though. > > Sincerely, > > Joshua D. Drake > > > > > > >On Fri, 2004-12-03 at 06:30 -0800, Joshua D. Drake wrote: > > > > > >>Cott Lang wrote: > >> > >> > >> > >>>Consider Sun's new line of Opterons. They've been around for a couple of > >>>years under the Newisys name. I'm using dozens of them for web servers > >>>and PG servers and so far both the v20z and v40z have been excellent > >>>performers with solid reliability. The pricing was also competitive > >>>since Sun is looking to break into the market. > >>> > >>> > >>> > >>> > >>Really? I am not being sarcastic, but I found their prices pretty sad. > >>Did you go direct or web purchase? I have thought about using them > >>several times but > >> > >>Sincerely, > >> > >>Joshua D. Drake > >> > >> > >> > >> > >> > >>>On Wed, 2004-12-01 at 14:24 -0800, Josh Berkus wrote: > >>> > >>> > >>> > >>> > >>>>Folks, > >>>> > >>>>A lot of people have been having a devilish time with Dell hardware > >>>>lately. > >>>>It seems like the quality control just isn't there on the Dell servers. > >>>> > >>>>Thing is, some companies are required to use 1st-tier or at least > >>>>2nd-tier > >>>>vendors for hardware; they won't home-build. For those people, what > >>>>vendors > >>>>do others on this list recommend? What have been your good/bad > >>>>experiences? > >>>> > >>>> > >>>> > >>>> > >>>> > >>>---(end of broadcast)--- > >>>TIP 4: Don't 'kill -9' the postmaster > >>> > >>> > >>> > >>> > >> > >> > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Alternatives to Dell?
On Fri, 2004-12-03 at 20:53 -0500, Mike Rylander wrote: > On Fri, 03 Dec 2004 06:38:50 -0800, Joshua D. Drake > <[EMAIL PROTECTED]> wrote: > > That's true :) One of the reasons the compaq's are expensive > > is they supposedly use a quad board, even for the dual machine. > > Which means a different opteron chip as well. > > I can confirm that. You have a choice of CPUs, but all the DL585s are > expandable to 4 procs if you get the 800 series Opterons. Each CPU > sits on it's own daughter board that links up the HyperTransport > busses between all the others. Each CPU card has (I think...) 8 slots > for DIMMS, for a max of 64G. Why would I want that giant beast when a 1U will do for dual opterons? :) The V40zs have dual procs on the main board with a daughter board for the other two procs. Each CPU has 4 DIMM slots. Sun has the daughter boards for an outrageous price, but you can buy white box Newisys daughter boards for a lot less. The 64GB of 2GB DIMMs I am jealous of, other than that, the DL585 is so outrageously priced I never considered it. ---(end of broadcast)--- TIP 3: 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] best arrangement of 3 disks for (insert) performance
> Having WAL on a separate drive from the database would be something of > a win. I'd buy that 1 disk for OS+WAL and then RAID [something] > across the other two drives for the database would be pretty helpful. Just my .02, I did a lot of testing before I deployed our ~50GB postgresql databases with various combinations of 6 15k SCSI drives. I did custom benchmarks to simulate our applications, I downloaded several benchmarks, etc. It might be a fluke, but I never got better performance with WALs on a different disk than I did with all 6 disks in a 0+1 configuration. Obviously that's not an option with 3 disks. =) I ended up going with that for easier space maintenance. Obviously YMMV, benchmark for your own situation. :) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] software vs hw hard on linux
On Fri, 2003-09-12 at 07:34, Jeff wrote: > What I'm wondering about is what folks experience with software raid vs > hardware raid on linux is. A friend of mine ran a set of benchmarks at > work and found sw raid was running obscenely faster than the mylex and > (some other brand that isn't 3ware) raids.. I ended up going with a hybrid: RAID-1 across sets of two disks in hardware on Adaptec ZCR cards, and RAID-0 across the RAID-1s with Linux software RAID. Although the ZCR (2010 I believe) supports 0+1, using software striping turned in better performance for me. This way, I get brain dead simple dead disk replacement handled by hardware with some speed from software RAID. Also, I would think mirroring on the SCSI controller should take traffic off the PCI bus... I have another machine that's stuck using a Compaq 5i plus controller with no battery backed write cache, in RAID 5. It sucks. Really bad. I'd rather use an IDE drive. :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Linux / Clariion
Anybody used Linux with EMC Clariions for PG databases? Any good war stories, pros, cons, performance results ? I'm wearing thin on my 6 disk 0+1 configuration and looking for something beefy, possibly for clustering, and I'm wondering what the net wisdom is. :) thanks! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
