|
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 (7 rows) Time: 2.857 ms Time: 0.162 ms Regards Kristian
From:
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:
--
Maxim Boguk | ||||||||||||||||||||||||||||||
