对于变长的字符集,比如uft8mb3和utf8mb4, InnoDB试图用N字节来存储 CHAR(N)。如果CHAR(N)列的值的长度超过N字节,列后面的空格减少到最小值。CHAR(N)列值的最大长度是最大字符编码数 x N。比如utf8mb4字符集的最长编码为4,则列的最长字节数是 4*N。
Text类型引发的问题
插入text字段导致报错
创建测试表
[root@barret] [test]>create table user(id bigint not null primary key auto_increment, -> name varchar(20) not null default '' comment '姓名', -> age tinyint not null default 0 comment 'age', -> gender char(1) not null default 'M' comment '性别', -> info text not null comment '用户信息', -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' -> ); Query OK, 0 rows affected (0.04 sec)
插入测试数据
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
错误分析
[root@barret] [test]>select @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ | 4194304 | +----------------------+ 1 row in set (0.00 sec)
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.
增加该参数的大小可以缓解报错,但是不能彻底的解决问题。
RDS实例被锁定
背景描述
公司每个月都会做一些营销活动,有个服务apush活动推送,单独部署在高可用版的RDS for MySQL 5.7,配置是4C8G 150G磁盘,数据库里也就4张表,晚上22:00下班走的时候,rds实例数据使用了50G空间,第二天早晨9:30在地铁上收到钉钉告警短信,提示push服务rds实例由于disk is full被locked with —read-only,开发也反馈,应用日志报了一堆MySQL error。
select group_concat(dru_name) from t_drugstore group by province;
其中内置group_concat返回一个聚合的string,最大长度由参数group_concat_max_len(Maximum allowed result length in bytes for the GROUP_CONCAT())决定,默认是1024,一般都太短了,开发要求改长一点,例如1024000。
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'
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'
mysql.general_log记录的是经过MySQL Server处理的所有的SQL,包括后端和用户的,insert比较频繁,同时argument mediumblob NOT NULL,对MySQL Server性能有影响的,一般我们在dev环境为了跟踪排查问题,可以开启general_log,Production环境禁止开启general_log,可以开启audit_log,它是在general_log的基础上做了一些filter,比如我只需要业务账号发起的所有的SQL,这个很有用的,很多时候需要分析某一段时间内哪个SQL的QPS,TPS比较高。
mysql.slow_log记录的是执行超过long_query_time的所有SQL,如果遵循MySQL开发规范,slow query不会太多,但是开启了log_queries_not_using_indexes=ON就会有好多full table scan的SQL被记录,这时slow_log表会很大,对于RDS来说,一般只保留一天的数据,在频繁insert into slow_log的时候,做truncate table slow_log去清理slow_log会导致MDL,影响MySQL稳定性。