Shulou(Shulou.com)06/01 Report--

Today, we will study the relevant parameters of mysql data and the introduction of shared tablespaces and independent tablespaces under the innodb engine. The parameters mainly include innodb_data_file_path, innodb_data_home_dir, innodb_buffer_pool_size and innodb_buffer_pool_instances. First: first introduce innodb_buffer_pool_size We know that when mysql database operates data pages, it will first pin the data pages into memory, and then do related processing, then the memory size of mysql that can be used by mysql is controlled by the innodb_buffer_pool_size parameter, this parameter is mainly used to cache the innodb table index, data, and buffer when inserting data. The correct configuration in the case of high concurrency and high Imax O is very important and may lead to a great performance improvement, which is the most important setting of InnoDB and has a decisive impact on InnoDB performance. The default setting is only 8m, so InnoDB performance is poor under the default database settings. On a database server with only the InnoDB storage engine, you can set up 60-80% memory. This parameter is not dynamic. To change this value, you need to restart the mysqld service # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. # innodb, unlike myisam, uses buffer pools to cache indexes and # rows of data. The larger you set it, the less disk I / o is required to access the data in the table. # on a dedicated database server, you can set up to 80% of the computer's physical memory size for this # parameter. Do not set it # too large, because competition for physical memory may cause paging in the operating system. Note that on 32-bit systems # may be limited to 2-3.5 g of user-level memory per process, so don't set it too high. Second: innodb_data_home_dir and innodb_data_file_path parameters innodb_data_file_path are used to specify innodb shared tablespace files. If we do not specify innodb_data_home_dir and innodb_data_file_path in the My.cnf file, then ibdata1 will be created as innodb tablespace under the datadir directory by default. Although not specifying innodb_data_home_dir and specifying it as empty display is the same, but the effect is not the same, if not specified then all innodb tablespace files can only be stored in the datadir directory. If the display is specified as empty, you can specify a different path for the innodb tablespace file. Specify multiple innodb tablespace files under the same path 1) specify [mysqld] innodb_data_file_path = ibdata1:1G;ibdata2:12M:autoextend:max:500M note:The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line in the my.cnf file as follows. Note: because innodb_data_home_dir is not specified, both ibdata1 and ibdata2 are created in the datadir (innodb_data_home_dir default is datadir) directory. 2) specify the following in the my.cnf file [mysqld] innodb_data_home_dir = / data/mysql/mysql3306/data innodb_data_file_path = ibdata1:1G Ibdata2:12M:autoextend:max:500M note: you can specify the path to the innodb tablespace file that can be displayed. If you specify innodb_data_home_dir, you cannot specify different paths for different innodb tablespace files. 2. Specify innodb tablespace under different paths specify [mysqld] innodb_data_home_dir = innodb_data_file_path = ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend note under the my.cnf file: if you want to specify files in a different directory for innodb tablespace, you must specify innodb_data_home_dir =. In this example, you will create an ibdata1 under datadir and an ibdata2 under the / data/mysql/mysql3306/data1/ directory. If I do not specify innodb_data_home_dir =, I will only specify the following in the my.cnf file: innodb_data_file_path = / data/data/ibdata1:18m;/data/data/1ibdata2:100m:autoextend:max:2000M bogon:root@~ > service mysqld start Starting MySQL. ERROR! The server quit without updating PID file (/ tmp/mysql.pid). Check the error log. The following bogon:root@/ > tail-n 100 / data/log/error.log 171127 21:08:16 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: File name. / / data/data/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. # # so if you want to specify a different path for the innodb tablespace file, you must specify innodb_data_home_dir in the my.cnf file. Note: 1) when setting the file size, you must pay attention to whether your OS has a maximum file size limit of 2GB! InnoDB will not pay attention to your OS file size limit. In some file systems you may want to set a maximum capacity limit: innodb_data_home_dir = innodb_data_file_path = / ibdata/ibdata1:100M:autoextend:max:2000M 2) if you are modifying the number of files in a shared tablespace, be sure to find the file and check its current actual size when describing the original file. Then write it after which file innodb_data_file_path =. Or you'll make a mistake. Third: the innodb_buffer_pool_instances innodb buffer pool is divided into the number of memory buffer pools. For systems with multiple GB-scoped buffer pools, dividing the buffer pool into multiple buffer pools can improve concurrency and reduce page contention when different threads read and write to the cache. Each page stored or read from the buffer pool is randomly assigned to one of the buffer pool instances, using a hash function. Each buffer pool manages its own free list, refresh list, LRU, and all other data structures connected to the buffer pool and is protected by its own buffer pool mutex (mutex). This option takes effect only if you set innodb _ buffer _ pool _ size to 1gb or higher. The sum of the sizes of all buffer pools is the total cache pool size that you specify. For best efficiency, specify a combination of innodb _ buffer _ pool _ instances and innodb _ buffer _ pool _ size so that each buffer pool instance is at least 1gb. Prior to MySQL 5.6.6, the default is 1, and the default value is 1 in MySQL 5.6.6 and later, depending on the value of innodb_buffer_pool_size in 32-bit systems. Innodb_buffer_pool_instances can open multiple memory buffer pools and hash the data to be buffered into different buffer pools, so that memory can be read and written in parallel. 2 innodb_buffer_pool_instances parameters significantly affect the test results, especially when the load is very high. (3) in the experimental environment, innodb_buffer_pool_instances=8 has a great improvement when the size of buffer_pool is very small, but when using large buffer_pool, innodb_buffer_pool_instances=1 performs best. Finally, talk about the table space under the innodb engine of mysql: first, for the innodb engine, there are two cases: one is the shared table space storage mode, the other is the exclusive table space storage mode. Shared tablespaces: all data in Innodb is stored in a single tablespace, and this tablespace can be composed of many files, and a table can exist across multiple files, so its size limit is no longer a file size limit, but its own limit. As can be seen from the official documentation of Innodb, the maximum tablespace limit of Innodb is 64TB, that is, the single table limit of Innodb is basically around 64TB, of course, this size includes all indexes of this table and other related data. Independent tablespaces: when using exclusive tablespaces to store Innodb tables, the data of each table is stored in a separate file, at this time the single table limit becomes the file system size limit for the myasam engine: the maximum limit for MySQL single table has been extended to 64PB (official documents show). In other words, from the current technical environment, the MyISAM storage engine single table size limit of MySQL database is no longer determined by the MySQL database itself, but by the file system on the OS of the host. 2. Check the tablespace MySQL [(none)] > show variables like 'innodb_data%' of the database

+-+

| | Variable_name | Value |

+-+

| | innodb_data_file_path | / data/data/ibdata1:100m;/data/data1/ibdata2:100m:autoextend:max:2000M |

| | innodb_data_home_dir |

+-+

The tablespace consists of four files: ibdata1 and ibdata2, the size of each file is 100m. When the ibdata1 file is full, the ibdata2 will automatically expand. When the current storage space is full, you can add data files to other disks. The syntax is as follows: pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec [: autoextend [: max:sizespecification]] if you use the autoextend option to describe the last data file, when InnoDB uses up all table free space, it will automatically expand the last data file by 8 MB each time. Example: innodb_data_file files exist for both shared and independent tablespaces, because they not only hold data, but also act as UNDO tablespaces similar to ORACLE. 3. Advantages and disadvantages of shared tablespaces since Innodb has two types: shared tablespaces and independent tablespaces, these two tablespaces must have their own application scenarios sometimes, and it is reasonable to exist. The following is an excerpt from the official introduction of mysql: 3.1 the advantages of shared tablespaces: tablespaces can be divided into multiple files to be stored on each disk, so tables can be divided into multiple files to be stored on disks. The size of the table is not limited by disk size, and it is convenient to expand capacity. Putting it on multiple disks can disperse io and improve performance. 3.2 shortcomings of shared tablespaces all data and indexes are stored in one file, although a large file can be divided into multiple small files, multiple tables and indexes are mixed stored in the tablespace, when the amount of data is very large, there will be a lot of gaps in the tablespace after a large number of table deletions, especially for statistical analysis, applications such as logging systems are the least suitable for shared tablespaces. Shared tablespaces are the least suitable for applications with frequent delete operations. Shared table space cannot be retracted after allocation: when temporary indexing occurs or the operation table space for creating a temporary table expands, there is no way to shrink that part of the space even if the related table is deleted (it can be understood as oracle table space 10G, but only 10m is used, but the table space displayed by the operating system is 10G), the cold backup of the database is very slow. Advantages and disadvantages of independent tablespaces 4.1 advantages of independent tablespaces each table has its own independent tablespace, and the data and indexes of each table will exist in its own tablespace, so that a single table can be moved in different databases. Space can be reclaimed (table emptiness cannot be recycled by itself except for drop table operation) Drop table operation automatically reclaims tablespaces. For statistical analysis or log tables, you can delete a large amount of data by: alter table TableName engine=innodb; retracts unused space. Using turncate table for the Innodb of innodb-plugin also shrinks space. For tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of tablespaces will not seriously affect performance. 4.2 the disadvantage of independent tablespaces is that the single table increases too much, and when the single table takes up too much space, there is not enough storage space. We can only think about solutions from the operating system level, such as soft joins. 5. conversion between shared tablespaces and independent tablespaces 5.1 View the tablespace management type mysql > show variables like "innodb_file_per_table" of the current database; ON stands for independent tablespace management and OFF stands for shared tablespace management (to view the tablespace management of a single table, you need to check whether each table has a separate data file.) 5.2 modify the tablespace management of the database and modify the parameter values of innodb_file_per_table, but the changes cannot affect the shared and independent tablespaces that have been used before Innodb_file_per_table=1 for the use of exclusive tablespaces innodb_file_per_table=0 for the use of shared tablespaces for the generation of irrelevant fragments: MySQL has quite a number of different storage engines to achieve the data storage function in the list. Every time MySQL removes a line from your list, the space is left blank. A large number of deletions over a period of time will make this empty space more than the space used to store the contents of the list. When MySQL scans data, the object it scans is actually the upper limit of the capacity requirement of the list, that is, the part of the area where the data is written at its peak. If a new insert is made, MySQL will try to take advantage of these empty areas, but will still not be able to fully occupy them. For oracle row links and row migration: row links may occur when there is a new insert, row links may occur when rows already exist in update, and row links occur when a block cannot hold a row of records when data is inserted for the first time. In this case, Oracle will use the link to store this row of records using one or more block retained in this segment. Row links are more likely to occur on larger rows, such as fields with data types such as LONG, LONG RAW, LOB, and so on. When a row of records is initially inserted, things can be stored in a block, the length is increased due to the update operation, and the free space of the block is completely full, and the row migration occurs. In this case, Oracle will migrate the entire row of data to a new block (assuming that the entire row of data can be stored in a block), and Oracle will retain the original pointer of the migrated row to the block of the new stored row data, which means that the ROW ID of the migrated row will not change. When a row migration or row chaining occurs, the performance of the operation on this row of data is degraded because Oracle must scan more block to get the information about the row. Summary: the original mysql also has undo tablespaces similar to oracle, that is, innodb shared tablespaces (ibdata1), which expands automatically by default, expanding 8m each time, sharing tablespaces similar to oracle tablespaces, you can put mysql data and indexes on different disks, distribute io, and then improve performance, but unlike oracle, you can dynamically add files to tablespaces. Mysql if you want to add files for shared tablespaces The mysql service needs to be restarted to take effect.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.