澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站_数据库数据文件,mysql优化

2019-10-21 作者:数据库网络   |   浏览(149)

一个小故事

某天,小王正在和HR妹妹闲聊,正HAPPY时,,突然收到系统告警消息,数据库磁盘被剩余空间500M,OMG,不行,磁盘快满了,要是业务要停了,,那就小王只能删库到跑路了,,,

澳门新萄京官方网站 1

 

先检查下,有没有可以删除的不用的文件,结果都是重要的或者拿不准的。先收缩下数据库吧,点击运行。等收缩完成就可以继续去根HR妹妹聊天了。突然电话座机和手机齐鸣,小王心里一种不祥的预感呢?好像这个场景在哪里见过。。不会是数据库阻塞了吧?? 手忙脚乱的先接起手机,因为来电显示是某业务部门主管 “小王啊,,现在系统卡死了,全部不动了,是怎么回事啊,你赶紧处理下”,,“恩,好的,我马上检查下”,然后又接起座机,是另外一个部门的主管说报表看不了。慌忙应付完了,赶紧检查数据库执行中的语句。 果然数据库产生大量的阻塞,,连带数据库服务器的操作都变得好慢(是我的心理作用吗?)。正准备先把收缩操作取消了,,电话有同时响起了,,,唉,不管了,先处理问题。然后点击取消。经过漫长的等待,,终于完成了,然后打电话跟各个部门解释,,写事故报告,,悲剧,,今天的午饭都不想吃了。

这个场景是不是很熟悉啊,关于数据库收缩的问题,是我在群里,论坛里,看到新人问过最频繁的问题之一。今天这篇文章对数据库收缩进行有个框架性说明,希望小伙伴在以后遇到相关的

问题时,做到心中有数。

 

 

SQLServer性能优化——等待——SLEEP_BPROOL_FLUSH

mysql优化的几个方面

  • 硬件 主要有 cpu、内存、磁盘io、网络
  • 系统配置 操作系统的不同、版本、以及操作系统的配置
  • 数据库 存储引擎的选择、数据库服务器配置参数、
  • 表结构设计及sql语句 sql语句优化、编写、表结构的设计

 

关于收缩的建议

不到万不得已,千万不要收缩数据库。收缩数据库影响极大:

1.收缩数据库对数据库的影响极大,产生大量日志和碎片,而且会锁表。如果你的库当前正在被使用,收缩不下去非常正常。
2.收缩数据库一定要手工来做的,而且是在维护窗口期做的事。
3.尽量使用语句来执行,可以提示错误

下面的文章详细介绍:
.

 

前言:

有一个用于历史归档的数据库(简称历史库),经过一定时间的积累,数据文件已经达到700多GB,后来决定某些数据可以不需要保留,就把这部分数据truncate了,空余出600多GB的空间,也就是说,经过收缩后,理论上数据库只有100多G。为此,我经过重建各个表(表数量不多,但单表数量还是有几千万)的聚集索引后,准备进行收缩。

但是当收缩开始时,即使把每次收缩的范围缩小到500MB,速度也极其慢,经常几个小时都没反应。经过查看等待信息之后发现有一个SPID=18的会话(SPID<=50的均为系统会话)一直显示等待状态为“SLEEP_BPROOL_FLUSH”,并且阻塞了收缩操作。

澳门新萄京官方网站 2

为此,我觉得即使是小概率事件(因为这个等待类型虽然常见,但是并不总引人注意),既然出现了,就不妨来研究一下。

说明:环境为SQL Server 2008R2

本文出处:

硬件

 

收缩的正确姿势

在不得不收缩的时候,参考下面的步骤

1.找到数据库中最大的几个表,重建所有索引。首先尝试指定Truncate Only收缩方式.它只是移除文件尾部的空闲空间,并不重新组织已经使用的数据页。

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);  

2 最后才考虑,不带选项的收缩。收缩不要一次性全部收缩。 可以每次收缩2G左右。不要把空间可用空间全部收缩了,可以剩余一部分比如4G。收缩完后,记得重建索引.

补充:

还有一种办法就是新建文件组,使用CREATE INDEX ... WITH(DROP_EXISTING = ON)ON语法将所有相关的的表和索引移动到新文件组。然后收缩旧的文件组。

 

3.可在进程中的任一点停止 DBCC SHRINKDATABASE 操作,任何已完成的工作都将保留。

  1. 不能在备份数据库时收缩数据库

 

简介:

既然这已经成为了问题,那么有必要先了解一下SLEEP_BPOOL_FLUSH这个等待状态是什么。在微软官方说明中: ,仅有简单的描述:当检查点为了避免磁盘子系统泛滥而中止新 I/O 的发布时出现。明显这种解释是不足的。因此我翻翻国外大牛的博客和其他书籍,总结如下:

这种等待状态与checkpoint进程有直接关系,checkpoint主要用于在内存的缓冲区(BufferPool)中,自加载到内存之后发生了数据改变(称为脏页),在checkpoint触发后把脏页从内存回写到磁盘的数据文件中。

所以很自然地想到Checkpoint。但是从行为特性来看,又意味着可能你的磁盘子系统有性能问题。

cpu选择

应用为计算密集型,应该使用更高的频率
应用的并发量高,则需要更多的核数

    数据库文件有两大类:数据文件跟日志文件,每一个数据库至少各有一个数据文件或者日志文件,数据文件用来存储数据,日志文件用来存储数据库的事务修改情况,可用于恢复数据库使用。

 可能需要收缩的场景

1.你删除了大量数据,而且数据不太可能增长。

2.要移除某个文件时,你需要先清空数据文件。

 

Checkpoint简介:

要了解SLEEP_BPOOL_FLUSH等待类型,有必要先了解一下Checkpoint这个东西。它是SQL Server后台触发的系统进程,也可以手动输入checkpoint来运行。

这个进程负责把缓冲区的被修改过的页写入到数据文件中。常见的地方是在备份中。这个进程的重要作用之一是加快数据库在异常情况下恢复的速度。当数据库发生故障时,SQL Server必须把数据库尽可能地还原到之前的正常状态。SQL Server会使用事务日志进行重做(redo)或回滚(undo),把未写入数据文件的修改重新附加会数据文件中。如果数据页被修改但还未写入数据文件,SQL Server必须把修改重做。如果之前已经有一次Checkpoint发生并把这些脏页写到数据文件,那么这一步就可以跳过,从而加快数据库的恢复速度。如图所示:

澳门新萄京官方网站 3

当一个数据页被事务修改后,这个修改会先被记录在事务日志中(实际上不写入LDF文件而是内存中的一块叫log buffer的区域中,然后再写到磁盘的LDF文件中,这个过程由WRITELOG和LOGBUFFER等待类型表示)。然后在内存的buffer pool中的对应数据页标识为脏页。当Checkpoint进程触发时,所有自上一次Checkpoint发生后至今的脏页都会被物理地写入磁盘的数据文件中,这个过程不会管引发脏页的事务的状态是什么(提交、未提交、回滚)。

通常来说,Checkpoint由SQL Server自动周期性运行(默认情况下为一分钟)。但是不代表真的是只有等待1分钟才触发。用户可以设置这个运行周期不过除非你确定问题的根源在此,否则不要随便修改。因为Checkpoint会自己分析当前IO请求、延时等情况进行触发。从而避免不必要的高IO开销。

在SQL Server中,有以下几种Checkpoint类型(关于Checkpoint的详细描述将在后续文章中专门介绍):

内部Checkpoint类型:不可配置,在特定情况下自动触发,比如备份。自动Checkpoint类型:如果未改动SQLServer相关配置,会在1分钟周期时触发。这种类型可以修改时间,但是这种修改是实例级别的,并且只能修改为小于等于1分钟。手动Checkpoint类型:通过SSMS或其他客户端发起checkpoint命令。这种触发可以输入一个秒数,用于指定checkpoint必须在这个秒数内完成。这种操作是库级别的。比如CHECKPOINT 10,代表SQL Server会在10秒内尝试执行checkpoint。详细内容可见: 2012引入的库级别选项。如果这个值大于0则会覆盖特定数据库上的默认自动Checkpoint配置,可以通过下面命令实现:

ALTER DATABASE[数据库名] SET TARGET_RECOVERY_TIME = [秒数或分钟数]

前面提到过,SQL Server会分析当前系统压力,当它认为当前没必要进行Checkpoint时,会扼杀这个进程,从而避免磁盘子系统的雪上加霜。当Checkpoint被扼杀时,就会记录在SLEEP_BPOOL_FLUSH等待类型的信息中。

在正常情况下,这种等待状态应该尽可能接近0。

内存

  • 一般来说通过增加内存,将数据缓存到内存中,从而提高数据吞吐性能
    瓶颈,如所有的数据都缓存到内存中了,在增加内存不会有性能的提升
    myisam 会将索引缓存到内存中
    数据通过操作系统缓存
    innodb 会同时在内存中缓存数据和索引

  • 内存的提升对于写会有提升,可以将多次写入改为一次写入

  • 内存的频率越高,速度越快

    这里分为两篇博文讲解,本文来说说数据文件。

总结

那我们处理磁盘空间不足的最好的办法是什么呢?最好的办法是在最初规划时,预估好未来一年或者二年的数据增长。给磁盘划分足够的空间。设置好数据库的初始大小,并且将自动增长使用固定量增长。

 

降低SLEEP_BPOOL_FLUSH等待:

既然有问题,那么就该解决,即使它可能通常没有多大性能问题。遇到这个问题时,建议首先检查配置,还是那句话,如无必要不要修改默认配置。可以通过下面语句查询配置值:

select * from sys.configurations where name ='recovery interval (min)'

其中“value”为0代表默认配置,这个值以分钟为单位,值越小,Checkpoint的频率就越高,越容易引发SLEEP_BPOOL_FLUSH等待。另外在事务中频繁使用CHECKPOINT命令也很容易触发这种等待。

除了这种情况之外,还有一个可能就是数据文件所在的磁盘子系统的性能问题。前面提到过,Checkpoint触发的结果是把缓冲区的脏页写入磁盘,如果当前磁盘负载非常大,那么Checkpoint操作就会被频繁扼杀,从而引起SLEEP_BPOOL_FLUSH等待。

磁盘的配置和选择

  • 传统机械磁盘

读取过程:1)移动磁头到磁盘表面正确的位置上,2)等待磁盘旋转,使所需的数据在磁头之下,3)等待磁盘旋转过去,所有所需的数据被磁头读出
12称为访问时间,3传输速度

选择因素:磁盘容量,传输速度,访问时间,主轴转速,物理尺寸

  • raid机械磁盘 磁盘冗余队列的简称

  • ssd
    相比机械磁盘,可以加强随机读写性能,随机读的性能提升明显
    支持的更好的并发
    长时间密集写操作容易损坏ssd

  • 网络存储
    外部文件存储设备加载到服务器上的方法
    最适合用于数据库备份

 

回归主题:

前面介绍了这种等待状态的含义、原因,那么现在来看看我的问题,因为问题还是要解决。经过检查,默认配置没问题,而我在执行的操作是数据文件收缩,所以问题应该是在收缩上面。

收缩数据文件有三个潜在问题:

收缩的逻辑就是把数据移动到数据文件较前的区中,因为收缩是从数据文件的最后的区开始回收,这个操作会消耗大量的时间和系统资源用于移动所有的数据。在这个过程中,SQL Server使用大量的CPU资源去决定数据可以移动到哪里,有多少空间可以用于移动,同时要求大量的IO资源用于从数据文件中读取数据和把数据写入到新的物理地址中。另外,如果表没有聚集索引,那么非聚集索引由于叶子节点记录了RID信息,所以移动会导致非聚集索引的信息更新开销。注意是“每个非聚集索引的每一行”都受影响。不用多说都可以想象到,这是很高开销的操作。日志文件的增长:不管当前使用何种恢复模式,SQL Server都会记录每个数据移动操作,每个数据页和区的分配或回收,还有每个索引的变更。这种记录会加重前面第一个问题的系统资源开销,同时会导致日志文件的快速增大。有一位MVP的博客上介绍了数据文件收缩所需的日志文件数量: 。里面介绍了碎片的不通类型和需要关注的碎片情景。

通过前面的分析,在查看服务器那个历史库所在的磁盘(普通SAS盘),可以初步确定是磁盘IO性能问题。因为在之前已经对所有表的聚集索引进行了重建(没有堆表),应该是数据紧密度足够高。这就是最头痛的问题,不可能因为收缩慢就说换磁盘,即使能换,财务流程也不是一般的繁琐。那么我们还是来想想怎么使得每次读写操作尽可能地小吧。 本文出处:

这个是一个历史库,历史库在月底(写本文的时候)会有比较多的月结类、年度结算类查询,在频繁使用的过程中收缩文件显然不合理,所以把这个操作放在闲时运行(闲时并不一定就是晚上,主要看系统类型和操作时间段)。另外,收缩的规模也要尽可能小,为了避免一大片的语句,可以用下面语句进行自动化收缩:

declare @sql nvarchar(1024)
declare @size int=758000--当前大小,MB为单位
declare @end int =1024  --停止范围
while @size>[email protected]  --直到达到停止范围前一直循环
begin
set @sql='DBCC SHRINKFILE (N''数据文件名'',' cast(@size as nvarchar(20)) ')'
--print @sql
exec (@sql)
set @[email protected]
end

其中注释掉的print语句用来检查将要执行的命令是否正确。这里只是抛砖引玉,读者可以根据实际情况修改或添加其他功能。另外代码倒数第二行set @[email protected]意思是每次收缩500MB,读者也可以根据具体情况测试,可能100MB/次反而是最好最快的,那不妨设为set @[email protected]。

通过调整每次收缩的规模、安排闲时运行,不定期手动运行Checkpoint,虽然等待状态依旧(毕竟磁盘性能是硬伤),但是收缩进度还如意。

最重要的手段还是在服务器闲时进行,在反复测试之后,晚上11点之后,服务器维护作业还未运行,而用户已经下班,此时即使每次收缩100G,也只需要1个多小时。

虽然结果有点不如意,读者可能希望看到如何彻底解决。但是毕竟是正式环境,不能轻易尝试和修改。但是除了前面的方式之外,还是有其他方式可以按需选择:

拆分数据文件,把文件移动到负荷较低或性能较高的磁盘。不过这个操作要考虑数据后期合并。某些库是可以短暂脱机的,可以把数据库移动到性能较好的盘然后附加再进行收缩。其实。。。不收缩未尝不是件好事。

系统

mysql适合的操作系统
windows、FreeBSD、Solaris、linux

  • centos优化
    linux优化
  • 插件式存储引擎 存储引擎针对于表,而不是针对于库的
![](https://upload-images.jianshu.io/upload_images/7357680-3810dcef62fd9615.png)

总结:

SLEEP_BPOOL_FLUSH等待跟SQL Server的Checkpoint进程有密切关系,而Checkpoint主要负责的是把脏页写入磁盘。在Checkpoint触发前,SQL Server会分析服务器当前负载,如果磁盘子系统压力过大导致Checkpoint被认为必须扼杀时,SQL Server会把这种状态记录到SLEEP_BPOOL_FLUSH等待状态中。

在一个正常的系统中,这种等待状态的等待时间不应该很长,但是它还是有可能影响系统性能。过于频繁地运行CHECKPOINT命令或把“recovery interval”的值设的过低,都可能引发SLEEP_BPOOL_FLUSH等待。数据文件的磁盘子系统性能过低也同样会引发这种等待信息。

因此,在发现这种等待状态频繁出现或等待时间很长时,需要检查SQL Server配置、语句及磁盘子系统。

前言: 有一个用于历史归档的数据库(简称历史库),经过一定时间的积累,数据文件已经达...

存储引擎


myisam

myisam存储引擎由MYD和MYI组成

 

特性

  • 并发性和锁级别 使用的是表级锁,读写混合操作并发差,
  • 表损坏修复 check table tablename 进行检查, repair table tablename 进行修复
  • 支持的索引类型 全文索引,
  • 支持数据压缩 myisampack命令压缩 已压缩的表只能进行读操作

    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!

限制

  • mysql5.0之前默认表大小为4g,5.0之后支持256tb

 

适用场景

  • 非事务应用
  • 只读类应用
  • 空间类应用

    本系列上一篇博文链接:SQL SERVER大话存储结构(5)_SQL SERVER 事务日志解析

innodb

mysql5.5版本之后称为默认存储引擎
适合处理大量小事务
innodb使用表空间进行数据存储
innodb_file_per_tabl
on 独立表空间:tablename.ibd; 通过optimize table命令收缩系统文件,可以同时向多个文件刷新数据
off 系统表空间 ibdatax 无法简单收缩文件大小,会产生io瓶颈
将系统表空间转到独立表空间,1mysqldump导出,2停止mysql服务,修改参数,3重启mysql,重建innodb表空间,4重新导入

 

特性

  • innodb 是一种事务型存储引擎
  • 完全支持事务的acid特性
  • redo log 和 undo log redo log 实现事务持久性,undo log 未提交事务
  • innodb 支持行级锁,行级锁可以最大程度支持并发,行级锁是由存储引擎实现的

性能监控命令

show engine innodb status


锁的概念

锁的主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性
锁的类型:读锁(共享的,不相互干扰),写锁(排他的,堵塞其他读锁和写锁)
锁的粒度:被加锁资源的最小单位,行级锁,表级锁,锁定的数据越少,并发越高
阻塞:不同锁之间的兼容,一个锁需要等待另一个锁的释放,因而产生了阻塞,阻塞是为了确保事务的可以并发。大量慢查询或者是alter table
死锁:两个或者两个以上事务,相互占用对方等待的资源。系统会自动处理。少量死锁不会对系统发生影响。

 

CSV

1 创建数据文件时,在考虑什么

文件系统存储特点:

  • 以文件方式存储
  • 所有的列 都是非空的
  • 不支持索引
  • 不适合大表 不适合在线处理
  • 可以直接对文件进行编辑

1.1 数据文件与文件组

    数据文件有两类,一类是主数据文件,一类是辅助数据文件。

    每一个数据库都有一个主数据文件数据文件用来存储数据,扩展名是 mdf。 

    一个数据库可以有0到多个的辅助数据文件,扩展名是 ndf。

 

    文件组这个概念,可能大多数人只有在涉及表分区的时候有了解过。

    顾名思义,文件组,就是给数据文件分为多个组,方便分配磁盘IO资源以及运维管理。每个数据库至少有一个文件组,含数据库主数据文件的组称之为 主文件组,一般不指定文件组名创建表格或者索引,则会默认把数据文件放在主文件组中,因为默认 主文件组就是 默认文件组,当然,也可以通过ALTER 语句来修改默认文件组为 其他文件组,这样,创建数据文件但不指定文件组时,则存放到设置的默认文件组中。

 

    这里有个注意事项:数据库中的大多数操作都是仅针对于文件组操作,比如创建索引或者创建表格。

 

    那么,什么情况下需要单文件,什么情况下有需要多个辅助数据文件呢?

  • 建立表格及索引时,只能指定到某一个文件组,不能够指定到这个文件组的某个文件
  • 同一个文件组内的数据文件,起到一个平摊分布数据的作用,如果是位于不同的驱动器,则有利于提高并发IO,如果是位于同一个驱动器,则有利于后期的运维管理;
  • 当使用表分区的时候,每一个分区会使用到一个辅助数据文件(可以同一个驱动器,也可以不同)
  • 大库的灵活运维管理,其实呢,如果在同一个驱动器上建立多个数据文件,对IO性能并没有任何改善,但是,却为后期的管理提供了方便性,尤其是大库管理,比如线上数据库损坏,需要还原出来一个新的数据文件,或者是测试环境的搭建等等,很多时候会遇到剩余的磁盘空间并不足以来存放这个大库,但是如果是多个数据文件,那么就可以分开指定驱动器存储,减少磁盘大小的要求。

 

    那么,什么情况下,会使用到多个文件组呢?

  • 使用表分区
  • 当磁盘IO资源出现瓶颈的情况下,可以考虑迁移部分热表到 其他文件组的文件上(不同驱动器),分散IO;
  • 当磁盘空间不足但是想把文件中的 冷表(类似与记录登录日志)的表格,迁移到其他驱动器上,可以考虑使用文件组;
  • 历史数据和热数据分开,历史归档数据损坏,不影响热数据;
  • 大库的灵活运维管理,可以使用文件组来备份数据库的一部分,比如某些特定的表格放在 辅助数据文件上,出事故后,还原的时候,可以对数据库进行部分还原,主文件组还原结束,即可提供服务,但在其他文件组上的对象暂时不能使用,等到其他文件组也还原结束,其存储的数据才能提供服务。

 

    如何新增文件或者文件组呢?如何迁移表格数据到新的文件组呢?

 

 1 --案例 1 :给数据库 dbpage新增 文件组 testfg,并在这个文件组内建立辅助数据文件 dbpage_3,dbpage_4
 2 USE [master]
 3 GO
 4 ALTER DATABASE [dbpage] ADD FILEGROUP [testfg]
 5 GO
 6  
 7 ALTER DATABASE [dbpage]
 8 ADD FILE (
 9            NAME = N'dbpage_3',
10            FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAdbpage_3.ndf' ,
11            SIZE = 51200KB ,
12            FILEGROWTH = 10240KB
13          ) TO FILEGROUP [testfg]
14 GO
15  
16 ALTER DATABASE [dbpage]
17 ADD FILE (
18            NAME = N'dbpage_4',
19            FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAdbpage_4.ndf' ,
20            SIZE = 51200KB ,
21            FILEGROWTH = 10240KB
22          ) TO FILEGROUP [testfg]
23 GO
24  
25 --案例 2 :指定文件组创建表格
26 CREATE TABLE tbtest(id int not null,name varchar(10) not null) on [testfg]
27  
28 --案例 3 :迁移表到其他文件组
29 --表无聚集索引,通过建立聚集索引,把整个表格迁移到 指定文件组
30 alter table tbtest add constraint pk_tbtest primary key (id) on [testfg]
31  
32 --表有聚集索引
33 方法一:重建聚集索引,先删除聚集索引,然后再建立新的聚集索引指定到文件组,如上一个SQL
34 方法二:利用表分区,先建立 中间表格,中间表添加分区方案,分区建立在 指定的文件组上,然后再 需要迁移到表格上执行 swith partion,然后重命名表格,最后删除旧表,中间表格的分区脚步这里不涉及
35  
36 ALTER TABLE tbtest SWITCH PARTITION 1 TO tbtest_new PARTITION 1 
37 GO
38  
39 EXEC sp_rename 'tbtest','tbtest_old'
40 EXEC sp_rename 'tbtest_new','tbtest'
41 GO
42  
43 DROP TABLE tbtest_old
44 GO

 

  检查某个表格在文件组的分布

1 select
2  
3       fg.name fgname,o.name tbname ,index_id,rows,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages
4 from sys.partitions p
5 join sys.allocation_units au on p.partition_id=au.container_id
6 join sys.filegroups fg on fg.data_space_id=au.data_space_id
7 join sys.objects o on p.object_id=o.object_id
8 where o.type='u'  and p.object_id=object_id('orders')

    澳门新萄京官方网站 4

澳门新萄京官方网站 5

    检查每个文件组一共有多少个表格

 

 1 with data as(
 2       select
 3             fg.name fg_name, o.name tbname
 4       from sys.partitions p
 5          join sys.allocation_units au on p.partition_id=au.container_id
 6          join sys.objects o on p.object_id=o.object_id
 7          join sys.filegroups fg on fg.data_space_id=au.data_space_id
 8          where o.type='u'
 9       group by o.name,fg.name
10 )
11 select
12       a.fg_name,
13          count(*) tbcount,
14          tbnames=stuff((select ',' b.tbname from data b where a.fg_name=b.fg_name order by tbname for xml path('')),1,1,'')
15 from data a
16 group by fg_name

    澳门新萄京官方网站 6

适用场景,作为数据交换的中间表使用

  • .csv 文件存储表内容
  • .csm 文件存储表的元数据和数量数
  • .frm 文件存储表结构信息

1.2 增长选项

    设置数据库文件的似乎,需要判断是否启用自动增长,如果启用,是采用百分比增长还是按指定大小增长,是否设置文件最大大小。

    首先,在线上业务,建议是:不设置文件的最大大小,避免 某些业务数据异常增长 导致空间不足,当然,这个设置的前提是,做了磁盘剩余空间监控及报警。

    既然不设置最大文件大小,那么就需要设置自动增长,但是,注意,建议在数据库最开始的时候,就设置足够大的空间,避免频繁自动增长,每次自动增长都会在增长期间影响到数据库的IO性能,从而影响数据库的使用,所以建议在最开始的时候,设置足够大的空间,如果后面发现文件自动增长比较频繁,可以找一个业务低峰期,再扩大数据文件,设置文件增长大小,建议不要设置为 百分比,避免数据库太大,按百分比,一次增长太大,导致增长影响时间加长,监建议设置为 指定大小 ,可以在200M左右,实际可根据磁盘性能及增长情况来调整。

    增长的调整,可以通过指定选项FILEGROWTH ,设置百分比 FILEGROWTH = 10%或者 设置指定大小 FILEGROWTH = 204800KB,或者通过界面操作。

    澳门新萄京官方网站 7

archive

1.3 即时初始化

  说起文件增长,这里要提一个至关重要的 windows 系统参数配置:即时初始化(Instant File Initialization)。

 

    什么是初始化呢?

    当服务需要申请存储空间来使用 时,操作系统需要用零来填充空间,填充结束则完成初始化操作,但是,如果申请的空间比较大时,会耗费非常久的时间。

 

    什么是即时初始化呢?

    即时文件初始化功能将回收使用的磁盘空间,而无需使用零填充空间,直接跳过了零填充的过程,新数据写入文件时会覆盖磁盘内容。如果SQL SERVER服务登录用户开启了即时初始化,那么就可以瞬间完成对数据文件的初始化,注意,日志文件不能立即初始化。

    

    SQL SERVER 中哪些操作可以即时初始化?

  1. 创建数据库
  2. 向现有数据库中添加文件
  3. 增大现有文件的大小、包括自动增长操作(不含日志文件的自动增长)
  4. 还原数据库或文件组

    就拿建立数据库来说,不设置即时初始化文时,创建一个100G的数据库需要接近6min,但是开启了即时初始化后,仅需要3秒。更好的应用是在自动增长这块,能有效减少自动增长的时间,从而大大减少自动增长的影响时间。

 

    如何开启即时初始化?

    查看SQLSERVER引擎的登录用户->给该用户添加 ' 执行卷维护服务 ' -> 重启SQLSERVER服务。

    查看SQL SERVER引擎的登录用户,如下:

    澳门新萄京官方网站 8

 

    打开 `管理工具`,点击 `本地安全策略` ,按下图找到 `执行卷维护任务` ,双击后选择添加 SQL server 服务的登录用户,然后点击 `应用` 即可。

    澳门新萄京官方网站 9

 

    添加后,需要重启SQL SERVER服务,使其加载该权限。所以,建议在数据库一开始安装的时候,就配置好该权限,或者在数据库宕机或者维护期间,做该操作。

 

    如何检查是否开启即时初始化?

    检查创建DB的时候,是否直接跳过零填充的过程。

 

 1 /*
 2 以全局方式打开跟踪标记 3004 和 3605。
 3 3004:查看SQL Server对日志文件进行填零初始化的过程
 4 3605:要求DBCC的输出放到SQL server ERROR LOG
 5   -1:以全局方式打开指定的跟踪标记。
 6 */
 7  
 8 DBCC TRACEON(3004,3605,-1)
 9 GO
10  
11 --创建测试库
12 CREATE DATABASE [xinysu]
13  CONTAINMENT = NONE
14  ON  PRIMARY
15 ( NAME = N'xinysu',
16   FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAxinysu.mdf' ,
17   SIZE = 104857600KB , FILEGROWTH = 204800KB
18 )
19  LOG ON
20 ( NAME = N'xinysu_log',
21   FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAxinysu_log.ldf' ,
22   SIZE = 524288KB , FILEGROWTH = 102400KB
23 )
24 GO
25  
26 --查看错误日志
27 Exec xp_readerrorlog 0,1,Null,Null,'2017-05-29 10:28:00','2017-05-29 10:30:00'
28  
29 --删除测试库
30 DROP DATABASE xinysu
31 GO
32  
33 DBCC TRACEOFF(3004,3605,-1)
34 GO

   

    可以看到,创建数据库xinysu,数据文件100G,日志文件512Mb,都是直接跳过零填充的过程,速度非常快。

文件系统存储特点

  • 以zlib对表数据进行压缩,磁盘io更少
  • 数据存储在ARZ为后缀的文件中
  • frm 存储表结构

2 DB收缩

存储特点

  • 只支持insert 和 select
  • 支持在自增id 上建立索引
  • 记录日志和数据采集类应用

2.1 指令及设置

 

    执行收缩的指令有两种:shrinkfile跟shrinkdatabase。一个是指定某个文件进行压缩,一个是指定某个数据库,对数据库下的所有文件进行压缩。  

 

    Shrinkfile的指令如下:

 

 1 DBCC SHRINKFILE  ( { file_name | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]   }  )  [ WITH NO_INFOMSGS ]
 2  
 3 /*
 4 target_size
 5 用兆字节表示的文件大小(用整数表示)。 如果未指定,则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。 默认大小为创建文件时指定的大小。如果target_size指定,DBCC SHRINKFILE 尝试将文件收缩到指定的大小。 将要释放的文件部分中的已使用页重新定位到保留的文件部分中的可用空间。 
 6  
 7 EMPTYFILE
 8 将所有数据从指定的文件都迁移到其他文件相同的文件组。 换而言之,清空文件将迁移数据,从指定的文件到同一个文件组中的其他文件。 清空文件可确保你没有新数据将添加到文件。可以通过删除该文件ALTER DATABASE语句。
 9  
10 NOTRUNCATE
11 文件末尾的可用空间不会返回给操作系统,文件的物理大小也不会更改。 因此,指定 NOTRUNCATE 时,文件看起来未收缩。
12 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
13  
14 TRUNCATEONLY
15 将文件末尾的所有可用空间释放给操作系统,但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。
16 target_size如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 选项不会移动日志中的信息,但会删除日志文件末尾的失效 VLF。
18  
19 WITH NO_INFOMSGS
20 取消显示所有信息性消息。
21 */
22  
23 --举例说明
24 DBCC SHRINKFILE ( dbpage_data, 100 )
25  
26 DBCC SHRINKFILE ( dbpage_data, EMPTYFILE)
27 --清空 dbpage_data 数据文件上面的所有内容
28  
29 DBCC SHRINKFILE ( dbpage_data, 100 ,NOTRUNCATE)
30 --收缩数据库 datapage的数据文件,文件名师 dbpage_data,收缩到100Mb
31 --重新分配超过100Mb的数据行到前面100Mb未分配的区,保留空闲空间
32  
33 DBCC SHRINKFILE ( dbpage_data, TRUNCATEONLY)
34 --收缩数据库 datapage的数据文件,文件名是 dbpage_data,文件末尾未使用的空间释放给操作系统,不会重新分配数据行到未分配的区
35  

 

  Shrinkdatabase指令使用如下:

 

 1 DBCC SHRINKDATABASE ( database_name | database_id | 0  [ , target_percent ]  [ , { NOTRUNCATE | TRUNCATEONLY  } ] ) [ WITH NO_INFOMSGS ]  
 2  
 3 /*
 4 database_name | database_id | 0  
 5 要收缩的数据库的名称或 ID。 如果指定 0,则使用当前数据库。
 6  
 7 target_percent
 8 数据库收缩后的数据库文件中所需的剩余可用空间百分比。
 9  
10 NOTRUNCATE
11 通过将已分配的区从文件末尾移动到文件前面的未分配区来压缩数据文件中的数据。 target_percent是可选的。
12 文件末尾的可用空间不会返回给操作系统,文件的物理大小也不会更改。 因此,指定 NOTRUNCATE 时,数据库看起来未收缩。
13 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
14  
15 TRUNCATEONLY
16 将文件末尾的所有可用空间释放给操作系统,但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。 target_percent如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 将影响日志文件。 若要仅截断数据文件,请使用 DBCC SHRINKFILE。
18  
19 WITH NO_INFOMSGS
20 取消严重级别从 0 到 10 的所有信息性消息。
21 */
22  
23 --举例说明
24 DBCC SHRINKDATABASE (dbpage, 20)
25 --对数据库dbpage执行收缩处理,其中收缩后空闲空间占整个数据库大小的 20%
26 --等同于先执行 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE) ,再执行DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)
27  
28 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE)
29 --对数据库dbpage执行收缩处理,其中收缩后空闲空间占整个数据库大小的 20%
30 --数据文件,分配文件末尾的区到文件前面未分配的区,压缩空间不会返回给操作系统,文件大小不变
31  
32 DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)
33 --对数据库dbpage执行收缩处理,但是收缩的空间不一定是 20%
34 --收缩的空间是文件末尾的可用空间,也就是 target_percent 在这里指定了也没有用
35 --日志文件跟数据文件,释放文件末尾的可用空间给系统文件,但是文件内不执行任何数据页移动

   

    测试数据库 dbpage,先查看数据库的空间分布情况,再收缩数据库,使得收缩后的数据,剩余的空余空间占整个数据库的50%。

 

