On 9/13/18 3:59 PM, Chris Williams wrote:
Yeah, I figured that. Unfortunately, every time it happens, I open a
support ticket with them, but they always just tell me that this is
normal behavior for postgres. Whether it's "normal" or not, I really
would like to get my db to shut down faster, and their suggestion of
manually shutting down all of my apps ahead of time is a real headache.
Given that I haven't gotten anywhere with their support, I figured I'd
try asking on the mailing list.
The thing is, what you are doing ("(e.g. reboot, changing instance size,
etc.)") are instance operations not database operations. That comes
under AWS's purview. For what it is worth the behavior is documented:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RebootInstance.html
"The time required to reboot your DB instance depends on the crash
recovery process of your specific database engine. To improve the reboot
time, we recommend that you reduce database activity as much as possible
during the reboot process. Reducing database activity reduces rollback
activity for in-transit transactions.
"
Pretty sure shutting down those 600 connections and uncoupling the
replication(s) goes a long way to the time elapsed.
Thanks,
Chris
On Thu, Sep 13, 2018 at 3:17 PM Joshua D. Drake <jd@xxxxxxxxxxxxxxxxx
<mailto:jd@xxxxxxxxxxxxxxxxx>> wrote:
On 09/13/2018 03:04 PM, Chris Williams wrote:
> Hi,
>
> I'm using AWS RDS Postgres (9.6.6) and have run into very slow
> shutdowns (10+ minutes) a few times when making database
modifications
> (e.g. reboot, changing instance size, etc.). Other times, it shuts
> down quickly (1 minute or so). I have not been able to figure
out why
> sometimes it takes a long time to shutdown.
This is probably something you would have to talk to Amazon about. AWS
RDS Postgres is a fork of PostgreSQL and not 100% compatible from an
administrative perspective.
JD
>
> When it happens, I see a bunch of lines in the postgres log like the
> following over and over (almost every second or two) during this 10
> minute shutdown period:
> 2018-09-12 06:37:01
> UTC:XXX.XXX.XXX.XXX(19712):my_user@my_db:[16495]:FATAL:
> 2018-09-12 06:37:01
> UTC:localhost(31368):rdsadmin@rdsadmin:[16488]:FATAL: the database
> system is shutting down
>
> Once I start seeing these messages, I start manually shutting
down all
> of our applications that are connected to the db. I'm not sure if
> shutting down the apps fixes it or if there's some timeout on the
RDS
> side, but it seems like once I start doing this, the database
finally
> shuts down.
>
> When it takes this long to shut down, it ends up causing a lot more
> downtime than I would like. I've tried asking AWS's support why it
> takes so long to shutdown sometimes, but they basically just told me
> that's "how it works" and that I should try to shut down all of my
> connections ahead of time before making database modifications.
>
> We just have a few ruby on rails applications connected to the
> database, and don't really have any long running or heavy queries
and
> the db is under very light load, so I don't understand why it
takes so
> long to shutdown. We do have a sizeable number of connections
though
> (about 600) and there are two replicas connected to it. I also
tried
> setting idle_in_transaction_session_timeout to 300 seconds to see if
> that would help, but it made no difference.
>
> I was wondering if anyone else had seen this behavior on their RDS
> Postgres instances or had any suggestions on how I could shorten the
> shutdown time?
>
> Thanks,
> Chris
>
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
*** A fault and talent of mine is to tell it exactly how it is. ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
***** Unless otherwise stated, opinions are my own. *****
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
