BUG #13510: memory problem with mass-update statements

From: peter(dot)slavov(at)sumup(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13510: memory problem with mass-update statements
Date: 2015-07-21 14:59:36
Message-ID: 20150721145936.9707.48553@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13510
Logged by: Peter Slavov
Email address: peter(dot)slavov(at)sumup(dot)com
PostgreSQL version: 9.4.4
Operating system: Debian wheezy
Description:

Hi,

In our company we have a zabbix monitoring server which uses PostgreSQL
database to store data. Most of the time the servers run very fast and
stable. But after a zabbix server restart something strange happen - some
cache flush to database is activated and on the database there multiple
updates are executed. Updates are formed as a string with multiple queries
in it - like this:
------------
update trends_uint set num=12,value_min=1,value_avg=1,value_max=1 where
itemid=24134 and clock=1437400800;
update trends_uint set
num=6,value_min=7028177,value_avg=7029677,value_max=7031178 where
itemid=28942 and clock=1437400800;
update trends_uint set num=12,value_min=3,value_avg=3,value_max=3 where
itemid=38537 and clock=1437400800;
update trends_uint set num=12,value_min=1,value_avg=1,value_max=1 where
itemid=38538 and clock=1437400800;
update trends_uint set num=12,value_min=1,value_avg=1,value_max=1 where
itemid=38539 and clock=1437400800;
update trends_uint set
num=6,value_min=1434709932,value_avg=1434709932,value_max=1434709932 where
itemid=40349 and clock=1437400800;
update trends_uint set num=12,value_min=2,value_avg=2,value_max=2 where
itemid=44233 and
update trends_uint set num=12,value_min=1,value_avg=1,value_max=1 where
itemid=44234 and clock=1437400800;
-- more -- ~ 50 queries max
----------
when this happen this process on the PostgreSQL server starts to eat the
whole available memory (4GB RAM + 8GB swap), until it crashes with "out of
memory" error , and then it starts again.
When this happen zabbix server is not affected in any way.
Here is some more info:
- database size is ~ 50GB
- updated tables when this happen are partitioned daily for an year - ~365
partitions
- shared buffers are set to 900MB

Can you tell me where can be the problem - is it the partitions or something
else?

thanks

Browse pgsql-bugs by date

  From Date Subject
Next Message koreth 2015-07-21 15:22:07 BUG #13511: View containing correlated subquery with MAX() produces slow plan
Previous Message Fabien COELHO 2015-07-21 14:17:10 Re: BUG #13442: ISBN doesn't always roundtrip with text