[Maria-developers] Lag Free ALTER Table for Slave
22 Jan
2020
22 Jan
'20
12:29 p.m.
Hi Everyone!
This mail will describe usage and implementation of Lag Free Alter. And this
will also answer the question raised by Kristian and Simon J Mudd(in 2016)
Desc:- This will split Alter into 2 different commits. START ALTER and COMMIT
/ROLLBACK ALTER , Start Alter will be written in binlog as soon as we get the
locks for the table, alter will proceeds as usual and at the time of writing
binlog if alter is successful we will write COMMIT Alter other wise ROLLBACK
Alter.
Usage:- Using this feature is quite simple.
1. On master you have to turn on `BINLOG_SPLIT_ALTER` dynamic variable.
2. Slave must be using parallel replication.
Advance Usage:-
So alter is divided like this.
1. START identifier Actual_alter_stmt
2. COMMIT/ROLLBACK identifier Actual_alter_stmt. OR
2. COMMIT/ROLLBACK identifier ALTER
identifier is thread_id.
Questions by Simon Mudd.
>>* this behaviour should be configurable?
Yes.
>> - new global variable on the master to allow injection of this changed event stream?
Right , `BINLOG_SPLIT_ALTER`
>> - a new session variable set in the session where the command is triggered ?
Right , `BINLOG_SPLIT_ALTER`
>> - on slave a setting to indicate how many INPLACE ALTER TABLEs can run at once?
No setting so far , but I am thinking of maximum no of CONCURRENT ALTER =
slave_parallel_threads
>>* how does a DBA monitor what’s going on?
>> - the progress and number of active DDL statements
So as we there is 2 part, So progress will go like this
1. Executing of start alter (this will take most of time)
2. Waiting for commit/rollback Signal
3. Commit/ Rollback Alter.
Number of active ALTER , these will create new threads so DBA can know
using this
or I am thinking of adding variable in SHOW SLAVE INFO. which will
show active DDL.
>> - please consider adding counters/metrics for:
>> * number of “asynchronous DDLs” in progress / completed successfully / failed or rolled back
Okay, We can have counter for these metrics. If i get time to implement this.
>> * sizes of the DDL changes made so far
Not sure if we need this.
>> * number of threads busy in this state (maybe can be implied from SHOW PROCESSLIST or equivalent but nicer to have an easy to query metric)
Show processlist will show the busy threads.
Sample Output when concurrent alter is running on slave.
slave_parallel_threads= 10
Every 1.0s: mysql -uroot -S
/home/sachin/alter/build/mysql-test/var/tmp/mysqld.2.sock -e show
processlist
sachin-sp52: Wed Jan 22 16:56:00 2020
Id User Host db Command Time State Info Progress
1 system user NULL Daemon NULL InnoDB purge
coordinator NULL 0.000
3 system user NULL Daemon NULL InnoDB purge
worker NULL 0.000
2 system user NULL Daemon NULL InnoDB purge
worker NULL 0.000
4 system user NULL Daemon NULL InnoDB purge
worker NULL 0.000
5 system user NULL Daemon NULL InnoDB
shutdown handler NULL 0.000
10 root localhost:44478 test Sleep 6 NULL 0.000
11 root localhost:44480 test Sleep 7 NULL 0.000
15 root localhost:44492 test Sleep 6 NULL 0.000
16 root localhost:44494 test Sleep 6 NULL 0.000
17 system user NULL Slave_IO 6
Waiting for master to send event NULL 0.000
19 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
22 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
20 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
23 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
24 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
25 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
26 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
21 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
27 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
28 system user NULL Slave_worker 6
Waiting for work from SQL thread NULL 0.000
18 system user NULL Slave_SQL 6 Slave
has read all relay log; waiting for the slave I/O thread to update it
NULL 0.000
29 system user test Slave_worker 6 NULL
/*!100001 START 19 alter table t3 add column c int, force,
algorithm=inplace */ 0.000
30 system user test Slave_worker 6 NULL
/*!100001 START 17 alter table t1 add column c int, force,
algorithm=inplace */ 0.000
31 system user test Slave_worker 6 NULL
/*!100001 START 18 alter table t2 add column c int, force,
algorithm=inplace */ 0.000
32 system user test Slave_worker 6 NULL
/*!100001 START 20 alter table t4 add column c int, force,
algorithm=inplace */ 0.000
33 system user test Slave_worker 6 NULL
/*!100001 START 21 alter table t5 add column c int, force,
algorithm=inplace */ 0.000
34 root localhost NULL Query 0 Init show
processlist 0.000
>>* what happens if the commit/rollback ddl event never arrives? (the statement on the master could be skipped for one of several reasons)
So here are 2 options
If slave is still running DBA can manually run
COMMIT/ROLLBACK identifier alter_stmt/ALTER , where identifier is same as
START ALTER identifier.
so this will either commit or rollback alter.
If slave is not running.
Since slave is not running all context is lost , so this will take as much as
time a normal alter.
>> - I assume that users on the slave see the old structure all the time until it completes
Right, we work on copy of alter for myisam and for other case innodb will take
care of it.
>> - would this have a storage or performance penalty on the slave if the commit/rollback DDL event never arrives?
Not performance but yes storage and ram penalty.
>> - can a “commit” / “rollback” be manually triggered by the DBA in such circumstances?
yes.
>>* what happens if the server crashes while this process (or these processes) are ongoing when the server starts up again?
So if we crash in START ALTER no issue , we can run it again , since it is
transactional(we are working on copy so ..)
If we crash after start alter and we receive COMMIT alter then it will be
treated as normal alter.
ROLLBACK ALTER will be instant.
If we crash in COMMIT ALTER this will be same as crash in normal ALTER table ,
so DBA has to deal with it.
Architecture:-
Lets look at sample alter perf data for innodb and myisam
Myisam
+ 96.72% 0.00% mysqld mysqld [.] mysql_parse
+ 96.72% 0.00% mysqld mysqld [.] mysql_execute_command
+ 96.72% 0.00% mysqld mysqld [.]
Sql_cmd_alter_table::execute
- 96.72% 0.00% mysqld mysqld [.] mysql_alter_table
- mysql_alter_table
- 96.63% copy_data_between_tables
+ 79.64% handler::ha_write_row
+ 6.24% TABLE::update_default_fields
+ 5.71% READ_RECORD::read_record
+ 1.87% do_copy_null
1.05% Field::do_field_int
+ 0.95% _mcount
InnoDB
+ 41.27% 0.00% mysqld mysqld [.] mysql_parse
+ 41.27% 0.00% mysqld mysqld [.] mysql_execute_command
+ 41.27% 0.00% mysqld mysqld [.]
Sql_cmd_alter_table::execute
- 41.27% 0.00% mysqld mysqld [.] mysql_alter_table
- mysql_alter_table
- 41.27% mysql_inplace_alter_table
- 41.15% handler::ha_inplace_alter_table
ha_innobase::inplace_alter_table
- row_merge_build_indexes
- 41.00% row_merge_read_clustered_index
+ 22.88% row_merge_insert_index_tuples
+ 5.89% row_build_w_add_vcol
+ 5.20% BtrBulk::finish
+ 3.20% row_merge_buf_add
+ 0.87% page_cur_move_to_next
0.62% rec_get_offsets_func
So as we can see most of the work is done by copy_data_between_tables/
mysql_inplace_alter_table.
Master Side:-
There is not much change into master side.
So after locking table and before executing these functions we write
START _id_ alter_stmt into binlog.
And at the time of write_bin_log we write
COMMIT _id_ alter_stmt into binlog.
So alter statement will be divided into 2 , hence 2 gtid. START_ALTER will
have special flag FL_START_ALTER_E1(4), this will be used on slave side to
create new worker thread for start alter processing.
Slave Side:-
This require parallel replication on slave side.
So in do_event when we get a gtid_event with FL_START_ALTER_E1, ::choose_thread
will call rpl_parallel_add_extra_worker, which will create a new worker thread
and this will do start alter processing. So the gtid_log_event and next
Query_log_event will be scheduled to this new thread. And this thread will be
exited as soon as alter is finished.
On slave side START ALTER will binlog after successfully getting MDL lock and
thd lock. Untill this point we are executed as DDL(so new GCO), but after
getting locks we will call finish_event_group. So that new events can proceed in
parallel.
Then it will continue to execute code in mysql_alter_table until
we reach a non transactional part(like renaming table/dropping table in myisam)
So before executing NON-Transactional part it will wait signal from other worker
thread to either abort or proceed forward. We will add a entry into mi->
start_alter_list, with thread_id(of master) as a key.
COMMIT/ROLLBACK Alter is treated as a normal query_log_event so it will be
assigned normal worker. This command will take SQLCOM_COMMIT_PREVIOUS path in
mysql_execute_command. We will simple search for thread_id into start_alter_list
and change status from ::WAITING to ::COMMIT_ALTER and signal the wait condition
if we dont find the thread_id we will wait on wait condition, So
simple it is just
consumer producer between start alter and commit/rollback.
Questions by Kristian:-
>Can you explain what you mean by "true LOCK=NONE"? It is not clear from your
>description. I think you mean something that will allow to run an ALTER
>TABLE on the slave in parallel with the same statement on the master?
Yes
>There will be a number of things to consider with this. Previously, all
>transactions in the binlog have been atomic; this introduces related events
>that can be arbitrarily separated in the binlog.
>For example, suppose the slave stops in the middle between BEGIN_DDL_EVENT
>and COMMIT_DDL_EVENT/ROLLBACK_DDL_EVENT. What is then the slave's binlog
>position / GTID position?
Now we are using 2 gtid for ALTER , So it wont be issue.
>Hopefully, the exiting pool of slave worker threads (currently used for
>parallel replication) can be used for this as well? Doesn't seem right to
>introduce a new kind of thread pool...
We are using same(global) thread pool but I am creating new threads for START
ALTER. Otherwise we will have a deadlock , Suppose if we have 5 concurrent START
ALTER and just 5 slave-worker-threads , all worker will be waiting for
COMMIT/ROLLBACK, but we cant execute commit rollback because we dont have any
free worker. There can be more case when we have concurrent DML too ,
so creating
a new thread was the safest option.
>Won't you need some mechanism to prevent following events to access the
>table before ALTER TABLE in a worker thread can acquire the metadata lock?
Right, So Untill Locking START ALTER is executed as DDL so no following event
will be executed before locking.
And one more thing regarding locks , So there will we only one thread will be
doing whole work of alter, (we can just assume that there is some
sleep on slave)
so execution on master and slave will be equivalent.
>There are some nasty issues to be aware of with potential deadlocks related
>to FLUSH TABLES WITH GLOBAL READ LOCK and such, when multiple threads are
>trying to coordinate waiting and locking; there were already some quite hard
>issues with this for parallel replication.
I have to test for it.
Code branch bb-10.5-olter
Jira (Mdev-11675(https://jira.mariadb.org/browse/MDEV-11675))
Regards
Sachin
--
Regards
Sachin Setiya
Software Engineer at MariaDB
22 Jan
22 Jan
12:45 p.m.
GitHub branch is still in development phase , I will update it as soon as I implement more features , although r_ttt test in rpl suite can be runned. On Wed, Jan 22, 2020, 5:59 PM Sachin Setiya <sachin.setiya@mariadb.com> wrote: > Hi Everyone! > > This mail will describe usage and implementation of Lag Free Alter. And > this > will also answer the question raised by Kristian and Simon J Mudd(in 2016) > > Desc:- This will split Alter into 2 different commits. START ALTER and > COMMIT > /ROLLBACK ALTER , Start Alter will be written in binlog as soon as we get > the > locks for the table, alter will proceeds as usual and at the time of > writing > binlog if alter is successful we will write COMMIT Alter other wise > ROLLBACK > Alter. > > Usage:- Using this feature is quite simple. > 1. On master you have to turn on `BINLOG_SPLIT_ALTER` dynamic variable. > 2. Slave must be using parallel replication. > > Advance Usage:- > So alter is divided like this. > 1. START identifier Actual_alter_stmt > 2. COMMIT/ROLLBACK identifier Actual_alter_stmt. OR > 2. COMMIT/ROLLBACK identifier ALTER > > identifier is thread_id. > > Questions by Simon Mudd. > >>* this behaviour should be configurable? > Yes. > >> - new global variable on the master to allow injection of this > changed event stream? > Right , `BINLOG_SPLIT_ALTER` > >> - a new session variable set in the session where the command is > triggered ? > Right , `BINLOG_SPLIT_ALTER` > >> - on slave a setting to indicate how many INPLACE ALTER TABLEs can > run at once? > No setting so far , but I am thinking of maximum no of CONCURRENT ALTER = > slave_parallel_threads > >>* how does a DBA monitor what’s going on? > >> - the progress and number of active DDL statements > So as we there is 2 part, So progress will go like this > 1. Executing of start alter (this will take most of time) > 2. Waiting for commit/rollback Signal > 3. Commit/ Rollback Alter. > Number of active ALTER , these will create new threads so DBA can know > using this > or I am thinking of adding variable in SHOW SLAVE INFO. which will > show active DDL. > >> - please consider adding counters/metrics for: > >> * number of “asynchronous DDLs” in progress / completed successfully > / failed or rolled back > Okay, We can have counter for these metrics. If i get time to implement > this. > >> * sizes of the DDL changes made so far > Not sure if we need this. > >> * number of threads busy in this state (maybe can be implied from > SHOW PROCESSLIST or equivalent but nicer to have an easy to query metric) > Show processlist will show the busy threads. > Sample Output when concurrent alter is running on slave. > slave_parallel_threads= 10 > Every 1.0s: mysql -uroot -S > /home/sachin/alter/build/mysql-test/var/tmp/mysqld.2.sock -e show > processlist > sachin-sp52: Wed Jan 22 16:56:00 2020 > > Id User Host db Command Time State Info Progress > 1 system user NULL Daemon NULL InnoDB purge > coordinator NULL 0.000 > 3 system user NULL Daemon NULL InnoDB purge > worker NULL 0.000 > 2 system user NULL Daemon NULL InnoDB purge > worker NULL 0.000 > 4 system user NULL Daemon NULL InnoDB purge > worker NULL 0.000 > 5 system user NULL Daemon NULL InnoDB > shutdown handler NULL 0.000 > 10 root localhost:44478 test Sleep 6 NULL > 0.000 > 11 root localhost:44480 test Sleep 7 NULL > 0.000 > 15 root localhost:44492 test Sleep 6 NULL > 0.000 > 16 root localhost:44494 test Sleep 6 NULL > 0.000 > 17 system user NULL Slave_IO 6 > Waiting for master to send event NULL 0.000 > 19 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 22 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 20 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 23 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 24 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 25 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 26 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 21 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 27 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 28 system user NULL Slave_worker 6 > Waiting for work from SQL thread NULL 0.000 > 18 system user NULL Slave_SQL 6 Slave > has read all relay log; waiting for the slave I/O thread to update it > NULL 0.000 > 29 system user test Slave_worker 6 NULL > /*!100001 START 19 alter table t3 add column c int, force, > algorithm=inplace */ 0.000 > 30 system user test Slave_worker 6 NULL > /*!100001 START 17 alter table t1 add column c int, force, > algorithm=inplace */ 0.000 > 31 system user test Slave_worker 6 NULL > /*!100001 START 18 alter table t2 add column c int, force, > algorithm=inplace */ 0.000 > 32 system user test Slave_worker 6 NULL > /*!100001 START 20 alter table t4 add column c int, force, > algorithm=inplace */ 0.000 > 33 system user test Slave_worker 6 NULL > /*!100001 START 21 alter table t5 add column c int, force, > algorithm=inplace */ 0.000 > 34 root localhost NULL Query 0 Init show > processlist 0.000 > > >>* what happens if the commit/rollback ddl event never arrives? (the > statement on the master could be skipped for one of several reasons) > So here are 2 options > If slave is still running DBA can manually run > COMMIT/ROLLBACK identifier alter_stmt/ALTER , where identifier is same as > START ALTER identifier. > so this will either commit or rollback alter. > If slave is not running. > Since slave is not running all context is lost , so this will take as much > as > time a normal alter. > >> - I assume that users on the slave see the old structure all the time > until it completes > Right, we work on copy of alter for myisam and for other case innodb will > take > care of it. > >> - would this have a storage or performance penalty on the slave if the > commit/rollback DDL event never arrives? > Not performance but yes storage and ram penalty. > >> - can a “commit” / “rollback” be manually triggered by the DBA in such > circumstances? > yes. > >>* what happens if the server crashes while this process (or these > processes) are ongoing when the server starts up again? > So if we crash in START ALTER no issue , we can run it again , since it is > transactional(we are working on copy so ..) > If we crash after start alter and we receive COMMIT alter then it will be > treated as normal alter. > ROLLBACK ALTER will be instant. > If we crash in COMMIT ALTER this will be same as crash in normal ALTER > table , > so DBA has to deal with it. > > Architecture:- > Lets look at sample alter perf data for innodb and myisam > Myisam > + 96.72% 0.00% mysqld mysqld [.] mysql_parse > + 96.72% 0.00% mysqld mysqld [.] > mysql_execute_command > + 96.72% 0.00% mysqld mysqld [.] > Sql_cmd_alter_table::execute > - 96.72% 0.00% mysqld mysqld [.] mysql_alter_table > - mysql_alter_table > - 96.63% copy_data_between_tables > + 79.64% handler::ha_write_row > + 6.24% TABLE::update_default_fields > + 5.71% READ_RECORD::read_record > + 1.87% do_copy_null > 1.05% Field::do_field_int > + 0.95% _mcount > InnoDB > + 41.27% 0.00% mysqld mysqld [.] mysql_parse > + 41.27% 0.00% mysqld mysqld [.] > mysql_execute_command > + 41.27% 0.00% mysqld mysqld [.] > Sql_cmd_alter_table::execute > - 41.27% 0.00% mysqld mysqld [.] mysql_alter_table > - mysql_alter_table > - 41.27% mysql_inplace_alter_table > - 41.15% handler::ha_inplace_alter_table > ha_innobase::inplace_alter_table > - row_merge_build_indexes > - 41.00% row_merge_read_clustered_index > + 22.88% row_merge_insert_index_tuples > + 5.89% row_build_w_add_vcol > + 5.20% BtrBulk::finish > + 3.20% row_merge_buf_add > + 0.87% page_cur_move_to_next > 0.62% rec_get_offsets_func > So as we can see most of the work is done by copy_data_between_tables/ > mysql_inplace_alter_table. > Master Side:- > There is not much change into master side. > So after locking table and before executing these functions we write > START _id_ alter_stmt into binlog. > And at the time of write_bin_log we write > COMMIT _id_ alter_stmt into binlog. > So alter statement will be divided into 2 , hence 2 gtid. START_ALTER will > have special flag FL_START_ALTER_E1(4), this will be used on slave side to > create new worker thread for start alter processing. > > Slave Side:- > This require parallel replication on slave side. > So in do_event when we get a gtid_event with FL_START_ALTER_E1, > ::choose_thread > will call rpl_parallel_add_extra_worker, which will create a new worker > thread > and this will do start alter processing. So the gtid_log_event and next > Query_log_event will be scheduled to this new thread. And this thread will > be > exited as soon as alter is finished. > On slave side START ALTER will binlog after successfully getting MDL lock > and > thd lock. Untill this point we are executed as DDL(so new GCO), but after > getting locks we will call finish_event_group. So that new events can > proceed in > parallel. > Then it will continue to execute code in mysql_alter_table until > we reach a non transactional part(like renaming table/dropping table in > myisam) > So before executing NON-Transactional part it will wait signal from other > worker > thread to either abort or proceed forward. We will add a entry into mi-> > start_alter_list, with thread_id(of master) as a key. > > COMMIT/ROLLBACK Alter is treated as a normal query_log_event so it will be > assigned normal worker. This command will take SQLCOM_COMMIT_PREVIOUS path > in > mysql_execute_command. We will simple search for thread_id into > start_alter_list > and change status from ::WAITING to ::COMMIT_ALTER and signal the wait > condition > if we dont find the thread_id we will wait on wait condition, So > simple it is just > consumer producer between start alter and commit/rollback. > > Questions by Kristian:- > >Can you explain what you mean by "true LOCK=NONE"? It is not clear from > your > >description. I think you mean something that will allow to run an ALTER > >TABLE on the slave in parallel with the same statement on the master? > Yes > >There will be a number of things to consider with this. Previously, all > >transactions in the binlog have been atomic; this introduces related > events > >that can be arbitrarily separated in the binlog. > >For example, suppose the slave stops in the middle between BEGIN_DDL_EVENT > >and COMMIT_DDL_EVENT/ROLLBACK_DDL_EVENT. What is then the slave's binlog > >position / GTID position? > Now we are using 2 gtid for ALTER , So it wont be issue. > >Hopefully, the exiting pool of slave worker threads (currently used for > >parallel replication) can be used for this as well? Doesn't seem right to > >introduce a new kind of thread pool... > We are using same(global) thread pool but I am creating new threads for > START > ALTER. Otherwise we will have a deadlock , Suppose if we have 5 concurrent > START > ALTER and just 5 slave-worker-threads , all worker will be waiting for > COMMIT/ROLLBACK, but we cant execute commit rollback because we dont have > any > free worker. There can be more case when we have concurrent DML too , > so creating > a new thread was the safest option. > >Won't you need some mechanism to prevent following events to access the > >table before ALTER TABLE in a worker thread can acquire the metadata lock? > Right, So Untill Locking START ALTER is executed as DDL so no following > event > will be executed before locking. > And one more thing regarding locks , So there will we only one thread will > be > doing whole work of alter, (we can just assume that there is some > sleep on slave) > so execution on master and slave will be equivalent. > >There are some nasty issues to be aware of with potential deadlocks > related > >to FLUSH TABLES WITH GLOBAL READ LOCK and such, when multiple threads are > >trying to coordinate waiting and locking; there were already some quite > hard > >issues with this for parallel replication. > I have to test for it. > > Code branch bb-10.5-olter > Jira (Mdev-11675(https://jira.mariadb.org/browse/MDEV-11675)) > Regards > Sachin > > > -- > Regards > Sachin Setiya > Software Engineer at MariaDB >
24 Feb
24 Feb
10:21 p.m.
New subject: [Maria-developers] [External] Lag Free ALTER Table for Slave
Hi there Sachin, Good to see you working on this! > On 22 Jan 2020, at 13:29, Sachin Setiya <sachin.setiya@mariadb.com> wrote: … > Usage:- Using this feature is quite simple. > 1. On master you have to turn on `BINLOG_SPLIT_ALTER` dynamic variable. > 2. Slave must be using parallel replication. > > Advance Usage:- > So alter is divided like this. > 1. START identifier Actual_alter_stmt > 2. COMMIT/ROLLBACK identifier Actual_alter_stmt. OR > 2. COMMIT/ROLLBACK identifier ALTER > > identifier is thread_id. thread_id of the active connection? So it’s only possible for a single thread to kick off a _single_ “background/asynchronous ALTER table” ? Might it not be possible for me to kick off several at once? e.g. START 1 ALTER TABLE A add column a varchar(100) NOT NULL DEFAULT ‘A’; START 2 ALTER TABLE B add column b varchar(100) NOT NULL DEFAULT ‘B’; …. START 26 ALTER TABLE Z add column z varchar(100) NOT NULL DEFAULT ‘Z’; I would expect the identifier to be unique (globally). Being numeric is fine. What happens if I repeat the same or an existing identifier? I’d expect to get some sort of error message “background alter table with identifier 6 already running”, but then the next question would be: “What ALTER TABLE is identifier 6?” leading maybe to a request to SHOW ALTER TABLE 6 or similar. That’s unless I’ve misunderstood by what is expected to be provided by “identifier”. I’m guessing it’s just meant to be a unique reference which somehow can be used or verified later? > Questions by Simon Mudd. … >>> * this behaviour should be configurable? > Yes. >>> - new global variable on the master to allow injection of this changed event stream? > Right , `BINLOG_SPLIT_ALTER` >>> - a new session variable set in the session where the command is triggered ? > Right , `BINLOG_SPLIT_ALTER` >>> - on slave a setting to indicate how many INPLACE ALTER TABLEs can run at once? > No setting so far , but I am thinking of maximum no of CONCURRENT ALTER = > slave_parallel_threads >>> * how does a DBA monitor what’s going on? >>> - the progress and number of active DDL statements > So as we there is 2 part, So progress will go like this > 1. Executing of start alter (this will take most of time) > 2. Waiting for commit/rollback Signal > 3. Commit/ Rollback Alter. > Number of active ALTER , these will create new threads so DBA can know > using this > or I am thinking of adding variable in SHOW SLAVE INFO. which will > show active DDL. >>> - please consider adding counters/metrics for: >>> * number of “asynchronous DDLs” in progress / completed successfully / failed or rolled back > Okay, We can have counter for these metrics. If i get time to implement this. >>> * sizes of the DDL changes made so far > Not sure if we need this. Actually this is critical. Imagine I kick off an ALTER TABLE of a 1 TB table. That is going to take a long time to run, so I need a reasonably easy way to figure out how far things have progressed. I had anticipated something like a counter of bytes or rows affected / copied so far, or an indication of the position copying data from the source to destination table. Perhaps that’s too complicated and I need to use something like I_S.TABLES and some combination of DATA_LENGTH / INDEX_LENGTH and DATA_FREE for both the original and new versions of the table, but I suspect that during the ALTER TABLE this meta data may not yet exist anywhere. So how do I find out how much work has been done and how much is left? Some of the OSCs I have done in production have taken over 3 months to run so having a progress meter of some sort tends to be really useful. Other than that I’m following your work with a lot of interest. Regards, Simon
27 Feb
27 Feb
6:25 a.m.
New subject: [Maria-developers] [External] Lag Free ALTER Table for Slave
Hi Simon, On Tue, Feb 25, 2020 at 3:51 AM Simon Mudd <simon.mudd@booking.com> wrote: > > Hi there Sachin, > > Good to see you working on this! > > > > On 22 Jan 2020, at 13:29, Sachin Setiya <sachin.setiya@mariadb.com> wrote: > > … > > > Usage:- Using this feature is quite simple. > > 1. On master you have to turn on `BINLOG_SPLIT_ALTER` dynamic variable. > > 2. Slave must be using parallel replication. > > > > Advance Usage:- > > So alter is divided like this. > > 1. START identifier Actual_alter_stmt > > 2. COMMIT/ROLLBACK identifier Actual_alter_stmt. OR > > 2. COMMIT/ROLLBACK identifier ALTER > > > > identifier is thread_id. > > thread_id of the active connection? Yes > > So it’s only possible for a single thread to kick off a _single_ “background/asynchronous ALTER table” ? Right > Might it not be possible for me to kick off several at once? For that you need multiple client. This is same as current , on one client we can have just one alter we issue next alter when previous is finished. > > e.g. > > START 1 ALTER TABLE A add column a varchar(100) NOT NULL DEFAULT ‘A’; > START 2 ALTER TABLE B add column b varchar(100) NOT NULL DEFAULT ‘B’; > …. > START 26 ALTER TABLE Z add column z varchar(100) NOT NULL DEFAULT ‘Z’; > Actually client will not be able to write START ALTER , it will simple write alter table , which will be splitted automatically by server. > I would expect the identifier to be unique (globally). Being numeric is fine We dont need it , we just need it unique at any specific point of time. >. What happens > if I repeat the same or an existing identifier? I’d expect to get some sort of error message “background alter table with identifier 6 already running”, > but then the next question would be: “What ALTER TABLE is identifier 6?” leading maybe to a request to SHOW ALTER TABLE 6 or similar. You wont be able to specify identifier , because you will be writing normal alter table > > That’s unless I’ve misunderstood by what is expected to be provided by “identifier”. I’m guessing it’s just meant to be a unique reference > which somehow can be used or verified later? Yes unique reference (but any point of time, can be reused by later alter). Yes this will be used by matching COMMIT/ROLLBACK ALTER on slave. > > > Questions by Simon Mudd. > > … > > >>> * this behaviour should be configurable? > > Yes. > >>> - new global variable on the master to allow injection of this changed event stream? > > Right , `BINLOG_SPLIT_ALTER` > >>> - a new session variable set in the session where the command is triggered ? > > Right , `BINLOG_SPLIT_ALTER` > >>> - on slave a setting to indicate how many INPLACE ALTER TABLEs can run at once? > > No setting so far , but I am thinking of maximum no of CONCURRENT ALTER = > > slave_parallel_threads > >>> * how does a DBA monitor what’s going on? > >>> - the progress and number of active DDL statements > > So as we there is 2 part, So progress will go like this > > 1. Executing of start alter (this will take most of time) > > 2. Waiting for commit/rollback Signal > > 3. Commit/ Rollback Alter. > > Number of active ALTER , these will create new threads so DBA can know > > using this > > or I am thinking of adding variable in SHOW SLAVE INFO. which will > > show active DDL. > >>> - please consider adding counters/metrics for: > >>> * number of “asynchronous DDLs” in progress / completed successfully / failed or rolled back > > Okay, We can have counter for these metrics. If i get time to implement this. > >>> * sizes of the DDL changes made so far > > Not sure if we need this. > > Actually this is critical. Imagine I kick off an ALTER TABLE of a 1 TB table. > That is going to take a long time to run, so I need a reasonably easy way to figure out how far things have progressed. > I had anticipated something like a counter of bytes or rows affected / copied so far, > or an indication of the position copying data from the source to destination table. > > Perhaps that’s too complicated and I need to use something like I_S.TABLES and some combination of DATA_LENGTH / INDEX_LENGTH and DATA_FREE > for both the original and new versions of the table, but I suspect that during the ALTER TABLE this meta data may not yet exist anywhere. > So how do I find out how much work has been done and how much is left? > > Some of the OSCs I have done in production have taken over 3 months to run so having a progress meter of some sort > tends to be really useful. Simon this might be helpful https://mariadb.com/kb/en/alter-table/#progress-reporting, But I don't have much idea in progress reporting of alter. > > Other than that I’m following your work with a lot of interest. > > Regards, > > Simon > -- Regards Sachin Setiya Software Engineer at MariaDB
8:38 a.m.
New subject: [Maria-developers] [External] Lag Free ALTER Table for Slave
Hi Simon, On Tue, Feb 25, 2020 at 12:21 AM Simon Mudd <simon.mudd@booking.com> wrote:
Actually this is critical. Imagine I kick off an ALTER TABLE of a 1 TB table. That is going to take a long time to run, so I need a reasonably easy way to figure out how far things have progressed. I had anticipated something like a counter of bytes or rows affected / copied so far, or an indication of the position copying data from the source to destination table.
This is also a weak point in the InnoDB storage engine in MariaDB. MySQL 5.7 introduced a progress reporting interface for InnoDB native ALTER TABLE, but we never properly integrated it with MariaDB, even though MariaDB Server 10.2 and later are based on MySQL 5.7. For ALGORITHM=COPY, MariaDB actually introduced progress reporting, way before MySQL had anything comparable: https://mariadb.com/kb/en/progress-reporting/ When https://jira.mariadb.org/browse/MDEV-16329 implements online ALGORITHM=COPY, we should make sure that also the log of concurrent modifications will be accounted for in the progress reporting. This progress reporting should also be exposed somehow in replication in this https://jira.mariadb.org/browse/MDEV-11675 that you are commenting on. How, I do not know, and I would expect that you would have to query the replicas directly, not via a master. If I remember correctly, MySQL 5.7 used PERFORMANCE_SCHEMA for the InnoDB native ALTER TABLE progress reporting. For the InnoDB storage engine in MariaDB, we have two open tasks, which look like they could be duplicating each other: https://jira.mariadb.org/browse/MDEV-9260 Improve progress report on on-line alter table https://jira.mariadb.org/browse/MDEV-12512 Accurately report progress for ALTER TABLE...ALGORITHM=INPLACE
Some of the OSCs I have done in production have taken over 3 months to run so having a progress meter of some sort tends to be really useful.
This strongly suggests that we need test coverage for various 'bad day' scenarios, such as slow or intermittent connection for the replication stream, or the restart of the master or replica (with or without crash). Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
1790
Age (days ago)
1826
Last active (days ago)
4 comments
3 participants
participants (3)
-
Marko Mäkelä -
Sachin Setiya -
Simon Mudd