From a7077c43b1c7b4a7213e40aac66471fc7738f893 Mon Sep 17 00:00:00 2001 From: Andreas Jonsson Date: Wed, 10 Apr 2024 17:48:14 +0000 Subject: [PATCH] Bug 36569: Compact action log/combined index With cataloguing logging enabled on the action_logs table it will become large. The module and action columns in action_logs are MEDIUMTEXT (max ~16 million bytes) with 196 first characters used for the index. Since these columns holds names that are only used programatically we could reduce the size of these columns to make the index more compact. Since module and action is often used together we can instead create a combined index and still have a smaller table. Test plan: * Start koha-testing-docker without patch applied (ku) * Apply patch * Run the command: sudo koha-upgrade-schema kohadev * Verify database schema: > sudo koha-mysql kohadev -t -e 'SHOW CREATE TABLE action_logs' | \ grep -E '`action`|`module`' The output should be (truncated long lines here): `module` varchar(30) CHARACTER SET ascii COLLATE ascii_general_ci ... `action` varchar(30) CHARACTER SET ascii COLLATE ascii_general_ci ... KEY `module_idx` (`module`), KEY `action_idx` (`action`), KEY `module_action_idx` (`module`,`action`), * Run unit test: prove t/db_dependent/Koha/ActionLogs.t * Stop koha-testing-docker and delete the containers (kd) * Start koha-testing-docker with patch applied * Verify database schema: > sudo koha-mysql kohadev -t -e 'SHOW CREATE TABLE action_logs' | \ grep -E '`action`|`module`' The output should be same as above --- .../bug_36569-compact-action_logs-indices.pl | 19 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 9 +++++---- 2 files changed, 24 insertions(+), 4 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_36569-compact-action_logs-indices.pl diff --git a/installer/data/mysql/atomicupdate/bug_36569-compact-action_logs-indices.pl b/installer/data/mysql/atomicupdate/bug_36569-compact-action_logs-indices.pl new file mode 100755 index 0000000000..26ef71f253 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_36569-compact-action_logs-indices.pl @@ -0,0 +1,19 @@ +use Modern::Perl; + +return { + bug_number => "36569", + description => "Compact action_logs columns module and actions", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + say $out "Modify columns module and action, this can take a long time if the table action_logs is large."; + say $out "Make sure that there is disk space for an extra copy of action_logs while upgrading!"; + $dbh->do( + q{ALTER TABLE action_logs MODIFY COLUMN module VARCHAR(30) CHARSET ascii, MODIFY COLUMN action VARCHAR(30) CHARSET ascii} + ); + + say $out "Create combined index action_logs(module, action)"; + $dbh->do(q{CREATE INDEX IF NOT EXISTS module_action_idx ON action_logs(module, action)}); + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3828776962..dcb7c04908 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -166,8 +166,8 @@ CREATE TABLE `action_logs` ( `action_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for each action', `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'the date and time the action took place', `user` int(11) NOT NULL DEFAULT 0 COMMENT 'the staff member who performed the action (borrowers.borrowernumber)', - `module` mediumtext DEFAULT NULL COMMENT 'the module this action was taken against', - `action` mediumtext DEFAULT NULL COMMENT 'the action (includes things like DELETED, ADDED, MODIFY, etc)', + `module` varchar(30) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'the module this action was taken against', + `action` varchar(30) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'the action (includes things like DELETED, ADDED, MODIFY, etc)', `object` int(11) DEFAULT NULL COMMENT 'the object that the action was taken against (could be a borrowernumber, itemnumber, etc)', `info` mediumtext DEFAULT NULL COMMENT 'information about the action (usually includes SQL statement)', `interface` varchar(30) DEFAULT NULL COMMENT 'the context this action was taken in', @@ -176,8 +176,9 @@ CREATE TABLE `action_logs` ( PRIMARY KEY (`action_id`), KEY `timestamp_idx` (`timestamp`), KEY `user_idx` (`user`), - KEY `module_idx` (`module`(191)), - KEY `action_idx` (`action`(191)), + KEY `module_idx` (`module`), + KEY `action_idx` (`action`), + KEY `module_action_idx` (`module`, `action`), KEY `object_idx` (`object`), KEY `info_idx` (`info`(191)), KEY `interface` (`interface`) -- 2.39.2