Re: [PERFORM] pg_dump issue
Title: RE: [PERFORM] pg_dump issue I did carry it down to the subdirectory level but only included the total for brevity. I'll paste the complete readout at the end of the email. I'll try the "vmstat 1" as you suggest next time the backups run. If the Eng staff finds anything I'll post the results and maybe save someone else some grief if they have the same issue. Thanks again for your input Tom. Tim PROD001 PROD002 220K ./global[PARA]4.0K ./pg_xlog/archive_status[PARA]529M ./pg_xlog[PARA]36K ./pg_clog[PARA]256K ./pg_subtrans[PARA]4.0K ./base/1/pgsql_tmp[PARA]4.8M ./base/1[PARA]4.8M ./base/17229[PARA]4.0K ./base/62878500/pgsql_tmp[PARA]4.8M ./base/62878500[PARA]5.5M ./base/1152695[PARA]4.0K ./base/67708567/pgsql_tmp[PARA]1.6G ./base/67708567[PARA]12K ./base/1157024/pgsql_tmp[PARA]6.3G ./base/1157024[PARA]4.0K ./base/1159370/pgsql_tmp[PARA]543M ./base/1159370[PARA]4.0K ./base/1157190/pgsql_tmp[PARA]164M ./base/1157190[PARA]4.0K ./base/1621391/pgsql_tmp[PARA]81M ./base/1621391[PARA]8.6G ./base[PARA]4.0K ./pg_tblspc[PARA]604K ./pg_log[PARA]9.1G . 220K ./global[PARA]4.0K ./pg_xlog/archive_status[PARA]529M ./pg_xlog[PARA]136K ./pg_clog[PARA]208K ./pg_subtrans[PARA]4.0K ./base/1/pgsql_tmp[PARA]4.9M ./base/1[PARA]4.8M ./base/17229[PARA]5.3M ./base/1274937[PARA]4.0K ./base/64257611/pgsql_tmp[PARA]1.6G ./base/64257611[PARA]4.0K ./base/71683200/pgsql_tmp[PARA]6.1G ./base/71683200[PARA]4.0K ./base/1281929/pgsql_tmp[PARA]478M ./base/1281929[PARA]4.0K ./base/58579022/pgsql_tmp[PARA]154M ./base/58579022[PARA]81M ./base/1773916[PARA]4.0K ./base/55667447/pgsql_tmp[PARA]4.8M ./base/55667447[PARA]8.3G ./base[PARA]4.0K ./pg_tblspc[PARA]588K ./pg_log[PARA]8.8G . -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 30, 2006 12:20 PM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] pg_dump issue "mcelroy, tim" <[EMAIL PROTECTED]> writes: > The du . -h in $PGDATA showed PROD001 at 9.1G and Prod0002 at 8.8G so > they're pretty much the same, one would think the smaller one should be > faster. Yes, the backup files are identical in size. Hmph. You should carry the "du" analysis down to the subdirectory level, eg make sure that it's not a case of lots of pg_xlog bloat balancing out bloat in a different area on the other system. But I suspect you won't find anything. > I'm hoping the Engineering staff can find something system related as I > doubted and still doubt that it's a postgres issue. I tend to agree. You might try watching "vmstat 1" output while taking the dumps, so you could at least get a clue whether the problem is CPU or I/O related ... regards, tom lane
Re: [PERFORM] pg_dump issue
Title: RE: [PERFORM] pg_dump issue Thanks Tom. I have scheduled vacuums as follows and all have run without error. Mon - Thu after-hours: vacuumdb -z -e -a -v On Fridays I add the -f option vacuumdb -z -e -a -v -f The du . -h in $PGDATA showed PROD001 at 9.1G and Prod0002 at 8.8G so they're pretty much the same, one would think the smaller one should be faster. Yes, the backup files are identical in size. BTW - this is postgres 8.0.1. Stuck at this due to "that is the latest postgresql version certified by our vendor's application". I'm hoping the Engineering staff can find something system related as I doubted and still doubt that it's a postgres issue. Tim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 30, 2006 11:16 AM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] pg_dump issue "mcelroy, tim" <[EMAIL PROTECTED]> writes: > I have identical postgres installations running on identical machines. Dual > Core AMD Opteron(tm) Processor 870 , 16GB RAM, Red Hat Linux 3.2.3-20 and > 120GB worth of disk space on two drives. > Recently, I have noticed that my nightly backups take longer on one machine > than on the other. I back up five (5) databases totaling 8.6GB in size. On > Prod001 the backups take app. 7 minutes, on Prod002 the backups take app. 26 > minutes! Quite a discrepancy. Are the resulting backup files identical? Chasing down the reasons for any diffs might yield some enlightenment. One idea that comes to mind is that Prod002 is having performance problems due to table bloat (maybe a missing vacuum cron job or some such). A quick "du" on the two $PGDATA directories to check for significant size differences would reveal this if so. regards, tom lane
[PERFORM] pg_dump issue
Title: pg_dump issue Good morning, I have identical postgres installations running on identical machines. Dual Core AMD Opteron(tm) Processor 870 , 16GB RAM, Red Hat Linux 3.2.3-20 and 120GB worth of disk space on two drives. Recently, I have noticed that my nightly backups take longer on one machine than on the other. I back up five (5) databases totaling 8.6GB in size. On Prod001 the backups take app. 7 minutes, on Prod002 the backups take app. 26 minutes! Quite a discrepancy. I checked myself than checked with our Engineering staff and have been assured that the machines are identical hardware wise, CPU, disk, etc. Question; has anyone run into a similar issue? Here is the command I use for the nightly backup on both machines: pg_dump -F c -f $DB.backup.$DATE $DB Kind of scratching my head on this one Thank you, Tim McElroy
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Ok, thank you all again for your help in this matter. Yes, Michael I (the original poster) did say or imply I guess is a better word for it that a combo of training and hands-on is the best way for one to learn PostgreSQL or just about anything for that matter. Thank you for recognizing the true intention of my statements. One does need some sort of basis from which to grow. I will say that nothing can replace the hands-on real-world training one can get in this business as it is the best way to learn and remember. Just my opinion. For example, I stated I was a SysAdmin for 20 years. I was then thrust into the Oracle world as a DBA about 2 years ago while still maintaining my SysAdmin responsibilities. I have yet to receive any formal Oracle training and have had to learn that on my own via, manuals, Google searches and begging the Oracle Database Architect here for assistance. However, with PostgreSQL I initially started down the very same track but was fortunate enough to receive the ok for that week long PG boot camp. Although I didn't take all that much away from the boot camp it did provide an excellent base from which I continue to grow as a PG DBA and it has helped me to understand postgres a lot easier and quicker than Oracle. So please, lets just not throw emails back-n-forth amongst the group. Since joining I have found the group as a whole to be a great resource of information and PG knowledge and do not want us to get a testy with each other over something I said or someone's interpretation of what I said. Case closed. BTW - I am still working towards getting the knowledge out here about what I learned form the posts, mainly that the buffers/cache row of information from the free command is the one we need most be concerned with. Thank you, Tim McElroy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Michael Stone Sent: Monday, May 08, 2006 5:17 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? On Mon, May 08, 2006 at 03:38:23PM -0400, Vivek Khera wrote: >On May 8, 2006, at 1:30 PM, Jim C. Nasby wrote: >>>Yeah, I prefer my surgeons to work this way too. training is for the >>>birds. >> >>I think you read too quickly past the part where Tim said he'd >>taking a >>week-long training class. > >s/training/apprenticeship/g; Of course, the original poster did say that hands-on was the best way to learn. What is apprenticeship but a combination of training and experience. Are you just sniping for fun? Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Thank you again to all who have offered advice, suggestions, tips and offers of support/training. From the gist of some of the latter posts I must come off as a rank rookie, lol. Deservedly so as I've only been working with postgres for 7 months and in the linux/unix world a year or so. My background is Stratus SysAdmin (which I still do in addition to DBA) so the transition is an on-going process. That said, at this time I'll put the thread to rest as my company just doubled the memory to 16GB, isn't that how it always works out anyway ;) I'll also be moving the new postgresql.conf settings that were worked out with the patient help of Jim Nasby, thanks again Jim. The DEV box I put those on has shown some improvement. As far as outside support and training, thank you but no. Probably doesn't show but I did attend a week long PostgreSQL boot camp in January (which I found aimed more to the development side than DBA by the way), but there is no better way to learn and understand better than actual day-to-day working experience. Thank you, Tim McElroy -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED]] Sent: Friday, May 05, 2006 8:35 PM To: mcelroy, tim Cc: 'Michael Stone'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote: > Sorry, been up all night and maybe provided too much information or not the Do you have any budget for support or training, either from the company selling you the app or a company that provides PostgreSQL support? I suspect some money invested there would result in a lot less frustration. It'd also certainly be cheaper than switching to Oracle. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Thanks for a great explanation Craig, makes more sense now. Tim -Original Message- From: Craig A. James [mailto:[EMAIL PROTECTED]] Sent: Friday, May 05, 2006 10:51 AM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? mcelroy, tim wrote: > Sorry, been up all night and maybe provided too much information or not > the right information and only confused folks, tired I guess. When I > say 'in use' I am referring to the 'used' column. Thanks all who have > responded to this inquiry, I appreciate it. > > Here's free from PROD001: > [EMAIL PROTECTED] kernel]# free -k -t > total used free shared buffers cached > Mem: 7643536 6975772 667764 0 165496 5393396 > -/+ buffers/cache: 1416880 6226656 > Swap: 8185108 5208 8179900 > Total: 15828644 6980980 8847664 On Linux (unlike most Unix systems), "used" includes both processes AND the kernel's file-system buffers, which means "used" will almost always be close to 100%. Starting with a freshly-booted system, you can issue almost any command that scans files, and "used" will go up and STAY at nearly 100% of memory. For example, reboot and try "tar cf - / >/dev/null" and you'll see the same sort of "used" numbers. In My Humble Opinion, this is a mistake in Linux. This confuses just about everyone the first time they see it (including me), because the file-system buffers are dynamic and will be relenquished by the kernel if another process needs memory. On Unix systems, "used" means, "someone else is using it and you can't have it", which is what most of us really want to know. Craig
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Thanks Michael. Are you saying the 'used' column is the irrelevant number? Is the number that is more pertinent is 1416880? Is that the actual amount of memory in use? I agree about the allocation of a bogus amount of memory but the issue occurred after-hours when the application(s) were not running. Or are you saying the app whacked the DB during the day and never recovered? Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Michael Stone Sent: Friday, May 05, 2006 10:41 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote: >Sorry, been up all night and maybe provided too much information or not the >right information and only confused folks, tired I guess. When I say 'in >use' I am referring to the 'used' column. Which is a mostly irrelevant number. >Here's free from PROD001: >[EMAIL PROTECTED] kernel]# free -k -t > total used free shared buffers cached >Mem: 7643536 6975772 667764 0 165496 5393396 >-/+ buffers/cache: 1416880 6226656 >Swap: 8185108 5208 8179900 >Total: 15828644 6980980 8847664 You've got 1.4G in use, 5.3G of disk cache, 165M of buffers and 667M free. That doesn't seem unreasonable. If an application needs more memory the amount of disk cache will decrease. As I said in an earlier email, the problem is that the application is trying to allocate a bogus amount of memory, not that you have a memory problem. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Memory and/or cache issues?
Title: Message On the boxes in question the settings are: shared_buffers = 1000 work_mem = 1024 I have revised these on my DEV box and see some improvement (a quick thank you to Jim Nasby for his assistance with that): shared_buffers = 2 work_mem = 8024 Regards, Tim -Original Message- From: Dave Dutcher [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 10:32 AM To: 'mcelroy, tim' Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Memory and/or cache issues? For a standard config most of the memory used by Postgres is the shared buffers. The shared buffers are a cache to store blocks read from the disk, so if you do a query, Postgres will allocate and fill the shared buffers up to the max amount you set in your postgresql.conf file. Postgres doesn't release that memory between queries because the point is to be able to pull data from ram instead of the disk on the next query. Are you sure your settings in postgresql.conf are standard? What are your settings for shared_buffers and work_mem? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of mcelroy, tim Sent: Friday, May 05, 2006 8:58 AM To: 'Tom Lane' Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? Are you saying the kernel's disc cache may be getting whacked? No, I understand that PG should use as much memory as it can and the system as well. The main problem here is that with almost all the 8GB of RAM 'in use' when I try to do a pg_dump or vacuumdb I run out of memory and the system crashes I well understand that unused memory is not a good thing, just that when you have none and can't do the maint workbad stuff happens. For example, I just created a benchdb on my DEV box with 1,000,000 tuples. As this ran the mem in use jumped up 1G and it hasn't gone down? Once the PG process has finished its task shouldn't it release the memory it used? Thanks, Tim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, May 05, 2006 9:44 AM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? "mcelroy, tim" <[EMAIL PROTECTED]> writes: > I see this on all the postgres installations, no matter what I > set the postgresql.conf settings to regarding memory allocation, once > postgres starts up 95% of the memory on the box is used. Is there a way > within Linux to 'see' what or who is actually using this memory? Probably kernel disk cache. Are you under the misimpression that unused memory is a good thing? If a Unix-ish system *isn't* showing near zero free memory under load, the kernel is wasting valuable resources. regards, tom lane
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Sorry, been up all night and maybe provided too much information or not the right information and only confused folks, tired I guess. When I say 'in use' I am referring to the 'used' column. Thanks all who have responded to this inquiry, I appreciate it. Here's free from PROD001: [EMAIL PROTECTED] kernel]# free -k -t total used free shared buffers cached Mem: 7643536 6975772 667764 0 165496 5393396 -/+ buffers/cache: 1416880 6226656 Swap: 8185108 5208 8179900 Total: 15828644 6980980 8847664 Here's free from PROD002: [EMAIL PROTECTED] root]# free -k -t total used free shared buffers cached Mem: 7643536 6694220 949316 0 161008 4916420 -/+ buffers/cache: 1616792 6026744 Swap: 8185108 11584 8173524 Total: 15828644 6705804 9122840 Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Michael Stone Sent: Friday, May 05, 2006 10:24 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? On Fri, May 05, 2006 at 09:57:58AM -0400, mcelroy, tim wrote: >Are you saying the kernel's disc cache may be getting whacked? No, I >understand that PG should use as much memory as it can and the system as >well. The main problem here is that with almost all the 8GB of RAM 'in use' >when I try to do a pg_dump or vacuumdb I run out of memory and the system >crashes You need to be way more specific about what "in use" means. Try pasting the output of actual commands like "free". The main problem here according to the output you sent is that your process is trying to allocate 10billion terabytes of RAM (which ain't gonna work) and dies. That is not a memory issue. Mike Stone ---(end of broadcast)--- TIP 1: 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] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Are you saying the kernel's disc cache may be getting whacked? No, I understand that PG should use as much memory as it can and the system as well. The main problem here is that with almost all the 8GB of RAM 'in use' when I try to do a pg_dump or vacuumdb I run out of memory and the system crashes I well understand that unused memory is not a good thing, just that when you have none and can't do the maint workbad stuff happens. For example, I just created a benchdb on my DEV box with 1,000,000 tuples. As this ran the mem in use jumped up 1G and it hasn't gone down? Once the PG process has finished its task shouldn't it release the memory it used? Thanks, Tim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, May 05, 2006 9:44 AM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? "mcelroy, tim" <[EMAIL PROTECTED]> writes: > I see this on all the postgres installations, no matter what I > set the postgresql.conf settings to regarding memory allocation, once > postgres starts up 95% of the memory on the box is used. Is there a way > within Linux to 'see' what or who is actually using this memory? Probably kernel disk cache. Are you under the misimpression that unused memory is a good thing? If a Unix-ish system *isn't* showing near zero free memory under load, the kernel is wasting valuable resources. regards, tom lane
Re: [PERFORM] Memory and/or cache issues?
Title: RE: [PERFORM] Memory and/or cache issues? Thanks Tom. I thought the same thing and waded through the archives trying various fixes such as vacuum, vacuum full (both with analyze), reindex and still the same issue. However, once the box was rebooted the backup went smooth and the data was fine. We have two (2) machines (PROD001 & PROD002) that are "in-sync" and the data matched exactly. PROD002 was where I had the problem. I see this on all the postgres installations, no matter what I set the postgresql.conf settings to regarding memory allocation, once postgres starts up 95% of the memory on the box is used. Is there a way within Linux to 'see' what or who is actually using this memory? I would love to say it's a hardware thing and that postgres is fine :) Regards, Tim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, May 05, 2006 9:25 AM To: mcelroy, tim Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Memory and/or cache issues? "mcelroy, tim" <[EMAIL PROTECTED]> writes: > pg_dump: ERROR: invalid memory alloc request size 18446744073709551613 That looks more like a corrupt-data problem than anything directly to do with having or not having enough memory. regards, tom lane
[PERFORM] Memory and/or cache issues?
Title: Memory and/or cache issues? Good morning, First the stats: I'm using PostgreSQL 8.0.1 (I know I should upgrade, cannot due to vendor app. restrictions...), RedHat 9 on a SUN V40Z with 8GB of memory. I'm using the "out-of-the-box" settings in postgresql.conf. I've been testing various changes but cannot increase anything to improve performance till I get this memory leak and/or cache issue resolved. Scenario: Last night the backup of my largest DB failed (4.4GB in size with 44Million+ tuples) with a memory alloc error. I'll attach it at the end of this email. Once we rebooted the box and freed memory all was well, the backup completed fine but as the backup ran and I did a few minor queries all of a sudden 3+GB of memory was used up! I then performed my nightly vacuumdb with analyze and just about the remaining 4GB of memory was gone! This was the only application running in the machine at the time. Questions: 1. I thought using such "smallish" setting as provided would cause postgres to go to swap instead of eating up all the memory? 2. If PostgreSQL is the culprit (which I hope it is not) does postgres release any memory it assumes during processing when that processing is complete? Such as the backup and vacuumdb I mentioned? 3. Does anyone know of a way to determine if it actually is postgres hogging this memory? Using TOP I only see my postgres processes using 1% or 2% of memory. It would be nice to have a tool that showed exactly what is eating up that 7+GB? 4. IS this due to my low setting in postgresql.conf? Any and all help is welcomed. For you PostgreSQL purists out there of whom I am fast becoming, your help is needed as my company is considering dumping postgresql in favor of Oracle.I would much rather figure out the issue then switch DBs. Here is the error received from the failed backup and the second was noted in my pg_log file: pg_dump: ERROR: invalid memory alloc request size 18446744073709551613 pg_dump: SQL command to dump the contents of table "msgstate" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613 pg_dump: The command was: COPY public.msgstate (id, connectormsgid, parentid, orderidfk, clordid, orgclordid, msg, rawmsg, msgtype, "action", sendstate, statechain, fromdest, todest, inserted, op_id, released, reason, outgoing, symbol, qty, price, stopprice, side, data1, data2, data3, data4, data5) TO stdout; 2006-05-04 18:04:58 EDT USER=postgres DB=FIX1 [12427] PORT = [local] ERROR: invalid memory alloc request size 18446744073709551613 Thank you, Tim McElroy
Re: [PERFORM] Vacuum template databases, Urgent: Production probl
Title: RE: [PERFORM] Vacuum template databases, Urgent: Production probl Humm, well I am running 8.0.1 and use that option and see the following in my vacuum output log: vacuumdb: vacuuming database "template1" So I would assume that it is being vacuumed? Maybe I'm wrong. If so, we should be upgrading soon and it won't be an issue. Thanks, Tim -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 14, 2006 4:17 PM To: mcelroy, tim Cc: 'Tom Lane'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum template databases, Urgent: Production probl On Tue, Mar 14, 2006 at 12:28:17PM -0500, mcelroy, tim wrote: > If one adds the '-a' arg to vacuumdb wouldn't that vacuum all databases > including template1? It does on 8.1... [EMAIL PROTECTED]:15]~:18%vacuumdb -va | & grep template1 vacuumdb: vacuuming database "template1" [EMAIL PROTECTED]:16]~:19% Try it and find out. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [PERFORM] Vacuum template databases, Urgent: Production probl
Title: RE: [PERFORM] Vacuum template databases, Urgent: Production problem If one adds the '-a' arg to vacuumdb wouldn't that vacuum all databases including template1? Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Tom Lane Sent: Tuesday, March 14, 2006 12:28 PM To: Pallav Kalva Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum template databases, Urgent: Production problem Pallav Kalva <[EMAIL PROTECTED]> writes: > Do we have to vacuum template0 database regularly ? No, and in fact you can't because it's marked not datallowconn. But you do need to vacuum template1 and usps every now and then. regards, tom lane ---(end of broadcast)--- TIP 1: 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] PG Statistics
Title: RE: [PERFORM] PG Statistics Thanks you Steve. As mentioned in my other reply to Michael Fuhr I'll post the results from tests to be performed this week. Tim -Original Message- From: Steve Poe [mailto:[EMAIL PROTECTED]] Sent: Monday, March 13, 2006 7:38 PM To: mcelroy, tim Cc: 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] PG Statistics Tim, When I have done ODBC load tests with stats_block_level enabled on (20 mins. per test), I've seen about 3-4% performance hit. Your mileage may vary. Steve Poe On Mon, 2006-03-13 at 18:49 -0500, mcelroy, tim wrote: > Good evening, > > Does anyone know how much of a performance hit turning > stats_block_level and stats_row_level on will incur? Do both need to > be on to gather cache related statistics? I know the > annotated_conf_80 document states to only turn them on for debug but > if they're not that performance intensive I cannot see the harm. > > Thank you, > Tim McElroy >
Re: [PERFORM] PG Statistics
Title: RE: [PERFORM] PG Statistics Thank you for the insight Michael. I'll be performing some tests with the various setting on/off this week and will post the results. Tim -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED]] Sent: Monday, March 13, 2006 7:19 PM To: mcelroy, tim Cc: 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] PG Statistics On Mon, Mar 13, 2006 at 06:49:39PM -0500, mcelroy, tim wrote: > Does anyone know how much of a performance hit turning stats_block_level and > stats_row_level on will incur? Do both need to be on to gather cache > related statistics? I know the annotated_conf_80 document states to only > turn them on for debug but if they're not that performance intensive I > cannot see the harm. I ran some tests a few months ago and found that stats_command_string had a significant impact, whereas stats_block_level and stats_row_level were almost negligible. Here are my test results: http://archives.postgresql.org/pgsql-performance/2005-12/msg00307.php Your results may vary. If you see substantially different results then please post the particulars. -- Michael Fuhr
[PERFORM] PG Statistics
Title: PG Statistics Good evening, Does anyone know how much of a performance hit turning stats_block_level and stats_row_level on will incur? Do both need to be on to gather cache related statistics? I know the annotated_conf_80 document states to only turn them on for debug but if they're not that performance intensive I cannot see the harm. Thank you, Tim McElroy
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % Sorry I realized your fears :) PostgreSQL is a new (last four months) install here and I'm responsible for it. Great DB and I enjoy working with it a lot and learning the nuances of it. Keep in mind that the management are 'old-time' system folks who love charts showing system and in this case DB performance. I'm basically just using the out-of-the-box defaults in my postgresql.conf file and that seems to be working so far. But as the DB grows I just need a way to prove the DB is functioning properly when apps get slow. You know the old you're guilty till proven innocent syndrome Ok enough on that. Yes, thank you we try to keep on the ball regarding system monitoring. BTW - I'm still waiting to see if anyone out there can say yea or nay if the SQL I wrote is a valid indicator of overall cache % hit? > SELECT 100 - round((blks_hit::numeric / (blks_hit::numeric + > blks_read::numeric)) * 100,2) > AS "Cache % Hit" > FROM pg_stat_database > WHERE datname = 'Fix1'; > > > > Cache % Hit > > 98.06 > (1 row) Thank you, Tim -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 08, 2006 10:24 PM To: mcelroy, tim Cc: 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] pg_reset_stats + cache I/O % On Wed, Mar 08, 2006 at 01:35:35PM -0500, mcelroy, tim wrote: > I actually need this info as I was tasked by management to provide it. Not > sure if they understand that or not, I do but management does like to see > how well the system and its components are performing. Also, I would > utilize these results to test any cache tuning changes I may make. What I feared. While monitoring cache hit % over time isn't a bad idea, it's less than half the picture, which makes fertile ground for optimizing for some mythical target instead of actual system performance. If the "conclusion" from these numbers is that shared_buffers needs to get set larger than min(5, 10% of memory) I'd very seriously re-consider how performance tuning is being done. But hopefully I'm just being paranoid and you guys are just doing a great job of monitoring things and keeping on the ball. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % I actually need this info as I was tasked by management to provide it. Not sure if they understand that or not, I do but management does like to see how well the system and its components are performing. Also, I would utilize these results to test any cache tuning changes I may make. Tim -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 08, 2006 1:28 PM To: mcelroy, tim Cc: 'Tom Lane'; 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] pg_reset_stats + cache I/O % Out of curiosity, why do you want this info? More important, do the folks who are looking at this understand that a key part of PostgreSQL's tuning strategy is to let the OS handle the bulk of the caching? On Wed, Mar 08, 2006 at 08:59:51AM -0500, mcelroy, tim wrote: > Thanks Tom, sorry I neglected to copy the list on my previous email. > > Does this query make sense and is it valid for an accurate cache % hit ratio > for the entire DB? I would assume I could use the same logic with other > views such as pg_stat_user_tables to get a per table ratio? > > SELECT 100 - round((blks_hit::numeric / (blks_hit::numeric + > blks_read::numeric)) * 100,2) > AS "Cache % Hit" > FROM pg_stat_database > WHERE datname = 'Fix1'; > > > > Cache % Hit > > 98.06 > (1 row) > > Thank you, > Tim > > -Original Message----- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 07, 2006 2:37 PM > To: mcelroy, tim > Cc: 'pgsql-performance@postgresql.org' > Subject: Re: [PERFORM] pg_reset_stats + cache I/O % > > "mcelroy, tim" <[EMAIL PROTECTED]> writes: > > ERROR: function round(double precision, integer) does not exist > > Try coercing to numeric instead of float. Also, it'd be a good idea to > put that coercion outside the sum()'s instead of inside --- summing > bigints is probably noticeably faster than summing numerics. > > regards, tom lane -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % Thanks Tom, sorry I neglected to copy the list on my previous email. Does this query make sense and is it valid for an accurate cache % hit ratio for the entire DB? I would assume I could use the same logic with other views such as pg_stat_user_tables to get a per table ratio? SELECT 100 - round((blks_hit::numeric / (blks_hit::numeric + blks_read::numeric)) * 100,2) AS "Cache % Hit" FROM pg_stat_database WHERE datname = 'Fix1'; Cache % Hit 98.06 (1 row) Thank you, Tim -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 07, 2006 2:37 PM To: mcelroy, tim Cc: 'pgsql-performance@postgresql.org' Subject: Re: [PERFORM] pg_reset_stats + cache I/O % "mcelroy, tim" <[EMAIL PROTECTED]> writes: > ERROR: function round(double precision, integer) does not exist Try coercing to numeric instead of float. Also, it'd be a good idea to put that coercion outside the sum()'s instead of inside --- summing bigints is probably noticeably faster than summing numerics. regards, tom lane
[PERFORM] pg_reset_stats + cache I/O %
Title: pg_reset_stats + cache I/O % Good afternoon, Relatively new to PostgreSQL and have been assigned the task of capturing cache I/O % hits. I figured out (thanks to other posts) how to turn on the capture and what stats to (kind of) capture. I did find a view in the archives as shown below but it does not execute, error follows. I'm using 8.0.1 so that shouldn't be the issue. Any help will be appreciated. Also, I also found pg_reset_stats.tar.gz in the archives with a lot of talk regarding its addition as a patch, did it ever make it in? If not, can I get a copy of it somewhere? The tar.gz gets corrupted when I download it. Thank you, Tim CREATE VIEW cache_hits AS SELECT relname, ROUND(CASE WHEN heap_blks_hit = 0 THEN 0 ELSE ((heap_blks_hit::float /(heap_blks_read::float + heap_blks_hit::float)) * 100) END ,2) as heap, ROUND(CASE WHEN idx_blks_hit = 0 THEN 0 ELSE ((idx_blks_hit::float /(idx_blks_read::float + idx_blks_hit::float)) * 100) END,2) as index,ROUND(CASE WHEN toast_blks_hit = 0 THEN 0 ELSE ((toast_blks_hit::float /(toast_blks_read::float + toast_blks_hit::float)) * 100) END,2) as toast FROM pg_statio_user_tables WHERE heap_blks_read <> 0 or idx_blks_read <> 0 OR toast_blks_read <> 0 union select 'ALL TABLES', ROUND(CASE WHEN sum(heap_blks_hit) = 0 THEN 0 ELSE ((sum(heap_blks_hit::float) /(sum(heap_blks_read::float) + sum(heap_blks_hit::float))) * 100) END ,2) as heap, ROUND(CASE WHEN sum(idx_blks_hit) = 0 THEN 0 ELSE ((sum(idx_blks_hit::float) /(sum(idx_blks_read::float) + sum(idx_blks_hit::float))) * 100) END,2) as index,ROUND(CASE WHEN sum(toast_blks_hit) = 0 THEN 0 ELSE ((sum(toast_blks_hit::float) /(sum(toast_blks_read::float) + sum(toast_blks_hit::float))) * 100) END,2) as toast FROM pg_statio_user_tables HAVING sum(heap_blks_read) <> 0 or sum(idx_blks_read) <> 0 OR sum(toast_blks_read) <> 0 ; ERROR: function round(double precision, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.
