IDOUtils queries differ quite a lot – some of the are just executed during startup, while others happen all the time. By analyzing the performance on our Oracle database with grid it came to the top queries just like for
- servicechecks, servicestatus
- hostchecks, hoststatus
But how to improve the performance of those queries when they are called all the time?
Well, the query as is is always the same, only the values happen to change. So the basic idea is to prepare the statements with value place holders and if it comes to the query, just to bind the paramaters (values) to the prepared statement and execute that. This is a real performance boost compared to putting the query within the rdbm cache all the time.
Generally speaking the query statements are prepared after database connection and the statement handle is stored within the global dbinfo object (where the connection handler resides too).
dbinfo.oci_statement_programstatus = OCI_StatementCreate(dbinfo.oci_connection);
OCI_Prepare(dbinfo.oci_statement_programstatus, MT("MERGE INTO table USING DUAL ON (v1=:X1) WHEN MATCHED THEN UPDATE SET v2=:X2 WHEN NOT MATCHED THEN INSERT (v1, v2) VALUES (:X1, :X2)"))
When a query should be executed, all values will be binded (X1, X2) to the statement.
OCI_BindUnsignedBigInt(dbinfo.oci_statement_programstatus, MT(":X1"), (big_uint *) value1)
OCI_BindString(dbinfo.oci_statement_programstatus, MT(":X2"), (char *) value2)
Then the query gets executed.
Well it sounds quite simple but regarding the architecture of *DOUtils it was a hard nut to crack. The most common problem was the query buffer building – each unixtimestamp conversion is done before query building and sending the query. That does not fit for prepared statements where the whole query is pushed into the database cache.
Within the code, there is an char* array which gets the SQL-code from ndo2db_db_timet_to_sql and this is then printed to the whole statements. Not very useful since you may paste that right within each query. For the prepared statements, I’ve added all plain unixtimestamps to the data array and then binding the values directly.
(SELECT unixts2date(:X3) FROM DUAL)
So the bind param task has been done for the initial steps, improved delete statements and other improvements need to be implemented.
Another thing which was quite nasty is that Oracle support was dependant on libdbi, but it was not even used. So I decided to split the code completely and change configure. If you use –enable-oracle it will only require ocilib to work, it does not complain about a missing libdbi. The other way around it also works fine just like it was.
Conclusion to that – you won’t need libdbi to get Oracle support for Icinga IDOUtils – just ocilib.
Those improvements have been pushed to actual GIT master und you are very welcome to test and report bugs! =)
work was not getting better and getting Oracle to work was on hold. So I decided to push a night of coding after I had prepared the MERGE queries.
And yes, it was successful – initial support for Oracle is done!
Oracle driver requirements
You”ll catch the problems with libdbi and Oracle in older blog posts. The new driver proposed by myself was ocilib, developed by Vincent Rogier. Currently it’s kind of a break up, so you need to have the libdbi installed and then install ocilib as an add-on (and Oracle libs and includes i.e. the Oracle Instant Client). As far as I know ocilib is not in the repositories right now so get the latest version and compile them yourself (documentation is really good!).
Modifying Configure for Oracle
If you enable IDOUtils during configure you now have the opportunity to use the flag –enable-oracle – if you didn’t install ocilib to default path /usr/local/ [lib/include] you can use
to point configure to ocilib. It will be linked at runtime so you do not need to tell configure where $ORACLE_HOME and $LD_LIBRARY_PATH relays. Configure will output the following
- export LD_LIBRARY_PATH in ido2db Initscript where ocilib resides (OCI_IMPORT_RUNTIME)
- enable #define USE_ORACLE and ocilib.h for the compiler
- create ido2db.cfg-sample with Oracle support
After that you can perform a normal install.
Whencompiling the code it heavily depends on #define USE_ORACLE – if you plan to change back to another RDBM using libdbi you’ll have to issue
# make distclean
# make clean
# ./configure --enable-idoutils
Oracle Database Setup
In module/idoutils/db/ you will find oracle.sql and oracle-drop.sql. The first one creates the table definitions, a time conversion function and all the triggers and sequences needed for commonly used insert ids. oracle-drop.sql is just for testing purposes and cleans the database scheme.
Make sure you setup the Oracle DB with an appropriate scheme with username/password. Then copy oracle.sql to your $ORACLE_HOME at the db server and import it e.g. by using sqlplus
# su - oracle
$ sqlplus dbuser/dbpass
Then edit your ido2db.cfg for using Oracle. Please note that Oracle ignores the db_host, instead point db_name to //DBSERVER/DBNAME
That should do the trick. If you are experiencing problems turn the debug_level=-1 and debug_verbosity=2 and make sure the max_debug_file_size is set to at least 100 MB – the improved debug output will put a lot of output into that file.
Changes to the code
The biggest part has been done already getting Postgres to work – rewrite the INSERT OR UPDATE queries and extract the unique constraints for UPDATE conditions. Those queries have been adapted to use the MERGE trick. Some queries tried to issue an UPDATE where the unique constraint contained a value to be updated too. That failed heavily but introduces a really nice feature of ocilib.
By using OCI_Initialize it is possible to register an error handler function. This function simply gets the last OCI error and writes that to syslog and debug output. So it is really easy to find out why queries will fail and the nice thing is – the function doesn’t need to be called everywhere, just register it to ocilib.
Another heavy task was getting the insert id – MySQL supports last_insert_id but Postgres and Oracle don’t. In Postgres it’s rather easy defining the PK as SERIAL and getting the sequence id in order to get the last insert id. For Oracle, there are several ON INSERT TRIGGERs defined in oracle.sql which auto increment the id (primary key). Given that a specified function reads that values from the opened session.
Simply said, you do the following for an Oracle DB query in IDOUtils
- OCI_Initialize(ido2db_ocilib_err_handler, NULL, OCI_ENV_DEFAULT)
- oci_connection = OCI_ConnectionCreate(dbname,username, password, OCI_SESSION_DEFAULT);
- oci_statement = OCI_StatementCreate(oci_connection);
- OCI_ExecuteStmt(oci_statement, MT(“SELECT * FROM ….”));
- oci_resultset = OCI_GetResultset(oci_statement);
- instance_id = OCI_GetUnsignedInt(idi->dbinfo.oci_resultset, 1);
There were some other minor and major changes to the code…
- changed NOW() to SYSDATE
- modify table serviceescalation_contactgroups to serviceescalationcontactgroups (30 chars max in Oracle)
- primary key only is id anstead of [tablename]_id (30 chars max in Oracle)
- dropped table_prefix – ido2db.cfg setting will be ignored
- long_output uses CLOB (Character Large Object) since varchar2 supports 4000 bytes at maximum
I’ve also added a runtime version check for ocilib – if the library contains errors and does not export symbols correctly ido2db will quit correctly.
This is the initial version of Oracle support for Icinga IDOUtils. It was a bunch of work but there are many things to follow:
- rewrite heavily used queries (host/service/check/status, timedevents) to prepared statements and parameter bindings
- improve housekeeping DELETE queries with partitioned COMMITs
- improve getting the insert ids
- do not depend on libdbi if –enable-oracle is used
Oracle support for IDOUtils will be in Icinga 1.0 RC – watch out for the upcoming release and have fun testing! Please report any bugs or feature requests to the mailinglists and/or our dev tracker!
Many thanks to David Schmidt for implementing the first version of NDOUtils Oracle, many ideas have been improved within here. And also many many many thanks to Vincent Rogier for implementing such a great Oracle driver within the project ocilib. It is a pleasure coding based on ocilib, reading the documentation meanwhile and getting instant support for free! 🙂
Since this is the third RDBM to be maintained by only 2 Core team members, please contact us if you like to participate and/or help us improving more RDBM support! 🙂
Since I’m very busy at work this time I just wanted to give you some feedback on my current attempt – IDOUtils with Oracle based on ocilib.
Regarding that libdbi currently doesn’t provide a stable Oracle driver and furthermore, the main interface doesn’t allow prepared statements and parameter bindings, ocilib should be used and enabled during configure.
Currently, I am preparing a version which works as an addon to IDOUtils with libdbi, meaning that the libdbi still has to be installed and optional ocilib. Oracle support with ocilib will be enabled using –enable-oracle during configure.
I have chosen to let ido2db link against ocilib at runtime which gives the benefit of only loading the ocilib and based on that the Oracle libs. The configure flag enables the include directory /usr/local/include where ocilib.h resides and the library directory /usr/local/lib with ocilib. In the first initial version, the path will be hard coded, later I will implement configure flags just as –with-ocilib-lib/inc for more customizations.
The code within configure also enables the include statement for ocilib.h and the #define USE_ORACLE which will be used in the Code to point to ocilib DB handling instead of libdbi – just as NDOUtils Oracle does.
Meanwhile because of the linking at runtime it is required to hand the library path over to ido2db since it’s not in systems default library path. If not exported before running ido2db within the init script, error will look like this:
# /etc/init.d/ido2db start
Starting ido2db:/opt/icinga/bin/ido2db: error while loading shared libraries: libocilib.so.3: cannot open shared object file: No such file or directory
And start of ido2db simply fails because of the missing LD_LIBRARY_PATH. This path is defined running configure, so I have modified the rc.ido2db.in to export this environment variable with the concurrent ocilib library path.
#add ocilib lib path to link at runtime if enabled
@ido2db_runtime@ will be defined running configure, the basic setting is done in configure.in (and running autoconf 2.61 afterwards to generate configure script).
dnl create ido2db_runtime for rc.ido2db.in
RHEL’s autoconf is quite old (2.59) so I compiled my own static version using this article.
The reason why I am working on RHEL is quite simple – it’s in a VM and already configured with NDOUtils Oracle so the working environment (Oracle DB connection, etc) is already there 🙂
So for the first part, ocilib initialization during build is enabled and working. My next steps are
* adapt DB init, connection, passing queries to ocilib
* add Oracle based MERGE queries for the ON DUPLICATE KEY MySQL queries
* rewrite heavy queries to prepared statements/parameter bindings
* improve housekeeping DELETE sequences (since ido2db dies regularly when deleting more thant 500k queries at startup…)
Stay tuned! =)
Since we decided to give you the opportunity to test the new IDOUtils with re-engineered code for more RDBMs and Postgres support too we were aware of the fact that there would have been bugfixes to apply 😉
There were several issues to fix, not only Postgres but also typos and rewritten queries. We also added some improvements to the code, to mention configure support for manual libdbi detection and also the new version handling for IDOUtils. It’s now the same als Icinga Core, the old fashioned 1.4b8 has been dropped since we implemented a lot of new stuff to the original code.
Following the Changelog what’s been done so far:
- idoutils: fix failure_prediction_options in tables hosts, services for postgres
- idoutils: add configure support for manual libdbi detection
- idoutils: fix typo laste_state (wrong column)
- idoutils: fix buffer freeing with NAGIOS_SIZEOF_ARRAY
- idoutils: delete old mysql files coming from ndoutils
- idoutils: add new version handling, idomod/ido2db now shows version of Icinga core
- idoutils: rename existing mysql/pgsql files, added upgrade/fix files
- idoutils: fix wrong typecast in servicestatusdata_add, no more segfault while query preparing
- idoutils: add experimental db trimming interval option in ido2db.cfg
- core: fix wrong copyright url for Ethan
- docu: fix quickinstall guides
Those changes and improvements to the code are worth a new release!
Thanks for testing and reporting bugs and improvements to our mailinglists, dev tracker and here in the blog! =)
PS: Concerning Oracle, I’ve started preparing the code in another branch (edit Makefile for ocilib, rewrite db connection, add parameter bindings for queries and so on). I hope this will be done within the next few weeks and maybe we’ll catch up with another Icinga release before 1.0 alpha 🙂
After a long time debugging, code re factoring, implementing and testing new stuff another success story for Icinga!
Let’s start with the details 🙂
IDOUtils is a “simple” kind of INSERT, UPDATE and DELETE application so no worries about difficult SELECT and JOIN. Meanwhile the biggest problem are the queries which are not normalized and truly MySQL specific. Since Hendrik provided the normalization of the INSERT queries I stepped further into the INSERT OR UPDATE queries. Those are non-standard and i.e. Postgres or Oracle do not support them.
Oracle queries can be taken from NDOUtils Oracle mostly using a trick to MERGE statements (props to David Schmidt!), but the main goal is to rewrite all of them to fit parameter bindings. Check this article for more information.
Postgres is a bit more difficult because MERGE or UPSERT (another approach to INSERT OR UPDATE) is not yet supported. The documentation points to a function written in plpgsql.
The biggest problem – how to break up the code and create functions which are deciding upon RDBM which query to build and execute?
My first approach using on single function which gets different queries as string and decides what to do was a nice try but simply failed looking at the design – being generic and making the code more readable, too.
So I tried it the hard way and split the code for the SQL-queries from dbhandler.c into a new file called dbqueries.c – for the INSERT OR UPDATE queries for the first part.
One single function for each query has been implemented and performs then the RDBM specific query. For Postgres I decided to try an UPDATE in the first place and if there are now rows affected an INSERT will be issued instead. Unique constraints defined in table scheme are matching the UPDATE constraints.
Furthermore there were other issues to fix for Postgres – string escaping, Unix time conversion functions and the task of getting the last insert ID. MySQL doesn’t use defined sequence ids (dunno yet how last insert id works…) but Postgres does that for each table. After a hard night debugging I fixed that too – now the table relationship is working for Postgres too.
Concerning the fact that each value is stored in the data array it’s quite simple to implement more queries for more RDBMs – MySQL and Postgres will take care of giving you examples on how to build the queries 🙂
You’re very welcome to test Postgresql in upcoming Icinga 0.8.3!
Please report bugs or feature request to our mailinglists and at our development section.
Looking forward to Oracle and ocilib =)