과거의 기억에서 부터

옛날에 물이 먹어서 고장이 났던 핸드폰이 있었다.
우연히 다시 찾아서 전원을 켜보니 다시 실행이 된다.
가장 마지막 메일이 2014년 10월 달  그 이 후로 부터 2년  반 만에 다시 만났다.
과거의 기억도 좀 남아 있고 또 추억의 사진도 몇장 건졌다.
다시 친해져 볼까 한다. 이 아이랑

MySQL 의 날짜와 로우넘버

MySQL 에서 Date 와 Rownum 을 만드는 쿼리 임

세상에는 나 보다 똑똑 하신 분들이 많네

/**
    make date calander 
*/


--
-- 현재 일자 부터 과거 로 몇일 
--
 SELECT dt.yyyymmdd
    FROM (
           SELECT
               curdate() - INTERVAL (100*a.a + (10 * b.a) + (c.a)) DAY AS yyyymmdd
           FROM (SELECT 0 AS a
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 ) AS a
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS b
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS c
         ) dt
    where yyyymmdd BETWEEN curdate() - INTERVAL 10 day and curdate()
    order by 1
;

--
-- 년도별 칼렌더
--

SELECT
  ym
  , min(CASE dw
        WHEN 1
          THEN d END) Sun
  , min(CASE dw
        WHEN 2
          THEN d END) Mon
  , min(CASE dw
        WHEN 3
          THEN d END) Tue
  , min(CASE dw
        WHEN 4
          THEN d END) Wed
  , min(CASE dw
        WHEN 5
          THEN d END) Thu
  , min(CASE dw
        WHEN 6
          THEN d END) Fri
  , min(CASE dw
        WHEN 7
          THEN d END) Sat
FROM
  (
    SELECT
        date_format(dt, '%Y%m') ym
      , WEEK(dt)                w
      , Day(dt)                 d
      , DAYOFWEEK(dt)           dw
    FROM
      (
        SELECT concat(y, '0101') + INTERVAL a * 100 + b * 10 + c DAY AS dt
        FROM
          (
            SELECT 0 a
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
          ) aa,
          (
            SELECT 0 b
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) bb,
          (
            SELECT 0 c
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) cc,
          (
            SELECT '2018' y
          ) dd
        WHERE a * 100 + b * 10 + c < DayOfYear(concat(y, '1231'))
      ) a
  ) a
GROUP BY ym, w
;



/**
    MySQL Mk Rownum

*/

select
    @rnum := @rnum + 1 as Rownum
from
    ( select @rnum := -1 ) r

Tibero DB Create

간략하게 티베로 디비 만드는 스크립트 입니다.

# vi /etc/sysctl.conf
kernel.shmmax = 1073741824
kernel.shmall = 2097152
kernel.sem = 10000 32000 10000 10000
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000

* /etc/security/limits.conf 를 수정
# vi /etc/security/limits.conf
tibero soft nproc 2047
tibero hard nproc 16384
tibero soft nofile 1024
tibero hard nofile 65536

seforce 0

groupadd dba
useradd -g dba tibero

export  TB_BASE=/opt/tibero
export  TB_HOME=/opt/tibero/tibero6
export  TB_SID=tibero
export  TB_VER=tibero6
export  TB_PROF_DIR=$TB_HOME/bin/prof
export  JAVA_HOME=/opt/java

export  PATH=$TB_HOME/bin:$TB_HOME/client/bin:$JAVA_HOME/bin:/bin:/usr/bin:/usr/local/bin:.
export  LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$JAVA_HOME/lib:/lib64:/usr/lib64:/usr/local/lib:.

######## TIBERO alias ########
alias tbhome='cd $TB_HOME'
alias tbbin='cd $TB_HOME/bin'
alias tblog='cd $TB_HOME/instance/$TB_SID/log'
alias tbcfg='cd $TB_HOME/config'
alias tbcfgv='vi $TB_HOME/config/$TB_SID.tip'
alias tbcli='cd ${TB_HOME}/client/config'
alias tbcliv='vi ${TB_HOME}/client/config/tbdsn.tbr'

cp license.xml $TB_HOME/license
cd $TB_HOME/bin
sh $TB_HOME/config/get_tip.sh

/*

############################################
### Database Identification
############################################
DB_NAME=TBTAC
LISTENER_PORT=8629

############################################
### File Configuration
############################################
DB_CREATE_FILE_DEST="/tibero/tbdata"
CONTROL_FILES="/dev/mapper/sdiskvg1-lvctl_20m_01vg1","/dev/mapper/sdiskvg2-lvctl_20m_01vg2"

############################################
### Memory & Session
############################################
MAX_SESSION_COUNT=50
TOTAL_SHM_SIZE=768M
MEMORY_TARGET=1536M

DBMS_LOG_TOTAL_SIZE_LIMIT=120M
TRACE_LOG_TOTAL_SIZE_LIMIT=300M
############################################
### Undo and Rollback Segments
############################################
#UNDO_RETENTION=900
UNDO_TABLESPACE="UNDOTBS0"

############################################
### Cache and I/O
############################################
DB_BLOCK_SIZE=8192
#DB_FILE_MULTIBLOCK_READ_COUNT=64

###########################################
### Archive Log Mode
############################################
LOG_ARCHIVE_FORMAT="arcTAC%t_%s_%r.arc"
LOG_ARCHIVE_DEST="/tibero/tibarch"
#_ARCHIVE_LAG_TARGET=21600
#_ARCHIVE_CLOSED_THREAD=Y
#_ARCHIVE_CLOSED_THREAD_DIE_ON_FAIL=N

############################################
### Statistics
############################################
#AUDIT_TRAIL=NONE
#AUDIT_SYS_OPERATIONS=N
#AUDIT_FILE_DEST="/tibero/tiblog/tac/audit"

############################################
### APM Report
############################################
#AUTOMATIC_PERFORMANCE_MONITORING=Y
#APM_SNAPSHOT_SAMPLING_INTERVAL=60
#APM_SNAPSHOT_RETENTION=7
#APM_SNAPSHOT_TOP_SQL_CNT=10

############################################
### Checkpoint
############################################
#_LOG_INC_CHECKPOINT_TIMEOUT=1
#_LOG_INC_CKPT_LAG_LIMIT_PCT=25

############################################
### Log Dest
############################################
#CM_LOG_DEST="/tibero/tiblog/tac/log/cm"
#DBMS_LOG_DEST="/tibero/tiblog/tac/log/dbmslog"
#EVENT_TRACE_DEST="/tibero/tiblog/tac/log/event"
#LSNR_LOG_DEST="/tibero/tiblog/tac/log/lsnr"
#SQL_TRACE_DEST="/tibero/tiblog/tac/log/sqltrace"
#TRACE_DUMP_DEST="/tibero/tiblog/tac/dump/tracedump"
#TRACE_LOG_DEST="/tibero/tiblog/tac/log/tracelog"

############################################
### Cluster Database
############################################
CLUSTER_DATABASE=Y
THREAD=0
LOCAL_CLUSTER_ADDR=10.10.1.101
LOCAL_CLUSTER_PORT=8631
LOCAL_CLUSTER_VIP=192.167.90.111
LOCAL_CLUSTER_NIC=enp0s8
LOCAL_CLUSTER_VIP_NETMASK=255.255.255.0
LOCAL_CLUSTER_VIP_BROADCAST=192.167.90.255

############################################
### TBCM Parameter
############################################
CM_CLUSTER_MODE=ACTIVE_SHARED
CM_FILE_NAME="/dev/mapper/sdiskvg1-lvtbcm_10m_01vg1","/dev/mapper/sdiskvg2-lvtbcm_10m_01vg2"
CM_PORT=8632
CM_HEARTBEAT_EXPIRE=60
CM_WATCHDOG_EXPIRE=50
#CM_NET_EXPIRE_MARGIN=10
CM_AUTO_VIP_FAILBACK=N
CM_ENABLE_CM_DOWN_NOTIFICATION=Y
CM_ENABLE_FAST_NET_ERROR_DETECTION=Y
CM_FENCE=Y
#_CM_ENABLE_VIP_ALIAS_CHECK=N
#_CM_ENABLE_VIP_ALIAS_RETRY=N
#_CM_REBOOT_RETRY_COUNT=3

############################################
### Miscellaneous
############################################
#ACTIVE_SESSION_HISTORY=Y
USE_NET_KEEPALIVE=Y
USE_RECYCLEBIN=Y
NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
#BIND_VARIABLE_CAPTURE=Y
#JOB_HISTORY_LOG=Y

    TBTAC1=(
        (INSTANCE=(HOST=192.167.90.101)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
    )

    TBTAC=(
        (INSTANCE=(HOST=192.167.90.111)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (INSTANCE=(HOST=192.167.90.211)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (LOAD_BALANACE=Y)
        (USE_FAILOVER=Y)
    )

*/

tbboot nomount

tbsql sys/tibero

