Performances severely decreases with the increase of number of tables.

[A Bindi]

I made an AppGini app to manage our company's IT/HR asset which has become indispensable.

The project started on 2016 and developed/upgraded until today until he has reached the total amount of 95 tables (and many relations).

The problem is that in the years (as tables were added) performance has been declining, so in this moment to open a lookup field to a simple table (two fields and only few records) need to wait up to 20 seconds, making the use very frustrating.

It should be noted that a user that belong from a group that has access limited to only 2-4 tables (in the same database) gets much better performances !

Another thing to note is that the "Query Logs" in the "Admin area" is constantly full populated with "Slow queries".

The application is hosted on premise on a Windows 2008 Server HYPER-V VM with IIS 7.5 (with FastCGI PHP installed) and MySQL 5.7

ALex.

[jsetzer]

May I ask which version of AppGini you are using?

Let's assume you are using up to date version 24.x:

From the screenshot I can see it is all the same query. I have searched (standard generated) code and found exactly one PHP-function: getTablePermissions() in incCommon.php which runs this query.

This should be the relevant line of code:

Code: Select all

$res_group = sql("SELECT `tableName`, `allowInsert`, `allowView`, `allowEdit`, `allowDelete` FROM `membership_grouppermissions` WHERE `groupID`='{$groupID}'", $eo);

I'm wondering why this query executes so often, because AppGini uses caching for table permissions:

Code: Select all

function getTablePermissions($tn) {
  static $table_permissions = [];
  if(isset($table_permissions[$tn])) return $table_permissions[$tn];
  // ...

Code_fanBmtiltB.png (33.6 KiB) Viewed 10721 times

On first run, code in line 62 (bullet #4) should execute. On subsequent runs, code in line 58 should find entry for $tn and return (at bullet #3). Therefore, code should run once.

---

There is one very stupid idea which I would certainly check first:
As AppGini tries to find $tn in cached permissions, maybe in your custom code there are function-calls of getTablePermissions('...') passing a $tn which does not exist any more (or passing '' <blank> or null). This could explain why that query runs again and again: simply $tn can never be inside static $table_permissions array. That could be a result of a deleted table, a typo or a wrong variable-assignment in custom code, for example.

• I'd go through all references to getTablePermissions-function and double-check $tn-parameters.
• If this does not help, I would scan my own code for places in which exactly the same SQL statement was run. If there is such, this custom code should be optimized for example by caching or eliminated in favour of built-in security functions like check_record_permission(...).
• If there is no other spot, I'd start debugging the function shown above and see, why caching fails.
• If there is nothing to fix, I'd focus on speeding up by adding appropriate indexes to membership_grouppermissions, then clear log and check again.

I cannot say about other performance issues, because your screenshot only names that one slow query. This is where I would focus on, first.

PS: Furthermore: a runtime of over a second for this query seems like a lot to me, even with lots of groups and tables. How many groups and users do you have? That issue can be addressed in the next step.

[A Bindi]

Hello jsetzer, thank you for prompt reply.

Unfortunately we still use the Appgini Ver.5.97 (rev.1142) due the incompatibility of Appgini's newer versions with our actual server (Windows 2008 Server IIS 7.5 and MySQL 5.7): in consideration of this I does not know if what you wrote in your reply is applicable or not.

About users/group we have 4 groups and 7 users (LDAP authenticaton as described here: https://forums.appgini.com/phpbb/viewtopic.php?t=2707).

ALex.

[jsetzer]

Sorry, I cannot help on that older version because I do not have any comparable environment here.
I think this requires some debugging and research directly on your machine in your specific environment.

[A Bindi]

Sorry, I cannot help on that older version because I do not have any comparable environment here.
I think this requires some debugging and research directly on your machine in your specific environment.

We are planning to replace the actual Windows 2008/IIS with a new phisical server (with linux operating system) to be able to create a new working environment compatible with the latest available version of AppGini and PHP.

But the statistics seem to me that speaks clearly: since 2016 to today, with the increase in the number of tables, the performance has progressively worsened.

Also: compared to a user who has access to all tables the performance for a user who has access to few tables (up to four for example) are allways much better.

I does know if some other colleague has experienced this behavior.

ALex.

[ppfoong]

How much RAM does you allocate for the VM, and what is your MySQL config?

There are plenty of MySQL config samples in the web for optimization, and here is one for v5.7 for your reference.
https://www.red-gate.com/simple-talk/da ... rformance/