Re: zabbix on postgresql - very slow delete of events

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

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 eventid = [some testing id];

select * from pg_stat_xact_user_tables where seq_scan>0 or idx_scan>0 order by seq_scan+idx_scan desc;

ABORT;

And provide result of the last query and how long delete runs.

It might help us understand whats going on.

Currently I have 3 ideas:

1)very very slow and overloaded IO subsystem

2)a lot of stuff being delete by ON DELETE CASCADE 

3)some locking prevent foreign key checks run fast

On Wed, Jul 24, 2019 at 11:12 AM Kristian Ejvind <Kristian.Ejvind@xxxxxxxxx> wrote:

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 we would have a problem with it in a long time. After all, although our Zabbix installation
definitely is larger than "small", it's still far from "large".

I think I would need assistance with debugging why postgresql behaves like it does.
Is there a defect with deleting data from a table that has multiple foreign keys referencing it from a  certain table?
Is there a problem with the query optimizer that chooses the wrong plan when working on the foreign key constraints?
How do I inspect how the db works on the deletion of rows from the referencing tables?

Regards
Kristian



?On 2019-07-23, 16:33, "Kenneth Marshall" <ktm@xxxxxxxx> wrote:

    On Tue, Jul 23, 2019 at 01:41:53PM +0000, 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 partitioned table in postgres 10, we haven't worked on that.
    >
    > Regards
    > Kristian

    Hi Kristian,

    Why are you not partitioning the events and alerts tables as well? That
    would eliminate this problem and you already have the infrastructure in
    place to support the management since you are using it for the history
    and trends tables.

    Regards,
    Ken






Resurs Bank AB
Kristian Ejvind
Linux System Administrator
IT Operations | Technical Operations

Ekslingan 8
Box 222 09, SE-25467 Helsingborg

Direkt Tfn:
Mobil: +46 728571483
Vxl: +46 42 382000
Fax:
E-post: Kristian.Ejvind@xxxxxxxxx
Webb: http://www.resursbank.se

--

Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone RU: +7  985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"