Loading

Collabtive speed

General Discussion around Collabtive

Collabtive speed

Postby Remiguel » 01.05.2015, 12:36

In general my users are happy with the access time we have. We are managing over 100 projects (with tasks, messages, time registers) and additionally we have 500 closed projects.
Where we are not so happy is when project admin is editing data. Instead of 900 ms to refresh a complete page, Collabtive need 12 s after each edition.
My version of Collabtive is highly customized but the way to access to the server is the same.
To work on an improvement, I need to understand what is the behavior of Collabtive while editing and after a data edition?
Why it takes more time to refresh a page after an edition than refreshing the same page (with cache disabled)?

Any tip or support would be greatly appreciated
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Re: Collabtive speed

Postby Eva » 01.05.2015, 16:14

Does your observation concern all edits or just edits of, let's say, projects / tasks / messages ...?
The more specific you get, the better your chances for a helpful reply. :)
Project Management the way you like it: Collaborative - Open Source - Free

facebook.com/Collabtive
twitter.com/Collabtive
xing.com/companies/collabtive
linkedin.com/company/collabtive
User avatar
Eva
 
Posts: 1471
Joined: 01.01.2008, 23:31
Location: Saarbrücken, Germany

Re: Collabtive speed

Postby Remiguel » 01.05.2015, 19:07

Thanks Eva for the reply

It's happening with every edits.
The difference between refreshing time after editing and refreshing the page without editing is maybe clearer in timetracker tab in project view (tracker_project.tpl).
But It is really happening in all views with all editable elements.
I guess that every time a modification is sent to the database, the whole page is refreshed, and all the variables retrieved from server side as well.
With few registers the difference is not so noticeable. We have already more than 20.000 time registers and that makes an huge difference.

I just want to understand better the Collabtive behavior, to check my code modifications and improve or correct them. :oops:
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Re: Collabtive speed

Postby Eva » 01.05.2015, 21:59

I guess that every time a modification is sent to the database, the whole page is refreshed, and all the variables retrieved from server side as well.


As far as I know the code base, this assumption is correct. Maybe you could only load the first x items, and then asynchronously load the rest?
Project Management the way you like it: Collaborative - Open Source - Free

facebook.com/Collabtive
twitter.com/Collabtive
xing.com/companies/collabtive
linkedin.com/company/collabtive
User avatar
Eva
 
Posts: 1471
Joined: 01.01.2008, 23:31
Location: Saarbrücken, Germany

Re: Collabtive speed

Postby Remiguel » 02.05.2015, 00:15

I've already tried to limit the amount of data by filtering and limiting queries, but I didn't notice any improvement.
When I refresh the same page, skipping the cache but without editing any data, it is really quick (less than 900 ms). What are the difference by doing that or editing a value?
I thought that in both cases all the variables are retrieved from server side. First case forced, since I skip the cache and the second case done automatically.
Obviously it shouldn't be the same since the refresh time is different :(
I will read on "asynchronous load" to learn how it works. Thanks!
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Re: Collabtive speed

Postby Remiguel » 02.05.2015, 18:58

After some tests I measure a latency of almost 10 s when the UPDATE or INSERT query is send to the server. The refresh page process, after the UPDATE actually, is really quick (under 1 s).
The strange think, If I execute directly the UPDATE query in PhPMy Admin the execution time is less than 1 ms. :o
With the exact same configuration (Page and server) but with a table with less registers the latency is shaved off under 2 s :shock:
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Re: Collabtive speed

Postby ChristianF » 05.05.2015, 11:02

Hello,

What happens if you try to run an EXPLAIN on that query?

Also, have you tried running the code through a profiler, to see exactly where the shoe is pressing?
ChristianF
 
Posts: 17
Joined: 13.03.2015, 10:18
Location: Norway

Re: Collabtive speed

Postby Remiguel » 05.05.2015, 16:51

Thank you ChristianF

I have run some of my queries with EXPLAIN, but I didn't get anything valuable, that way. Since all tables have primary keys on ID and columns used, in the statement are indexed, little can be done to improve the db.
I didn't try any profiler for Mysql. Do you have one to suggest to me?

I did some progress: I found that the dropdown lists (for selecting projects) are making the page refresh process, to run slow. 3 dropdown list are looking data with the function "getMyProjects". As you know, this function is fetching a lot of data.
I've created an extra "getMyProjects" lite function to get only what I need for the project dropdown list. The new function is using a SELECT IN, as suggested by you, in a previous post below.
The result in term of speed is really noticeable. Where I was measuring 12 s (after an edition) now the refresh page process last only 6 s.
Without edition the refresh time is also shaved off 50% on this same page.
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Re: Collabtive speed

Postby ChristianF » 08.05.2015, 15:03

Hello,

Sounds like it's the MySQL server that's having troubles with rebuilding the cache, and getting hammered with too many queries at the same time.
I haven't noticed the same issues on our install of Collabtive, but I'm not sure whether this is because of the changes I've done to the code, difference in the data quantities, or due to differences in our MySQL server settings. You could take a look at the changes I've done to the getMyProjects () function, to see if there's anything functionally different between our two implementations.
Code: Select all
    /**
     * Lists all projects assigned to a given member ordered by due date ascending
     *
     * @param int $user Eindeutige Mitgliedsnummer
     * @param int $status Bearbeitungsstatus von Projekten (1 = offenes Projekt)
     * @return array $myprojekte Projekte des Mitglieds
     */
    function getMyProjects($user, $status = 1)
    {
        global $conn;

        $myprojekte = array();
        $user = (int) $user;
       $status = (int) $status;

        // 20150312: Use only one query to fetch all information about projects. - christian@nax.no
        $sel = $conn->prepare("SELECT * FROM projekte WHERE ID IN(SELECT projekt FROM projekte_assigned WHERE user = ? ORDER BY ID ASC) AND status=?");
        $selStmt = $sel->execute(array ($user, $status));

        while ($project = $sel->fetch()) {
            if (empty($project)) {
                continue;
            }

            if ($project["end"]) {
                $daysleft = $this->getDaysLeft($project["end"]);
                $project["daysleft"] = $daysleft;
                $endstring = date(CL_DATEFORMAT, $project["end"]);
                $project["endstring"] = $endstring;
            } else {
                $project["daysleft"] = "";
            }

            $startstring = date(CL_DATEFORMAT, $project["start"]);
            $project["startstring"] = $startstring;

//             $project["name"] = stripslashes($project["name"]);
//             $project["desc"] = stripslashes($project["desc"]);
            $project["done"] = $this->getProgress($project["ID"]);

            $companyObj = new company();
            $project["customer"] = $companyObj->getProjectCompany($project["ID"]);

            // 20150312: Use default array appending method, instead of merging arrays. - christian@nax.no
            $myprojekte[] = $project;
        }

        if (empty($myprojekte)) {
            return false;
        }

        // Sort projects by due date ascending
        $date = array();
        foreach ($myprojekte as $key => $row) {
            $date[$key] = $row['end'];
        }
        array_multisort($date, SORT_ASC, $myprojekte);

        return $myprojekte;
    }

Here's some stats from our database:
Code: Select all
mysql> select count(*), "Projects" FROM projekte UNION SELECT count(*), "Tasklists" FROM tasklist UNION SELECT count(*), "tasks" FROM tasks UNION SELECT count(*),"Comments" from comments;
+----------+-----------+
| count(*) | Table    |
+----------+-----------+
|       20 | Projects  |
|      342 | Tasklists |
|     2708 | tasks     |
|     7543 | Comments  |
+----------+-----------+


As for the profiler, I was really thinking about a PHP profiler. It'll tell you approximately how long the SQL queries take too. Other than that, I think mysqladmin should provide some statistical data about slow queries and such.
ChristianF
 
Posts: 17
Joined: 13.03.2015, 10:18
Location: Norway

Re: Collabtive speed

Postby Remiguel » 09.05.2015, 01:33

Thanks Christian for your reply
To notice what we were facing you need a lot of projects (more than 400) and more than 20.000 time record associated to those project.
Additional you need the former code structure, when "getMyProjects" function, was calling the "getProject" function.
"getProject" is pulling data from other functions. Moreover, I was using "getProject" to calculate department budget, total expense per dept, extra progress bars ...etc etc

Now My "getMyProjects" is more like yours, to retrieve essential data for my index.tpl. Really clean and quick

To populate dropdown lists (3 in the same page with 400 projects) I created a new function, with a simple query to get data, only for the list. This function don't load data for the progress bars neither customer data. That make the page reload much more quicker.
My users seem to be happy again with Collabtive, thanks to those improvements ;)
Thanks for pointing out the way to improve queries with SELECT IN.
Remiguel
 
Posts: 96
Joined: 10.06.2014, 09:34
Location: Spain / France

Next

Return to General

Who is online

Users browsing this forum: No registered users

cron