Re: performance enhancements for PostgreSQL

scott.marlowe wrote:
> On Mon, 18 Nov 2002, Johnson, Shaunn wrote:
>
>>But I'm at the point now that I can't kill some jobs. Yes, I know
>>I shouldn't use 'kill' in any forceful way, but just a kill seems to do
>>nothing (or, if it is doing something, it's not fast enough
>>for the user community and it's stopping production).
>

Kill -9 the postmaster and all postgres processes. This doesn't
solve your problem though.

> You can kill individual backends pretty safely, it's the postmaster you
> can't kill -9 safely.

Feel free to kill -9 the postmaster process. Be sure to also
kill all postgres processes. Shared memory segments won't be
released though.

>>the server is blocking on access to the metadata tables. not even
>>logins are being processed. I'm not sure what caused the
>>problem, but I think a database restart is the best course,
>>which I have been trying to do. Do not kill -9, as it will corrupt the
>>WAL.

False. Feel free to kill -9 the postmaster and all backends.

>
> If you want to shut down the server and it doesn't seem to respond to
> pg_ctl stop, try 'pg_ctl -m fast stop' and see if that works.

> Sounds like processes are hanging, and users are just trying to reconnect
> over and over and you're running out of connections. This is a symptom,
> not the problem, which is your machine is having issues.

Agreed.

>>* how to restart PostgreSQL without running the risk of corrupting data?
>>* what are the benefits to adding a 2nd CPU over, say, more memory?
>
> pg_ctl -m fast stop

If that doesn't work (I doubt it will):

killall -9 postmaster
killall -9 postgres

Killing the postmaster and postgres processes with -9 will not
corrupt your data. A flaky HD/SCSI controller/device driver
might though. The whole point of an ACID compliant database is
that you can yank the power and it will not result in
corruption. I'd guess that postgres processes are not responding
to signals because they are trapped in a kernel call for I/O in
a device driver that is either buggy itself, or isn't robust
enough to handle I/O errors from a bad device - SCSI controller,
HD, etc.

Mike Mascari
mascarm(at)mascari(dot)com