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