Re: [PERFORM] Tuning PostgreSQL
On 2003-07-22 09:04:42 +0200, Alexander Priem wrote: > Hi all, > > Vincent, You said that using RAID1, you don't have real redundancy. But > RAID1 is mirroring, right? So if one of the two disks should fail, there > should be no data lost, right? > Right. But the proposal was a single disk for WAL, without redundancy, and I argued that wasn't really safe. RAID1 by itself is extremely safe, possibly even the safest RAID type there is. > I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb > drives. I don't know if I can get the money for this, but how would the > following setup sound? > > Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL. > Four 18Gb (15.000rpm) disks in RAID5 array for data. > Our own testing has shown that a 6 disk RAID-10 array is faster than what you describe. Of course, this is very much dependant on how much INSERT/UPDATES you generate (which taxes your WAL more), so your mileage may vary. > For the same amount of money, I could also get: > > Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL. > Five/Six 36Gb (10.000rpm) disks in RAID5 array for data. > It is said that a higher RPM is particularly useful for a WAL disk. So you might consider using two 18GB 15K rpm drives for a RAID-1 WAL disk (+OS and swap), and using 36GB 10K rpm disks in a RAID-5 array if you need that diskspace. > Which would be the best of the above? The one with four 15k-rpm disks or the > one with five/six 10k-rpm disks? > Would these configs be better than all disks in one huge RAID5 array? There > are so many possible configs with RAID... > 15K rpm disks are significantly faster than 10K rpm disks. If your only concern is performance, buy 15K rpm disks. If you want more diskspace for your money, fall back to larger 10K rpm disks. I personally think seperate WAL disks are vastly overrated, since they haven't shown a big performance gain in our own tests. But as I have said, this is extremely dependant on the type of load you generate, so only your own tests can tell you what you should do in this respect. About RAID types: the fastest RAID type by far is RAID-10. However, this will cost you a lot of useable diskspace, so it isn't for everyone. You need at least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want as much useable diskspace as possible and still want to be redundant. RAID-1 is very useful for small (2-disk) arrays. If you have the time and are settled on buying 6 disks, I'd test the following scenarios: - 6-disk RAID-10 array (should perform best) - 4-disk RAID-10 array containing data, 2-disk RAID-1 array for WAL, OS, etc - 4-disk RAID-5 array containing data, 2-disk RAID-1 array for WAL, OS, etc - 6-disk RAID-5 array (will probably perform worst) Hope this helps. Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Tuning PostgreSQL
On 2003-07-21 09:06:10 -0700, Josh Berkus wrote: > Alexander, > > > Hmmm. Seems to me that this setup would be better than one RAID5 with three > > 36Gb disks, wouldn't you think so? With one RAID5 array, I would still have > > the data and the WAL on one volume... > > Definitely. As I've said, my experience with RAID5 is that with less than 5 > disks, it performs around 40% of a single scsi disk for large read-write > operation on Postgres. > > If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1 array > for the database. > In this setup your database is still screwed if a single disk (the WAL disk) stops working. You'll have to revert to your last backup if this happens. The RAID-1 redundancy on your data disks buys you almost nothing: marginally better performance and no real redundancy should a single disk fail. I'd use RAID-5 if you absolutely cannot use more disks, but I would use RAID-10 or two RAID-1 partitions if you can afford to use 4 disks. Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Sanity check requested
On 2003-07-18 18:20:55 +0200, Oliver Scheit wrote: > > Be sure to mount noatime > > I did "chattr -R +A /var/lib/pgsql/data" > that should do the trick as well or am I wrong? > According to the man page it gives the same effect. There are a few things you should consider though: - new files won't be created with the same options (I think), so you'll have to run this command as a daily cronjob or something to that effect - chattr is probably more filesystem-specific than a noatime mount, although this isn't a problem on ext[23] ofcourse Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sanity check requested
On 2003-07-17 10:41:35 -0500, Nick Fankhauser wrote: > I'm using ext2. For now, I'll leave this and the OS version alone. If I > I'd upgrade to a journaling filesystem as soon as possible for reliability. Testing in our own environment has shown that PostgreSQL performs best on ext3 (yes, better than XFS, JFS or ReiserFS) with a linux 2.4.21 kernel. Be sure to mount noatime and to create the ext3 partition with the correct stripe size of your RAID array using the '-R stride=foo' option (see man mke2fs). Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Hardware performance
On 2003-07-16 19:57:22 -0700, Balazs Wellisch wrote: > We're now stuck on the question of what type of RAID configuration to use > for this server. RAID 5 offers the best fault tolerance but doesn't perform > all that well. RAID 10 offers much better performance, but no hot swap. Or > should we not use RAID at all. I know that ideally the log (WAL) files > should reside on a separate disk from the rest of the DB. Should we use 4 > separate drives instead? One for the OS, one for data, one for WAL, one for > swap? Or RAID 10 for everything plus 1 drive for WAL? Or RAID 5 for > everything? > We have recently run our own test (simulating our own database load) on a new server which contained 7 15K rpm disks. Since we always want to have a hot-spare drive (servers are located in a hard-to-reach datacenter) and we always want redundancy, we tested two different configurations: - 6 disk RAID 10 array, holding everything - 4 disk RAID 5 array holding postgresql data and 2 disk RAID 1 array holding OS, swap and WAL logs Our database is used for a very busy community website, so our load contains a lot of inserts/updates for a website, but much more selects than there are updates. Our findings were that the 6 disk RAID 10 set was significantly faster than the other setup. So I'd recommend a 4-disk RAID 10 array. I'd use the 5th drive for a hot-spare drive, but that's your own call. However, it would be best if you tested some different setups under your own database load to see what works best for you. Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Strange result: UNIX vs. TCP/IP sockets
http://grotto11.com/blog/slash.html?+1039831658 Summary: IE and IIS cheat at TCP level by leaving out various SYN and ACK packets, thereby making IE requests from IIS servers blazingly fast, and making IE requests to non-IIS servers infuriatingly slow. But since this only relates to making and breaking TCP connections, I don't think this is relevant for a larger query time. It's probably normal for a TCP connection to be slightly slower than a unix socket, but I don't think that's wat Andrew is experiencing. On 2003-07-04 14:35:18 -0300, The Hermit Hacker wrote: > > 'K, this is based on "old information", I don't know if Sun changed it > 'yet again' ... but, when I was working at the University, one of our IT > directors gave me a report that deal with something Sun did (god, I'm so > detailed here, eh?) to "mimic" how Microsoft broke the TCP/IP protocol > ... the report was in relation to Web services, and how the change > actually made Sun/Solaris appear to be slower then Microsoft ... > > And Sun made this the 'default' setting, but it was disablable in > /etc/systems ... > > Sorry for being so vague, but if I recall correctly, it had something to > do with adding an extra ACK to each packet ... maybe even as vague as the > above is, it will jar a memory for someone else? > > > On Fri, 4 Jul 2003, Andrew Sullivan wrote: > > > Hi all, > > > > We're run into a rather odd problem here, and we're puzzling out > > what's going on. But while we do, I thought I'd see if anyone else > > has anything similar to report. > > > > This is for 7.2.4 on Solaris 8. > > > > We have a query for which EXPLAIN ANALYSE on a local psql connection > > always returns a time of between about 325 msec and 850 msec > > (depending on other load, whether the result is in cache, &c. -- this > > is an aggregates query involving min() and count()). > > > > If I connect using -h 127.0.0.1, however, I can _sometimes_ get the > > query to take as long as 1200 msec. The effect is sporadic (of > > course. If it were totally predictable, the computing gods wouldn't > > be having any fun with me), but it is certainly there off and on. > > (We discovered it because our application is regularly reporting > > times on this query roughly twice as long as I was able to get with > > psql, until I connected via TCP/IP.) > > > > I'll have more to report as we investigate further -- at the moment, > > this has cropped up on a production system, and so we're trying to > > reproduce it in our test environment. Naturally, we're looking at > > the TCP/IP stack configuration, among other stuff. In the meantime, > > however, I wondered if anyone knows which bits I ought to be prodding > > at to look for sub-optimal libraries, &c.; or whether anyone else has > > run into similar problems on Solaris or elsewhere. > > > > A > > > > -- > > > > Andrew Sullivan 204-4141 Yonge Street > > Liberty RMS Toronto, Ontario Canada > > <[EMAIL PROTECTED]> M2P 2A8 > > +1 416 646 3304 x110 > > > > > > ---(end of broadcast)----------- > > TIP 8: explain analyze is your friend > > > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy > Systems Administrator @ hub.org > primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Vincent van Leeuwen Media Design - http://www.mediadesign.nl/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
