Thank you, @tmortagne, for your pointers.
Please see below for the screenshot of mytop when the initialization got stuck at 12%. Just let me know if you have some thoughts.
About the schema’s definition, the command show create database xwiki; gets CREATE DATABASE xwiki /*!40100 DEFAULT CHARACTER SET latin1 */, as it comes from an old instance.
In addition, I generated a script of the DDL statements without any data by adding argument --no-data in command mysqldump.exe --skip-lock-tables --routines --add-drop-table --disable-keys --extended-insert --no-data ... . Please see the attached schema definitions if necessary. Trying to save you some communication time, but I am not sure if helpful.
xwikiv-mariadb-v10.9-structure-only-xwiki-202209161220.sql.txt (30.2 KB)
Details of the mytop screenshots:
- The correct test, after releasing the
DATABASECHANGELOGLOCKby SQL statementUPDATE DATABASECHANGELOGLOCK SET locked=0, lockgranted=null, lockedby=null WHERE id=1;:
MariaDB 10.9.2 on localhost load (1.35 0.94 0.49) up 0+00:07:32 [11:33:09]
Queries: 692.0 qps: 2 Slow: 0.0 Se/In/Up/De(%): 39/00/00/00
Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 4 ( 2/ 0) 25/00/00/00
Handler: (R/W/U/D) 0/ 1762/ 0/ 0 Tmp: R/W/U: 113/ 113/ 0
MyISAM Key Cache Efficiency: 100.0% Bps in/out: 493.0/ 4.2k Now in/out: 97.8/ 3.9k
Id User Host/IP DB Time % Cmd State Query
-- ---- ------- -- ---- - --- ----- ----------
5 xwiki localhost:41290 xwiki 361.4 0.0 Sleep
7 xwiki localhost:41294 xwiki 360.9 0.0 Sleep
6 xwiki localhost:41292 xwiki 358.9 29.2 Query 1/2 copy to tmp Alter table xwikistatsvisit MODIFY XWV
After stopping tomcat, the stuck SQL query Alter table xwikistatsvisit MODIFY XWV_NAME varchar(768) not null remains:
MariaDB 10.9.2 on localhost load (1.29 1.17 0.70) up 0+00:12:03 [11:37:40]
Queries: 909.0 qps: 1 Slow: 0.0 Se/In/Up/De(%): 36/00/00/00
Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 2 ( 2/ 2) 25/00/00/00
Handler: (R/W/U/D) 0/ 1820/ 0/ 0 Tmp: R/W/U: 113/ 113/ 0
MyISAM Key Cache Efficiency: 100.0% Bps in/out: 345.0/ 4.1k Now in/out: 97.8/ 3.9k
Id User Host/IP DB Time % Cmd State Query
-- ---- ------- -- ---- - --- ----- ----------
6 xwiki localhost:41292 xwiki 630.3 35.3 Query 1/2 copy to tmp Alter table xwikistatsvisit MODIFY XWV_NAME varchar(768) not null
- In a previous test, I didn’t release the
DATABASECHANGELOGLOCK, so I guess the below result is incorrect:
MariaDB 10.9.2 on localhost load (0.00 0.08 0.08) up 0+02:08:37 [11:20:01]
Queries: 2.2k qps: 0 Slow: 0.0 Se/In/Up/De(%): 30/00/00/00
Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 4 ( 1/ 0) 25/00/00/00
Handler: (R/W/U/D) 0/ 0/ 0/ 0 Tmp: R/W/U: 113/ 113/ 0
MyISAM Key Cache Efficiency: 100.0% Bps in/out: 49.9/ 1.2k Now in/out: 97.6/ 3.9k
Id User Host/IP DB Time % Cmd State Query
-- ---- ------- -- ---- - --- ----- ----------
11 xwiki localhost:40412 xwiki 298.1 0.0 Sleep
13 xwiki localhost:40416 xwiki 297.5 0.0 Sleep
12 xwiki localhost:40414 xwiki 6.5 0.0 Sleep