Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-20488

Performance degradation due to the schema and DB check query

    XMLWordPrintable

Details

    • Problem report
    • Status: Closed
    • Trivial
    • Resolution: Won't fix
    • None
    • None
    • API (A), Frontend (F)
    • None
    • Team B
    • Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022)
    • 0.25

    Description

      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:

      https://git.zabbix.com/projects/ZT/repos/rsm-scripts/browse/frontends/php/include/classes/db/PostgresqlDbBackend.php#34

      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)

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            agavrilovs Aleksandrs Petrovs-Gavrilovs
            Votes:
            4 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: