Have feelings, have dry goods, wechat search [three prince Ao Bing] pay attention to this different programmer.

This article has been included in GitHub github.com/JavaFamily, there are a line of large factory interview complete test sites, information and my series of articles.

preface

As we all know, MySQL is widely used in the Internet OLTP (Online Transaction Processing) business system. In the Dachang development specification, we often see a clause that “text large field type is not recommended”.

The following explains why it is not recommended to use the text type from the problems caused by the storage structure of the text type, and the suggested method of text transformation.

background

DML is slow because the log table is written

Problem description

Long_query_time =1s (RDS for MySQL 5.7); add slow query alarm (RDS for MySQL 5.7);

Through monitoring, I found that CPU, QPS, TPS and other indicators are not very high. Recently, it happened that the whole site was doing marketing activities on the Double 11, and the number of users increased slightly. Some interfaces that are not slow become slow, which affects normal services. Troubleshooting is required.

Problem analysis

I can see that some INSERT and update statements are slow, and the monitoring of the alarm period shows that the IOPS is very high, reaching about 70MB/s. Because the CloundDBA function of RDS is not available and the audit log function is not available, Troubleshooting is difficult, and only binlog can be analyzed for troubleshooting.

Max_binlog_size =512MB. Check the binlog generation in high IOPS periods.

Mysql > insert into request_log (); mysql > insert into request_log ();

