Abstract

I created 1 billion + InnoDB tables on MySQL8.0 (note tables not rows) as follows:

$ mysql -A Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1425329 Server version: 8.0.12 MySQL Community Server - GPL Copyright (C) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or 'h' for help. Type 'c' to clear the current input statement. mysql> select count(*) from information_schema.tables;  + -- -- -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- -- -- + | 1011570298 | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (6 hours 57 min 6.31 SEC)Copy the code

Yes, it took 6 hours and 57 minutes to count the numbers!

Who needs to create a billion + tables?

In my previous article, I created and tested creating 4000W tables on MySQL 8.0 (this was a real case). But 1 billion tables is not a real case scenario, because I want to challenge the test of creating 1 billion tables on PG, so I’m going to create the next 1 billion InnoDB tables in MySQL.

Note: I think MySQL8.0 is the first MySQL release with the possibility of creating 1 billion InnoDB tables.

Challenge 1 billion InnoDB tables

Disk space

The first and most important challenge is disk space. InnoDB allocates data pages on disk when creating. Ibd files. Without disk compression, we need at least 25 terabytes of storage capacity. But here’s the good news: our ZFS provides transparent disk compression. Here’s how disk utilization looks:

Actual size:

# du -sh --apparent-size /mysqldata/
26T     /mysqldata/
Copy the code

After the compression:

# du -sh /mysqldata/
2.4T    /mysqldata/
Copy the code

The compression rate:

# zfs get compression,compressratio ... Mysqldata /mysql/data compressratio 7.14x - mysqldata/mysql/data compression Gzip Inherited from mysqldata/mysqlCopy the code

(It looks like the report is not 100% accurate, we achieved 10x + compression)

Lots of little files

Creating a table space file for each table is a big problem. In MySQL 8.0, however, you can create a General Tablespace and “allocate” the table to the Tablespace when creating the table. Here I create a generic table space for each database, and 1000 tables are created on each database.

The result:

mysql> select count(*) from information_schema.schema; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 1011575 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (1.31 SEC)Copy the code

Create a table

Another challenge is how to create tables quickly so that we don’t have to spend months. I used three tricks up my sleeve:

  • Disable all possible consistency checks in MySQL and reduce innoDB page size to 4K (these configuration changes are not suitable for production environments)
  • Create tables concurrently. Since the previous mutex contention issues in MySQL 8.0 have been fixed, creating tables concurrently is fine.
  • Use native NVMe cards on instances of AWS EC2 I3.8 Xlarge

my.cnf config file (I repeat: do not use this in production):

The configuration information for my.cnf is as follows (again: do not use it directly in production) :

[mysqld] default-authentication-plugin = mysql_native_password performance_schema=0 datadir=/mysqldata/mysql/data socket=/mysqldata/mysql/data/mysql.sock log-error = /mysqldata/mysql/log/error.log skip-log-bin=1 innodb_log_group_home_dir = /mysqldata/mysql/log/ innodb_doublewrite = 0 innodb_checksum_algorithm=none innodb_log_checksums=0 innodb_flush_log_at_trx_commit=0 innodb_log_file_size=2G innodb_buffer_pool_size=100G innodb_page_size=4k innodb_flush_method=nosync innodb_io_capacity_max=20000 innodb_io_capacity=5000 innodb_buffer_pool_instances=32 innodb_stats_persistent = 0 tablespace_definition_cache = 524288 schema_definition_cache  = 524288 table_definition_cache = 524288 table_open_cache=524288 table_open_cache_instances=32 open-files-limit=1000000Copy the code

ZFS pool:

# zpool status
 pool: mysqldata
 state: ONLINE
 scan: scrub repaired 0B in 1h49m with 0 errors on Sun Oct 14 02:13:17 2018
config:
 NAME        STATE     READ WRITE CKSUM
 mysqldata   ONLINE       0     0     0
 nvme0n1   ONLINE       0     0     0
 nvme1n1   ONLINE       0     0     0
 nvme2n1   ONLINE       0     0     0
 nvme3n1   ONLINE       0     0     0
errors: No known data errors
Copy the code

A simple script to create tables concurrently (table structure using tables from sysbench) :

#/bin/bash function do_db { db_exist=$(mysql -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'") if [ "$db_exist" == "1" ]; then echo "Already exists: $db"; return 0; fi; tbspace="create database $db; use $db; CREATE TABLESPACE $db ADD DATAFILE '$db.ibd' engine=InnoDB"; #echo "Tablespace $db.ibd created!" tables="" for i in {1.. 1000} do table="CREATE TABLE sbtest$i ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c varchar(120) NOT NULL DEFAULT '', pad varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 tablespace $db;" tables="$tables; $table;" done echo "$tbspace; $tables" | mysql } c=0 echo "starting..." c=$(mysql -A -s -Nbe "select max(cast(SUBSTRING_INDEX(schema_name, '_', -1) as unsigned)) from information_schema.schemata where schema_name like 'sbtest_%'") for m in {1.. 100000} do echo "m=$m" for i in {1.. 30} do let c=$c+1 echo $c db="sbtest_$c" do_db & done wait doneCopy the code

How fast can we create tables? It can be observed by the following state quantity:

# mysqladmin -i 10 -r ex|grep Com_create_table
...
| Com_create_table                                      | 6497                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| Com_create_table                                      | 6449
Copy the code

We create about 650 tables per second, with the number of tables created per 10 seconds above.

Statistical table quantity

