Re: zabbix on postgresql - very slow delete of events

Hi.

Just a short message, confirming that after we've altered the tables to have matching
types, deletes now take 1 ms, instead of 5 sec. Indexes are being used now.

Thanks for assistance.

Regards
Kristian

ps. would be nice with some warnings or indications in analyze output when this happens.

[cid:imagec5a681(dot)PNG(at)530b3f9e(dot)479048e5]
Kristian Ejvind
Linux System Administrator
IT Operations | Technical Operations

Resurs Bank
Ekslingan 8
Box 222 09, SE-25467 Helsingborg

Mobil: +46 728571483
Växel: +46 42 38 20 00
E-post: Kristian(dot)Ejvind(at)resurs(dot)se<mailto:Kristian(dot)Ejvind(at)resurs(dot)se>
Webb: www.resursbank.se<http://www.resursbank.se>

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
Date: Wednesday, 24 July 2019 at 19:17
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

On Wed, Jul 24, 2019 at 6:12 PM Kristian Ejvind <Kristian(dot)Ejvind(at)resurs(dot)se<mailto:Kristian(dot)Ejvind(at)resurs(dot)se>> 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/<https://smex12-5-en-ctp.trendmicro.com:443/wis/clicktime/v1/query?url=https%3a%2f%2fdataegret.com&umid=90a98c9f-46cd-4941-b939-8da90b514311&auth=daed959355609b907128d19d56c675829c94a38e-92a73de4d891916aa17d6a4577153d5be0a70dd8>

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

"??????, ?? ??? ?????????? ??? ?? ??????, ?? ?????? ??? ??-???????? ?????? ????? ? ??? ????? ??? ????"