High WriteLatency RDS Postgres 9.3.20
Hi,
I'm experiencing high WriteLatency levels in a Postgres server 9.3.20
hosted in Amazon RDS. So far it's been almost two months of investigation
and people at AWS technical support don't seem to find the cause. I think
it could be related to Postgres and the number of schema/tables in the
database, that's why I post this issue here.
I have around 4600 schemas, each contains 62 tables. The DB size is only
around 130 GB. the server has plenty of available RAM, CPU usage is less
than 10% and there are only around 16 connections, but WriteLatency is
unusually high.
As I don't have access to the server, I cannot see which are the process
that are wiring to disk, but my guess is that each Postgres process is
writing to disk for some reason.
This issue doesn't seem related to workload. If I restart the server,
WriteLatency drops to normal levels and remains like that until, after some
time (a few hours or a day), without any obvious reason, it spikes again
and continues at high levels since then.
Is it possible that, for some reason, Postgres processes start writing to
disk at some point due to reaching any internal limit? Maybe related to
relcache/catcache/syscache? Any other thoughts?
Thanks
Juan
On 2018-06-18 18:43:06 -0300, Juan Manuel Cuello wrote:
I'm experiencing high WriteLatency levels in a Postgres server 9.3.20
hosted in Amazon RDS.
A lot of performance improvements have been made since 9.3, and it'll
soon-ish be out of support.
If you can reproduce the issue on postgres proper, rather than a
modified version in an environment that precludes getting detailed data,
we might be able to sensibly help you further.
So far it's been almost two months of investigation
and people at AWS technical support don't seem to find the cause. I think
it could be related to Postgres and the number of schema/tables in the
database, that's why I post this issue here.
There've been improvements made since 9.3. Upgrade.
Greetings,
Andres Freund
I would also add that AWS' I/O capabilities are quite poor and expensive. I
assume that you have tried purchasing additional IOOPs on that setup to see
whether you got an expected speed up? If not you should try that as a
diagnostic tool even if you wouldn't want to pay that on an ongoing basis.
We have a client that is I/O write bound and it has taken us significant
efforts to get it to perform well on AWS. We definitely run our own
instances rather than depend on RDS and have always been able to outperform
RDS instances which seem to really be focused to provide a PAAS capability
for developers who really don't want to have to understand how a db works.
Running our identical environment on bare metal is like night & day under
any circumstances when compared to AWS.
Client's requirement is AWS so we keep working on it and we like AWS for
many things but understand it will always underperform on I/O.
Post actual measurements with and without IOOPs or create your own PG
server instance and then people might be able to give you additional
insights.
- - Ben Scherrey
On Tue, Jun 19, 2018, 5:24 AM Andres Freund <andres@anarazel.de> wrote:
Show quoted text
On 2018-06-18 18:43:06 -0300, Juan Manuel Cuello wrote:
I'm experiencing high WriteLatency levels in a Postgres server 9.3.20
hosted in Amazon RDS.A lot of performance improvements have been made since 9.3, and it'll
soon-ish be out of support.If you can reproduce the issue on postgres proper, rather than a
modified version in an environment that precludes getting detailed data,
we might be able to sensibly help you further.So far it's been almost two months of investigation
and people at AWS technical support don't seem to find the cause. I think
it could be related to Postgres and the number of schema/tables in the
database, that's why I post this issue here.There've been improvements made since 9.3. Upgrade.
Greetings,
Andres Freund
On Mon, Jun 18, 2018 at 7:23 PM Andres Freund <andres@anarazel.de> wrote:
So far it's been almost two months of investigation
and people at AWS technical support don't seem to find the cause. I think
it could be related to Postgres and the number of schema/tables in the
database, that's why I post this issue here.There've been improvements made since 9.3. Upgrade.
You are right, and I'm aware of that (I'm planning a version upgrade), I
just wanted to know if anybody knew if the number of schema/tables could be
the cause of high write levels due to Postgres processes reaching some
internal limit.
Thanks.
Juan
On Tue, Jun 19, 2018 at 12:16 AM Benjamin Scherrey <
scherrey@proteus-tech.com> wrote:
I would also add that AWS' I/O capabilities are quite poor and expensive.
I assume that you have tried purchasing additional IOOPs on that setup to
see whether you got an expected speed up? If not you should try that as a
diagnostic tool even if you wouldn't want to pay that on an ongoing basis.
I haven't tried increasing available IOPS, but looking at the metrics, I'm
far away of the limit, so it doesn't seem to be related, but I will explore
this option further.
We have a client that is I/O write bound and it has taken us significant
efforts to get it to perform well on AWS. We definitely run our own
instances rather than depend on RDS and have always been able to outperform
RDS instances which seem to really be focused to provide a PAAS capability
for developers who really don't want to have to understand how a db works.
Running our identical environment on bare metal is like night & day under
any circumstances when compared to AWS.Client's requirement is AWS so we keep working on it and we like AWS for
many things but understand it will always underperform on I/O.Post actual measurements with and without IOOPs or create your own PG
server instance and then people might be able to give you additional
insights.
I'll consider your suggestions and I'll back with more info in case I
create my own environment, I just wanted to know if the number of
schemas/tables could be the cause of high writes levels, in order to
discard this hypothesis.
Thanks