Page 1 of 1

[SOLVED][3.1] Dropdown SQL Performance

Posted: Mon Oct 17, 2016 8:26 am
by alexisjavier
Hello,

I installed a new server with 3.1 on CentOS Linux (Stack 205)
My previous version was 3.0 on Windows Server 2012.

I all work fine except when I use a combo which other fields depend on, I get a noticeable loss in performance comparing to previous server.

In this example I have 3 Fields and 1 Dropdown.
Code: Select all
DROPDOWN (@@tipoPermiso): SELECT CADENA, DESCRIPCION FROM `PMT_WORKFLOWS` WHERE SECTOR = @@uSector
Field 1: SELECT CADENA FROM `PMT_WORKFLOWS` WHERE CADENA = @@tipoPermiso 
Field 2 ( @@Cadena): SELECT APROBADORES FROM `PMT_WORKFLOWS` WHERE CADENA = @@tipoPermiso
Field 3:SELECT CONCAT (APROBADOR_1, '  ', APROBADOR_2, '  ', APROBADOR_3, '  ', APROBADOR_4, '  ', APROBADOR_5) from PMT_WORKFLOWS WHERE CADENA = @@Cadena
The problem is that the browser will stay "blocked" 5 seconds while changing the dropdown value. If i do exacly same dynaform on the previous version, on windows, the change is almost instant.

On PM3.0 (Windows Server)
windows(PM3.0).gif
windows(PM3.0).gif (48.15 KiB) Viewed 1985 times
On PM3.1 (CentOS)
linux(PM 3.1).gif
linux(PM 3.1).gif (41.45 KiB) Viewed 1985 times
If I add more fields depending on this value the problem gets worse.

Could it be something with PHP or Apache, or maybe with the new PM version?

Thank you!
Alexis.

Re: [3.1] Dropdown SQL Performance

Posted: Mon Oct 17, 2016 6:29 pm
by amosbatto
Are you using MySQL 5.5 which you installed with code from the MySQL web site or are you using MySQL from the CentOS repository?

Have you checked the MySQL error log at /var/log/mysqld.log ?

Also check if you enabled logging inside MySQL's /etc/my.cnf file:
Code: Select all
general_log_file='/var/log/mysql_queries.log'
general_log = on 
global log_output = 'file'
Also check whether you enabled debug or debug_sql in your env.ini file for ProcessMaker.

Try adding this line your my.cnf file:
skip-name-resolve

Another possibility is that you have some firewall which is slowing down MySQL queries.

If none of that helps you, then turn on logging in your my.cnf file to see if you see anything strange. Also, read this:
http://www.tecmint.com/mysql-mariadb-pe ... timization

Re: [3.1] Dropdown SQL Performance

Posted: Tue Oct 18, 2016 1:29 pm
by alexisjavier
amosbatto wrote:Are you using MySQL 5.5 which you installed with code from the MySQL web site or are you using MySQL from the CentOS repository?

Have you checked the MySQL error log at /var/log/mysqld.log ?

Also check if you enabled logging inside MySQL's /etc/my.cnf file:
Code: Select all
general_log_file='/var/log/mysql_queries.log'
general_log = on 
global log_output = 'file'
Also check whether you enabled debug or debug_sql in your env.ini file for ProcessMaker.

Try adding this line your my.cnf file:
skip-name-resolve

Another possibility is that you have some firewall which is slowing down MySQL queries.

If none of that helps you, then turn on logging in your my.cnf file to see if you see anything strange. Also, read this:
http://www.tecmint.com/mysql-mariadb-pe ... timization

Hello,

First of all thanks for yor response!

Im using latest version from MySQL Site.
I did some tests in HeidiSQL with a remote machine (the same as with the PM client) and the same queries are resolved in 0,003 seconds.
More complex queries are also resolved in less than a second.
I have no SQL Logging and no Logging in env.ini

If I debug the browser I see long responses on the querys, even though they are really simple.
2016-10-18 13_15_13-root@SWKFLITTM02_~.png
2016-10-18 [email protected]_~.png (73.84 KiB) Viewed 1969 times
Though In in the Previous Version (Windows) seems to be ok
2016-10-18 13_17_32-root@SWKFLITTM02_~.png
2016-10-18 [email protected]_~.png (14.22 KiB) Viewed 1969 times
The conflicting line is: VM316 jquery-1.11.js:4

I've tried:
skip-name-resolve
Commented #log-bin=mysql-bin
Commented lower_case_table_names=1
Using IP, DNS, FQDN to access the server.
Using Mobile App

All with same result.

This process was imported from 3.0 maybe that is conflicting in some way?
any other Idea?

Thanks!!
Alexis.

Re: [3.1] Dropdown SQL Performance

Posted: Tue Oct 18, 2016 4:42 pm
by amosbatto
The dev team has only tested with MySQL 5.5, so the code might have some issue with MySQL 5.7. Are you using PHP 5.6?

In your processmaker/shared/sites/workflow/db.php file, are your hosts "localhost" ? if not, you might have a problem with reverse DNS lookup. How long does it take to ping your host?

Re: [3.1] Dropdown SQL Performance

Posted: Wed Oct 19, 2016 11:00 am
by alexisjavier
Hello, Thanks again for you help!

* Sorry, Im using MySQL 5.5.53 (Im on the official stack 205 apparently)
* I did create another PM table, just in case the imported one was conflicting, but same result.
* I put only one record on this databes. Same problem.
* php version is: 5.6.26
* My php.db seems to be ok.
* Server is in LAN, ping are always <1ms
* Installed open-vm-tools. no change...
* Upgraded from 3.1 to 3.1.1. No change
Code: Select all
<?php
// Processmaker configuration
  define ('DB_ADAPTER',     'mysql' );
  define ('DB_HOST',        'localhost' );
  define ('DB_NAME',        'wf_workflow' );
  define ('DB_USER',        'wf_workflow' );
  define ('DB_PASS',        '[email protected]' );
  define ('DB_RBAC_HOST',   'localhost' );
  define ('DB_RBAC_NAME',   'wf_workflow' );
  define ('DB_RBAC_USER',   'wf_workflow' );
  define ('DB_RBAC_PASS',   '[email protected]' );
  define ('DB_REPORT_HOST', 'localhost' );
  define ('DB_REPORT_NAME', 'wf_workflow' );
  define ('DB_REPORT_USER', 'wf_workflow' );
  define ('DB_REPORT_PASS', '[email protected]' );
* just to test i've installed GUI and try PM from the same server using localhost: Same issue
* Ive change pmos.conf to localhost and 127.0.0.1 and test it on the same server... Same issue

I do notice a CPU Spike form 0 to 90% when just changing the dropdown value.
2016-10-19 11_54_00-root@SWKFLITTM02_~.png
2016-10-19 [email protected]_~.png (124.99 KiB) Viewed 1960 times
Im really lost with this one, any other idea?



Thanks,
Alexis

Re: [3.1] Dropdown SQL Performance

Posted: Wed Oct 19, 2016 7:31 pm
by amosbatto
Sorry, I'm out of ideas. The only thing I can think of is a DNS issue which is slowing everything down. You can try changing "localhost" to "127.0.0.1" in your db.php file just in case and add in MySQL:
mysql> grant all on *.* to 'root'@'127.0.0.1' identified by 'PASSWORD' with grant option;

I've seen cases where localhost didn't resolve correctly but 127.0.0.1 did.
After that, I have no idea if you don't have some firewall.

Re: [3.1] Dropdown SQL Performance

Posted: Thu Oct 20, 2016 9:43 am
by alexisjavier
yup, me too:

Ive tried:
changing db.php user to root/admin/wf_workflow
changin db.php host to 127.0.0.1/localhost/publicIP/HOSTNAME

but made no difference, im lost...
im blaming php or jquery but dont know what to do any more.

The next step is trying to install 3.0.7 and see if I get the same result, the other option is try to get a MS 2012 licence

Thanks !!

Re: [3.1] Dropdown SQL Performance

Posted: Mon Oct 24, 2016 8:07 am
by alexisjavier
Well Its solved!

I dont know which thing solved but i did:

* Change hostname to localhost:
Code: Select all
hostnamectl set-hostname localhost
* Installed php56w-opcache
Code: Select all
yum install php56w php56w-opcache
Reboot.
Thanks for your tips!

Re: [SOLVED][3.1] Dropdown SQL Performance

Posted: Thu Jan 05, 2017 1:00 pm
by erpnedir
I had the same problem. Over 5 secs waiting for each dependent fields, and even over 10 secs waiting to get the simple "Edit Process" screen.
My installation was a custom one on a Win64 based server.
Then I installed a second server with the same specs. But this time installed with a bitnami installer.
Now everything works very fast :)

Re: [SOLVED][3.1] Dropdown SQL Performance

Posted: Tue Jan 14, 2020 2:13 pm
by silviuA
Hi all,
I know it is an old thread, but I found out that installing php56w-opcache does really make a difference when trying to reduce loading times.
I think it needs to be one of the solutions when confronted with long loading times of forms.
Also, I wander why this extension (php56w-opcache) is not in the wiki as a recommended install beside php?