On Wed, Jul 24, 2019 at 6:12 PM Kristian Ejvind <kristian.ejv...@resurs.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/ 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 "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
