ejv...@resurs.se>
Webb: www.resursbank.se<http://www.resursbank.se>
From: Maxim Boguk
Date: Wednesday, 24 July 2019 at 19:17
To: Kristian Ejvind
Cc: "pgsql-performance@lists.postgresql.org"
Subject: Re: zabbix on postgresql - very slow delete of events
On Wed, Jul 24, 20
On Wed, Jul 24, 2019 at 6:12 PM Kristian Ejvind
wrote:
> Hi Maxim
>
>
>
> Thanks for your advice, and let me start with your second email, which
> I'll copy here:
>
>
>
> =
>
> Hi Kristian,
>
>
>
> After comparing structure of zabbix tables with same in my zabbix
> installation I found one
"
Subject: Re: zabbix on postgresql - very slow delete of events
Hi Kristian,
If you look for explain analyze results for delete,
you will see that 99% of time query spent on the foreign key triggers checks.
In the same time the database have indexes on foreign key side in place.
I recommend
>
>
> All these queries execute well below 1 ms, using indexes.
>
>
>
> Let's delete one row. See explain results here:
> https://explain.depesz.com/s/aycf . 5 seconds to delete a single row,
> wow!
>
> This shows that it is the foreign key constraints on event_recovery and
> alerts that take a
Hi Kristian,
If you look for explain analyze results for delete,
you will see that 99% of time query spent on the foreign key triggers
checks.
In the same time the database have indexes on foreign key side in place.
I recommend try this:
\timing on
BEGIN;
delete from zabbix.events where
Hi.
Well, the events table has both a primary key and foreign keys referencing it,
which is not possible
on a partitioned table in postgresql 10. How did you work around this issue?
On the other hand, if we can get the deletion of rows from the events table run
at normal speed, I
can't imagine
On Tue, Jul 23, 2019 at 01:41:53PM +, Kristian Ejvind wrote:
> Thanks Kenneth. In fact we've already partitioned the largest history* and
> trends* tables
> and that has been running fine for a year. Performance was vastly improved.
> But since you
> can't have a unique index on a
Thanks Kenneth. In fact we've already partitioned the largest history* and
trends* tables
and that has been running fine for a year. Performance was vastly improved. But
since you
can't have a unique index on a partitioned table in postgres 10, we haven't
worked on that.
Regards
Kristian
?On
On Tue, Jul 23, 2019 at 08:07:55AM +, Kristian Ejvind wrote:
> Hi
>
> This will be a rather lengthy post, just to give the full (I hope) picture.
> We're using Zabbix for monitoring and I'm having problems
> understanding why the deletion of rows in the events table is so slow.
>
> Zabbix:
Hi
This will be a rather lengthy post, just to give the full (I hope) picture.
We're using Zabbix for monitoring and I'm having problems
understanding why the deletion of rows in the events table is so slow.
Zabbix: 4.2 (never mind the name of the db - it is 4.2)
new values per second: ~400
10 matches
Mail list logo
