RDS空间使用率过高
RDS的磁盘空间包括:数据文件空间、日志文件空间、临时文件空间、系统文件空间。下面将介绍这四块空间的优化。
指的是存放数据的文件,对应到数据库中就是一张张的表,表的组成主要包括:数据和索引两类,数据文件占用实例的空间非常多的时候,需要看一下到底是哪一张表占用了空间。在设计应用的时候,就要考虑未来数据的增长趋势,合理的设计数据的存放位置(存放文件or数据库)、存储格式(数据类型,字段大小)、存放方式(存储引擎选择,分区还是分表)。
数据库采用了大字段varchar(8000)\nvarchar\varbinary\ntext\image\text\blob\clob(sqlserver/mysql),可能导致数据库日志飚升,达到或超过数据文件大小,导致实例被锁定。实际案例中,有的客户1小时增长超过100G。经过改进,将大部分字段调小,该问题消除。大字段的最大存储大小是 2^31-1 个字节 (2 GB),所以SQL Server需要用一种特别的方式来存储和操作它,其成本也就比普通字段高。而如果数据库使用了full模式,响应的日志量也就高很多了。
临时文件产生的因素
一些查询语句(order by, group by)会创建临时表。用explain查看select语句的执行计划,如果extra列显示“using temporary”,即使用了内部临时表。使用临时表一般都意味着性能比较低,在实际应用中应该尽量避免临时表的使用。
tmpdir (临时空间)空间不够,导致程序报错,异常如下
The table /home/*** is full
优化临时空间的常见方法
a. 创建索引:在ORDER BY或者GROUP BY的列上创建索引;
b. 分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。
如果表的设计已经确定,修改比较困难,那么也可以通过优化SQL语句来减少临时表的大小,以提升SQL执行效率。
出现临时空间快速增长时,可以通过show processlist找出正在排序的SQL,kill掉查询;并进行SQL优化,添加必要的索引。
每个数据库在安装的时候会初始化一些系统文件,这些系统文件是数据库正常运行的前提,mysql:ibdata1,ib_logfile0,sqlserver:MSDBLog,master.mdf。
MySQL中有时会出现ibdata1占用空间过大,原因是:长时间没有提交事务,同时数据库中有大量的更新,插入,删除,导致innodb创建大量的undo来维护一致性;mysql 5.1中undo的purge是和master thread 共用一个线程,则可能的purge的速度到达了瓶颈,ibdata1文件中大量的都是undo_log。
建议用户将版本从5.1升级到5.5,5.5中有独立的purge线程可以很快的回收掉undo log;
在5.6中可以单独设置undo tablespace文件,避免与ibdata1混用在一起;
CREATE TABLE `class_meta` (
`class_name` varchar(128) NOT NULL COMMENT ‘类名’,
`class_desc` varchar(2048) default ” COMMENT ‘类的描述’,
`class_status` char(20) default ‘test1′ COMMENT ‘test1,test2′,
PRIMARY KEY (`class_name`),
UNIQUE KEY `cm_cn_uk` (`class_name`),
KEY `cm_cd_ind` (`class_desc`(767)),
KEY `cm_cs_ind` (`class_status`),
KEY `cm_cdcn_ind` (`class_desc`(767),`class_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’meta信息’;
通过上面的表结构能看到如下地方不合适
1、主键与唯一索引明显重复,索引cm_cd_ind与索引cm_cdcn_ind索引重复(这种情况经常出现,大家留意下)
2、cm_cs_ind如果两个状态分布均匀也明显不合适建索引
3、class_desc由于是描述性质的,也不合适建索引
4、最好以自增做为主键,可以减少整表的空间
5、class_status列明显可以用tinyint来存,可以省下19个字节