12

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


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


7

Sounds good. To be sure, please execute ///civicrm/menu/rebuild?reset=1&triggerRebuild=1 which fixes the trigger and function definitions in the database.


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


3

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


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.


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.


2

Single command to update all tables is: SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';


2

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


2

Found the problem. Corrupt log tables. Causes MEGA-chaos for poor mysql. It's crash-a-roo anytime a CiviCRM table is used. To resolve, 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 ...


1

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?


1

I had the issue which kept recurring - traced it to an extension that I use for exporting to excel - was creating the temp file in MyISAM - removed the extension and the issue went away.


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!


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


Only top voted, non community-wiki answers of a minimum length are eligible