Hello,
I am having problems keeping PostgreSQL alive in certain situations and I wonder if I can tune some parameters to allow it to handle higher load.
What gets logged in syslog log file are things like this:
May 5 21:52:34 localhost postgres[25012]: [64] NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
May 5 21:52:34 localhost postgres[25013]: [61-1] NOTICE: Message from PostgreSQL backend:
May 5 21:52:34 localhost postgres[25013]: [61-2] ^IThe Postmaster has informed me that some other backend
May 5 21:52:34 localhost postgres[25013]: [61-3] ^Idied abnormally and possibly corrupted shared memory.
May 5 21:52:34 localhost postgres[25013]: [61-4] ^II have rolled back the current transaction and am
May 5 21:52:34 localhost postgres[25013]: [61-5] ^Igoing to terminate your database system connection and exit.
May 5 21:52:34 localhost postgres[25013]: [61-6] ^IPlease reconnect to the database system and repeat your query.
May 5 21:52:34 localhost postgres[25015]: [61] FATAL 1: The database system is starting up
...
The DB then restarts.
This happens when I hit the DB with a few simple UPDATE statements a second.
The statements are really simple, changing 3 TIMESTAMP, 2 INT, and one VARCHAR field using just the primary key to match the row to update.
I'm executing about 6-8 of those per second.
The DB I have has less than 10 tables, most of which are completely empty, and just one, the one I'm updating has about 12,000 pretty narrow (< 10 columns) rows.
I have run VACUUM ANALYZE on this DB, but once I start my application, which seems to be able to do about 7000-8000 updates before postgreSQL backend starts dying, I do not run VACUUM ANALYZE.
I have already changed a few postgresql.conf parameters:
shared_buffers = 64
sort_mem = 1024
fsync = false
deadlock_timeout = 5000
The UPDATE statements are the only ones running against this DB (except for a simple, no join, straight forward SELECT that runs ev ery 20 seconds).
What other parameters can I change?
Could the problem be that I'm doing a lot of UPDATEs without running VACUUM?
Thanks,
Otis
_______________________________________________________________
Sign up for FREE iVillage newsletters.
From health and pregnancy to shopping and relationships, iVillage
has the scoop on what matters most to you.