MySQL Architecture Composition--storage engine

http://hongge.blog.51cto.com/

MySQL Server System Architecture

650) this.width=650; "title=" clip_image002 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M01/86/CD/ Wkiol1fllsxdpmowaadcwqpkiro977.jpg "" 449 "height=" 290 "/>

In the previous section we learned about the logic modules of MySQL, and then we learned about the MySQL storage engine.

First, the MySQL storage engine introduction:

MySQL in versions prior to 5.1 (not included), the storage engine needs to be compiled and installed at the same time as MySQL when it is installed.

But starting with MySQL5.1, MySQL AB has made a major transformation of its architecture and introduced a new concept: the plug-in storage engine architecture. MySQL AB in the framework of the transformation, the storage engine layer and the SQL layer are separate, the coupling is smaller, even can be loaded into the new storage engine online, that is, a new storage engine can be loaded into a running MySQL, without affecting the normal operation of MySQL. The plug-in storage engine

Architecture, which is more flexible and convenient for loading and removing the storage engine, and makes it easier to develop your own storage engine.

MySQL's plug-in storage engine mainly includes Myisam,innodb,ndb cluster,maria,falcon,memory,archive, among them the most well-known and most widely used MyISAM and Innodb two kinds of storage engines. MyISAM is the upgraded version of MySQL's oldest ISAM storage engine and is the default storage engine for MySQL. And InnoDB is not actually mysq, but the third-party software company Innobase (acquired by Oracle in 2005), its biggest feature is the transaction control and other features, so the user is also very extensive.

Some of the other storage engines are relatively small in usage scenarios, and are applied to certain scenarios, such as NDB cluster, although it also supports transactions, but is primarily used for distributed, highly available clustered environments. Maria is the latest version of MySQL's updated storage engine for MyISAM, and Falcon is a database storage engine developed by MySQL company to replace the current InnoDB storage engine with advanced features such as transactions. Memory storage engine All data and indexes are stored in memory, save only. frm files on the hard disk, so it is mainly used for some temporary tables, or very high performance requirements, but allow the loss of data at the time of crash in a specific scenario, will consume a large amount of RAM. Archive is a storage engine with data that is stored in a high proportion of compression, supports only insert,select, does not support update and delete, and is primarily used to store outdated and infrequently accessed historical information and does not support indexing.

MyISAM Introduction to the storage Engine:

1, mysql5.1 before the default storage engine.

2. MyISAM Storage Engine table in the database, each table is stored as three physical files named after the table name. First of all, there must be a. frm file that holds the table structure definition information that is indispensable for any storage engine, plus. MyD and. MYI files, respectively, storing the table's data (. MYD) and index data (. MYI). Each table has and only such three files are stored as a table of the MyISAM storage type, meaning that no matter how many indexes the table has, it is stored in the same one. The MYI file.

3. MyISAM supports the following three types of indexes:

B-tree Index

B-tree index, that is, all the index nodes are stored according to the balance Tree data structure, all the index data nodes are in the leaf node.

R-tree Index

The R-tree index is stored in a different way from the B-tree index, and is primarily designed to index fields of storage space and multidimensional data, so the current MySQL version only supports the geometry type of fields for indexing.

Full-text Index

The Full-text index is a full-text index, and his storage structure is b-tree. The main thing is to solve inefficient problems that we need to use like queries.

MyISAM the above three types of indexes, the most commonly used is the B-tree index, occasionally used to Fulltext, but the R-tree index general system is rarely used. In addition, MyISAM's B-tree index has a large limit, which is that all fields participating in an index cannot exceed 1000 bytes in length.

3. Do not support transactions

4. Only table lock

5, the following conditions will cause damage to the table:

Mysqld is being killed when writing to the table

Mainframe Downtime (Crash)

Disk hardware failure

MyISAM the storage engine bug

6, although each MyISAM table is stored in a. MYD file with the same suffix, the format of each file may not actually be exactly the same, because the MyISAM data storage format is divided into static (fixed) length, Dynamic variable length and compression (compressed) in three formats. Of course, whether compression in three formats is entirely optional, you can specify {compressed by Row_format when creating a table | Default}, which can also be compressed using the Myisampack tool, which is not compressed. In the case of non-compression, it is static or dynamic, which is related to the definition of a field in our table. As long as a field with a variable-length type exists in the table, the table must be in dynamic format, or fixed if there are no variable-length fields, or, of course, you can force a dynamic with the varchar type field through the ALTER TABLE command Table is converted to fixed, but the result is that the original varchar field type is automatically converted to the Char type. Conversely, if you convert a fixed to dynamic, the Char type field is also converted to a varchar type.

Knowledge Point expansion: How to estimate the amount of disk space consumed based on the number of records in a table

First count the number of bytes in a row in a table.

Then, depending on how many rows are added to the table per day in the database, you can figure out how much hard disk space to increase each day, so that you can estimate how much space is planned based on the amount of data.

For example, create a TB1 table in the database test

650) this.width=650; "title=" clip_image003 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M00/86/CD/ Wkiol1fllsbwzligaaccsvx43ys960.png "" 396 "height=" 178 "/>

Perform Mysql>desc test1.tb1 view the table structure of the TB1

650) this.width=650; "title=" clip_image005 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M02/86/CE/ Wkiom1fllsfdqifsaabek4lw6kk937.jpg "" "height=" 154 "/>

10 bytes + 20 bytes + 2 bytes + 20 bytes + 8 bytes + 8 bytes + 100 bytes = 168 bytes

A row of the Tb1 table has 118 bytes

If you add 10,000 records per day, you need to 10000X168/1024/1024=1.6MB

This allows for reasonable planning of disk space based on the number of records added per day.

After an error occurs with one of the table files of the MyISAM storage engine, it affects only the table, without affecting the other tables, and does not affect other databases. If there is a problem with the MyISAM table when our database is running, you can try to verify it through the Check Table command online, and you can try to fix it by repair the table command. In the case of a database shutdown, we can also detect or repair some (or some) of the tables in the database by using the Myisamchk tool. However, it is strongly recommended that you do not need to repair the table easily, before repair, as far as possible to do the backup work, so as not to bring unnecessary consequences.

Innodb Introduction to the storage Engine:

The reason why Innodb is so favored is mainly due to its many features:

1. Support Business

The most important aspect of INNODB in terms of functionality is support for transactions, which is undoubtedly one of the most important reasons to make Innodb one of the most popular storage engines for MySQL.

2, the improvement of locking mechanism

Innodb changed the locking mechanism of MyISAM and implemented the row lock.

3. Implement FOREIGN key

INNODB implements the important features of the foreign key reference database.

4, Innodb storage engine is not the same as MyISAM, although there are. frm files to hold the metadata associated with the table structure definition, but the table data and index data are stored together. It is entirely up to the user to decide whether each table is stored separately or all tables are stored together.

The physical structure of INNODB is divided into two parts:

1. Data files (table data and index data)

Holds data in the data table and all index data, including primary keys and other normal indexes. In InnoDB, there is a concept of tablespace (tablespace), but there is a big difference between the table space and Oracle. First, the Innodb table space is divided into two forms. One is a shared tablespace, in which all tables and index data are stored in the same tablespace (one or more data files), specified by Innodb_data_file_path, and additional data files require an outage restart. Another is the exclusive table space, where the data and indexes of each table are stored in a separate. ibd file.

Although we can set our own tables using shared tablespace or exclusive table space, shared tablespace must exist because InnoDB's undo information and some other metadata information are stored in the shared table space itself. The data file for a shared tablespace is two forms that can be set to a fixed size and can be automatically expanded in size.

