Edit: For some reason the forum isn't allowing me to embed links (complains about to many even with just one link) so I had to obscure the link names.
Hey, I just wanted to start a thread about tuning Oracle to work better with Zabbix when dealing with 1000nvps+ and hopefully get some input from others that have already done this (I'm just in a testing phased right now) - I'm not a DBA so bare with me! I'd also like to get feedback from others that are currently using Oracle for large environments - I think most people use MySQL. So I'll start with different points:
1. Session/process usage
We were seeing high session usage inside of Oracle. Looking into this it seems like there were two factors:
A) For each poller process Zabbix has a dedicated connection to Oracle
B) The frontend was causing a high number of sessions
Investigating into this further I found the issue ZBX-6160, this fix is unfortunately only on trunk but I manually backported this issue to 2.0 and it seems to have resolved the issue with persistent connections caused by the frontend. So basically at this point we just need to make sure we have max sessions/processes high enough to account for the 1 per poller model and manually backport the frontend fix.
2. SQL Hit ratio/variable binding issue
So to monitor Zabbix I installed Orabbix. One of the things Orabbix is complaining about is the SQL hit ratio. Looking at this statistic we're averaging around a 15% hit ratio. I started investigating this and I believe it's referring not being able to pull up cached SQL plans possibly due to literals being used instead of bind variables. This then lead me to this -www.zabbix.com/forum/showthread.php?t=25892 - which has a corresponding Jira for it . In that Jira it mentions this can partially be overcome by setting "shared_cursor=force" - after setting this though that SQL hit ratio metric has not improved (maybe I'm interpreting this metric incorrectly). Any suggestions here?
3. Table partitioning
I plan to use table partitioning which is discussed for Oracle specifically here -http://remigium.blogspot.com/2012/10/zabbix-on-oracle-few-notes.html - I haven't had a chance to try this yet though (use of interval partitioning specifically)
Any other useful information that people using Oracle in a large environment are doing?
Hey, I just wanted to start a thread about tuning Oracle to work better with Zabbix when dealing with 1000nvps+ and hopefully get some input from others that have already done this (I'm just in a testing phased right now) - I'm not a DBA so bare with me! I'd also like to get feedback from others that are currently using Oracle for large environments - I think most people use MySQL. So I'll start with different points:
1. Session/process usage
We were seeing high session usage inside of Oracle. Looking into this it seems like there were two factors:
A) For each poller process Zabbix has a dedicated connection to Oracle
B) The frontend was causing a high number of sessions
Investigating into this further I found the issue ZBX-6160, this fix is unfortunately only on trunk but I manually backported this issue to 2.0 and it seems to have resolved the issue with persistent connections caused by the frontend. So basically at this point we just need to make sure we have max sessions/processes high enough to account for the 1 per poller model and manually backport the frontend fix.
2. SQL Hit ratio/variable binding issue
So to monitor Zabbix I installed Orabbix. One of the things Orabbix is complaining about is the SQL hit ratio. Looking at this statistic we're averaging around a 15% hit ratio. I started investigating this and I believe it's referring not being able to pull up cached SQL plans possibly due to literals being used instead of bind variables. This then lead me to this -www.zabbix.com/forum/showthread.php?t=25892 - which has a corresponding Jira for it . In that Jira it mentions this can partially be overcome by setting "shared_cursor=force" - after setting this though that SQL hit ratio metric has not improved (maybe I'm interpreting this metric incorrectly). Any suggestions here?
3. Table partitioning
I plan to use table partitioning which is discussed for Oracle specifically here -http://remigium.blogspot.com/2012/10/zabbix-on-oracle-few-notes.html - I haven't had a chance to try this yet though (use of interval partitioning specifically)
Any other useful information that people using Oracle in a large environment are doing?
Comment