Re: [GENERAL] DB Restart
Sam Jas wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you saying that you're running out of memory overall, or do you have a ulimit issue for the postgres user? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Restart
Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited --- On Thu, 12/11/09, Richard Huxton d...@archonet.com wrote: From: Richard Huxton d...@archonet.com Subject: Re: [GENERAL] DB Restart To: Sam Jas samja...@yahoo.com Cc: general pgsql-general@postgresql.org Date: Thursday, 12 November, 2009, 10:39 AM Sam Jas wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you saying that you're running out of memory overall, or do you have a ulimit issue for the postgres user? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Connect more, do more and share more with Yahoo! India Mail. Learn more. http://in.overview.mail.yahoo.com/
Re: [GENERAL] DB Restart
Sam Jas wrote: Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited Unless you are running on a 32-bit system you should be alright then. What precisely is the problem? What do you mean by the DB hangs? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Restart
We are running on 64 - bit. Whenever the reserved memory in top command it crosses 3.9g it hangs. If we try to kill process (using pg_cancel_backend()) it won't kill. At last either we have to kill all process at OS level or we have to reboot the server. After rebooting server everything worked perfectly fine. BTW we are using postgreSQL 8.3.2. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Thanks Sam --- On Thu, 12/11/09, Richard Huxton d...@archonet.com wrote: From: Richard Huxton d...@archonet.com Subject: Re: [GENERAL] DB Restart To: Sam Jas samja...@yahoo.com Cc: general pgsql-general@postgresql.org Date: Thursday, 12 November, 2009, 12:56 PM Sam Jas wrote: Thanks for your reply. No we are not running OOM. ulimit o/p is as below. [postg...@server1 ~]$ ulimit unlimited Unless you are running on a 32-bit system you should be alright then. What precisely is the problem? What do you mean by the DB hangs? -- Richard Huxton Archonet Ltd The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: [GENERAL] DB Restart
(Please refrain from html email on the list) On Thu, Nov 12, 2009 at 12:10 AM, Sam Jas samja...@yahoo.com wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Are you sure this is what your problem really is? It's quite normal for the bgwriter to show a high res / shr number because it touches all the shared_buffers eventually. On my main db at work, where we have 8G shared_buffers, it looks like this: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 32284 postgres 15 0 8446m 7.5g 7.5g S 0.0 23.8 2:35.10 postgres: writer process 337 postgres 15 0 8475m 7.0g 7.0g S 0.0 22.2 116:15.04 postgres: slony www 10.0.0.104(56186) idle 336 postgres 16 0 8455m 4.7g 4.7g S 4.1 14.8 148:57.29 postgres: slony www 10.0.0.104(56184) COMMIT 335 postgres 18 0 8457m 3.9g 3.9g S 0.0 12.5 598:04.23 postgres: slony www 10.0.0.104(56183) idle Note that the amount of memory used by a process on its own is RES-SHR, so that none of these processes are actually using 7.5, 7.0, 4.7 or 3.9 Gigs by themselves. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Generally this isn't a problem. Is this 100% reproduce-able? Are you sure there's no other problem, or that maybe a checkpoint kicks in at the same time and your hung database isn't just unresponsive for a minute or so? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Restart
On Thu, Nov 12, 2009 at 12:10 AM, Sam Jas samja...@yahoo.com wrote: Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. Oh yeah, how much memory does this machine have on it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Restart
Sam Jas wrote: We are running on 64 - bit. Whenever the reserved memory in top command it crosses 3.9g it hangs. If we try to kill process (using pg_cancel_backend()) it won't kill. At last either we have to kill all process at OS level or we have to reboot the server. After rebooting server everything worked perfectly fine. BTW we are using postgreSQL 8.3.2. PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process Well, this process won't respond to pg_cancel_backend() because it's not a normal backend. It's the background-writer process. Do you see the same problem with ordinary backend processes, or is it always the writer? Also - are you familiar with strace? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Restart
Sam Jas wrote: ... BTW we are using postgreSQL 8.3.2. red flag. 8.3 is up to 8.3.8, lots of updates since 8.3.2 I attach the release notes for these incremental revisions. note there are gobs of fixes in many of these releases.you can upgrade 8.3.2 directly to 8.3.8, with only the caveat that you should reindex any GiST indexes due to a fix in 8.3.5 E.6. Release 8.3.3 *Release date: *2008-06-12 This release contains one serious and one minor bug fix over 8.3.2. For information about new features in the 8.3 major release, see Section E.9 release-8-3.html. E.6.1. Migration to Version 8.3.3 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.6.2. Changes * Make |pg_get_ruledef()| parenthesize negative constants (Tom) Before this fix, a negative constant in a view or rule might be dumped as, say, -42::integer, which is subtly incorrect: it should be (-42)::integer due to operator precedence rules. Usually this would make little difference, but it could interact with another recent patch to cause PostgreSQL to reject what had been a valid SELECT DISTINCT view query. Since this could result inpg_dump output failing to reload, it is being treated as a high-priority fix. The only released versions in which dump output is actually incorrect are 8.3.1 and 8.2.7. * Make ALTER AGGREGATE ... OWNER TO update pg_shdepend (Tom) This oversight could lead to problems if the aggregate was later involved in a DROP OWNED or REASSIGN OWNED operation. E.5. Release 8.3.4 *Release date: *2008-09-22 This release contains a variety of fixes from 8.3.3. For information about new features in the 8.3 major release, see Section E.9 release-8-3.html. E.5.1. Migration to Version 8.3.4 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.5.2. Changes * Fix bug in btree WAL recovery code (Heikki) Recovery failed if the WAL ended partway through a page split operation. * Fix potential use of wrong cutoff XID for HOT page pruning (Alvaro) This error created a risk of corruption in system catalogs that are consulted by VACUUM: dead tuple versions might be removed too soon. The impact of this on actual database operations would be minimal, since the system doesn't follow MVCC rules while examining catalogs, but it might result in transiently wrong output from pg_dump or other client programs. * Fix potential miscalculation of datfrozenxid (Alvaro) This error may explain some recent reports of failure to remove old pg_clog data. * Fix incorrect HOT updates after pg_class is reindexed (Tom) Corruption of pg_class could occur if REINDEX TABLE pg_class was followed in the same session by an ALTER TABLE RENAME orALTER TABLE SET SCHEMA command. * Fix missed combo cid case (Karl Schnaitter) This error made rows incorrectly invisible to a transaction in which they had been deleted by multiple subtransactions that all aborted. * Prevent autovacuum from crashing if the table it's currently checking is deleted at just the wrong time (Alvaro) * Widen local lock counters from 32 to 64 bits (Tom) This responds to reports that the counters could overflow in sufficiently long transactions, leading to unexpected lock is already held errors. * Fix possible duplicate output of tuples during a GiST index scan (Teodor) * Regenerate foreign key checking queries from scratch when either table is modified (Tom) Previously, 8.3 would attempt to replan the query, but would work from previously generated query text. This led to failures if a table or column was renamed. * Fix missed permissions checks when a view contains a simple UNION ALL construct (Heikki) Permissions for the referenced tables were checked properly, but not permissions for the view itself. * Add checks in executor startup to ensure that the tuples produced by an INSERT or UPDATE will match the target table's current rowtype (Tom) This situation is believed to be impossible in 8.3, but it can happen in prior releases, so a check seems prudent. * Fix possible repeated drops during DROP OWNED (Tom) This would typically result in strange errors such as cache lookup failed for relation NNN. * Fix several memory leaks in XML operations (Kris Jurka, Tom) * Fix |xmlserialize()| to raise error properly for unacceptable target data type (Tom) * Fix a couple of places that mis-handled multibyte characters in text search
[GENERAL] DB Restart
Hi, We are facing issue with the RES memory. Below is the o/p of top command. It shows that writer process reserved 3.8g. We have observed that if it increased to 3.9g we need to restart the db. Otherwise it hangs. Kindly suggest us the good way to figure it out this issue. shared_buffer is 3 GB. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4822 postgres 15 0 4045m 3.8g 3.8g S 0.7 12.1 2:09.63 postgres: writer process 18860 postgres 15 0 12868 1276 816 R 0.7 0.0 0:00.43 top 4825 postgres 15 0 86904 2648 660 S 0.3 0.0 0:25.32 postgres: stats collector process 3992 postgres 15 0 88220 1848 1104 S 0.0 0.0 0:00.09 sshd: postg...@pts/11 3993 postgres 15 0 66060 1628 1200 S 0.0 0.0 0:00.02 -bash --Thanks Sam Jas Connect more, do more and share more with Yahoo! India Mail. Learn more. http://in.overview.mail.yahoo.com/
