Обсуждение: 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
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 .
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 .
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
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/