Our community support channels provide interesting insights into how things are being monitored in various environments. Sometimes it is not only about finding the right configuration syntax or fiddling with the perfect cluster setup. This time I’d like to share a solution for a common problem that I discovered while I was helping another Icinga user. 🙂 (more…)
As mentioned in the last post, there are other improvements for Icinga and IDOUtils.
This time, I want to give you a deeper look onto database performance and the housekeeping stuff.
As you might know, selecting, updating or even deleting a row from a table heavily depends on the row count. If table size grows bigger e.g. in the historical tables from IDOUtils, those queries will be slower and hold back the main process. Current approach of IDOUtils is one forked child of ido2db for one idomod connection – working sequentially on the gotten data.
So even one select taking longer will slow down the data processing and worst case the socket will get blocking and idomod complains about writing to data sink.
But how to resolve those issues?
First of all there were several approaches originally found in mysql-mods.sql – setting indexes on table columns which are being used within the WHERE clause. Regarding the fact that ido2db is not just an insert application, but also deletes historical data on demand (table trimming options), selects objects for caching and furthermore updates existing rows (service/hoststatus e.g.) we decided to apply most useful indexes on the table creation statements. It does slow down an insert a bit, but the overall benefit is much bigger than that 🙂
Also the upcoming Icinga Web benefits from that – e.g. the logentries tables select performs a lot faster when using the API and a RDBMS.
But that’s not all – indexes are only one approach of improvement. In the last few months, Hendrik, Christoph and myself discussed a lot about the periodic housekeeping. The basic approach was to remove housekeeping function from the main data processing. Simply because historical deletes on large tables will take even longer and prevent new data being written to the database.
There have been discussions about a cronjob and seperated forked processes for housekeeping, but we wanted something within ido2db and simple to use. So Hendrik came up with the idea to create an own thread within each ido2db child which runs completely seperated from the main data processing flow – the so-called threaded housekeeper.
The thread just waits for the appropriate instance getting connected and then performs the periodic housekeeping – independant from the main flow. And it does not interfere with the normal data processing. So to speak it resolves a big performance issue within IDOUtils.
Basically, this is the way it performs:
- sleep a while after creation and intialization
- idle wait for database connection and connected instance from main process
- perform periodic maintenance not interferring with main process
- will be terminated when ido2db shuts down
Best thing so far – it has been implemented and tested and improved quite a while. Mostly done in our own git branches, but the final solution is within current git master and will be one of the outstanding new features for Icinga IDOUtils in the upcoming Icinga 1.0.1 release.
Stay tuned for more updates!
… and prepare for Icinga 1.0.1! =)
December 16 2009: Today the Icinga Team releases the Icinga Core 1.0. This is a milestone for both the team and the project as a whole. After many months of hard work we are proud to bring you a stable, alternative monitoring solution. This release includes many changes as suggested by the community and in particular the inclusion of Oracle in IDOUtils.
With just as many new improvements, Icinga Web UI has hit release 0.9.1 alpha. We have added a makefile for easier installation and fixed installation permission and cache problems. More changes are still to come, including an ExtJS update to 3.0.3. See below for the full list of new developments across Icinga Core, API, Docs and Web.
As we are always eager to keep the momentum going, we have decided to release the stable Icinga Core alongside the Icinga Web 0.9.1 alpha. These two will converge again in the coming months to a uniform release status. Till then, we hope you like the latest improvements.
- Improved IDOUtils with Oracle
Added prepared statements for most called queries
Split code into ocilib OR libdbi, to allow oracle to decide which rdbm lib will be used during configuration
- idoutils: fixed duplicate rows in table system commands, timed events, timed event queue (missing unique keys)
- idoutils: added upgrade path/sql queries for unique key failure – check docs for more information
- idoutils: changed default data_processing_options in idomod.cfg
- idoutils: fixed this version and perl path generation in db install scripts
- idoutils: fixed save custom variables segfault
- Updates and fixes for quickstart guides
- New section on upgrading Icinga & IDOUtils
- Revised section for Icinga Web
- Restructured DB access for upcoming RDBM support
- Made several fixes for table prefix, exception handling
- Started a ‘how-to’ guide for upcoming documentation
- Added makefile for easier installation
- Fixed installation permission and cache problems
- Modified .htaccess
- Removed yui
- Removed php notice warnings (isset, undef vars)
- In the process of changing API result keys to uppercase
- In the process of updating ExtJS to 3.0.3
- Introducing commands through the web
Should you find any issues, please report them to the following links:
As always we look forward to your feedback, so feel free to drop us a comment.