[GENERAL] postgresql performace degrading after a while
Hi All, I seem to have a problem with postgresql 7.4.2 running on Red Hat Enterprise Linux ES3. I am running an application server over a database in which tables contains at most a few thousands of records. The problematic table, however, contains 67 records, with an application daemon updating all those records every 30 seconds. Everything looks fine as the application goes up, however as it keeps running, after 1-1.5 days the postgresql connections start consuming 100% of the machine's CPU in kernel mode. When I come to this point, restart of postgresql, the application and even the entire machine does not help, as it continues to consume 100% of the CPU immediately after going up. Nevertheless, I have negated hardware issues/ application bug as I discovered that when creating a dump of all application data using the pg_dump utility, then recreating the database using dropdb and createdb and finally restoring from the dump, I manage to return to the start point, i.e. everything works fine and I gain more 1-1.5 days of work. Can anyone have an idea of what I should do to avoid recreating the DB every day? Thanks in Advance.
Re: [GENERAL] postgresql performace degrading after a while
First of all thanks for you quick and efficient response. Indeed I forgot to mention that I AM vacuuming the database using a daemon every few hours; however this seems not to be the issue this time, as when the CPU consumptions went up I tried to vacuum manually and this seemed to take no affect. As for the version, I am aware of the most recent versions and the next version of the application will be certified with postgresql 8.1.x, however the behavior I mentioned occurred on a live site in which making changes is not simple. I will do it if I have no choice but I would be happy if there is any workaround to hold the server for a while until the ordinary upgrade occurs. Thanks again. -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Monday, January 30, 2006 9:17 AM To: Ron Marom Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql performace degrading after a while On Mon, Jan 30, 2006 at 09:02:24AM +0200, Ron Marom wrote: > I seem to have a problem with postgresql 7.4.2 running on Red Hat > Enterprise Linux ES3. If you can't upgrade to 8.0 or 8.1 then at least consider using the latest version in the 7.4 branch (7.4.11). You're missing almost two years of bug fixes. http://www.postgresql.org/docs/7.4/static/release.html > I am running an application server over a database in which tables > contains at most a few thousands of records. The problematic table, > however, contains 67 records, with an application daemon updating > all those records every 30 seconds. Are you vacuuming that table frequently? If not then it's accumulating a lot of dead tuples, which would cause performance to degrade. Are you vacuuming at all? See "Routine Database Maintenance Tasks" in the documentation for an explanation of what it is and why it's necessary. http://www.postgresql.org/docs/7.4/static/maintenance.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
