Working case – 第 2 页

数据库应急杀进程脚本

2020-09-24 没有评论

Oracle：（1）数据库内操作

--单实例：
select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;' from v$session s
where s.status='INACTIVE'  --状态为非活跃
and s.USERNAME= 'ZZZ'  --用户为ZZZZ
s.type<>'BACKGROUND' --不为oracle后台进程
and program not like '%(J0%' --不为oracle的JOB进程
order by s.LOGON_TIME asc,sql_exec_start asc;

1

2

3

4

5

6

7

--单实例：

select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;' from v$session s

where s.status='INACTIVE' --状态为非活跃

and s.USERNAME= 'ZZZ' --用户为ZZZZ

s.type<>'BACKGROUND' --不为oracle后台进程

and program not like '%(J0%' --不为oracle的JOB进程

order by s.LOGON_TIME asc,sql_exec_start asc;

（2）操作系统中操作（要求登录到数据库主机）

## kill掉所有local=no的非本地连接进程
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

1 2	## kill掉所有local=no的非本地连接进程 ps -ef\|grep -v grep\|grep LOCAL=NO\|awk '{print $2}'\|xargs kill -9

SQL Server:

--kill 被阻塞会话
select  'kill '+cast(spid as varchar) FROM sys.sysprocesses sp
where sp.blocked !=0 and sp.spid != sp.blocked and loginame='XXX';

1

2

3

--kill 被阻塞会话

select 'kill '+cast(spid as varchar) FROM sys.sysprocesses sp

where sp.blocked !=0 and sp.spid != sp.blocked and loginame='XXX';

..experience, Working case

pg报错current transaction is aborted

2020-08-26 没有评论

在一个事务中，pg报错了current transaction is aborted：

mumy_corehrdban_psdb=> begin;
BEGIN
mumy_corehrdban_psdb=> select * from orasup_test1 ;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=> insert into orasup_test1 values(111);
INSERT 0 1
mumy_corehrdban_psdb=> select * from not_exist;
ERROR:  relation "not_exist" does not exist
LINE 1: select * from not_exist;
                      ^
mumy_corehrdban_psdb=> insert into orasup_test1 values(222);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

mumy_corehrdban_psdb=> select * from orasup_test1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> \d
ERROR:  current transaction is aborted, commands ignored until end of transaction block
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> rollback;
ROLLBACK
mumy_corehrdban_psdb=> select * from orasup_test1;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

mumy_corehrdban_psdb=> begin;

BEGIN

mumy_corehrdban_psdb=> select * from orasup_test1 ;

a

---

1

2

3

(3 rows)

mumy_corehrdban_psdb=> insert into orasup_test1 values(111);

INSERT 0 1

mumy_corehrdban_psdb=> select * from not_exist;

ERROR: relation "not_exist" does not exist

LINE 1: select * from not_exist;

^

mumy_corehrdban_psdb=> insert into orasup_test1 values(222);

ERROR: current transaction is aborted, commands ignored until end of transaction block

mumy_corehrdban_psdb=> select * from orasup_test1;

ERROR: current transaction is aborted, commands ignored until end of transaction block

mumy_corehrdban_psdb=>

mumy_corehrdban_psdb=> \d

ERROR: current transaction is aborted, commands ignored until end of transaction block

mumy_corehrdban_psdb=>

mumy_corehrdban_psdb=> rollback;

ROLLBACK

mumy_corehrdban_psdb=> select * from orasup_test1;

a

---

1

2

3

(3 rows)

mumy_corehrdban_psdb=>

原因是在一个事务中，pg如果遇到的Error的报错，会忽略后续的命令，后续所有命令都会报错：current transaction is abor […]

..experience, Working case

postgresql 检索当前数据库中所有的分区表

2020-05-15 没有评论

pg 10由于没有hash分区，而pg_pathman一直都是支持多种分区的。所以如果某些pg 11以前的系统，可能会混合部署pg原生分区和pg_pathman。要检查这种混合部署环境中的分区情况，可以用下面的sql：

select b.parent::text as part_table,
     'native partition' as part_tool,
CASE WHEN a.partstrat='r' THEN 'range'
     WHEN a.partstrat='l' THEN 'list'
     WHEN a.partstrat='h' THEN 'hash'
     ELSE 'other'
     END as part_type,
     b.cnt as part_cnt
from
pg_partitioned_table a,
(SELECT
    parent.oid,
    parent.relname          AS parent,
    COUNT(*) as cnt
FROM pg_inherits
    JOIN pg_class parent                 ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child                  ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent        ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child         ON nmsp_child.oid   = child.relnamespace
GROUP BY  oid,parent) b
where a.partrelid=b.oid
union all
select
     parent::text,
     'pg_pathman',
CASE WHEN parttype=1 THEN 'hash'
     WHEN parttype=2 THEN 'range'
     ELSE 'other'
END ,
count(*)
from pathman_partition_list
group by parent,parttype

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

select b.parent::text as part_table,

'native partition' as part_tool,

CASE WHEN a.partstrat='r' THEN 'range'

WHEN a.partstrat='l' THEN 'list'

WHEN a.partstrat='h' THEN 'hash'

ELSE 'other'

END as part_type,

b.cnt as part_cnt

from

pg_partitioned_table a,

(SELECT

parent.oid,

parent.relname AS parent,

COUNT(*) as cnt

FROM pg_inherits

JOIN pg_class parent ON pg_inherits.inhparent = parent.oid

JOIN pg_class child ON pg_inherits.inhrelid = child.oid

JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace

JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace

GROUP BY oid,parent) b

where a.partrelid=b.oid

union all

select

parent::text,

'pg_pathman',

CASE WHEN parttype=1 THEN 'hash'

WHEN parttype=2 THEN 'range'

ELSE 'other'

END ,

count(*)

from pathman_partition_list

group by parent,parttype

显示结果如下 […]

..experience, Working case

某系统物理机本地存储和虚拟机SAN存储性能对比测试

2019-11-20 一条评论

本文用fio测试了某系统的物理机本地存储和虚拟机使用SAN存储的性能对比。 FIO测试原始数据说明：虚拟机在小块文件的IO处理能力上，要比物理机处理的好。而物理机在大块文件的处理能力上 […]

..experience, Working case

一次MySQL存储空间撑爆的故障处理和分析

2019-11-06 2条评论

在一次对线上系统的压测过程中，数据库突然变成了只读状态。我们看了一下，是因为空间在短时间内，被撑爆了。云上的rds数据库，如果在空间打爆的情况下，确实会变成只读的情况。我们这个业务，做了中美数据拆分，美国的数据库是在aws上，中国的数据库是在阿里云上，跑同样的一套逻辑。可以看到，在短时间内： a […]

..experience, Working case

阿里云关于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-6629fc00ec671 […]

..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-6629fc00ed […]

..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全球账号支 […]