Re: zabbix on postgresql - very slow delete of events

On Wed, Jul 24, 2019 at 6:12 PM Kristian Ejvind <[email protected]>
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 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
>
> =====
>
>
>
> Well spotted! On closer examination it seems that data types are wrong in
> several places. I suspect that this comes
>
> from the time when our Zabbix ran on a MySQL database, which was converted
> over to PostgreSQL a few years
>
> ago. I agree this discrepancy is suspicious and I will continue to examine
> it.
>
>
>
> Regarding your ideas in the email below, I can say that 1) is not valid,
> disk latency is in the range of a few ms.
>
> This is the output from your recommended query, which seems to verify your
> suspicions.
>
>
>
> zabbix_34=# begin; delete from zabbix.events where eventid = 7123123;
> select * from pg_stat_xact_user_tables where seq_scan>0 or idx_scan>0 order
> by seq_scan+idx_scan desc; rollback;
>
> Time: 0.113 ms
>
> Time: 4798.189 ms (00:04.798)
>
> relid  | schemaname |    relname     | seq_scan | seq_tup_read | idx_scan
> | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd
>
>
> --------+------------+----------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------
>
>   41940 | zabbix     | event_recovery |        3 |     35495224 |        0
> |             0 |         0 |         0 |         1 |             0
>
>   41675 | zabbix     | alerts         |        1 |       544966 |        1
> |             0 |         0 |         0 |         0 |             0
>
>   42573 | zabbix     | problem        |        2 |        13896 |        0
> |             0 |         0 |         0 |         0 |             0
>
>   41943 | zabbix     | event_tag      |        1 |        22004 |        0
> |             0 |         0 |         0 |         0 |             0
>
>   41649 | zabbix     | acknowledges   |        1 |           47 |        0
> |             0 |         0 |         0 |         0 |             0
>
>   41951 | zabbix     | events         |        0 |            0 |        1
> |             1 |         0 |         0 |         1 |             0
>
> 260215 | zabbix     | event_suppress |        1 |            0 |        0
> |             0 |         0 |         0 |         0 |             0
>

Hi Kristian,

This result definitely proves that indexes not used during foreign key
checks (see that non-zero seq_scan counters for linked tables).
Only possible reason (IMHO) that wrong usage numeric in place of bigint.
I recommend change types of events.eventid (and any other similar fields)
to bigint.
It should resolve your performance issues with deletes on events table (as
additional bonus - bigint a lot faster and compact type than numeric).

-- 
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

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