关闭

通过mysql的表分区解决数据库挂死

标签: mysqlmysql表分区mysql数据库挂死
861人阅读 评论(0) 收藏 举报
分类:

原文:通过mysql的表分区解决数据库挂死

源代码下载地址:http://www.zuidaima.com/share/1834891018636288.htm

来说下我们这个应用场景吧:我们这个监控系统目前每天存储的历史监控数据大概有几百万条(具体跟监控频率有关系),用户可以自定义历史数据保存时间,假如用户设置的时间为12天,那么12天后也就是第13天,我们就要删掉最早一天的数据,以此类推。最近项目部署后,刚好到第12天,项目就挂掉了,排查原因发现是mysql数据库被一句sql语句给搞死了,看了下程序里面对历史数据删除的代码如下:

public void cleanDataByDayTime( Date currentTime, Integer day ) {
		String dateTime = DateUtil.getDateByFormat( DateUtil.DEFAULT_DATETIME_FORMAT, DateUtil.addDaysForDate( currentTime, -day ) );
		// for (int i = 0; i < TableRouter.FACTOR; i++) {
		String delHql = "delete from MonitorDatum where dEndTime<='" + dateTime + "'";
		this.batchHql( delHql );
		// }
	}

这么大的数据量这样删除,导致IO操作很慢,数据库直接挂掉了。下面说下我们的解决方案,目前采用的是表分区的方式:

1. mysql配置修改
在mysql配置文件my.ini中[mysqld]中添加以下两项:
 

event_scheduler=1
innodb_file_per_table=1

2.初始化分区(建表时建立初始化分区)

-- 示例表结构
 

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (
    alarm_id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dbs_id              BIGINT UNSIGNED NOT NULL DEFAULT 0,  
    sql_id              BIGINT UNSIGNED NOT NULL DEFAULT 0,  
    rule_id             INT UNSIGNED NOT NULL DEFAULT 0,     
    alarm_time          DATETIME NOT NULL,
    PRIMARY KEY (alarm_id, alarm_time),
    INDEX (alarm_time)
) PARTITION BY RANGE (TO_DAYS(alarm_time))
(PARTITION pmax VALUES LESS THAN MAXVALUE);

注:分区参照字段必须与主键字段共同建立联合主键,分区参照字段需要建立索引,本例中的分区参照字段为alarm_time.

 

如果删除分区采用下面方式:pd20140526为分区名称

ALTER TABLE t_test DROP PARTITION pd20140526;

如果查询指定表的所有分区采用下面方式:dbName为数据库名称,tableName为被分区的表的名称

SELECT PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'dbName' AND TABLE_NAME = 'tableName'

3.定时创建分区

-- 创建分区的事件

EVERY 1 DAY:每天创建一个分区

01:00:00:每天凌晨1点创建分区,这个可以根据自己的需求修改。

pd:创建分区的前缀,这个也可以根据需求修改。

DELIMITER $$
DROP EVENT IF EXISTS event_create_partition$$
CREATE EVENT event_create_partition ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE(), '01:00:00') ON COMPLETION PRESERVE ENABLE DO BEGIN
		DECLARE partition_name VARCHAR(32);
		DECLARE partition_desc INT UNSIGNED;
		
		SET partition_name = CONCAT('pd', DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'%Y%m%d'));
		SET partition_desc = TO_DAYS(CURRENT_DATE());
	    SET @partition_alter = CONCAT('ALTER TABLE t_test REORGANIZE PARTITION pmax INTO (
			PARTITION ', partition_name, ' VALUES LESS THAN (', partition_desc, '),
			PARTITION pmax VALUES LESS THAN MAXVALUE)');
		
		PREPARE stmt FROM @partition_alter;
		EXECUTE stmt;
	    DEALLOCATE PREPARE stmt;
	END$$

DELIMITER ;

最后在mysql安装目录的data目录中(我本地的路径是:C:\ProgramData\MySQL\MySQL Server 5.5\data\zq,zq是我的数据库名),具体查看分区后的结构,如下图:

 

