OracleBlog – 第 4 页 – 笑看数据库江湖的那些事儿……

SQL Server数据文件结构

2020-09-24 没有评论

Data file 结构：第一个extent:

page 0 : File Header，注m_type = 15，Metadata: ObjectId = 99

page 1 : PFS( page free space)，注m_type = 11，Metadata: ObjectId = 99

page 2 : GAM ( global allocation map)，注m_type = 8，Metadata: ObjectId = 99，每个GAM管理4GB的页面，所以每隔4GB大小的页面，会有一个GAM。

page 3 : SGAM ( shared global allocation map)，注m_type = 9，Metadata: ObjectId = 99

page 4 : 空的，注m_type = 0，Metadata: ObjectId = 0

page 5 : 空的，注m_type = 0，Metadata: ObjectId = 0

page 6 : DCM（differential change map），注m_type = 16，Metadata: ObjectId = 99

page 7 ：BCM（bulk change map），注m_type = 17，Metadata: ObjectId = 99

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

page 0 : File Header，注m_type = 15，Metadata: ObjectId = 99

page 1 : PFS( page free space)，注m_type = 11，Metadata: ObjectId = 99

page 2 : GAM ( global allocation map)，注m_type = 8，Metadata: ObjectId = 99，每个GAM管理4GB的页面，所以每隔4GB大小的页面，会有一个GAM。

page 3 : SGAM ( shared global allocation map)，注m_type = 9，Metadata: ObjectId = 99

page 4 : 空的，注m_type = 0，Metadata: ObjectId = 0

page 5 : 空的，注m_type = 0，Metadata: ObjectId = 0

page 6 : DCM（differential change map），注m_type = 16，Metadata: ObjectId = 99

page 7 ：BCM（bulk change map），注m_type = 17，Metadata: ObjectId = 99

注意，如果大temp文件（一个超过4G）的page latch，往往是在temp数据文件的第一个extent的第2个page上，其争用可以用下面的sql监控： [crayon-680c […]

Study note

数据库内查询pg的表结构定义

2020-09-24 没有评论

需要利用到plperlu和自己写一个system函数。

-bash-4.2$ psql
psql (9.6.2)
Type "help" for help.

postgres=# create extension plperlu;
CREATE EXTENSION
postgres=# \dx
                      List of installed extensions
  Name   | Version |   Schema   |              Description               
---------+---------+------------+----------------------------------------
 plperlu | 1.0     | pg_catalog | PL/PerlU untrusted procedural language
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

1

2

3

4

5

6

7

8

9

10

11

12

13

-bash-4.2$ psql

psql (9.6.2)

Type "help" for help.

postgres=# create extension plperlu;

CREATE EXTENSION

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+----------------------------------------

plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(2 rows)

postgres=# CREATE OR REPLACE FUNCTION system(text) RETURNS text
postgres-# AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu;
CREATE FUNCTION
postgres=# 
postgres=#

1

2

3

4

5

postgres=# CREATE OR REPLACE FUNCTION system(text) RETURNS text

postgres-# AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu;

CREATE FUNCTION

postgres=#

postgres=# select system('pg_dump -s -t orasup_test1 dbinfo2 |egrep -v "^--|^$"');
                       system                        
-----------------------------------------------------
 SET statement_timeout = 0;                         +
 SET lock_timeout = 0;                              +
 SET idle_in_transaction_session_timeout = 0;       +
 SET client_encoding = 'UTF8';                      +
 SET standard_conforming_strings = on;              +
 SET check_function_bodies = false;                 +
 SET client_min_messages = warning;                 +
 SET row_security = off;                            +
 SET search_path = public, pg_catalog;              +
 SET default_tablespace = '';                       +
 SET default_with_oids = false;                     +
 CREATE TABLE orasup_test1 (                        +
     a integer,                                     +
     b character varying(200)                       +
 );                                                 +
 ALTER TABLE orasup_test1 OWNER TO djidba_rw;       +
 CREATE INDEX idx_b ON orasup_test1 USING btree (b);+
 
(1 row)

postgres=# 
postgres=#

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

postgres=# select system('pg_dump -s -t orasup_test1 dbinfo2 |egrep -v "^--|^$"');

system

-----------------------------------------------------

SET statement_timeout = 0; +

SET lock_timeout = 0; +

SET idle_in_transaction_session_timeout = 0; +

SET client_encoding = 'UTF8'; +

SET standard_conforming_strings = on; +

SET check_function_bodies = false; +

SET client_min_messages = warning; +

SET row_security = off; +

SET search_path = public, pg_catalog; +

SET default_tablespace = ''; +

SET default_with_oids = false; +

CREATE TABLE orasup_test1 ( +

a integer, +

b character varying(200) +

); +

ALTER TABLE orasup_test1 OWNER TO djidba_rw; +

CREATE INDEX idx_b ON orasup_test1 USING btree (b);+

(1 row)

postgres=#

..experience, Working case

How to upgrade aws rds postgresql

2020-09-24 没有评论

## 0. Read the summary of upgrade pg https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html ## 1. Prepare […]

Study note

pg常用大小查询

2020-09-24 没有评论

查出所有数据库大小：

select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database order by size desc;

1	select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database order by size desc;

查出所有表按大小排序并分离data与index：

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SELECT

table_name,

pg_size_pretty(table_size) AS table_size,

pg_size_pretty(indexes_size) AS indexes_size,

pg_size_pretty(total_size) AS total_size

FROM (

SELECT

table_name,

pg_table_size(table_name) AS table_size,

pg_indexes_size(table_name) AS indexes_size,

pg_total_relation_size(table_name) AS total_size

FROM (

SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name

FROM information_schema.tables

) AS all_tables

ORDER BY total_size DESC

) AS pretty_sizes;

查看索引大小：

select relname as table_name,pg_size_pretty(pg_relation_size(relid)) as table_size, 
indexrelname as index_name,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size from pg_stat_user_indexes 
order by pg_relation_size(indexrelid) desc;

1

2

3

4

select relname as table_name,pg_size_pretty(pg_relation_size(relid)) as table_size,

indexrelname as index_name,

pg_size_pretty(pg_relation_size(indexrelid)) as index_size from pg_stat_user_indexes

order by pg_relation_size(indexrelid) desc;

..experience, Working case

数据库应急杀进程脚本

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

显示结果如下 […]

Study note

小记MySQL的锁和事务

2019-11-20 没有评论

（一）先说明一下定义： 1. 读现象（Read phenomena）： SQL 92标准规定了3种不同的读现象。脏读、不可重复读和幻读。分别解释一下。 1.1 脏读： A dirty read (aka uncommitted dependency) occurs when a transactio […]

..experience, Working case

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

2019-11-20 一条评论

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

It's my life

华硕AC86U路由器不定期连不上公网的解决办法

2019-11-20 没有评论

华硕的路由器，不知什么原因，总是会不定期连不上公网，在网络地图的互联网状态，会显示连接失败的状态。测试了一下发现，ACU86U在上级的光猫路由器比如重启，比如间歇断网之后，哪怕上级路由器恢复，ac86u还是无法重连外网。我测试了断开上级的光猫路由的电源插头再插上，发现AC86U不会自动重拨，AC8 […]

..experience, Working case

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

2019-11-06 2条评论

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

It's my life

dba将死，云架构师即将到来

2019-10-11 没有评论

最近在招人，在招人的时候，有不少反思。作为一个dba，我们这个行业的趋势如何，我们的出路如何。（一）首先看到的一点是，目前越来越多的公司使用了云服务，自建机房的企业越来越少了。上云之后，很多企业对数据库的使用方式，是直接使用了云厂商提供的RDS，数据库服务，而不是在云上自建虚拟机再安装数据库。除 […]