首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
2008从入门到精通,关键字在值为null的应用举例www.64222.com:
发布时间:2019-12-28 20:36
浏览次数:

 前言

1197多语句事务要求更大的max_binlog_cache_size报错

转自:http://www.maomao365.com/?p=6873

数据库基本概念:区、页、行

:SQL Server中管理空间的基本单位。一个区大小为64KB,是八个物理上连续的页。SQL Server中每MB有16个区。一旦一个区被存储满,SQL Server将分配一个区给下一条数据。
:SQL Server中存储数据的基本单位,是区的分配单元。一个页大小为8KB。是SQL Server 每次读取和写入数据的最小单位。页的类型有数据页,索引页,文本页等等。
:行存储于页中,一行一般最多占满一页,也就是8KB,但也有些VARCHAR(MAX),TEXT,IMAGE类型的字段,可以跨越多页存储,一行最大2GB。

转帖请注明出处: http://www.cnblogs.com/Troy-Lv5/

  binlog_cache_size:为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。

摘要:
下文通过案例分析in 关键字在值为null的应用举例,
分析出not in关键字在null值产生的异常信息
如下所示:

收缩数据库

数据库中每个文件都可以通过删除未使用的页的方法来收缩。数据文件和事务日志文件都可以收缩。数据库收缩分为手动收缩和通过数据库设置自动收缩。

开发环境嘛, 作为.Net系Sql Server那是必备的. 听过Sql server可以安装在Linux上了..于是抱着试一试的心态体验了一把..

max_binlog_cache_size设置的参考标准

CREATE TABLE testA(keyId INT)
CREATE TABLE testB(keyId INT)
GO
INSERT INTO testA(keyId) VALUES (1)
INSERT INTO testA(keyId) VALUES (2)
INSERT INTO testA(keyId) VALUES (3)
GO
INSERT INTO testB(keyId) VALUES (1)
INSERT INTO testB(keyId) VALUES (2)
INSERT INTO testB(keyId) VALUES (4)
INSERT INTO testB(keyId) VALUES (NULL)
GO
 SELECT * FROM testA WHERE keyId IN (SELECT keyId FROM testB)
-----输出------
/*
keyId
1
2
*/
 SELECT * FROM testA WHERE keyId not IN (SELECT keyId FROM testB)
------无输出记录-----
/*
keyId
*/
INSERT INTO testA VALUES (NULL) ---在testA表中插入空值
SELECT * FROM testA WHERE EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
----输出----
/*
keyId
1
2
*/
SELECT * FROM testA WHERE not  EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
/*
keyId
3
NULL
*/
SELECT * FROM testA WHERE testA.keyId NOT in (
    SELECT testB.keyId FROM testB WHERE testB.keyId IS NOT NULL    
)

drop table testA
drop table testB
设置数据库自动收缩

数据库引擎会定期检查每个数据库的空间使用情况,自动收缩有可用空间的数据库。

ALTER DATABASE test
SET AUTO_SHRINK ON;

不试不知道, 一试吓一跳...安装依然是爆简单, 居然比在Windows上面安装还要简单....

  Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数;Binlog_cache_use 表示用binlog_cache_size缓存的次数,当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

 

设置关闭自动收缩
ALTER DATABASE test
SET AUTO_SHRINK OFF;

Just Do It!!!!

【故障情景】

通过以上测试,我们可以看出not in 查询关键字如果子表中存在空值,则无法查询出任何记录,会导致异常产生,
需使用not exists获取相应的空值信息

数据库手动收缩

DBCC SHRINKDATABASE(test,10);
GO
或者使用如下命令:

USE test
DBCC SHRINKFILE('test',0,TRUNCATEONLY);

上面命令收缩了test数据库的test.mdf文件,设置目标文件大小为0MB,TRUNCATEONLY参数表示将文件所有可用空间全部释放给操作系统,但不在文件内部执行页迁移。

 

  通过脚本以load的方式导入数据时,出现多行事务需要的max_binlog_cache_size空间不足。该数据文件HAOHUAN.txt只包含以逗号分隔的500万行左右的数据,每行四列,文件大小为270M。

收缩事务日志文件