采用表分区的方式后我们删除具体某一天的数据就不是直接操作数据库表数据了,我们直接通过ALTER TABLE t_test DROP PARTITION pd20140526;语句删除分区文件了,将表数据的操作上升到对文件的操作了,速度非常快。表分区对查询操作来说也是非常快的。有兴趣的话,大家可以参照上面的在自己本地测试下。

1
0
查看评论

故障案例--mysql5.5分区表的一个坑

故障现象 db每隔一段时间就异常重启,查看DB错误日志的错误日志Database was not shut down normally相关的信息,而查看/var/log/message并没有发现什么异常,没有发生OOM。由于每次异常重启的间隔都比较相近,所以怀疑是业务的某个sql引起的,后来经过业务...
  • cug_jiang126com
  • cug_jiang126com
  • 2016-07-21 17:19
  • 4594

MySQL 分区的使用总结

为什么要分表和分区日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率...
  • LiuHang03
  • LiuHang03
  • 2016-11-09 09:58
  • 783

不建议mysql分区表

缘起:业内进行一些技术交流的时候也更多的是自己分库分表,而不是使用分区表。   解决什么问题? 回答:当mysql单表的数据库过大时,数据库的访问速度会下降,“数据量大”问题的常见解决方案是“水平切分”。   mysql常见的水平切分方式有哪些? 回答:分库分表,分区...
  • qq_19707521
  • qq_19707521
  • 2017-03-01 11:01
  • 4421

【MySQL】MySQL分区表效率测试对比

这一段时间对数据库优化方面的认识深有体会,就以MySQL为例测试一下分区表的性能
  • King_818
  • King_818
  • 2016-05-13 15:13
  • 5555

MySQL分区表(总结)

分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,...
  • xtjsxtj
  • xtjsxtj
  • 2013-11-18 10:25
  • 17029

Mysql表分区的利弊

Mysql大数据中表分区的应用 : http://blog.csdn.net/w_yunlong/article/details/50432448 此文中分别向使用分区和未使用分区的表中插入800万条记录,然后 查询测试,我自己测试结果: 使用分区的查询耗时:0.5s 未使用分区的查询耗...
  • luwei42768
  • luwei42768
  • 2016-11-13 20:50
  • 3185

Mysql分区表使用的一些限制和需要注意的地方

mysql分区策略都基于两个非常重要的假设:查询都能够过滤(prunning)掉很多额外的分区、分区本身并不会带来很多额外的代价。而事实证明,这两个假设在某些场景下会有问题。下面介绍一些可能会遇到的问题。   NULL位会使分区过滤无效   关于分区表一个容易让人误解的地方...
  • longxingzhiwen
  • longxingzhiwen
  • 2016-12-29 19:09
  • 1205

MySQL5.6分区数量太多引发的血案

一个月之前,Scott和同事们发现公司有一个MySQL MHA集群的master(假设master机器名为hostA)每隔一周左右就会挂一次(指MySQL挂掉),在几周内,MHA来回切了好几次。 按照国际惯例,Scott按照如下顺序去查问题到底出在哪里: (1)先翻MySQL error log,...
  • trikking
  • trikking
  • 2016-01-04 10:13
  • 3902

Mysql数据库表分区深入详解

0、mysql数据库分区的由来?1)传统不分区数据库痛点mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看), 一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。[...
  • wojiushiwo987
  • wojiushiwo987
  • 2016-10-21 20:50
  • 4474

MySQL 分区表原理及使用详解

转载: http://www.codeceo.com/article/mysql-partition.html 1. 什么是表分区? 表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。 2...
  • An342647823
  • An342647823
  • 2016-04-11 21:50
  • 3870
    About Me
    扫描关注最代码微信公众号。

    自由、简单、快乐
    学习网站:java源代码学习
    个人资料
    • 访问:4443440次
    • 积分:39450
    • 等级:
    • 排名:第114名
    • 原创:665篇
    • 转载:89篇
    • 译文:1篇
    • 评论:1296条
    友情链接
    博客专栏