Re: zabbix on postgresql - very slow delete of events - Mailing list pgsql-performance

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