pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSuspending postgresql during heavy load
6+ messages / 5 participants
[nested] [flat]
* Suspending postgresql during heavy load
@ 2014-10-07 15:07 The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-07 15:17 ` Re: Suspending postgresql during heavy load Scott Marlowe <scott.marlowe@gmail.com>
2014-10-07 17:32 ` Re: Suspending postgresql during heavy load Jerry Sievers <gsievers19@comcast.net>
2014-10-07 19:50 ` Re: Suspending postgresql during heavy load Shreeyansh dba <shreeyansh2014@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: The Doctor @ 2014-10-07 15:07 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suspending postgresql during heavy load
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
@ 2014-10-07 15:17 ` Scott Marlowe <scott.marlowe@gmail.com>
2014-10-07 16:31 ` Re: Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
2 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2014-10-07 15:17 UTC (permalink / raw)
To: The Doctor <doctor@doctor.nl2k.ab.ca>; +Cc: pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suspending postgresql during heavy load
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-07 15:17 ` Re: Suspending postgresql during heavy load Scott Marlowe <scott.marlowe@gmail.com>
@ 2014-10-07 16:31 ` The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-08 12:14 ` Re: Suspending postgresql during heavy load Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 1 reply; 6+ messages in thread
From: The Doctor @ 2014-10-07 16:31 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suspending postgresql during heavy load
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-07 15:17 ` Re: Suspending postgresql during heavy load Scott Marlowe <scott.marlowe@gmail.com>
2014-10-07 16:31 ` Re: Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
@ 2014-10-08 12:14 ` Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 0 replies; 6+ messages in thread
From: Stuart Bishop @ 2014-10-08 12:14 UTC (permalink / raw)
To: The Doctor <doctor@doctor.nl2k.ab.ca>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
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/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suspending postgresql during heavy load
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
@ 2014-10-07 17:32 ` Jerry Sievers <gsievers19@comcast.net>
2 siblings, 0 replies; 6+ messages in thread
From: Jerry Sievers @ 2014-10-07 17:32 UTC (permalink / raw)
To: The Doctor <doctor@doctor.nl2k.ab.ca>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Suspending postgresql during heavy load
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
@ 2014-10-07 19:50 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shreeyansh dba @ 2014-10-07 19:50 UTC (permalink / raw)
To: The Doctor <doctor@doctor.nl2k.ab.ca>; +Cc: pgsql-admin
First thing is why do you want to suspend Postgres session or stop the
database under heavy load ?
I suspect either your hardware is not sufficient for heavy load
transactions OR your configuration parameters are not set properly to
handle heavy load transactions.
If your hardware is not sufficient the you should upgrade your hardware
ASAP which will give you better performance OR if you have sufficient
hardware you should change some parameters in postgresql.conf for better
performance.
I suggest you should not Suspending/Shutdown server for your convenience
which is not good choice for loosing on-going transactions .
--
Thanks & Regards,
Harshad Adalkonda
Database Administrator
<www.shreeyansh.com>
www.shreeyansh.com
On Tue, Oct 7, 2014 at 8:37 PM, 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.
>
> --
> 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
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-10-08 12:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-07 15:07 Suspending postgresql during heavy load The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-07 15:17 ` Scott Marlowe <scott.marlowe@gmail.com>
2014-10-07 16:31 ` The Doctor <doctor@doctor.nl2k.ab.ca>
2014-10-08 12:14 ` Stuart Bishop <stuart@stuartbishop.net>
2014-10-07 17:32 ` Jerry Sievers <gsievers19@comcast.net>
2014-10-07 19:50 ` Shreeyansh dba <shreeyansh2014@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox