꿩이야기

Rocky Linux 8.9에서 Oracle express 설치 내용 (virtual box에 설치)

enterprise 설치하고 싶었지만... 라이센스 무섭..

yum -y update

systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld

# Oracle 설치
cd /usr/local/src
curl -o oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
yum -y localinstall oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm
rm oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm

cd /usr/local/src
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
#rpm -ivh oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm 
yum -y localinstall oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

# 설정 완료되면 자동 실행됨
# 비밀번호 설정
/etc/init.d/oracle-xe-21c configure

# 확인
ps -ef | grep oracle


# 환경변수 설정 - 맨 아래 입력 후 저장
# vi /etc/profile

export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
export ORACLE_SID=XE
export PATH=$ORACLE_HOME/bin:$PATH


source /etc/profile

# sqlplus 환경설정
yum install -y epel-release
yum install -y rlwrap

# vi ~/.bashrc

alias sqlplus='rlwrap sqlplus'



source ~/.bashrc

# 재부팅
reboot

# 오라클 실행
/etc/init.d/oracle-xe-21c start

# sqlplus 접속 - system 계정으로 접속
sqlplus

SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> create user gwchu identified by 12345;
SQL> grant resource, connect, dba to gwchu;

### dbeaver 접속
# 다음의 설정 추가
# vi /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora

testdb = 
  (DESCRIPTION =
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.116)(PORT = 1521))
    )
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )


# 오라클 재시작
/etc/init.d/oracle-xe-21c restart

# DBeaver 접속확인

# 파일 용량 크므로 삭제
rm -f /usr/local/src/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

위처럼 설정한 후 연결 잘 되면 끝~

# my.cnf 메모리 설정

MariaDB의 메모리 공간은 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다

글로벌 메모리 영역은 스레드가 공유해서 사용되는 공간이다

DB 최초 기동시 메모리를 최소한만 사용하다가 설정된 값까지 증가되고

증가한 이후 메모리룰 반환하지 않고 설정된 값 이내에서 사용된다

글로벌 메모리 = innodb_buffer_pool_size + key_buffer_size + innodb_log_buffer_size + tmp_table_size

로컬 메모리 영역은 각 스레드별로 사용되며 공유되지 않는 공간이다

로컬 메모리 = (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + thread_stack + binlog_cache_size) * max_connections

총 메모리 = 글로벌 메모리 영역 + 로컬 메모리 영역 + Mariadb 기본 기동(350MB) + performance_schema data(150MB) + OS / 파일 버퍼링 공간(전체 메모리의 약 10%) 를 고려할 것

[참고자료]
https://www.webcomand.com/docs/admin_guide/configuration/mysqlmariadb/index.html
https://support.skdt.co.kr/ko/support/solutions/articles/42000064656--cloud-z-db-mariadb%EC%9D%98-%EB%A9%94%EB%AA%A8%EB%A6%AC-%EC%84%A4%EC%A0%95%EC%9D%84-%EC%96%B4%EB%96%BB%EA%B2%8C-%ED%95%B4%EC%95%BC-%ED%95%98%EB%82%98%EC%9A%94-
MariaDB 공식문서

To be continued.........

Made by 꿩

# PostgreSQL Architecture

PostgreSQL = 프로세스 기반의 DBMS

PG는 1개의 connection마다 1개의 backend 프로세스가 생성이 된다.

PostgreSQL의 프로세스 리스트를 보면 다음과 같다.

# ps -ef | grep postgres

postgres 11126     1  0  2021 ?        00:00:55 /home/postgresql/bin/postgres -D /home/postgresql/data
postgres 11129 11126  0  2021 ?        00:00:00 postgres: checkpointer 
postgres 11130 11126  0  2021 ?        00:07:01 postgres: background writer 
postgres 11131 11126  0  2021 ?        00:07:20 postgres: walwriter 
postgres 11132 11126  0  2021 ?        00:00:48 postgres: autovacuum launcher 
postgres 11133 11126  0  2021 ?        00:01:49 postgres: stats collector 
postgres 11134 11126  0  2021 ?        00:00:01 postgres: logical replication launcher 
postgres 12671 11126  0 11:03 ?        00:00:00 postgres: postgres postgres [local] idle
root     11245 11198  0 11:02 pts/0    00:00:00 grep --color=auto postgres

여기서 pid가 11126인 프로세스가 바로 Postmaster 프로세스이다.

PostgreSQL를 구동할 때 가장 먼저 프로세스이다

초기 복구 작업, Shared Memory 초기화, Background 프로세스 구동작업을 수행한다.

만약, 당신이 PG에 접속하고 싶은 경우, 이 프로세스가 Backend 프로세스를 생성해 줄 것이다.

 Backend 프로세스는 여러개가 있다.

checkpointer는 체크 포인트 발생시 dirty 버퍼를 데이터파일에 기록하고,

background writer는 주기적으로 dirty 버퍼를 데이터파일에 기록한다.

wal writer는 데이터 파일의 변경 사항을 로그파일로 기록하는데,

이 wal 파일은 데이터베이스에 대한 모든 조작 기록을 보관하고 있다.

이 파일의 존재 이유는 서버가 갑자기 중지되었을 경우

데이터 파일에 적용하지 못한 작업(checkpoint 작업이 안된)을

이 로그에 읽어서 다시 실행하여 서버에 안전하게 복구하기 위해서이다.

wal 파일을 특정 시점까지만 실행하면, 특정 시점 복구도 할 수 있다.

autovacuum launcher는 자동으로 vacuum 하는 프로세스이며

stats collector는 쿼리 최적화를 위해 통계 정보를 수집하는 프로세스이다.

logical replication launcher는 subscriber의 위치에서 테이블을 싱크해주는 프로세스이고

pid 12671인 프로세스는 local에서 누군가가 pg에 접속해 있다는 말이다.

PostgreSQL의 메모리 사용

PG의 메모리 사용 부분을 보면 Shared Memory 영역이 있다.

그 중 Shared Buffer는 사용자가 요청한 데이터 블록을 저장하는 공간이며 공유 메모리 버퍼이다.

 많은 사용자가 동시에 접근할 때 경합을 최소화하고

자주 사용하는 블록이 최대한 오랫동안 버퍼 내에 있는 영역이다.

결국 Shared Buffer의 목적은 디스크 I/O를 최소화 하는 것이다.

WAL Buffer는 데이터의 변경 사항을 잠시 저장하는 버퍼로

WAL writer 프로세스를 통해 WAL 파일에 기록된다.

PostgreSQL의 데이터 구조

각 DBMS별로 데이터 저장 구조는 다 다르다.

PG에서는 데이터베이스 > 스키마 > 테이블의 형태로 데이터가 분류되며

다음의 그림처럼 데이터가 저장이 된다.

데이터가 저장되는 파일들은 여러 개의 페이지들로 구성되며

하나의 페이지는 일반적으로 8KB를 차지한다.

페이지의 구성요소는 다음과 같다.

Page Header는 24bytes로 기본적인 페이지 정보를 저장하며

Item은 4 bytes로 데이터 시작위치, 크기가 저장된 포인터이다.

Tuple = 데이터 row 한 줄

PG는 MVCC 동시성 제어를 위해 MGA 방식을 사용하는데

만약 update나 delete가 발생할 경우

위의 그림에서 Tuple은 삭제되는게 아니라 더이상 사용하지 않도록 표시가 된다.

추후, VACUUM 작업을 해줘야 해당 Tuple들이 삭제될 것이다.

즉, Update와 Delete가 빈번하게 일어나면

테이블의 크기가 점점 늘어나게 될 것이다.

[참고자료]

https://waspro.tistory.com/146

https://d2.naver.com/helloworld/227936

https://mozi.tistory.com/565

https://dbrang.tistory.com/1579

To be continued.........

Made by 꿩

# mariabackup 백업 & 복구

내가 하는 업무가 다양해서 일관성이 없다 보니

예전에 했던 일들을 자꾸 까먹어서

블로그에 써놓으면 좀 낫지 않을까?란 생각으로 쓰는 포스트이다.

우선 백업부터 하자

# 풀백업
mariabackup \
--backup \
--defaults-file={mariadb config 경로} \
--target-dir={백업파일 저장 경로} \
--user=root  \
--password='...' \
--no-lock 

# 증분백업
mariabackup \
--backup \
--defaults-file={mariadb config 경로} \
--target-dir={백업파일 저장 경로} \
--incremental-basedir={이전 풀/증분백업 LSN 저장 경로} \
--user=root  \
--password='...'

# prepare
mariabackup \
--prepare \
--target-dir={백업파일 저장 경로} \
--user=root \
--password='...'

--backup 백업이므로 이 옵션은 필수!

--defaults-file에는 mariadb config 파일 경로(my.cnf)를 명시한다.

--target-dir에는 백업파일 저장 경로를 명시한다.

--incremental-basdir에는 증분백업을 위한 이전 백업 파일의 저장 경로를 명시한다.

그리고 백업 도중 변경사항이 발생할 수 있다.

복원 전, --prepare 옵션을 통해서 백업 중 발생한 변경사항(redo로그)를 데이터 파일에 반영한다. 

prepare를 하게 되면 xtrabackup_checkpoints 파일의 backup_type이 

full-backuped -> log-applied로 변경된다.

백업 명령어를 실행하면 다음처럼 백업파일이 생성된다.

drwxr-xr-x 6 root root 4.0K 2022-02-11 18:15 .
drwx------ 4 root root 4.0K 2022-02-11 18:11 ..
-rw-r----- 1 root root  24K 2022-02-11 18:15 aria_log.00000001
-rw-r----- 1 root root   52 2022-02-11 18:15 aria_log_control
-rw-r----- 1 root root  326 2022-02-11 18:15 backup-my.cnf
-rw-r----- 1 root root  972 2022-02-11 18:15 ib_buffer_pool
-rw-r----- 1 root root  12M 2022-02-11 18:15 ibdata1
-rw-r----- 1 root root 2.5K 2022-02-11 18:15 ib_logfile0
drwx------ 2 root root 4.0K 2022-02-11 18:15 mysql
drwx------ 2 root root 4.0K 2022-02-11 18:15 performance_schema
drwx------ 2 root root 4.0K 2022-02-11 18:15 test
-rw-r----- 1 root root   25 2022-02-11 18:15 xtrabackup_binlog_info
-rw-r----- 1 root root   73 2022-02-11 18:15 xtrabackup_checkpoints
-rw-r----- 1 root root  553 2022-02-11 18:15 xtrabackup_info
drwx------ 2 root root 4.0K 2022-02-11 18:15 whatisyourname

여기서 xtrabackup_info 파일이 있다.

백업과 관련된 정보를 저장한 파일인데

복원의 목적이 slave서버를 구축하는 것이라면

binlog_pos 정보를 통해서 replication 설정을 하면 된다.

# cat xtrabackup_info 
uuid = 3879b27e-8b1b-11ec-9fd1-6c2b59b09b40
name = 
tool_name = mariabackup
tool_command = --defaults-file=... --user=root --backup --target-dir=...
tool_version = 10.4.11-MariaDB
ibbackup_version = 10.4.11-MariaDB
server_version = 10.4.11-MariaDB-log
start_time = 2022-02-11 18:15:54
end_time = 2022-02-11 18:15:56
lock_time = 0
binlog_pos = filename 'db-bin.000001', position '1766', GTID of the last change '0-1-7'
innodb_from_lsn = 0
innodb_to_lsn = 71865
partial = N
incremental = N
format = file
compressed = N

복원 명령어는 --copy-back 옵션을 사용하면 된다.

mariabackup \
--copy-back \
--target-dir={백업파일 저장 경로} \
--datadir={복원 data 디렉토리 경로} \
--user root \
--password '...'

data 디렉토리는 다른 이름으로 변경하여 놓자.

덮어쓰는 옵션은 있지만 미래는 모르는 일...

DB는 보수적으로...

난 나를 믿을 수 없다... ㅜㅡㅜ

[참고자료]

https://brush-describr.tistory.com/8

https://techblog.woowahan.com/2576/

https://m.blog.naver.com/anjae83/221749783199

https://semode.tistory.com/335

To be continued.........

Made by 꿩

MySQL -> MariaDB로 마이그레이션 하는 중

계정정보를 따로 추출하는 걸 발견해서

따로 기록해둔다.

나중에 유용할듯?!

mysql -u유저 -p`비밀번호` -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do
    mysql -u유저 -p`비밀번호` -e"$user" > user_grant.txt
    sed '1d' user_grant.txt >> user_privileges.txt
    echo "flush privileges" >> user_privileges.txt
done <user.txt
awk '{print $0";"}' user_privileges.txt >user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

발견지: https://stackoverflow.com/questions/23519797/how-to-export-import-existing-user-with-its-privileges?noredirect=1&lq=1 

To be continued.........

Made by 꿩

# Transaction과 Isolation Level

Transaction(트랜잭션)이란? 논리적인 작업의 최소 단위이다.

트랜잭션이 왜 필요할까?

하나의 작업이 있는데 여러번 연산을 해야하는 상황이 있다.

예를 들어, 친구에게 계좌이체를 한다고 해보자.

계좌이체라는 하나의 작업은 2가지 연산으로 구성된다.

1. 내 계좌에서 돈을 출금하는 UPDATE하는 연산

2. 친구 계좌에 돈을 입금하는 UPDATE하는 연산

만약 1번 작업은 수행됐는데 2번 작업에서 에러가 나면

내 계좌에서 돈이 빠져나갔는데

친구는 돈을 못받은 상황이 되는 것이다.

즉, 트랜잭션은 하나의 작업을 수행하는데

여러 개의 연산을 하나로 묶어서

전부 실행되야지 일부만 실행되면 안된다. "All or Nothing - 원자성"

트랜잭션이 성공적으로 수행이 되면

내 계좌에서 빠져나간 돈만큼의 돈이

친구 계좌에 들어온다. 즉, 일관성을 만족해야 하는 것이다.

내가 돈을 송금하는 동안 다른 친구도 그 친구에게 돈을 송금할 수 있다.

계좌이체 트랜잭션이 동시에 들어오는 경우

다른 트랜잭션이 영향을 줄 수 없게

트랜잭션의 격리성이 보장되야 한다.

트랜잭션이 반영된 후에도 시스템 장애가 발생하더라도

트랜잭션 작업 결과는 손실되지 않고 영구적으로 반영되야한다.

이를 지속성이라 한다.

정리하자면
트랜잭션은 ACID라는 4가지 특성을 만족해야 한다.
(Atomicity-원자성, Consistency-일관성, Isolation-격리성, Durability-지속성)

이 중에서 Isolation에 대해 더

자세히 알아보려고 한다.

DB에는 Isolation Level(트랜잭션 격리 수준)이라는 게 있다.

이는 하나의 트랜잭션이 조회 또는 변경 작업 중인데

다른 트랜잭션이 들어온 경우

어느 수준까지 데이터를 조회를 허용할지에 대해서 설정하는 것이다.

SQL 표준 Transaction Isolation Level에는 4가지가 있다.

1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable

1. Read Uncommitted는 커밋되지 않은 데이터도 읽을 수 있는 수준이다.

Select를 수행하는 경우 트랜잭션이 COMMIT 되지 않은 데이터를 읽을 수 있다(dirty read)

업데이트되지 않은 데이터를 읽는 것은 무결성을 깨뜨릴 위험성이 높다.

하지만 동시성이 향상되는 효과가 있기도 하다.

Oracle, PostgreSQL에서는 지원하지 않는다.

2. Read Committed는 커밋된 데이터만 읽을 수 있다.

Select문은 이전에 커밋된 데이터만 읽을 수 있고

Write 작업은 해당 트랜잭션이 끝날 때까지 waiting하게 된다.

트랜잭션에서 select가 여러번 있을때, 다른 트랜잭션에서 커밋을 하면

커밋 후의 select는 커밋된 데이터를 참조하게 된다.(non-repeatable read)

3. Repeatable Read는 트랜잭션 시작 시점 데이터만 읽을 수 있다.

동일 데이터 Write 작업은 Read Committed처럼 Waiting이 되고

이미 트랜잭션이 시작됐는데 다른 트랜잭션에서 데이터가 변경된 경우

해당 데이터를 Select할 때 변경이전의 트랜잭션 시작 지점의 데이터를 읽게 된다.

4. Serializable은 모든 읽기 작업은 shared lock을 가진다.

즉, 다른 트랜잭션은 해당 데이터를 변경하지 못한다.

