Mailing List Archive: DAViCal: General

sync_tokens table has 15million rows?

Index | Next | Previous | View Threaded

alavaliant at gmail

Jul 15, 2012, 4:02 AM

Post #1 of 4 (227 views)
Permalink

sync_tokens table has 15million rows?

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
Davical-general [at] lists
https://lists.sourceforge.net/lists/listinfo/davical-general

jan.mate at inf-it

Jul 15, 2012, 3:13 PM

Post #2 of 4 (214 views)
Permalink

Re: sync_tokens table has 15million rows? [In reply to]

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 <alavaliant [at] gmail> 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
> Davical-general [at] lists
> 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
Davical-general [at] lists
https://lists.sourceforge.net/lists/listinfo/davical-general

andrew at morphoss

Jul 16, 2012, 2:11 PM

Post #3 of 4 (209 views)
Permalink

Re: sync_tokens table has 15million rows? [In reply to]

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.
------------------------------------------------------------------------

Attachments:

signature.asc (0.82 KB)

alavaliant at gmail

Jul 17, 2012, 3:08 PM

Post #4 of 4 (206 views)
Permalink

Re: sync_tokens table has 15million rows? [In reply to]

On Tue, Jul 17, 2012 at 9:11 AM, Andrew McMillan <andrew [at] morphoss> 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
Davical-general [at] lists
https://lists.sourceforge.net/lists/listinfo/davical-general

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads