maria-developers team mailing list archive

[Simon Mudd <simon.mudd@xxxxxxxxxxx>]

Hi there Sachin,

Good to see you working on this!


> On 22 Jan 2020, at 13:29, Sachin Setiya <sachin.setiya@xxxxxxxxxxx> 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