System slow, db slow, php-fail

[v.lo...@ifbcon.de]

Dear all,

we are running an SG installation for a long time now.
Everything is fine so far, except the system becomes slower and slower.

We found out, that it is very slow in case a user wants to create a new dataset, or edit or edit as newe some dataset.
Other functions like looking up a contact or files etc. works fast.

Loging in as admin speeds up the actions listed above somewhat.

Looking into the events we see db-slow and system slow events and additionally php failures

see screenshots:

At the time being we have more than hundred projects in the system. In order not to show projects that are not active any more to the users we created a folder (Archiv) in the top level of SGS and moved the projekt protals of the non-active projects to there.
This was done by cutting thee project portals from the original place to the Archiv-folder.

see screenshots:

We suspect that thie lack of speed may be caused by more than one issue, but we would appreciate any help on solving it.

---

System information:

Installation is 0745p

Simple Groupware version: 0.745
Simple Groupware language: de
PHP Version: 5.4.36-0+deb7u3
Database + Version: mysql 554
Server OS: Linux svr-ifb 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u2 x86_64
Webserver: Apache/2.2.22 (Debian)
Webbrowser: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:55.0) Gecko/20100101 Firefox/55.0
APC cache usage: 31 MB / 32 MB
Disk usage: 118 GB / 917 GB

---

Thanks in advance,

Cheers Volker

[Vlad 0304]

Hi Volker,

A first look at your problem: database transactions process slowly due to index missing or non-effective.

What I will doing in such cases:

- database transaction logging turn on to detect slowest of sentece's;

- run slowest query in "explain mode" (postgresql) or another time/resource profiler mode;

- create missing database table index to run faster;

- rewrite the query or related parameters if it in your code;

- check step-by-step again until "bottle neck" exist.

Do not forget to switch database logging off when done.


Best regards,

Vlad

--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupware+unsubscribe@googlegroups.com.
To post to this group, send email to simple-groupware@googlegroups.com.
Visit this group at https://groups.google.com/group/simple-groupware.
For more options, visit https://groups.google.com/d/optout.

[Patrick Pliessnig]

Volker

Your screenshots show one slow db statement. the select statement on simple_contacts. It's generated by sgs.

Go through it and you will see a lot of project names in the statement. I guess you'll see also you're archived projects, not only the active ones.

The problem with it is that it uses a REGEXP command to check access rights of the projects (visible in the screenshots). Unfortunately select statements with REGEXP command will not use any index with Mysql. So they are inherently slow.

How can you improve this select statement? The project names you see are in fact user groups generated by your projects. For each project one user group. One way to improve this select command is to reduce the number of user groups by using manual groups. Eg, If you have one project team for all the projects, then one user group would be enough. This select statement would be greatly reduced.

One index you should particulary check is an index on the folder field in a schema like simple_contacts. Another one you might consider is an index on the email field in simple_contacts.

Moving your project portals around should probably not have a big influence on performance (I guess), because what you perceive as folder in the UI is just a field in a schema's db table.
 
HTH
Patrick

To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To post to this group, send email to simple-g...@googlegroups.com.

[v.lo...@ifbcon.de]

Dear Vlad & Patrick,

first of all thanks for your time and help. Good to hear our issue may be solved and the speed improved.

As for one part it actually makes sense to me the REGEXP search has trouble to go through all the different project groups.
But what do you mean by

"One way to improve this select command is to reduce the number of user groups by using manual groups"

manual groups?

Do we have to delete the automatically generated projectgroup and add a manual group in the project module instead (of course with the goal to reduce the number of groups significantly)?

ok, Second question: As we are far away from being database wizards, how do we add an index (to the column of a database table you mentioned , I guess)?
Is this done by using the <INDEX> tag in the modul at the end of the field where we want to have the index, or has this to be done in MySQL (at least for the folder field, because there is no folder filed, as far as I know, in the module sgxml).

Well, looking forward for some more hints,

thanks for you time and efforts, good to know you are around,

Volker

[Patrick Pliessnig]

Volker

But what do you mean by
"One way to improve this select command is to reduce the number of user groups by using manual groups"
manual groups?

Do we have to delete the automatically generated projectgroup and add a manual group in the project module instead (of course with the goal to reduce the number of groups significantly)?

Yes, this is exactly what I suggest! I just don't know how exactly this select statement is generated. So you could start by deactivating a group and check, whether this group is still listed in the REGEXP expression. 2nd you could delete the group and check. But keep in mind that by deleting you just move the group to the bin which is nothing else than folder. So the group would be still there. And last, you could purge the group ( 1. delete, 2. empty the bin ).

Anyway by manual group, I mean a group that you create manually and use to control reading rights in the project folders. those automatically generated project groups are exactly the same than manual groups. they are just created automatically. so if you have a project team in Berlin and one in Duisburg, just create the groups team_berlin and team_duisburg and use them. If you don't care about reading rights within your organisation you might as well use the anonymous user instead of groups.

If this makes no sense to you, let me know. then we better switch to a voice communication. It would be easier to explain, don't hesitate.

nb1: the fact that your admin user is slightly faster is an indication that it could be about reading/writing rights, as for the admin user it is easier to check. It has almost all the rights.

nb2: if you proceed with replacing the project groups with manual groups, you could also easily reconfigure project creation by deactivating automatic group creation and using automatically your manual groups.

[Patrick Pliessnig]

Second question: As we are far away from being database wizards, how do we add an index (to the column of a database table you mentioned , I guess)?

1. A module with an xml-schema is nothing else than a front-end to the underlying db-table.
2. In the underlying db-table you always find automatic system fields. one of them is the folder field ( a numerical ID )
3. On any active folder you can find ( as admin user ) at the bottom left 3 system views: structure ( structure of the db-table ), index ( the indexes of the db-table ), schema ( the complete xml schema governing the db-table ).

if you go to the index-view you will see whether there is an index on the folder field ( there should be one ).

if there is none you can use either method to create one.

a) add the field to your schema and use the <INDEX> tag

b) open an sql console ( eg in the system folder ) and enter the necessary sql command.

For the folder field I suggest method b)

https://www.w3schools.com/sql/sql_create_index.asp.

Am Dienstag, 3. Oktober 2017 18:06:52 UTC+2 schrieb v.lo...@ifbcon.de:

[v.lo...@ifbcon.de]

Dear Patrick,

so here is the good news:

I followed your instructions and finally deleted and purged all automatically generated project groups, as I could see by deleting them successively,. they disappeared in the search trace (so they REGEXP load dropped).

Then I went through all of the projects and assigned r/w rights manually for only like 3 main project teams (groups) and left some admin-groups in the system for critical matters like accounting etc., 

After managing that I went through the maintenance ( clean all caches, optimized tables etc.), well and it is up to speed!!!

And btw, as you guessed, you can move portals to anywhere in the sys_tree and it will almost make no difference in performance.

Now I try to learn and work on keys and index use, but I still am not sure I understand how to find out where an index may be needed and why (so how you find out the necessity to add an index to a special field).

Thanks for your help, simply great!

Best, Volker

P.S.: Thanks to Vlad too, for trying to help me/us out!

[Patrick Pliessnig]

Hi Volker

I am glad you're up to speed again. -:)

To understand where to use indexes you need to know how mysql executes sql statements. It is an exploration rather than fixed rules. First step is to know the sql statements you use, then analyse them. Best is to follow Vlads instructions.

If you know how you use the system from a users perspective, sometimes you could also simply guess where an index could speed up and give it a try. The folder field is such a case. It is added to almost every select statement to limit the results to one folder unless you use the nosqlfolder="true" tag in a schema. of course selfmade select statements could also be without folder field. a key is simply an index with unique values. normaly sgs adds an index to the folder field automatically.

Cheers
Patrick