/*

CREATE DATABASE "tibero"
    USER SYS IDENTIFIED BY TIBERO
MAXDATAFILES 4096
maxinstances 8
CHARACTER SET MSWIN949
LOGFILE GROUP 0 ('redo01a.redo','redo01b.redo') SIZE 1024M,
        GROUP 1 ('redo11a.redo','redo11b.redo') SIZE 1024M,
        GROUP 2 ('redo21a.redo','redo21b.redo') SIZE 1024M,
        GROUP 3 ('redo31a.redo','redo31b.redo') SIZE 1024M,
        GROUP 4 ('redo41a.redo','redo41b.redo') SIZE 1024M
MAXLOGFILES 100
maxloggroups 255
MAXLOGMEMBERS 10
NOARCHIVELOG
DATAFILE 'system001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TABLESPACE USR
DATAFILE 'usr001.dtf' SIZE 128M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'temp001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
UNDO TABLESPACE UNDO
DATAFILE 'undo001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

*/

tbdown normal

tbboot 
sh $TB_HOME/scripts/system.sh -p1 tibero -p2 syscat -a1 y -a2 y -a3 y -a4 y

우리 회사 데이터베이스를 티베로로 변경하기

우승헌 지음 | 영진닷컴 | 2017년 01월 20일 출간 


 서평: 실제 프로젝트를 진행한 담당자가 적은 책이다.
 티베로의 기술력을 간접적으로  알수 있는 좋은 책

PostgreSQL 성능 최적화

 

 

[도서] PostgreSQL 성능 최적화

저자 그레고리 스미스 | 출판사 지앤선

서평:

 번역이 엉망이다. 무슨 번역기 돌린듯한 느낌 그래서 내용에 집중을 못하겠다.

MySQL or MariaDB FederatedX Storage Engine

MySQL 계열에서 DB Link 기능 같은걸 찾아 보다 찾은 Storage Engine 이다.
MySQL 은 Federated Engine 이 기본적으로 탑재 되어 있다. 그리고 X 는 MariaDB에서 사용하는 확장 판이다.
또한 타겟에서 끌고 올때 소스의 버퍼에 올리기 때문에 통으로나 데이터량이 많으면 소스에 스와핑이 생기거나 OOM 이 생길 수 있다.

/**
    MySQL or MariaDB FederatedX Storage Engine 
    ( like dblink of oracle)
*/

--
-- 01. Plugin Install
--

INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

# my.cnf
federated # 이부분 추가

--
-- 02. Configure
--

connection=scheme://username:password@hostname:port/database/tablename
connection=scheme://username@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
ex)
connection=mysql://username:password@hostname:port/database/tablename



onnection="connection_one"
connection="connection_one/table_foo"

ex)
# 사용시 table name change 안됨 
create server 'server_one' foreign data wrapper 'mysql' options
  (HOST '127.0.0.1',
  DATABASE 'db1',
  USER 'root',
  PASSWORD '',
  PORT 3306,
  SOCKET '',
  OWNER 'root');

ex)

CREATE TABLE federatedx.t1 (
  `id` int(20) NOT NULL,
  `name` varchar(64) NOT NULL default ''
  )
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';

# table name change 가능
CREATE TABLE test_table ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';

--
-- 03. explain
-- ( 원격 테이블도 인덱스를 사용할수 있다.)

+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|    1 | SIMPLE      | ox_clients | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

--
-- 99. 작업 절차
--

INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

create database if not exists dblink;

grant all on dblink.* to `root`@`localhost`;

use dblink;

create server 'server_one' foreign data wrapper 'mysql' options
(
    HOST '127.0.0.1',
    DATABASE 'db1',
    USER 'root',
    PASSWORD '',
    PORT 3306,
    SOCKET '',
    OWNER 'root'
);

create table if not exists dblink.tbl_link_01
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';

create table if not exists dblink.tbl_link_02
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root:aaaa@127.0.0.1:9306/federatedx/test_federatedx';

CREATE TABLE dblink.tbl_link_03
(
  id     int(20) NOT NULL auto_increment,
  name   varchar(32) NOT NULL default '',
  other  int(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY name (name),
  KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';

SQL 레벨업 DB 성능 최적화를 위한 SQL 실전 가이드

미크 지음 | 윤인성 옮김 | 한빛미디어 | 2016년 01월 30일 출간 

서평:
  가끔은 그럴때가 있다. 어려운 문제를 어떻게 SQL 로 풀어 낼까 하고 그런 고민에 대한 해답을 주는 책이다. 이책도 일본 엔지니어가 쓴 책인데...