use dbpage

GO

 

sp_spaceused

--数据库总大小 58M,其中未使用的空间有 40Mb

 

dbcc shrinkdatabase ('dbpage',50)

--执行收缩后,返回数据文件占用2512个数据页,实际使用1248个数据页,预估可以再收缩1248个数据页;日志文件占用288个数据页。

 

sp_spaceused

 

澳门新萄京官方网站 10.png)

澳门新萄京官方网站 11.png)

 澳门新萄京官方网站 12

澳门新萄京官方网站 13

澳门新萄京官方网站 14

澳门新萄京官方网站 15.png)

     收缩是指,回收数据库未使用的空间,如果数据文件20M,但是实际大小只有10M,那么DBCC SHRINKFILE 指定target_size=15M,则是把最后5Mb上面实际存储的数据内容重新分配到前面15Mb中未分配的区中,注意,DBCC SHRINKFILE 不会将文件收缩到小于存储文件中的数据所需要的大小。 例如,数据文件 实际使用 7 MB ,但占用 10 MB ,执行 DBCC SHRINKFILE 语句target_size的 6 时,将文件收缩到仅 7 MB,不是 6 MB。对于dbcc shrinkdatabase,也是跟以上的注意事项一样。

 

    设置有自动收缩跟手动收缩两种。

    自动收缩,可在 数据库 的`属性` 设置,把自动收缩设置为true,也可以执行命令如下:

