Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
On Tue, 11 May 2004, Paul Tuckfield wrote: > If you are having a "write storm" or bursty writes that's burying > performance, a scsi raid controler with writeback cache will greatly > improve the situation, but I do believe they run around $1-2k. If > it's write specific problem, the cache matters more than the striping, > except to say that write specfic perf problems should avoid raid5 Actually, a single channel MegaRAID 320-1 (single channel ultra 320) is only $421 at http://www.siliconmechanics.com/c248/u320-scsi.php It works pretty well for me, having 6 months of a production server on one with zero hickups and very good performance. They have a dual channel intel card for only $503, but I'm not at all familiar with that card. The top of the line megaraid is the 320-4, which is only $1240, which ain't bad for a four channel RAID controller. Battery backed cache is an addon, but I think it's only about $80 or so. ---(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] Quad processor options
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bjoern Metzdorf Sent: Tuesday, May 11, 2004 3:11 PM To: scott.marlowe Cc: [EMAIL PROTECTED]; Pgsql-Admin (E-mail) Subject: Re: [PERFORM] Quad processor options scott.marlowe wrote: >>Next drives I'll buy will certainly be 15k scsi drives. > > Better to buy more 10k drives than fewer 15k drives. Other than slightly > faster select times, the 15ks aren't really any faster. Good to know. I'll remember that. >>In peak times we can get up to 700-800 connections at the same time. >>There are quite some updates involved, without having exact numbers I'll >>think that we have about 70% selects and 30% updates/inserts. > > Wow, a lot of writes then. Yes, it certainly could also be only 15-20% updates/inserts, but this is also not negligible. > Sure, adaptec makes one, so does lsi megaraid. Dell resells both of > these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I > believe. We run the lsi megaraid with 64 megs battery backed cache. The LSI sounds good. > Intel also makes one, but I've heard nothing about it. It could well be the ICP Vortex one, ICP was bought by Intel some time ago.. > I haven't directly tested anything but the adaptec and the lsi megaraid. > Here at work we've had massive issues trying to get the adaptec cards > configured and installed on, while the megaraid was a snap. Installed RH, > installed the dkms rpm, installed the dkms enabled megaraid driver and > rebooted. Literally, that's all it took. I didn't hear anything about dkms for debian, so I will be hand-patching as usual :) Regards, Bjoern - Personally I would stay away from anything intel over 2 processors. I have done some research and if memory serves it something like this. Intel's architecture makes each processor compete for bandwidth on the bus to the ram. Amd differs in that each proc has its own bus to the ram. Don't take this as god's honest fact but just keep it in mind when considering a Xeon solution, it may be worth your time to do some deeper research into this. There is some on this here http://www4.tomshardware.com/cpu/20030422/ Rob ---(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] Configuring PostgreSQL to minimize impact of checkpoints
Love that froogle. It looks like a nice card. One thing I didn't get straight is if the cache is writethru or write back. If the original posters problem is truly a burst write problem (and not linux caching or virtual memory overcommitment) then writeback is key. On Tue, 11 May 2004, Paul Tuckfield wrote: If you are having a "write storm" or bursty writes that's burying performance, a scsi raid controler with writeback cache will greatly improve the situation, but I do believe they run around $1-2k. If it's write specific problem, the cache matters more than the striping, except to say that write specfic perf problems should avoid raid5 Actually, a single channel MegaRAID 320-1 (single channel ultra 320) is only $421 at http://www.siliconmechanics.com/c248/u320-scsi.php It works pretty well for me, having 6 months of a production server on one with zero hickups and very good performance. They have a dual channel intel card for only $503, but I'm not at all familiar with that card. The top of the line megaraid is the 320-4, which is only $1240, which ain't bad for a four channel RAID controller. Battery backed cache is an addon, but I think it's only about $80 or so. ---(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 ---(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] Configuring PostgreSQL to minimize impact of checkpoints
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of scott.marlowe Sent: Tuesday, May 11, 2004 2:23 PM To: Paul Tuckfield Cc: [EMAIL PROTECTED]; Matthew Nuzum; [EMAIL PROTECTED]; Rob Fielding Subject: Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints On Tue, 11 May 2004, Paul Tuckfield wrote: > If you are having a "write storm" or bursty writes that's burying > performance, a scsi raid controler with writeback cache will greatly > improve the situation, but I do believe they run around $1-2k. If > it's write specific problem, the cache matters more than the striping, > except to say that write specfic perf problems should avoid raid5 Actually, a single channel MegaRAID 320-1 (single channel ultra 320) is only $421 at http://www.siliconmechanics.com/c248/u320-scsi.php It works pretty well for me, having 6 months of a production server on one with zero hickups and very good performance. They have a dual channel intel card for only $503, but I'm not at all familiar with that card. The top of the line megaraid is the 320-4, which is only $1240, which ain't bad for a four channel RAID controller. Battery backed cache is an addon, but I think it's only about $80 or so. ---(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 - If you don't mind slumming on ebay :-) keep an eye out for PERC III cards, they are dell branded LSI cards. Perc = Power Edge Raid Controller. There are models on there dual channel u320 and dell usually sells them with battery backed cache. That's how I have acquired all my high end raid cards. Rob ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
On Tue, 11 May 2004, Paul Tuckfield wrote: > Love that froogle. > > It looks like a nice card. One thing I didn't get straight is if > the cache is writethru or write back. > > If the original posters problem is truly a burst write problem (and not > linux caching or virtual memory overcommitment) then writeback is key. the MegaRaid can be configured either way. it defaults to writeback if the battery backed cache is present, I believe. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
On Tue, 11 May 2004, Rob Sell wrote: > > If you don't mind slumming on ebay :-) keep an eye out for PERC III cards, > they are dell branded LSI cards. Perc = Power Edge Raid Controller. There > are models on there dual channel u320 and dell usually sells them with > battery backed cache. That's how I have acquired all my high end raid > cards. Not all Perc3s are lsi, many are adaptec. The perc3di is adaptec, the perc3dc is lsi/megaraid. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Intermittent slowdowns, connection delays
Hi All, We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a Dual Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM Seagate disks and gigabit Intel Server Ethernet. The server is dedicated to serving data to our web-based CMS. We have a few web servers load balanced, and we do around 1M page impressions per day. Our website is highly personalized, and we've optimized it to limit the number of queries, but we still see between 2 and 3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more - a fair volume. The single UPDATE per page load is updating a timestamp in a small table (about 150,000 rows) with only 1 index (on the 1 field that needs to be matched). We're seeing some intermittent spikes in query time as actual connection time. I.e., during these seemingly random spikes, our debug output looks like this (times from start of HTTP request): SQL CONNECTION CREATING 'gf' 0.0015 - ESTABLISHING CONNECTION 1.7113 - CONNECTION OK SQL QUERY ID 1 COST 0.8155 ROWS 1 SQL QUERY ID 2 COST 0.5607 ROWS 14 .. etc.. (all queries taking more time than normal, see below) Refresh the page 2 seconds later, and we'll get: SQL CONNECTION CREATING 'gf' 0.0017 - ESTABLISHING CONNECTION 0.0086 - CONNECTION OK SQL QUERY ID 1 COST 0.0128 ROWS 1 SQL QUERY ID 2 COST 0.0033 ROWS 14 .. etc.. (with same queries) Indeed, during these types, it takes a moment for "psql" to connect on the command line (from the same machine using a local file socket), so it's not a network issue or a web-server issue. During these spurts, there's nothing too out of the ordinary in vmstat, systat or top. These programs show that we're not using much CPU (usually 60-80% idle), and disks usage is virtually nil. I've attached 60 seconds of "vmstat 5". Memory usage looks like this (constantly): Mem: 110M Active, 1470M Inact, 206M Wired, 61M Cache, 112M Buf, 26M Free I've cleaned up and tested query after query, and nothing is a "hog". On an idle server, every query will execute in < 0.05 sec. Perhaps some of you veterans have ideas? Thanks, Jason Coene Gotfrag eSports 585-598-6621 Phone 585-598-6633 Fax [EMAIL PROTECTED] http://www.gotfrag.com d01.gotfrag.com> vmstat 5 procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr tw0 fd0 in sy cs us sy id 0 9 5 335952 103108 625 0 0 0 319 4 0 0 5840 437 3 5 92 0 4 5 350772 90140 24534 0 0 0 2533 0 8 0 14480 45969 8 22 71 0 0 0 321016 112884 10603 0 0 0 2840 0 3 0 20300 26562 6 12 82 0 0 0 341428 99548 10823 0 0 0 1014 0 4 0 6870 4891 4 5 91 0 0 0 352064 91748 13041 0 0 0 1979 0 6 0 7430 4950 6 6 88 0 0 0 346236 96024 7562 0 0 0 2070 0 2 0 7360 2057 4 3 93 0 1 0 366876 82184 10081 0 0 0 1502 0 50 0 8280 2607 5 5 90 0 0 0 321600 112344 9724 0 0 0 3984 0 1 0 8850 3440 5 5 90 2 0 0 321200 112716 24244 0 0 0 2571 0 8 0 7940 33756 8 17 75 0 0 1 329016 107352 16676 0 0 0 2834 0 10 0 9220 44430 9 20 71 0 0 0 328620 107328 13862 0 0 0 1713 0 2 0 6160 8500 4 7 90 0 0 0 317376 114780 3798 0 0 0 1321 0 0 0 5140 1137 2 2 97 0 5 0 334724 102396 12999 0 0 0 1106 0 39 0 6720 24891 5 13 82 0 3 3 336904 102068 12886 0 0 0 2527 0 29 0 8790 18817 6 10 84 2 0 0 324008 110416 14625 0 0 0 2378 0 4 0 7450 28433 7 14 79 0 0 4 333692 104400 15440 0 0 0 1154 0 7 0 6450 31156 4 16 80 4 12 0 352328 91884 19349 0 0 0 1095 0 5 0 6230 46283 9 21 70 5 5 0 345796 95412 15790 0 0 0 1896 0 2 0 7270 50062 10 20 70 4 1 0 331440 105316 16178 0 0 0 2909 0 5 0 17280 48194 9 20 71 0 0 0 326664 108364 11869 0 0 0 1533 0 61 0 6400 11855 5 9 85 0 0 2 322980 110452 5970 0 0 0 1520 0 0 0 5940 1614 3 3 95 0 10 6 343108 97884 17571 0 0 0 1409 0 14 0 6430 33528 6 18 76 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Quad processor options
On 2004-05-11T15:29:46-0600, scott.marlowe wrote: > The other nice thing about the LSI cards is that you can install >1 and > the act like one big RAID array. i.e. install two cards with a 20 drive > RAID0 then make a RAID1 across them, and if one or the other cards itself > fails, you've still got 100% of your data sitting there. Nice to know you > can survive the complete failure of one half of your chain. ... unless that dying controller corrupted your file system. Depending on your tolerance for risk, you may not want to operate for long with a file system in an unknown state. Btw, the Intel and LSI Logic RAID controller cards have suspeciously similar specificationsi, so I would be surprised if one is an OEM. /Allan -- Allan Wind P.O. Box 2022 Woburn, MA 01888-0022 USA signature.asc Description: Digital signature
Re: [PERFORM] Quad processor options
On Tue, 2004-05-11 at 12:06, Bjoern Metzdorf wrote: > Has anyone experiences with quad Xeon or quad Opteron setups? I am > looking at the appropriate boards from Tyan, which would be the only > option for us to buy such a beast. The 30k+ setups from Dell etc. don't > fit our budget. > > I am thinking of the following: > > Quad processor (xeon or opteron) > 5 x SCSI 15K RPM for Raid 10 + spare drive > 2 x IDE for system > ICP-Vortex battery backed U320 Hardware Raid > 4-8 GB Ram Just to add my two cents to the fray: We use dual Opterons around here and prefer them to the Xeons for database servers. As others have pointed out, the Opteron systems will scale well to more than two processors unlike the Xeon. I know a couple people with quad Opterons and it apparently scales very nicely, unlike quad Xeons which don't give you much more. On some supercomputing hardware lists I'm on, they seem to be of the opinion that the current Opteron fabric won't really show saturation until you have 6-8 CPUs connected to it. Like the other folks said, skip the 15k drives. Those will only give you a marginal improvement for an integer factor price increase over 10k drives. Instead spend your money on a nice RAID controller with a fat cache and a backup battery, and maybe some extra spindles for your array. I personally like the LSI MegaRAID 320-2, which I always max out to 256Mb of cache RAM and the required battery. A maxed out LSI 320-2 should set you back <$1k. Properly configured, you will notice large improvements in the performance of your disk subsystem, especially if you have a lot of writing going on. I would recommend getting the Opterons, and spending the relatively modest amount of money to get nice RAID controller with a large write-back cache while sticking with 10k drives. Depending on precisely how you configure it, this should cost you no more than $10-12k. We just built a very similar configuration, but with dual Opterons on an HDAMA motherboard rather than a quad Tyan, and it cost <$6k inclusive of everything. Add the money for 4 of the 8xx processors and the Tyan quad motherboard, and the sum comes out to a very reasonable number for what you are getting. j. andrew rogers ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Configuring PostgreSQL to minimize impact of
On Tue, 2004-05-11 at 14:52, Paul Tuckfield wrote: > Love that froogle. > > It looks like a nice card. One thing I didn't get straight is if > the cache is writethru or write back. The LSI MegaRAID reading/writing/caching behavior is user configurable. It will support both write-back and write-through, and IIRC, three different algorithms for reading (none, read-ahead, adaptive). Plenty of configuration options. It is a pretty mature and feature complete hardware RAID implementation. j. andrew rogers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Quad processor options
On Tue, 11 May 2004, Allan Wind wrote: > On 2004-05-11T15:29:46-0600, scott.marlowe wrote: > > The other nice thing about the LSI cards is that you can install >1 and > > the act like one big RAID array. i.e. install two cards with a 20 drive > > RAID0 then make a RAID1 across them, and if one or the other cards itself > > fails, you've still got 100% of your data sitting there. Nice to know you > > can survive the complete failure of one half of your chain. > > ... unless that dying controller corrupted your file system. Depending > on your tolerance for risk, you may not want to operate for long with a > file system in an unknown state. It would have to be the primary controller for that to happen. The way the LSI's work is that you disable the BIOS on the 2nd to 4th cards, and the first card, with the active BIOS acts as the primary controller. In this case, that means the main card is doing the RAID1 work, then handing off the data to the subordinate cards. The subordinate cards do all their own RAID0 work. mobo ---controller 1-- Btw, the Intel and LSI Logic RAID controller cards have suspeciously > similar specificationsi, so I would be surprised if one is an OEM. Hmmm. I'll take a closer look. ---(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] Intermittent slowdowns, connection delays
Hi Paul, Thanks for the valuable feedback. I suspect you're correct about the serialization in some capacity, but the actual cause is eluding me. Basically, every time a registered user checks a page, the site has to authenticate them (with a query against a table with > 200,000 records). It doesn't update this table, however - it updates another table with "user stats" information (last click, last ip, etc). >From what I've seen, there doesn't seem to be any serious locking issues. It does make sense when a number of users whose information isn't in cache, it could take a bit longer - but AFAIK this shouldn't prevent other simultaneous queries. What else could cause such serialization? If I look at open locks (this is a view, info from pg tables): relname| mode | numlocks --+--+-- users| AccessShareLock |4 userstats| AccessShareLock |4 pg_statistic | AccessShareLock |2 users_ix_id | AccessShareLock |2 countries| AccessShareLock |2 comments | AccessShareLock |2 countries_ix_id | AccessShareLock |2 userstats_ix_id | AccessShareLock |2 comments_ix_parentid | AccessShareLock |2 users| RowExclusiveLock |1 filequeue_ix_id | AccessShareLock |1 pg_class | AccessShareLock |1 vopenlocks | AccessShareLock |1 pg_locks | AccessShareLock |1 userstats| RowExclusiveLock |1 filequeue| AccessShareLock |1 pg_class_oid_index | AccessShareLock |1 Also of note, executing a random "in the blue" query on our "users" table returns results very fast. While there's no doubt that caching may help, returning a row that is definitely not cached is very fast: < 0.05 sec. Top tells me that the system isn't using much memory - almost always under 100MB (of the 2GB we have). Is there a way to increase the amount of physical RAM that PG uses? It seems there's a lot of room there. Postgresql.conf has: shared_buffers = 16384 sort_mem = 8192 vacuum_mem = 8192 Also, would queries becoming serialized effect connection delays? I think there's still something else at large here... I've attached a vmstat output, while running dd. The RAID array is tw0. It does show the tw0 device getting significantly more work, numbers not seen during normal operation. Thanks, Jason Coene Gotfrag eSports 585-598-6621 Phone 585-598-6633 Fax [EMAIL PROTECTED] http://www.gotfrag.com -Original Message- From: Paul Tuckfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 7:50 PM To: Jason Coene Subject: Re: [PERFORM] Intermittent slowdowns, connection delays The things you point out suggest a heavy dependence on good cache performance (typical of OLTP mind you) Do not be fooled if a query runs in 2 seconds then the second run takes < .01 secons: the first run put it in cache the second got all cache hits :) But beyond that, in an OLTP system, and typical website backing database, "cache is king". And serialization is the devil So look for reasons why your cache performance might deteriorate during peak, (like large historical tables that users pull up dozens of scattered rows from, flooding cache) or why you may be serializing somewhere inside postgres (ex. if every page hit re-logs in, then theres probably serialization trying to spawn what must be 40 processes/sec assuming your 11hit/sec avg peaks at about 40/sec) Also: I am really surprised you see zero IO in the vmstat you sent, but I'm unfamiliar with BSD version of vmstat. AFAIR, Solaris shows cached filesystem reads as "page faults" which is rather confusing. Since you have 1500 page faults per second, yet no paging (bi bo) does thins mean the 1500 page faults are filesystem IO that pg is doing? do an objective test on an idle system by dd'ing a large file in and watching what vmstat does. On May 11, 2004, at 3:10 PM, Jason Coene wrote: > Hi All, > > We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a > Dual > Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM > Seagate > disks and gigabit Intel Server Ethernet. The server is dedicated to > serving > data to our web-based CMS. > > We have a few web servers load balanced, and we do around 1M page > impressions per day. Our website is highly personalized, and we've > optimized it to limit the number of queries, but we still see between > 2 and > 3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more > - a > fair volume. > > The single UPDATE per page load is updating a timestamp in a small > table > (about 150,000 rows) with only 1 index (on the 1 field that needs to be > matched). > > We're seeing some intermittent spikes in query time as actual
Re: [PERFORM] Quad processor options
BM> see my other mail. BM> We are running Linux, Kernel 2.4. As soon as the next debian version BM> comes out, I'll happily switch to 2.6 :) it's very simple to use 2.6 with testing version, but if you like woody - you can simple install several packets from testing or backports.org if you think about perfomance you must use lastest version of postgresql server - it can be installed from testing or backports.org too (but postgresql from testing depend from many other testing packages). i think if you upgade existing system you can use backports.org for nevest packages, if you install new - use testing - it can be used on production servers today ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Using LIKE expression problem..
Hi everybody.. Before anything else I would like to thank all those person who answers my previous question… again thank you very much This is my question … In my query .. Select * from table1 where lastname LIKE ‘PUNCIA%’.. In the query plan ..it uses seq scan rather than index scan .. why ? I have index on lastname, firtname… Thanks
Re: [PERFORM] Using LIKE expression problem..
In the query plan ..it uses seq scan rather than index scan .. why ? I have index on lastname, firtname… Have you run VACUUM ANALYZE; on the table recently? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Clarification on some settings
Hello, I've been having some performance issues with a DB I use. I'm trying to come up with some performance recommendations to send to the "adminstrator". Hardware: CPU0: Pentium III (Coppermine) 1000MHz (256k cache) CPU1: Pentium III (Coppermine) 1000MHz (256k cache) Memory: 3863468 kB (4 GB) OS: Red Hat Linux release 7.2 (Enigma) Kernel: 2.4.9-31smp I/O I believe is a 3-disk raid 5. /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall were set to 2G Postgres version: 7.3.4 I know its a bit dated, and upgrades are planned, but several months out. Load average seems to hover between 1.0 and 5.0-ish during peak hours. CPU seems to be the limiting factor but I'm not positive (cpu utilization seems to be 40-50%). We have 2 of those set up as the back end to 3 web-servers each... supposedly load-balanced, but one of the 2 dbs consistently has higher load. We have a home-grown replication system that keeps them in sync with each other... peer to peer (master/master). The DB schema is, well to put it nicely... not exactly normalized. No constraints to speak of except for the requisite not-nulls on the primary keys (many of which are compound). Keys are mostly varchar(256) fields. Ok for what I'm uncertain of... shared_buffers: According to http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Its more of a staging area and more isn't necessarily better. That psql relies on the OS to cache data for later use. But according to http://www.ca.postgresql.org/docs/momjian/hw_performance/node3.html its where psql caches previous data for queries because the OS cache is slower, and should be as big as possible without causing swap. Those seem to be conflicting statements. In our case, the "administrator" kept increasing this until performance seemed to increase, which means its now 25 (x 8k is 2G). Is this just a staging area for data waiting to move to the OS cache, or is this really the area that psql caches its data? effective_cache_size: Again, according to the Varlena guide this tells psql how much system memory is available for it to do its work in. until recently, this was set at the default value of 1000. It was just recently increased to 18 (1.5G) according to http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html it should be about 25% of memory? Finally sort_mem: Was until recently left at the default of 1000. Is now 16000. Increasing the effective cache and sort mem didn't seem to make much of a difference. I'm guessing the eff cache was probably raised a bit too much, and shared_buffers is way to high. What can I do to help determine what the proper settings should be and/or look at other possible choke points. What should I look for in iostat, mpstat, or vmstat as red flags that cpu, memory, or i/o bound? DB maintenance wise, I don't believe they were running vacuum full until I told them a few months ago that regular vacuum analyze no longer cleans out dead tuples. Now normal vac is run daily, vac full weekly (supposedly). How can I tell from the output of vacuum if the vac fulls aren't being done, or not done often enough? Or from the system tables, what can I read? Is there anywhere else I can look for possible clues? I have access to the DB super-user, but not the system root/user. Thank you for your time. Please let me know any help or suggestions you may have. Unfortunately upgrading postgres, OS, kernel, or re-writing schema is most likely not an option. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
> > Jack Orenstein <[EMAIL PROTECTED]> writes: > > I'm looking at one case in which two successive transactions, each > > updating a handful of records, take 26 and 18 *seconds* (not msec) to > > complete. These transactions normally complete in under 30 msec. ... > None of this is necessarily going to fix matters for an installation > that has no spare I/O capacity, though. And from the numbers you're > quoting I fear you may be in that category. "Buy faster disks" may > be the only answer ... > I had a computer once that had an out-of-the-box hard drive configuration that provided horrible disk performance. I found a tutorial at O'Reilly that explained how to use hdparm to dramatically speed up disk performance on Linux. I've noticed on other computers I've set up recently that hdparm seems to be used by default out of the box to give good performance. Maybe your computer is using all of it's I/O capacity because it's using PIO mode or some other non-optimal method of accessing the disk. Just a suggestion, I hope it helps, Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! [EMAIL PROTECTED] | http://www.followers.net/isp ---(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] Configuring PostgreSQL to minimize impact of checkpoints
Jan Wieck <[EMAIL PROTECTED]> writes: > If we would combine the background writer and the checkpointer, ... which in fact is on my agenda of things to do ... > then a > "checkpoint flush" could actually be implemented as a temporary change > in that activity that basically is done by not reevaluating the list of > to be flushed blocks any more and switching to a constant amount of > blocks flushed per cycle. When that list get's empty, the checkpoint > flush is done, the checkpoint can complete and the background writer > resumes normal business. Sounds like a plan. I'll do it that way. However, we might want to have different configuration settings controlling the write rate during checkpoint and the rate during normal background writing --- what do you think? Also, presumably a shutdown checkpoint should just whomp out the data without any delays. We can't afford to wait around and risk having init decide we took too long. >> None of this is necessarily going to fix matters for an installation >> that has no spare I/O capacity, though. > As a matter of fact, the background writer increases the overall IO. It > writes buffers that possibly get modified again before a checkpoint or > their replacement requires them to be finally written. So if there is no > spare IO bandwidth, it makes things worse. Right, the trickle writes could be wasted effort. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
Matthew Nuzum wrote: Jack Orenstein <[EMAIL PROTECTED]> writes: I'm looking at one case in which two successive transactions, each updating a handful of records, take 26 and 18 *seconds* (not msec) to complete. These transactions normally complete in under 30 msec. None of this is necessarily going to fix matters for an installation that has no spare I/O capacity, though. And from the numbers you're quoting I fear you may be in that category. "Buy faster disks" may be the only answer ... I had a computer once that had an out-of-the-box hard drive configuration that provided horrible disk performance. I found a tutorial at O'Reilly that explained how to use hdparm to dramatically speed up disk performance on Linux. I've noticed on other computers I've set up recently that hdparm seems to be used by default out of the box to give good performance. Maybe your computer is using all of it's I/O capacity because it's using PIO mode or some other non-optimal method of accessing the disk. There's certainly some scope there. I have an SGI Octane whos SCSI 2 disks were set-up by default with no write buffer and CTQ depth of zero :/ IDE drivers in Linux maybe not detecting your IDE chipset correctly and stepping down, however unlikely there maybe something odd going on but you could check hdparm out. Ensure correct cables too, and the aren't crushed or twisted too bad I digress... Assuming you're running with optimal schema and index design (ie you're not doing extra work unnecessarily), and your backend has better-then-default config options set-up (plenty of tips around here), then disk arrangement is critical to smoothing the ride. Taking things to a relative extreme, we implemented a set-up with issues similar sounding to yours. It was resolved by first optimising everything but hardware, then finally optimising hardware. This served us because it meant we squeezed as much out of the available hardware, before finally throwing more at it, getting us the best possible returns (plus further post optimisation on the new hardware). First tip would to take your pg_xlog and put it on another disk (and channel). Next if you're running a journalled fs, get that journal off onto another disk (and channel). Finally, get as many disks for the data store and spread the load across spindles. You're aiming here to distribute the contention and disk I/O more evenly to remove the congestion. sar and iostat help out as part of the analysis. You say you're using IDE, for which I'd highly recommend switching to SCSI and mutliple controllers because IDE isn't great for lots of other reasons. Obviously budgets count, and playing with SCSI certainly limits that. We took a total of 8 disks across 2 SCSI 160 channels and split up the drives into a number of software RAID arrays. RAID0 mirrors for the os, pg_xlog, data disk journal and swap and the rest became a RAID5 array for the data. You could instead implement your DATA disk as RAID1+0 if you wanted more perf at the cost of free space. Anyway, it's certainly not the fastest config out there, but it made all the difference to this particular application. Infact, we had so much free I/O we recently installed another app on there (based on mysql, sorry) which runs concurrently, and itself 4 times faster than it originally did... YMMV, just my 2p. -- Rob Fielding [EMAIL PROTECTED] www.dsvr.co.uk Development Designer Servers Ltd ---(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] Configuring PostgreSQL to minimize impact of checkpoints
Quoting Rob Fielding <[EMAIL PROTECTED]>: > Assuming you're running with optimal schema and index design (ie you're > not doing extra work unnecessarily), and your backend has > better-then-default config options set-up (plenty of tips around here), > then disk arrangement is critical to smoothing the ride. The schema and queries are extremely simple. I've been experimenting with config options. One possibility I'm looking into is whether shared_buffers is too high, at 12000. We have some preliminary evidence that setting it lower (1000) reduces the demand for IO bandwidth to a point where the spikes become almost tolerable. > First tip would to take your pg_xlog and put it on another disk (and > channel). That's on my list of things to try. > Next if you're running a journalled fs, get that journal off > onto another disk (and channel). Finally, get as many disks for the data > store and spread the load across spindles. Dumb question: how do I spread the data across spindles? Do you have a pointer to something I could read? Jack Orenstein This message was sent using IMP, the Internet Messaging Program. ---(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] Configuring PostgreSQL to minimize impact of checkpoints
On Tue, 11 May 2004 [EMAIL PROTECTED] wrote: > Quoting Rob Fielding <[EMAIL PROTECTED]>: > > > Assuming you're running with optimal schema and index design (ie you're > > not doing extra work unnecessarily), and your backend has > > better-then-default config options set-up (plenty of tips around here), > > then disk arrangement is critical to smoothing the ride. > > The schema and queries are extremely simple. I've been experimenting > with config options. One possibility I'm looking into is whether > shared_buffers is too high, at 12000. We have some preliminary evidence > that setting it lower (1000) reduces the demand for IO bandwidth to > a point where the spikes become almost tolerable. If the shared_buffers are large, postgresql seems to have a performance issue with handling them. Plus they can cause the kernel to dump cache on things that would otherwise be right there and therefore forces the database to hit the drives. You might wanna try settings between 1000 and 1 and see where your sweet spot is. > > First tip would to take your pg_xlog and put it on another disk (and > > channel). > > That's on my list of things to try. > > > Next if you're running a journalled fs, get that journal off > > onto another disk (and channel). Finally, get as many disks for the data > > store and spread the load across spindles. > > Dumb question: how do I spread the data across spindles? Do you have > a pointer to something I could read? Look into a high quality hardware RAID controller with battery backed cache on board. We use the ami/lsi megaraid and I'm quite pleased with its writing performance. How you configure your drives is up to you. For smaller numbers of drives (6 or less) RAID 1+0 is usually a clear winner. For medium numbers of drives, say 8 to 20, RAID 5 works well. For more drives than that, many folks report RAID 5+0 or 0+5 to work well. I've only played around with 12 or fewer drives, so I'm saying RAID 5+0 is a good choice from my experience, just reflecting back what I've heard here on the performance mailing list. If you're not doing much writing, then a software RAID may be a good intermediate solution, especially RAID1 with >2 disks under linux seems a good setup for a mostly read database. ---(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] Help how to tune-up my Database
scott.marlowe wrote: Sorry about that, I meant kbytes, not megs. My point being it's NOT measured in 8k blocks, like a lot of other settings. sorry for the mixup. No worries, I just wanted to sort that out for my own benefit, and anyone else who may not have caught that. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Configuring PostgreSQL to minimize impact of checkpoints
The king of statistics in these cases, is probably vmstat. one can drill down on specific things from there, but first you should send some vmstat output. Reducing cache -> reducing IO suggests to me the OS might be paging out shared buffers. This is indicated by activity in the "si" and "so" columns of vmstat. intentional disk activity by the applciation(postgres) shows up in the "bi" and "bo" columns. If you are having a "write storm" or bursty writes that's burying performance, a scsi raid controler with writeback cache will greatly improve the situation, but I do believe they run around $1-2k. If it's write specific problem, the cache matters more than the striping, except to say that write specfic perf problems should avoid raid5 please send the output of "vmstat 10" for about 10 minutes, spanning good performance and bad performance. On May 11, 2004, at 9:52 AM, [EMAIL PROTECTED] wrote: Quoting Rob Fielding <[EMAIL PROTECTED]>: Assuming you're running with optimal schema and index design (ie you're not doing extra work unnecessarily), and your backend has better-then-default config options set-up (plenty of tips around here), then disk arrangement is critical to smoothing the ride. The schema and queries are extremely simple. I've been experimenting with config options. One possibility I'm looking into is whether shared_buffers is too high, at 12000. We have some preliminary evidence that setting it lower (1000) reduces the demand for IO bandwidth to a point where the spikes become almost tolerable. First tip would to take your pg_xlog and put it on another disk (and channel). That's on my list of things to try. Next if you're running a journalled fs, get that journal off onto another disk (and channel). Finally, get as many disks for the data store and spread the load across spindles. Dumb question: how do I spread the data across spindles? Do you have a pointer to something I could read? Jack Orenstein This message was sent using IMP, the Internet Messaging Program. ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Quad processor options
Hi, I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Quad processor options
We use XEON Quads (PowerEdge 6650s) and they work nice, provided you configure the postgres properly. Dell is the cheapest quad you can buy i think. You shouldn't be paying 30K unless you are getting high CPU-cache on each processor and tons of memory. I am actually curious, have you researched/attempted any postgresql clustering solutions? I agree, you can't just keep buying bigger machines. They have 5 internal drives (4 in RAID 10, 1 spare) on U320, 128MB cache on the PERC controller, 8GB RAM. Thanks, Anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 3:06 PM To: [EMAIL PROTECTED] Cc: Pgsql-Admin (E-mail) Subject: [PERFORM] Quad processor options Hi, I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > Hi, > > I am curious if there are any real life production quad processor setups > running postgresql out there. Since postgresql lacks a proper > replication/cluster solution, we have to buy a bigger machine. > > Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI > hardware-raid 10. > > Has anyone experiences with quad Xeon or quad Opteron setups? I am > looking at the appropriate boards from Tyan, which would be the only > option for us to buy such a beast. The 30k+ setups from Dell etc. don't > fit our budget. > > I am thinking of the following: > > Quad processor (xeon or opteron) > 5 x SCSI 15K RPM for Raid 10 + spare drive > 2 x IDE for system > ICP-Vortex battery backed U320 Hardware Raid > 4-8 GB Ram Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Quad processor options
it's very good to understand specific choke points you're trying to address by upgrading so you dont get disappointed. Are you truly CPU constrained, or is it memory footprint or IO thruput that makes you want to upgrade? IMO The best way to begin understanding system choke points is vmstat output. Would you mind forwarding the output of "vmstat 10 120" under peak load period? (I'm asusming this is linux or unix variant) a brief description of what is happening during the vmstat sample would help a lot too. I am curious if there are any real life production quad processor setups running postgresql out there. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. Has anyone experiences with quad Xeon or quad Opteron setups? I am looking at the appropriate boards from Tyan, which would be the only option for us to buy such a beast. The 30k+ setups from Dell etc. don't fit our budget. I am thinking of the following: Quad processor (xeon or opteron) 5 x SCSI 15K RPM for Raid 10 + spare drive 2 x IDE for system ICP-Vortex battery backed U320 Hardware Raid 4-8 GB Ram Would be nice to hear from you. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Quad processor options
Anjan Dave wrote: We use XEON Quads (PowerEdge 6650s) and they work nice, > provided you configure the postgres properly. > Dell is the cheapest quad you can buy i think. > You shouldn't be paying 30K unless you are getting high CPU-cache > on each processor and tons of memory. good to hear, I tried to online configure a quad xeon here at dell germany, but the 6550 is not available for online configuration. at dell usa it works. I will give them a call tomorrow. I am actually curious, have you researched/attempted any > postgresql clustering solutions? > I agree, you can't just keep buying bigger machines. There are many asynchronous, trigger based solutions out there (eRserver etc..), but what we need is basically a master <-> master setup, which seems not to be available soon for postgresql. Our current dual Xeon runs at 60-70% average cpu load, which is really much. I cannot afford any trigger overhead here. This machine is responsible for over 30M page impressions per month, 50 page impressums per second at peak times. The autovacuum daemon is a god sent gift :) I'm curious how the recently announced mysql cluster will perform, although it is not an option for us. postgresql has far superior functionality. They have 5 internal drives (4 in RAID 10, 1 spare) on U320, > 128MB cache on the PERC controller, 8GB RAM. Could you tell me what you paid approximately for this setup? How does it perform? It certainly won't be twice as fast a as dual xeon, but I remember benchmarking a quad P3 xeon some time ago, and it was disappointingly slow... Regards, Bjoern ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Quad processor options
scott.marlowe wrote: Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. This is basically what I read too. But I cannot spent money on a quad opteron just for testing purposes :) But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1 Quantum ATLAS_V_18_SCA. iostat show between 20 and 60 % user avg-cpu. And this is not even peak time. I attached a "vmstat 10 120" output for perhaps 60-70% peak load. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. Next drives I'll buy will certainly be 15k scsi drives. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. Could you recommend a certain controller type? The only battery backed one that I found on the net is the newest model from icp-vortex.com. Regards, Bjoern ~# vmstat 10 120 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 1 0 24180 10584 32468 2332208 0 1 0 21 2 2 0 0 0 2 0 24564 10480 27812 2313528 8 0 7506 574 1199 8674 30 7 63 2 1 0 24692 10060 23636 2259176 0 18 8099 298 2074 6328 25 7 68 2 0 0 24584 18576 21056 2299804 3 6 13208 305 1598 8700 23 6 71 1 21 1 24504 16588 20912 2309468 4 0 1442 1107 754 6874 42 13 45 6 1 0 24632 13148 19992 2319400 0 0 2627 499 1184 9633 37 6 58 5 1 0 24488 10912 19292 2330080 5 0 3404 150 1466 10206 32 6 61 4 1 0 24488 12180 18824 2342280 3 0 293440 1052 3866 19 3 78 0 0 0 24420 14776 19412 2347232 6 0 403 216 1123 4702 22 3 74 0 0 0 24548 14408 17380 2321780 4 0 522 715 965 6336 25 5 71 4 0 0 24676 12504 17756 2322988 0 0 564 830 883 7066 31 6 63 0 3 0 24676 14060 18232 2325224 0 0 483 388 1097 3401 21 3 76 0 2 1 24676 13044 18700 2322948 0 0 701 195 1078 5187 23 3 74 2 0 0 24676 21576 18752 2328168 0 0 467 177 1552 3574 18 3 78 ---(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] Quad processor options
Did you mean to say the trigger-based clustering solution is loading the dual CPUs 60-70% right now? Performance will not be linear with more processors, but it does help with more processes. We haven't benchmarked it, but we haven't had any problems also so far in terms of performance. Price would vary with your relation/yearly purchase, etc, but a 6650 with 2.0GHz/1MB cache/8GB Memory, RAID card, drives, etc, should definitely cost you less than 20K USD. -anjan -Original Message- From: Bjoern Metzdorf [mailto:[EMAIL PROTECTED] Sent: Tue 5/11/2004 4:28 PM To: Anjan Dave Cc: [EMAIL PROTECTED]; Pgsql-Admin (E-mail) Subject: Re: [PERFORM] Quad processor options Anjan Dave wrote: > We use XEON Quads (PowerEdge 6650s) and they work nice, > provided you configure the postgres properly. > Dell is the cheapest quad you can buy i think. > You shouldn't be paying 30K unless you are getting high CPU-cache > on each processor and tons of memory. good to hear, I tried to online configure a quad xeon here at dell germany, but the 6550 is not available for online configuration. at dell usa it works. I will give them a call tomorrow. > I am actually curious, have you researched/attempted any > postgresql clustering solutions? > I agree, you can't just keep buying bigger machines. There are many asynchronous, trigger based solutions out there (eRserver etc..), but what we need is basically a master <-> master setup, which seems not to be available soon for postgresql. Our current dual Xeon runs at 60-70% average cpu load, which is really much. I cannot afford any trigger overhead here. This machine is responsible for over 30M page impressions per month, 50 page impressums per second at peak times. The autovacuum daemon is a god sent gift :) I'm curious how the recently announced mysql cluster will perform, although it is not an option for us. postgresql has far superior functionality. > They have 5 internal drives (4 in RAID 10, 1 spare) on U320, > 128MB cache on the PERC controller, 8GB RAM. Could you tell me what you paid approximately for this setup? How does it perform? It certainly won't be twice as fast a as dual xeon, but I remember benchmarking a quad P3 xeon some time ago, and it was disappointingly slow... Regards, Bjoern ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > scott.marlowe wrote: > > > Well, from what I've read elsewhere on the internet, it would seem the > > Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the > > exception to this is SGI's altix, which uses their own chipset and runs > > the itanium with very good memory bandwidth. > > This is basically what I read too. But I cannot spent money on a quad > opteron just for testing purposes :) Wouldn't it be nice to just have a lab full of these things? > > If your I/O is saturated, then the answer may well be a better RAID > > array, with many more drives plugged into it. Do you have any spare > > drives you can toss on the machine to see if that helps? Sometimes going > > from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in > > performance. > > Next drives I'll buy will certainly be 15k scsi drives. Better to buy more 10k drives than fewer 15k drives. Other than slightly faster select times, the 15ks aren't really any faster. > > In short, don't expect 4 CPUs to solve the problem if the problem isn't > > really the CPUs being maxed out. > > > > Also, what type of load are you running? Mostly read, mostly written, few > > connections handling lots of data, lots of connections each handling a > > little data, lots of transactions, etc... > > In peak times we can get up to 700-800 connections at the same time. > There are quite some updates involved, without having exact numbers I'll > think that we have about 70% selects and 30% updates/inserts. Wow, a lot of writes then. > > If you are doing lots of writing, make SURE you have a controller that > > supports battery backed cache and is configured to write-back, not > > write-through. > > Could you recommend a certain controller type? The only battery backed > one that I found on the net is the newest model from icp-vortex.com. Sure, adaptec makes one, so does lsi megaraid. Dell resells both of these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I believe. We run the lsi megaraid with 64 megs battery backed cache. Intel also makes one, but I've heard nothing about it. If you get the LSI megaraid, make sure you're running the latest megaraid 2 driver, not the older, slower 1.18 series. If you are running linux, look for the dkms packaged version. dkms, (Dynamic Kernel Module System) automagically compiles and installs source rpms for drivers when you install them, and configures the machine to use them to boot up. Most drivers seem to be slowly headed that way in the linux universe, and I really like the simplicity and power of dkms. I haven't directly tested anything but the adaptec and the lsi megaraid. Here at work we've had massive issues trying to get the adaptec cards configured and installed on, while the megaraid was a snap. Installed RH, installed the dkms rpm, installed the dkms enabled megaraid driver and rebooted. Literally, that's all it took. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Quad processor options
Paul Tuckfield wrote: Would you mind forwarding the output of "vmstat 10 120" under peak load period? (I'm asusming this is linux or unix variant) a brief description of what is happening during the vmstat sample would help a lot too. see my other mail. We are running Linux, Kernel 2.4. As soon as the next debian version comes out, I'll happily switch to 2.6 :) Regards, Bjoern ---(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] Quad processor options
Anjan Dave wrote: Did you mean to say the trigger-based clustering solution > is loading the dual CPUs 60-70% right now? No, this is without any triggers involved. Performance will not be linear with more processors, > but it does help with more processes. > We haven't benchmarked it, but we haven't had any > problems also so far in terms of performance. From the amount of processes view, we certainly can saturate a quad setup :) Price would vary with your relation/yearly purchase, etc, > but a 6650 with 2.0GHz/1MB cache/8GB Memory, RAID card, > drives, etc, should definitely cost you less than 20K USD. Which is still very much. Anyone have experience with a self built quad xeon, using the Tyan Thunder board? Regards, Bjoern ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Quad processor options
scott.marlowe wrote: Next drives I'll buy will certainly be 15k scsi drives. Better to buy more 10k drives than fewer 15k drives. Other than slightly faster select times, the 15ks aren't really any faster. Good to know. I'll remember that. In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. Wow, a lot of writes then. Yes, it certainly could also be only 15-20% updates/inserts, but this is also not negligible. Sure, adaptec makes one, so does lsi megaraid. Dell resells both of these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I believe. We run the lsi megaraid with 64 megs battery backed cache. The LSI sounds good. Intel also makes one, but I've heard nothing about it. It could well be the ICP Vortex one, ICP was bought by Intel some time ago.. I haven't directly tested anything but the adaptec and the lsi megaraid. Here at work we've had massive issues trying to get the adaptec cards configured and installed on, while the megaraid was a snap. Installed RH, installed the dkms rpm, installed the dkms enabled megaraid driver and rebooted. Literally, that's all it took. I didn't hear anything about dkms for debian, so I will be hand-patching as usual :) Regards, Bjoern ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > scott.marlowe wrote: > > Sure, adaptec makes one, so does lsi megaraid. Dell resells both of > > these, the PERC3DI and the PERC3DC are adaptec, then lsi in that order, I > > believe. We run the lsi megaraid with 64 megs battery backed cache. > > The LSI sounds good. > > > Intel also makes one, but I've heard nothing about it. > > It could well be the ICP Vortex one, ICP was bought by Intel some time ago.. Also, there are bigger, faster external RAID boxes as well, that make the internal cards seem puny. They're nice because all you need in your main box is a good U320 controller to plug into the external RAID array. That URL I mentioned earlier that had prices has some of the external boxes listed. No price, not for sale on the web, get out the checkbook and write a blank check is my guess. I.e. they're not cheap. The other nice thing about the LSI cards is that you can install >1 and the act like one big RAID array. i.e. install two cards with a 20 drive RAID0 then make a RAID1 across them, and if one or the other cards itself fails, you've still got 100% of your data sitting there. Nice to know you can survive the complete failure of one half of your chain. > > I haven't directly tested anything but the adaptec and the lsi megaraid. > > Here at work we've had massive issues trying to get the adaptec cards > > configured and installed on, while the megaraid was a snap. Installed RH, > > installed the dkms rpm, installed the dkms enabled megaraid driver and > > rebooted. Literally, that's all it took. > > I didn't hear anything about dkms for debian, so I will be hand-patching > as usual :) Yeah, it seems to be an RPM kinda thing. But, I'm thinking the 2.0 drivers got included in the latest 2.6 kernels, so no biggie. I was looking around in google, and it definitely appears the 2.x and 1.x megaraid drivers were merged into "unified" driver in 2.6 kernel. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Quad processor options
I'm confused why you say the system is 70% busy: the vmstat output shows 70% *idle*. The vmstat you sent shows good things and ambiguous things: - si and so are zero, so your not paging/swapping. Thats always step 1. you're fine. - bi and bo (physical IO) shows pretty high numbers for how many disks you have. (assuming random IO) so please send an "iostat 10" sampling during peak. - note that cpu is only 30% busy. that should mean that adding cpus will *not* help. - the "cache" column shows that linux is using 2.3G for cache. (way too much) you generally want to give memory to postgres to keep it "close" to the user, not leave it unused to be claimed by linux cache (need to leave *some* for linux tho) My recommendations: - I'll bet you have a low value for shared buffers, like 1. On your 3G system you should ramp up the value to at least 1G (125000 8k buffers) unless something else runs on the system. It's best to not do things too drastically, so if Im right and you sit at 1 now, try going to 3 then 6 then 125000 or above. - if the above is off base, then I wonder why we see high runque numbers in spite of over 60% idle cpu. Maybe some serialization happening somewhere. Also depending on how you've laid out your 4 disk drives, you may see all IOs going to one drive. the 7M/sec is on the high side, if that's the case. iostat numbers will reveal if it's skewed, and if it's random, tho linux iostat doesn't seem to report response times (sigh) Response times are the golden metric when diagnosing IO thruput in OLTP / stripe situation. On May 11, 2004, at 1:41 PM, Bjoern Metzdorf wrote: scott.marlowe wrote: Well, from what I've read elsewhere on the internet, it would seem the Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the exception to this is SGI's altix, which uses their own chipset and runs the itanium with very good memory bandwidth. This is basically what I read too. But I cannot spent money on a quad opteron just for testing purposes :) But, do you really need more CPU horsepower? Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting at 99% idle, and iostat says your drives are only running at some small percentage of what you know they could, you might be memory or memory bandwidth limited. Adding two more CPUs will not help with that situation. Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1 Quantum ATLAS_V_18_SCA. iostat show between 20 and 60 % user avg-cpu. And this is not even peak time. I attached a "vmstat 10 120" output for perhaps 60-70% peak load. If your I/O is saturated, then the answer may well be a better RAID array, with many more drives plugged into it. Do you have any spare drives you can toss on the machine to see if that helps? Sometimes going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in performance. Next drives I'll buy will certainly be 15k scsi drives. In short, don't expect 4 CPUs to solve the problem if the problem isn't really the CPUs being maxed out. Also, what type of load are you running? Mostly read, mostly written, few connections handling lots of data, lots of connections each handling a little data, lots of transactions, etc... In peak times we can get up to 700-800 connections at the same time. There are quite some updates involved, without having exact numbers I'll think that we have about 70% selects and 30% updates/inserts. If you are doing lots of writing, make SURE you have a controller that supports battery backed cache and is configured to write-back, not write-through. Could you recommend a certain controller type? The only battery backed one that I found on the net is the newest model from icp-vortex.com. Regards, Bjoern ~# vmstat 10 120 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 1 0 24180 10584 32468 2332208 0 1 0 21 2 2 0 0 0 2 0 24564 10480 27812 2313528 8 0 7506 574 1199 8674 30 7 63 2 1 0 24692 10060 23636 2259176 0 18 8099 298 2074 6328 25 7 68 2 0 0 24584 18576 21056 2299804 3 6 13208 305 1598 8700 23 6 71 1 21 1 24504 16588 20912 2309468 4 0 1442 1107 754 6874 42 13 45 6 1 0 24632 13148 19992 2319400 0 0 2627 499 1184 9633 37 6 58 5 1 0 24488 10912 19292 2330080 5 0 3404 150 1466 10206 32 6 61 4 1 0 24488 12180 18824 2342280 3 0 293440 1052 3866 19 3 78 0 0 0 24420 14776 19412 2347232 6 0 403 216 1123 4702 22 3 74 0 0 0 24548 14408 17380 2321780 4 0 522 715 965 6336 25 5 71 4 0 0 24676 12504 17756 2322988 0 0 564 830 883 7066 31 6 63 0 3 0 24676 14060 18232
Re: [PERFORM] Quad processor options
On Tue, 11 May 2004, Bjoern Metzdorf wrote: > I am curious if there are any real life production quad processor setups > running postgresql out there. Since postgresql lacks a proper > replication/cluster solution, we have to buy a bigger machine. Du you run the latest version of PG? I've read the thread bug have not seen any information about what pg version. All I've seen was a reference to debian which might just as well mean that you run pg 7.2 (probably not but I have to ask). Some classes of queries run much faster in pg 7.4 then in older versions so if you are lucky that can help. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Quad processor options
...and on Tue, May 11, 2004 at 03:02:24PM -0600, scott.marlowe used the keyboard: > > If you get the LSI megaraid, make sure you're running the latest megaraid > 2 driver, not the older, slower 1.18 series. If you are running linux, > look for the dkms packaged version. dkms, (Dynamic Kernel Module System) > automagically compiles and installs source rpms for drivers when you > install them, and configures the machine to use them to boot up. Most > drivers seem to be slowly headed that way in the linux universe, and I > really like the simplicity and power of dkms. > Hi, Given the fact LSI MegaRAID seems to be a popular solution around here, and many of you folx use Linux as well, I thought sharing this piece of info might be of use. Running v2 megaraid driver on a 2.4 kernel is actually not a good idea _at_ _all_, as it will silently corrupt your data in the event of a disk failure. Sorry to have to say so, but we tested it (on kernels up to 2.4.25, not sure about 2.4.26 yet) and it comes out it doesn't do hotswap the way it should. Somehow the replaced disk drives are not _really_ added to the array, which continues to work in degraded mode for a while and (even worse than that) then starts to think the replaced disk is in order without actually having resynced it, thus beginning to issue writes to non-existant areas of it. The 2.6 megaraid driver indeed seems to be a merged version of the above driver and the old one, giving both improved performance and correct functionality in the event of a hotswap taking place. Hope this helped, -- Grega Bremec Senior Administrator Noviforum Ltd., Software & Media http://www.noviforum.si/ pgptjSeTHjtN2.pgp Description: PGP signature
