Postgres&Timescale behavior

[Stayler]

I setup Postgresql with timescale partitioning and it`s look very good.
But one day I noticed strange behavior:
every day on DB-host in 02:04 I have many IOPS read & cpu iowait and so on.

If look in log:

#> grep -i house /var/log/zabbix/zabbix_server.log
.....
16661:20200227:004157.453 housekeeper [deleted 0 hist/trends, 0 items/triggers, 747 events, 77 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 0.242270 sec, idle for 1 hour(s)]
 16661:20200227:014159.228 executing housekeeper
 16661:20200227:014159.454 housekeeper [deleted 0 hist/trends, 0 items/triggers, 516 events, 61 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 0.216586 sec, idle for 1 hour(s)]
 16661:20200227:024200.579 executing housekeeper
 16661:20200227:033906.604 housekeeper [deleted 0 hist/trends, 0 items/triggers, 636 events, 118 problems, 0 sessions, 0 alarms, 0 audit, 0 records in [COLOR=#FF0000]3426[/COLOR].014272 sec, idle for 1 hour(s)]
 16661:20200227:043908.646 executing housekeeper
 16661:20200227:043908.990 housekeeper [deleted 0 hist/trends, 0 items/triggers, 943 events, 62 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 0.298944 sec, idle for 1 hour(s)]
 16661:20200227:053910.132 executing housekeeper
 16661:20200227:053910.305 housekeeper [deleted 0 hist/trends, 0 items/triggers, 544 events, 44 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 0.153209 sec, idle for 1 hour(s)]
.....

So in this period of time, as I see, in db log:

.....
2020-02-27 03:39:04.778 EET [104465] ERROR:  could not open relation with OID 1561123
2020-02-27 03:39:04.778 EET [104465] STATEMENT:  SELECT MAX(h.clock) FROM history_uint h WHERE h.itemid='69466'
2020-02-27 03:39:04.777 EET [103891] ERROR:  could not open relation with OID 1561123
2020-02-27 03:39:04.777 EET [103891] STATEMENT:  SELECT MAX(h.clock) FROM history_uint h WHERE h.itemid='69466'
2020-02-27 03:39:04.777 EET [102314] ERROR:  could not open relation with OID 1561123
2020-02-27 03:39:04.777 EET [102314] STATEMENT:  SELECT MAX(h.clock) FROM history_uint h WHERE h.itemid='69466'
2020-02-27 03:39:04.777 EET [104464] ERROR:  could not open relation with OID 1561123
.....

So I tried to run "explain analyze" in this period. Its says: in ordinary goes to "history_uint_1" index, but in that time - to "history_uint_clock_idx";
Why it`s happening? How to debug? It`s some of Zabbix works?

[dimir]

Timescale takes care of history and trends, but not events and problems, because of the current design limits. The work on supporting events and problems for partitioning is in process of discussion.
That there looks like a deadlock. What kind of timeouts do you have configured in your database?

[Stayler]

You are talking about those?

                  name                 | setting
-------------------------------------+---------
 archive_timeout                     | 0
 authentication_timeout              | 60
 checkpoint_timeout                  | 300
 deadlock_timeout                    | 1000
 idle_in_transaction_session_timeout | 0
 lock_timeout                        | 0
 statement_timeout                   | 0
 wal_receiver_timeout                | 60000
 wal_sender_timeout                  | 60000