MYSQL memory engine,性能不升反降

作为DBA,需要经常给各个业务系统做数据库优化。

现在遇到业务系统跟踪到两个慢查询:

(1) 单表16万多,在生产环境并发业务场景经常出现需要7秒多

# Query_time: 7.776702  Lock_time: 0.000091 Rows_sent: 0  Rows_examined: 160155 Thread_id: 28237376 Schema: prod_gw Errno: 0 Killed: 0 Bytes_received: 
SET timestamp=1607308218;
SELECT * FROM FILE_LEVEL        WHERE 1 = 1        AND (SLICE_STATUS = 2 OR  SLICE_STATUS = 3)        AND FILETYPE in (1,6)        AND FIND_IN_SET('2',SYSTEM_TYPE)        AND SENDCOUNT < 3        ORDER BY PRIORITY desc, CREATE_DATETIME asc        LIMIT 0,100;

(2)单表16万多,在生产环境并发业务场景经常出现需要10秒多

# Query_time: 10.499983  Lock_time: 0.000090 Rows_sent: 0  Rows_examined: 192378 Thread_id: 28237338 Schema: prod_gw Errno: 0 Killed: 0 Bytes_received:
# QC_Hit: No  Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  Filesort: Yes  Filesort_on_disk: No
use prod_gw;
SET timestamp=1607308218;
select * from FILE_BLOCK        WHERE 1=1        AND UPLOADSTATUS in (0,3)        AND SEND_COUNT < 3        ORDER BY CREATE_DATETIME asc        LIMIT 0,1000;

业务流程大概是文件分块,传输类的需求。在不考虑修改业务逻辑的情况下,先考虑从数据库的角度进行分析和优化。

两个表表结构如下:

CREATE TABLE `file_block` (
  `REFID` varchar(32) NOT NULL DEFAULT '',
  `STUDYIUID` varchar(128) DEFAULT NULL,
  `FILEID` varchar(128) DEFAULT NULL,
  `FILE_GUID` varchar(64) DEFAULT NULL,
  `BLOCKINDEX` int(16) DEFAULT NULL,
  `UPLOADSTATUS` tinyint(1) DEFAULT NULL,
  `START_BYTE` int(32) DEFAULT NULL,
  `END_BYTE` int(32) DEFAULT NULL,
  `FILE_PATH` varchar(255) DEFAULT NULL,
  `BLOCK_COUNT` int(16) DEFAULT NULL,
  `SEND_COUNT` int(4) DEFAULT NULL,
  `CREATE_DATETIME` varchar(19) DEFAULT NULL,
  `RESEND_STATUS` tinyint(1) DEFAULT '0',
  `FILETYPE` int(4) DEFAULT '0',
  `MODIFY_DATETIME` varchar(19) DEFAULT NULL,
  PRIMARY KEY (`REFID`),
  KEY `FILEID_INDEX` (`FILEID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `file_block` (
  `REFID` varchar(32) NOT NULL DEFAULT '',
  `STUDYIUID` varchar(128) DEFAULT NULL,
  `FILEID` varchar(128) DEFAULT NULL,
  `FILE_GUID` varchar(64) DEFAULT NULL,
  `BLOCKINDEX` int(16) DEFAULT NULL,
  `UPLOADSTATUS` tinyint(1) DEFAULT NULL,
  `START_BYTE` int(32) DEFAULT NULL,
  `END_BYTE` int(32) DEFAULT NULL,
  `FILE_PATH` varchar(255) DEFAULT NULL,
  `BLOCK_COUNT` int(16) DEFAULT NULL,
  `SEND_COUNT` int(4) DEFAULT NULL,
  `CREATE_DATETIME` varchar(19) DEFAULT NULL,
  `RESEND_STATUS` tinyint(1) DEFAULT '0',
  `FILETYPE` int(4) DEFAULT '0',
  `MODIFY_DATETIME` varchar(19) DEFAULT NULL,
  PRIMARY KEY (`REFID`),
  KEY `FILEID_INDEX` (`FILEID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句主要的问题都是利用状态字段或者类似的字段进行过滤,where条件中的字段的值重复度高,离散程度很低,即使建索引也效果不好。

从业务流程上思考,都是临时存储的数据,处理完就会删除,接着处理另一批,数据的重要性程序较低,表中存储的数据行数通常不会超过100万。

网上都说mysql的内存数据库性能较好,是不是可以用mysql的memory 存储引擎,把表存到内存中,加快并发查询速度?

说干就干:

1: 根据上面两个表,分别新建对应的memory存储引擎表

file_level_memory 和 file_block_memory

2: 分别把原表的数据插入到新增的两个表

发现无法直接插入,需要修改 tmp_table_size 和 max_heap_table_size 的大小,改的足够大才行。

SELECT 
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA='prod_gw' AND t.TABLE_NAME='FILE_BLOCK'
ORDER BY data_length DESC, index_length DESC;

查询表占用空间大小,两个表分别占用: file_level内存表需要约880多M内存空间,file_block内存表需要 380多M内存空间

修改tmp_table_size 和 max_heap_table_size都到了1.5G,才完成所有数据的插入。

3:使用jmeter进行并发性能测试

使用上述慢查询sql,在jemter中对原表和内存表分别进行并发查询性能测试

1png.png

2.png

每个表对应的查询语句分别进行三次测试,然而测试结果差强人意:(内存16G,CPU 8核)

tableavg(ms)min(ms)max(ms)
file_level9243431696
 8793211602
 9012941599
file_level_memory14066412214
 14526352302
 14347002296
FILE_BLOCK8632881657
 8352341578
 8343211554
FILE_BLOCK_memory8983191582
 870891498
 8803351518

前面是表名,后面是查询耗时

发现MEMORY引擎的内存表并没有比Innodb引擎的表查询快,相反还要略差?!

理论上不是使用内存表会更快吗?然而......

不仅如此,数据库运行期间,内存表会长期占用内存;会导致mysql占用的总内存明显增加。除非删除内存表,内存才会下降。

总体来看,使用mysql的内存表,需要消耗更多的内存,但性能上并没有网上提到的有明显的提升。

因此,无法使用内存表做优化。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页