When our file table space is about to run out, we have to add data files for it, of course, only shared table space has this action. Shared table space to increase the operation of the data file is relatively simple, only need to follow the Innodb_data_file_path parameter in the standard format to set the file path and related properties can be, but one thing to note here is that innodb when creating a new data file is not created directory, If the specified directory does not exist, an error will be given and cannot be started.

2. log files

Innodb log files are similar to Oracle's redo logs, and you can also set up multiple log groups (minimum 2

), using a round-robin strategy to write sequentially.

Because Innodb is the storage engine for transactions, the system crash (downtime) is not a very serious loss to him, due to the existence of redo logs (that is, the thing log), with the checkpoint mechanism of protection, INNODB can be completely redo The log crash the database to a time when it has not yet been able to write the data to disk, and it can roll back all incomplete transactions that have been written to disk and restore the data.

Innodb not only in terms of functional characteristics and MyISAM storage engine has a large difference, in the configuration is also handled separately. In the mysql boot parameter file (/ETC/MY.CNF) settings, all parameters of the Innodb are basically prefixed with "innodb_", whether it is INNODB data and log related, or some other performance, transaction, etc. related parameters are the same. As with all INNODB-related system variables, all INNODB-related system State values are also prefixed with "innodb_".

The difference between MyISAM and InnoDB

1, MyISAM does not support transactions, and INNODB support. InnoDB autocommit By default is open, that is, each SQL statement will be encapsulated into a transaction by default, automatically commit, which will affect the speed, so it is best to put a number of SQL statements between Begin and commit, compose a transaction to commit.

650) this.width=650; "title=" clip_image006 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M02/86/CD/ Wkiol1fllsft1avdaable9rzwwy738.png "" 427 "height=" 139 "/>

mysql> use test_db;

Mysql> Show tables;

mysql> desc TB1;

Mysql> begin;

mysql> INSERT into TB1 values (' Lisi ', 1);

mysql> INSERT into TB1 values (' Zhangsan ', 2);

Mysql> commit;

2, InnoDB support data row locking, MyISAM does not support row locking, only support locking the entire table. That is, MyISAM read and write locks on the same table are mutually exclusive, MyISAM concurrent read and write if the queue is waiting for both read requests and write requests, the default write requests high priority, even if the read request first arrived, so MyISAM is not suitable for a large number of queries and modifications coexist, so the query process will be blocked for a long time. Because MyISAM is the lock table.

3, INNODB support foreign key, MyISAM not support.

4. InnoDB does not support full-text indexing, and MyISAM supports it.

Second, Mysqlslap performance test MySQL two types of storage engines

Mysqlslap is MySQL's own benchmark tool, the advantages: query data, simple syntax, flexible and easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server. The performance test data is given and the performance comparison of various engines is provided. MSQLSLAP provides an intuitive verification basis before and after MySQL performance optimization, and recommends that system operations and DBA personnel should have some common stress testing tools Can accurately grasp the online database support user traffic limit and its anti-pressure problems.

Now take a look at this stress test tool mysqlslap, about his options manual and--help introduced in detail.
Here are some common options to explain.
--concurrency represents the number of concurrent, and multiple can be separated by commas. Example: concurrency=50,100,200
--engines represents the engine to be tested, can have multiple, separated by delimiters.
--iterations represents how many times to run these tests, that is, how many times to run and get the results.
The--auto-generate-sql represents a SQL script generated by the system itself.
Whether the--auto-generate-sql-load-type represents a read or write or a mixture of the two (read,write,update,mixed)
--number-of-queries represents the total number of queries to run. The number of queries that each client runs can be calculated using the total number of queries/concurrency. such as the penultimate result 2=200/100.
The--debug-info represents the additional output of CPU and memory information (note: Only when MySQL is compiled with--with-debug).
--number-int-cols represents several properties for an integer type in a test table.
--number-char-cols represents the number of char type fields for the test table.
--create-schema represents a pattern that is defined by itself (in MySQL, which is the database in which the library creates the test).
The--query represents its own SQL script.
--only-print If you just want to print and see what the SQL statement is, you can use this option.

