[ZBX-5051] Three Patch on Zabbix Oracle: open cursor, TX lock, share pool Created: 2012 May 23  Updated: 2017 May 30  Resolved: 2013 Mar 08

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F), Server (S)
Affects Version/s: 1.8.9
Fix Version/s: None

Type: Incident report Priority: Critical
Reporter: frankyao Assignee: Unassigned
Resolution: Won't fix Votes: 0
Labels: performance, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Server: Red Hat 4.1.2-46,16 cores, 32G.
3 Proxy:Red Hat 4.1.2-46, 4 cores, 8G,
Oracle server: Red Hat 4.1.2-46, 16 cores, 16G, 10 SATA disk, Oracle 11.2.0.3.0

========================================================

Zabbix Performance:

Number of hosts (monitored/not monitored/templates) 4751 3445 / 1194 / 112
Number of items (monitored/disabled/not supported) 569221 530812 / 24719 / 13690
Number of triggers (enabled/disabled)[problem/unknown/ok] 196589 128102 / 68487 [1119 / 42324 / 84659]
Number of users (online) 73 12
Required server performance, new values per second 1411.19 -


Attachments: Zip Archive src.zip    

 Description   

I've read release notes after 1.8.8 and these problems have not been fixed.

1. There is a place that curosr open in DB will not be closed in some logic. I read the code and fix it.

2. get-rowid-method is very low performance in zabbix-1.8.8. As we use oracle, we remove the function in source code and use Sequence of oracle to get id.It will solve the TX lock problem in oracle in high scalability. PHP of frontend needs to be modify as well.

3. For oracle, zabbix made a method to compose sql into a large sql, and add 'begin' & 'end'. This will make oracle can not share cursors. Because of that, Oracle will CRASH due to share pool issue.

================================

source code patched is in the attachment



 Comments   
Comment by Alexei Vladishev [ 2012 Jun 13 ]

This patch will not be incorporated because it breaks distributed monitoring. We cannot use sequences in 1.8.x and 2.0.x.

Comment by Olgierd Wolodkiewicz [ 2012 Oct 06 ]

I work in corporation and we rely only on Oracle RAC in production environment. I implemented Zabbix 2.0.3 with RAC g11r2 with REQUIRED option shared_cursor=force. Now it is stable but far from perfect. The issues I have are:

  • terrible locks when I change config in GUI and zabbix_server read config (rcache)
  • when I remove lld items (prototypes) it causes RAC to be almost crashed and I see also lock errors in zabbix_server

Help me please and implement optimizations for Oracle. If not my management will refuse for sure to buy any service support (we only think about Enterprise).

Values/sec = 200
hosts = 300
Items > 100000
triggers > 10000

Comment by Alexander Vladishev [ 2013 Mar 08 ]

It is reopened for detail research.

Comment by Andris Zeila [ 2013 Mar 08 ]

I reviewed the patch:
1) The cursor is not being closed when in the case of fatal failure (zabbix can't allocate new identifiers). While
it would be better to close result, it's the least worry at that moment.

2) Id calculations will be improved(fixed) in 2.2 (the fix is not yet in trunk, but getting there).

3) Oracle setting shared_cursor=force should solve the batch execution problems.

I understand the most problems (locking) are because of id calculations, which will be fixed in 2.2.
Therefore closing this one.

Comment by Ryan Rupp [ 2013 Mar 18 ]

Can you link to the new feature Jira that resolves issue #2 that you're referring to? Will this be merged back to the 2.0 branch as a bug fix? Also, for #3 is this a permanent solution or are there any plans to resolve this in the application code? Trying to evaluate the performance now for my company with Oracle RAC vs MySQL for Zabbix

Comment by frankyao [ 2013 Mar 21 ]

for #3, if your set shared_cursor=force, the 'big sql' will make the oracle crash as well in our environment.

After consulting our DBA who worked for eBay, he said that in Oracle, 'shared curosor' WILL NOT work in the sql wrapped in 'begin' and 'end' wheather you set 'shared_cursor=force' or not.

Comment by Andris Zeila [ 2013 Mar 21 ]

The ID calculation improvements are done as basis for ZBXNEXT-1575, but unfortunately it won't be backported to 2.0 branch.

Regarding Oracle crashes, could you please attach zabbix server log (if possible with DebugLevel 4)?

Comment by frankyao [ 2013 Mar 21 ]

I have leaved the company where I'm in charge of zabbix. While the system encounter the 'shared pool' issue, there are not any log in zabbix server in 4-debug-level. We found this issue in oracle monitor.

Generated at Sun Mar 16 22:56:02 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.