Bug 11136 - action_logs should be a MyISAM table, not InnoDB
Summary: action_logs should be a MyISAM table, not InnoDB
Status: CLOSED WONTFIX
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Kyle M Hall
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-10-24 18:09 UTC by Kyle M Hall
Modified: 2020-06-04 20:32 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 11136 - action_logs should be a MyISAM table, not InnoDB (1.26 KB, patch)
2013-10-24 18:12 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 11136 - action_logs should be a MyISAM table, not InnoDB (1.88 KB, patch)
2013-10-25 16:42 UTC, Kyle M Hall
Details | Diff | Splinter Review
[SIGNED OFF] Bug 11136 - action_logs should be a MyISAM table, not InnoDB (2.72 KB, patch)
2014-01-12 03:54 UTC, Mark Tompsett
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Kyle M Hall 2013-10-24 18:09:07 UTC
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.
Comment 1 Kyle M Hall 2013-10-24 18:12:35 UTC Comment hidden (obsolete)
Comment 2 Zeno Tajoli 2013-10-24 20:29:48 UTC
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
Comment 3 Kyle M Hall 2013-10-25 16:42:31 UTC Comment hidden (obsolete)
Comment 4 Kyle M Hall 2013-10-25 16:45:25 UTC
> 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.
Comment 5 Mark Tompsett 2014-01-12 03:54:47 UTC
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>
Comment 6 Mark Tompsett 2014-01-12 03:57:24 UTC
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! :)
Comment 7 Jonathan Druart 2014-01-16 12:28:31 UTC
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.
Comment 8 Mark Tompsett 2014-01-16 13:23:25 UTC
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?
Comment 9 Mark Tompsett 2014-01-31 17:07:54 UTC
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.
Comment 10 Nick Clemens (kidclamp) 2019-06-06 19:24:33 UTC
MyISAM does not support transactions, this route seems to have dead ended.