New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Plugin main page is very long to display #2412

Closed
mohierf opened this Issue Jan 4, 2018 · 7 comments

Comments

Projects
None yet
5 participants
@mohierf
Collaborator

mohierf commented Jan 4, 2018

Currently, on my test server the plugin main page need almost 30 seconds to get displayed.

I found out that this is because of a very huge and not optimized SQL query built by the Task::getJobLogs function.

The SQL request:

SELECT task.`id` AS 'task.id', task.`name` AS 'task.name', job.`id` AS 'job.id', job.`name` AS 'job.name', job.`method` AS 'job.method', agent.`id` AS 'agent.id', agent.`name` AS 'agent.name', agent.`computers_id` AS 'agent.computers_id', run.`id` AS 'run.id', run.`itemtype` AS 'run.itemtype', run.`items_id` AS 'run.items_id', run.`state` AS 'run.state', log.`date` AS 'log.last_date', UNIX_TIMESTAMP(log.`date`) AS 'log.last_timestamp', log.`id` AS 'log.last_id', log.`comment` AS 'log.last_comment'
FROM `glpi_plugin_fusioninventory_agents` AS agent
INNER JOIN ( SELECT MAX(run.`id`) AS max_id, run.`plugin_fusioninventory_agents_id`, run.`plugin_fusioninventory_taskjobs_id`, run.`items_id`, run.`itemtype`, MAX(log.`id`) AS max_log_id
FROM ( SELECT *
FROM ( SELECT *, @num := IF(@agent_id = plugin_fusioninventory_agents_id && @taskjob_id = plugin_fusioninventory_taskjobs_id && @items_id = items_id && @itemtype = itemtype, @num:= @num + 1, 1) AS row_num, @agent_id:=plugin_fusioninventory_agents_id AS tmp_var1, @taskjob_id:=plugin_fusioninventory_taskjobs_id AS tmp_var2, @items_id:=items_id AS tmp_var3, @itemtype:=itemtype AS tmp_var4
FROM glpi_plugin_fusioninventory_taskjobstates
ORDER BY plugin_fusioninventory_taskjobs_id, plugin_fusioninventory_agents_id, itemtype, items_id, id DESC ) AS limited_states
WHERE row_num <= 2 ) AS run
LEFT JOIN `glpi_plugin_fusioninventory_taskjoblogs` AS log ON log.`plugin_fusioninventory_taskjobstates_id` = run.`id` GROUP BY run.`plugin_fusioninventory_agents_id`, run.`plugin_fusioninventory_taskjobs_id`, run.`items_id`, run.`itemtype`, run.`id` ) max_run ON max_run.`plugin_fusioninventory_agents_id` = agent.`id`
INNER JOIN `glpi_plugin_fusioninventory_taskjobstates` AS run ON max_run.`max_id` = run.`id`
LEFT JOIN `glpi_plugin_fusioninventory_taskjoblogs` as log ON max_run.`max_log_id` = log.`id`
INNER JOIN `glpi_plugin_fusioninventory_taskjobs` AS job ON job.`id` = run.`plugin_fusioninventory_taskjobs_id`
INNER JOIN `glpi_plugin_fusioninventory_tasks` as task ON job.`plugin_fusioninventory_tasks_id` = task.`id`
WHERE 1 AND task.`id` IN (0,6,12,13,14,15) GROUP BY job.`id`, agent.`id`, run.`id`, log.`id`
ORDER BY agent.`id` ASC, run.`id` DESC;

The EXPLAIN of this request:
image

I am not enough SQL aware to propose an optimization of this request 😞 Anyone ?

@Jean-Christophe-P

This comment has been minimized.

Jean-Christophe-P commented Jan 9, 2018

You could use the Debug function of GLPI.
It gives you a lot of info.

I just tried here and the query you are talking about takes 0.272s to execute.
My setup is not big (400pc, 54 snmp switches, 15 snmp printers)

@mohierf

This comment has been minimized.

Collaborator

mohierf commented Jan 9, 2018

I did used the debug mode 😉 to find out the guilty request.

On my configuration: 6000 computers (5000 agents), 6 tasks, the request needed 30 seconds to get executed

@mohierf

This comment has been minimized.

Collaborator

mohierf commented Jan 10, 2018

@wawax @ddurieux @trasher : What about removing the graph built after this query? This removal may be temporary... we will restore once the query got improved.

@wawax

This comment has been minimized.

Member

wawax commented Jan 10, 2018

this is what I proposed to @ddurieux yesterday

@trasher

This comment has been minimized.

Collaborator

trasher commented Jan 10, 2018

would be OK for me; I do not see a way to get that really fixed quickly

@mohierf

This comment has been minimized.

Collaborator

mohierf commented Jan 10, 2018

I will submit a PR for this today

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 10, 2018

@mohierf

This comment has been minimized.

Collaborator

mohierf commented Jan 18, 2018

Note that the same problem exist on the task "Job executions" tab because the same function getJobLogs is called. I think that we will have a real problem to report about tasks execution because of this ugly query ! 😞

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 18, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 18, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 19, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 19, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 19, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 19, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 19, 2018

mohierf added a commit to mohierf/fusioninventory-for-glpi that referenced this issue Jan 24, 2018

ddurieux added a commit to ddurieux/fusioninventory-for-glpi that referenced this issue Apr 16, 2018

ddurieux added a commit to ddurieux/fusioninventory-for-glpi that referenced this issue Apr 17, 2018

ddurieux added a commit to ddurieux/fusioninventory-for-glpi that referenced this issue Apr 18, 2018

ddurieux added a commit to ddurieux/fusioninventory-for-glpi that referenced this issue Apr 26, 2018

@ddurieux ddurieux closed this May 6, 2018

ddurieux added a commit that referenced this issue Jun 6, 2018

Closes #2412 - improve the get jobs log process (#2527)
* Closes #2412 - improve the get jobs log process

stonebuzz added a commit to stonebuzz/fusioninventory-for-glpi that referenced this issue Jul 25, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment