 Se on Marko, se on Oryus. Okei, minä olen Marko Mäkelä. Minulla olen tullut innoDB, sillä olen tullut innobase-kompani 2003. Ja en ole tullut linoksia vuosina 1993, joten minulla on vähän ympäristöä ja ympäristöä. Ja sellaista 2016 vuosina, minulla olen tullut innobase-kompani ja sen jälkeen olen tullut innobase-kompani ja sen jälkeen jälkeen Mäkelä-kompani. Ja tänään olen tullut tullut innobase-kompani Mäkelä-kompani ja sen jälkeen Mäkelä-kompani. Nyt nähdään, se on se, miten tullut linoksia. Tällä hetkellä, oli vain tämä generiikkinen forma altta table, joka pitäisi tullut uudelleen palveluun kokemuksesta ja sen jälkeen alkuun alkuun yksi palvelu alkuun alkuun alkuun ja sen jälkeen alkuun alkuun alkuun alkuun alkuun alkuun alkuun alkuun alkuun kulku visteen alkuun alkuun alkuun ja sen jälkeen alkuun syy eikä 확un Mäkelä-kompani 23 So it's creating anulok for each row that it's copying, so in case you kill the server and it restarts, the server will roll back the insert into this intermediate table which should not be visible to the user in the first place. So we had a hack, heikki tuuri, the creator of InnoDB asked me to do this stupid hack that let's commit the insert for every 10,000 copied rows so that the rollback will be faster. Then after a while I thought that maybe we should fix that and not write those unnecessary andulok records in the first place. So that's what we did in MySQL 80 before I left the local and we also backpotted that to MariaDB 10.2. And this is copying the data one table row at a time for each index. So there is no sorting taking place. So it will basically insert the data in random order to each index. Not very efficient. And then we got this native alter table. It started with the InnoDB plugin for MySQL 51. That was published when Oracle had acquired InnoBase but MySQL was still an independent company. Later MySQL was bought by Sun and Sun by Oracle but this was very old times like maybe 2004 or 2005 something like that. So in there we had this at secondary indexes unique or non-unique secondary indexes or you could add primary key to a table and that would be done natively inside InnoDB. And this is pre-sorting all the data and it's inserting the data in sorted order in the each index and it's not writing undulok. In MySQL 56 we got more features to that. You can also add or drop columns or you can drop primary key and at the same time add a different primary key. And you can change the column to accept or not accept null values. And all this can be done by rebuilding the table but using this native algorithm to rebuild it. And some operations with this algorithm in place which syntax was introduced in 56 and which is chosen by default when it's possible. Some operations are instantaneous. For example if you rename a column previously it would use the algorithm copy. The copy are huge table just because you wanted to rename a column. But starting with MySQL 56 and MariaDB 10.0 it will just change the column name both in the FNM file and in the InnoDB internal data dictionary. Or if you change the default value of a column in the InnoDB it doesn't actually care about the default value at all. It doesn't store it anywhere. So in that case you don't need to update the FNM file. Some people call this native algorithm in place. They call it online alter even when there is no online operation going on. Like there is no concurrent insert or update or delete by other connections or it's not allowed. Because in some cases we don't allow online alter. For example if you add an auto increment column to a table it's not possible to do that online because you have to assign new values for the auto increment column. And if you are at the same time allowing concurrent updates while this alter is running it's simply not possible to deterministically assign those auto increment values. In MySQL 57 and MariaDB 10.0 we got the bulk index creation. This is something that I didn't develop. I was slightly involved with the design. I suggested that we could skip the redo logging for the pages that we are writing when we are creating the indexes. And I think that in retrospect it turned out to be a mistake because it's a complicating online backup. In MariaDB we have, Maria backup has an option that if it detects that the table was being altered during the backup then it will recopy the data file at the end. That's one way of doing it. But we also introduced in MariaDB this option innerDB log optimized DDL which you can set to off. It's on by default for compatibility but you can disable it so that your backups will work based on the redo log. But the main change there is that it's building the indexes one leaf page at the time instead of inserting one record at the time and then doing page splits and so on. It's first building the leaf pages and then building the upper level pages so it's a little bit more efficient. I would claim that some features in MySQL 5.6 and 5.7 are a bit incomplete or half-baked because they don't support the native alter table to full extent. So if you have multiple full text indexes in your table and you need to rebuild the table it will be refused because there is some artificial limitation that you can only create one full text index at the time. So if you need table rebuild it will go to do algorithm copy. And also in 5.7 some combinations of operations involving virtual columns are not allowed. I would say that it's only due to bad implementation of the virtual column feature in MySQL 5.7. Okay then as a special case of native alter we have online alter table. MariaDB has the syntax alter online table originally in MySQL 5.6 it's called alter table and then you can specify log equals none if you want the operation to fail if it can't be performed online. So two kinds of operations are allowed online. You can add secondary indexes or you can rebuild the table. There is even this keyword force if you want to request the table to be rebuilt even if the rebuild wouldn't be needed otherwise. And this online alter is not implemented for full text or spatial indexes. I would claim that these indexes are very buggy. We have fixed some or several hangs for full text indexes shut down hangs and hangs during server normal operation in MariaDB and there is still no end inside for full text index box. Also for spatial index we have a test that has concurrent rollback and MVCC read and it's occasionally returning wrong results. So I think that the spatial index has some design problems. I wouldn't recommend using them. So those are not supported by online alter. Now next section of my talk is about instant alter. What can you do without spending much time and without changing the existing data format? So in 5.6 and 10.0 you could rename columns and you could change the default value. Not very fancy but still better than nothing. In 5.7 you could extend worker columns in some cases. There is a bit too strict limitation that if you extend the column from less than 2.56 bytes to at least 2.56 then it will not be allowed. Even though we could allow it in some cases but that's how it is in 5.7 and 10.2. In 10.3 we introduced the new syntax so that you can specify that I want this to be instant or I want this to be done without copying the table. And if it can't be done that way then the alter table will fail. So you can request that please if this would cause the table to be rebuilt return an error and don't kill my server for the next few hours by running that huge alter. In 10.3 we also got some simple metadata changes that are done instantly. If you drop a check constraint which we have in Maria Libby or you drop a foreign key constraint that can be done instantly. Or if you enable or disable the attribute that you want system versioning for a particular column that's the topic of the next talk you can do that instantly. But if you enable or disable the system versioning for the entire table that will require a rebuild. In 10.4 you can instantly change the character set from 3 bytes UTF-8 to 4 bytes UTF-8. And you can change the collation if the column is not indexed then the collation doesn't actually affect anything. It's just an attribute of the character set that will affect some queries that you execute against the table. But if it's not indexed then there is no change to any internal data storage so you can change the collation. But if you have an index on that column then of course that index on the column needs to be rebuilt. But the whole table could, you could save the rebuild for the table. It could only rebuild the indexes that use those columns for which the collation is changed. About extending variable length columns. In MySQL 503 I introduced a more compact data storage format which is called row format compact. And the later variations of it are called row format dynamic. It only differs for the treatment of block columns and row format compressed which I did in the 5.1 plugin. So this format is not encoding the length of fixed length columns at all. And for variable length columns if the maximum length is at most 255 bytes then it will use one byte for encoding the length. Or if the actual length is less than, if the actual length fits in seven bytes then it will also be encoded in one byte. The most significant byte would then be zero. Otherwise if the maximum length exceeds 256 then and at the same time this current length exceeds 128 then we will encode it in two bytes. And the first byte will have the most significant bit set. So this means that actually if the maximum length is less than 128 bytes then we can extend to anything without changing the format. Because we know that for every column in the table this length will be encoded in such a way that the most significant bit will be zero. So we can allow that. And also for the original row format of InnerDB where we explicitly store the length of all columns we can allow any extension of varcar columns. For car columns in UTF 8 we are internally in this new format we are using a variable length format. So this applies also to car columns but not for row format redundant. And if you change the character set of a column of course if the length in bytes changes so that this thing or this thing cannot be applied then it will have to use algorithm copy. Because we don't support native rebuild when any data type is changing. We only support it for removing or adding the not null attribute. So what can we do even better to have more instant alter table by changing the file format. First I would like to say a little bit about compatibility because it was mentioned by Peter Taitsev. At MariaDB we don't guarantee that you can downgrade but I at least personally want to keep that in mind. I want to be careful about the data format changes so that the goal is that you can downgrade and if for some reason there is some new feature that doesn't work in the old versions then it should fade in an obvious way. Okay this is not true for the undo lock format change in MariaDB 10.3 but for the data file format it should be. And of course there are other parts of the server which will suffer for downgrading but for exporting or importing data files I think this is important that you can take a data file from a newer version to an older version if needed. So basically this should hold that if you don't use the instant add or drop column in MariaDB 10.3 or later then you should be able to import the file to older server. So in 10.3 we got the instant add column as the last column and with a constant default value. We didn't change any data dictionary table format for this. This is everything is self contained in the IBD file in the MariaDB data file. This doesn't support raw format compressed. This is based on something earlier that was done both by Alibaba and Tencent in their MySQL 5.6 forks. But the default value is something new and also that we are keeping all the metadata inside the data file it's new in MariaDB. MySQL 8.0 got something similar but they are storing the metadata externally in the global data dictionary. So I believe that the import or export of the data files will not work. I didn't test it but I would assume so. So MariaDB is evaluating the default expression at the outer table time and then storing the values in a hidden metadata record. Here is an example. We create the table, insert three rows into it and then we instantly add a few columns. It's adding this hidden metadata record where it's adding the default values of the columns as they were when the outer table was executed. And the user records are not touched at all. So we are basically inserting one record here. And when we are reading data we will see all the values for the old records that are coming from the metadata record. And when we update something like in this case for the last row we update this column T to the null value in that case. Because the last column still corresponds to the same value as in the metadata record. It's not stored in the record, in the table, in the index. But all fields up to the last changed column will be stored. In MariaDB 10.4 we got the instant drop column and changing the ordering of column and also adding a column anywhere in the table. Internally we are still storing the new added columns at the end of the cluster in this record. But we are keeping the metadata record keeps a mapping of table columns to index fields. And this mapping allows us to drop columns. So the columns are kept as a garbage in the old records and we will keep storing dummy values for the dropped columns. And the secondary index record format doesn't change at all. So all this of course means that the performance of DML operations will or can decrease. Basically you use all the table as normally and if you don't want to have instant operation for adding or dropping you use the force clause. There is a block post about that and there is a counter that you can check to see how many table rebuilds you are avoided by these format changes. Well I am already over time trying to go quickly through the rest. So we have some problem with the online table rebuild and create index. And biggest problem is with replication. That replication slave will only start doing its work when the master has already committed. This is a problem with both MySQL and MariaDB. MariaDB 10.5 will hopefully fix this so that the slave will start replicating when the master starts executing the online alter. And for online alter we need to buffer concurrent changes. Currently MariaDB writes this log before committing the concurrent DML operations. This means that if you got a duplicate key error in a DML operation then the alter table would fail also with the duplicate key error. Because we are writing both for doing the operation and for rolling back the operation. If we defer the log writing until commit time then we wouldn't have this problem. And in MariaDB we are working on doing cross engine alter online table. Which means that it's algorithm copy with the logging and it would fix this problem. But I think we still need the engine native for creating secondary indexes or for instant changes. We could speed up bulk operations by doing smarter undo logging. And then I had some section about theoretical limits what you could do. Like if we tagged the records what was the schema definition when this record was written. Then we could lazily convert the records to the newest version when reading the table. And we could also have some validation step even if we have some change that could fail. Like if you are changing an assigned integer to unsigned with the smaller range even. It might be okay if all the rows are fine but we have to validate the table contents. So we could do this by some format tagging. And as a special case of this even without data format changes we could do at check constant or at foreign key constant without rebuilding a table. Just doing a validation step that's not being done currently. So we are currently doing an unnecessary table rebuild. So in summary it might already be 10, 3 and 10, 4. Change the data file format to allow instant add and drop column and changing the order of columns. If you don't want to have that format changed then you have to use the force keyword. And I think it would be useful to have an option that configuration parameter that even when you don't specify this it could use the original format because there is some overhead for all DML operations when you have used instant alter. For example if you have used the instant drop column then you will have the garbage in the table forever. If it was a not null fixed length column you will have to allocate space for those not null fixed length columns for all records even though it's not there. So the table could be smaller if you rebuild the table after drop column. And same thing after add column if the data length is varying a lot you can't do in place updates. So it could be better to rebuild. So maybe a good idea could be on the replication slave have it always rebuild the table and on the master do instant alter. Okay, that was it. Are there any questions or comments? Yes? Yes currently I think it's so that if you are using the same version of on the master and slave then it will be instant alter on both. But like I mentioned in MySQL 10.5 we have this task, where was it? No that one it's yeah this one is the change in 10.5 that it would start doing the alter table on the slave at the same time when the master starts it. So that's currently been worked around by third party tools like GitHub, OSC and Percona Toolkit. Yes Monty what did you? Oh yes yes but my common topic is in Adobe so I didn't consider yes it's the same problem with if you do this cross-engine on an alter table then yeah yeah you are right about it. I think it would be built into the replication so it would just work like that. Well I understood that it's referring to bin lock events and not commands. Okay thank you very much.