BUG #14831: Intermittent write blocks
The following bug has been logged on the website:
Bug reference: 14831
Logged by: Harisankar Valiyattil
Email address: harisankar.valiyattil@capitalone.com
PostgreSQL version: 9.4.7
Operating system: linux
Description:
We use postgres on amazon RDS. We have a table with close to 15 million data
now and an application is constantly firing some update(insert/update)
operations to this table at around 25 transactions per second. the table has
a jsonb column which has a gin index on it; the jonb data has appx 20-40
attrinbutes in an average. The application keep on updating data on this
jsonb column.
While the traffic is running, we see that there is a sudden drop in the
write operations (no write happening in DB) causing increase in RDS cpu,
queue depth etc. after a few seconds (30 sec, 60 sec) things come back to
normal. If the traffic is constant this repeats at constant intervals.
Our work_mem is 4MB.
What could be causing this? What kind of troubleshooting we can do? What is
the process that runs in the background that causes this intermittant write
delay?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
On 2017-09-27 20:59:33 +0000, harisankar.valiyattil@capitalone.com wrote:
The following bug has been logged on the website:
Bug reference: 14831
Logged by: Harisankar Valiyattil
Email address: harisankar.valiyattil@capitalone.com
PostgreSQL version: 9.4.7
Operating system: linux
Description:We use postgres on amazon RDS. We have a table with close to 15 million data
now and an application is constantly firing some update(insert/update)
operations to this table at around 25 transactions per second. the table has
a jsonb column which has a gin index on it; the jonb data has appx 20-40
attrinbutes in an average. The application keep on updating data on this
jsonb column.While the traffic is running, we see that there is a sudden drop in the
write operations (no write happening in DB) causing increase in RDS cpu,
queue depth etc. after a few seconds (30 sec, 60 sec) things come back to
normal. If the traffic is constant this repeats at constant intervals.Our work_mem is 4MB.
What could be causing this? What kind of troubleshooting we can do? What is
the process that runs in the background that causes this intermittant write
delay?
Presumably it's the OS starting to flush dirty buffers to disk, which it
periodically does. Not sure if RDS allows you to monitor that, but the
Dirt and Writeback lines in /proc/meminfo would show that. 9.6+ have
code to limit the amount of dirty buffers in the kernel (enabled by
default for background processes, disabled by default for backends
themseleves).
Greetings,
Andres Freund
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi Andres,
Thanks for your reply. What kind of fine tuning we can do in 9.4.7 to overcome this issue – is this a bug that is addressed in 9.6?
Thanks
Harisankar
On 9/27/17, 5:06 PM, "Andres Freund" <andres@anarazel.de> wrote:
Hi,
On 2017-09-27 20:59:33 +0000, harisankar.valiyattil@capitalone.com wrote:
The following bug has been logged on the website:
Bug reference: 14831
Logged by: Harisankar Valiyattil
Email address: harisankar.valiyattil@capitalone.com
PostgreSQL version: 9.4.7
Operating system: linux
Description:We use postgres on amazon RDS. We have a table with close to 15 million data
now and an application is constantly firing some update(insert/update)
operations to this table at around 25 transactions per second. the table has
a jsonb column which has a gin index on it; the jonb data has appx 20-40
attrinbutes in an average. The application keep on updating data on this
jsonb column.While the traffic is running, we see that there is a sudden drop in the
write operations (no write happening in DB) causing increase in RDS cpu,
queue depth etc. after a few seconds (30 sec, 60 sec) things come back to
normal. If the traffic is constant this repeats at constant intervals.Our work_mem is 4MB.
What could be causing this? What kind of troubleshooting we can do? What is
the process that runs in the background that causes this intermittant write
delay?
Presumably it's the OS starting to flush dirty buffers to disk, which it
periodically does. Not sure if RDS allows you to monitor that, but the
Dirt and Writeback lines in /proc/meminfo would show that. 9.6+ have
code to limit the amount of dirty buffers in the kernel (enabled by
default for background processes, disabled by default for backends
themseleves).
Greetings,
Andres Freund
________________________________________________________
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
On September 27, 2017 2:24:48 PM PDT, "Valiyattil, Harisankar" <Harisankar.Valiyattil@capitalone.com> wrote:
Hi Andres,
Thanks for your reply. What kind of fine tuning we can do in 9.4.7 to
overcome this issue – is this a bug that is addressed in 9.6?
First: this is not a bug. And thus this is the wrong list to discuss this. It's largely about OS level tuning.
Second: in honestly not sure what you can do on RDS, in Linux with proper access of turn down dirty background bytes.
Andres
Regards,
Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I am posting in postgres forum for the first time, sorry if I posted it in the wrong group. How I can add this to the postgres performance related list?
Thanks
Harisankar
On 9/27/17, 5:28 PM, "Andres Freund" <andres@anarazel.de> wrote:
Hi,
On September 27, 2017 2:24:48 PM PDT, "Valiyattil, Harisankar" <Harisankar.Valiyattil@capitalone.com> wrote:
Hi Andres,
Thanks for your reply. What kind of fine tuning we can do in 9.4.7 to
overcome this issue – is this a bug that is addressed in 9.6?
First: this is not a bug. And thus this is the wrong list to discuss this. It's largely about OS level tuning.
Second: in honestly not sure what you can do on RDS, in Linux with proper access of turn down dirty background bytes.
Andres
Regards,
Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
________________________________________________________
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs