[Davical-general] Davical performance

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Davical-general] Davical performance

Mark Davies
I am looking at Davical performance, since my own servers are beginning
to struggle under the load of thousands of calendars and calendar items.

I notice that the standard Davical install script does not apply any
database indexes, over and above those required for key constraints.
Does anyone have any experience in this area, e.g. recommendations for
suitable indexes to apply to improve query and update performance? I am
thinking that the caldav_data and calendar_item tables in particular are
suitable for optimisation with some carefully-chosen indexes.

I also notice that there are two areas of the Davical code which incur a
substantial performance hit when a single user has many calendars:

1. When retrieving a principal, the "schedule-default-calendar-URL" is
always populated. This runs a potentially expensive SQL query (actually
expensive on my server!) to retrieve all calendars for the given user,
then searches through them all (in PHP) looking for one called "home";
if not found, it uses the first calendar. I think the query needs to be
improved here so that the database does more of the work. Even better,
perhaps the home calendar URL ought to be stored in the database as a
separate field, e.g. on the "principal" table.

2. Also when retrieving a principal, a "calendar_free_busy_set" property
is populated. Again this is expensive, with a complicated SQL query over
the collection table. In this case, it appears that
calendar_free_busy_set is not in the extant CalDAV RFC - I think it
appeared in a draft but was then removed. So should Davical be
populating it at all?

I am considering making code changes to improve these areas. Would the
community like these committed back to the repository? What about
(performance-related) database indexes - should these be part of the
standard build of Davical?

Mark Davies

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Stefan Pielmeier
Hi Mark,

Community is definitely interested in your improvements, that is me :-)

Stefan Pielmeier
from mobile device


> On 21 Nov 2014, at 11:40, Mark Davies <[hidden email]> wrote:
>
> I am looking at Davical performance, since my own servers are beginning
> to struggle under the load of thousands of calendars and calendar items.
>
> I notice that the standard Davical install script does not apply any
> database indexes, over and above those required for key constraints.
> Does anyone have any experience in this area, e.g. recommendations for
> suitable indexes to apply to improve query and update performance? I am
> thinking that the caldav_data and calendar_item tables in particular are
> suitable for optimisation with some carefully-chosen indexes.
>
> I also notice that there are two areas of the Davical code which incur a
> substantial performance hit when a single user has many calendars:
>
> 1. When retrieving a principal, the "schedule-default-calendar-URL" is
> always populated. This runs a potentially expensive SQL query (actually
> expensive on my server!) to retrieve all calendars for the given user,
> then searches through them all (in PHP) looking for one called "home";
> if not found, it uses the first calendar. I think the query needs to be
> improved here so that the database does more of the work. Even better,
> perhaps the home calendar URL ought to be stored in the database as a
> separate field, e.g. on the "principal" table.
>
> 2. Also when retrieving a principal, a "calendar_free_busy_set" property
> is populated. Again this is expensive, with a complicated SQL query over
> the collection table. In this case, it appears that
> calendar_free_busy_set is not in the extant CalDAV RFC - I think it
> appeared in a draft but was then removed. So should Davical be
> populating it at all?
>
> I am considering making code changes to improve these areas. Would the
> community like these committed back to the repository? What about
> (performance-related) database indexes - should these be part of the
> standard build of Davical?
>
> Mark Davies
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Cyril Giraud-2
In reply to this post by Mark Davies
You're welcome!
Community is actually testing davical focusing on next debian release.

Please:
- create a new issue on https://gitlab.com/groups/davical-project/issues
- make a separate branch on git to push your performance improvements
(please, include the name of the issue on the branch name). Network:
https://gitlab.com/davical-project/davical/network/master
- when your code is ok and tested, email us on this list
- we'll discuss about merging on next davical meeting (you, and all the
community are welcome for davical IRC meetings). Next meeting probably
15th Dec, 19:00 UTC, according to Jim emails.

Ask on this list if you fail to get write access to gitlab (or Wiki).

        Cyril.


