patrickesser
commented
1 year ago works for me. Thanks !
Closed sukhwinder33445 closed 9 months ago
patrickesser
commented
1 year ago works for me. Thanks !
gbin2265
commented
9 months ago Hello @sukhwinder33445
A question about icingadb for the module map. I work in full mode icingadb (monitoring disabled).
The MAP module works, but only if I am limited in the number of hosts (100 hosts) But I have over 6k of hosts and 200K of services.
I have made the adjustments to my code that you suggested in these 2 'pull requests'. https://github.com/nbuchwitz/icingaweb2-module-map/pull/117 https://github.com/nbuchwitz/icingaweb2-module-map/pull/116
I have already adjusted several parameters to my PHP but still get the internal error for my 6k hosts.
I started analyzing what requests are happening in the postgres database because I suspect that a lot of data is passed on to icingaweb2 that is not necessary
I see that it asks ALL my host vars (no filtering on the flatname value = 'geolocation'), host by host. I have more than 50 flatname's for every host, this is a lot of database lines that he has to process just to have the geolocation (300k)
Could it be that filtering is not handled correctly by icingadb to only request the lines with flatname = 'geolocation'
$hostQuery->filter(IplFilter::like('host.vars.geolocation', '*'));
$this->icingadbUtils->applyRestrictions($hostQuery);
I see this line in the debug of my postgresql where there is no filtering for the flatvalue = 'geolocation'
SELECT
sub_customvar_flat.id ,
sub_customvar_flat.environment_id ,
sub_customvar_flat.customvar_id ,
sub_customvar_flat.flatname_checksum,
sub_customvar_flat.flatname ,
sub_customvar_flat.flatvalue
FROM
customvar_flat sub_customvar_flat
INNER JOIN
host_customvar sub_customvar_flat_host_customvar
ON
sub_customvar_flat_host_customvar.customvar_id = sub_customvar_flat.customvar_id
INNER JOIN
host sub_customvar_flat_host
ON
sub_customvar_flat_host.id = sub_customvar_flat_host_customvar.host_id
WHERE
sub_customvar_flat_host.id = $1
regards, Geert
gbin2265
commented
9 months ago @sukhwinder33445
I also see that when querying the list of services (also for the hosts) it queries ALL fields from the database and not just the fields specified in the data controller.
Does the icingadb not accept the array of fields it only needs?
// get service data
$serviceQuery = $this->backend
->select()
->from('servicestatus', array(
'host_name',
'service_display_name',
'service_name' => 'service',
'service_acknowledged',
'service_state' => 'service_' . $this->stateColumn,
'service_last_state_change' => 'service_' . $this->stateChangeColumn,
'service_in_downtime'
))
->applyFilter(Filter::fromQueryString('_host_geolocation >'));
This is what I see about request in the postgresql
SELECT
service.id ,
service.environment_id ,
service.name_checksum ,
service.properties_checksum ,
service.host_id ,
service.name ,
service.name_ci ,
service.display_name ,
service.checkcommand_name ,
service.checkcommand_id ,
service.max_check_attempts ,
service.check_timeperiod_name ,
service.check_timeperiod_id ,
service.check_timeout ,
service.check_interval ,
service.check_retry_interval ,
service.active_checks_enabled ,
service.passive_checks_enabled ,
service.event_handler_enabled ,
service.notifications_enabled ,
service.flapping_enabled ,
service.flapping_threshold_low ,
service.flapping_threshold_high ,
service.perfdata_enabled ,
service.eventcommand_name ,
service.eventcommand_id ,
service.is_volatile ,
service.action_url_id ,
service.notes_url_id ,
service.notes ,
service.icon_image_id ,
service.icon_image_alt ,
service.zone_name ,
service.zone_id ,
service.command_endpoint_name ,
service.command_endpoint_id ,
service_state.service_id AS service_state_service_id ,
service_state.environment_id AS service_state_environment_id ,
service_state.state_type AS service_state_state_type ,
service_state.soft_state AS service_state_soft_state ,
service_state.hard_state AS service_state_hard_state ,
service_state.previous_soft_state AS service_state_previous_soft_state ,
service_state.previous_hard_state AS service_state_previous_hard_state ,
service_state.check_attempt AS service_state_check_attempt ,
service_state.severity AS service_state_severity ,
service_state.output AS service_state_output ,
service_state.long_output AS service_state_long_output ,
service_state.performance_data AS service_state_performance_data ,
service_state.normalized_performance_data AS service_state_normalized_performance_data ,
service_state.check_commandline AS service_state_check_commandline ,
service_state.is_problem AS service_state_is_problem ,
service_state.is_handled AS service_state_is_handled ,
service_state.is_reachable AS service_state_is_reachable ,
service_state.is_flapping AS service_state_is_flapping ,
service_state.is_overdue AS service_state_is_overdue ,
service_state.is_acknowledged AS service_state_is_acknowledged ,
service_state.acknowledgement_comment_id AS service_state_acknowledgement_comment_id ,
service_state.last_comment_id AS service_state_last_comment_id ,
service_state.in_downtime AS service_state_in_downtime ,
service_state.execution_time AS service_state_execution_time ,
service_state.latency AS service_state_latency ,
service_state.check_timeout AS service_state_check_timeout ,
service_state.check_source AS service_state_check_source ,
service_state.scheduling_source AS service_state_scheduling_source ,
service_state.last_update AS service_state_last_update ,
service_state.last_state_change AS service_state_last_state_change ,
service_state.next_check AS service_state_next_check ,
service_state.next_update AS service_state_next_update ,
service_host.id AS service_host_id ,
service_host.environment_id AS service_host_environment_id ,
service_host.name_checksum AS service_host_name_checksum ,
service_host.properties_checksum AS service_host_properties_checksum ,
service_host.name AS service_host_name ,
service_host.name_ci AS service_host_name_ci ,
service_host.display_name AS service_host_display_name ,
service_host.address AS service_host_address ,
service_host.address6 AS service_host_address6 ,
service_host.address_bin AS service_host_address_bin ,
service_host.address6_bin AS service_host_address6_bin ,
service_host.checkcommand_name AS service_host_checkcommand_name ,
service_host.checkcommand_id AS service_host_checkcommand_id ,
service_host.max_check_attempts AS service_host_max_check_attempts ,
service_host.check_timeperiod_name AS service_host_check_timeperiod_name ,
service_host.check_timeperiod_id AS service_host_check_timeperiod_id ,
service_host.check_timeout AS service_host_check_timeout ,
service_host.check_interval AS service_host_check_interval ,
service_host.check_retry_interval AS service_host_check_retry_interval ,
service_host.active_checks_enabled AS service_host_active_checks_enabled ,
service_host.passive_checks_enabled AS service_host_passive_checks_enabled ,
service_host.event_handler_enabled AS service_host_event_handler_enabled ,
service_host.notifications_enabled AS service_host_notifications_enabled ,
service_host.flapping_enabled AS service_host_flapping_enabled ,
service_host.flapping_threshold_low AS service_host_flapping_threshold_low ,
service_host.flapping_threshold_high AS service_host_flapping_threshold_high ,
service_host.perfdata_enabled AS service_host_perfdata_enabled ,
service_host.eventcommand_name AS service_host_eventcommand_name ,
service_host.eventcommand_id AS service_host_eventcommand_id ,
service_host.is_volatile AS service_host_is_volatile ,
service_host.action_url_id AS service_host_action_url_id ,
service_host.notes_url_id AS service_host_notes_url_id ,
service_host.notes AS service_host_notes ,
service_host.icon_image_id AS service_host_icon_image_id ,
service_host.icon_image_alt AS service_host_icon_image_alt ,
service_host.zone_name AS service_host_zone_name ,
service_host.zone_id AS service_host_zone_id ,
service_host.command_endpoint_name AS service_host_command_endpoint_name ,
service_host.command_endpoint_id AS service_host_command_endpoint_id ,
service_host_state.host_id AS service_host_state_host_id ,
service_host_state.environment_id AS service_host_state_environment_id ,
service_host_state.state_type AS service_host_state_state_type ,
service_host_state.soft_state AS service_host_state_soft_state ,
service_host_state.hard_state AS service_host_state_hard_state ,
service_host_state.previous_soft_state AS service_host_state_previous_soft_state ,
service_host_state.previous_hard_state AS service_host_state_previous_hard_state ,
service_host_state.check_attempt AS service_host_state_check_attempt ,
service_host_state.severity AS service_host_state_severity ,
service_host_state.output AS service_host_state_output ,
service_host_state.long_output AS service_host_state_long_output ,
service_host_state.performance_data AS service_host_state_performance_data ,
service_host_state.normalized_performance_data AS service_host_state_normalized_performance_data,
service_host_state.check_commandline AS service_host_state_check_commandline ,
service_host_state.is_problem AS service_host_state_is_problem ,
service_host_state.is_handled AS service_host_state_is_handled ,
service_host_state.is_reachable AS service_host_state_is_reachable ,
service_host_state.is_flapping AS service_host_state_is_flapping ,
service_host_state.is_overdue AS service_host_state_is_overdue ,
service_host_state.is_acknowledged AS service_host_state_is_acknowledged ,
service_host_state.acknowledgement_comment_id AS service_host_state_acknowledgement_comment_id ,
service_host_state.last_comment_id AS service_host_state_last_comment_id ,
service_host_state.in_downtime AS service_host_state_in_downtime ,
service_host_state.execution_time AS service_host_state_execution_time ,
service_host_state.latency AS service_host_state_latency ,
service_host_state.check_timeout AS service_host_state_check_timeout ,
service_host_state.check_source AS service_host_state_check_source ,
service_host_state.scheduling_source AS service_host_state_scheduling_source ,
service_host_state.last_update AS service_host_state_last_update ,
service_host_state.last_state_change AS service_host_state_last_state_change ,
service_host_state.next_check AS service_host_state_next_check ,
service_host_state.next_update AS service_host_state_next_update
FROM
service
LEFT JOIN
service_state
ON
service_state.service_id = service.id
LEFT JOIN
host service_host
ON
service_host.id = service.host_id
LEFT JOIN
host_state service_host_state
ON
service_host_state.host_id = service_host.id
WHERE
service.id IN (
(
SELECT
sub_customvar_flat_host_service.id AS sub_customvar_flat_host_service_id
FROM
customvar_flat sub_customvar_flat
INNER JOIN
host_customvar sub_customvar_flat_host_customvar
ON
sub_customvar_flat_host_customvar.customvar_id = sub_customvar_flat.customvar_id
INNER JOIN
host sub_customvar_flat_host
ON
sub_customvar_flat_host.id = sub_customvar_flat_host_customvar.host_id
LEFT JOIN
service sub_customvar_flat_host_service
ON
sub_customvar_flat_host_service.host_id = sub_customvar_flat_host.id
WHERE
(
sub_customvar_flat.flatname = $1)
AND (
sub_customvar_flat.flatvalue IS NOT NULL)))
ORDER BY
service.display_nameHello @sukhwinder33445,
As a non-PHP programmer, I started looking for where which data is called up.
What I see is that everything is stored in memory and only then is the filtering done for the geolocation or the icon.
This isn't a problem if you only have a few hosts and services, but not if you have a large environment.
I would like to find out that it is not possible to make one SQL statement that requests everything that is needed and not that there is a request per host that requests all flatname/value.
regards, Geert
sukhwinder33445
commented
9 months ago Hello @gbin2265,
Thank you for your report. Indeed, there are unnecessary queries for too long. I will open a new PR soon to fix this bug.
regards, Sukhwinder
sukhwinder33445
commented
9 months ago Hello @gbin2265,
The issues should be fixed with https://github.com/nbuchwitz/icingaweb2-module-map/pull/123. Can you please test this?
Thanks Sukhwinder
gbin2265
commented
9 months ago Hello @sukhwinder33445
First and foremost, many thanks for the support you want to give.
I have tested this version https://github.com/nbuchwitz/icingaweb2-module-map/pull/123/commits/9c0f962564315f75e42834fc39c41f007364e25c
I now see in the database that he retrieves all data from the db with one query.
I have this message now on the screen : Method "getFilter" does not exist and was not trapped in __call()
I only modified the Datacontroller.php file and the module.js I'm going to try to see why this doesn't work.
The version of icinga/icingaweb/icingaweb-db is the last snapshot available from this weekend.
regards, Geert
gbin2265
commented
9 months ago When i remove the line $this->Filter($serviceQuery, $this->getFilter());
then i see the first object on the screen and then it stays in a loop.
regards, Geert
fixes #114