Re: [PERFORM] Insert are going slower ...
From: Hervé Piedvache [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 11:42 PM effective_cache_size = 500 looks like madness to me. my (modest) understanding of this, is that you are telling postgres to assume a 40Gb disk cache (correct me if I am wrong). btw, how much effect does this setting have on the planner? is there a recommended procedure to estimate the best value for effective_cache_size on a dedicated DB server ? gnari ---(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] Insert are going slower ...
gnari wrote: is there a recommended procedure to estimate the best value for effective_cache_size on a dedicated DB server ? Rule of thumb(On linux): on a typically loaded machine, observe cache memory of the machine and allocate good chunk of it as effective cache. To define good chunck of it, you need to consider how many other things are running on that machine. If it is file server + web server + database server, you have to allocate the resources depending upon requirement. But remember It does not guarantee that it will be a good value. It is just a starting point..:-) You have to tune it further if required. HTH Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Insert are going slower ...
Le mercredi 14 Juillet 2004 12:13, Shridhar Daithankar a écrit : gnari wrote: is there a recommended procedure to estimate the best value for effective_cache_size on a dedicated DB server ? Rule of thumb(On linux): on a typically loaded machine, observe cache memory of the machine and allocate good chunk of it as effective cache. To define good chunck of it, you need to consider how many other things are running on that machine. If it is file server + web server + database server, you have to allocate the resources depending upon requirement. But remember It does not guarantee that it will be a good value. It is just a starting point..:-) You have to tune it further if required. In my case it's a PostgreSQL dedicated server ... effective_cache_size = 500 For me I give to the planner the information that the kernel is able to cache 500 disk page in RAM free total used free sharedbuffers cached Mem: 79591207712164 246956 0 173727165704 -/+ buffers/cache: 5290887430032 Swap: 2097136 98802087256 What should I put ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Insert are going slower ...
Hervé Piedvache wrote: In my case it's a PostgreSQL dedicated server ... effective_cache_size = 500 For me I give to the planner the information that the kernel is able to cache 500 disk page in RAM That is what? 38GB of RAM? free total used free sharedbuffers cached Mem: 79591207712164 246956 0 173727165704 -/+ buffers/cache: 5290887430032 Swap: 2097136 98802087256 What should I put ? 7165704 / 8 = 895713 So counting variations, I would say 875000. That is a 8GB box, right? So 875000 is about 7000MB. Which should be rather practical. Of course you can give it everything you can but that's upto you. Can you get explain analze for inserts? I think some foreign key check etc. are taking long and hence it accumulates. But that is just a wild guess. Off the top of my head, you have allocated roughly 48K shard buffers which seems bit on higher side. Can you check with something like 10K-15K? HTH Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Insert are going slower ...
Herve' I forgot to ask about your hardware. How much RAM, and what's your disk setup? CPU? sort_mem = 512000 Huh? Sort_mem is in K. The above says that you've allocated 512MB sort mem. Is this process the *only* thing going on on the machine? vacuum_mem = 409600 Again, 409.6MB vacuum mem? That's an odd number, and quite high. max_fsm_pages = 5000 50million? That's quite high. Certianly enough to have an effect on your memory usage. How did you calculate this number? 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) effective_cache_size = 500 If you actually have that much RAM, I'd love to play on your box. Please? Off the top of my head, you have allocated roughly 48K shard buffers which seems bit on higher side. Can you check with something like 10K-15K? Shridhar, that depends on how much RAM he has. On 4GB dedicated machines, I've set Shared_Buffers as high as 750MB. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Insert are going slower ...
Josh, Le mercredi 14 Juillet 2004 18:28, Josh Berkus a écrit : I forgot to ask about your hardware. How much RAM, and what's your disk setup? CPU? 8 Gb of RAM Bi - Intel Xeon 2.00GHz Hard drive in SCSI RAID 5 /dev/sdb6 101G 87G 8.7G 91% /usr/local/pgsql/data /dev/sda7 1.8G 129M 1.6G 8% /usr/local/pgsql/data/pg_xlog Server dedicated to PostgreSQL with only one database. sort_mem = 512000 Huh? Sort_mem is in K. The above says that you've allocated 512MB sort mem. Is this process the *only* thing going on on the machine? PostgreSQL dedicated server yes ... so it's too much ? How you decide the good value ? vacuum_mem = 409600 Again, 409.6MB vacuum mem? That's an odd number, and quite high. Yep but I have 8 Gb of memory ... ;o) So why not ? Just explain me why it's not a good choice ... I have done this because of this text from you found somewhere : As this setting only uses RAM when VACUUM is running, you may wish to increase it on high-RAM machines to make VACUUM run faster (but never more than 20% of available RAM!) So that's less than 20% of my memory ... max_fsm_pages = 5000 50million? That's quite high. Certianly enough to have an effect on your memory usage. How did you calculate this number? Not done by me ... and the guy is out ... but in same time with 8 Gb of RAM ... that's not a crazy number ? 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) effective_cache_size = 500 If you actually have that much RAM, I'd love to play on your box. Please? Hum ... yes as Shridhar told me the number is a crazy one and now down to 875000 ... Off the top of my head, you have allocated roughly 48K shard buffers which seems bit on higher side. Can you check with something like 10K-15K? Shridhar, that depends on how much RAM he has. On 4GB dedicated machines, I've set Shared_Buffers as high as 750MB. Could you explain me the interest to reduce this size ?? I really miss understand this point ... regards, -- Bill Footcow ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Insert are going slower ...
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... -- Bill Footcow ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] vacuum full 100 mins plus?
Should I be concerned that my vacuum process has taken upwards of 100 + minutes to complete? I dropped all indexes before starting and also increased the vacuum_mem before starting. Looking at the output below, it appears that a vacuum full hasn't been done on this table for quite sometime. Would I be better off exporting the data vacuuming the table and reimporting the data? I cannot drop the table do to views attached to the table mdc_oz=# set vacuum_mem = 10240; SET mdc_oz=# vacuum full verbose cdm.cdm_ddw_Tran_item; INFO: vacuuming cdm.cdm_ddw_tran_item INFO: cdm_ddw_tran_item: found 15322404 removable, 10950460 nonremovable row versions in 934724 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 233 to 308 bytes long. There were 1081 unused item pointers. Total free space (including removable row versions) is 4474020460 bytes. 544679 pages are or will become empty, including 0 at the end of the table. 692980 pages containing 4433398408 free bytes are potential move destinations. CPU 29.55s/4.13u sec elapsed 107.82 sec. TIA Patrick Hatcher ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum full 100 mins plus?
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. Patrick Hatcher [EMAIL PROTECTED] omTo Sent by: [EMAIL PROTECTED] pgsql-performance cc [EMAIL PROTECTED] .org Subject [PERFORM] vacuum full 100 mins plus? 07/14/2004 02:29 PM Should I be concerned that my vacuum process has taken upwards of 100 + minutes to complete? I dropped all indexes before starting and also increased the vacuum_mem before starting. Looking at the output below, it appears that a vacuum full hasn't been done on this table for quite sometime. Would I be better off exporting the data vacuuming the table and reimporting the data? I cannot drop the table do to views attached to the table mdc_oz=# set vacuum_mem = 10240; SET mdc_oz=# vacuum full verbose cdm.cdm_ddw_Tran_item; INFO: vacuuming cdm.cdm_ddw_tran_item INFO: cdm_ddw_tran_item: found 15322404 removable, 10950460 nonremovable row versions in 934724 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 233 to 308 bytes long. There were 1081 unused item pointers. Total free space (including removable row versions) is 4474020460 bytes. 544679 pages are or will become empty, including 0 at the end of the table. 692980 pages containing 4433398408 free bytes are potential move destinations. CPU 29.55s/4.13u sec elapsed 107.82 sec. TIA Patrick Hatcher ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Odd sorting behaviour
On Thu, Jul 08, 2004 at 12:19:13PM +0200, Steinar H. Gunderson wrote: I'm trying to find out why one of my queries is so slow -- I'm primarily using PostgreSQL 7.2 (Debian stable), but I don't really get much better performance with 7.4 (Debian unstable). My prototype table looks like this: I hate to nag, but it's been a week with no reply; did anybody look at this? Is there any more information I can supply to make it easier? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum full 100 mins plus?
Patrick, 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. Yeah. If you've a table that's not been vacuumed in a month, it's often faster to clean it out and import it. I've seen vacuums take up to 3 hours in really bad cases. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Odd sorting behaviour
Steinar, - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? And why would it take ~100ms to sort the rows at all? (In earlier tests, this was _one full second_ but somehow that seems to have improved, yet without really improving the overall query time. I'm puzzled by the 83792 rows as well. I've a feeling that Explain Analyze is failing to output a step. - Why does it use uid_index for an index scan on the table, when it obviously has no filter on it (since it returns all the rows)? In order to support the merge join. It should be a bit faster to do the sort using the index than the actual table. Also, because you pass the 0 condition. Furthermore, why would this take half a second? (The machine is a 950MHz machine with SCSI disks.) I don't see half a second here. - Also, the outer sort (the sorting of the 58792 rows from the merge join) is slow. :-) I don't see a sort after the merge join. Which version are we talking about? I'm looking at the 7.4 version because that outputs more detail. Most of your time is spent in that merge join. Why don't you try doubling sort_mem temporarily to see how it does? Or even raising shared_buffers? -- -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] vacuum full 100 mins plus?
A long time ago, in a galaxy far, far away, [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 -- output = reverse(gro.gultn @ enworbbc) http://www3.sympatico.ca/cbbrowne/spreadsheets.html Signs of a Klingon Programmer #6: Debugging? Klingons do not debug. Our software does not coddle the weak. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] vacuum full 100 mins plus?
Christopher Browne [EMAIL PROTECTED] writes: A long time ago, in a galaxy far, far away, [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. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd sorting behaviour
Josh Berkus [EMAIL PROTECTED] writes: - The subquery scan o12 phase outputs 1186 rows, yet 83792 are sorted. Where do the other ~82000 rows come from? I'm puzzled by the 83792 rows as well. I've a feeling that Explain Analyze is failing to output a step. 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. 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. Most of your time is spent in that merge join. Why don't you try doubling sort_mem temporarily to see how it does? Or even raising shared_buffers? 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. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
