[Davical-general] sync_tokens table has 15million rows?

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

[Davical-general] sync_tokens table has 15million rows?

Jason alavaliant
Hi,

The company I work for runs a large davical install,  recently we have
been seeing slowness in some queries and load on our postgres server.
 So one of our dbas did some digging through the queries and has come
to the conclusion that the main cause of the slowdown is that our
sync_tokens table has somehow grown to 15.4million rows which is
making any operation to do with it understandably slow.

 We aren't 100% sure of the safest way to deal with this however or
what would have triggered it.    I've been reading the twiki and old
mailing list posts but I'm still not really sure of the ramifications
of attempting to manually remove rows from that table since nobody
else seems to have hit anything like this.    If we start removing
tokens with a modification_time older than a certain value with
anything else break because it's token vanishes?   (Reading through
the davical code the only line I can see that mentions deleting from
the sync_tokens table is one that removes older one sync tokens aften
new ones have been created for the collection so I'm unsure if we need
to keep a certain amount since they only seem to get deleted in very
specific situations? (or is davical perhaps just lacking the cleanup
function and all sync_tokens tables are slowly filling up?))

The question of how things bloated to 15.4million rows is also rather
interesting,  my minimal understanding of that table makes me think
it's mainly used for free/busy request management?   Only a small
percentage of our clients would be making use of that currently (At a
guess we'd have several hundred clients connected to the server with
perhaps 20-50 using freebusy - it's hard to judge since our users only
started experimenting with it recently.)   Even though our mixture of
iCal and thunderbird/lightning clients should all support scheduling
so they could be doing something in the background without a user
requesting it?   Could a misbehaving client be somehow requesting
something that is making extra sync_tokens be generated?

Anyway thoughts on the matter much appreciated.

Thanks
-J

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: sync_tokens table has 15million rows?

Ján Máté-2
Hi Jason,

it is 100% safe to delete old/all sync-tokens and sync_changes (I had the same problem in past and cleanup was recommended by davical main developer - cite: "Things should recover OK from that."). In worst case you will need to restart your davical clients.

As I know in the latest version there is something for sync-token cleanup, but I don't know how it works (maybe Andrew will reply).

JM


On Jul 15, 2012, at 1:02 PM, Jason alavaliant <[hidden email]> wrote:

> Hi,
>
> The company I work for runs a large davical install,  recently we have
> been seeing slowness in some queries and load on our postgres server.
> So one of our dbas did some digging through the queries and has come
> to the conclusion that the main cause of the slowdown is that our
> sync_tokens table has somehow grown to 15.4million rows which is
> making any operation to do with it understandably slow.
>
> We aren't 100% sure of the safest way to deal with this however or
> what would have triggered it.    I've been reading the twiki and old
> mailing list posts but I'm still not really sure of the ramifications
> of attempting to manually remove rows from that table since nobody
> else seems to have hit anything like this.    If we start removing
> tokens with a modification_time older than a certain value with
> anything else break because it's token vanishes?   (Reading through
> the davical code the only line I can see that mentions deleting from
> the sync_tokens table is one that removes older one sync tokens aften
> new ones have been created for the collection so I'm unsure if we need
> to keep a certain amount since they only seem to get deleted in very
> specific situations? (or is davical perhaps just lacking the cleanup
> function and all sync_tokens tables are slowly filling up?))
>
> The question of how things bloated to 15.4million rows is also rather
> interesting,  my minimal understanding of that table makes me think
> it's mainly used for free/busy request management?   Only a small
> percentage of our clients would be making use of that currently (At a
> guess we'd have several hundred clients connected to the server with
> perhaps 20-50 using freebusy - it's hard to judge since our users only
> started experimenting with it recently.)   Even though our mixture of
> iCal and thunderbird/lightning clients should all support scheduling
> so they could be doing something in the background without a user
> requesting it?   Could a misbehaving client be somehow requesting
> something that is making extra sync_tokens be generated?
>
> Anyway thoughts on the matter much appreciated.
>
> Thanks
> -J
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Davical-general mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/davical-general


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

[Davical-general] Merging calendars on server side?

Bernd Hohmann
Hi,

