Обсуждение: Suspending postgresql during heavy load

Question is their a way to suspend PostGres during a heavy average load
session?

I would like to suspend Postgresql transaction on a load average higher
then a load average of 5.

--
Member - Liberal International This is doctor@@nl2k.ab.ca Ici doctor@@nl2k.ab.ca
God,Queen and country!Never Satan President Republic!Beware AntiChrist rising!
http://www.fullyfollow.me/rootnl2k  Look at Psalms 14 and 53 on Atheism
Truth is a gaffe to liars.  -unknown

On Tue, Oct 7, 2014 at 9:07 AM, The Doctor <doctor@doctor.nl2k.ab.ca> wrote:
> Question is their a way to suspend PostGres during a heavy average load
> session?
>
> I would like to suspend Postgresql transaction on a load average higher
> then a load average of 5.

So you just want to stop the db under heavy load? I'm not sure what
you're asking here. PostgreSQL is designed around a transactional
model, it can't just be shut off at your convenience.

OTOH you might want to look at how you're using it and what kind of
hardware you're using and if you need to upgrade it.

On Tue, Oct 07, 2014 at 09:17:47AM -0600, Scott Marlowe wrote:
> On Tue, Oct 7, 2014 at 9:07 AM, The Doctor <doctor@doctor.nl2k.ab.ca> wrote:
> > Question is their a way to suspend PostGres during a heavy average load
> > session?
> >
> > I would like to suspend Postgresql transaction on a load average higher
> > then a load average of 5.
>
> So you just want to stop the db under heavy load? I'm not sure what
> you're asking here. PostgreSQL is designed around a transactional
> model, it can't just be shut off at your convenience.
>
> OTOH you might want to look at how you're using it and what kind of
> hardware you're using and if you need to upgrade it.

In this case PostGresQL 9.3.5 taking on brute force attacks from
Blog Spammers using Serendity .

I have yused mod_limits to limit the damage from the brute force
wep spam attack, but this is turing the web server off for about 120 s and I would like
to turn away these pests forver.

--
Member - Liberal International This is doctor@@nl2k.ab.ca Ici doctor@@nl2k.ab.ca
God,Queen and country!Never Satan President Republic!Beware AntiChrist rising!
http://www.fullyfollow.me/rootnl2k  Look at Psalms 14 and 53 on Atheism
Truth is a gaffe to liars.  -unknown

The Doctor <doctor@doctor.nl2k.ab.ca> writes:

> Question is their a way to suspend PostGres during a heavy average load
> session?
>
> I would like to suspend Postgresql transaction on a load average higher
> then a load average of 5.

You aren't being very clear if you want to suspend a single backend or
the whole instance (which I'm not sure if feasible).

Individual backends will pause/continue via SIGSTOP SIGCONT.

I've done this under various interesting diagnostic conditions and did
not observe ill effects but there might certainly be some, especially if
you left the session paused for an extended period.

HTH

>
> --
> Member - Liberal International This is doctor@@nl2k.ab.ca Ici doctor@@nl2k.ab.ca
> God,Queen and country!Never Satan President Republic!Beware AntiChrist rising!
> http://www.fullyfollow.me/rootnl2k  Look at Psalms 14 and 53 on Atheism
> Truth is a gaffe to liars.  -unknown

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

On 7 October 2014 23:31, The Doctor <doctor@doctor.nl2k.ab.ca> wrote:
> On Tue, Oct 07, 2014 at 09:17:47AM -0600, Scott Marlowe wrote:
>> On Tue, Oct 7, 2014 at 9:07 AM, The Doctor <doctor@doctor.nl2k.ab.ca> wrote:
>> > Question is their a way to suspend PostGres during a heavy average load
>> > session?
>> >
>> > I would like to suspend Postgresql transaction on a load average higher
>> > then a load average of 5.

If you do want to stop your system being overwhelmed, I'd suggest
putting pgbouncer between your web application and PostgreSQL. You can
use this to limit the number of concurrent connections being
processed, limiting your load and improving overall throughput. If you
are seeing this sort of problem at all, you will likely find you have
hundreds or thousands of open database connections when your hardware
is incapable of processing more than 30 or so connections
simultaneously in an efficient manner.


> In this case PostGresQL 9.3.5 taking on brute force attacks from
> Blog Spammers using Serendity .
>
> I have yused mod_limits to limit the damage from the brute force
> wep spam attack, but this is turing the web server off for about 120 s and I would like
> to turn away these pests forver.

You will still need something on the web end to actually stop the
attack. Nothing you do at the PostgreSQL end can differentiate
attacker requests from genuine user requests. The bots will beat users
any day as bots just keep reloading for ever, while users give up and
go away after too many error pages in a row.



--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/