--csv=name production of CSV format data files

View MySQL database default maximum number of connections

650) this.width=650; "title=" clip_image008 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M00/86/CD/ Wkiol1fllsjgzzhoaaascuk9zss881.jpg "" 414 "height="/>

You can see that mysql5.7.13 default is 151, note: Different versions of the default maximum number of connections do not differ. General production environment is not enough, in my.cnf [mysqld] under Add max_connections=1024 added to 1024, restart MySQL.

Modify MY.CNF file and restart Mysqld service

650) this.width=650; "title=" clip_image009 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M01/86/CE/ Wkiom1fllsjyphozaaac9dvgux0357.png "414" height= "/>"

View the maximum number of connections that have been modified

650) this.width=650; "title=" clip_image011 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image011 "src=" http://s3.51cto.com/wyfs02/M01/86/CD/ Wkiol1fllsnz-jylaaaq6gqixru209.jpg "" 416 "height="/>

View Mysql the storage engine is used by default and is viewed as follows:

Mysql> show engines;

650) this.width=650; "title=" clip_image013 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image013 "src=" http://s3.51cto.com/wyfs02/M00/86/CD/wKioL1fLlsmQb37_ Aaa9vzkqkd0201.jpg "" 415 "height="/>

Now let's take a look at some examples of specific tests

[Email protected] ~]# mysqlslap--defaults-file=/etc/my.cnf--concurrency=100,200--iterations=1--number-int-cols= --number-char-cols=30--auto-generate-sql--auto-generate-sql-add-autoincrement--auto-generate-sql-load-type= Mixed--engine=myisam,innodb--number-of-queries=2000-uroot-p123456–verbose

Show Results:

650) this.width=650; "title=" clip_image015 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image015 "src=" http://s3.51cto.com/wyfs02/M02/86/CE/ Wkiom1fllsrzgj7xaac9hlxqmpe926.jpg "" 431 "height=" 322 "/>

Test Description: simulation test Two read and write concurrency, the first 100, the second 200, the automatic generation of SQL script, the test table contains 20 init fields, 30 char fields, each execution of 2000 query request. The test engines are myisam,innodb, respectively.

Test Result Description:

MyISAM for the first time 100 client simultaneously initiates the 0.413/S, the second 200 client simultaneously initiates the 0.509/s of the additional search

InnoDB for the first time 100 client simultaneously initiates the 0.692/S, the second 200 client simultaneously initiates the 0.617/s of the additional search

This shows that MyISAM storage engine processing performance is the best, but also the most common, but does not support transactions. The INONDB storage engine provides transactional data engine (ACID), the most used in transactional engines. With transaction rollback, system repair and other characteristics.

The Mysqlslap Test tool produces a CSV format data file and converts it into a chart form:

[Email protected] ~]# mysqlslap--defaults-file=/etc/my.cnf--concurrency=100,200--iterations=1--number-int-cols= --number-char-cols=30--auto-generate-sql--auto-generate-sql-add-autoincrement--auto-generate-sql-load-type= Mixed--engine=myisam,innodb--number-of-queries=2000-uroot-p123456--csv=/root/a.csv

Execution Result:

650) this.width=650; "title=" clip_image017 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image017 "src=" http://s3.51cto.com/wyfs02/M01/86/CD/ Wkiol1fllsvqbczraaamfoiduvy623.jpg "" 444 "height="/>

Copy the a.csv to a Windows host, open and generate a chart

650) this.width=650; "title=" clip_image019 "style=" border-top:0px; border-right:0px; Background-image:none; border-bottom:0px; padding-top:0px; padding-left:0px; border-left:0px; padding-right:0px "border=" 0 "alt=" clip_image019 "src=" http://s3.51cto.com/wyfs02/M00/86/CE/ Wkiom1fllszcyok0aac1u1xfsba562.jpg "" 442 "height=" 303 "/>

http://hongge.blog.51cto.com/

MySQL Architecture Composition--storage engine