Re: [PERFORM] Insert are going slower ...
Hervé Piedvache wrote: Josh, Le mercredi 14 Juillet 2004 18:28, Josh Berkus a écrit : checkpoint_segments = 3 You should probably increase this if you have the disk space. For massive insert operations, I've found it useful to have as much as 128 segments (although this means about 1.5GB disk space) Other point I have also read this : NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop checkpointing. So ... still true for 7.4.3 ??? So I'm with fsync = off so the value of checkpoint_segments have no interest ?? Thanks for your help... I suggest you check this first. Check the performance tuning guide.. http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php That is a starters. As Josh suggested, increase checkpoint segments if you have disk space. Correspondingly WAL disk space requirements go up as well. HTH Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 15, 2004 at 12:52:38AM -0400, Tom Lane wrote: No, it's not missing anything. The number being reported here is the number of rows pulled from the plan node --- but this plan node is on the inside of a merge join, and one of the properties of merge join is that it will do partial rescans of its inner input in the presence of equal keys in the outer input. If you have, say, 10 occurrences of 42 in the outer input, then any 42 rows in the inner input have to be rescanned 10 times. EXPLAIN ANALYZE will count each of them as 10 rows returned by the input node. OK, that makes sense, although it seems to me as is loops= should have been something larger than 1 if the data was scanned multiple times. The large multiple here (80-to-one overscan) says that you've got a lot of duplicate values in the outer input. This is generally a good situation to *not* use a mergejoin in ;-). We do have some logic in the planner that attempts to estimate the extra cost involved in such rescanning, but I'm not sure how accurate the cost model is. Hum, I'm not sure if I'm in the termiology here -- outer input in A left join B is A, right? But yes, I do have a lot of duplicates, that seems to match my data well. Raising shared_buffers seems unlikely to help. I do agree with raising sort_mem --- not so much to make the merge faster as to encourage the thing to try a hash join instead. sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Swapping in 7.4.3
With pg_autovaccum it's now at 95M swap; averaging 5MB / day increase with same load. Cache slightly increases or decreases according to top. --- On Tue 07/13, Matthew T. O'Connor [EMAIL PROTECTED] wrote: From: Matthew T. O'Connor [mailto: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Date: Tue, 13 Jul 2004 16:26:09 -0400 Subject: Re: [PERFORM] Swapping in 7.4.3 Jim Ewert wrote:br When I went to 7.4.3 (Slackware 9.1) w/ JDBC, the improvements are that it doesn't initially take much memory (have 512M) and didn't swap. I ran a full vaccum and a cluster before installation, however speed degaded to 1 *second* / update of one row in 150 rows of data, within a day! pg_autovacuum now gives excellent performance however it is taking 66M of swap; only 270k cached.br brbrAre you saying that your system stays fast now that you are using brpg_autovacuum, but pg_autovacuum is using 66M of memory? Please brclarify, I'm not sure what question you want an answered.brbrMatthewbrbr ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! ---(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] vacuum full 100 mins plus?
Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: A long time ago, in a galaxy far, farpliers [EMAIL PROTECTED] (Patrick Hatcher) wrote: Answered my own question. I gave up the vacuum full after 150 mins. I was able to export to a file, vacuum full the empty table, and reimport in less than 10 mins. I suspect the empty item pointers and the sheer number of removable rows was causing an issue. In that case, you'd be a little further better off if the steps were: - drop indices; - copy table to file (perhaps via pg_dump -t my_table); - truncate the table, or drop-and-recreate, both of which make it unnecessary to do _any_ vacuum of the result; - recreate indices, probably with SORT_MEM set high, to minimize paging to disk - analyze the table (no need to vacuum if you haven't created any dead tuples) - cut SORT_MEM back down to normal sizes Rather than doing all this manually, you can just CLUSTER on any handy index. In 7.5, another possibility is to issue one of the forms of ALTER TABLE that force a table rewrite. The range of usefulness of VACUUM FULL is really looking narrower and narrower to me. I can foresee a day when we'll abandon it completely. I would love to see this 10lb sledge hammer go away when we have enough tiny screwdrivers and needlenose pliers to make it obsolete! regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Swapping in 7.4.3
This is normal. My personal workstation has been up for 16 days, and it shows 65 megs used for swap. The linux kernel looks for things that haven't been accessed in quite a while and tosses them into swap to free up the memory for other uses. This isn't PostgreSQL's fault, or anything elses. It's how a typical Unix kernel works. I.e. you're seeing a problem that simply isn't there. On Thu, 2004-07-15 at 07:49, Jim Ewert wrote: With pg_autovaccum it's now at 95M swap; averaging 5MB / day increase with same load. Cache slightly increases or decreases according to top. --- On Tue 07/13, Matthew T. O'Connor [EMAIL PROTECTED] wrote: From: Matthew T. O'Connor [mailto: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Date: Tue, 13 Jul 2004 16:26:09 -0400 Subject: Re: [PERFORM] Swapping in 7.4.3 Jim Ewert wrote:br When I went to 7.4.3 (Slackware 9.1) w/ JDBC, the improvements are that it doesn't initially take much memory (have 512M) and didn't swap. I ran a full vaccum and a cluster before installation, however speed degaded to 1 *second* / update of one row in 150 rows of data, within a day! pg_autovacuum now gives excellent performance however it is taking 66M of swap; only 270k cached.br brbrAre you saying that your system stays fast now that you are using brpg_autovacuum, but pg_autovacuum is using 66M of memory? Please brclarify, I'm not sure what question you want an answered.brbrMatthewbrbr ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! ---(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 ---(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] Insert are going slower ...
Shridhar, I suggest you check this first. Check the performance tuning guide.. http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php That is a starters. As Josh suggested, increase checkpoint segments if you have disk space. Correspondingly WAL disk space requirements go up as well. Well, not if he has fsync=off. But having fsync off is a very bad idea. You do realize, Herve', that if you lose power on that machine you'll most likely have to restore from backup? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] hardware raid suggestions
I've been using the adaptec ZCR raid cards in our servers for a while now, mostly small systems with 3 or 6 disks, and we've been very happy with them. However, we're building a new DB machine with 14 U320 15K SCA drives, and we've run into a performance bottlenkeck with the ZCR card where it just won't scale well. Without going into too many details, we've tested RAID5, RAID10 and RAID50 on pretty much every array size from 4-14 disks (raid 50 tests used more drives), using JFS, reiserfs and EXT3. With every different configuration, performance didn't improve after array size became greater than 6 disks.. We used various benchmarks, including pgbench with scale factors of 10, 100, 1000, 5000 and clients of 10, 15, 30 and 45. We've done many other tests and monitoring tools, and we've come to the conclusion that the ZCR is the problem. We're looking into getting an Adaptec 2200S or the Megaraid 320 2x which have better processors, and hopefully better performance. We feel that the use of the AIC7930 as the CPU on the ZCR just doesn't cut it and a faster raid controller would work better. Does anyone out there have any experience with these cards with postgresql and linux? If so, would you be willing to share your experiences and possibly give a recommendation? --brian ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Odd sorting behaviour
Steinar, sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) Well, then the next step is increasing the statistical sampling on the 3 join columns in that table. Try setting statistics to 500 for each of the 3 cols, analyze, and see if that makes a difference. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 15, 2004 at 11:11:33AM -0700, Josh Berkus wrote: sort_mem is already 16384, which I thought would be plenty -- I tried increasing it to 65536 which made exactly zero difference. :-) Well, then the next step is increasing the statistical sampling on the 3 join columns in that table. Try setting statistics to 500 for each of the 3 cols, analyze, and see if that makes a difference. Made no difference on either version (7.2 or 7.4). BTW, you guys can stop Cc-ing me now; I'm subscribed. :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(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] hardware raid suggestions
Brian, We're looking into getting an Adaptec 2200S or the Megaraid 320 2x which have better processors, and hopefully better performance. We feel that the use of the AIC7930 as the CPU on the ZCR just doesn't cut it and a faster raid controller would work better. Does anyone out there have any experience with these cards with postgresql and linux? If so, would you be willing to share your experiences and possibly give a recommendation? Yes, my experience with adaptecs has been universally bad. I just really don't think that the SCSI-2 card company is up to making high-end raid cards. MegaRaid is generally positively reviewed in a lot of places. Be careful to order the battery back-up at the same time as the Raid card; the batteries have the annoying habit of going off the market for months at a time. You should also consider looking into driver issues. In general, the RAID card drivers distributed for Linux simply aren't as good as those the same companies write for Windows or Unix. That may be your issue with the ZCR, as well as CPU. Oh, and don't bother with the upgrade if you're not getting battery backup. You need it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] hardware raid suggestions
Not sure what your hw platform is, but I always used to get fantastic performance from Compaq Smart Array battery backed cards. Note that I haven't bought any recently so HP may have hp invent-ed them... But whatever the brand - if you get a swag of battery backed cache you won't know yourself. It's fun to install an OS on them as well - watch the drive format and verify take 10 seconds ;) Another option to look at is outboard raid boxes that present a single drive interface to the server - I know people who swear by them. -- Mark Aufflick e [EMAIL PROTECTED] w www.pumptheory.com (work) w mark.aufflick.com (personal) p +61 438 700 647 On 16/07/2004, at 4:07 AM, Brian Hirt wrote: I've been using the adaptec ZCR raid cards in our servers for a while now, mostly small systems with 3 or 6 disks, and we've been very happy with them. However, we're building a new DB machine with 14 U320 15K SCA drives, and we've run into a performance bottlenkeck with the ZCR card where it just won't scale well. Without going into too many details, we've tested RAID5, RAID10 and RAID50 on pretty much every array size from 4-14 disks (raid 50 tests used more drives), using JFS, reiserfs and EXT3. With every different configuration, performance didn't improve after array size became greater than 6 disks.. We used various benchmarks, including pgbench with scale factors of 10, 100, 1000, 5000 and clients of 10, 15, 30 and 45. We've done many other tests and monitoring tools, and we've come to the conclusion that the ZCR is the problem. We're looking into getting an Adaptec 2200S or the Megaraid 320 2x which have better processors, and hopefully better performance. We feel that the use of the AIC7930 as the CPU on the ZCR just doesn't cut it and a faster raid controller would work better. Does anyone out there have any experience with these cards with postgresql and linux? If so, would you be willing to share your experiences and possibly give a recommendation? --brian ---(end of broadcast)--- TIP 8: explain analyze is your friend === = Pain free spam virus protection by: www.mailsecurity.net.au Forward undetected SPAM to: [EMAIL PROTECTED] === = Pain free spam virus protection by: www.mailsecurity.net.au Forward undetected SPAM to: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
