Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Phil Dye 149 posts 325 karma points
    Dec 01, 2015 @ 13:03
    Phil Dye
    0

    Pruning database

    Over a few years, we've ended up with a HUGE number of rows in the Visitor tables from profiling. Is there an efficient way to prune some of this old history (eg anyone with a LastVisit date more than year ago)?

    Raw SQL DELETE's cause lock issues that take the site down. I can do with it with DELETEs of smaller batches but it's a tedious process. Does the product not perform it's own housekeeping?

    Phil

    Copy Link
  • Phil Dye 149 posts 325 karma points
    Dec 01, 2015 @ 16:34
    Phil Dye
    0

    I've finally been able to work out that there are ~23 million rows in Visitors, 3 million in VisitorTriggerRules !

    Copy Link
  • SpindoctorBT 33 posts 124 karma points
    Dec 08, 2015 @ 20:43
    SpindoctorBT
    0

    Hi Phil,

    I am afraid there's no housekeeping logic built-in, definately a good idea for the roadmap, but that doesn't help you much right now.

    I can imagine that lots of these visits are bots, I assume you excluded those using this: http://www.spindoctorhq.com/blog/2013/12/14/ignoring-crawlers-how-to/

    My advice for now would be to copy the recent/relevant data to a new table, truncate the two tables and re-insert the copied data. This skips the sql server logging part that slows the delete down.

    Sorry I can't give you anything better at this point. Hope this helps.

    Thanks,

    Wouter

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft