Hi everyone,
We'd like to make it clear that we have as a guideline for maintenance of GPLI not to make changes on its source code. Our intention with this discussion is to incentivate GLPI's developers to make changes on the source code so that it can solve issues like ours. We've been performing all orientations that you guys told us, unfortunately none of them solved our problem so far. We believe if your team review our sql query, we could get satisfactory results. Looking forward to hearing from you, Douglas Fernandes IT Specialist ----- Mensagem original ----- De: [email protected] Para: [email protected] Enviadas: Quinta-feira, 21 de maio de 2015 9:47:18 Assunto: Glpi-dev Digest, Vol 119, Issue 12 Send Glpi-dev mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit https://mail.gna.org/listinfo/glpi-dev or, via email, send a message with subject or body 'help' to [email protected] You can reach the person managing the list at [email protected] When replying, please edit your Subject line so it is more specific than "Re: Contents of Glpi-dev digest..." Today's Topics: 1. Re: Glpi-dev Digest, Vol 119, Issue 7 (Moron, Olivier) ---------------------------------------------------------------------- Message: 1 Date: Thu, 21 May 2015 12:47:00 +0000 From: "Moron, Olivier" <[email protected]> To: Liste de diffusion des developpeurs GLPI <[email protected]> Subject: Re: [Glpi-dev] Glpi-dev Digest, Vol 119, Issue 7 Message-ID: <[email protected]> Content-Type: text/plain; charset="utf-8" Hello, I made a try with a standard GLPI0.85.4 fresh instance and with 189 969 users in DB. Using the machine I already described: a Linux (Debian 8) virtual machine with MariaDB and Innodb (+ a 5GB query cache). And like this, your query took 8s to return results. And with the new indexes like proposed by Fredrico, it took 8.9s, so no improvement! But replacing in the query the function NOW() by a fixed date, then first query execution gave 8s, but next query executions gave 2.2ms ?! Even without the proposed indexes, the figures are first execution: 7.8s and next executions: 2.2ms! So the problem in this request is the NOW() function call which prevents optimizations (due to non-repeatable results I guess). ? You should not use the NOW() function in your queries Regards, Olivier MORON Miscellaneous Program Member RAYNET SNC Tel : +33 4 76 33 49 52 Fax: +33 4 76 70 56 63 From: Glpi-dev [mailto:[email protected]] On Behalf Of Frederico Gendorf Sent: Thursday, May 21, 2015 2:13 PM To: Liste de diffusion des developpeurs GLPI Subject: Re: [Glpi-dev] Glpi-dev Digest, Vol 119, Issue 7 Hi Douglas, you can optimize you database creating index for those columns `glpi_users`.`begin_date` `glpi_users`.`end_date` ALTER TABLE `glpi`.`glpi_users` ADD INDEX `begin_date` (`begin_date` ASC), ADD INDEX `end_date` (`end_date` ASC); try it and say to us the result! []s Frederico Gendorf IT Analyst ESSS - SIMULATING THE FUTURE | Florian?polis - SC - Brazil Office: +55 (48) 3953 0004 <tel:+554839530004> E-mail: [email protected]<mailto:[email protected]> | www.esss.com.br<http://www.esss.com.br/> [http://www.esss.com.br/signature/signature_esss.png] On Wed, May 20, 2015 at 4:10 PM, Douglas P. Fernandes <[email protected]<mailto:[email protected]>> wrote: Hi, friends. We made some tests on GLPI Tables and some assays regarding queries, too. This query bellow performed 15 sec to retrieve data from DB, what enforces our idea toward problem related to query criteria, not DB at all. SELECT DISTINCT `glpi_users`.* FROM `glpi_users` LEFT JOIN `glpi_useremails` ON (`glpi_users`.`id` = `glpi_useremails`.`users_id`) LEFT JOIN `glpi_profiles_users` ON (`glpi_users`.`id` = `glpi_profiles_users`.`users_id`) WHERE `glpi_users`.`id` > '1' AND ( `glpi_profiles_users`.`entities_id` IN ('105') OR (`glpi_profiles_users`.`is_recursive`='1' AND `glpi_profiles_users`.`entities_id` IN ('0')) ) AND `glpi_users`.`is_deleted` = '0' AND `glpi_users`.`is_active` = '0' AND (`glpi_users`.`begin_date` IS NULL OR `glpi_users`.`begin_date` < NOW()) AND (`glpi_users`.`end_date` IS NULL OR `glpi_users`.`end_date` > NOW()) ORDER BY `glpi_users`.`firstname`, `glpi_users`.`realname`, `glpi_users`.`name` LIMIT 0,100; We made, too, the change to InnoDB approach, no visible effects. It seens, anyway, a problem related really about how search criteria are constructed. Is there a possibility to optimize this query? Best regards, Douglas Fernandes IT Specialist Grupo de Governan?a de TIC Coordenadoria de Infraestrutura de Tecnologia da Informa??o e Comunica??o - COTEC SECRETARIA DO PLANEJAMENTO E GEST?O - SEPLAG GOVERNO DO ESTADO DO CEAR? ________________________________ De: [email protected]<mailto:[email protected]> Para: [email protected]<mailto:[email protected]> Enviadas: Quarta-feira, 20 de maio de 2015 11:57:41 Assunto: Glpi-dev Digest, Vol 119, Issue 7 Send Glpi-dev mailing list submissions to [email protected]<mailto:[email protected]> To subscribe or unsubscribe via the World Wide Web, visit https://mail.gna.org/listinfo/glpi-dev or, via email, send a message with subject or body 'help' to [email protected]<mailto:[email protected]> You can reach the person managing the list at [email protected]<mailto:[email protected]> When replying, please edit your Subject line so it is more specific than "Re: Contents of Glpi-dev digest..." Today's Topics: 1. Re: Evolutions GLPI - Appliquer une r?gle m?tiers ? la modification (David DURIEUX) 2. Re: GLPI with bootstrap template ( work in progress ) (Augusto Ferronato) 3. Re: Glpi-dev Digest, Vol 119, Issue 5 (Moron, Olivier) ---------------------------------------------------------------------- Message: 1 Date: Wed, 20 May 2015 16:37:32 +0200 From: David DURIEUX <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Subject: Re: [Glpi-dev] Evolutions GLPI - Appliquer une r?gle m?tiers ? la modification Message-ID: <[email protected]<mailto:[email protected]>> Content-Type: text/plain; charset=iso-8859-1 Le Wed, 20 May 2015 14:28:38 +0000 TEILLET Guillaume <[email protected]<mailto:[email protected]>> a ?crit: >J'ai oubli? de pr?ciser la version 0.84.4 et qu'il s'agit de la >modification du ticket. Sur la doc en ligne il est pr?cis? : " >Remarque : les r?gles m?tier pour les tickets ne sont jou?es qu'? la >cr?ation du ticket. Lors de la modification de celui-ci, aucun >m?canise automatique n'est lanc?." >http://www.glpi-project.org/DOC/FR/ ... C'est sur la derni?re stable que ?a a ?t? ajout? 0.85.x, donc effectivement, ?a ne fonctionnera pas en 0.84.x David ++ >Guillaume > >-----Message d'origine----- >De?: Glpi-dev >[mailto:[email protected]<mailto:[email protected]>] De la part >de David >DURIEUX Envoy??: mercredi 20 mai 2015 15:59 >??: [email protected]<mailto:[email protected]> >Objet?: Re: [Glpi-dev] Evolutions GLPI - Appliquer une r?gle m?tiers ? >la modification > > >Le Wed, 20 May 2015 13:53:43 +0000 >TEILLET Guillaume ><[email protected]<mailto:[email protected]>> a >?crit: > >>Bonjour, >>Serait-il possible de permettre d'appliquer une r?gle m?tiers ? la >>modification ? Merci. >>M TEILLET > >Oui les r?gles peuvent ?tre jou?es lors de la modification de ticket, >donc on ne comprend pas la question... > > >David >++ > >_______________________________________________ >Glpi-dev mailing list >[email protected]<mailto:[email protected]> >https://mail.gna.org/listinfo/glpi-dev > >_______________________________________________ >Glpi-dev mailing list >[email protected]<mailto:[email protected]> >https://mail.gna.org/listinfo/glpi-dev ------------------------------ Message: 2 Date: Wed, 20 May 2015 11:44:01 -0300 From: Augusto Ferronato <[email protected]<mailto:[email protected]>> To: Liste de diffusion des developpeurs GLPI <[email protected]<mailto:[email protected]>> Subject: Re: [Glpi-dev] GLPI with bootstrap template ( work in progress ) Message-ID: <cap_n9_kg_d_xyhtr5hnz-ymg2-o2f0ucrpsgnhsm2ger9bb...@mail.gmail.com<mailto:cap_n9_kg_d_xyhtr5hnz-ymg2-o2f0ucrpsgnhsm2ger9bb...@mail.gmail.com>> Content-Type: text/plain; charset="utf-8" Hello, Great job, Have some material in english? Regards, 2015-05-20 5:57 GMT-03:00 Bernd Kammlott <[email protected]<mailto:[email protected]>>: > Hey! > > i am working on a new Interface for GLPI and whould like to contribute > this Interface to the GLPI Project. > > What i am currently working on: > > - basic template in glpi style > - separate as mutch html code from php logic as possible > - integrate a baisic template system > - add the possibility to overwrite templates ( logo + css in CI style ) > - Template will be responsive for mobile access > > If there are people who whould like to contribute to this interface please > contact me. > > You can check out the current version of the interace on this page: > > For username and password and source check: > https://www.kaneo-gmbh.de/glpi-with-bootstrap-theme.html > > GLPI Interface: > https://www.kaneo-gmbh.de/glpi-demo/ > > best regards > Bernd > > > > _______________________________________________ > Glpi-dev mailing list > [email protected]<mailto:[email protected]> > https://mail.gna.org/listinfo/glpi-dev > > -- ____________________ Jos? Augusto Ferronato -------------- next part -------------- An HTML attachment was scrubbed... URL: </public/glpi-dev/attachments/20150520/3082d1a2/attachment.html> ------------------------------ Message: 3 Date: Wed, 20 May 2015 14:57:28 +0000 From: "Moron, Olivier" <[email protected]<mailto:[email protected]>> To: Liste de diffusion des developpeurs GLPI <[email protected]<mailto:[email protected]>> Subject: Re: [Glpi-dev] Glpi-dev Digest, Vol 119, Issue 5 Message-ID: <[email protected]<mailto:[email protected]>> Content-Type: text/plain; charset="utf-8" Hello, There can be many bottlenecks in such queries: 1) Server perfs: you must check how it behaves when doing the requests 2) mySQL perfs: same remark 3) PHP perfs: if server CPU is low and MySQL also then it can come from PHP computing, 4) Network perfs: at the end if nothing previously noted seems to fit the trouble, then it can come from network bandwith. At the end several solutions can be applied depending on the fine analysis done above ?. You may update your server so that you can use Apache2.4 and PHP5-FPM fastcgi, Which OS are you using? Windows or Linux? If Linux, you may also use HHVM instead of PHP5-FPM. HHVM is a JIT compiler for PHP (used by Facebook servers): speed gain is somewhere between 2 and 10. You can also use MariaDB (which is a binary replacement for MySQL (but with better perf). And you may convert myISAM tables to INNODB (which gives the possibility to load into memory the complete database, and such speed during queries). This is my 1 cent contribution ?, Regards, Olivier MORON Miscellaneous Program Member RAYNET SNC Tel : +33 4 76 33 49 52<tel:%2B33%204%2076%2033%2049%2052> Fax: +33 4 76 70 56 63<tel:%2B33%204%2076%2070%2056%2063> From: Glpi-dev [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Douglas P. Fernandes Sent: Wednesday, May 20, 2015 4:27 PM To: [email protected]<mailto:[email protected]> Subject: Re: [Glpi-dev] Glpi-dev Digest, Vol 119, Issue 5 Following informations, GLPI version: 0.85.4 Server (physical): CPU Intel Xeon E5620 2.40GHz, RAM 8GB (1.5GB free), Not Swap Web server: Apache 2.2.9-10 PHP release: 5.3.3-7 SQL application, and engine: MySQL 5.1.49-3 Queries: 1 - Search Requester in "Assistance>Tickets" (47 sec); 2 - Time to loading "Administration>Users" (12 sec); 3 - Search Users in "Administration>Users" (8 sec). Best regards, Douglas Fernandes IT Specialist Grupo de Governan?a de TIC Coordenadoria de Infraestrutura de Tecnologia da Informa??o e Comunica??o - COTEC SECRETARIA DO PLANEJAMENTO E GEST?O - SEPLAG GOVERNO DO ESTADO DO CEAR? ________________________________ De: [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> Para: [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> Enviadas: Quarta-feira, 20 de maio de 2015 10:02:09 Assunto: Glpi-dev Digest, Vol 119, Issue 5 Send Glpi-dev mailing list submissions to [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> To subscribe or unsubscribe via the World Wide Web, visit https://mail.gna.org/listinfo/glpi-dev or, via email, send a message with subject or body 'help' to [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> You can reach the person managing the list at [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> When replying, please edit your Subject line so it is more specific than "Re: Contents of Glpi-dev digest..." Today's Topics: 1. Request for analysis of response time issue (Douglas P. Fernandes) 2. Re: Request for analysis of response time issue (David DURIEUX) 3. Re: Request for analysis of response time issue (Moron, Olivier) ---------------------------------------------------------------------- Message: 1 Date: Wed, 20 May 2015 09:45:10 -0300 (BRT) From: "Douglas P. Fernandes" <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> To: [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> Subject: [Glpi-dev] Request for analysis of response time issue Message-ID: <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> Content-Type: text/plain; charset="utf-8" Dear Representative. We are committed to adopt GLPI here, at Seplag (Seplag is a State Secretary, State of Cear?). We have 150 thousand potential users, among all the public users. At assay phase, we found a problem regarding Requester, Ticket Form: search. It takes 47 seconds to retrieve data. The same search, at Administration ? User, takes about 08 (eight) seconds. It seems to us that a response time of 8 seconds is too much, let alone 47 seconds. We wonder if there's a way to enhance this search mechanism? If so, this would be decisive for our intent to adopt this excellent software. Best regards, Att, Douglas Fernandes Especialista em TI Grupo de Governan?a de TIC Coordenadoria de Infraestrutura de Tecnologia da Informa??o e Comunica??o - COTEC SECRETARIA DO PLANEJAMENTO E GEST?O - SEPLAG (85) 3101-4540<tel:%2885%29%203101-4540> / (85) 9121-9564<tel:%2885%29%209121-9564> -------------- next part -------------- An HTML attachment was scrubbed... URL: </public/glpi-dev/attachments/20150520/c4c799dd/attachment.html> ------------------------------ Message: 2 Date: Wed, 20 May 2015 14:56:56 +0200 From: David DURIEUX <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> To: [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> Subject: Re: [Glpi-dev] Request for analysis of response time issue Message-ID: <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> Content-Type: text/plain; charset=utf-8 Le Wed, 20 May 2015 09:45:10 -0300 (BRT) "Douglas P. Fernandes" <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> a ?crit: > > >Dear Representative. > Hi > > >We are committed to adopt GLPI here, at Seplag (Seplag is a State >Secretary, State of Cear?). We have 150 thousand potential users, >among all the public users. > > > > >At assay phase, we found a problem regarding Requester, Ticket Form: >search. It takes 47 seconds to retrieve data. The same search, at >Administration ? User, takes about 08 (eight) seconds. It seems to us >that a response time of 8 seconds is too much, let alone 47 seconds. >We wonder if there's a way to enhance this search mechanism? If so, >this would be decisive for our intent to adopt this excellent >software. It's not normal, give the version of: * PHP * MySQL Is the server swap ? David ++ > > >Best regards, > > >Att, > >Douglas Fernandes >Especialista em TI >Grupo de Governan?a de TIC >Coordenadoria de Infraestrutura de Tecnologia >da Informa??o e Comunica??o - COTEC >SECRETARIA DO PLANEJAMENTO E GEST?O - SEPLAG > >(85) 3101-4540<tel:%2885%29%203101-4540> / (85) >9121-9564<tel:%2885%29%209121-9564> > ------------------------------ Message: 3 Date: Wed, 20 May 2015 13:01:54 +0000 From: "Moron, Olivier" <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> To: Liste de diffusion des developpeurs GLPI <[email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>>> Subject: Re: [Glpi-dev] Request for analysis of response time issue Message-ID: <[email protected]<mailto:[email protected]<mailto:[email protected]>>> Content-Type: text/plain; charset="utf-8" Hello, I may help you, but I need a little bit more info about your configuration. Which GLPI version? What is the server (virtual or physical, CPU, RAM, HDD,?)? What is the web server? What is the PHP release? What is the SQL application, and engine (MySQL, MariaDB, MyISAM, INNODB)? What queries did you do to get this figures? ? Here is our current configuration: Server: Windows 2008 R2 (on a ESX VMWare) 64bits CPU: 4 cores RAM: 8GB HD: 20GB Web server: IIS7 (64bits) PHP: 5.4.14 64bits DB: MySQL 5.6.10 64bits DB Engine: myISAM DB size is 3GB Records figures: Ticket qt: 188640 Users: 7941 Entities: 970 Computers: 11462 Software: 231143 Logs: 15818092 They are some solution to improve Tickets and Users default requests, but you need to touch a little bit source files. Regards, Olivier MORON Miscellaneous Program Member RAYNET SNC Tel : +33 4 76 33 49 52<tel:%2B33%204%2076%2033%2049%2052> Fax: +33 4 76 70 56 63<tel:%2B33%204%2076%2070%2056%2063> From: Glpi-dev [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Douglas P. Fernandes Sent: Wednesday, May 20, 2015 2:45 PM To: [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> Subject: [Glpi-dev] Request for analysis of response time issue Dear Representative. We are committed to adopt GLPI here, at Seplag (Seplag is a State Secretary, State of Cear?). We have 150 thousand potential users, among all the public users. At assay phase, we found a problem regarding Requester, Ticket Form: search. It takes 47 seconds to retrieve data. The same search, at Administration ? User, takes about 08 (eight) seconds. It seems to us that a response time of 8 seconds is too much, let alone 47 seconds. We wonder if there's a way to enhance this search mechanism? If so, this would be decisive for our intent to adopt this excellent software. Best regards, Att, Douglas Fernandes Especialista em TI Grupo de Governan?a de TIC Coordenadoria de Infraestrutura de Tecnologia da Informa??o e Comunica??o - COTEC SECRETARIA DO PLANEJAMENTO E GEST?O - SEPLAG (85) 3101-4540<tel:%2885%29%203101-4540> / (85) 9121-9564<tel:%2885%29%209121-9564> -------------- next part -------------- An HTML attachment was scrubbed... URL: </public/glpi-dev/attachments/20150520/db40be19/attachment.html> ------------------------------ Subject: Digest Footer _______________________________________________ Glpi-dev mailing list [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> https://mail.gna.org/listinfo/glpi-dev ------------------------------ End of Glpi-dev Digest, Vol 119, Issue 5 **************************************** -------------- next part -------------- An HTML attachment was scrubbed... URL: </public/glpi-dev/attachments/20150520/e03a68cf/attachment.html> ------------------------------ Subject: Digest Footer _______________________________________________ Glpi-dev mailing list [email protected]<mailto:[email protected]> https://mail.gna.org/listinfo/glpi-dev ------------------------------ End of Glpi-dev Digest, Vol 119, Issue 7 **************************************** _______________________________________________ Glpi-dev mailing list [email protected]<mailto:[email protected]> https://mail.gna.org/listinfo/glpi-dev -------------- next part -------------- An HTML attachment was scrubbed... URL: </public/glpi-dev/attachments/20150521/a099b683/attachment.html> ------------------------------ Subject: Digest Footer _______________________________________________ Glpi-dev mailing list [email protected] https://mail.gna.org/listinfo/glpi-dev ------------------------------ End of Glpi-dev Digest, Vol 119, Issue 12 *****************************************
_______________________________________________ Glpi-dev mailing list [email protected] https://mail.gna.org/listinfo/glpi-dev
