[ZBX-13571] Slow select from history_text after upgrade to 3.4.7 Created: 2018 Mar 05  Updated: 2018 Mar 26  Resolved: 2018 Mar 25

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 3.4.7
Fix Version/s: 3.4.8rc1, 4.0.0alpha5, 4.0 (plan)

Type: Incident report Priority: Major
Reporter: Sergey Mamaev Assignee: Andris Zeila
Resolution: Fixed Votes: 0
Labels: performance, regression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Server = Centos 7 x86_64, database = Oracle 12.1 on AIX + 3par flash storage. Housekeeping is off (using partitioned history tables)


Team: Team A
Sprint: Sprint 30
Story Points: 0.5

 Description   

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"?



 Comments   
Comment by Sergey Mamaev [ 2018 Mar 07 ]

Any suggestions here? Can you confirm this as a bug?

Comment by Pavel Liachovič [ 2018 Mar 16 ]

Hello, we have very similar problem with partitioned mysql, after upgrade to 3.4 we see a lot of select queries from (zero) to (unix_time). This queries block's insert and update queries.
Is this normal?

Comment by Kaspars Mednis [ 2018 Mar 16 ]

Hi Pavel,

Which exact Zabbix server version are you using ?

Regards,
Kaspars

Comment by Pavel Liachovič [ 2018 Mar 16 ]

We have now upgraded to Zabbix 3.4.7 from 3.0.14

Comment by Kaspars Mednis [ 2018 Mar 16 ]

Can you paste some examples of the select queries from (zero) to (unix_time) ? Is only the history_text table affected ?

Comment by Pavel Liachovič [ 2018 Mar 16 ]

Sorry we have no slow-log enabled but It was something like this:

select clock,ns,value from history_uint where itemid=27596 and clock>0 and clock<=1521098462 order by clock desc limit 2

This type of queries was not only from history_uint tables, also from history, history_log and history_text

Comment by Vladislavs Sokurenko [ 2018 Mar 16 ]

Create trigger

{Zabbix server:trap1.avg(#5)}<0

See following queries

 17601:20180316:143632.266 In zbx_history_get_values() itemid:28249 value_type:3 start:0 count:5 end:1521203791
 17601:20180316:143632.282 query [txnlev:1] [select clock,ns,value from history_uint where itemid=28249 and clock>0 and clock<=1521203791 order by clock desc limit 5]
 17601:20180316:143632.292 query [txnlev:1] [select clock,ns,value from history_uint where itemid=28249 and clock=1521203584]
 17601:20180316:143632.298 End of zbx_history_get_values():SUCCEED values:6

Expected as before upgrade periods are used:

 26649:20180316:144105.087 query [txnlev:1] [select clock,ns,value from history_uint where itemid=23667 and clock<=1521204064 and clock>1521200464 order by clock desc limit 5]
 26649:20180316:144105.097 query [txnlev:1] [select clock,ns,value from history_uint where itemid=23667 and clock=1521203959]
Comment by Andris Zeila [ 2018 Mar 19 ]

wiper Regression (sql queries to improve performance on partitioned history tables) found and fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-13571.

Comment by Vladislavs Sokurenko [ 2018 Mar 19 ]

Successfully tested.

Comment by Andris Zeila [ 2018 Mar 19 ]

Released in:

  • pre-3.4.8rc1 r78764
  • pre-4.0.0alpha5 r78765
Generated at Fri Mar 29 16:03:48 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.