-
Problem report
-
Resolution: Won't fix
-
Trivial
-
None
-
None
-
None
-
Team B
-
Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022)
-
0.25
Zabbix version 4.0.9
Response time of zabbix frontend (including login page) increased dramatically from about 200ms to average 6-8 sec. Simple SELECTs can take few minutes. INSERTs are executing without any big latency.
We have identified the following query which is constantly executed by the frontend:
select 1 from information_schema.tables WHERE table_catalog='zabbix' AND table_schema='public' AND table_name='dbversion';
This query currently takes around 4 seconds to execute. It seems to be executed every time a connection is made to the database. Here is the current analysis of the query:
zabbix=# explain analyze select 1 from information_schema.tables WHERE table_catalog='zabbix' AND table_schema='public' AND table_name='dbversion';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Result (cost=0.81..1338870.56 rows=1 width=4) (actual time=288.223..4496.569 rows=1 loops=1)
One-Time Filter: (((current_database())::information_schema.sql_identifier)::text = 'zabbix'::text)
-> Nested Loop Left Join (cost=0.81..1338870.56 rows=1 width=0) (actual time=288.217..4496.562 rows=1 loops=1)
-> Nested Loop (cost=0.00..1338761.70 rows=1 width=4) (actual time=288.197..4496.541 rows=1 loops=1)
Join Filter: (nc.oid = c.relnamespace)
-> Seq Scan on pg_namespace nc (cost=0.00..17.50 rows=1 width=4) (actual time=0.026..0.872 rows=1 loops=1)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text = 'public'::text))
Rows Removed by Filter: 426
-> Seq Scan on pg_class c (cost=0.00..1338744.00 rows=16 width=8) (actual time=288.169..4495.663 rows=1 loops=1)
Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (((relname)::information_schema.sql_identifier)::text = 'dbversion'::text) AND (pg_has_role(relowne
r, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE
, REFERENCES'::text)))
Rows Removed by Filter: 5057
-> Nested Loop (cost=0.80..108.84 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.53..108.55 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (c.reloftype = oid)
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.27..0.29 rows=1 width=4) (never executed)
Index Cond: (oid = t.typnamespace)
Heap Fetches: 0
Planning Time: 0.393 ms
Execution Time: 4496.682 ms
(19 rows)
We have identified the following PHP file where we decided to make a change in order to avoid the execution of the query: /usr/share/zabbix/include/classes/db/PostgresqlDbBackend.php
**
We added the following line:
return true;
It was added here:
This ensures that the function does not make any query to check if the DB schema is the correct one.
Before the workaround some steps of the scenario for login to the web UI could take up to 11 s. On average I would say each step took around 7s. The slowness in response was not limited to login. All user interactions with the UI were noticeably slow.
Now it takes around 60ms for each step of the test scenario. Other user interactions have also become very responsive.
We use PostgreSQL 11
Current count of databases: 6
Current count of users : 13
Table counts of all DBs:
zabbix=# select count(*)
from information_schema.tables;
count
-------
2193
(1 row)
zabbix=# \c postgres
psql (9.2.24, server 11.4)
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
You are now connected to database "postgres" as user "postgres".
postgres=# select count(*)
from information_schema.tables;
count
-------
188
(1 row)
postgres=# \c reports
psql (9.2.24, server 11.4)
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
You are now connected to database "reports" as user "postgres".
reports=# select count(*)
from information_schema.tables;
count
-------
196
(1 row)
reports=# \c pollers
psql (9.2.24, server 11.4)
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
You are now connected to database "pollers" as user "postgres".
pollers=# select count(*)
from information_schema.tables;
count
-------
194
(1 row)
