How to scale up a MySQL shared tablespace

How to scale up a MySQL shared tablespace

1. What are shared and exclusive tablespaces?

Shared and exclusive tablespaces are used for data storage.

Shared tablespace: All table data of a database is stored in one file. By default, the file path of the shared tablespace is under the data Directory. The default file name is: ibdata1 initialized to 10 M.

Exclusive tablespace: each table is generated and stored in an independent file. Each table has a. frm table description file and A. ibd file. The file contains the data content and index content of a single table. By default, the file is stored in the table.

Advantages and disadvantages

Shared tablespace:

Advantages:

The tablespace can be divided into multiple files and stored on each disk. Put data and files together for convenient management.

Disadvantages:

All the data and indexes are stored in one file to store a very large file. Although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. After a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the daily value system do not use the most shared tablespace.

Independent tablespace: In the configuration file (my. cnf), set innodb_file_per_table

Advantages:

1. Each table has its own independent tablespace.

2. Data and indexes of each table are stored in its own tablespace.

3. A single table can be moved in different databases.

4. space can be recycled

A) The Drop table operation automatically recycles tablespaces. For statistical analysis or daily value tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.

B) using turncate table for innodb-plugin will also shrink the space.

C) For tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.

Disadvantages:

The increase in a single table is too large, for example, more than 100 GB.

2. What are stored in the shared tablespace?

When innodb_file_per_table is enabled, tables are stored in their own tablespace, but the shared tablespace is still storing other InnoDB internal data:

(1) Data Dictionary, that is, the metadata of the InnoDB table

(2) change Buffer

(3) Dual-write buffer

(4) rollback segments

(5) undo Space

(6) foreign key constraint system table

Therefore, when initializing ibdata1, it is better to set a larger value to avoid the dramatic increase of ibdata1 due to high concurrency, which greatly affects the performance.

Iii. Why does the ibdata1 size increase rapidly?

(1) bugs

(2) The transaction clearing speed cannot keep up, mainly disk IO

(3) large transaction undo, even if the kill, the space cannot be recycled.

The improvements are as follows:

(1) Concurrent purge threads are insufficient

(2) disk IO

(3) do not use a 32-bit System

(4) Minimize the execution of large transactions and split large transactions into multiple small transactions

When innodb_file_per_table = 1 is set to enable the independent tablespace, ibdata1 becomes very large. The common reason is that large active transactions have not been completed for a long time or the number of uncleared transactions in the rollback space exists.

You can check the active transaction being executed or the History list length Value in the TRANSACTIONS section of show engine innodb status to confirm the cause.

4. How to resize the shared tablespace
Scenario 1: Scale up ibdata1 for the shared tablespace on the same disk:
Check that the initial ibdata1 size configured in the my. cnf file is 1000 MB, which is automatically increased as follows:
Innodb_data_home_dir =/apps/dbdat/mariadb10_data3306
Innodb_data_file_path = ibdata1: 1000 M: autoextend
Check that the actual ibdata1 file size in the data file directory is 1786773504, as shown below:
-Rw-r -- 1 apps 1786773504 Jul 27 ibdata1
There are two notes for resizing:
1. If the actual size of ibdata1 does not exceed 1000 MB, write 1000 MB in the expanded configuration file;
2. If the actual size of ibdata1 exceeds 1000 MB, write the actual exact size value in the expanded configuration file, as shown in the preceding scenario:
(Product) root @ localhost [(none)]> select 1786773504/1024/1024;
+ ---------------------- +
| 1786773504/1024/1024 |
+ ---------------------- +
| 1, 1704.00000000 |
+ ---------------------- +
1 row in set (0.00 sec)
Change the configuration of my. cnf and add an ibdata2, as shown below:
Innodb_data_file_path = ibdata1: 1704 M; ibdata2: 1000 M: autoextend ------ note the format, semicolon and colon
After you restart mysql, check whether the newly added ibdata2 has taken effect.
[Apps @ mvxl0782 mariadb10_data3306] $ ls-l | grep ibd
-Rw-r -- 1 apps 1786773504 Jul 31 ibdata1
-Rw ---- 1 apps 1048576000 Jul 31 ibdata2

Scenario 2: Expand ibdata1 for the shared tablespace in different disks:
Change the configuration of my. cnf and add an ibdata3 to different disks, as shown in the following figure.
Innodb_data_file_path = ibdata1: 1704 M; ibdata2: 1000 M;/bytes 2/dbdat/ibdata3: 100 M: autoextend
When you restart mysql, the following error is reported:
160731 18:53:29 mysqld_safe mysqld from pid file/apps/dbdat/mariadb10_data3306/mysql. pid ended
160731 18:53:38 mysqld_safe Starting mysqld daemon with databases from/apps/dbdat/mariadb10_data3306
160731 18:53:38 [Note]/apps/svr/mariadb10/bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 15681...
18:53:38 7f81081d9760 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in
Future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
160731 18:53:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
160731 18:53:38 [Note] InnoDB: The InnoDB memory heap is disabled
160731 18:53:38 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
160731 18:53:38 [Note] InnoDB: Memory barrier is not used
160731 18:53:38 [Note] InnoDB: Compressed tables use zlib 1.2.3
160731 18:53:38 [Note] InnoDB: Using Linux native AIO
160731 18:53:38 [Note] InnoDB: Using CPU crc32 instructions
160731 18:53:38 [Note] InnoDB: Initializing buffer pool, size = 21.0G
160731 18:53:39 [Note] InnoDB: Completed initialization of buffer pool
18:53:39 7f81_1d9760 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.
160731 18:53:39 [ERROR] InnoDB: File/apps/dbdat/mariadb10_data3306 // defaults 2/dbdat/ibdata3: 'create' returned OS error 71. Cannot cont
Inue operation
160731 18:53:39 mysqld_safe mysqld from pid file/apps/dbdat/mariadb10_data3306/mysql. pid ende

From the above we can see that mysql is actually identifying the/apps/dbdat/mariadb10_data3306 // Release 2/dbdat/ibdata3 file, because innodb_data_home_dir =/apps/dbdat/mariadb10_data3306 has the directory of the Set, modify the settings as follows:
Innodb_data_home_dir =
Innodb_data_file_path =/apps/dbdat/mariadb10_data3306/ibdata1: 1704 M;/apps/dbdat/extensions/ibdata2: 1000 M;/extensions 2/dbdat/ibdata3: 100 M: autoextend

--------- Note the format, semicolon and colon.

Check that the ibdat3 file in the new disk has been generated as follows:
[Apps @ mvxl0782 mariadb10_data3306] $ cd/Issue 2/dbdat
[Apps @ mvxl0782 dbdat] $ ls-lt
Total 102404
-Rw ---- 1 apps 104857600 Jul 31 ibdata3

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151587.htm