若要运行SHRINKFILE命令收缩文件,首先要将数据库恢复模式设置成SIMPLE来截断该文件。

USE test
GO
ALTER DATABASE test SET RECOVERY SIMPLE;--将test数据库恢复模式设置成SIMPLE
GO
DBCC SHRINKFILE('test_log',1,TRUNCATEONLY);
--将test_log.ldf文件收缩到MB,且不允许文件内部页迁移
GO
ALTER DATABASE test SET RECOVERY FULL;
--将test数据库恢复模式设置回FULL
GO

 开始安装

1 [root@172-16-3-190 shells]# bash +x load_data_into.sh 
2                 文件的总数为:1 
3                 文件名为:/tmp/load/HAOHUAN.txt 
4 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
5 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by 'n' (merchant_no,bank_code,bank_card,protocol_no)
6 Warning: Using a password on the command line interface can be insecure.
7 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
截断数据文件

必须先用SELECT语句获取数据文件的file_id

USE test
GO
SELECT FILE_ID,name FROM sys.database_files;--获取test数据库中数据文件和日志文件的file_id
GO
DBCC SHRINKFILE(1,TRUNCATEONLY);--截断并收缩该file_id对应的文件
GO

 由于是新系统, 没有安装过Sql Server, 所以就变得很简单了. 根据微软官方的文档一步一步就OK...微软安装文档

【故障排查】

清空文件
ALTER DATABASE test--创建一个空数据文件test1data.ndf放在E盘
ADD FILE(
NAME='test1data',
FILENAME='E:test1data.ndf',
SIZE=5MB
);
GO

DBCC SHRINKFILE('test1data',EMPTYFILE);--利用SHRINKFILE语句清空该数据文件
GO

ALTER DATABASE test--删除该文件
REMOVE FILE test1data;
GO

www.64222.com 1

  查看max_binlog_cache_size的大小,发现数据文件的大小确实较max_binlog_cache_size的值要小,如果max_binlog_cache_size的大小不足以存放事务的binlog,那么会临时使用磁盘临时文件来存放binlog,通过查看Binlog_cache_disk_use发现使用临时文件存放的次数为1。因此增大max_binlog_cache_size的值到300M,再次执行脚本发现还是报相同的错误。且使用临时文件的次数为2,使用临时文件的存放binlog的总次数也相应由15增加到了16次。

复制和移动数据库

SQL Server 2008使用2中方式来进行复制和移动数据库操作。

由于是CentOS所以直接选择RHEL就OK了....

 1 mysql> show global variables like '%binlog_cache%';
 2 +-----------------------+-----------+
 3 | Variable_name | Value |
 4 +-----------------------+-----------+
 5 | binlog_cache_size | 16777216 |
 6 | max_binlog_cache_size | 268435456 |
 7 +-----------------------+-----------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> show global status like '%binlog_cache%';
11 +-----------------------+-------+
12 | Variable_name | Value |
13 +-----------------------+-------+
14 | Binlog_cache_disk_use | 1 |
15 | Binlog_cache_use | 15 |
16 +-----------------------+-------+
17 2 rows in set (0.00 sec)
18 
19 mysql> set @@global.max_binlog_cache_size=300000000;
20 Query OK, 0 rows affected, 1 warning (0.00 sec)
21 
22 [root@172-16-3-190 shells]# bash +x load_data_into.sh          
23                 文件的总数为:1 
24                 文件名为:/tmp/load/HAOHUAN.txt 
25 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
26 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by 'n' (merchant_no,bank_code,bank_card,protocol_no)
27 Warning: Using a password on the command line interface can be insecure.
28 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
29 
30 mysql> show global status like '%binlog_cache%';         
31 +-----------------------+-------+
32 | Variable_name | Value |
33 +-----------------------+-------+
34 | Binlog_cache_disk_use | 2 |
35 | Binlog_cache_use | 16 |
36 +-----------------------+-------+
37 2 rows in set (0.00 sec)
分离和附加数据库

此方法是复制和移动数据库的最快方式。要求用户必须是源和目标服务器sysadmin固定服务器角色的成员,源数据库脱机,分离数据库前要将数据库设置成单用户模式。

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有