[Bug 11136] New: action_logs should be a MyISAM table, not InnoDB
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Bug ID: 11136 Summary: action_logs should be a MyISAM table, not InnoDB Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: gmcharlt@gmail.com Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 --- Comment #1 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 22378 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=22378&action=edit 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |kyle@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Zeno Tajoli <z.tajoli@cineca.it> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |In Discussion CC| |z.tajoli@cineca.it --- Comment #2 from Zeno Tajoli <z.tajoli@cineca.it> --- 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-... http://dba.stackexchange.com/questions/9221/any-problem-will-all-innodb-and-... -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #22378|0 |1 is obsolete| | --- Comment #3 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 22426 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=22426&action=edit 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 --- Comment #4 from Kyle M Hall <kyle@bywatersolutions.com> ---
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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off Patch complexity|Trivial patch |Small patch -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #22426|0 |1 is obsolete| | --- Comment #5 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 24210 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=24210&action=edit [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> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtompset@hotmail.com --- Comment #6 from M. 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! :) -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@biblibre.co | |m --- Comment #7 from Jonathan Druart <jonathan.druart@biblibre.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |In Discussion --- Comment #8 from M. Tompsett <mtompset@hotmail.com> --- 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? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11136 --- Comment #9 from M. Tompsett <mtompset@hotmail.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org