It took us more than six hours to view the number of tables by “count(*) from information_schema.tables”. Because:

  • MySQL 8.0 uses a new data dictionary (which is nice to avoid creating 1 billion FRM files). Everything is stored in the following file:

    ls -lah /mysqldata/mysql/data/mysql.ibd

    Rw – r — — — — — 1 Oct 18 15:02 mysql mysql 6.1 T/mysqldata/mysql/data/mysql. Ibd

  • Information_schema. tables is actually a view:

    mysql> show create table information_schema.tablesG *************************** 1. row *************************** View: TABLES Create View: CREATE ALGORITHM=UNDEFINED DEFINER=mysql.infoschema@localhost SQL SECURITY DEFINER VIEW information_schema.TABLES AS select cat.name AS TABLE_CATALOG,sch.name AS TABLE_SCHEMA,tbl.name AS TABLE_NAME,tbl.type AS TABLE_TYPE,if((tbl.type = ‘BASE TABLE’),tbl.engine,NULL) AS ENGINE,if((tbl.type = ‘VIEW’),NULL,10) AS VERSION,tbl.row_format AS ROW_FORMAT,internal_table_rows(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl.hid den <> ‘Visible’),ts.se_private_data,coalesce(stat.table_rows,0),coalesce(cast(stat.cached_time as unsigned),0)) AS TABLE_ROWS,internal_avg_row_length(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl .hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.avg_row_length,0),coalesce(cast(stat.cached_time as unsigned),0)) AS AVG_ROW_LENGTH,internal_data_length(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tb l.hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.data_length,0),coalesce(cast(stat.cached_time as unsigned),0)) AS DATA_LENGTH,internal_max_data_length(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(t bl.hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.max_data_length,0),coalesce(cast(stat.cached_time as unsigned),0)) AS MAX_DATA_LENGTH,internal_index_length(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,( tbl.hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.index_length,0),coalesce(cast(stat.cached_time as unsigned),0)) AS INDEX_LENGTH,internal_data_free(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl.hi dden <> ‘Visible’),ts.se_private_data,coalesce(stat.data_free,0),coalesce(cast(stat.cached_time as unsigned),0)) AS DATA_FREE,internal_auto_increment(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl. hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.auto_increment,0),coalesce(cast(stat.cached_time as unsigned),0),tbl.se_private_data) AS AUTO_INCREMENT,tbl.created AS CREATE_TIME,internal_update_time(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl.h idden <> ‘Visible’),ts.se_private_data,coalesce(cast(stat.update_time as unsigned),0),coalesce(cast(stat.cached_time as unsigned),0)) AS UPDATE_TIME,internal_check_time(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl.hi dden <> ‘Visible’),ts.se_private_data,coalesce(cast(stat.check_time as unsigned),0),coalesce(cast(stat.cached_time as unsigned),0)) AS CHECK_TIME,col.name AS TABLE_COLLATION,internal_checksum(sch.name,tbl.name,if(isnull(tbl.partition_type),tbl.engine,”),tbl.se_private_id,(tbl. hidden <> ‘Visible’),ts.se_private_data,coalesce(stat.checksum,0),coalesce(cast(stat.cached_time as unsigned),0)) AS CHECKSUM,if((tbl.type = ‘VIEW’),NULL,get_dd_create_options(tbl.options,if((ifnull(tbl.partition_expression,’NOT_PART_TBL’) = ‘NOT_PART_TBL’), the AS 0, 1))) CREATE_OPTIONS,internal_get_comment_or_error(sch.name,tbl.name,tbl.type,tbl.options,tbl.comment) AS TABLE_COMMENT from (((((mysql.tables tbl join mysql.schemata sch on((tbl.schema_id = sch.id))) join mysql.catalogs cat on((cat.id = sch.catalog_id))) left join mysql.collations col on((tbl.collation_id = col.id))) left join mysql.tablespaces ts on((tbl.tablespace_id = ts.id))) left join mysql.table_stats stat on(((tbl.name = stat.table_name) and (sch.name = stat.schema_name)))) where (can_access_table(sch.name,tbl.name) and is_visible_dd_object(tbl.hidden)) character_set_client: utf8 collation_connection: utf8_general_ci

And see its execution plan through Explain as follows:

mysql> explain select count(*) from information_schema.tables G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cat partitions: NULL type: index possible_keys: PRIMARY key: name key_len: 194 ref: NULL rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tbl partitions: NULL type: ALL possible_keys: schema_id key: NULL key_len: NULL ref: NULL rows: 1023387060 filtered: 100.00 Extra: Using the where; Using join buffer (Block Nested Loop) *************************** 3. row *************************** id: 1 select_type: SIMPLE table: sch partitions: NULL type: eq_ref possible_keys: PRIMARY,catalog_id key: PRIMARY key_len: 8 ref: Mysql. Tbl. schema_id Rows: 1 filtered: 11.11 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: stat partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 388 ref: Mysql. SCH. Name, mysql. TBL. Name rows: 1 filtered: Extra 100.00: Using index *************************** 5. row *************************** id: 1 select_type: SIMPLE table: ts partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: mysql.tbl.tablespace_id rows: 1 filtered: 100.00 Extra: Using the index of * * * * * * * * * * * * * * * * * * * * * * * * * * * 6. The row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type: SIMPLE table: col partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: Mysql.tbl. collation_id Rows: 1 filtered: 100.00 Extra: Using indexCopy the code

conclusion

Just out of personal interest, I created 1 billion InnoDB tables and indexes on MySQL 8.0 and I succeeded. It took me about 2 weeks.

MySQL 8.0 is the first version of MySQL to support the creation of 1 billion InnoDB tables.

ZFS compression combined with NVMe cards can reduce costs. For example, select i3.4 XLarge or i3.8 Xlarge instances of AWS.

Original text: blog.csdn.net/n88Lpo/arti…