CREATE TABLE request_log (` `id bigint(20) NOT NULL AUTO_INCREMENT,` `log text.` `created_at datetime NOT NULL.` `status tinyint(4) NOT NULL.` `method varchar(10) DEFAULT NULL.` `url varchar(50) DEFAULT NULL.` `update_at datetime DEFAULT NULL.` `running_time tinyint(4) DEFAULT '0'.` `user_id bigint(20) DEFAULT NULL.` `type varchar(50) DEFAULT NULL.` `PRIMARY KEY (id)`
`) ENGINE=InnoDB AUTO_INCREMENT=4229611 DEFAULT CHARSET=utf8` 
Copy the code

Analysis of the binlog:

$ mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000539|egrep "insert into request_log"
Copy the code

The screen was full of unreadable content, and I didn’t finish scrolling for a long time.

It is determined that the log field in the request_log is the cause of frequent binlog_cache flush and excessive binlog switchover, which results in high IOPS and affects other normal DML operations.

Problem solving

After communicating with the developer, I plan to fix this problem in the next version. The request information will not be written into the table, but into the local log file, and will be extracted to ES for query through FileBeat. If you just want to check the log, you can also access log tools such as grayLog, without writing into the database.

At the end of this article, I’m going to talk about a few more MySQLText types.

The Text in the MySQL

The Text type

Text is a large object that can store a large amount of data. It has four types: TINYTEXT, text, MEDIUMTEXT, and LONGTEXT. The value range of different types is different, as shown below

Data Type Storage Required
TINYTEXT L + 1 bytes, where L < 2**8
TEXT L + 2 bytes, where L < 2**16
MEDIUMTEXT L + 3 bytes, where L < 2**24
LONGTEXT L + 4 bytes, where L < 2**32

Where the L table is the number of bytes of actual length stored in type text. The maximum storage length of the TEXT type can be calculated as 2**16-1 = 65535 Bytes.

The InnoDB data page

The Innodb data page consists of the following 7 sections:

content Take up the size instructions
File Header 38Bytes Data header
Page Header 56 Bytes Data header
Infimun and Supermum Records False record
User Records User data
Free Space Free space: Internal linked list structure, records are deleted, will be added to the free_LRU list
Page Dictionary Page data dictionary: A relative location record for storing records, also known as Slot, inside a sparse directory
File Trailer 8Bytes End of file: To check whether a page has been written to disk in its entirety

Description: File Trailer has only one FiL_Page_end_lsn part, occupying 8 bytes. The first 4 bytes represent the checksum value of the page, and the last 4 bytes and FIL_PAGE_LSN in the File Header, whether a page has been Corrupt. Checking is done through the File Trailer section which has some overhead. Users can turn the page integrity checking on or off by using the parameter Innodb_checksums.

The default table storage engine from MySQL 5.6 is InnoDB, which is for ROW storage. Each page(default page size = 16KB) is also specified. Maximum 16K/ 2-200 = 7992 rows can be stored.

InnoDB row format

Innodb supports four row formats:

Row format Compact Storage features Enhanced variable-length column storage Large prefix indexes are supported Support compression Table space types are supported
REDUNDANT No No No No system, file-per-table, general
COMPACT Yes No No No system, file-per-table, general
DYNAMIC Yes Yes Yes No system, file-per-table, general
COMPRESSED Yes Yes Yes Yes file-per-table, general

Since Dynamic is a Compact variant, the structure is the same, now the default is Dynamic format; COMPRESSED mainly compresses table and index data and is generally applicable to archiving and backup classes with low usage. The REDUNDANT and COMPACT row formats are described in this section.

Redundant row format

This format is for compatibility with older versions of MySQL.

Line record format:

Variable-length offset list record_header col1_value col2_value … . text_value
Field length offset list Record header information, 48 bytes Column 1 data Column 2 data … . Text Column pointer data

It has the following characteristics:

  • The first 768 Bytes of a variable-length column are stored in the index record, and the rest are stored in overflow Page. Fixed-length fields exceeding 768 Bytes are stored in the off-page as variable-length fields.
  • Each record in the index page contains a 6 Bytes header that links the record for row locking.
  • The records in the cluster index contain all user-defined columns. There is also a 6-byte transaction ID (DB_TRX_ID) and a 7-byte Roll Pointer column.
  • Each cluster index row also includes a 6-byte row ID field if the created table does not show the specified primary key.
  • Each secondary index record contains all defined primary key index columns.
  • A record contains a pointer to each column of the record. If the total length of a record’s columns is less than 128 bytes, this pointer takes 1 byte, otherwise 2 bytes. This array of Pointers is called a Record directory. The area to which the pointer points is the data portion of the record.
  • Fixed-length character fields such as CHAR(10) are stored in fixed-length format with trailing Spaces.
  • Fixed-length fields with a length greater than or equal to 768 bytes will be encoded as variable-length fields and stored in the off-page.
  • An SQL NULL value stores one or two bytes in the record dirictoty. For variable-length fields null values take up 0 bytes in the data area. For fixed-length fields, the fixed length is still stored in the data portion, and reserving fixed-length space for null values allows columns to update from null values to non-null values without causing index splitting.
  • For vARCHAR types, the Redundant row format also takes up no storage space, whereas NULL values of the CHAR type do.

Variable length types are stored in the form of length + data. The length of different types ranges from 1 to 4 Bytes (L+1 to L+ 4). For a value of TEXT type, L Bytes are required to store the value and 2 Bytes are required to store the value length. The maximum line length of Innodb is set to 65535 Bytes. For Text, only 9 to 12 Bytes are stored. Data is stored separately in overflow Page.

Compact row format

This row format reduces storage space at the expense of the Redundant format, but increases CPU overhead for some operations. If the system workload is limited by cache hit ratio and disk speed, the Compact row format may be faster. If your workload is CPU speed constrained, the Compact row format, which is supported by all file formats, may be slower.

Line record format:

Variable-length field length list NULL flag bit record_header col1_value col2_value … . text_value
Variable-length field length list Record header information – Column 1 data Column 2 data … . Text Column pointer data

The Compact header is a list of non-null variable-length field lengths and is placed in reverse column order, represented by 1 byte if the column length is less than 255 bytes; If the value is greater than 255 bytes, use 2 bytes. The maximum length of the variable length field cannot exceed 2 bytes. This is because the maximum length of the VARCHAR type in the MySQL database is 65535. The second part after the variable length field is the NULL flag bit, indicating whether the data in the row has a NULL value. If yes, it is represented by 1, which should be 1 byte.

When creating a table, use NOT NULL DEFAULT values. If a large number of NULL values are stored in the columns of the table, space is occupied and index column stability is affected.

It has the following characteristics:

  • Each record in the index contains a 5-byte header, which can be preceded by a variable length header. This header is used to link related records together and also for row locking.
  • The variable-length portion of the record header contains a bit vector representing null values. If the number of fields in the index that can be null is N, this bit vector contains the number of bytes rounded up by N/8. Scale If there are 9-16 fields that can be NULL, this bit vector uses two bytes. A NULL column takes no space, only the bits in the bitvector. The variable-length part of the header also contains the length of the variable-length field. Each length takes one or two bytes, depending on the maximum length of the field. If all columns can be null and a fixed length is specified, there is no longer part of the record header.
  • For each variable length field that is not NULL, the record header contains either one or two bytes of field length. The 2-byte record header is used only if the field is stored in an external overflow area or if the maximum field length exceeds 255 bytes and the actual length exceeds 127 bytes. Field corresponding to external storage, the length of two bytes indicates the length of the internal storage portion plus a 20-byte pointer to the external storage portion. The inner part is 768 bytes, so the length value is 768+20, and the 20-byte pointer stores the true length of the field.
  • NULL does not occupy any space in this part, that is, except for the NULL flag bit, the actual storage does not occupy any space.
  • The data portion of the record header followed by a non-empty field.
  • Clustered index records contain all user-specified fields. There is also a 6-byte transaction ID column and a 7-byte rollback segment pointer.
  • If the primary key index is not specified, the clustered index also includes a 6-byte Row ID column.
  • Each secondary index record contains all primary key columns defined for the cluster index key that are not in the secondary index. If any of the primary key columns are variable-length, then the record header of each secondary index has a variable-length section to record their length, even if the secondary index is defined on a fixed-length column.
  • Fixed-length character fields such as CHAR(10) are stored in fixed-length format with trailing Spaces.
  • For variable length character sets such as UFT8MB3 and UTF8MB4, InnoDB tries to store CHAR(N) in N bytes. If the length of the CHAR(N) column value exceeds N bytes, the space following the column is reduced to a minimum. The maximum length of CHAR(N) column values is the maximum number of character encodings x N. For example, if the longest encoding of utF8MB4 character set is 4, the maximum number of bytes in the column is 4*N.

Problems caused by the Text type

An error occurred when the text field was inserted

Create test table

[root@barret] [test]>create table user(id bigint not null primary key auto_increment, 
  -> name varchar(20) not null default ' ' comment 'name', 
  -> age tinyint not null default 0 comment 'age', 
  -> gender char(1) not null default 'M' comment 'gender',
  -> info text not null comment 'User Information',
  -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time'
  -> );
Query OK, 0 rows affected (0.04 sec)
Copy the code

Insert test data

root@barret] [test]>insert into user(name,age,gender,info) values('moon'.34.'M'.repeat('a'.1024*1024*3));
ERROR 1406 (22001): Data too long for column 'info' at row 1
[root@barret] [test]>insert into user(name,age,gender,info) values('sky'.35.'M'.repeat('b'.1024*1024*5));
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated
Copy the code

Error analysis

[root@barret] [test]>select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+| | + 4194304----------------------+
1 row in set (0.00 sec)
Copy the code

Max_allowed_packet controls the maximum size of communication buffer, and an error will be reported when the size of packets sent exceeds this value. As we all know, MySQL includes Server layer and storage engine, which follow the 2PC protocol. Server layer mainly processes user requests: Connection request – >SQL syntax analysis – > Semantic check – > Generate execution plan – > Execution plan – > Fetch Data; The storage engine layer stores data and provides data read and write interfaces.

Max_allowed_packet =4M, when the first insert repeat('a',1024*1024*3), the packet Server executes SQL to send the packet to InnoDB layer, check that the packet size does not exceed the limit of 4M. An error was reported when the Text limit was exceeded. The size of the second INSERT packet exceeds the limit of 4M, and the Server fails to pass the detection.Copy the code

References the description of this parameter in the AWS RDS parameter group

max_allowed_packet: This value by default is small, to catch large (possibly incorrect) packets. Must be increased if using large TEXT columns or long strings. As big as largest BLOB.

Increasing the size of this parameter alleviates error reporting, but does not completely solve the problem.

The RDS instance is locked

Background description

Apush is deployed separately in the high availability version of RDS for MySQL 5.7. The configuration is 4C8G and 150G disk. There are only 4 tables in the database. The RDS instance of the push service is fully locked with — read-only, and the application is reporting a MySQL error.

Problem analysis

Mysql > select * from push_log (select * from push_log (select * from push_log (select * from push_log (select * from push_log));

request text default '' comment 'Request information',
response text default ' ' comment 'Response information'
mysql>show  table status like 'push_log';Copy the code

Avg_row_length is about 150KB, Rows = 78W, and the size of the table is about 780000*150KB/1024/1024 = 111.5G.

Update via primary key is also slow

insert into user(name,age,gender,info) values('thooo'.35.'M'.repeat('c'.65535);
insert into user(name,age,gender,info) values('thooo11'.35.'M'.repeat('d'.65535);
insert into user(name,age,gender,info) select name,age,gender,info from user;
Query OK, 6144 rows affected (5.62 sec)
Records: 6144  Duplicates: 0  Warnings: 0                                        
[root@barret] [test]>select count(*) from user;
+----------+
| count(*) |
+----------+| | + 24576----------+
1 row in set (0.05 sec)
Copy the code

Update and trace.

mysql> set profiling = 1; Query OK, 0 rows affected (0.00 SEC) mysql>update user set info = repeat('f'.65535) where id = 11;
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show profiles;
+----------+------------+--------------------------------------------------------+
| Query_ID | Duration   | Query                                                  |
+----------+------------+--------------------------------------------------------+| | | 0.27874125 1update user set info = repeat('f'.65535) where id = 11 |
+----------+------------+--------------------------------------------------------+
1 row in set.1 warning (0.00 sec)

mysql> show profile cpu,block io for query 1;  
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000124 | 0.000088 |   0.000035 |            0 |             0 |
| checking permissions | 0.000021 | 0.000014 |   0.000006 |            0 |             0 |
| Opening tables       | 0.000038 | 0.000026 |   0.000011 |            0 |             0 |
| init                 | 0.000067 | 0.000049 |   0.000020 |            0 |             0 |
| System lock          | 0.000076 | 0.000054 |   0.000021 |            0 |             0 |
| updating             | 0.244906 | 0.000000 |   0.015382 |            0 |         16392 |
| end                  | 0.000036 | 0.000000 |   0.000034 |            0 |             0 |
| query end            | 0.033040 | 0.000000 |   0.000393 |            0 |           136 |
| closing tables       | 0.000046 | 0.000000 |   0.000043 |            0 |             0 |
| freeing items        | 0.000298 | 0.000000 |   0.000053 |            0 |             0 |
| cleaning up          | 0.000092 | 0.000000 |   0.000092 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
11 rows in set.1 warning (0.00 sec)
Copy the code

As you can see in the updating step, the main time is 16392 IO output times. Updating a concurrent table by id is also slow.

Group_concat also causes query errors

In business development, there are often requirements like this, which need to be designated according to the name of medical insurance unit in each province, usually as follows:

select group_concat(dru_name) from t_drugstore group by province;
Copy the code

The built-in group_concat returns an aggregated string, The Maximum length is determined by group_concat_max_len (Maximum allowed result length in bytes for the GROUP_CONCAT()). The default value is 1024. For example, 1024000.

It is important to note that group_concat returns an error when the size of the result set exceeds the max_allowed_packet limit.

MySQL built-in log table

Slow_log = log_output=TABLE mysql.audit_log = mysql.slow_log = audit = log_output=TABLE mysql.audit_log = audit = log_output=TABLE mysql.audit_log = audit = log_output=TABLE The structure is similar to mysql.general_log.

Take a look at their table structures

CREATE TABLE `general_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL.`thread_id` bigint(21) unsigned NOT NULL.`server_id` int(10) unsigned NOT NULL.`command_type` varchar(64) NOT NULL.`argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
Copy the code
CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL.`query_time` time(6) NOT NULL.`lock_time` time(6) NOT NULL.`rows_sent` int(11) NOT NULL.`rows_examined` int(11) NOT NULL.`db` varchar(512) NOT NULL.`last_insert_id` int(11) NOT NULL.`insert_id` int(11) NOT NULL.`server_id` int(10) unsigned NOT NULL.`sql_text` mediumblob NOT NULL.`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
Copy the code

Mysql.general_log records all SQL processed by mysql Server, including backend and user. The argument mediumblob is NOT NULL. In dev environment, to track and troubleshoot problems, general_log can be enabled. In Production environment, general_log cannot be enabled. Audit_log can be enabled, which makes some filters based on general_log. For example, I only need all THE SQL initiated by the business account, which is very useful. Many times, I need to analyze which SQL has high QPS and TPS in a certain period of time.

Slow_log records the execution of all SQL that exceeds long_query_time. Slow queries are not much if you follow the mysql development specification. If log_QUERies_NOT_usING_indexes =ON is enabled, many full table scan SQL will be recorded, and the slow_log table will be large. For RDS, only one day of data will be kept. In the case of frequent inserts into slow_log, performing truncate table slow_log to clear slow_log will cause MDL, affecting MySQL stability.

It is recommended to set log_output=FILE and enable slow_log, audit_log, so slow_log, audit_log will be written to files. These files will be processed by the Go API and data will be written to the distributed column database clickHouse for statistical analysis.

Text Modification Suggestions

Using ES storage

In MySQL, the general log table stores data of request or Response class in the text type, which is used to manually troubleshoot problems when interface invocation fails. The frequent use is very low. It can be considered to write local log file, extract it to ES through FileBeat, and clean it by day index according to data retention policy.

