CiviCRM Community Forums (archive)

Hi, I'm noticing db size blow out due to logging being on for some tables that have a lot of unimportant stuff written to them. I can delete the triggers but they come back.

The table that seems truly unnecessary to log which is a stand out for me is the civicrm_job_log table - the fact there is a cleanup routine on this suggests the data does not have a long term value & I would suggest we specifically remove it from this table.

The other tables that I am noticing are mailing response tables (bounces etc) (& some custom value tables which is customer specific issue).

I think 4.4 onwards we no longer log tables like civicrm_job_log, I can't seem to find an issue though ..

Kurund

There is also an opportunity for reducing bloat by comparing all fields for old vs new in the trigger and only writing if there is an actual change. Our code is running many update queries that don't change the data, leading to unnecessary log table bloat. I might be wrong, but changing a single field on a contact edit form will as I understand it result in many records in many tables being updated because our libraries / packages don't do this work for us. Putting such cheques into the triggers for non log tables would significantly reduce disk access and improve performance even when logging is off. It would also make it easier to make sense of the logs for an entry / record / contact. unfortunately when I tried to prototype this I found that cancelling the write ended up creating errors bubbling up from mysql to php in a way that would have required chequing particular mysql error codes on every db write, and I didn't think our writes all went through our DAOs so the scope was too big to handle for a small client's budget.

That's possible. My investigation was a long time ago and in response to a query / concern from BackOffice Thinking.