On 21/11/2014 11:40, Mark Davies wrote:

> I am looking at Davical performance, since my own servers are beginning
> to struggle under the load of thousands of calendars and calendar items.
>
> I notice that the standard Davical install script does not apply any
> database indexes, over and above those required for key constraints.
> Does anyone have any experience in this area, e.g. recommendations for
> suitable indexes to apply to improve query and update performance? I am
> thinking that the caldav_data and calendar_item tables in particular are
> suitable for optimisation with some carefully-chosen indexes.
>
> I also notice that there are two areas of the Davical code which incur a
> substantial performance hit when a single user has many calendars:
>
> 1. When retrieving a principal, the "schedule-default-calendar-URL" is
> always populated. This runs a potentially expensive SQL query (actually
> expensive on my server!) to retrieve all calendars for the given user,
> then searches through them all (in PHP) looking for one called "home";
> if not found, it uses the first calendar. I think the query needs to be
> improved here so that the database does more of the work. Even better,
> perhaps the home calendar URL ought to be stored in the database as a
> separate field, e.g. on the "principal" table.
>
> 2. Also when retrieving a principal, a "calendar_free_busy_set" property
> is populated. Again this is expensive, with a complicated SQL query over
> the collection table. In this case, it appears that
> calendar_free_busy_set is not in the extant CalDAV RFC - I think it
> appeared in a draft but was then removed. So should Davical be
> populating it at all?
>
> I am considering making code changes to improve these areas. Would the
> community like these committed back to the repository? What about
> (performance-related) database indexes - should these be part of the
> standard build of Davical?
>
> Mark Davies
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general
>
>


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Jim Fenton
Mark,

As Cyril said, we'd be quite interested in these improvements, although
please be patient if it takes us a little while to incorporate them.

The best procedure is to fork the main repository, commit your changes
there, and after testing send us a merge request (Gitlab uses a slightly
different process from the pull request Github uses that most people are
familiar with).

With respect to item 2 (calendar_free_busy_set is not in the extant
CalDAV RFC), it might be a good idea to make that an option -- even
though it's not in the current RFC, there might still be clients that
depend on it, and we don't want to break any of them.

Thanks!

-Jim


On 11/22/2014 12:46 PM, Cyril Giraud wrote:

> You're welcome!
> Community is actually testing davical focusing on next debian release.
>
> Please:
> - create a new issue on https://gitlab.com/groups/davical-project/issues
> - make a separate branch on git to push your performance improvements
> (please, include the name of the issue on the branch name). Network:
> https://gitlab.com/davical-project/davical/network/master
> - when your code is ok and tested, email us on this list
> - we'll discuss about merging on next davical meeting (you, and all the
> community are welcome for davical IRC meetings). Next meeting probably
> 15th Dec, 19:00 UTC, according to Jim emails.
>
> Ask on this list if you fail to get write access to gitlab (or Wiki).
>
> Cyril.
>
>
> On 21/11/2014 11:40, Mark Davies wrote:
>> I am looking at Davical performance, since my own servers are beginning
>> to struggle under the load of thousands of calendars and calendar items.
>>
>> I notice that the standard Davical install script does not apply any
>> database indexes, over and above those required for key constraints.
>> Does anyone have any experience in this area, e.g. recommendations for
>> suitable indexes to apply to improve query and update performance? I am
>> thinking that the caldav_data and calendar_item tables in particular are
>> suitable for optimisation with some carefully-chosen indexes.
>>
>> I also notice that there are two areas of the Davical code which incur a
>> substantial performance hit when a single user has many calendars:
>>
>> 1. When retrieving a principal, the "schedule-default-calendar-URL" is
>> always populated. This runs a potentially expensive SQL query (actually
>> expensive on my server!) to retrieve all calendars for the given user,
>> then searches through them all (in PHP) looking for one called "home";
>> if not found, it uses the first calendar. I think the query needs to be
>> improved here so that the database does more of the work. Even better,
>> perhaps the home calendar URL ought to be stored in the database as a
>> separate field, e.g. on the "principal" table.
>>
>> 2. Also when retrieving a principal, a "calendar_free_busy_set" property
>> is populated. Again this is expensive, with a complicated SQL query over
>> the collection table. In this case, it appears that
>> calendar_free_busy_set is not in the extant CalDAV RFC - I think it
>> appeared in a draft but was then removed. So should Davical be
>> populating it at all?
>>
>> I am considering making code changes to improve these areas. Would the
>> community like these committed back to the repository? What about
>> (performance-related) database indexes - should these be part of the
>> standard build of Davical?
>>
>> Mark Davies
>>
>> ------------------------------------------------------------------------------
>> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREE
>> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Davical-general mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/davical-general
>>
>>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general



------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Mark Davies
In reply to this post by Cyril Giraud-2
OK, many thanks Stefan, Cyril and Jim for your replies. I'll go ahead then.

I have raised issue #31 on Gitlab to track my changes. I have created a
branch of the same name in my local repository and will push this up to
Gitlab and make a merge request once I have some useful, tested changes.
I guess might need some additional user permissions for that, but if so
I will ask you chaps when I get there. All is good so far.

Jim's idea of an option to enable/disable the obsolete
calendar_free_busy_set property sounds sensible, so I will do that.

Looking at the database upgrade scripts in dba/patches, it's not
entirely clear to me what version number I need on any upgrade script I
create (e.g. to apply new indexes). Does it depend on how quickly my
changes are (a) committed to Gitlab, and (b) merged back into master?
Presumably the upgrade script to accompany the next release will be
1.2.12.sql, but may also contain changes other than mine?

Cheers
Mark

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Mark Davies
In reply to this post by Jim Fenton
OK chaps, I have a new branch (issue_31_database_performance_
improvements) ready in my local repository. It addresses the most
critical of the points I raised on issue 31.

However as suspected I don't think I have the permissions to push this
branch into the Davical project on Gitlab. Here are the errors I see:

$ git push origin issue_31_database_performance_improvements
Username for 'https://gitlab.com': [hidden email]
...
remote: GitLab: You are not allowed to access some of the refs!
To https://gitlab.com/davical-project/davical.git
 ! [remote rejected] issue_31_database_performance_improvements ->
issue_31_data
base_performance_improvements (pre-receive hook declined)
error: failed to push some refs to
'https://gitlab.com/davical-project/davical.git'

My username on Gitlab is my email - [hidden email] - please
can someone allow me access? (Or tell me what I'm doing wrong if I
haven't got the git stuff right).

Thanks!

Cheers
Mark

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: Davical performance

Josef Kufner
This is not how Git works. You are expected to push your changes to your
public repository (clone of Davical plus your commits). And then send
pull request (or create merge request in Gitlab terminology).
Maintainsers or Davical then can merge your merge request, discuss
particular changes, you can add more commits, and so on.


Mark Davies wrote, on 3.12.2014 18:12:

> OK chaps, I have a new branch (issue_31_database_performance_
> improvements) ready in my local repository. It addresses the most
> critical of the points I raised on issue 31.
>
> However as suspected I don't think I have the permissions to push this
> branch into the Davical project on Gitlab. Here are the errors I see:
>
> $ git push origin issue_31_database_performance_improvements
> Username for 'https://gitlab.com': [hidden email]
> ...
> remote: GitLab: You are not allowed to access some of the refs!
> To https://gitlab.com/davical-project/davical.git
>  ! [remote rejected] issue_31_database_performance_improvements ->
> issue_31_data
> base_performance_improvements (pre-receive hook declined)
> error: failed to push some refs to
> 'https://gitlab.com/davical-project/davical.git'
>
> My username on Gitlab is my email - [hidden email] - please
> can someone allow me access? (Or tell me what I'm doing wrong if I
> haven't got the git stuff right).
>
> Thanks!
>
> Cheers
> Mark
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general
>

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

signature.asc (836 bytes) Download Attachment