'개발/Database'에 해당되는 글 19건

코로나19 때문에 집에서 한 2주를 쉬니깐(회사에서 나오지 말라고 했음), 게임만 하다가 지쳐서.. 공부를 해야겠다는 욕구가 들어 페이스북을 켰고(?), 좋은 자료를 발견하게 되어, 오랜만에 포스팅을 함.

기존에 MySQL을 운영해본 경험으로 보면, 기본 제공하는 기능만으로 절대 HA(고가용성)을 지원할 수 없는 것이 MySQL 인 것 같다. 

경쟁사 제품인 MS SQL Server와 비교했을 때도, 성능 면에서라던지 여러가지 기능이 부족해보이긴 하지만, 결론적으로 사용하다보면 DBMS 자체가 다 비슷하다고 본다. 유료냐 무료냐의 차이일 뿐.

MySQL은 HA를 지원하지 않지만, Replication은 지원을 하기 때문에, 데이터가 유실될 경우 Replication 구성을 통해 복원도 가능하며, 사용자가 은근히 많아서 지원하는 툴도 많다. 유료버전을 쓰면 Cluster구성도 가능하다고 하는데, 돈주고 굳이 그렇게 쓰는사람이 몇이나 될까?

MySQL을 운영하다보면, Replication만으로 운영을 하는 것은 한계가 있다는 것을 깨닫고, 좀 더 효율적인 분산을 위해 Application 레벨에서의 Sharding도 구현을 해보고, Redis를 앞에 둬서 Cache Aside 패턴으로 DBMS의 부하를 줄이는 방법도 고려를 했다.

다만, 이 방법들은 서비스해야 하는 Application Server가 동시접속자를 10만 ~ 100만정도가 예상될 경우, DBMS에서 감당해야하는 Connection 문제가 생기게 된다.

동시접속자 100만을 받아야한다고 칠 때, Application Server하나가 동시접속자 5천을 커버하고, DBMS 하나가 2만명을 받아야 한다고 쳐보면, Application Server가 200대, DBMS가 50대 필요할 것이고, Application 레벨에서 Connection Pool을 구현해서 최소 30개의 Connection을 유지해야 한다면, DBMS가 감당해야 하는 Connection 수는 상상을 초월하게 될 것이다. (30 * 200 * 50 = 30만!)

이런 이슈를 피하기 위해, 중간에 Middle Ware 개념의 Query Dedicate Server를 만들어서 운영하는 경우도 있긴한데, 구현 및 관리하기가 어렵고, 일반적으로 자체구현한 Middle Ware가 DBMS Native Protocol을 지원하는 것은 쉽지 않으므로, 성능이슈가 생기게 된다.

이런 것들을 해결해줄만한 아래와 같은 솔루션이 있었다는 것을 최근에 알게 되었다.

https://www.proxysql.com/

이 솔루션이 해주는 역할은 명확하다.

1. Application과 DBMS 사이에서 Middle Ware역할을 해주면서, Connection Multiplexing을 제공해준다.

(Connection Multiplexing이란, 출발지에서 요청한 수 많은 커넥션이 있을 때, 그 커넥션의 성격이 비슷한 경우 하나의 커넥션으로 인식하여, 효율적으로 사용할 수 있도록 해주는 기능을 의미한다.)

2. MySQL Native Protocol을 지원하기 때문에, Application에서 MySQL 붙는 것처럼 IP정보만 교체해주면 되고, 그렇기 때문에 성능 이슈가 없을 거라고 생각됨.

3. Query를 Routing할 수 있도록 Admin기능을 제공해준다. 

(ProxySQl이 제공하는 Meta Table에다가 MySQL Host들을 Seq 기반으로 등록하고, 출발지의 Connection의 정보 중 MySQL User와 MySQL DB정보를 읽어서 내가 원하는 MySQL Host로 보낼 수 있도록 제공)

3번 같은 경우는 HA Proxy와 L7을 이용해서도 충분히 만들어낼 수는 있는데, Connection Multiplexing이 안되는 것은 둘째치고, HA Proxy 설정하는 것이 만만치는 않아서, 실 서비스에 사용을 고려하진 못했었다.

