技术控

    今日:123| 主题:49331
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] MySQL 8.0 General Tablespaces: File per Database (and no FRM files)

[复制链接]
▼素顔美死人 发表于 2016-10-4 06:23:10
169 2

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

MySQL 8.0 General Tablespaces: File per Database (and no FRM files)-1 (general,files,database,files,general,general,manager)
In this blog post, we’ll look at MySQL 8.0 general tablespaces.
    Introduction
  MySQL 8.0 (the DMR version is available now) has two great features (among others):
  
       
  • The new data dictionary completely removed *.frm files, which is great   
  • The ability to create a tablespace and assign a group of tables to it (originally introduced in 5.7).  
  With those two options, we can use MySQL for creating multi-tenant environments with a “schema per customer” approach.
    Schema per Customer with MySQL 8.0
   Using schema per customer with older MySQL versions presents issues  … namely the number of files. (I’ve described schema per customer approach in MySQL in an older blog post.) Let’s say you are hosting a Drupal-based site for your customers, and you create a new database (AKA “schema”) per each customer. You do not want to create one schema for all because each customer wants to extend Drupal and use plugins that will create their own unique set of tables. With tablespace per table and an FRM file, 10K customers will end up with:
  
       
  • 65 tables per schema,   
  • Two files per table, and   
  • 10K schemas  
   . . . or a grand total of 1.3 million files!
   With MySQL 8.0, we can create a tablespace file per each schema and place those tablespace files in a specific set of directories. For example, if we have demo, test and production accounts, we can create a set of directories (outside of the MySQL datadir) and place tablespaces inside them. With no FRM files, we will only have 10 thousands of files, evenly split across multiple locations.
  Example:
  [code]mysql>  create database if not exists drupal_customer_name;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLESPACE drupal_customer_name
ADD DATAFILE '/var/lib/mysql_datafiles/drupal_demo/drupal_customer_name.ibd'
Engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql> use drupal_customer_name;
Database changed
mysql> create table t(i int) ENGINE=InnoDB
TABLESPACE drupal_customer_name;
Query OK, 0 rows affected (0.13 sec)
mysql> create table t1(i int) ENGINE=InnoDB
TABLESPACE drupal_customer_name;
Query OK, 0 rows affected (0.00 sec)
[/code]  Now let’s look at the directory:
  [code]ls -lah /var/lib/mysql_datafiles/drupal_demo/
-rw-r----- 1 mysqlmysql 144K Sep 26 00:58 drupal_customer_name.ibd
[/code]  The downside of this approach is that the “create tables” command should have the tablespace name in it. I’ve created a sample “deploy” script to create a new schema for a customer:
  [code]customer_name="my_drupal_customer"
mysql -f -vvv -e "createdatabaseif not exists $customer_name;
CREATETABLESPACE $customer_name ADDDATAFILE '/var/lib/mysql_datafiles/drupal_demo/${customer_name}.ibd'
engine=InnoDB;"
cat drupal.sql | sed -e "s/ENGINE=InnoDB/ENGINE=InnoDB TABLESPACE $customer_name/g"|mysql $customer_name
[/code]  Size and Timing
  In the next post, I plan to benchmark the performance of millions of tables with MySQL 8.0 and tablespace file per database. Here, I’ve compared the create table performance between MySQL 5.7 (with FRM and file per table), MySQL 8.0 with a file per table (FRMs are gone) and MySQL 8.0 with a file per database. Time to create 1000 databases for Drupal (no data), 65 tables in each database:
  
       
  • MySQL 5.7, file per table:  3m21.819s   
  • MySQL 8.0,   file per table: 2m54.358s   
  • MySQL 8.0,   file per database: 1m55.133s  
  What about the size on disk? It did not change much. Actually, the size on disk for the blank tables (no data) is more in MySQL 8.0:
  
       
  • 8.0:  10M (10485760 bytes) per 65 blank tables (Drupal)   
  • 5.7 : 9.2M (9280821 bytes) per 65 blank tables, including FRM files  
  With 10K schemas, it is 100G just to store tablespaces (schema overhead). At the same time, it is not 100% overhead: InnoDB creates 112K+ the tablespace file right away (it depends upon the table structure). When the data is loaded it will use this reserved space.
  Tablespace supports compression as well:  CREATE TABLESPACE … ADD DATAFILE ‘…’ FILE_BLOCK_SIZE = 8192 Engine=InnoDB; CREATE TABLE … ENGINE=InnoDB TABLESPACE … ROW_FORMAT=COMPRESSED;
    New Data Dictionary
  MySQL 8.0 uses a new transactional data dictionary.
   MySQL Server 8.0 now incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional system tables.
   That also means that all old metadata files are gone : no .frm, .par, .trn, .trg files. In addition tables inside a MySQL database are not using MyISAM tables anymore. The new installation has no single MyISAM table, although the MyISAM engine is supported:
  [code]Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2299
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 where engine = 'MyISAM';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*), engine from information_schema.tables where table_schema = 'mysql' group by engine;
+----------+--------+
| count(*) | ENGINE |
+----------+--------+
|        2 | CSV    |
|      30 | InnoDB |
+----------+--------+
2 rows in set (0.00 sec)
[/code]    Conclusion
  FRM free installation looks great (performance testing of it is my next step). I would also love to see some additional features in MySQL 8.0:
  
       
  • Easier tablespace level manipulations, i.e. “optimize tablespace” to re-claim space in the general tablespace file; add “if exists / if not exists” to create/drop tablespace   
  • Much smaller “reserved” space: if the data dictionary is stored elsewhere, we can create a one-page file (16K) + table structure.  
    References
  
       
  • MySQL 8.0 Manual   
  • Create general tablespace syntax in MySQL 8.0   
  • New data dictionary in MySQL 8.0 and data dictionary limitations  
  Please note: MySQL 8.0 is not production ready and only available for preview.
友荐云推荐




上一篇:I create iOS apps - is RxSwift for me?
下一篇:How-to: Do Scalable Graph Analytics with Apache Spark
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

含曦666 发表于 2016-10-4 08:18:49
学习▼素顔美死人,好好回帖!
回复 支持 反对

使用道具 举报

小珍 发表于 2016-10-4 11:06:53
上次给楼主开的药,你都吃完了?
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表