USE [master]

GO

ALTER DATABASE [databasename] SET AUTO_SHRINK ON WITH NO_WAIT

GO

 

    自动收缩,其运行结果等同于 执行 dbcc shrinkdatabase(dbname,25),及在数据库中保留25%的自由空间,其他剩余空间回收,每30分钟检查一次来收缩数据。

memory

2.2 原理

    执行的时候,对数据库的每一个文件逐个进行压缩,从文件的末尾开始压缩。数据库引擎将按照 target_percent ,预估出每个文件可以压缩的空间,把文件末尾可压缩的空间上面的区,移动到前面不压缩的空闲区上。

澳门新萄京官方网站,    比如 一个数据库 xinysu,有数据文件及日志文件各一个,其中数据文件有20Mb,实际使用12Mb,如果设置 target_percent 为40%,那么意味这压缩后的 数据文件的 理想大小 = 8Mb/( 1-0.4 ) = 13.3 Mb ,则理想的压缩结果 为 13.3 Mb 。则数据文件末尾的 20Mb-13.3Mb = 6.7Mb 空间上面有实际存储数据的 区,则会移动到 前面13.3Mb空间的空闲区上。

    对于日志文件说来,数据库引擎会尝试收缩每个日志文件到目标大小,但是这里会有个前提,如果虚拟日志中的所有逻辑日志没有超过指定的目标大小,那么文件会正常截断,收缩到指定的目标大小,如果逻辑日志的大小大于指定的大小,那么数据库引擎将会尽可能多的收缩空间的空间,但是收缩情况不如理想状态。

 

    执行收缩前,可以通过sp_spaceused或者 sys.database_files来查看空闲空间,再根据空闲空间来收缩。

    

