## 0. Read the summary of upgrade pg https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html ## 1. Prepare […]
数据库应急杀进程脚本
Oracle: (1)数据库内操作
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)操作系统中操作(要求登录到数据库主机)
1 2 |
## kill掉所有local=no的非本地连接进程 ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9 |
SQL Server:
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'; |
pg报错current transaction is aborted
在一个事务中,pg报错了current transaction is aborted:
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=> 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 […]
postgresql 检索当前数据库中所有的分区表
pg 10由于没有hash分区,而pg_pathman一直都是支持多种分区的。所以如果某些pg 11以前的系统,可能会混合部署pg原生分区和pg_pathman。 要检查这种混合部署环境中的分区情况,可以用下面的sql:
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 |
显示结果如下 […]
某系统物理机本地存储和虚拟机SAN存储性能对比测试
本文用fio测试了某系统的物理机本地存储和虚拟机使用SAN存储的性能对比。 FIO测试原始数据 说明: 虚拟机在小块文件的IO处理能力上,要比物理机处理的好。而物理机在大块文件的处理能力上 […]
一次MySQL存储空间撑爆的故障处理和分析
在一次对线上系统的压测过程中,数据库突然变成了只读状态。我们看了一下,是因为空间在短时间内,被撑爆了。云上的rds数据库,如果在空间打爆的情况下,确实会变成只读的情况。 我们这个业务,做了中美数据拆分,美国的数据库是在aws上,中国的数据库是在阿里云上,跑同样的一套逻辑。 可以看到,在短时间内: a […]
阿里云关于MySQL数据库myisam的支持
最近在做一个阿里云跨账号的数据库迁移,这个库是和论坛相关的,用的是Discuz程序,数据库中有一张myisam表,用于记录帖子和楼层。
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的复合索引第二列作为自增 […]
数据库序列的监控
需要监控数据库的序列,在达到最大值前,进行告警。特别是mysql,往往因为字段的定义和auto incremental的定义不同,导致各自的上限不同。 Oracle:
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-66a4b16109883 […]
隐式转换检查
数据库中是隐式转换往往是性能的杀手,下面2个语句分别可以在sql server和oracle查询到目前在内存中的,使用了隐式转换的SQL: sql server 隐式转换:
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-66a4b1610a […]
aws RDS 版本升级最佳实践的探讨
这篇文章其实在草稿箱中存在了挺长的一段时间,去年10月就已经开始写了,但是由于工作上的其他事情的干扰,一直还没写完。所以你可以看到我画的图中,now其实是指2018年10月(OCT)。趁着过年休假,把这个文章终于写完了。 aws rds被强制升级是个无奈的事情,版本不支持,而被强制升级会影响业务可用 […]
SQL Server报错The datediff function resulted in an overflow
zabbix的监控有一个报错:
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. |
经检查,这个报错,调用的是下面的一个监控:
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的争用。监控脚本中,包含了 […]
MySQL 不显示输出结果
有的时候,想看看语句执行时间有多长,但是有不想看的刷屏的输出,各个数据库可以用下面的方法: (1)Oracle: set autotrace trace,恢复的话,用set autottrace off (2)postgresql: EXPLAIN ANALYZE (3)MySQL: pager c […]