Instant Add Columns in My SQL Vin Chen

Instant Add Columns in My. SQL Vin Chen Tencent Game

Who I am l Tencent Game DBA Team l Our Team has been serving game business for a few years. The mission of the DBA Team is to provide stable and efficient online storage services for Tencent Games. As the storage requirements from game business evolve, we keep extending features of our database and improving its performance. We spared no effort to provide reliable, scalable and performant database solutions. l Profiles l l Team leader of Tencent DBA Developer Team Senior engineer of Tencent More than 7 years My. SQL development Focus on open source storage solutions(My. SQL, Mongo. DB, Redis, etc. ) l Contributor to Oracle My. SQL l Author of instant add columns

Background l Why we need instant add columns • High Frequency of Adding columns to BIG Table in Production Env • Expensive operation with Adding columns to BIG table • Causing downtime in the Game business l “Instant add columns” in Tencent Game • • First released in July 2012, Ten. DB Based on mysql 5. 5. 24, and 5. 6. 24, 5. 7. 20 Contribution to Oracle My. SQL in 2017 Merged and RELEASED in 8. 0. 12 dated July 2018

Implementation l Row Format l Dictionary l DDL & DML l Redo

Row Format l Record Format Characteristic (Compact/Dynamic) l Storage order is the same as definition order (except primary key) l NULL field not stored. A bitmap is used to indicate which fields are NULL. l Field number of a record is index->n_fields Record Header Primary Key Transaction ID Roll Ptr Other not null Fields Record Ptr Non-NULL Variable-Length Array n_owned+Info Bits (4 Bits + 4 Bits, 1 Byte) Nullable Bitmap Status + Heap No (3 Bits + 13 Bits, 2 Bytes) Record Extra Bytes Next Ptr (2 Bytes)

Row Format l Record Format Changes(Compact/Dynamic) l Field Count: the field count of the record(≤ index. n_fields) l Forward compatibility • Info Bits(higher 4 bits, 2 reserved bits) • REC_INFO_MIN_REC_FLAG 0 x 10 • REC_INFO_DELETED_FLAG 0 x 20 • REC_INFO_INSTANT_FLAG 0 x 80 l Only leaf pages of cluster index are affected Record Header Primary Key Transaction ID Roll Ptr Other not null Fields Record Ptr Non-NULL Variable-Length Array n_owned+Info Bits (4 Bits + 4 Bits, 1 Byte) Nullable Bitmap Field Count Nullable Bitmap Status + Heap No (3 Bits + 13 Bits, 2 Bytes) Record Extra Bytes Next Ptr (2 Bytes)

Row Format l Record Format Changes(Compact/Dynamic) l If REC_INFO_INSTANT_FLAG is set, store the “Field count” using a variable-length encoding of 2 bytes l REC_INFO_INSTANT_FLAG would be set for insert/update after instant adding columns. l If REC_INFO_INSTANT_FLAG is zero, there are two possibilities: • Instant adding columns have never happened in this table. • The record is inserted or updated before the first time of instant adding columns. • n_core_fields: The field count before the first time instant adding columns happened.

Dictionary l New metadata l Store n_core_fields • When the first time instant adding columns happens, we need to store the original field count of a table • Update sys_tables set (mix_len = mix_len | n_core_fields << 16) where table = : table_id; l Store default value l A new system table sys_columns_added l Dictionary loading l When loading a table from dictionary • n_core_fields = sys_table. mix_len >> 16 • cluster_index. n_core_fields = n_core_fields > 0 ? n_core_fields : cluster_index. n_fields. • dict_load_sys_columns_added() after dict_load_sys_columns()

Dictionary l Examples Use test; create table t 1(id int primary key, c 1 int); insert into t 1 values(1, 1); alter table t 1 add column d 1 varchar(20) not null default 'abc‘, algorithm=‘instant’; alter table t 1 alter column d 1 set default 'bcd'; My. SQL 8 Ten. DB

DDL l Key Logic l Create table: no behavior change l Alter table: l Instant adding column • No need to copy data • Insert or change the related metadata l Other: no behavior change l Drop/Truncate table: l Delete the related metadata

DML l Key Logic l insert: • Before the first time Instant adding columns happened, no behavior change • Otherwise, REC_INFO_INSTANT_FLAG would be set. l delete: • No behavior change, just mark the delete flag; l update: • For updating old version record, if the added columns changed or the length of column changed, it will use non-inplace update(delete + insert) • Otherwise, no behavior change.

DML l Key Logic l select:

Redo l a mix of physical and logical operations l n_core_fields need to be stored in dict object of redo log l default values is useless for recovery

Summary l Advantage l l Change metadata only, very fast No need to copy data No need to take twice the space Forward compatibility with My. SQL l Limitations l Only support adding columns in one statement, that is if there are other non. INSTANT operations in the same statement, it can’t be done instantly l Only support adding columns at last, not in the middle of existing columns l Not support COMPRESSED row format, which is seldom used l Not support a table which already has any fulltext index l Not support any table residing in DD tablespace l Not support temporary table(it goes with COPY)

My. SQL Usage In Tencent Games 400+ Games 10, 000+ Machines 20, 000+ Instances 20+ Fail. Over Per Day 17 Table DDL Per week All instances use Ten. DB, which base on Oracle My. SQL 5. 5/5. 6/5. 7 Load Test Functional Test Grayscale deployment Our Plan of Deploying My. SQL 8. 0 Mass production environment deployment

Thank you for your patience! vinchen@tencent. com