Bug 32719 - db_revs 210600001 can take a long time
Summary: db_revs 210600001 can take a long time
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-01-25 07:24 UTC by Magnus Enger
Modified: 2023-01-27 14:16 UTC (History)
2 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2023-01-25 07:24:40 UTC
I did an upgrade from 20.11.06 to 22.05.08 last night, and db_rev 210600001 (bug 28489) took something like an hour. The problem was probably that the sessions table was huge, and should have been cleaned regularly. But it wasn't. 

Here is what the update does: 

        $dbh->do('DELETE FROM sessions');
        $dbh->do('ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL');

I expected the delete to be quick and altering an empty table should also be quick. But it wasn't. "show processlist" showed this for a long time: 

copy to tmp table | ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL

I wonder if the problem is that the DELETE and the ALTER is in the same transaction? Which means the "deleted" data needs to be kept track of, while the "alter" is running? Maybe this could be made more efficient for others with a large sessions table?
Comment 1 Emmi Takkinen 2023-01-26 06:54:13 UTC
If I have understood correctly, while DELETE is performed, deleted rows are still kept in case of a rollback or crash (could be something else, here are some reasons and tips I found: https://mariadb.com/kb/en/big-deletes/).

We faced similar problem while archiving our action_logs table. We ended up renaming action_logs as work_action_logs and creating a new action_logs based on it. Then after moving everything in archive table and back to action_logs work_action_logs is dropped. Could something similar work here? Rename sessions table e.g. drop_sessions -> create new sessions table -> drop renamed table -> alter new sessions table.