좋은 솔루션을 알게 되었으니, 직접 세팅해보면서 이후에 추가로 포스팅해야겠음.

쿼리 결과를 텍스트로 뽑아내는 것 까지는 성공했는데, ANSI 인코딩으로 파일이 만들어지는 바람에,

텍스트에 한글이 포함된 경우 해당 텍스트를 받아야하는 로그 서버에서 인코딩 에러가 발생을 하였다.

utf-8로 인코딩을 위해 sqlcmd의 옵션들을 검토하다가 해당 옵션을 발견함!

sqlcmd -E -o "output할 파일의 절대경로" -Q "실행할 쿼리" -f 65001 -h-1 -W

[옵션에 대한 설명]

-o : output할 파일명 (가급적 절대경로)

-Q : 실행할 쿼리 (SP도 가능함)

-f 65001 : 이것이 파일로 output하면서 ANSI를 utf-8로 변환해주는 옵션이다.

-h-1 : sqlcmd로 쿼리 결과를 파일로 뽑아낼 경우 상단에 ----------------- 가 붙는다. 이걸 제거해주는 역할

-W : sqlcmd로 쿼리 결과를 파일로 뽑는 경우 어마어마하게 공백이 추가되어 쓸데 없이 byte수가 늘어난다. 공백을 제거해주는 역할을 한다.

모니터링이나 자동화 등 mysql client를 통해 command line 환경에서 값을 얻어와 활용하는 경우, 한 번의 커맨드로 끝내기 위해 password까지 포함해서 이용하게 된다.

이런 상황에서 아래와 같이 패스워드가 command line에 노출됬으니 주의하라는 메시지가 나오며, 결과값이 출력되게 된다.

$ mysql -uroot -pabc123 -e "show variables like 'innodb_%'"
Warning: Using a password on the command line interface can be insecure. 
...

쉘 스크립트나 Zabbix 등에서는 원하는 값을 integer나 string형태로 얻어와서 변수에 담아야 하기 때문에, 경고 메시지가 매우 껄끄러울 수 있으며, 오작동의 원인이 될 수 있다.

그래서 mysql 5.6.6 버전 이후부터는 mysql_config_editor라는 tool을 제공하고 있고, id, password, host 정보를 alias로 묶어서 관리할 수 있도록 하고 있다.

여기서는 등록 및 삭제, mysql 커맨드에서의 이용방법에 대해서만 알아보겠다.

[등록]

$ mysql_config_editor set --login-path=root_login --host=localhost --user=root --password=rootpassword

[삭제]

$ mysql_config_editor remove --login-path=root_login

[활용]

$ mysql --login-path=root_login -e "show variables like 'innodb_%'"

거슬렸던 Warning 메시지가 사라지는 것을 확인할 수 있다.

MySQL에서는 가급적이면 SP를 사용하지 않아서, 잘 모르고 있던 부분인데, 개발사쪽에서 요청이 들어와서 알아보게 되었다.

SP 관련 권한들은 Routine이라는 이름을 가지고 있었고, 생성 / 수정 권한 정도가 있다.

실제 권한 부여는 아래와 같은 커맨드로 가능하다.

mysql> GRANT CREATE, ALTER ROUTINE ON DB명.* TO '계정명';
mysql> FLUSH PRIVILEGES;

DB명 뒤에 * 는 테이블명이긴 한데, SP 권한을 주면서 굳이 테이블까지 제한을 해야되나 싶기도 하고 귀찮기도해서, 보통 모든 권한을 넣어주게 되는 것 같다.

mysql.proc 테이블에 CRUD 권한을 넣어주어도 비슷하게 동작을 하는 것 같지만, 시스템 관련 테이블을 직접 손대는 것 보다는 공식적으로 제공하는 GRANT 커맨드를 권장한다.

패스워드를 강제로 초기화 한 후, 아래와 같은 에러가 발생하며, 아무런 쿼리가 날라가지 않는 경우가 있다.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

