MySQL MMM 구성 (Centos7 + MySQL 8.0)

 

·       Version : MySQL 8.0.XX, Centos 7

 

MySQL MMM(MySQL Multi-Master) 구성하는 방법에 대해서 살펴본다. 이번 포스트에서는 MMM 구성에 대해서만 다루므로 MySQL 설치 Master-Slave 구성은 다른 글을 참고할 있도록 한다.

 

MMM구성에 관한 글을 찾아보면 대부분 Centos6 기반의 환경에서 설치된 글을 많이 있다. 필자의 경우 Centos7 환경에서 구성을 진행하였는데, Centos7 버전에서는 공식 가이드 문서에서 제공하는 코드와 조금 다르게 디렉터리 경로가 바뀐 부분이 있어 처음 설치시 오류가 발생하였다. 디렉터리 PATH 대한 설정 값을 수정하고 정상 작동확인한 내용을 정리하였다.

 

서버 구성

Host

IP

VIP

Role

DevMMM-Witness

192.168.0.1

 

Monitoring

DevMySQLMMM-1

192.168.0.2

192.168.0.102

Master 1

DevMySQLMMM-2

192.168.0.3

192.168.0.103

Master 2

 

·       Write VIP : 192.168.0.101

 

MNM 구성

·       MMM Monitor : MMM Agent 서버의 상태를 체크하고 상태에 따라 역할(reader, writer) 변경,관리

·       MMM Agent : MMM 에서 reader, writer 역할을 하는 구성원

·       MMM VIP : 유동적으로 writer 마스터 역할을 변경할 있도록 writer 구성하는 virtual IP

·       역할구성 : master역할을 있는 writer후보자들은는 두개로 구성하고, 나머지는 slave 역할만 하도록 구성

 

 

DevMMM-Witness

DevMySQLMMM-1

DevMySQLMMM-2

MMM 사용할 계정 생성

 

CREATE USER 'mmm_monitor'@'%' IDENTIFIED BY 'PASSWORD';

CREATE USER 'mmm_agent'@'%' IDENTIFIED BY 'PASSWORD';

 

GRANT ALL PRIVILEGES on *.* TO'mmm_monitor'@'%';

GRANT ALL PRIVILEGES on *.* TO'mmm_agent'@'%';

 

FLUSH PRIVILEGES;

 

호스트에 사용자 계정 추가

 

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

 

MMM 설치

sudo yum install mysql-mmm mysql-mmm-monitor

sudo yum install mysql-mmm mysql-mmm-agent

sudo yum install mysql-mmm mysql-mmm-agent

mmm_common.conf 설정

sudo vi /etc/mysql-mmm/mmm_common.conf

 

아래 설정 입력

active_master_role          writer

 

<host default>

        cluster_interface eth0

        pid_path /run/mysql-mmm-agent.pid

        bin_path /usr/libexec/mysql-mmm/

        replication_user repluser

        replication_password PASSWORD

        agent_user mmm_agent

        agent_password PASSWORD

</host>

 

<host DevMySQLMMM-1>

        ip 192.168.0.2

        mode master

        peer DevMySQLMMM-2

              mysql_port 3306

</host>

 

 

<host DevMySQLMMM-2>

        ip 192.168.0.3

        mode master

        peer DevMySQLMMM-1

              mysql_port 3306

</host>

 

<role writer>

        hosts DevMySQLMMM-1, DevMySQLMMM-2

        ips 192.168.0.101

        mode exclusive

</role>

 

<role reader>

        hosts DevMySQLMMM-1,DevMySQLMMM-2

        ips 192.168.0.102,192.168.0.103

        mode balanced

</role>

mmm_agent.conf 설정

 

sudo vi /etc/mysql-mmm/mmm_agent.conf

 

아래 설정 입력

include mmm_common.conf

this DevMySQLMMM-1

sudo vi /etc/mysql-mmm/mmm_agent.conf

 

아래 설정 입력

include mmm_common.conf

this DevMySQLMMM-2

mmm_mon.conf 설정

sudo vi /etc/mysql-mmm/mmm_mon.conf

 

아래 설정 입력

include mmm_common.conf

 

<monitor>

    ip                  127.0.0.1

    pid_path            /run/mysql-mmm-monitor.pid

    bin_path            /usr/libexec/mysql-mmm/

    status_path         /var/lib/mysql-mmm/mmm_mond.status

    ping_ips            192.168.0.2,192.168.0.2

    auto_set_online     60

</monitor>

 

<host default>

    monitor_user        mmm_monitor

    monitor_password    PASSWORD

</host>

debug 0

 

 

부팅시 자동 시작 등록

sudo systemctl enable mysql-mmm-monitor

sudo systemctl enable mysql-mmm-agent

sudo systemctl enable mysql-mmm-agent

MMM 시작

sudo systemctl start mysql-mmm-monitor

sudo systemctl start mysql-mmm-agent

sudo systemctl start mysql-mmm-agent

 

MMM동작 확인

·       sudo mmm_control show

·       sudo mmm_control checks all

 

·       Role Change (Run as Monitor)

sudo mmm_control move_role writer DevMySQLMMM-1

 

 

MMM Troubleshooting

MMM 서비스 아래와 같이 ADMIN OFFLINE 표시한 경우 아래 명령어 실행

 

mmm_control set_online mysqltest-2

 

 

[참고자료]

·       https://mysql-mmm.org/mmm2_guide.html

 

 

2020-01-31 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MMM, MySQL 복제, MySQL 이중화, MySQL Multi Master, 멀티마스터 복제, HA, Database 이중화, DBA

SQL Server master 데이터베이스 정리

 

·       Version : SQL Server

 

SQL Server에서 master 데이터베이스의 역할은 시스템에 대한 모든 정보를 기록한다. 여기에는 로그인 계정, 끝점, 연결된 서버 시스템 구성설정과 같은 인스턴스 차원의 메타 데이터가 포함된다.

·       master database : https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

 

우리는 가끔 의도하지 않게 master 데이터베이스에 개체를 생성하는 경우가 있다. 대부분 개체를 생성할 USE 문을 생략했을 수도 있고 많은 작업창을 띄어 놓고 사용하다가 실수할 수도 있다. 이번 아티클에서는 master 데이터베이스를 정리하는 방법에 대해서 알아본다. master 데이터베이스는 시스템의 설정을 저장하고 있으므로 해당 작업시 주의가 필요 하다.

 

[master에서 삭제하지 않을 목록]

아래 표에 정으되어 있는 오브젝트 유형은 시스템과 관련있기 때문에 삭제 목록에서 제외한다.

ET

External table

IT

Internal table

PC

Assembly (CLR) stored procedure

PG

Plan guide

RF

Replication-filter-procedure

S

System table

SQ

Service queue

TA

Assembly (CLR) DML Trigger

X

Extended procedure

-

CLR User-Defined Data Type (UDDT)

 

사용자는 자신만의 모듈을 생성하여 시스템 객체로 표시할 수도 있다. 경우 시스템 저장프로시저와 구별 있는 확실한 방법이 없기 때문에 주관적으로 식별해야 한다.

 

[master 에서 삭제할 목록]

오브젝트를 삭제하기 위해서는 약간의 순서가 필요하다. 예를 들어 테이블을 삭제하려면 외부키나 뷰를 제거해야하며, 테이블 함수를 사용하는 테이블을 삭제 때까지 파티션 함수를 제거할 없다. 또한 순환 참조가 있을 있으므로 많은 경우 스크립트를 여러번 실행해야 수도 있다.

 

아래 표시된 유형은 객체를 삭제하면 자동으로 삭제되기 때문에 크게 걱정할 필요가 없다.

C

Check constraint

D

Default constraint

PK

Primary key constraint

TR

DML trigger

UQ

Unique constraint

 

나머지는 아래 순서대로 삭제해야한다.

1

F

Foreign key constraint

Need to be dropped before tables, as described below.

2

V

View

Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.

FN

SQL scalar function

IF

SQL inline table-valued function

TF

SQL table-valued-function

3

P

Stored procedure

Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).

4

U

Table (user-defined)

Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.

5

TT

Table type

Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.

Alias types

 

6

SO

Sequence object

Dropped after tables/modules because they can't be dropped if any table or module references them.

R

Rule (old-style, stand-alone)

D

Old-style CREATE DEFAULT

Partition functions

 

7

Partition schemes

 

Can't be dropped until after partition functions.

8

SN

Synonym

I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).

9

Schemas

 

I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.

10

Roles and Users

 

For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. 

(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) 

For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

 

[기본 접근]

순환 참조는 여러 개체가 서로를 참조하므로 개체를 삭제하는 올바른 순서를 결정하기가 복잡하다. 그래서 스크립트가 실패하더라도 다시 실행하여 스크립트가 성공할때 까지 반복해서 실행 있는 스크립트로 작성해야 한다. 예를 들어 개의  SCHEMABINDING 뷰를 사용하는 경우 개의 뷰를 모두 삭제해야 테이블이 삭제된다.   순서가 명확하다면 순서대로 하면 되지만 뷰가 여러개 이면서 서로 뷰를 참조하는 경우 순서를 예측하기 어렵기 때문에 삭제 명령을 반복해서 실행한다.

try { drop view 1; go; drop view 2; }

 

 

[삭제 스크립트 (순서대로 실행)]

Foreign keys

-- script to drop Foreign Keys

USE [master];

GO

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER TABLE ' + objectname + N'

    DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

  SELECT N''FK ' + fkname + N' failed. Run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT fkname = QUOTENAME(fk.[name]),

    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

  FROM sys.foreign_keys AS fk

  INNER JOIN sys.objects AS t

  ON fk.parent_object_id = t.[object_id]

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

) AS src;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

View and functions

-- script to drop views

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

  SELECT N''View ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

  FROM sys.views AS v

  INNER JOIN sys.schemas AS s

  ON v.[schema_id] = s.[schema_id]

  WHERE v.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Stored Procedures

-- script to drop procedures

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.[type] = 'P'

  AND o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Tables

-- script to drop user tables

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Table ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.tables AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Table Types

-- script to drop table types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_table_type = 1

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Alias Types

-- script to drop alias types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.user_type_id > 256

  AND o.is_table_type = 0

  AND o.is_assembly_type = 0  -- not a CLR UDT

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Sequences

-- script to drop sequences

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.sequences AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE RULE

-- script to drop old-style Rules

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Rule ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'R'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE DEFAULT

-- script to drop old-style Defaults

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Default ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'D'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition functions

-- script to drop partition functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_functions

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition schemes

-- script to drop partition schemes

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_schemes

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Synonyms

-- script to drop synonyms

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.synonyms AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Schemas

-- script to drop schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Schema ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name])

  FROM sys.schemas AS s

  WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to re-route default schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

 END TRY

 BEGIN CATCH

  SELECT N''User ' + p.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

  SELECT 1

  FROM sys.schemas AS s

  WHERE name = p.default_schema_name

);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Roles and Users

-- script to change ownership of roles to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to change ownership of schemas to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

 END TRY

 BEGIN CATCH

  SELECT N''Schema ' + name + N' failed - run the script again.'',

    ERROR_MESSAGE(); END CATCH

'

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to remove members from roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

  PARSENAME(CONVERT(nvarchar(128),

  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

 THEN

  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

   + QUOTENAME(m.name) + N';'

 ELSE

  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

    + ''', @membername = N''' + m.name + N''';'

 END + N'

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP ROLE ' + QUOTENAME(r.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop users

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP USER ' + QUOTENAME(u.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''User ' + u.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

 

지금까지  master 데이터베이스를 정리하는 작업 순서, 스크립트 생성에 대해서 알아보았다. 객체를 삭제하는 명령은 자동화 있지만 모든것은 완벽히 분석할 수는 없다. (사용자 모듈을 시스템 모듈로 생성한 경우 ) 스크립트를 사용하더라도 생성된 스크립트를 반드시 DBA 검토를 해야한다. master 데이터베이스를 정리하는것은 위험한 작업이므로 반드시 많은 테스트와 검증이 필요하다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4530/cleaning-up-the-sql-server-master-database/

 

 

2019-04-18 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, mssql, DBA, master database, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions, sys.partition_schemes, sys.procedures, sys.schemas, sys.sequences,

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

 

·         Version : SQL Server

 

SQL Server에서 인덱스 재구성 통계 업데이트 작업을 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

 

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

·         Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE 실행하고 UPDATE 구문 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

 

 

세션2에서 아래 스크립트를 실행한다.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

 

sp_who2 실행하고 프로세스가 모두 실행중인지 확인한다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

59

RUNNABLE

.

AdventureWorks2014

UPDATE STATISTIC

 

 

세션3에서 아래 스크립트를 실행한다.

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

 

sp_who2 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 있다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

58

SUSPENDED

59

dventureWorks2014

SELECT

59

RUNNABLE

57

AdventureWorks2014

UPDATE STATISTIC

 

지금까지는 매우 간단한 시나리오였으며 SELECT UPDATE STATISTICS 의해 차단되고 UPDATE STATISTICS INDEX REORG(DBCC) 의해 차단되었음을 확인할 있다. 실제 서비스에서 sp_who2 실행하면 블로킹 체인의 SPID 한번에 확인하기 어려울 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID 확인할 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

SET NOCOUNT ON

GO

 

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

       SPID,

       BLOCKED,

       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

       BATCH

   FROM #T R

   WHERE (BLOCKED = 0 OR BLOCKED = SPID)

   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

   UNION ALL

   SELECT

      R.SPID,

      R.BLOCKED,

      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

      R.BATCH

   FROM #T AS R

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

   )

SELECT

   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

 

DROP TABLE #T

GO

 

 

아래 출력을 보면 ALTER INDEX 체인의 머리 부분에 있고 UPDATE STATISTICS 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 있다.

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

 

아래 스크립트를 사용하면 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 있다. WHERE절에 SPID 수정해서 사용한다.

SELECT

  tl.request_session_id as spid,tl.resource_type,

  tl.resource_subtype,

  CASE

     WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

     ELSE ''

  END AS object,

  tl.resource_description,

  request_mode,

  request_type,

  request_status,

  wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

 

 

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 있다. ALTER INDEX 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 까지 SELECT 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 있다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

 

 

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA

SQL Server 2017에서 향상된 UDF 실행 계획

 

·      Version : SQL Server 202017 CU3

 

SQL Server 2017 CU3 이후 부터 UDF(User-Defined Function) 실행계획이 개선되어 사용자에게 많은 정보를 보여주게 되었다. (필자가 쓰는 시점으로 최신 CU7까지 출시 되었다.) 이전에는 쿼리에 참조된 UDF 사용할 쿼리 실행 시간의 상당 부분이 함수 내에서 발생하여도 쿼리 실행계획을 보고 쉽게 파악하기가 어려웠다. SQL Server 2017 CU3 이후 부터는UdfCpuTime UdfElaspsedTime 가지 새로운 속성이 추가되어 사용자 정의 함수내에서 사용되는 리소스를 확인할 있게 되었다.

 

아래 스크립트는 테스트를 구성하기 위한 UDF 생성 스크립트이다. 테스트 쿼리를 실행하기 위해서는 Adventureworks 설치되어 있어야 한다.

-- Create UDF

CREATE FUNCTION ufn_CategorizePrice(@Price money)

RETURNS NVARCHAR(50)

AS

BEGIN

    DECLARE @PriceCategory NVARCHAR(50)

 

    IF @Price < 100 SELECT @PriceCategory = 'Cheap'

    IF @Price BETWEEN 101 and 500 SELECT @PriceCategory =  'Mid Price'

    IF @Price BETWEEN 501 and 1000 SELECT @PriceCategory =  'Expensive'

    IF @Price > 1001 SELECT @PriceCategory =  'Unaffordable'

    RETURN @PriceCategory

END

GO

 

아래 스크립트는 UDF 사용한 데이터 조회 스크립트이다. 실행 계획을 살펴보면 UDF 관련 정보를 확인할 있다.

-- Execute

SELECT dbo.ufn_CategorizePrice(UnitPrice),

    SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,

    OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,

    LineTotal, rowguid, ModifiedDate

FROM Sales.SalesOrderDetail

GO

 


 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/

 

  

2018-06-04 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL 2017, MS SQL, UDF 실행계획, 실행계획, DBA, DB튜닝, 향샹된 실행 계획 보기, SQL Server 2017, Showplan enhancements for UDF

MySQL/MariaDB InnoDB(Storage) row lock 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL/MariaDB에서 사용하는 InnoDB 스토리지 엔진에서  row lock 정보는 스토리지 엔진 테이블에서 발생한 단위의 lock 발생 횟수와 lock 대기 시간등을 나타낸다. InnoDB row lock 모니터링은 아래 스크립트를 실행하여 확인 있다.

Show status where variable_name like ‘Innodb_row_lock%’;

 

 

Variable Name

Comments

Innodb_row_lock_current_waits

현재 row 락을 획득하기 위해 대기중인

Innodb_row_lock_time

row lock 획득에 소비된 시간 (milliseconds)

Innodb_row_lock_time_avg

row lock 획득에 소비된 평균 시간 (milliseconds)

Innodb_row_lock_time_max

row lock 획득에 소비된 최대 시간 (milliseconds)

Innodb_row_lock_waits

InnoDB 테이블에서 row lock 기다려야 하는 횟수

 

InnoDB row lock 수는 lock 획득한 수가 아닌 lock 얻기 위해 대기하는 횟수로 지표가 평소보다 급증한다면 쿼리 대기가 발생( 경합)하는 것으로 쿼리 튜닝이 필요하다.

 

2018-05-18 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, InnoDB row lock, 이노디비, 스토리지 잠금, Row lock, 행락, MySQL Lock, lock wait, 잠금, 잠금 대기, DBA, 쿼리 튜닝, DB모니터링


MySQL/MariaDB InnoDB(Storage) buffer pool 모니터링

 

·      Version : MySQL 5.7, Docker

 

MySQL/MariaDB에서는 다양한 스토리지 엔진을 지원하며 스토리지마다 특성 또한 다르다. 스토리지 엔진에 대한 특성은 아래 링크를 참고 한다.

·       Comparison of MySQL database engines : https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

 

이번 포스트에서는 일반적으로 OLTP 서비스에 많이 사용하는 InnoDB 스토리지 엔진에서 buffer pool(버퍼풀) 관련한 모니터링 방법에 대해서 알아본다. 아래 스크립트를 실행 하면 InnoDB buffer pool 대한 값을 확인할 있다.

Show status where variable_name like ‘innodb_buffer%’;

 

 

Variable Name

Comments

Innodb_buffer_pool_dump_status

버퍼풀에 보관된 페이지를 기록한 상태

Innodb_buffer_pool_load_status

버퍼풀이 이전 시점에 해당하는 페이지를 읽음으로 워밍업을 진행한 페이지

Innodb_buffer_pool_resize_status

버퍼풀의 크기를 동적으로 조정하는 작업 상태

Innodb_buffer_pool_pages_data

전체 버퍼풀에서 현재 사용중인 버퍼풀 페이지

Innodb_buffer_pool_bytes_data

전체 버퍼풀에서 현재 사용중인 버퍼풀 바이트

Innodb_buffer_pool_pages_dirty

버퍼풀의 데이터 변경된 페이지 (더티페이지 )

Innodb_buffer_pool_bytes_dirty

버퍼풀의 데이터 변경된 바이트 (더티 데이터 )

Innodb_buffer_pool_pages_flushed

버퍼풀에서 플러시한 페이지

Innodb_buffer_pool_pages_free

전체 버퍼풀에서 사용하지 않은(사용가능한) 페이지

Innodb_buffer_pool_pages_misc

Row lock, hash index 같이 오버헤드에 할당되어 사용된 버퍼풀의 페이지

Innodb_buffer_pool_pages_total

전체 버퍼풀의 페이지

Innodb_buffer_pool_read_ahead_rnd

랜덤으로 미리 읽기가 발생한 페이지

Innodb_buffer_pool_read_ahread

미리 읽기 백그라운드 스레드가 버퍼풀로 미리 읽어들인 페이지

Innodb_buffer_pool_read_ahead_evicted

버퍼풀에 미리 읽어들인 페이지 사용되지 않고 제거된 페이지

Innodb_buffer_pool_read_requests

버퍼풀에서 논리적인 읽기 요청 횟수

Innodb_buffer_pool_reads

버퍼풀에 데이터가 없어서 디스크에서 직접 읽은 논리적

Innodb_buffer_pool_wait_free

페이지를 읽거나 생성할때 사용가능한 클린 페이지가 없을 경우 InnoDB 더티페이지를 비우고 작업이 끝나기를 기다리는

Innodb_buffer_pool_write_requests

버퍼풀에 대한 쓰기 횟수

 

InnoDB에서 버퍼풀의 크기는 MySQL 서버 변수에서 매우 중요한 부분이다. 너무 크게 설정하면 다른곳에서 사용할 메모리가 줄어들고, 너무 작게 설정하면 빈번한 디스크로의 요청이 발생하여 IO 부담을 가중시킬 있다. 일반 적으로 DB서버의 경우 DB외에 다른 서비스와 함께 사용하지 않으므로 OS 필요한 공간( + 여유 공간) + MySQL 필요 공간(예상 커넥션 , 스레드 , 소트 메모리 등등) 제외하고 버퍼풀로 사용하는경우가 많다.

 

버퍼풀 모니터링에서 중요하게 살펴보아야 하는 부분이Innodb_buffer_pool_pages_flushed 항목이다. 이는 버퍼풀의 더티 데이터를 디스크에 쓰는 과정으로 과도한 flushed 디스크에 부담을 있다. 또한 한번에 플러시 되는 크기가 디스크 IO 성능에 비례하여 너무 클경우 디스크에서 쓰기 지연 현상이 발생할 있으므로 디스크의 성능과 함께 모니터링 해야한다.

 

Innodb_buffer_pool_reads 수치가 높은경우 버퍼풀의 데이터를 활용하지 못하는것으로 디스크로 부터 직접 데이터를 읽어야 하기 때문에 디스크의 성능 저하가 발생 있다. 일반 적으로 버퍼풀의 크기가 작은 경우 버퍼풀에서 데이터를 오랫동안 캐싱할 없어 빈번한 IO 발생할 있다.

 

 

[참고자료]

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html

 

 

2018-05-15 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, InnoDB bufferpool, 버퍼풀, 이노디비, MySQL Storage, 스토리지 엔진, buffer pool 모니터링, DBA, MySQL 모니터링



MySQL/MariaDB Connection 모니터링

 

-       Version : MySQL 5.7.21, Ubuntu 16.0.4

 

MySQL 서버에서 connection 모니터링 하는 방법에 대해서 알아본다. MySQL  뿐만 아니라 다른 데이터베이스에서도 connections 수는 운영과 성능면에서 매우 중요하다. 특히 connection 개수가 max_connections 값을 초과할 경우 새로운 connection 할당되지 못하여 어플리케이션이 MySQL 서버에 연결되지 않아 오류가 발생할수 있다. 또한 신규 connection 할당 될때, 그에 따른 메모리 할당 기타 작업이 동반되기 때문에 빈번한 connection  연결 해제는 여러가지 성능 문제와도 관련이 있다.

 

MySQL에서 connection 대한 모니터링을 하려면 전역 시스템 변수 전역 상태 변수를 함께 살펴보아야 한다. 아래 스크립트를 실행하면connection 관련된전역 변수의 설정값을 확인 있다.

show variables where variable_name in (

'max_connections',

'max_user_connections'

)

 

 

Variable Name

Comment

Max_connections

현재 설정된 최대 동시 연결

Max_user_connections

계정당 생성할 있는 최대 동시 연결

 

 

아래 스크립트는 젼역 상태 변수로 connection 관련된 현재값을 확인 있다.

show status where variable_name in (

'max_used_connections',

'aborted_clients',

'aborted_connects',

'threads_connected',

'connections'

);

 


 

Variable Name

Comment

Max_used_connections

동시 최대 접속자

Aborted_clients

연결된 상태에서 강제로 연결 해제 연결

Aborted_connects

연결 과정 fail 연결

Threads_connected

현재 오픈된 연결

connections

연결 시도된

 

현재 사용중인 connection 사용 비율은 아래와 같은 공식으로 확인 있다.

Connection Usage(%) = (Threads_conneted / max_connections) * 100

 

위에서 언급 하였듯이 연결된 커넥션의 수가 max_connections 값을 초과 경우 커넥션을 할당 받지 못해서 연결 오류가 발생한다. 커넥션 수가 증가하는 원인에는 정상적인 서비스 요청이 증가하여 커넥션이 증가할 있지만 일부 비효울 적인 쿼리로 인해 특정 세션에서 대기나 잠금이 발생하여 리소스가 대기할 경우 새로운 요청에 대해서는 새로운 커넥션이 할당되어 연결되므로 연결 수가 증가할 있다. 그래서 커넥션 수를 모니터링 할때 리소스에 대한 모니터링을 함꼐 해야 한다.

 

max_connection커넥션의 수를 변경할 때는 서비스에 필요한 최대  연결수 이상으로 설정해야 한다.  OLTP 서버에서는 시간마다 요청수가 다르기 때문에 (서비스 요청이 몰리는 시간과 그렇지 않은 시간에 대한 커넥션 차이가 크다.) 최대한 서비스가 가장 바쁜 시간대의 값보다 크게 설정해야 한다.

 

아래 공식은 강제로 연결이 취소된 비율인데, 특정 세션에서 일정 시간이상동안 요청이 없다면 MySQL서버는 강제로 연결을 해제 한다. 비율이 높다면 wait_timeout 값의 조정이 필요 있다.

Aborted connection usage(%) = (aborted_clients  / connections) * 100

 

 

[참고자료]

·       https://dev.mysql.com/doc/refman/5.7/en/user-resources.html

·       https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

·       https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

 

2018-04-05 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

MySQL, connection, 커넥션 모니터링, max_connections, connection monitoring, DBA, MySQL 운영, aborted_cilent, max_user_connections, max_used_connections




In-Memory OLTP 사용시 메모리 할당량 초과 오류

 

·         Version : SQL Server 2014, 2016

 

SQL Server 에서 In-Memory OLTP 사용하는 환경에서 아래와 같은 오류가 에러로그에 기록되어 있다면 현재 적용되어 있는 In-Memory 용량을 초과한 것이다.

[INFO] HkDatabaseTryAcquireUserMemory(): Database ID: [7]. Out of user memory quota: requested = 131200; available = 74641; quota = 34359738368; operation = 1.

 

오류 로그에 기록된 내용은 아래와 같다.

·         requested = 131200 : 요청된 바이트

·         available = 74641 : 사용할 있는 바이트

·         quota = 34359738368 : 현재 할당된 바이트 (Standard Edition : 32G)

·         operation = 1 : 메모리 최적화 테이블 변수를 생성할 있도록 메모리가 요청

Ø  operation 0 : create memory optimized table

Ø  operation  1 : create memory optimized table variable

Ø  operation  2 : insert

Ø  operation  3 : update

 

위의 메시지 외에도 사용자 어플리케이션에서는 아래와 같은 메시지를 수신하는 경우도 있다.

Msg 41823, Level 16, State 171, Line 6 Could not perform the operation because the database has reached its quota for in-memory tables. See 'http://go.microsoft.com/fwlink/?LinkID=623028' for more information.

 

메모리 최적화 테이블 변수 사용시 루프를 사용할때 위와 같은 메모리 부족 오류가 자주 발생 할 수 있으니 주의 할 수 있도록 한다.

·         메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) : http://sqlmvp.kr/220996905075

 

SQL Server 2016 In-Memory OLTP quotas.

Edition

In-Memory OLTP quota(per DB)

Express

352MB

Web

16GB

Standard

32GB

Developer

Unlimited

Enterprise

Unlimited

 

 

[참고자료]

·         https://blogs.msdn.microsoft.com/psssql/2017/06/07/you-may-see-out-of-user-memory-quota-message-in-errorlog-when-you-use-in-memory-oltp-feature/

·         https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/in-memory-oltp-in-standard-and-express-editions-with-sql-server-2016-sp1/

 

 

2017-08-23 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2016, In memory OLTP, SQL Server, DBA, Memory optimize table, In Memory quota


SQL Server 2016 쿼리 실행에 대한 스레드(오퍼레이터) 성능 통계

 

·         Version : SQL Server 2014 SP2, 2016

 

SQL Server 2016에서 쿼리 성능 문제를 해결할 참고 있는 가지 정보가 추가 되었다. 대표적인 것이 라이브 쿼리 통계 이다.

·         SQL Server 2016 라이브 쿼리 통계 : http://sqlmvp.kr/220453033961

 

하지만 아직도 많은 쿼리 성능 문제를 해결하기 위한 메트릭은 부족한 편이다. 이번에 소개하는 기능은 showplan XML을 활성화하고 쿼리를 실행하였을때 각 스레드(오퍼레이터)에서 실행한 결과를 확인할 수 있는 기능이다. 예를 들어 SQL Server2014까지는 쿼리 실행결과 행결과를 반환하지만 스캔 성능에 관해 자세한 정보를 제공하지는 않았다.

<RunTimeInformation>

    <RunTimeCountersPerThread Thread = "0"ActualRows = "8001"ActualEndOfScans = "1"ActualExecutions = "1"/>

 </ RunTimeInfo

 

SQL Server 2016 부터는 실제 행 수를 포함하여 I/O read, CPU 시간 등의 내용이 스레드 별로 표시 된다.

<RunTimeInformation>

   <RunTimeCountersPerThread Thread="0" ActualRows="8001" ActualRowsRead="10000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="965" ActualCPUms="965" ActualScans="1" ActualLogicalReads="26073" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

</RunTimeInformation>

 

퀴리가 병렬로 수행되는 경우 아래와 같이 각 스레드의 실행 내용이 표시된다.

<RunTimeInformation>

    <RunTimeCountersPerThread Thread = "6"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "160"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "8"ActualRows = "0"ActualRowsRead = "886279"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "138"ActualCPUms = "137"ActualScans = "1" 1 "ActualLogicalReads ="2341 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "10"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "121"ActualCPUms = "120"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "12"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "155"ActualCPUms = "155"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "11"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "133"ActualCPUms = "132"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "9"ActualRows = "0"ActualRowsRead = "724955"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "124"ActualCPUms = "124"ActualScans = "1" 1 "ActualLogicalReads ="1918 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "1"ActualRows = "0"ActualRowsRead = "724955"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "121"ActualCPUms = "120"ActualScans = "1" 1 "ActualLogicalReads ="1918 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "7"ActualRows = "0"ActualRowsRead = "414260"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "111"ActualCPUms = "109"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "5"ActualRows = "0"ActualRowsRead = "1035650"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "168"ActualCPUms = "165"ActualScans = "1" 1 "ActualLogicalReads ="2740 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "/>

    <RunTimeCountersPerThread Thread = "4"ActualRows = "8001"ActualRowsRead = "932086"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "160"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "2"ActualRows = "0"ActualRowsRead = "828520"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "156"ActualCPUms = "130"ActualScans = "1" 1 "ActualLogicalReads ="2192 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "

    <RunTimeCountersPerThread Thread = "3"ActualRows = "0"ActualRowsRead = "932085"Batches = "0"ActualEndOfScans = "1"ActualExecutionMode = " "ActualElapsedms = "159"ActualCPUms = "158"ActualScans = "1" 1 "ActualLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLockReadAheads ="0 "ActualLogicalReads ="0 "

    <RunTimeCountersPerThread Thread = "0"ActualRows = "0"Batches = "0"ActualEndOfScans = "0"ActualExecutionMode = " "ActualElapsedms = "0"ActualCPUms = "0"ActualLogicalReads = 2 "ActualPhysicalReads ="0 "ActualReadAheads ="0 "ActualLobLogicalReads ="0 "ActualLobPhysicalReads ="0 "ActualLobReadAheads ="0 "/>

 </ RunTimeInformation>

 

또한 새로운 확장이벤트(query_thread_profiler)를 도입하여 각 노드 및 스레드의 성능에 대한 통찰력을 제공한다. 아래 스크립트는 이벤트를 생성하는 예제이다.

CREATE EVENT SESSION [PerfStats_XE] ON SERVER

ADD EVENT sqlserver.query_thread_profile(

ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text))

ADD TARGET package0.ring_buffer(SET max_memory=(25600))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

 

 

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

 

 

2017-08-21 / 강성욱 / http://sqlmvp.kr

 

SQL SQL Server 2016, Live query stats, operator level performance stat, DBA, SQL Tuning, DB Tuning, xEvent, MS SQL


SQL Server DBA 체크리스트

·         Version : SQL Server

 

SQL Server 운영할때 DBA role에서 정기적으로 수행하는 작업이 있을 것이다. 관리자마다 중요하게 생각하는 포인트는 다르겠지만 매일, 매주, 매월  일반적으로 점검해야하는 사항들에 대해서 정리해보려고 한다. 이번 포스트에서는 목록만 나열하고 추후 기회가 된다면 체크리스트 표를 만들어서 관리할 있는 방안에 대해서도 다루도록 한다.

 

[Daily Check list]

·         백업 : 백업 프로세스마다 성공적으로 수행되었는지 확인한다. 백업 단계마다 시간을 확인하여 평소와 다르게 시간이 오래걸렸는지 확인하는 것도 중요하다. (평소와 다르게 오래 걸렸다면 블록커가 있거나 다른 서비스에 영향을 수도 있기 때문이다.)

ü  SSMS에서 간단하게 백업 & 복원 하기 : http://sqlmvp.kr/140150000447

ü  백업 전략과 Differential Database Backup : http://sqlmvp.kr/140172210356

ü  Striping SQL Server Database Backup : http://sqlmvp.kr/140172045344

ü  백업 확장이벤트로 백업 진행 과정과 소요되는 시간 확인 : http://sqlmvp.kr/220391499627

ü  SQL Server 기본 백업 폴더 변경하기 : http://sqlmvp.kr/140172513430

ü  압축 백업 Checksum 옵션으로 손상 확인하기 : http://sqlmvp.kr/140205079888

ü  백업 히스토리 모니터링 : http://sqlmvp.kr/140172297682

ü  마지막 백업 시간 DBCC 확인 : http://sqlmvp.kr/140199855853

·         야간 배치 처리 : 야간에 실행되는 배치 작업에 대한 프로세스를 검토

·         SQL Server 오류 로그 : SQL Server 예기치 못한 오류나 보안 문제(로그인 성공/실패) 검토

ü  SQL Server Error Log 관리 : http://sqlmvp.kr/140171497815

ü  SQL Server Error Log 어디에 저장 될까? : http://sqlmvp.kr/140166321615

ü  SQL Server Error Log 보관 주기 설정(스크립트 ) : http://sqlmvp.kr/140186367262

ü  Read the End of a Large Error Log (에러로그 부분 읽기) : http://sqlmvp.kr/140173766910

·         Windows 이벤트 로그 : 최소한 응용프로그램이벤트 로그를 검사하여  Windows 또는 하드웨어 관련 오류 또는 경고가 있는지 확인 (하드웨어 공급업체 일부는 오류가 발생할 것으로 예상되면 윈도우 이벤트 로그에 경고를 기록하므로 사전에 예방할 있다.)

·         SQL Server Agent job : 실패한 SQL Server Agent 작업을 검토 한다. (SQL Server오류로그에서 Agent 오류 로그도 함께 확인한다.)

ü  SQL Server Agent Error log 위치 변경 : http://sqlmvp.kr/220006015843

ü  Windows Event Log SQL Server Agent Log 기록 : http://sqlmvp.kr/220012972112

ü  Verbose SQL Server Agent Logging : http://sqlmvp.kr/220017842626

·         HA 또는 DR 로그 : 고가용성 / 재해복구 로그를 확인한다. 사용중인 솔루션 (로그전달, 미러링, 클러스터링, 복제 ) 따라 검사해야할 항목이 결정된다.

·         Performance Logs : 성능 모니터링 정보를 확인하여 기준이 초과되었는지 검토하고 하루 느린 부분이 있는지 확인 한다.

ü  SQL, IIS, Windows 모니터링 (무료) : http://sqlmvp.kr/220973949008

ü  SQL Server 사용 성능 카운터 : http://sqlmvp.kr/140209466918

ü  확장 이벤트를 사용한 CPU 고부하 쿼리 추적 : http://sqlmvp.kr/140207447975

ü  SQL Server 활용한 Perfmon 로그 저장 : http://sqlmvp.kr/140164562598

ü  DMV 이용한 SQL Server 성능 카운터 확인 : http://sqlmvp.kr/140178354418

ü  DeadLock(교착상태) 모니터 하기 : http://sqlmvp.kr/140164120810

ü  DMV 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 : http://sqlmvp.kr/140178245107

ü  메모리 / CPU 관련 성능 카운터 : http://sqlmvp.kr/140209713721

ü  SQL Server Performance Counter Guidance : http://sqlmvp.kr/140194972031

·         Security Log : SQL Server 오류로그 또는 타사 솔루션에서 보안 로그를 검토하여 정책 위반 사항이 있는지 확인한다.

·         스토리지 : 단기간에 데이터베이스 백업, 배치 프로세스등을 지원할 있도록 드라이브에 충분한 여유 공간이 있는지 확인

ü  Xp_fixeddrives 세부 정보 확인하기 (디스크 공간 확인) : http://sqlmvp.kr/140197113754

ü  확장 저장 프로시저를 활용한 논리디스크 용량 확인 : http://sqlmvp.kr/220281589209

ü  데이터베이스 여유 공간 확인 : http://sqlmvp.kr/140173687321

·         Service Blocker : 전송 사용자 대기열을 검사하여 응용프로그램에서 데이터가 제대로 처리되고 있는지 확인

·         Corrective Actions : 발견한 문제 오류를 기반으로 취약 사항을 보완한다.

·         Improvements : 검토 분석을 기반으로 환경을 개선할 있는 방향을 찾는다.

·         Learn something new : 기술에 대한 지식을 향상시키기 위해 새로운 것을 배우기 위해 매일 시간을 할당 한다.

 

[Weekly / Monthly Check list]

·         백업 검증 : 백업된 파일을 정기적으로 복원하여 전체 프로세스가 정상적으로 작동하는지 확인 한다. 가끔 백업 파일은 정상으로 생성되었지만 가끔 파일이 손상되어복원이 안되는 경우가 있다.

ü  SSMS에서 간단하게 백업 & 복원 하기 : http://sqlmvp.kr/140150000447

ü  네트워크 드라이브에 데이터베이스 복원하기 : http://sqlmvp.kr/220289793106

ü  MDF 이용한 데이터베이스 복원 : http://sqlmvp.kr/140172813338

ü  SQL Server 2014 향상된 백업 복원 : http://sqlmvp.kr/140210996290

ü  트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 : http://sqlmvp.kr/220343607293

ü  손상된 부트페이지 복구하기 : http://sqlmvp.kr/220414698171

ü  트랜잭션 로그 LSN 이용한 삭제된 데이터 복구 : http://sqlmvp.kr/140206499367

ü  SQL Server Point in time recovery : http://sqlmvp.kr/140172126806

ü  SNAPSHOT 이용한 SQL Server 복원 : http://sqlmvp.kr/140172677501

·         Windows, SQL Server 또는 응용프로그램 업데이트 : 하드웨어, OS, DBMS 또는 응용프로그램 관점에서 SQL Server 설치해야하는 서비스팩 / 패치를 확인 한다.

ü  Update Center for Microsoft SQL Server : https://technet.microsoft.com/en-us/library/ff803383.aspx

·         용량 계획 : 6개월 또는 12개월 또는 18개월과 같이 특정 기간 동안 충분한 저장 공간을 확보할 있도록 용량 계획을 검토, 수정한다.

·         단편화 : 데이터베이스의 조각화를 검토하여 백업 SQL Server 분석을 기반으로 특정 인덱스를 다시 작성해야하는지 확인한다.

ü  DMV 이용한 인덱스 크기 조각화 정보 반환 : http://sqlmvp.kr/140192441485

ü  인덱스 유지관리 작업과 SQL Server 쿼리 성능 : http://sqlmvp.kr/220290829197

·         유지보수 : 매주 또는 매월 데이터베이스 유지 관리를 수행한다.

·         보안 : 조직을 떠나거나 직위가 변경된 개인의 불필요한 로그인과 사용자를 제거한다.

·         데이터베이스 축소 : 데이터베이스 또는 트랜잭션 로그가 경우 필요에 따라 파일을 축소하여 디스크 공간을 비운다.

ü  DB관리 - DB 축소(SHRINK) : http://sqlmvp.kr/140124850555

ü  DB 수정, 파일 크기 수정 : http://sqlmvp.kr/140124921608

 

[Automation]

·         SQL Server 영향을 주는 특정 수준의 오류 수준이나 오류 메시지에 대한 경로를 자동으로 통보 받도록 설정한다.

ü  Deadlock 감지하여 알림하기 : http://sqlmvp.kr/140195732732

ü  장기 트랜잭션 확인