Even worse, row log buffer is finite and we have to complete online ddl before row log buffer become full (But MySQL 5.6 and MariaDB 10.0 does not tell me how much buffer is used either).
Sometimes, we have to wait for online ddl statement to be done, but it could be failed because of insuffcient row log buffer. We don't know how many hours we have to wait.
I think row log buffer usage and the progress of inplace alter is good metric to determine to continue altering or not.
So I added 3 global status variables. Actually I wanted to print these metric to client console - like MariaDB's copy style alter progressing. But it's not so easy, becuase copy altering is processed by MySQL handler, but inplace altering is processed by storage engine. So I choose the simple way - just adding status variables. Becuase it's global status variables, it's useless if there's two or more concurrent inplace alter session.
- Innodb_onlineddl_rowlog_rowsShows how many rows are stored in row log buffer.
- Innodb_onlineddl_rowlog_pct_usedShows row log buffer usage in percent ( *100%, it's 4-digit. 10000 means 100.00% ).
- Innodb_onlineddl_pct_progressShows the progress of inplace alter table. It might be not so accurate becuase inplace alter is highly depend on disk and buffer pool status. But still it is useful and better than nothing.
Innodb_onlineddl_pct_progress is based on estimation, but Innodb_onlineddl_rowlog_rows and Innodb_onlineddl_rowlog_pct_used is accurate value.
The percent reported by Innodb_onlineddl_pct_progress is not so accurate. Becuase inplace alter table is highly depend on InnoDB buffer pool warming-up status and disk throughput and some other things.
This features doesn't take into all these factor to calculate progressing. Now just take into account each index's page count and some hunches.
Online DDL is consist of two big(time consuming) task.
- 1. Read all rows and store it to buffer from old table
- 2. Rebuild each index
The first task need only once per ddl statement, but second is needed for each index which need to be rebuilt.
And simply, I assign weight 1.0 to above two main task. And second task, weight 1.0 is splited into fixed weight(0.5) and dynamic weight(0.5).
So all index's weight is 0.5 at minimum. and each index get a weight of dynamic ratio of total dynamic weight of all indexes based on their page count.
And second task is consist of two sub-task.
- 2-1. Sort & merge buffer
- 2-2. Insert sort-merged buffer to real index tree
Each sub-task is also highly depend on the disk throughput and buffer pool warming up status. So I assign 40% for first sub-task and 60% for second sub-task my own hunch (It's not based on some math or Big-O things.. ).
I have to assign some weight and percent for each task and sub-task because Online DDL is separated with independent functions.
For example,
Let's think about add new column to existing table which have two index including primary key.
And let's assume primary key's total page count is 100 and ix_fd2 index's total page count is 50.
CREATE TABLE tb_test(
fdpk int,
fd1 varchar(10),
fd2 bigint,
primary key (fdpk),
index ix_fd2(fd2)
) ENGINE=InnoDB;
ALTER TABLE tb_test ADD fd3 DATETIME, LOCK=NONE, ALGORITHM=INPLACE;
According to above weighting, total weight would be 3 (Task 1 and Task 2 for two indexes) and total dynamic weight would be 1(0.5 for each index).
Primary key will get 1.1667[= 0.5(fixed weight) and 0.6667(dynamic weight, 1.0 * 100/(100+50)) ] and second index(ix_fd2) will get 0.8333[= 0.5(fixed weight) and 0.3333(dynamic weight, 1.0 * 50/(100+50)) ].
So all task's weight would be assigned like below.
[weight:1.0000] 1. Read all rows and store it to buffer from old table
[weight:1.1667] 2. Rebuild primary key
[weight:40% of 1.1667] 2-1. Sort & merge buffer
[weight:60% of 1.1667] 2-2. Insert sort-merged buffer to real index tree
[weight:0.8333] 3. Rebuild secondary index (ix_fd2)
[weight:40% of 0.8333] 3-1. Sort & merge buffer
[weight:60% of 0.8333] 3-2. Insert sort-merged buffer to real index tree
Finally, we can calculate percent of consuming time for each task and sub-task.
[Time:33.33%] 1. Read all rows and store it to buffer from old table
[Time:38.89%] 2. Rebuild primary key
[Time:15.56%] 2-1. Sort & merge buffer
[Time:23.33%] 2-2. Insert sort-merged buffer to real index tree
[Time:27.78%] 3. Rebuild secondary index (ix_fd2)
[Time:11.11%] 3-1. Sort & merge buffer
[Time:16.67%] 3-2. Insert sort-merged buffer to real index tree
So, if inplace alter is completed to 2.2 than current progress is 72.22%.
And I also added some message which tell you what task is running and calculated weight for each task and sub-task.
140921 13:58:44 [Warning] Online DDL : Start
140921 13:58:44 [Warning] Online DDL : Start reading clustered index of the table and create temporary files
140921 14:01:08 [Warning] Online DDL : End of reading clustered index of the table and create temporary files
140921 14:01:08 [Warning] Online DDL : Start merge-sorting index PRIMARY (1 / 2), estimated cost : 15.5547%
140921 14:03:24 [Warning] Online DDL : End of merge-sorting index PRIMARY (1/ 2)
140921 14:03:24 [Warning] Online DDL : Start building index PRIMARY (1 / 2), estimated cost : 23.3321%
140921 14:07:21 [Warning] Online DDL : End of building index PRIMARY (1 / 2)
140921 14:07:21 [Warning] Online DDL : Completed
140921 14:07:21 [Warning] Online DDL : Start merge-sorting index ix1 (2 / 2), estimated cost : 11.1119%
140921 14:09:44 [Warning] Online DDL : End of merge-sorting index ix1 (2 / 2)
140921 14:09:44 [Warning] Online DDL : Start building index ix1 (2 / 2), estimated cost : 16.6679%
140921 14:13:12 [Warning] Online DDL : End of building index ix1 (2 / 2)
140921 14:13:12 [Warning] Online DDL : Completed
I ran the some test for checking the accuracy of estimating inplace alter progress.
-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 13GB
-- // index size : 7GB
-- // ---------------------------
CREATE TABLE tb_onlineddl1 (
pk1 int(11) NOT NULL,
pk2 bigint(20) NOT NULL,
fd1 bigint(20) DEFAULT NULL,
fd2 bigint(20) DEFAULT NULL,
fd3 datetime DEFAULT NULL,
fd4 text,
fd5 varchar(50) DEFAULT NULL,
fd6 bigint(20) DEFAULT NULL,
fd7 bigint(20) DEFAULT NULL,
PRIMARY KEY (pk1, pk2),
UNIQUE KEY ux1 (pk2, pk1),
KEY ix1 (fd6, fd7)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 4GB
-- // index size : 2.5GB
-- // ---------------------------
CREATE TABLE tb_onlineddl2 (
pk1 int(11) NOT NULL,
pk2 bigint(20) NOT NULL,
PRIMARY KEY (pk1, pk2),
KEY ix1 (pk2, pk1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE tb_onlinedd1 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;
ALTER TABLE tb_onlinedd2 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;
And I make two chart for the change of "Innodb_onlineddl_pct_progress" status variable during alter.
The first part of the chart, there's some inaccurate angle, but the other part is looks good. ^^
You can found the source cod change from Kakao Github .