Using Object Storage

Some business scenario tables use TEXT and BLOB types, and some picture information stored, such as commodity pictures, is not updated frequently. Therefore, object storage can be considered, such as ALI Cloud OSS and AWS S3, which can conveniently and efficiently meet such requirements.

conclusion

Since MySQL is a single-process multithreaded model, a SQL statement cannot be executed using multiple CPU cores, which makes MySQL suitable for OLTP (features: A large number of user access, logical read, index scan, return a small amount of data, SIMPLE SQL) business system, at the same time to develop some modeling specifications and development specifications for MySQL, try to avoid the use of Text type, it not only consumes a lot of network and IO bandwidth, while the DML operation on the table will become very slow.

In addition, it is recommended to transfer the SQL of complex statistical analysis class to REAL-TIME data warehouse OLAP, such as clickHouse, ADB of Li Cloud and Redshift of AWS, so as to separate THE SQL of OLTP and OLAP class business and ensure the stability of business system.

Well, that’s all for this episode, I’m Aobing, and the more you know, the more you don’t know, and we’ll see you next time!

I’m Aobing, the more you know, the more you don’t know, thank you for your talent: likes, favorites and comments, we’ll see you next time!

This article is constantly updated. You can search “Santaizi Aobing” on wechat and read it for the first time. Reply [Information] There are the interview materials and resume templates for first-line big factories prepared by me.