Skip to content
This repository has been archived by the owner. It is now read-only.

[dev.icinga.com #1110] dump configfilevariables and more multiline mbufs as bulk inserts #509

Closed
icinga-migration opened this issue Jan 12, 2011 · 12 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Jan 12, 2011

This issue has been migrated from Redmine: https://dev.icinga.com/issues/1110

Created by mfriedrich on 2011-01-12 15:53:26 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2011-08-01 21:40:38 +00:00)
Target Version: 1.6
Last Update: 2014-12-08 14:34:48 +00:00 (in Redmine)


dumping the configfilevariables (from icinga.cfg and resource.cfg)

this is been invoked in idomod.c

        case NEBCALLBACK_PROCESS_DATA:

                procdata=(nebstruct_process_data *)data;

                /* process has passed pre-launch config verification, so dump original config */
                if(procdata->type==NEBTYPE_PROCESS_START){
                        idomod_write_config_files();
                        idomod_write_config(IDOMOD_CONFIG_DUMP_ORIGINAL);
                        }

                /* process is starting the event loop, so dump runtime vars */
                if(procdata->type==NEBTYPE_PROCESS_EVENTLOOPSTART){
                        idomod_write_runtime_variables();
                        }

                break;

        case NEBCALLBACK_RETENTION_DATA:

                rdata=(nebstruct_retention_data *)data;

                /* retained config was just read, so dump it */
                if(rdata->type==NEBTYPE_RETENTIONDATA_ENDLOAD)
                        idomod_write_config(IDOMOD_CONFIG_DUMP_RETAINED);

                break;

where idomod_write_config_files does that.

