The table action_logs is written too at a higher rate than practically any other table. Additionally, action_logs has no foreign key constraints. It seems that this would be a somewhat unique situation where using MyISAM would provide quite a performance boost for large sites with many transactions that use the Action Logs.
Created attachment 22378 [details] [review] Bug 11136 - action_logs should be a MyISAM table, not InnoDB The table action_logs is written too at a higher rate than practically any other table. Additionally, action_logs has no foreign key constraints. It seems that this would be a somewhat unique situation where using MyISAM would provide quite a performance boost for large sites with many transactions that use the Action Logs.
Hi, I set 'In discussion' for two points: 1) In fact it is an DB update, so the patch needs to change also installer/data/mysql/kohastructure.sql 2)Are we sure that is a good idea to use two different engine of MySQL at same time in Koha ? Reading those two pagees we can think that mixing InnoDB and MySQL needs a quite tuning to be useful: http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-multiple-storage-engines/ http://dba.stackexchange.com/questions/9221/any-problem-will-all-innodb-and-one-myisam-table
Created attachment 22426 [details] [review] Bug 11136 - action_logs should be a MyISAM table, not InnoDB The table action_logs is written too at a higher rate than practically any other table. Additionally, action_logs has no foreign key constraints. It seems that this would be a somewhat unique situation where using MyISAM would provide quite a performance boost for large sites with many transactions that use the Action Logs.
> 1) In fact it is an DB update, so the patch needs to change also > installer/data/mysql/kohastructure.sql Yep, I just thought of that too! > 2)Are we sure that is a good idea to use two different engine of MySQL at > same time in Koha ? > Reading those two pagees we can think that mixing InnoDB and MySQL needs a > quite tuning to be useful: We already have MyISAM tables, so there is already precedent for this.
Created attachment 24210 [details] [review] [SIGNED OFF] Bug 11136 - action_logs should be a MyISAM table, not InnoDB The table action_logs is written too at a higher rate than practically any other table. Additionally, action_logs has no foreign key constraints. It seems that this would be a somewhat unique situation where using MyISAM would provide quite a performance boost for large sites with many transactions that use the Action Logs. TEST PLAN --------- NOTE: These instructions toast the database, so backup as needed! 1) open mysql client 2) use the koha database 3) show create table action_logs; -- This should currently be InnoDB listed as the engine. 4) close mysql client 5) apply patch 6) ./installer/data/mysql/updatedatabase.pl -- This should apply the update. 7) open mysql client 8) use the koha database 9) show create table action_logs; -- The engine should now be MyISAM. 10) drop the koha database 11) create the koha database 12) close mysql client 13) go to the staff client and do the web installation 14) open mysql client 15) use the koha database 16) show create table action_logs; -- The engine should now be MyISAM. Also, the AUTO_INCREMENT won't be shown, since the log file is currently empty. Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
I agreed with Kyle's reasoning, noted that both the fresh install and update are done. This will need a number tweak before being pushed to master, as it is 3.13 instead of the expected 3.15, but that's not really an issue I think. Signed off! :)
I disagree with this patch for 1 reason: the MyISAM engine does not support transactions. So when unit tests are launched, the action_logs table will be filled even if the queries are executed into a transaction. I am not sure it is blocker but I have to note this problem.
After further reading, and looking through Koha, I realized that many of our testing modules have begun to use a: $dbh->{AutoCommit} = 0; This is in stark contrast to the autocommit=1 which is implied in MyISAM. Unless there is a way to disconnect all unit tests from triggering an insert into action_logs, we have a problem if there is $dbh->rollback(). If not for this problem, it would be good. Also, have we bench marked action_logs?
t/db_dependent/Members.t calls C4::Member::DelMember which potentially logs based on a system preference. MyISAM is effectively autocommit=1 and prevents transactions. Transactions should be used in tests.
MyISAM does not support transactions, this route seems to have dead ended.