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] OT: Help with performance problems
scott.marlowe wrote: On Fri, 23 Apr 2004, Chris Hoover wrote: DB's on Powervaults 220S using raid 5 (over 6 disks) What controller is this, the adaptec? We've found it to be slower than the LSI megaraid based controller, but YMMV. Wow, really? You got any more details of the chipset, mobo and kernel driver ? I've been taken to my wits end wrestling with an LSI MegaRAID 320-1 controller on a supermicro board all weekend. I just couldn't get anything more than 10MB/sec out of it with megaraid driver v1 OR v2 in Linux 2.4.26, nor the version in 2.6.6-rc2. After 2 days of humming the Adaptec mantra I gave in and switched the array straight onto the onboard Adaptec 160 controller (same cable and everything). Software RAID 5 gets me over 40MB sec for a nominal cpu hit - more than 4 times what I could get out of the MegaRAID controller :( Even the 2nd SCSI-2 channel gets 40MB/sec max (pg_xlog :) And HOW LONG does it take to detect drives during POSTo never mind ... I really just wanna rant :) There should be a free counseling service for enraged sysops. -- Rob Fielding [EMAIL PROTECTED] www.dsvr.co.uk Development Designer Servers Ltd ---(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] Where to start for performance problem?
The problems with giving suggestions about increasing performance is that one persons increase is another persons decrease. having said that, there are a few general suggestions : Set-up some shared memory, about a tenth of your available RAM, and configure shared_memory and max_clients correctly. I've used the following formula, ripped off the net from somewhere. It's not entirely acurate, as other settings steal a little shared memory, but it works for the most part : ((1024*RAM_SIZE) - (14.2 * max_connections) - 250) / 8.2 as I say, it should get you a good value, otherwise lower it bit by bit if you have trouble starting your db. Increase effective_cache (50%-70% avail ram) and sort_mem (about 1/20th ram) and lower you random_page_cost to around 2 or less (as low as 0.3) if you have fast SCSI drives in a RAID10 set-up - this was a big speedup ;) But this might not be the answer though. The values detailed above are when tuning an already stable setup. Perhaps you need to look at your system resource usage. If you're degrading performance over time it sounds to me like you are slowly running out of memory and swap ? Generall if I take something over, I'll try and get it onto my terms. Have you tried importing the DB to a fresh installation, one where you know sensible defaults are set, so you aren't inheriting any cruft from the previous sysadmin. To be honest tho, I've never run pg so that it actually shutdown because it was running so badly - i just wouldn't think it would do that. -- 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] tuning questions
I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum memory to 8192, and effective cache size to 1. /proc/sys/kernel/shmmax is set to 16 and /proc/sys/fs/file-max is set to 65536. Ulimit -n 3192. Your sharedmemory is too high, and not even being used effectivey. Your other settings are too low. Ball park guessing here, but I'd say first read (and understand) this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Then make shared memory about 10-20% available ram, and set: ((shmmax/1024) - ( 14.2 * max_connections ) - 250 ) / 8.2 = shared_buffers decrease random_page_cost to 0.3 and wack up sort mem by 16 times, effective cache size to about 50% RAM (depending on your other settings) and try that for starters. -- Rob Fielding [EMAIL PROTECTED] www.dsvr.co.uk Development Designer Servers Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] WAL Optimisation - configuration and usage
48468 $ vmstat 5 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 7 1 29588 10592 15700 809060 1 097750 103 13 9 79 3 8 0 29588 11680 15736 807620 0 0 3313 438 1838 3559 19 13 68 2 13 1 29588 12808 15404 800328 0 0 4470 445 1515 1752 7 7 86 0 9 1 29588 10992 15728 806476 0 0 2933 781 1246 2686 14 10 76 2 5 1 29588 11336 15956 807884 0 0 3354 662 1773 5211 27 17 57 4 5 0 29696 13072 16020 813872 0 24 4282 306 2632 7862 45 25 31 4 6 1 29696 10400 16116 815084 0 0 5086 314 2668 7893 47 26 27 9 2 1 29696 13060 16308 814232 27 0 3927 748 2586 7836 48 29 23 3 8 1 29696 10444 16232 812816 3 0 4015 433 2443 7180 47 28 25 8 4 0 29696 10904 16432 812488 0 0 4537 500 2616 8418 46 30 24 4 6 2 29696 11048 16320 810276 0 0 6076 569 1893 3919 20 14 66 0 5 0 29696 10480 16600 813788 0 0 4595 435 2400 6215 33 21 46 3 6 0 29696 10536 16376 812248 0 0 3802 504 2417 7921 43 25 32 1 6 1 29696 11236 16500 809636 0 0 3691 357 2171 5199 24 15 61 0 14 1 29696 10228 16036 801368 0 0 4038 561 1566 3288 16 12 72 Sorry it's so long but I thought some brief info would be better than not. Thanks for reading, -- Rob Fielding Development Designer Servers Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] WAL Optimisation - configuration and usage
Rod Taylor wrote: random_page_cost = 0.5 Try a value of 2 for a while. OK thanks Richard and Rod. I've upped this to 2. I think I left this over from a previous play with setttings on my IDE RAID 0 workstation. It seemed to have a good effect being set as a low float so it stuck. I've set it to 2. From another post off list, I've also bumped up max_fsm_relations = 1000 # min 10, fsm max_fsm_pages = 2 # min 1000, fs vacuum_mem = 32768 # min 1024 as they did seem a little low. I'm hesitant to set them too high at this stage as I'd prefer to keep as much RAM available for runtime at this time. I'm still hoping that perhaps the uber-pgadmin Mr Lane might reply about my WAL issue :) however I'm getting the feeling now the server is running with a much higher level of performance than it has been. Won't know until tomorrow thought. Cheers, -- Rob Fielding 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] WAL Optimisation - configuration and usage
Further update to my WAL experimentation. pg_xlog files have increased to 81, and checking today up to 84. Currently nothing much going on with the server save a background process running a select every 30 seconds with almost no impact (according to IO from vmstats). This in itself is a good sign - an improvement on running last week, but I'd still like to get clarification on WAL file usage if possible. Log file tailing has nothing more interesting than a whole set of "recycled transaction log file" entries : 2004-03-01 16:01:55 DEBUG: recycled transaction log file 00710017 2004-03-01 16:07:01 DEBUG: recycled transaction log file 00710018 2004-03-01 16:17:14 DEBUG: recycled transaction log file 00710019 2004-03-01 16:22:20 DEBUG: recycled transaction log file 0071001A 2004-03-01 16:32:31 DEBUG: recycled transaction log file 0071001B 2004-03-01 16:37:36 DEBUG: recycled transaction log file 0071001C 2004-03-01 16:47:48 DEBUG: recycled transaction log file 0071001D 2004-03-01 16:52:54 DEBUG: recycled transaction log file 0071001E 2004-03-01 17:03:05 DEBUG: recycled transaction log file 0071001F Looks kinda automated, but the times aren't quite even at around 6-10 minutes apart. cheers, -- 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