idomod_write_main_config_file
idomod_write_resource_config_files


        if(asprintf(&temp_buffer
                 ,"\n%d:\n%d=%ld.%ld\n%d=%s\n"
                 ,IDO_API_MAINCONFIGFILEVARIABLES

(line by line all variables)
(write to sink)

        if(asprintf(&temp_buffer
                 ,"%d\n\n"
                 ,IDO_API_ENDDATA
                )==-1)
                temp_buffer=NULL;

this will be fetched into ido2db, where data is read from teh socket and stored into mbuf as buffer.

        /* special case for data items that may appear multiple times */

        case IDO_DATA_CONFIGFILEVARIABLE:
                ido2db_add_input_data_mbuf(idi,type,IDO2DB_MBUF_CONFIGFILEVARIABLE,newbuf);
                break;

and the handling stuff after finished the read triggered on ENDDATA ...

                        /* the current data section is ending... */
                        if(data_type==IDO_API_ENDDATA){

                                /* finish current data processing */
                                ido2db_end_input_data(idi);


(handle the fully written buffer line by line)


        case IDO2DB_INPUT_DATA_RESOURCECONFIGFILEVARIABLES:
                result=ido2db_handle_configfilevariables(idi,1);
                break;

into dbhandlers.c

the first call onto ido2db_query_insert_or_update_configfilevariables_add makes it questionable qhy an update must take place if updating everything which is within the where clause/the unique constraint.

this would be a if not existing then insert it, otherwise return the primary id (or sequence.curval).

maybe a call for a procedure doing exactly that.

but that's not the main point in here.

multiple insert combo

after having gotten the configfile_id the for loop over mbuf takes each line and does an insert into the table. so by means, single query for single line.

this should be written asl bulk query with normalized sql:

insert into icinga_configfilevariables (instance_id, configfile_id, varname, varvalue) values (1, 30, 'test1', 1), (1, 30, 'test2', 2), (1, 30, 'test3', 3);

building the values string together during the loop, and sending the query afterwards.
on oracle/pgsql this will affect the loss of param binding (but also possible in some way) in the first attempt, but since those queries are only run once at startup of the core, it shouldn't harm overall performance (1 insert instead of n lines).

the logic can be build upon issue #1108

and is considered to be applied for more than this example, but all possible multiline buffers.

Changesets

2011-07-22 15:31:22 +00:00 by mfriedrich be5d66e

dump configfilevariables multiline mbuf as bulk inserts #1110

this time, for mysql/pgsql via libdbi and oracle (ocilib)

needs proper testing.

refs #1110

2011-07-22 17:54:44 +00:00 by mfriedrich 456cd77

dump timeperiod timeranges multiline mbuf as bulk inserts #1110

refs #1110

2011-07-22 18:19:00 +00:00 by mfriedrich 4bd8fc1

idoutils: dump host contactgroups and parenthosts multiline mbuf as bulk inserts #1110

refs #1110

2011-07-22 18:35:26 +00:00 by mfriedrich 26518be

idoutils: dump service contactgroups multiline mbuf as bulk inserts #1110

refs #1110

2011-07-23 21:45:27 +00:00 by mfriedrich 621bb94

dump host/servicegroup memebers multiline mbuf as bulk inserts #1110

refs #1110

2011-07-23 21:51:13 +00:00 by mfriedrich 263f6d8

dump contactgroupmembers multiline mbuf as bulk inserts #1110

refs #1110

2011-07-23 22:01:21 +00:00 by mfriedrich cfbbbb9

dump runtimevariables multiline mbuf as bulk inserts #1110

refs #1110

2011-07-23 22:33:49 +00:00 by mfriedrich 7a17a66

idoutils: clean unused code from previous commits #1110

refs #1110

2011-07-24 18:26:59 +00:00 by Tommi 9a8f61c

idoutils: large oracle parts updated

new function for binding CLOBs to fix #1362
fixes missed long_output column in eventhandlers oracle column #1748
change perfdata columns to CLOB #1749
change logentry_data column to CLOB #1750
fixes usage of unsigned long values #1751
rewrite SQL and formatting #1377
refs #1362, #1748, #1749, #1750, #1751, #1377
refs #1110

2011-07-31 15:00:39 +00:00 by Tommi 42902613b36654d45b9cc0d892110bdfcc75edac

idoutils:oracle->replace mega sql multivalue strings with true bulk ops #1110
add handling array binds to error handler and bind dumper

refs #1110, #1377

2011-08-01 19:12:28 +00:00 by Tommi 791c141a210e84340bdff3972f80a50a7aaef614

idoutils: oracle fixes  #1110
-replace merge with true inserts
-move contactgroupmembers code to array
more some small enhancements, changes etc for R1.5 #1377
-adjust bind variable output printing only first row for arrays
-change some oracle debug output from level PROCESSINFO to SQL
-change hardcoded dbms_output buffersize to #define
fixes #1110
refs #1377

2011-08-02 18:13:38 +00:00 by Tommi fd2266e

idoutils:oracle->replace mega sql multivalue strings with true bulk ops #1110
add handling array binds to error handler and bind dumper

refs #1110, #1377

2011-08-02 18:14:27 +00:00 by Tommi 91b9d8b

idoutils: oracle fixes  #1110
-replace merge with true inserts
-move contactgroupmembers code to array
more some small enhancements, changes etc for R1.5 #1377
-adjust bind variable output printing only first row for arrays
-change some oracle debug output from level PROCESSINFO to SQL
-change hardcoded dbms_output buffersize to #define
fixes #1110
refs #1377

2011-08-03 09:10:10 +00:00 by Tommi b1fc92b1d42ab39a00abc0d583f56413afbfad0f

idoutils:  oracle fixes
fixes SEGV for runtimevariables #1110
remove batch error handling because of internal errors #1377
refs #1377, #1110

2011-08-03 09:52:19 +00:00 by Tommi 145ce4f6e933e7a9a2c757d49c67438f825420ce

idoutils:  oracle fixes
fixes SEGV for runtimevariables #1110
remove batch error handling because of internal errors #1377
refs #1377, #1110

2011-08-03 10:38:15 +00:00 by Tommi e9b668a

idoutils:  oracle fixes
fixes SEGV for runtimevariables #1110
remove batch error handling because of internal errors #1377
refs #1377, #1110

2011-08-03 10:38:32 +00:00 by Tommi 786ead8

idoutils:  oracle fixes
fixes SEGV for runtimevariables #1110
remove batch error handling because of internal errors #1377
refs #1377, #1110

Relations:

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jan 19, 2011

Updated by mfriedrich on 2011-01-19 17:20:31 +00:00

narf wrong issue id.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Feb 7, 2011

Updated by mfriedrich on 2011-02-07 15:26:04 +00:00

  • Target Version changed from 1.3 to 1.4
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Apr 27, 2011

Updated by mfriedrich on 2011-04-27 17:09:40 +00:00

  • Target Version changed from 1.4 to 1.5
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 12, 2011

Updated by mfriedrich on 2011-07-12 06:33:22 +00:00

  • Target Version changed from 1.5 to 1.6
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 22, 2011

Updated by mfriedrich on 2011-07-22 15:30:19 +00:00

for configfilevariables...

[1311348360.270817] [002.0] [pid=22029] INSERT INTO icinga_configfilevariables (instance_id, configfile_id, varname, varvalue) VALUES (1, 39, 'log_file', '/usr/local/icinga/var/icinga\.log'),(1, 39, 'cfg_file', '/usr/local/icinga/etc/objects/commands\.cfg'),(1, 39, 'cfg_file', '/usr/local/icinga/etc/objects/contacts\.cfg'),(1, 39, 'cfg_file', '/usr/local/icinga/etc/objects/timeperiods\.cfg'),(1, 39, 'cfg_file', '/usr/local/icinga/etc/objects/templates\.cfg'),(1, 39, 'cfg_file', '/usr/local/icinga/etc/objects/localhost\.cfg'),(1, 39, 'cfg_dir', '/usr/local/icinga/etc/modules'),(1, 39, 'object_cache_file', '/usr/local/icinga/var/objects\.cache'),(1, 39, 'precached_object_file', '/usr/local/icinga/var/objects\.precache'),(1, 39, 'resource_file', '/usr/local/icinga/etc/resource\.cfg'),(1, 39, 'status_file', '/usr/local/icinga/var/status\.dat'),(1, 39, 'status_update_interval', '10'),(1, 39, 'icinga_user', 'icinga'),(1, 39, 'icinga_group', 'icinga'),(1, 39, 'check_external_commands', '1'),(1, 39, 'command_check_interval', '-1'),(1, 39, 'command_file', '/usr/local/icinga/var/rw/icinga\.cmd'),(1, 39, 'external_command_buffer_slots', '4096'),(1, 39, 'lock_file', '/usr/local/icinga/var/icinga\.lock'),(1, 39, 'temp_file', '/usr/local/icinga/var/icinga\.tmp'),(1, 39, 'temp_path', '/tmp'),(1, 39, 'event_broker_options', '-1'),(1, 39, 'log_rotation_method', 'd'),(1, 39, 'log_archive_path', '/usr/local/icinga/var/archives'),(1, 39, 'use_daemon_log', '1'),(1, 39, 'use_syslog', '1'),(1, 39, 'use_syslog_local_facility', '0'),(1, 39, 'syslog_local_facility', '5'),(1, 39, 'log_notifications', '1'),(1, 39, 'log_service_retries', '1'),(1, 39, 'log_host_retries', '1'),(1, 39, 'log_event_handlers', '1'),(1, 39, 'log_initial_states', '0'),(1, 39, 'log_current_states', '1'),(1, 39, 'log_external_commands', '1'),(1, 39, 'log_passive_checks', '1'),(1, 39, 'log_external_commands_user', '0'),(1, 39, 'log_long_plugin_output', '0'),(1, 39, 'service_inter_check_delay_method', 's'),(1, 39, 'max_service_check_spread', '30'),(1, 39, 'service_interleave_factor', 's'),(1, 39, 'host_inter_check_delay_method', 's'),(1, 39, 'max_host_check_spread', '30'),(1, 39, 'max_concurrent_checks', '0'),(1, 39, 'check_result_reaper_frequency', '10'),(1, 39, 'max_check_result_reaper_time', '30'),(1, 39, 'check_result_path', '/usr/local/icinga/var/spool/checkresults'),(1, 39, 'max_check_result_file_age', '3600'),(1, 39, 'cached_host_check_horizon', '15'),(1, 39, 'cached_service_check_horizon', '15'),(1, 39, 'enable_predictive_host_dependency_checks', '1'),(1, 39, 'enable_predictive_service_dependency_checks', '1'),(1, 39, 'soft_state_dependencies', '0'),(1, 39, 'auto_reschedule_checks', '0'),(1, 39, 'auto_rescheduling_interval', '30'),(1, 39, 'auto_rescheduling_window', '180'),(1, 39, 'sleep_time', '0\.25'),(1, 39, 'service_check_timeout', '60'),(1, 39, 'host_check_timeout', '30'),(1, 39, 'event_handler_timeout', '30'),(1, 39, 'notification_timeout', '30'),(1, 39, 'ocsp_timeout', '5'),(1, 39, 'perfdata_timeout', '5'),(1, 39, 'retain_state_information', '1'),(1, 39, 'state_retention_file', '/usr/local/icinga/var/retention\.dat'),(1, 39, 'retention_update_interval', '60'),(1, 39, 'use_retained_program_state', '1'),(1, 39, 'use_retained_scheduling_info', '1'),(1, 39, 'retained_host_attribute_mask', '0'),(1, 39, 'retained_service_attribute_mask', '0'),(1, 39, 'retained_process_host_attribute_mask', '0'),(1, 39, 'retained_process_service_attribute_mask', '0'),(1, 39, 'retained_contact_host_attribute_mask', '0'),(1, 39, 'retained_contact_service_attribute_mask', '0'),(1, 39, 'interval_length', '60'),(1, 39, 'use_aggressive_host_checking', '0'),(1, 39, 'execute_service_checks', '1'),(1, 39, 'accept_passive_service_checks', '1'),(1, 39, 'execute_host_checks', '1'),(1, 39, 'accept_passive_host_checks', '1'),(1, 39, 'enable_notifications', '1'),(1, 39, 'enable_event_handlers', '1'),(1, 39, 'process_performance_data', '0'),(1, 39, 'obsess_over_services', '0'),(1, 39, 'obsess_over_hosts', '0'),(1, 39, 'translate_passive_host_checks', '0'),(1, 39, 'passive_host_checks_are_soft', '0'),(1, 39, 'check_for_orphaned_services', '1'),(1, 39, 'check_for_orphaned_hosts', '1'),(1, 39, 'service_check_timeout_state', 'u'),(1, 39, 'check_service_freshness', '1'),(1, 39, 'service_freshness_check_interval', '60'),(1, 39, 'check_host_freshness', '0'),(1, 39, 'host_freshness_check_interval', '60'),(1, 39, 'additional_freshness_latency', '15'),(1, 39, 'enable_flap_detection', '1'),(1, 39, 'low_service_flap_threshold', '5\.0'),(1, 39, 'high_service_flap_threshold', '20\.0'),(1, 39, 'low_host_flap_threshold', '5\.0'),(1, 39, 'high_host_flap_threshold', '20\.0'),(1, 39, 'date_format', 'us'),(1, 39, 'p1_file', '/usr/local/icinga/lib/p1\.pl'),(1, 39, 'enable_embedded_perl', '0'),(1, 39, 'use_embedded_perl_implicitly', '1'),(1, 39, 'stalking_event_handlers_for_hosts', '0'),(1, 39, 'stalking_event_handlers_for_services', '0'),(1, 39, 'illegal_object_name_chars', '`~!\$%\^&\*|\'\"<>\?,\(\)='),(1, 39, 'illegal_macro_output_chars', '`~\$&|\'\"<>'),(1, 39, 'use_regexp_matching', '0'),(1, 39, 'use_true_regexp_matching', '0'),(1, 39, 'admin_email', 'icinga@localhost'),(1, 39, 'admin_pager', 'pageicinga@localhost'),(1, 39, 'daemon_dumps_core', '0'),(1, 39, 'use_large_installation_tweaks', '0'),(1, 39, 'enable_environment_macros', '0'),(1, 39, 'debug_level', '0'),(1, 39, 'debug_verbosity', '1'),(1, 39, 'debug_file', '/usr/local/icinga/var/icinga\.debug'),(1, 39, 'max_debug_file_size', '100000000'),(1, 39, 'event_profiling_enabled', '0')
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 22, 2011

Updated by mfriedrich on 2011-07-22 15:57:08 +00:00

todo

  • runtimevariables (needs to be cleaned on (re)start)
[1311348367.459346] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'config_file', '/usr/local/icinga/etc/icinga\.cfg') ON DUPLICATE KEY UPDATE varvalue='/usr/local/icinga/etc/icinga\.cfg'
[1311348367.499761] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'total_services', '8') ON DUPLICATE KEY UPDATE varvalue='8'
[1311348367.541119] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'total_scheduled_services', '8') ON DUPLICATE KEY UPDATE varvalue='8'
[1311348367.582561] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'total_hosts', '1') ON DUPLICATE KEY UPDATE varvalue='1'
[1311348367.623977] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'total_scheduled_hosts', '1') ON DUPLICATE KEY UPDATE varvalue='1'
[1311348367.665430] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_services_per_host', '8\.000000') ON DUPLICATE KEY UPDATE varvalue='8\.000000'
[1311348367.706811] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_scheduled_services_per_host', '8\.000000') ON DUPLICATE KEY UPDATE varvalue='8\.000000'
[1311348367.748231] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'service_check_interval_total', '2400') ON DUPLICATE KEY UPDATE varvalue='2400'
[1311348367.789607] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'host_check_interval_total', '300') ON DUPLICATE KEY UPDATE varvalue='300'
[1311348367.831018] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_service_check_interval', '300\.000000') ON DUPLICATE KEY UPDATE varvalue='300\.000000'
[1311348367.872453] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_host_check_interval', '300\.000000') ON DUPLICATE KEY UPDATE varvalue='300\.000000'
[1311348367.913841] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_service_inter_check_delay', '37\.500000') ON DUPLICATE KEY UPDATE varvalue='37\.500000'
[1311348367.955273] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'average_host_inter_check_delay', '300\.000000') ON DUPLICATE KEY UPDATE varvalue='300\.000000'
[1311348367.996674] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'service_inter_check_delay', '37\.500000') ON DUPLICATE KEY UPDATE varvalue='37\.500000'
[1311348368.038093] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'host_inter_check_delay', '300\.000000') ON DUPLICATE KEY UPDATE varvalue='300\.000000'
[1311348368.079493] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'service_interleave_factor', '8') ON DUPLICATE KEY UPDATE varvalue='8'
[1311348368.120890] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'max_service_check_spread', '30') ON DUPLICATE KEY UPDATE varvalue='30'
[1311348368.162289] [002.0] [pid=22029] INSERT INTO icinga_runtimevariables (instance_id, varname, varvalue) VALUES (1, 'max_host_check_spread', '30') ON DUPLICATE KEY UPDATE varvalue='30'
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 23, 2011

