MySQL 8 new features: Temptable engine

下载 0

潇潇

发布于

1636

人观看

#信息技术

许多数据库操作都需要使用临时表。MySQL透明地创建这些临时表，但是可以调整和配置此行为以获得最佳性能。
MySQL8为内存中的临时表引入了一个新的引擎，它可以替换以前版本中使用的内存引擎。
在本课程中，我们将提供需要临时表的操作的概述，并展示规则此功能的配置选项。
我们还将展示一些基准来比较不同内部临时表引擎的性能。

展开查看详情

1 .MySQL8 New Features: Temptable Engine Pep Pla Tue Nov 5th 2018 - Percona Live Europe Frankfurt, Germany © The Pythian Group Inc., 2018 2

2 . Pep Pla Born in Vinaròs, a small village near the Mediterranean and currently living in Barcelona. Most of the time I’m busy with my three kids, my partner and our two cats. And in my spare time I’m a DBC at Pythian, surrounded by some of the most brilliant DBAs in the world. © The Pythian Group Inc., 2018 3

3 .ABOUT PYTHIAN Pythian’s 400+ IT professionals help companies adopt and manage disruptive technologies to better compete © The Pythian Group Inc., 2018 4

5 .AGENDA ● Introduction ● Sorting ● Temporary space ● Memory engine ● Temptable engine ● Benchmarks © The Pythian Group Inc., 2017 2018 6

6 .Sorting It can change your life © The Pythian Group Inc., 2018 7

7 .Sorting ● Definition of “sort” ● to put a number of things in an order or to separate them into groups ● It is a natural activity ● Kids are taught how to sort things: colors, sizes, numbers… ● As adults we classify everything and everybody. ● The results of “sorting” can change your life. © The Pythian Group Inc., 2018 8

8 .The Sorting Hat © The Pythian Group Inc., 2018 9

9 .The Sorting Hat © The Pythian Group Inc., 2018 10

10 .Sorting ● The Sorting Hat method is quite simple. ● What if we want to perform more complex classifications? ● Place all the students born on the same month together. ● Some methods: ● Sorting Hat and then order by age. ● Order by age and then Sorting Hat. ● Sorting Hat and order by age at the same time. ● Problem: We need a space to keep all the students during the process. © The Pythian Group Inc., 2018 12

11 .Sorting ● MySQL Sorting Hat is called Indexes. ● What if we want to perform more complex classifications? ● MySQL has algorithms to perform complex classifications. ● Problem: We need a space to keep all the data during the process. © The Pythian Group Inc., 2018 13

12 .Temporary Space Time flies © The Pythian Group Inc., 2018 14

13 .Temporary Space © The Pythian Group Inc., 2018 15

14 .Temporary Space ● MySQL stores information on tables. ● MySQL has two different types of “temporary tables”. ● Explicit temporary tables ● Implicit temporary tables © The Pythian Group Inc., 2018 16

15 .Temporary Space ● MySQL explicit temporary tables. ● Create temporary table statement. ● Session based ■ Only visible from the session that creates the table ■ Dropped when the session is closed. ● Create temporary table privilege. © The Pythian Group Inc., 2018 17

16 .Temporary Space ● MySQL implicit temporary tables. ● Created internally without user intervention. ● Statement based ■ Usually only available during the execution of the statement that required the table ● Invisible for the user, no privileges required. © The Pythian Group Inc., 2018 18

17 .Temporary Space ● Some operations that create an internal temporary table: ● Evaluation of some UNION statements, of some views, of derived tables, of common table expressions. ● Subquery or semi-join materialization ● Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue. ● Evaluation of DISTINCT combined with ORDER BY may require a temporary table. ● INSERT ... SELECT statements that select from and insert into the same table, . ● …. © The Pythian Group Inc., 2018 19

18 .Temporary Space ● Some misconceptions ● Table rebuilds do not create a “temporary” table. ● Memory engine tables. ● Performance requirements ● Use the fastest possible storage: memory ● Overflow to disk if required. © The Pythian Group Inc., 2018 20

20 .Memory engine ● Default engine for internal temporary tables before MySQL8 ● Fixed row format ● Bad for “oversized” columns ● Configured using per session parameters. ● Tmp_table_size or (max_heap_table_size if lower) ● Data migrated to disk table in case of an overflow. ● No additional features required: partitioning, privileges or MVCC © The Pythian Group Inc., 2018 22

21 .Memory engine ● Status information: ● Created_tmp_tables ● Created_tmp_disk_tables ● Sort_merge_passes ● Sort_range ● Sort_rows ● Sort_scan ● Performance schema ● memory/memory/HP_SHARE ● memory/memory/HP_INFO ● memory/memory/HP_PTRS ● memory/memory/HP_KEYDEF © The Pythian Group Inc., 2018 23

23 .Temptable engine ● Default engine for internal temporary in MySQL8 ● Not “really” an engine. ● Only for internal temporary tables. ● It is not shown with show engines. ● Variable width row format ● Great for “oversized” columns ● Configured using global parameters. ● temptable_max_ram ● Data is not migrated to disk table in case of an overflow. (Documentation) ● No additional features required: partitioning, privileges or MVCC © The Pythian Group Inc., 2018 25

24 .Temptable engine ● Status information: ● Created_tmp_tables ● Sort_merge_passes ● Sort_range ● Sort_rows ● Sort_scan ● Performance schema ● memory/temptable/physical_disk ● memory/temptable/physical_ram © The Pythian Group Inc., 2018 26

26 .Temptable vs. Memory ● Memory utilization ● Temptable memory usage is global. ● Memory memory usage is controlled per session. ● Both are allocated and released on demand. ● Memory can bring your system to its knees. ● Efficiency ● Temptable uses variable row format. ● Memory uses a fixed row format. ● Temptable usually needs far less memory. ● No data migration ● Temptable does not migrate data. ● Memory converts the ALL the table to a disk engine if required. © The Pythian Group Inc., 2018 28

28 .Benchmarks ● Not a real production environment ● One table with 20.000.000 rows. ● Only one session. No concurrency. ● Three tests ● One large temporary table without sorting (cursor). ● One large temporary table with sorting. ● 500 small temporary tables without sorting. ● Four memory configurations ● 5Gb, 1Gb, 512Mb and 256Mb. ● The same values where used for both engines. © The Pythian Group Inc., 2018 30

29 .Benchmarks ● One large temporary table without sorting (cursor). ● Temptable is 13% faster. ● Temptable needs 1% Write operations.(!) ● One large temporary table with sorting. ● Temptable is 15% faster. ● Temptable again needs 1% Write operations.(!) ● Multiple small operations without sorting ● Temptable is 42% faster. ● Temptable again needs 1% Write operations © The Pythian Group Inc., 2018 31

0点赞

0收藏

0下载