I backed the database up, opened it up in Sequel Pro and went to the civicrm_system_log table. Under "Table Info" I could select "InnoDB" from the dropdown. Then I cleared the caches on the CiviCRM installation and the system status came back green ("System Status: OK").
Per a comment from George Lozier on another thread, the MySQL query to do the same is:
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 ...
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 ...
A few orgs who use logging on fairly high volume sites have modified the logging tables to either use MyISAM or InnoDB and have had fairly good success with it. Can you please report back if you adopt this approach and your success with it
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.
My strong advice to you would be to change the tables to be anything but ARCHIVE. Even InnoDB would be fine and preferred over ARHCIVE. We've had a number of sites have their entire audit schema's become corrupt and its been down to the storage engine being ARCHIVE.
Also you'll probably want to add indexes into the audit schema otherwise you cant access the ...
Found the problem. Corrupt log tables. Causes MEGA-chaos for poor mysql. It's crash-a-roo anytime a CiviCRM table is used.
Find where the civicrm databases are stored. If on a typical *nix distribution and using mysql, likely /var/lib/mysql/civicrm
Shutdown mysql (service mysql stop)
Create a new directory (mkdir logtables), now move all the ...
Short answer: Change the storage engine of the table with the following command in mysql:
ALTER TABLE log_civicrm_install_canary ENGINE=InnoDB
The civicrm_install_canary is a table used for installation purposes. The purpose is explained in the answer on What is the installation canary?
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!
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)