Bugzilla – Attachment 164632 Details for
Bug 36569
Compact action_logs indicies
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 36569: Compact action log/combined index
Bug-36569-Compact-action-logcombined-index.patch (text/plain), 4.75 KB, created by
Andreas Jonsson
on 2024-04-10 18:02:58 UTC
(
hide
)
Description:
Bug 36569: Compact action log/combined index
Filename:
MIME Type:
Creator:
Andreas Jonsson
Created:
2024-04-10 18:02:58 UTC
Size:
4.75 KB
patch
obsolete
>From a7077c43b1c7b4a7213e40aac66471fc7738f893 Mon Sep 17 00:00:00 2001 >From: Andreas Jonsson <andreas.jonsson@kreablo.se> >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 36569
:
164631
| 164632