Zabbix with Oracle backend : bad performance

[max]

Hi !

I have installed Zabbix 1.4.4 and recently 1.4.5 with an Oracle 10g backend on a VMWare virtual machine (deployed on ESX3.0.1, 4x2,6Ghz and 16Go RAM. The VM itself : 3Go RAM and can use all CPUs). OS : RHEL4.
I'm monitoring 5 hosts with items fournished by default with the agent (such as net.*, system.*, etc...) through templates. Totally : 5 hosts, 130 items, no trigger.
Even though I have few things to monitor, web interface is slow (about 5seconds to show a page) and the average load seems abnormally high on the server :
- without zabbix : <1
- with zabbix_server (no hosts) : 1,2
- with zabbix_server (5 hosts) : unstable between 3-5

Some details about my install configuration :

For the PHP frontends, I've installed Apache 2 and PHP 5.2.5 configured with Oracle instant Client 10 for oci8 module :

./configure \
      --enable-module=so \
      --prefix=/u02/zabbix/apache \
      --with-port=8888

./configure \
      --prefix=/u02/zabbix/apache/php \
      --with-config-file-path=/u02/zabbix/apache/conf \
      --with-apxs2=/u02/zabbix/apache/bin/apxs \
      --with-oci8=instantclient,/usr/lib/oracle/10.2.0.3/client/lib/ \
      --enable-sigchild \
      --enable-bcmath \
      --with-gd \
      --with-zlib-dir=/u02/zabbix/sources/php-5.2.5/ext/zlib

About the Zabbix server, after installing lib-sqlora8 and libcurl, I installed zabbix server with the following configuration :

./configure \
      --prefix=/u02/zabbix/serveur \
      --enable-server \
      --with-oracle=/u02/zabbix/libsqlora8 \
      --with-net-snmp \
      --with-libcurl=/u02/zabbix/libcurl/bin/curl-config

I'm expecting the Oracle backend to be responsible to this. I've tested the Zabbix on a local Ubuntu VM with Zabbix 1.4.4 and mysql 5 with good performances. I'm going to test on this VM ZAbbix with Oracle 10g XE backend to see if I found the same performances.

For those who uses an Oracle backend :
Can you tell me if you have such bad perfs as described below ? Or did you make something to correct this ?
What differences have between your configuration and mine ?

Thanks.

[Alexei]

I am aware of a number of ZABBIX 1.4.x/Oracle installations. Most of them are used to monitor hundreds (200-400) of servers with relatively good performance. Oracle is much slower than MySQL anyway!

[max]

In the ./configure of Zabbix, in the --with-oracle parameter, could we use something else than libsqlora on http://www.poitschke.de/libsqlora8/ ? Is there a way to use ORACLE libs ?
This way, I might have better performance... ?

[max]

Hi,

Still have this performance problem.
Load average without zabbix : around 2.
With Zabbix monitoring itself with some checks provided by the agent : see the graph... quite unbelievable (and have a look at the right hand bottom at the time made for the creation of this graph...).

See attached image or here :
http://tof.canardplus.com/show/98326...74c02da14.html

Is it due to some Oracle performance parameter ?
Is it due to VmWare environnement ?
Do I need to change something in RH ?

[xs-]

Have you tried reindexing all tables in oracle?

[max]

Hi !

Some more test to try solving this :
I've installed VMServer on my local computer and created a VM with RHEL4 and OracleXE. I've followed the same procedure to install Zabbix server (as described at the beginning) and I get good performances (monitoring 5 hosts about 200 items ) ! I made an export of my user scheme and imported it in the VM on ESX but it's still the same : the connexion zabbix<>oracle seems really slow and eats cpu through serveral oracle process.

Have you tried reindexing all tables in oracle?

I thought this could be a part of the problem, but didn't dig deeper yet. I'll try this.

[max]

Some topics related the same problem :

http://www.zabbix.com/forum/showthread.php?p=32273

http://www.zabbix.com/forum/showthread.php?t=6168

http://www.zabbix.com/forum/showthread.php?t=6316

[exkg]

oracle backend and performance

We have:

- Zabbix 1.5.3-5868
- Oracle 10G
- ~1300 hosts
- ~9000 itens
- ~3500 triggers
- ~160 screens
- ~120 maps
- ~300 groups
- ~250 insert/second
- 8 CPUs
- 32 GB of memory
- single scsi discs

The performance is poor when we try to access:

- Configuration -> Screens/Maps.
- Monitoration -> Screens/Maps/Graphs.
- Dashboard/System Summary.

We saw that queries checking permissions (to each host in maps) are the bottleneck.

Our Oracle DBA ask if is possible:

- use of 'sequence' in IDS table because the updates make this table 'locked'

I dont know if is clear ...

[exkg]

..... someone ?

[Alexei]

It is expected to have any sorts of problems with beta releases. It seems that 1.5.3 is not an exception.

Anyway thanks for the report! We will pay special attention to the listed screens doing testing.

[exkg]

Tanks Alexei,

Our scenario have a lot of groups and each host participates in 5 or 7 groups.
I'm thinking try migrate Oracle -> MySQL ... are u think ? I dont know if its a 'problem' whith database or whith application.

[Alexei]

You will see HUGE performance improvements after migration to MySQL. Oracle is nice and bullet proof, but heavy, MySQL is also nice yet works much faster.

[exkg]

Right,

We will try soon and post results here.


[]s,
Luciano

[exkg]

Hi,


We port the Oracle tables to MySQL tables, put it in other hardware (not to stronger) and things (screens/maps/permissions checks) are acceptable ... but our Oracle DBAs ask to 'one more try' to adjust Oracle parameters. We are waiting .... and will post news soon.



[]s,
Luciano

[xs-]

Oracle as the same problem as postgresql, index pollution (insane amounts of inserts/updates/deletes).
If you reindex all tables (specially history and trends) once a day, performance will probably be good.
MySQL handles this differently (don't exactly know how), which normally would be bad, but in this case has a positive effect.

One of our oracle dba's had an idea to do dynamic index rebuilding / rotation or something (cant remember what he called it) for specific tables, we haven't tried it yet.