Updated by mfriedrich on 2011-07-23 22:04:39 +00:00

  • Done % changed from 0 to 80

i'm leaving

  • host/service escalation contacts and contactgroups
  • contact addresses
  • customvariables and status

as is, as those are not that often used.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 24, 2011

Updated by Tommi on 2011-07-24 17:01:33 +00:00

for oracle its better to use real bulk inserts with arrays(OCI_BindArray* functions) on prepared statements instead of building a long sql with literals which will causing higher parsing load and poisoning the SGA.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 24, 2011

Updated by mfriedrich on 2011-07-24 17:53:50 +00:00

well i was not aware of that solution, either way, the combined union will increase performance with 1 query compared to 2000 queries e.g. for contactgroupmembers. if you prefer to have that binded instead, feel free to change it, it's out of my scope currently as i am working on pgsql mainly.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Jul 31, 2011

Updated by Tommi on 2011-07-31 15:28:56 +00:00

oracle bind array code added to my testido branch, replacing previous version. Because of partially changed logic, also libdbi code may affected, but shouldnt. It worked in my small environment but should be tested in bigger installations (5000+ services)

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Aug 1, 2011

Updated by Tommi on 2011-08-01 21:40:38 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 80 to 100

Applied in changeset 791c141a210e84340bdff3972f80a50a7aaef614.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:34:48 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category set to IDOUtils
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.