I'm satisfied with davical in my small installation.

But I stumbled over a problem and didn't found a solution.

Issue: I have my own calendar, the calendar of my wife, public and
school holidays. Thunderbird/Lightning can merge calendars on client
side perfectly, Outlook 2003 (my wife demands it) can't and displays the
calendars side by side which gets confusing.

I guess other calendar clients have the same problem.

So I'm looking for a solution to tell the server "for user $wife merge
calendar '$husband $wife $public_holidays $waste_collection
$daily_reciepes' together"

Any ideas?

Bernd


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|

Re: sync_tokens table has 15million rows?

Andrew McMillan
In reply to this post by Jason alavaliant
On Sun, 2012-07-15 at 23:02 +1200, Jason alavaliant wrote:
> Hi,
>
> The company I work for runs a large davical install,  recently we have
> been seeing slowness in some queries and load on our postgres server.
>  So one of our dbas did some digging through the queries and has come
> to the conclusion that the main cause of the slowdown is that our
> sync_tokens table has somehow grown to 15.4million rows which is
> making any operation to do with it understandably slow.

Wow :-)

You'll be pleased to know that 1.1.1 includes some code to clean out old
rows whenever new rows are inserted.  In the steady state you should end
up with about a weeks worth of changes (maximum), or two changes
(minimum) for each calendar collection.

As Mate noted: it's completely OK to truncate the table.

Cheers,
                                        Andrew.
--
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
           Cohen's Law:
                      There is no bottom to worse.
------------------------------------------------------------------------


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Merging calendars on server side?

Andrew McMillan
In reply to this post by Bernd Hohmann
On Mon, 2012-07-16 at 00:25 +0200, Bernd Hohmann wrote:

> Hi,
>
> I'm satisfied with davical in my small installation.
>
> But I stumbled over a problem and didn't found a solution.
>
> Issue: I have my own calendar, the calendar of my wife, public and
> school holidays. Thunderbird/Lightning can merge calendars on client
> side perfectly, Outlook 2003 (my wife demands it) can't and displays the
> calendars side by side which gets confusing.
>
> I guess other calendar clients have the same problem.
I haven't actually heard of any others that do it that way.


> So I'm looking for a solution to tell the server "for user $wife merge
> calendar '$husband $wife $public_holidays $waste_collection
> $daily_reciepes' together"
>
> Any ideas?

There is a trick you can do which is as follows:

1) Create a normal collection (not a calendar or addressbook) in your
wife's account.

2) BIND each of the collections to be merged into that collection
(there's been discussion about BIND on this mailing list before).

3) Enable the 'get_includes_subcollections' configuration option.

This will arrange things so that GET requests
on .../caldav.php/wifeusername/normalcollection.ics will return a merged
set of all of the bound collections.

Kinda complicated, and no UI for it :-(


The only problem is that this calendar will be read-only, so you might
still need to keep your wife's main collection separate so she can write
to that.

Cheers,
                                        Andrew.
--
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
    Each man is his own prisoner, in solitary confinement for life.
------------------------------------------------------------------------


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: sync_tokens table has 15million rows?

Jason alavaliant
In reply to this post by Andrew McMillan
On Tue, Jul 17, 2012 at 9:11 AM, Andrew McMillan <[hidden email]> wrote:

> On Sun, 2012-07-15 at 23:02 +1200, Jason alavaliant wrote:
>> Hi,
>>
>> The company I work for runs a large davical install,  recently we have
>> been seeing slowness in some queries and load on our postgres server.
>>  So one of our dbas did some digging through the queries and has come
>> to the conclusion that the main cause of the slowdown is that our
>> sync_tokens table has somehow grown to 15.4million rows which is
>> making any operation to do with it understandably slow.
>
> Wow :-)
>
> You'll be pleased to know that 1.1.1 includes some code to clean out old
> rows whenever new rows are inserted.  In the steady state you should end
> up with about a weeks worth of changes (maximum), or two changes
> (minimum) for each calendar collection.
>
> As Mate noted: it's completely OK to truncate the table.
>

Thanks for the confirmation,  we'll look at upgrading our install.

-J

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general