Working case – 第 3 页

阿里云关于MySQL数据库myisam的支持

2019-09-10 没有评论

最近在做一个阿里云跨账号的数据库迁移，这个库是和论坛相关的，用的是Discuz程序，数据库中有一张myisam表，用于记录帖子和楼层。

mysql> show create table abc_ddxid_ppiy_gdsitnpl \G;
*************************** 1. row ***************************
       Table: abc_ddxid_ppiy_gdsitnpl
Create Table: CREATE TABLE `abc_ddxid_ppiy_gdsitnpl` (
  `pid` int(10) unsigned NOT NULL,
  `fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `first` tinyint(1) NOT NULL DEFAULT '0',
  `floorid` int(8) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tid`,`floorid`),
  UNIQUE KEY `pid` (`pid`),
  KEY `fid` (`fid`),
  KEY `first` (`tid`,`first`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql> show create table abc_ddxid_ppiy_gdsitnpl \G;

*************************** 1. row ***************************

Table: abc_ddxid_ppiy_gdsitnpl

Create Table: CREATE TABLE `abc_ddxid_ppiy_gdsitnpl` (

`pid` int(10) unsigned NOT NULL,

`fid` mediumint(8) unsigned NOT NULL DEFAULT '0',

`tid` mediumint(8) unsigned NOT NULL DEFAULT '0',

`first` tinyint(1) NOT NULL DEFAULT '0',

`floorid` int(8) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`tid`,`floorid`),

UNIQUE KEY `pid` (`pid`),

KEY `fid` (`fid`),

KEY `first` (`tid`,`first`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

这里可以看到tid是帖子号，floorid是楼层，这里用了一个myisam的复合索引第二列作为自增 […]

..experience, Working case

数据库序列的监控

2019-08-03 没有评论

需要监控数据库的序列，在达到最大值前，进行告警。特别是mysql，往往因为字段的定义和auto incremental的定义不同，导致各自的上限不同。 Oracle：

SELECT 
x.*,
CASE WHEN increment_by<0
 THEN round(last_number/min_value*100,4)
WHEN increment_by>0
  THEN round(last_number/max_value*100,4)
 ELSE 0
  END 
AS percent_usage
from DBA_SEQUENCES x  WHERE cycle_flag='N'
ORDER BY percent_usage DESC;

1

2

3

4

5

6

7

8

9

10

11

SELECT

x.*,

CASE WHEN increment_by<0

THEN round(last_number/min_value*100,4)

WHEN increment_by>0

THEN round(last_number/max_value*100,4)

ELSE 0

END

AS percent_usage

from DBA_SEQUENCES x WHERE cycle_flag='N'

ORDER BY percent_usage DESC;

SQL Server: [crayon-670eed5d71683 […]

..experience, Working case

隐式转换检查

2019-08-03 没有评论

数据库中是隐式转换往往是性能的杀手，下面2个语句分别可以在sql server和oracle查询到目前在内存中的，使用了隐式转换的SQL： sql server 隐式转换：

DECLARE @dbname SYSNAME  
SET @dbname = QUOTENAME(DB_NAME());  
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,  
         ic.DATA_TYPE AS ConvertFrom ,  
         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,  
         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,  
         t.value('(@Length)[1]', 'int') AS ConvertToLength ,  
         query_plan  
FROM sys.dm_exec_cached_plans AS cp  
--FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp  
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )  
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )  
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')  
    AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')  
    AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')  
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 
and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

DECLARE @dbname SYSNAME

SET @dbname = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,

t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,

t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,

t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,

ic.DATA_TYPE AS ConvertFrom ,

ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,

t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,

t.value('(@Length)[1]', 'int') AS ConvertToLength ,

query_plan

FROM sys.dm_exec_cached_plans AS cp

--FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )

CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )

JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')

AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')

AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')

WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')

