[dev.icinga.com #1100] only insert service and host checks when they are finally processed, increase performance by replacing insert/update with single insert #502
Comments
Updated by mfriedrich on 2011-01-11 20:32:39 +00:00 provided patch uses a not normalized query though, keep that in mind when applying, needs to be implemented like it should on sql standards. |
Updated by Anonymous on 2011-01-11 21:27:59 +00:00 I have no understanding of SQL queries, but cutting the number of update queries in half and make them inserts instead, sounds to me like a big performance increase =) |
Updated by mfriedrich on 2011-01-19 15:26:01 +00:00 for oracle, this will need a replaced prepared statement, and the normal bindings. since this is a reduction/renaming of current code (MERGE holds the needed INSERT, and the binded values are multiple available), this is rather easy to implement on the current code basis. best would be to apply that in several steps - mysql, then pgsql, then oracle. and if it works out, then for hostchecks too. |
Updated by mfriedrich on 2011-01-19 15:42:11 +00:00 ok, hostchecks is rather similar. as an addon to the above explainations: stepping into base/checks.c on hostchecks INITIATE
PROCESSED
see? end_time is blanked out by the core next to other stuff, sending unfinished data to ido2db. so taking the fully processed *check will get all available data from
so thanks again to Opsview, this patch really makes sense :-) |
Updated by mfriedrich on 2011-01-19 15:43:59 +00:00
|
Updated by mfriedrich on 2011-01-19 15:50:11 +00:00 ok, for oracle i made a good choice a while ago - the prepared statements are named modular, it's only the function called for preparing/freeing the statements in db.c then. the binding stays unchanged in dbqueries.c
|
Updated by mfriedrich on 2011-01-19 17:25:39 +00:00 mysql and posgres inserts are luckily the same as idoutils queries are normalized. i made my tests with postgres in the first place on the hostchecks - that looks good. postgres looks good.
servicechecks too.
|
Updated by mfriedrich on 2011-01-19 17:30:41 +00:00 now for mysql,
the reason the first rows take longer - the config dump takes ages and screws the check latency. so after the core is in sync with idoutils, it runs flawlessly. oracle will be an overnight express test.
|
Updated by mfriedrich on 2011-01-20 08:47:40 +00:00
oracle is fine too. servicechecks
hostchecks
|
Updated by mfriedrich on 2011-01-20 14:08:09 +00:00
works for me. will backport the hostchecks for opsview too.
|
Updated by mfriedrich on 2014-12-08 14:34:46 +00:00
|
This issue has been migrated from Redmine: https://dev.icinga.com/issues/1100
Created by mfriedrich on 2011-01-11 20:27:57 +00:00
Assignee: mfriedrich
Status: Resolved (closed on 2011-01-20 14:08:09 +00:00)
Target Version: 1.3
Last Update: 2014-12-08 14:34:46 +00:00 (in Redmine)
this approach makes use of reducing the insert on duplicate key update queries straight into insert queries.
the common approach is to just take the final resultset, and not keep started values up into the database (which will trigger the update statement as a unique key violation will take place).
taking this a step further, this will remove an update query from postgres too, and revoke the merge trick in oracle to a simple insert prepared statement.
consider this a patch for 1.3.0 unstable, like some other opsview patches from their svn too.
http://labs.opsview.com/2010/07/opsview-is-75-faster-than-a-standard-nagios-database-implementation/
https://secure.opsera.com/wsvn/wsvn/opsview/trunk/?#path\_trunk\_
https://secure.opsera.com/wsvn/wsvn/opsview/trunk/opsview-base/patches/?#path\_trunk\_opsview-base\_patches\_
https://secure.opsera.com/wsvn/wsvn/opsview/trunk/opsview-base/patches/ndoutils\_no\_unique\_key\_on\_servicechecks.patch
Attachments
Changesets
2011-01-20 09:23:38 +00:00 by mfriedrich 20b6343
The text was updated successfully, but these errors were encountered: