Performance Issues

[it4e]

Hello,
After tyding up my SQL queries and checking with your tab on performances in your manual, my Performance sady still isn't as fast as i would like.
For Example a simple query like this:

SELECT `vendor`.`No_`, `vendor`.`Name`, `vendor`.`Name_2`, `vendor`.`Address`, `vendor`.`Address_2`, `vendor`.`Post_Code`, `vendor`.`City`, `vendor`.`Phone_No_`, `vendor`.`Fax_No_`, `vendor`.`E-Mail`, `vendor`.`Home_Page`, `vendor`.`ID` FROM `vendor` ORDER BY `vendor`.`Name` ASC, `vendor`.`ID`

..has 4778 results and is taking 6 seconds to load the page. It has no lookups and no formating functions etc.
The same Query takes 0,0016 Sec. in MariaDB to show results.

I have tried setting "$always_refresh_permissions" in config.php to 0 but it only improves the loading speed by max. 1 sec.

Do you have an Idea how to Fix this?

Thank you very much in advance.



Your current DaDaBIK version

You are using DaDaBIK version 10.1-Manarola enterprise, installed on 06-05-2020, the latest version of DaDaBIK is 10.1-Manarola released on 03-09-2020

You are runnning the last release of DaDaBIK

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enteprise to Platinum) please contact us.

System info

PHP Version: 7.3.18

mysql version: 5.5.5-10.4.12-MariaDB

Web server: Apache/2.4.43 (Win64) OpenSSL/1.1.1g PHP/7.3.18

Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:77.0) Gecko/20100101 Firefox/77.0

URL installation: http://localhost/dadabik/

 

[eugenio]

Hello,
it's quite strange, 6 seconds is too much for a few thousands records

Can you also try on mariadb
SELECT SQL_NO_CACHE `vendor`.`No_`, `vendor`.`N .......

to check the time without caching?

Is the name field indexed?
Is id indexed (I guess it's the primary key so yes).

Are you experiencing the same problem with other tables?

Best,

 

[it4e]

Hello Eugenio, thank you for your quick answer.

I tried SQL_NO_CACHE and the result in MariaDB was still 0,016s

I have the problem with other tables aswell, but the MariaDB query is not quite as fast as this one, the others take up to 1 sec to query and up to 10 sec to display in Dadabik.

I tried adding an index to Name, aswell as a unique Index to ID (Kind of pointless since it is already the primary key), but it didn't change anything.

Do you have another Idea?

Thank you for your help.

 

[eugenio]

Hello,
I haven't seen your application in detail but no, I haven't other ideas; I don't think it's a normal behaviour, though.

If you want, open an email support ticket and we'll look into that more closely.

Before doing that, I would try to create a new, fresh dadabik application, using the original dowloaded package without any code modification (except from config.php), based on the vendor table only (basically, you should first create a new db containing the vendor table only), this is useful to isolate the problem; check if, even in this case, the performances are low.

Best,

 

[it4e]

Hello,
I have tried a new Installation based on the Vendor Table only, and it loads in only 0,2sec.

I guess I have some settings in my Original DaDaBIK application, that slow it down so much.

I am going to be away for 2 weeks after that i will write a Support Ticket.

Thank you for your help.

 

[eugenio]

Ok, you are welcome.

Best,

 

[Jaroslav]

I have similar performace issue.

Every HTTP request is taking too long - approx 3 - 6 seconds - sometimes even 15 - 20 sec.

I have only default installed generated forms (from database tables) with almost same permissions.

No special validation/formatting functions on table fields.

Every table has max. 10 records.

It is taking long for results, search or item detail (view, save, delete) - it doesn't matter what function is called.

I use only admin user, no other users logged in when I am using application.

I tried set up config value: $always_refresh_permissions = 0; (computes the user permissions only when the user logs-in)
but it helps only little.

Any suggestions ???

I use web hosting Siteground.com where I host many Wordpress sites and no problem with mysql calls or duration of requests.

---

You are using DaDaBIK version 10.3-Manarola enterprise, installed on 04-09-2020 (installation code: 158985f5229df95ee6),
the latest version of DaDaBIK is 10.3-Manarola released on 26-08-2020
You are runnning the last release of DaDaBIK

System info
PHP Version: 7.3.16
mysql version: 5.6.40-84.0-log
Web server: Apache
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36
URL installation: https://pomedoprec.sk/my_dadabik/
Web Hosting: siteground.com (GrowBig plan)

 

[eugenio]

Hello Jaroslav,
how many tables you have? How many user groups you have? How many fields per table, on average, do you have?

If you have a very huge number of fields per table, this can have an impact on performances if granular permissions are enabled: dadabik, for each field of each of the tables, needs, for each user group, to keep in memory and/or retrieve from the db the related permissions for all the possible operations.

If the problem is the number of fields, you should see a huge difference if you set
$enable_granular_permissions = 0
(pay attention if your application is already in use, though ... because none of the permissions rules you set will be used, do it on a test copy of the application).

Best,

 

[Jaroslav]

Hello Eugenio,

I have 84 my own tables. Only 2 tables are with 30 columns, other ones are with approx. 5 - 7 columns.
I have also correctly setup primary keys (ID columns) and foreign keys as indexes (default index type BTREE).
I have only one user group - admin.

All tables have same permissions setup for this "admin" group:
- table: Yes to all (read, create, edit, delete, details, csv)
- columns: Yes to all - except ID column primary key (No to all)

I set up flag: $enable_granular_permissions = 0 but nothing much happend (I am sure it was applied).
Sometimes every request takes more then 10-15 seconds so application is unusable.

Can you recommend any online/offline/inbuilt tool to measure performance for MYSQL or PHP ?
I need to find out where the problem is - whether the database or php/apache.

Thanks.

 

[eugenio]

Hello,
84 tables is quite a lot, having 84 tables, if you set $enable_granular_permissions = 0 I am pretty sure you should see a difference, what do you mean with "nothing much appened", are the performances more or less the same? If yes, I would say it's not a DaDaBIK issue.

A simple test you can do is: create a copy of your db, then delete all the tables except from one and install dadabik over this copy with the same settings you have in the other installation. Are the performances the same?

Best,

 

[Jaroslav]

Hi Eugenio,

I tried various scenarios with fresh Dadabik installation:
- only one table
- all tables, no data
- all tables, codetables filled with data
- all tables, with triggers/no triggers
I didn´t do any changes in forms configurator or permissions in Admin after installation.
$enable_granular_permissions is set to 1 because I need it.

In every scenario there is a same behavior: first it works great, every request (search/insert/detail..) runs 2-3 sec.
Then it starts to be slow (after few minutes) and requests run more then 20 secs, even 504 gateway timeout occurs.
I do not understand. Is there any problem with browser cache? Or another type of cache ?
Dadabik populates data for every single_select dropdown list in to memory ?
I have no idea

You can try it:
Link to my project:
https://pomedoprec.sk/kvrps1/ (username and pwd are default)

 

[eugenio]

Hello,
there isn't anything in DaDaBIK that can lead to such behaviour (same application, at some point it starts to slow down). If this is what happens, I would describe to your hosting provider (if it's not self-hosted) what is happening and ask if they have an explanation.

Can you provide a link to the simple application (one table) I mentioned before? I want to check if I can reproduce this behaviour.

Best,

 

[Jaroslav]

Hello,

I create new database with 70 tables then fresh Dadabik install, no records in tables.

Create DB script: https://sajfez.eu/wp-content/uploads/2020/11/20201111_create_db.txt

Access URL: https://pomedoprec.sk/kvrps1/
Username and Pwd: root/letizia

Please you can login and try just click on various tables in left menu - it calls /index.php?function=search&tablename=XXXXXXX
and you can see that every request runs more then 20 secs.

I have Siteground.com webhosting.

Thx

 

[eugenio]

Hello,
what I said was an application with ONE table.

Best,

 

[Jaroslav]

OK,
I create application with ONE business table (named "S_RI") + 9 related codetables because of references (FK).

Create DB script: https://sajfez.eu/wp-content/uploads/20201112_light_RI_only_codetables.txt
Codetable values insert script: https://sajfez.eu/wp-content/uploads/20201112_light_RI_only.txt

I am testing it for 10 minutes and no problem occurs, every request takes 1-2 sec :S

I don't get it. Why 70 tables (35 business tables and 35 codetables) should be a problem and 1+9=10 tables not ? . :/

Thanks

 

[eugenio]

Hello,
you said that in all the scenarios after a while the performances decrease, but this is not happening, am I right?

The answer to your question is the one I explained in my first reply: if you have a huge number of tables and/or fields this can have an impact on performances if granular permissions are enabled. 70 tables is a lot.

If I were you, I would also try the same application on your local computer if you can, if you see a huge increment of the performances, it means the machine on the hosting service probably is not enough for your application. Be sure to use php7 though, the difference with php5 is huge.

Performances of dadabik apps having a huge number of tables/fields will be improved in V. 11.

Best,

 

[Jaroslav]

Hello,
I understand.
But I need 70 tables (and even more for history audit log) and also granular permissions enabled because it is core feature and I want to use it and configure tables with it to fullfill application requirements.

I deploy same application with all tables on local computer and it runs without problems, responses are ok, performance is great - for application and also for dadabik administration interface.

Now I have to looking for more powerfull hosting or wait for V.11. Is there any approximate realease date ?

Anyway, thanks a lot for your quick feedback Eugenio, I appreciate that.

 

[eugenio]

Hello,
no, there isn't any approximate release date, it won't be anytime soon because there are a lot of things going on for this new release, I mentioned that because performances improvement is one of the things I am personally working on during these days.

If it works well on your personal computer, for this specific problem you are experiencing, I think the issue is the lack of "CPU power" on your hosting so if you are planning to change, I would invest on that.

If you are using 10.3 as you mentioned, another thing you can try is the following query (I assume your prefix is dadabik_)

DELETE FROM dadabik_permissions WHERE subject_type_permission = 'user'

Please note that it's NOT a well tested "hack", it's something I am working on these days so use it carefully and test if all the permissions you set work as expected after the execution. This should increase the performances at least for some users (for sure, for the default root and alfonso users)

Best,