Thread: Dying PostgreSQL backend

Dying PostgreSQL backend

From
"otisg"
Date:
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.

Re: Dying PostgreSQL backend

From
Tom Lane
Date:
"otisg" <otisg@iVillage.com> writes:
> What gets logged in syslog log file are things like this:

The messages you quoted are no help, because none of them directly
show the crashing backend.

What I'd recommend doing is gdb'ing the core dump file to get a stack
trace; that would give us some clue what's wrong.  And you could do
"p debug_query_string" to see what query made it crash.

If you do not find a core file in the database subdirectory
($PGDATA/base/yourdbnumber/core) then you are probably running the
postmaster with "ulimit -c 0" which disables core dumps.  Restart
it with environment "ulimit -c unlimited".

            regards, tom lane

Re: Dying PostgreSQL backend

From
"otisg"
Date:
Hello,

Unfortunately, I cannot get PostgreSQL to dump core despite setting ulimit -c to:
[root@linux2 otis]# ulimit -a
core file size (blocks) unlimited

And starting PostgreSQL this way:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start"

The backend doesn't want to dump core, it just dies and restarts, dies and restarts.
I am really running only 2 very similar UPDATE statements. They update the same table, changing only a few fields.

I tried running VACUUM ANALYZE more frequently, but that didn't make any difference.

Is there any other way that I can get more information out of postgres backend?

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.


<-----Original Message----->
>
> From: Tom Lane
> Sent: 5/6/2002 12:47:31 PM
> To: otisg@iVillage.com
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Dying PostgreSQL backend
>
> "otisg" writes:
> What gets logged in syslog log file are things like this:
>
> The messages you quoted are no help, because none of them directly
> show the crashing backend.
>
> What I'd recommend doing is gdb'ing the core dump file to get a stack
> trace; that would give us some clue what's wrong. And you could do
> "p debug_query_string" to see what query made it crash.
>
> If you do not find a core file in the database subdirectory
> ($PGDATA/base/yourdbnumber/core) then you are probably running the
> postmaster with "ulimit -c 0" which disables core dumps. Restart
> it with environment "ulimit -c unlimited".
>
> regards, tom lane
>

Re: Dying PostgreSQL backend

From
Tom Lane
Date:
"otisg" <otisg@iVillage.com> writes:
> Unfortunately, I cannot get PostgreSQL to dump core despite setting
> ulimit -c to:
> [root@linux2 otis]# ulimit -a
> core file size (blocks) unlimited

> And starting PostgreSQL this way:
> su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p
> /usr/bin/postmaster start"

Most likely, the su is picking up your system default of ulimit -c 0
again :-(.  I'd suggest modifying the pg_ctl script to explicitly set
ulimit -c before it starts the postmaster.

> I am really running only 2 very similar UPDATE statements.

Well, I'd like to look into it; it's not supposed to be that easy to
make PG dump core ;-).  But I need information.

            regards, tom lane

Re: Dying PostgreSQL backend

From
"otisg"
Date:
Hello,

> What I'd recommend doing is gdb'ing the core dump file to get a stack
> trace; that would give us some clue what's wrong. And you could do
> "p debug_query_string" to see what query made it crash.
>
> If you do not find a core file in the database subdirectory
> ($PGDATA/base/yourdbnumber/core) then you are probably running the
> postmaster with "ulimit -c 0" which disables core dumps. Restart
> it with environment "ulimit -c unlimited".

Yes, I fixed that.

Unfortunately I can't gdb to look into the core file:
(gdb) p debug_query_string
No symbol table is loaded. Use the "file" command.
(gdb) file /tmp/core4
"/tmp/core4": not in executable format: File format not recognized
(gdb)

Any ideas?

I did, however, see this in the syslogd log at the time of core dump:

/usr/bin/postmaster child[4014]: starting with (postgres -d16 -v131072 -p mydb )
invoking IpcMemoryCreate(size=2015232)

The first line I had seen before (every few seconds, since my application makes 5-7 updates per second), but never the second one. This was the first time.
Maybe it means something to those familiar with the guts of PostgreSQL.

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.