이런 경우, 아래 명령어로 해결이 가능함.

mysql> set password = PASSWORD('강제로 변경한 패스워드');

ipv6 주소를 number형태로 바꾸면, int, long 범위를 훌쩍 넘어가버리는 38자리의 수가 반환이 된다.

java코드에서는 BigDecimal로 세팅을 하여, 핸들링이 가능한데, Mysql에서는 BigDecimal이라는 게 없고,

Decimal 이라는 Type이 존재를 한다.

아래와 같이 Decimal Type의 컬럼을 만들어서 사용을 하면 된다.

Decimal (길이, 소수점 자리 수) 형태로 만들어주어야 한다.

CREATE TABLE `test` (
  `test_decimal` decimal(64,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

A DB를 사용하고 있는데 전혀 다른 곳에 있는 B DB에 있는 테이블을 참조하고 싶을 때 사용한다.

CREATE [PUBLIC] SYNONYM A.SYNONYM명 FOR B.테이블명;

이렇게 SYNONYM을 생성하고 나면 위에서 명시한 B DB의 테이블에 있는 데이터를

A.SYNONYM명을 통해 가져올 수 있다.

A.SYNONYM은 일반 테이블처럼 사용할 수 있다.

SELECT *
FROM A.SYNONYM명

위의 쿼리를 날리면

SELECT *
FROM B.테이블명

B테이블의 데이터를 가져올 수 있다는 의미이다.

저희 사내 DB중에 mysql을 sharding 시켜서 이용하는 DB가 있습니다. 

하지만, 단점이 join을 사용하였을 경우, 쿼리 속도가 안드로메다가 된다는 점인데요.

다른 부서로 가신 분께 인수인계를 받은 소스 중에 DB가 sharding되어 있다는 점을 전혀 고려하지 않고,

개발이 되어 있던 쿼리가 있어서, 초반에 고객들이 별로 없었을 때는 문제가 없었지만,

고객이 많아지니까 쿼리 속도가 15초가 나오더군요-_-

그래서 일단 join을 제거하고, 소스 상에서 처리를 하였는데요.

여전히 느리더군요..

또 다시 문제를 파악하던 도중 문제를 발견했는데,

근본적인 문제는 특정 컬럼을 기준으로 order by 하는 데 index가 걸려있지 않아서 느리던 현상이었습니다.

따라서, DBA분께 요청해서 index를 생성하는 방향으로 해결하기로 했었습니다.

하지만, index를 생성하였는데도 속도가 그대로군요.

해당 쿼리가 어떤 index를 사용하고 있는지, explain명령어를 통해서 보았는데..

다른 index를 사용하고있더군요-_-..

그래서 강제로 방금 생성한 index를 사용하도록 쿼리를 수정하였더니 0.1초도 안걸렸습니다.

아래와 같이 사용하시면 됩니다.

SELECT *
FROM A use index {인덱스명}

반대로 인덱스를 사용하고 싶지 않은 경우

SELECT *
FROM A ignore index {인덱스명}

한 번 만들다가 실패한 이후에, 다시 만들려고하면

만들어지지도 않은 테이블이 이미 존재한다는 에러가 나온다. (아주 드물게...) 

이런 경우 ${MariaDBPath}/data/DB명 폴더에 가서, 테이블명.ibd파일을 지워버리고

다시 시도하면 만들어진다.

버그라고 한다.. 

프로시저도 비슷한 현상이 있던데..

그때 상황에서 급해서 그냥 DB명을 바꿔버려서, 해결을 못했던 적이 있다.

프로시저 같은 경우는 테이블처럼 파일도 따로 안남는 것 같던데...

이건 나중에 발생하면 찾아보는걸로.. 

테이블만 캐릭터셋이 바뀐다고 다 바뀌는건 아니었고,

컬럼에 대한 캐릭터셋을 바꾸니 정상으로 동작한다.

mysql은 컬럼별로 캐릭터셋 설정이 가능하다는걸 오늘 첨 알게 되었다--;; 

위에 있는 글 없었으면 하루종일 삽질할뻔;;;