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

Slow select from history_text after upgrade to 3.4.7


    • Icon: Incident report Incident report
    • Resolution: Fixed
    • Icon: Major Major
    • 3.4.8rc1, 4.0.0alpha5, 4.0 (plan)
    • 3.4.7
    • Server (S)
    • Server = Centos 7 x86_64, database = Oracle 12.1 on AIX + 3par flash storage. Housekeeping is off (using partitioned history tables)
    • Sprint 30
    • 0.5

      After upgrade from 3.4.4 to 3.4.7 we recieved a slow query performance of the query:
      select * from (select clock,ns,value from history_text where itemid=:"SYS_B_0" and clock>:"SYS_B_1" and clock<=:"SYS_B_2" order by clock desc) where rownum<=:"SYS_B_3"

      where :"SYS_B_..." - are known as bind variables in Oracle.

      The problem is that SQL monitoring shows that vaiable "SYS_B_1" in this query is 0 (zero value). clock<=:"SYS_B_2" is good, it contains different unix timestamp values. Final query in Oracle looks like this:
      select * from (select clock,ns,value from history_text where itemid=111950 and clock>0 and clock<=1520240673 order by clock desc) where rownum<=1

      All of our history tables are weekly partitioned by CLOCK value and our installation is rather big (current NVPS = 4303.62). Weekly partitions are about 47-48 billions records each. We are keeping ~15 last partitions of history tables. So the SQL plan itself is good (using local indexes in partitioned table, optimizer cost is very low), but the amount of actual fetch from history_text is large, event though there are partitions and index (itemid + clock) inside every partiton.

      I wonder is there any known bug in in Oracle backend code according to clock>:"SYS_B_1"?

            wiper Andris Zeila
            smamaev Sergey Mamaev
            Team A
            0 Vote for this issue
            8 Start watching this issue