From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | Kristian Ejvind <Kristian(dot)Ejvind(at)resurs(dot)se> |
Cc: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: zabbix on postgresql - very slow delete of events |
Date: | 2019-07-24 14:05:34 |
Message-ID: | CAK-MWwRYfkUgboJTWQvDn64LBeB4EarGZDe94ngbsUqmNnxdcQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>
>
> 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 lot of time.
>
> But why? I far as I can see, the delete is fully CPU bound during
> execution.
>
>
>
> Deleting the corresponding row directly from event_recovery or alerts
> executes in less than 0.1 ms.
>
>
>
> Any ideas?
>
>
>
> I've observed that alerts and event_recovery tables both have more than
> one foreign key that references events, if that matters.
>
>
>
Hi Kristian,
After comparing structure of zabbix tables with same in my zabbix
installation I found one very weird difference.
Why type of events.eventid had been changed from default bigint to numeric?
I suspect that the difference between events.eventid (numeric) type
and event_recovery.*_eventid (bigint) types might lead to inability of use
index during foreign key checks.
Anyway it will be clearly visible on the pg_stat_xact_user_tables results
(I now expect to see 3 sequential scan on event_recovery and may be on some
other tables as well).
Kind Regards,
Maxim
--
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"
From | Date | Subject | |
---|---|---|---|
Next Message | Kristian Ejvind | 2019-07-24 15:12:18 | Re: zabbix on postgresql - very slow delete of events |
Previous Message | Maxim Boguk | 2019-07-24 13:54:26 | Re: zabbix on postgresql - very slow delete of events |