SELECT

      name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB  

FROM sys.database_files;

 

    也可以通过 dbcc showconfig来查看表格的空间分布情况

 

dbcc showcontig(tbtest)

 

/*

DBCC SHOWCONTIG 正在扫描 'tbtest' 表...

表: 'tbtest' (290100074);索引 ID: 1,数据库 ID: 10

已执行 TABLE 级别的扫描。

- 扫描页数................................: 336367

- 扫描区数..............................: 42175

- 区切换次数..............................: 203346

- 每个区的平均页数........................: 8.0

- 扫描密度 [最佳计数:实际计数].......: 20.68% [42046:203347]

- 逻辑扫描碎片 ..................: 59.42%

- 区扫描碎片 ..................: 68.06%

- 每页的平均可用字节数.....................: 2485.3

- 平均页密度(满).....................: 69.29%

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

*/

 

 

    某些情况下,会遇到,明明数据库文件是有空闲空间的,但是这些空闲的区,分散在每一个区中,而不是完整的有多个空闲的区,那么这个情况下,则无法有效的收缩数据库,因为 DBCC SHRINKFILE做的操作是 基于区操作的,它会把使用过的区前移,但是,它不会合并区合并页,如果数据库中,大多数区都只是使用少量数据页,那么收缩的效果也不会明显的。

     

    收缩对于数据库来说,是一个耗费IO资源以及会增加碎片的操作,不宜过于频繁执行DB 收缩。

    收缩可能会出现几个问题:

  • 文件并没有变小
    • 是否执行的命令含有 NOTRUNCATE
    • 是否指定的大小比实际数据的大小还要小
    • 数据文件没有空闲的区
    • 日志文件中的LSN无法截断,详情查看本系列第6篇
  • 执行时间非常久

    • 某些 基于版本控制隔离级别 的事务 堵塞了 收缩操作,这里会在 errorlog中有记录,可以查看
      • 如果是这个原因堵塞,可以选择停止收缩操作或者停止事务操作或者等待
    • 回收的空间特别大,并且回收的空间上有大量的数据页面需要重分配到前面的空闲数据页面上

文件系统存储特点

  • 也称为heap存储引擎,所以所有数据存储在内存中
  • 重启数据消失 需要数据是可再生的
  • 表结构 存储在ifm

3 空间计算方法和区别

    日志文件的空间统计,可以使用DBCC SQLPERF(LOGSPACE)指令,可以看到整个实例所有数据库的日志使用百分比及日志大小。

    澳门新萄京官方网站 16.png)

     澳门新萄京官方网站 17

    那么,数据文件呢?

    数据库计算数据文件空间使用情况,有好几种方式,这些方式在统计的时候,注意区分是基于区统计的还是基于页面统计的以及执行代价。

功能特点

  • 支持hash 等值查找
  • btree索引 范围查找
  • 所有字段的长度都是定长的,varchar(10). => char(10)
  • 不支持 text blog 大字段类型
  • 使用的是表级锁
  • 最大大小由 max_heap_table_size. 默认16mb 修改之后需要重建已存在的表

3.1 基于区统计

    DBCC SHOWFILESTATS 。

 

    该指令基于区统计数据文件的使用情况,从GAM和SGAM页面读取对区的分配信息,计算整个数据文件中有多少区被分配。所以只需要读取数据文件中的GAM和SGAM页面内容既可以统计,方便快速,不会增加系统的额外负担。

澳门新萄京官方网站 18.png)

    澳门新萄京官方网站 19

    注意,这里显示的是使用的区情况,所以,如果需要转换成kb,一个区8个数据页面,一个数据页面有8kb,文件名为 dbpage_3的文件大小为 800*8*8kb=51200kb=50Mb。

临时表

  • 系统使用的(未超过限制使用momery临时表, 超过限制 使用myisam 临时表)
  • create temporary table 建立的临时表

3.2 基于页面统计

3.2.1 sp_spaceused

    exec sp_spaceused [objectname, updateusage]。

 

    有两个可选参数,可以不选择也可以任意选择1-2个,objectname 默认为空, 如果不指定objectname,则是统计当前数据库的页面使用情况,如果指定,则是只统计某一个对象;updateusage 默认为 FALSE,如果指定 updateusage=TRUE,则在执行前,对当前数据库或者指定的 object 执行 DBCC UPDATEUSAGE with no_infomsgs,执行结束 UPDATEUSAGE后,再执行 sp_spaceused 。

 

    DBCC UPDATEUSAGE with no_infomsgs 会对数据库做什么操作呢?

    它会针对表或索引中的每个分区更正行、已用页、保留页、叶级页和数据页的计数, 如果系统表中没有错误,则 DBCC UPDATEUSAGE 不返回数据。 如果发现错误,并对其进行了更正,同时返回系统表中更新的行和列。由此可见,对于整个数据库或者某个大表执行 UPDATEUSAGE ,由于需要完整统计表格的页面使用情况,所以会耗费一定量的IO资源,对性能有一定影响。

    所以,一般情况下,执行sp_spaceused来统计空间使用情况时,不建议设置 updateusage=TRUE,除非怀疑 sp_spaceused的输出结果有误。

 

    sp_spaceused是如何来统计数据文件使用情况呢?

    查看 系统存储过程 sp_spaceused 的SQL代码,可以看到无论是基于库统计还是基于表格统计,都是通过这三个 sys.dm_db_partition_stats,sys.internal_tables ,sys.partitions 动态管理视图来查看的。而这几张视图的数据并非是实时更新,所以,有时候,在对表格做索引删除或新增或者大量删除表格数据后,执行sp_澳门新萄京官方网站_数据库数据文件,mysql优化策略。spaceused可能不是很准确,这个时候,就需要设置选项 updateusage=TRUE。

 

    测试结果如下:

    澳门新萄京官方网站 20

 

    其实,通过原理及测试情况,发现,sp_spaceused其实就是一个简易版的查询放到了存储过程中使用,但是由于其参数的限制,要不是分析整个数据库,要不是只能够分析某一个表格,无法批量分析,这个多多少少有些不方便,如果需要分析多个表格,则可以通过DMV视图来实现,详见下一小节。

3.2.2 sys.dm_db_partition_status

    动态视图的查询,其实跟sp_spaceused不加updateusage选项的原理是一样的,不过这个查询可以动态调整,方便一些。

     查询SQL如下,根据所需动态调整即可。

 

 1 select
 2       o.name,
 3       sum(case when (p.index_id<2) then row_count end) rows,
 4       sum(p.reserved_page_count)*8 reseved_kb,
 5          sum(p.reserved_page_count-p.used_page_count)*8 unused_kb,
 6          sum(p.used_page_count)*8 used_kb,       
 7          sum(case when (p.index_id<2) then (p.in_row_data_page_count p.lob_used_page_count p.row_overflow_used_page_count)
 8                   else p.lob_used_page_count p.row_overflow_used_page_count end
 9              )*8 data_kb,
10          sum(p.used_page_count-(case when (p.index_id<2) then (p.in_row_data_page_count p.lob_used_page_count p.row_overflow_used_page_count)
11                                      else p.lob_used_page_count p.row_overflow_used_page_count end)
12           )*8 index_kb
13 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id=o.object_id
14 where o.type='u'
15       and o.name in ('orders','tba','tb_clu_no_unique')
16 group by o.name
17 order by o.name

澳门新萄京官方网站 21

3.2.3 DBCC SHOWCONTIG

    这个指令在检查数据库碎片的时候,经常使用到。 用于显示指定的表或视图的数据和索引的碎片信息。这个指令在不指定FAST的情况下,可以说是最精确的统计方式了,细致到某个表格用了多少页,页面上的数据使用情况,碎片率如何,每次统计的时候,都会扫描这个表格的涉及到数据页,扫描过程会对逐个页面加锁然后释放,故在业务高峰期的时候,慎用,一方面是IO资源占用,另一方面是锁影响。

 

DBCC SHOWCONTIG

[ (

    { table_name | table_id | view_name | view_id }

    [ , index_name | index_id ]

) ]

    [ WITH

        {

         [ , [ ALL_INDEXES ] ]

         [ , [ TABLERESULTS ] ]

         [ , [ FAST ] ]

         [ , [ ALL_LEVELS ] ]

         [ NO_INFOMSGS ]

         }

    ]

 

--语法说明

table_name,table_id,view_name,view_id

可以指定object_name检查,或者object_id来查看;如果不指定任意对象,则说明是检查当前整个数据库的所有表格

 

index_name,index_id

指定对象后,指定某个索引进行分析;如果不指定,则是按照聚集索引分析,没有聚集索引则是全表分析;

 

ALL_INDEXES

分析扫描所有索引,包含非聚集索引

 

TABLERESULTS

返回结果按照表格显示

 

FAST

指定是否要对索引执行快速扫描和输出最少信息。 快速扫描不读取索引的叶级或数据级页。

 

ALL_LEVELS

仅为保持向后兼容性而保留

 

NO_INFOMSGS

取消严重级别从 0 到 10 的所有信息性消息。

 

    测试聚集索引表格tbindex,含2个非聚集索引。测试情况如下:

 

DBCC SHOWCONTIG (tbindex)

 

DBCC SHOWCONTIG (tbindex,ix_number_name)

 

DBCC SHOWCONTIG (tbindex)  WITH TABLERESULTS,ALL_INDEXES

 

DBCC SHOWCONTIG (tbindex)  WITH TABLERESULTS,ALL_INDEXES,FAST

 

澳门新萄京官方网站 22.png)

 澳门新萄京官方网站 23

    总体来说,如果是基于整个数据文件来看空间使用情况,DBCC SHOWFILESTATS是首选;如果需要动态查询表格的空间使用情况,可以使用DMV sys.dm_db_partition_status ;如果需要非常全面的分析表格的空间情况、碎片情况,则是用DBCC SHOWCONTIG。各个指令使用时,需明确其性能影响及准确性。

    总结空间计算的指令,说明如下:

澳门新萄京官方网站 24.png)

    澳门新萄京官方网站 25

 

 

参考文档:

《SQL SERVER 2012实施与管理实战指南》第8章

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站_数据库数据文件,mysql优化

关键词: