'1. IT Story'에 해당되는 글 397건

1. MySQL Session 조회

SELECT   a.*

FROM    INFORMATION_SCHEMA.PROCESSLIST A

WHERE   1            =    1

AND     A.COMMAND     NOT IN ('Binlog Dump','Sleep')

#AND        A.DB         IN    ('ssgaddb') # real

;

2. MySQL Metadata LOCK 조회

: 메타 정보 잠금 MySQL 5.5.3 이후 추가된 새로운 유형의 잠금으로 DDL문에 대한 잠금

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,

       THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO

FROM performance_schema.metadata_locks

    INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID

WHERE PROCESSLIST_ID <> CONNECTION_ID();

2. MySQL LOCK 조회

: 일반적인 잠금은 객체에 대한 읽기나 쓰기에 대한 잠금

select * from information_schema.INNODB_LOCK_WAITS;

--테이블은 아직 LOCK을 얻지 못하고 기다리고 있는 트렌젝션을 관리

--테이블이 Lock당한 프로세스를 조회

select * FROM information_schema.INNODB_LOCKS;

--Lock을 건 프로세스를 조회

select * FROM information_schema.INNODB_TRX;

--오랜 시간 동안 Commit 되지 않은 트렌젝션을 보려면 이 테이블을 보면 확인 가능

1. DB LEVEL 사이즈 조회

SELECT

COUNT(*) NUM_OF_TABLE

   , TABLE_SCHEMA,CONCAT(ROUND(SUM(TABLE_ROWS)/1000000,2),'M') ROWS

   , CONCAT(ROUND(SUM(DATA_LENGTH)/(1024*1024*1024),2),'G') DATA

   , CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024*1024),2),'G') IDX

   , CONCAT(ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/(1024*1024*1024),2),'G') TOTAL_SIZE

   , ROUND(SUM(INDEX_LENGTH)/SUM(DATA_LENGTH),2) IDXFRAC

FROM     INFORMATION_SCHEMA.TABLES

GROUP BY

TABLE_SCHEMA

ORDER BY

SUM(DATA_LENGTH+INDEX_LENGTH) DESC LIMIT 10;

2. TABLE LEVEL 사이즈 조회

SELECT TABLE_NAME,

       ROUND(DATA_LENGTH/(1024*1024),2) AS 'TABLE SIZE(MB)',

       ROUND(INDEX_LENGTH/(1024*1024),2) AS 'INDEX SIZE(MB)'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'TESTDB'

GROUP BY TABLE_NAME, DATA_LENGTH, INDEX_LENGTH;

1.PostgreSQL Export

 1-1. META ONLY

   - pg_dump -U TEST -E UTF8 -s -v -f /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.log

 1-2. DATA ONLY

   - pg_dump -U TEST -E UTF8 -a -v -f /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.log

 1-3. 주요사용 옵션(대소문자 주의) 

1. -U username

2. -d dbname

3. -W password / -w no password

4. -a data only

5. -E encoding

6. -f file

7. -s schema only

8. -t table : include table

9. -T talbe : exclude table

10. --help

2. DB 및 계정 RENAME

- TEST_201903.dmp 파일 안에 TEST 단어를 TESTDB로 치환

- find . -name "TESTDB_dataonly_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

- find . -name "TESTDB_schema_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

3. PostgreSQL Import

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_schema_201903.dmp

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_dataonly_201903.dmp

상세 옵션: https://www.postgresql.org/docs/10/app-pgdump.html

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",

  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')

    FROM pg_catalog.pg_attribute a

    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL

  ), E'\n') AS "Column privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT polname

    || CASE WHEN polcmd != '*' THEN

           E' (' || polcmd || E'):'

       ELSE E':' 

       END

    || CASE WHEN polqual IS NOT NULL THEN

           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)

       ELSE E''

       END

    || CASE WHEN polwithcheck IS NOT NULL THEN

           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)

       ELSE E''

       END    || CASE WHEN polroles <> '{0}' THEN

           E'\n  to: ' || pg_catalog.array_to_string(

               ARRAY(

                   SELECT rolname

                   FROM pg_catalog.pg_roles

                   WHERE oid = ANY (polroles)

                   ORDER BY 1

               ), E', ')

       ELSE E''

       END

    FROM pg_catalog.pg_policy pol

    WHERE polrelid = c.oid), E'\n')

    AS "Policies"

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')

  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1, 2;

- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples

WITH avt AS (

    SELECT 

            setting AS autovacuum_vacuum_threshold 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_threshold'

)

, vsf AS (

    SELECT  setting AS autovacuum_vacuum_scale_factor 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_scale_factor'

)

, fma AS (

    SELECT  setting AS autovacuum_freeze_max_age 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_freeze_max_age'

)

, sto AS (

    select  opt_oid

          , split_part(setting, '=', 1) as param

          , split_part(setting, '=', 2) as value 

    from    (

                select 

                        oid opt_oid

                      , unnest(reloptions) setting 

                from    pg_class

            ) opt

)

SELECT  '"'||ns.nspname||'"."'||c.relname||'"' as relation

      , pg_size_pretty(pg_table_size(c.oid)) as table_size

      , age(relfrozenxid) as xid_age

      , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) as autovacuum_freeze_max_age

      , (coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples

      , n_dead_tup as dead_tuples

FROM    pg_class c 

        join pg_namespace ns 

        on  ns.oid = c.relnamespace

        join pg_stat_all_tables stat 

        on  stat.relid = c.oid

        join avt 

        on (1=1) join vsf 

        on (1=1) join fma 

        on (1=1)

        left join sto cavt 

        on cavt.param = 'autovacuum_vacuum_threshold' 

        and c.oid = cavt.opt_oid

        left join sto cvsf 

        on cvsf.param = 'autovacuum_vacuum_scale_factor' 

        and c.oid = cvsf.opt_oid

        left join sto cfma 

        on cfma.param = 'autovacuum_freeze_max_age' 

        and c.oid = cfma.opt_oid

WHERE   c.relkind = 'r' and nspname <> 'pg_catalog'

and     (

            age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)

            or

            coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup

        )

ORDER BY age(relfrozenxid) DESC;

1. Temp tablespace 대체하기

*Temp tablespace가 100% 일시 테이블 스페이스를 재성성 하는 방법

*주의사항 Temp tablespace를 생성할 저장공간이 없다면 불가능한 방법임

예시)

1) 생성

CREATE TEMPORARY TABLESPACE "TEMP_1" TEMPFILE

'/ora_data/temp01_1.dbf' SIZE 1024M AUTOEXTEND OFF,

'/ora_data/temp02_1.dbf' SIZE 1024M AUTOEXTEND OFF,

'/ora_data/temp03_1.dbf' SIZE 4096M AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2) 변경

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_1

3) 삭제

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

2. UNDO tablespace 대체하기

1) 생성

CREATE UNDO TABLESPACE "UNDOTBS1_1" DATAFILE

'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ASITSNT\UNDOTBS01_1.DBF' SIZE 20480M AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2) 변경

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1_1;

3) 삭제

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;