8

Retrieving the change log for a contact takes ages for my client - maybe a response of 10 minutes to get just 79 records for one contact. The contact database has about 39K rows, but with about 950k rows in the civicrm_log table (there are lots of transactions). Logging is switched on, which at the time seemed a very attractive option.

Would adding an index help? I have defragged & optimised the civicrm_log table, but to no effect.

Improve this question

6 Answers 6

Reset to default
11

If logging is on then results will be retrieved from the log_xxx tables not civicm_log.

Normally these tables use the archive engine - which is not indexable. We have in some cases selectively converted these to INNODB & added indexes

Archive tables are supposed to be quick to write to and slow to read from. They are also non-transactional - which means that if a rollback happens for a given transaction the archive table record will not be rolled back (on rare occasions this has helped us with forensics)

EDIT: also take a look at this extension I wrote https://github.com/eileenmcnaughton/nz.co.fuzion.innodbtriggers which converts log tables to innodb automatically and this blog post for more details: https://civicrm.org/blog/eileen/who-did-what-when

Improve this answer
3

For people googling, I significantly increased the speed of the Change Log by following Eileen's advice in her answer: convert some tables (activity, activity_contact, contact, group, group_contact) from ARCHIVE to INNODB, and adding indexes.

The Change Log still takes ~20 seconds to load, but that's much better than the minutes it used to. We've taken a hit on disk space, and I suppose write speed (though I can't tell the difference), but definitely worth it!

Improve this answer
2

The log tables as originally implemented (I have checked for a few years if this is the case) also contain a large number of superfluous records due to writes that don't actually change the data. Improving the triggers to check for a difference between old and new values before the write to log would improve things for everyone.

Improve this answer
2

Take a look at the INNODB logging extension. It converts the log table format from ARCHIVE to INNODB, adding indexes and speeding up as a result.

Improve this answer
1

Pretty sure an index would help indeed, the question is which one?

Can you log slow queries on your database and see which queries are taking a lot of time, see where it can be added?

finetune how many seconds before a query is slow (any query that takes more than 1 or 2 sec is slow is what I use)

http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

Improve this answer
3
  • Thanks - I know how to reproduce the problem - just click on 'Change Log' tab in the summary screen for almost anyone. I just tried this for someone who has maybe only a very few transactions, and it's still running. So how do I find out which SQL query is running? Or, as you said, how do I log slow queries?
    – Andy Clark
    Commented Nov 13, 2015 at 8:35
  • added a link to mysql doc on slow query log
    – Xavier
    Commented Nov 13, 2015 at 9:12
  • Here's an example. Clicking on the Change Log was triggering this sql: SELECT group_id FROM log_civicrm_group_contact WHERE log_date <= 20180828. Since that table has 2 million + records and it's an ARCHIVE without an index, it's bound to be slow. Other times, it seemed to be spending time sorting the results, so I suspect if you just add indices to the log_date timestamp field for all your big log_* tables, it might fix 90% of the issue.
    – Alan Dixon
    Commented Oct 19, 2018 at 15:45
1

I discussed this with Bjorn Endres, and his idea is to turn logging off, drop the log tables, and then turn logging back on. This is, I think, a good answer. The reason our log tables are so big is because we have transferred data from a legacy system. We don't really need 99% of the data in these logs so this is what we intend to do. Thanks, Bjorn!

Improve this answer
3
  • I've got detailed logging turned on for a couple of current sites I'm working with, and these are set up to use a separate database (not the main civicrm db). Don't know if this might help?
    – Graham
    Commented Nov 18, 2015 at 12:48
  • That's interesting, Graham. I guess this involved changes in Civi? What were they?
    – Andy Clark
    Commented Nov 19, 2015 at 13:13
  • @andy-clarke If you look in the civicrm.settings.php file you'll see there is an option there to define a separate db for logging.
    – Graham
    Commented Nov 20, 2015 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.