oracle隐式转换： [crayon-670eed5d71 […]

..experience, Working case

aws RDS 版本升级最佳实践的探讨

2019-02-03 一条评论

这篇文章其实在草稿箱中存在了挺长的一段时间，去年10月就已经开始写了，但是由于工作上的其他事情的干扰，一直还没写完。所以你可以看到我画的图中，now其实是指2018年10月（OCT）。趁着过年休假，把这个文章终于写完了。 aws rds被强制升级是个无奈的事情，版本不支持，而被强制升级会影响业务可用 […]

..experience, Working case

SQL Server报错The datediff function resulted in an overflow

2018-12-26 没有评论

zabbix的监控有一个报错:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

1	The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

经检查，这个报错，调用的是下面的一个监控：

select count(*) as cnt from sys.sysprocesses 
where  DateDiff(ss,last_batch,getDate())>=10 
and lastwaittype Like 'PAGE%LATCH_%' 
And waitresource Like '2:%'

1

2

3

4

select count(*) as cnt from sys.sysprocesses

where DateDiff(ss,last_batch,getDate())>=10

and lastwaittype Like 'PAGE%LATCH_%'

And waitresource Like '2:%'

这个监控脚本，是用来监控发生在temp上的pagelatch_up的争用。监控脚本中，包含了 […]

..experience, Working case

MySQL 不显示输出结果

2018-12-12 没有评论

有的时候，想看看语句执行时间有多长，但是有不想看的刷屏的输出，各个数据库可以用下面的方法：（1）Oracle： set autotrace trace，恢复的话，用set autottrace off （2）postgresql： EXPLAIN ANALYZE （3）MySQL： pager c […]

..experience, Working case

Azure云MySQL数据库受限功能列表

2018-11-20 没有评论

微软Azure云MySQL功能受限列表（截止2018年4月）多样性支持的数据库种类 SQL Server MySQL、MariaDB Postgresql CosmosDB（类似MongoDB） Redis 高可用性支持的区域 Azure中国账号支持2个区域 Azure全球账号支 […]

..experience, Working case

centos 7中配置keepalived日志为别的路径

2018-10-18 没有评论

keepalived 安装：

cd <keepalived_sourcecode_path>
./configure --prefix=/usr/local/keepalived

make &&  make install

mkdir /etc/keepalived
mkdir /etc/keepalived/scripts
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /root/keepalived-2.0.6/keepalived/etc/init.d/keepalived  /etc/init.d/
cp /usr/local/keepalived/sbin/keepalived /sbin/keepalived
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
chmod +x /etc/init.d/keepalived

1

2

3

4

5

6

7

8

9

10

11

12

cd <keepalived_sourcecode_path>

./configure --prefix=/usr/local/keepalived

make && make install

mkdir /etc/keepalived

mkdir /etc/keepalived/scripts

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

cp /root/keepalived-2.0.6/keepalived/etc/init.d/keepalived /etc/init.d/

cp /usr/local/keepalived/sbin/keepalived /sbin/keepalived

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

chmod +x /etc/init.d/keepalived

由于在默认状态下keepalived的日志会写入到/var/log/message中，我们需要将此剥离出来。在centos 6下可以：

（1）首先修改/etc/sysconfig/keepalived文件，注释掉如下，添加如下： 
#KEEPALIVED_OPTIONS="-D"
KEEPALIVED_OPTIONS="-D -d -S 0" 

（2）其次修改 /etc/rsyslog.conf 文件，添加如下：
local0.* /var/log/keepalived.log

1

2

3

4

5

6

（1）首先修改/etc/sysconfig/keepalived文件，注释掉如下，添加如下：

#KEEPALIVED_OPTIONS="-D"

KEEPALIVED_OPTIONS="-D -d -S 0"

（2）其次修改 /etc/rsyslog.conf 文件，添加如下：

local0.* /var/log/keepalived.log

[…]

..experience, Working case

pg数据库授权表给只读用户之后，权限慢慢消失

2017-11-16 2条评论

越来越多的互联网企业在使用postgresql数据库，我们也不例外。昨天开发请我建立了一个只读用户abc_tmp_test用户，并且将mkl_rw用户下的32个表授权给只读用户用。ok，请简单轻松的一个需求，很快就完成了。但是今天开发来和我说，昨天授权的几个表中，有部分表还是没有权限去读取，让我 […]

..experience, Working case

如何找到postgres中疯狂增长的wal日志的语句

2017-09-10 没有评论

很久以前，我写过一个文章，《如何查找疯狂增长arch的进程》，讲述在oracle数据库中如何查找导致当前疯狂增长arch的session。今天，我们在postgresql数据库中也遇到了类似的问题。在一个时间内，wal日志疯狂的增长，大约每分钟产生1G，而xlog疯狂cp去归档的结果，导致xlog […]

..experience, Working case

pg的跨库查询

2017-08-30 没有评论

mysql和mssql的跨库查询，基本只需要dbname.schema.table_name就可以实现，而pg的跨库查询，和oracle一样，需要通过类似dblink的方式来实现。pg在9.3之前建议使用dblink，在9.3之后，建议使用postgres_fwd(foreign-data wrap […]

Working case

是的，大疆DBA团队需要你的加入

2017-08-22 3条评论

是的，我们在招人。大疆DBA团队扩建了，目前有6个headcount，欢迎各路豪杰的加入。岗位职责： 1. 负责内网和云上（aws和阿里云）数据库的故障响应。 2. 负责公司数据库安装，部署，SQL优化，数据库故障的根因分析； 3.负责数据库自动化运维的开发,推进数据库的自动化建设； 4. 根据 […]