Kimai Forum
Kimai - Time Tracking Community
September 09, 2010, 09:54:34 PM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
News: Kimai v 0.9.0.1082 final ready for download
 
   Home   Help Search Login Register  
Pages: [1]
  Print  
Author Topic: Mysql performance improvements  (Read 532 times)
q_no
Kimai Newbie
*
Posts: 2


View Profile
« on: January 20, 2010, 05:05:58 PM »

Hi forum & Kimai devs,

we are using Kimai in our company for a while now. After adding plenty of customers, projects and recording the times for all employees Kimai started performing very bad. Opening Kimai, as well as reloading the project and customer lists was pretty slow, never mind selecting a big time frame for the records.

The solution is quite easy and can be applied quickly: set indexes on foreign keys! (especially in _pct,_evt,_zef) Afterwards you will notice a boost in loading times. Wink

Hope to see this tiny but effective change in a future version!  Smiley
Logged
ServiusHack
Global Moderator
Kimai Guru
*****
Posts: 612


View Profile
« Reply #1 on: January 21, 2010, 10:30:46 PM »

Hi there,

thanks for this idea. I will implement it and see, if there are more things which can boost up the database access.

Best Regards,
Severin Leonhardt
Logged
q_no
Kimai Newbie
*
Posts: 2


View Profile
« Reply #2 on: January 22, 2010, 01:56:23 AM »

Well, I can tell you what would improve the entire speed and user experience in general. Get the rid of all the hard-coded Ajax reponse handlers. I know it sounds kinda harsh, but see:
We are still using Kimai 0.7 since the upgrade path got kinda messed up when PDO was introduced at first, but I had a look at latest release lately and IMO it still suffers a bit from the first design decisions.

The jQuery response handlers work fine and they are pretty solid the way they were written, but at the same time not very effective. Looking at the 0.7 release there's one good example:
When stopping the time recording Kimai fires an Ajax request to write the record to the database. Afterwards reloadAllTables(); gets called and fires 3 additional requests, one for each list (customers, projects, tasks). Since sessions are required for each request, this method easily runs into session locks with file-based sessions handlers. This means each request has to wait for the previous one to finish and unlock the session until it it can get executed. Thus all responses are delayed and the server has to deal with more connections then actually needed. On a localhost or any other small environment you won't notice it at all, but when using Kimai in bigger company and/or on a shared hosting server it's not performing as good, as it could.
That's one of the reasons I like the Xajax approach of processing Ajax requests so much. Xajax does all the processing on client side automatically based on the commands sent by the server. You can merge the responses of different functions to one and return it at once.
When calling a function like "reloadAlltables()" it would call the corresponding function on the server. That function would look like this:

Code:
function reloadAllTables() {
  $objResponse = new xajaxResponse();
  $objResponse->loadcommands(reload_customers());
  $objResponse->loadcommands(reload_projects());
  $objResponse->loadcommands(reload_tasks());
  return $objResponse;
}

This helps a lot to reduce the overhead of firing several requests at once to a minimum. If you'r not familiar with Xajax have a look at the project's website and you will see what I mean.

I think Kimai could benefit a lot from using a similar approach. Unfortunately it would mean a lot of work to rewrite all request/response handlers, but for the user experience I think it's really worth the effort.

Furthermore, it would be easier to create some hooks for other extension developers. A tiny event handler on server side could call functions from all loaded extensions and merge their responses to one with a single request.
For instance: Upon initial page load Kimai could fire an event like "create_main_navi" and an extension that hooked into this event before could return a response to add another entry to the navi, as well as adding commands to load additional styles and javascript files. The event handler then merges the responses and returns it to the client. Wink


Just my 2 cents Wink
Logged
Kevin
Administrator
Kimai Guru
*****
Posts: 135



View Profile WWW
« Reply #3 on: January 22, 2010, 11:37:01 AM »

It seems you really looked into Kimai and care about it, thats great! Your technical feedback is highly appreciated Smiley

As I assume you represent a bigger company that benefits from Kimai, aren't you interested in helping to improve the codebase by sending in some patches? I don't know what Severin thinks about it, but I/we would be more than happy to implement code contributions. You could have an account for our Subversion repo as well.

Don't get me wrong. Not only the fact that our free time is kind of limited, but also the fact that your posting sounds as if you know what you are talking about lets me ask. And we could definitely need some help here.
Logged
Pages: [1]
  Print  
 
Jump to:  

Powered by SMF | Simple Machines LLC