만약 트랜잭션마다 consistency level이 다르면 어떻게 동작할까?

각 트랜잭션 특성을 생각하면 된다.

Repeatable Read 트랜잭션과 Serializable 트랜잭션이 있다고 가정하면

Serializable은 기본적으로 select만으로도 shared lock이 걸리므로

Repeatable Read 트랜잭션은 해당 데이터를 읽을 수 없다.

DB는 동시에 여러 client가 사용하게 되므로

성능면에서 동시성이 중요하고

데이터를 생각한다면 안정성이 중요하다.

그러나 이 두개를 모두 만족할 수는 없다.

서비스의 특성을 잘 파악하고

최적의 isolation level를 선택해서 잘 활용해야 할 것이다.

참고로 테스트는 MariaDB 10.2.11버전을 사용하였다.

[참고문서]
데이터베이스 개론(김연희 지음)
jupiny.com/2018/11/30/mysql-transaction-isolation-levels/
hyunki1019.tistory.com/111
blogs.oracle.com/oraclemagazine/on-transaction-isolation-levels
effectivesquid.tistory.com/entry/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-Isolation-Level

To be continued.........

Made by 꿩

# MVCC 동시성 제어

데이터베이스에는 많은 사용자들이 동시에 접근할 수 있다.

여러 사용자가 데이터를 변경할 수 있고

그와 동시에 데이터를 읽을 수도 있다.

그렇다면 데이터베이스는 어떻게 동시 접근을 처리할까?

MVCC는 Multi Version Concurreny Control의 약자로

Lock을 사용하지 않고 데이터 읽기의 일관성을 보장해주는 방법이다.

데이터에 접근하는 사용자는 접근한 시점의 데이터베이스의 스냅샷을 읽는다.

한 사용자가 데이터를 변경을 할 경우

데이터 변경이 완료(트랜잭션 commit)되기 전까지 변경사항을 볼 수 없다.

MVCC는 2가지의 구현방식이 있다.

1. MGA(Multi Generation Architecture)

MGA는 PostgreSQL에서 사용하는 방식이다.

어떤 데이터에 업데이트가 일어나면

기존 데이터는 그대로 두고 새로운 데이터가 추가된다.

그리고 기존 데이터에 표시가 된다.

MGA방식은 기존 데이터가 지워지지 않는다는 특징을 갖고 있다.

그래서 PostgreSQL는 주기적으로 VACUUM을 해줘야 하며

VACUUM을 하지 않을 경우, 실제 데이터보다 데이터 용량이 훨씬 큰 것을 발견할 수 있다.

또한 업데이트가 발생한 데이터의 물리적 위치가 변경이 되므로

업데이트를 할때마다 인덱스 수정작업이 항상 일어날 수 밖에 없다.

2. Rollback Segment 

Rollback Segment는 Oracle에서 사용하는 방식이다.

업데이트가 실행되면 기존 데이터 블록을 새로운 데이터로 변경하고

이전 데이터는 Rollback Segment에 보관된다.

데이터 변경 중, Select가 수행되면

Select쿼리는 SCN(System Commit Number)라는 고유한 번호를 가지고

데이터파일의 SCN을 비교한다.

이때, Select 쿼리의 SCN 번호보다 작은 데이터 파일의 SCN만 읽는다.

데이터가 변경되면 SCN이 변경되기 때문에

해당 데이터 파일은 변경 중 혹은 변경된 파일로 인식하고

Rollback Segment에서 이전 버전의 데이터 파일을 찾아 읽게 된다.

MGA와의 차이점은 업데이트 시 데이터의 물리적인 위치가 변경되지 않는다는 것이다.

즉, 업데이트 할때마다 인덱스 파일을 수정할 필요가 없고

Replication을 수행할 때 PK를 필수로 걸어 데이터를 판단할 필요가 없다.

물리적 위치를 사용하면 되기 때문이다.

게다가 이전 데이터가 보관되는 Rollback Segment에서 주기적으로 이전 데이터들을 지우기 때문에

PostgreSQL와 달리 VACCUM 작업을 할 필요가 없다.

[참고문서]
www.datanet.co.kr/news/articleView.html?idxno=116534
mysqldba.tistory.com/335
mangkyu.tistory.com/53

To be continued.........

Made by 꿩