[Elasticsearch] Similar image search by image or natural language(2)

Similar image search by image or natural language(1)에서는 이미지 간의 유사도를 측정해서 유사한 이미지를 찾도록 하였다. 이번 포스트에서는 자연어를 이용해서 자연어에서 설명하는 이미지와 유사한 이미지를 찾는 방법에 대해서 알아보겠다.

CLIP model은 Text로 들어오는 자연어를 Transformer 아키텍처를 사용해서 처리한다. 입력된 Text는 Tokenize되어 Embeding 된 후에 여러 개 층으로 구성된 Transformer 인코더를 통해 처리됩니다. 처리된 자연어는 Vector값으로 반환 되고, 자연어의 의미와 문맥이 어떤 이미지를 의미 하는 지를 512 차원의 Vector 값으로 나타내게 됩니다.

CLIP model process

이제, CLIP을 사용해서 검색에 사용할 자연어를 vector embedding해보자.
(이전 포스트에서 이미지를 vector embedding 하던 코드를 조금 변형해서 사용하면 된다.)

from sentence_transformers import SentenceTransformer
from PIL import Image
 
# 영어 모델 clip-ViT-B-32
# 다국어 모델 clip-ViT-B-32-multilingual-v1
img_model = SentenceTransformer('clip-ViT-B-32-multilingual-v1', device='cpu')
 
def vectorize(text):
    embedding = img_model.encode(text) 
    return embedding.tolist()
 
vectorize('빨간 사과')

위 코드를 실행해보면 텍스트가 vector embedding되어 512 차원의 vector를 반환하는 것을 확인 할 수 있다. 반환 받은 vector 값을 이용하여서 이미지 검색을 해보자.

GET image_vector/_search
{
  "knn": {
    "field": "vector",
    "k": 5,
    "num_candidates": 10,
    "query_vector": [
      -0.10659506916999817,
      0.20013009011745453,
      0.15304496884346008,
      0.03850187361240387,
      -0.126994326710701,
     ... 생략 ...
      -0.04996727779507637
    ]
  },
  "fields": [
    "image_name"
  ],
  "_source": false
}

결과를 보면 빨간 사과라는 자연어 Text에서 “빨간”“사과”의 의미를 잘 파악해서 이미지를 잘 찾아 낸 것을 볼 수 있다.

[Elasticsearch] Similar image search by image or natural language(1)

Elasticsearch(이하 ES)에서 자연어나 이미지로 유사한 이미지를 검색하는 방법에 대해서 알아보자.

유사한 이미지를 찾기 위해서는 먼저 이미지들 간의 유사도를 측정할 방법이 필요하고, 유사도를 측정하기 위해서 Vector를 사용할 수 있다. 이미지를 Embedding을 통해서 Vector로 변환하고, Vector 간의 유사도(Cosine similarity)를 계산해서 유사한 이미지를 찾을 수 있다.

그러면, 이미지를 Vector embedding하는 방법은 무엇이 있을까?

이미지를 vector embedding하는 방법과 이를 만들어주는 ML 모델들은 이미 많이 공개 되어 있다. ResNet, VGGNet, Inception 등 많은 모델들이 있다. 모델들의 성능을 잘 비교해서 알맞은 모델을 사용하면 된다.

여기에서는 OpenAI에서 공개한 CLIP model를 사용할 것이다.

CLIP을 사용해서 검색할 이미지들을 vector embedding해보자.
(사용할 이미지가 없다면 아래 첨부된 이미지를 사용해보자.)

from sentence_transformers import SentenceTransformer
from PIL import Image

img_model = SentenceTransformer('clip-ViT-B-32', device='cpu')

def vectorize(file_path):
    image = Image.open(file_path)
    embedding = img_model.encode(image)
    del image

    return embedding.tolist()

vectorize('./image/apple.jpg')

위 코드를 실행해보면 이미지가 vector embedding되어 512 차원의 vector를 반환하는 것을 확인 할 수 있다. 검색 대상의 이미지들을 모두 embedding 하면 된다.

vector embedding된 값들을 생성했다면, 이제 vector들을 ES에 올려서 유사 이미지 검색을 해보자.

먼저, ES에서 Vector를 담을 index를 생성한다.

PUT image_vector
{
  "mappings": {
    "properties": {
      "image_name": {
        "type": "keyword"
      },
      "vector": {
        "type": "dense_vector",
        "dims": 512,
        "index": true,
        "similarity": "cosine"
      }
    }
  }
}

생성한 인덱스에 만든 Vector 값들을 넣어준다.

from elasticsearch import Elasticsearch

def index(image_name, vector):
    es = Elasticsearch(cloud_id='elastic_cloud_id',
                       basic_auth=('elastic', 'elastic_secret'))
    doc = {     
        'image_name': image_name,
        'vector': vector
    }

    es.index(index='image_vector', body=doc)

index('apple.jpg', vector)

여기까지 하면 유사한 이미지를 검색하기 위한 준비는 끝난다.

원하는 이미지를 하나 골라서 검색을 해보자

GET image_vector/_search
{
  "knn": {
    "field": "vector",
    "k": 5,
    "num_candidates": 10,
    "query_vector": [
      0.5978590846061707,
      0.5926558375358582,
      -0.1243332028388977,
      0.11083680391311646,
      -0.6588778495788574,
     ... 생략 ...
      0.37319913506507874
    ]
  },
  "fields": [
    "image_name"
  ],
  "_source": false
}

왼쪽 끝의 사과 이미지와 위 예제 파일안에서 유사한 이미지 4개를 찾은 것을 확인 할 수 있다.

Next: Similar image search by image or natural language(2)

TLS certificate-based plain text encryption/decryption

TLS 인증서를 사용하여 RSA 알고리즘을 이용한 공개키/개인키 기반의 데이터 암호화 및 복호화를 할 수 있습니다.

먼저, 데이터 암호화를 위해서 TLS 인증서의 공개키를 가져오는 작업을 수행 합니다.

using System.Security.Cryptography.X509Certificates;
using System.Security.Cryptography;
using System.Text;

var certificate = new X509Certificate2(@"인증서 경로", "비밀번호");
var publicKey = certificate.GetRSAPublicKey();

가져온 공개키를 이용해서 데이터를 암호화 합니다.
(예제에서는 plain text를 암호화하는 작업을 수행합니다.)

var plainText = "Hello, world!";

var plainBytes = Encoding.UTF8.GetBytes(plainText);
var encryptedBytes = publicKey.Encrypt(plainBytes, RSAEncryptionPadding.OaepSHA256);
var encryptedText = Convert.ToBase64String(encryptedBytes);

Console.WriteLine(encryptedText);
//7ZfpMeUDxHkn2pCe8bMEGpmNzk3gYMHEzh5ZtH4wG5n8MEmnz4lj5CwmhNoyKdwj6mPnKVmVJBCLGbQD4edqa61lrSJ47U8W/3e6vJ5TE87e5s9o5Z4sBCdCu75uLenel5VZQllm70nyqR/9CZYt1PszArDj9KUeIJQcL00WH7ZR/dVPTPVYU6/zyFGwnNosF5tWpqjhTOoCEFeq9JL2OdeN+A==

다음으로, 암호화된 문자열을 개인키로 복호화하는 작업을 수행합니다.
TLS 인증서에서 개인키를 가져옵니다.

var privateKey = certificate.GetRSAPrivateKey();

가져온 개인키를 이요해서 암호화된 데이터를 복호화 합니다.

encryptedBytes = Convert.FromBase64String(encryptedText);

var decryptedBytes = privateKey.Decrypt(encryptedBytes, RSAEncryptionPadding.OaepSHA256);
var decryptedText = Encoding.UTF8.GetString(decryptedBytes);

Console.WriteLine(decryptedText);
//Hello, world!"

이렇게 TLS 인증서를 이용하여 RSA 알고리즘을 이용한 공개키/개인키 기반의 암호화 및 복호화를 수행할 수 있습니다.

[Python] HEX to RGB, RGB to HEX

색상을 표현하는 방법 중에는 RGB와 HEX 있다. 두 가지 방법 모두 Red, Green, Blue 3가지 값을 가지고 색상을 표현한다.

HEX의 경우 ‘#RRGGBB’ 형식으로 표현 되며 각 위치에 RR(Red), GG(Green), BB(Blue)를 00부터 FF 까지의 16진수로 색을 표현한다.
(Ex) Red: #ff0000, Green: #00ff00, Blue: #0000ff

RGB의 경우 (Red, Green, Blue) 3개 채널 형식으로 표현되며 각 위치에 0부터 255까지의 숫자로 색상을 표현 한다.
(Ex) Red: (255, 0, 0), Green: (0, 255, 0), Blue: (0, 0, 255)

모두 Red, Green, Blue 3개의 색상을 조합하는 형태이며 각 자리의 값을 원하는 형식으로 변경해주면 HEX값을 RGB로, RGB값을 HEX로 전환할 수 있다.

  • HEX to RGB
HEX = '#ff0000' #Red
sixCodes = HEX.lstrip('#')
RGB = tuple(int(sixCodes[i:i+2], 16) for i in (0, 2, 4))
print(RGB) #(255, 0, 0)
  • RGB to HEX
RGB = (255,0,0) #Red
sixCodes = '%02x%02x%02x' % RGB
HEX = '#' + sixCodes
print(HEX) #'#ff0000'

[Logstash on K8S] Azure SQL to ElasticSearch

Azure SQL(이하 sql)의 data를 ElasticSearch(이하 es)로 검색을 하기 위해서 sql 데이터를 es의 index로 옮겨야 한다. 데이터를 옮기는 방법은 여러가지 방법이 있지만 일반적으로 많이 쓰이는 데이터 처리 오픈소스인 Logstash를 사용하여 옮기는 방법을 알아보자.

 Logstash를 운영하는 방식도 다양하지만 간단하게 사용하려면 역시 K8S만 한 것이 없기 때문에 K8S에 구성해보자.

ConfigMap

apiVersion: v1
kind: ConfigMap
metadata:
  name: logstash-configmap-start
  namespace: search
  labels:
    task: sql-search-init    
data:
  start.sh: |
    #!/bin/bash
    curl -L -O https://go.microsoft.com/fwlink/?linkid=2203102
    tar -xvf ./sqljdbc_11.2.0.0_kor.tar.gz
    mv ./sqljdbc_11.2/enu/mssql-jdbc-11.2.0.jre11.jar ./lib/mssql-jdbc-11.2.0.jre11.jar
  • start.sh 에서는 jdbc 드라이버를 셋팅하는 작업을 수행한다. 이 링크에서 항상 최신 버전의 jdbc 드라이버를 확인하는 것을 권장한다.
apiVersion: v1
kind: ConfigMap
metadata:
  name: logstash-configmap
  namespace: search
  labels:
    task: sql-search    
data:
  logstash.yml: |
    http.host: "127.0.0.0"
    path.config: /usr/share/logstash/pipeline
  logstash.conf: 
    input {
      jdbc {
        jdbc_driver_library => "/usr/share/logstash/lib/mssql-jdbc-11.2.0.jre11.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://{database server}:1433;database={Database name};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
        jdbc_user => "{user}"
        jdbc_password => "{password}"
        statement => "{SQL Query statement};"        
        schedule => "{CRON}"
        tracking_column => "{tracking column}"
        
      }
    }    
    output {
      elasticsearch {
        hosts => "{elastic host}"
        index => "{index name}"
        document_id => "%{index id name}"
        user => elastic
        password => "{elastic password}"
      }
      stdout {
        codec => rubydebug
      }
    }
  • input
    • jdbc_connection_string 에서는 접속할 Azure SQL의 connection string을 넣어 주면된다.
    • jdbc_user SQL 접속 계정 정보 값을 넣어준다. logstash 전용 계정을 만들어 사용하는 것을 권장한다.
    • jdbc_password 계정 비밀번호 값을 넣어준다.
    • statement index에 담을 table 혹은 작성한 query를 넣어 준다.
    • schedule 얼마 만큼 반복 작업을 할 것인지 스케줄을 정한다. 표기는 CRON을 사용한다.
    • tracking_column 데이터 변경에 대한 기준이 되는 column을 넣어 준다 (ex. timestemp)
  • output
    • hosts elastic host 정보를 넣어준다.
    • index SQL정보를 저장할 index 이름
    • document_id 인덱스에 저장할 문서를 식별할 키 값
    • password elstic에 접속할 비밀번호

Deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: sql-logstash
  namespace: search
spec:
  replicas: 1
  selector:
    matchLabels:
      task: sql-search      
  template:
    metadata:
      labels:
        task: sql-search        
    spec:
      containers:
      - name: logstash
        image: docker.elastic.co/logstash/logstash:8.4.2
        ports:
          - containerPort: 5044
        imagePullPolicy: Always
        volumeMounts:
        - mountPath: /usr/share/logstash/config
          name: config-volume
        - mountPath: /usr/share/logstash/pipeline
          name: logstash-pipeline-volume
        - mountPath: /start
          name: start
        lifecycle:
          postStart:
            exec:
              command:
               - /start/start.sh
      volumes:
      - name: config-volume
        configMap:
          name: logstash-configmap
          items:
          - key: logstash.yml
            path: logstash.yml
      - name: logstash-pipeline-volume
        configMap:
          name: logstash-configmap
          items:
          - key: logstash.conf
            path: logstash.conf
      - name: start
        configMap:
          name: logstash-configmap-start
          defaultMode: 0777
      securityContext:
        fsGroup: 101

Service

apiVersion: v1
kind: Service
metadata:
  labels:
    task: sql-search
    kubernetes.io/name: logstash
  name: sql-logstash
  namespace: search
spec:
  ports:
  - port: 5000
    targetPort: 5000
  selector:
    k8s-app: logstash

Configmap 부터 Service 까지 순서대로 모두 K8S에 적용해 주면 된다.
(여러 개의 파일로 보는 것이 귀찮다면 하나의 yaml에 모두 넣어서 한번에 실행해도 된다.)

MS-SQL Replace ASCII control code

SQL 쿼리 결과를 XML 형태로 출력 할 때 아래와 같은 오류가 발생하는 경우가 있다.

FOR XML could not serialize the data for node ‘[ Column name]’ because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

XML로 변활 할 대상 데이터에 ASCII control code가 포함되어 있어서 발생하는 경우이다. 그래서 대상 데이터 안에 포함 되어 있는 해당 아래 스크립트 같이 XML로 변환 전에 모두 치환해 주어야 한다.

SELECT 
  REPLACE([Column name], char(0), '') 
FROM 
  [Table Name]

Control code를 삭제하는 Function을 만들어 두면 필요할 때 가져다 쓰면 좋다.

CREATE FUNCTION [dbo].[FN_ReplaceControlCharacter]  
(  
    @mString NVARCHAR(MAX)  
)  
RETURNS NVARCHAR(MAX)  
AS  
BEGIN      	
	SET @mString = REPLACE(@mString, char(0), '')
	SET @mString = REPLACE(@mString, char(1), '')
	SET @mString = REPLACE(@mString, char(2), '')
	SET @mString = REPLACE(@mString, char(3), '')
	SET @mString = REPLACE(@mString, char(4), '')
	SET @mString = REPLACE(@mString, char(5), '')
	SET @mString = REPLACE(@mString, char(6), '')
	SET @mString = REPLACE(@mString, char(7), '')
	SET @mString = REPLACE(@mString, char(8), '')
	SET @mString = REPLACE(@mString, char(9), '')
	SET @mString = REPLACE(@mString, char(10), '')
	SET @mString = REPLACE(@mString, char(11), '')
	SET @mString = REPLACE(@mString, char(12), '')
	SET @mString = REPLACE(@mString, char(13), '')
	SET @mString = REPLACE(@mString, char(14), '')
	SET @mString = REPLACE(@mString, char(15), '')
	SET @mString = REPLACE(@mString, char(16), '')
	SET @mString = REPLACE(@mString, char(17), '')
	SET @mString = REPLACE(@mString, char(18), '')
	SET @mString = REPLACE(@mString, char(19), '')
	SET @mString = REPLACE(@mString, char(20), '')
	SET @mString = REPLACE(@mString, char(21), '')
	SET @mString = REPLACE(@mString, char(22), '')
	SET @mString = REPLACE(@mString, char(23), '')
	SET @mString = REPLACE(@mString, char(24), '')
	SET @mString = REPLACE(@mString, char(25), '')
	SET @mString = REPLACE(@mString, char(26), '')
	SET @mString = REPLACE(@mString, char(27), '')
	SET @mString = REPLACE(@mString, char(28), '')
	SET @mString = REPLACE(@mString, char(29), '')
	SET @mString = REPLACE(@mString, char(30), '')
	SET @mString = REPLACE(@mString, char(31), '')

    RETURN @mString    
END  

0부터 31까지 순차적으로 있어서 While Loop를 사용하면 간결하게 보일 수 있긴 하지만, 데이터가 많은 경우(ex 100만개 이상의 row) 2배 이상의 속도 차이가 날 수 있다. 성능을 고려해야 한다면, 최대한 필요한 Control code를 특정 지어서 직접 치환하는 것을 권장한다.

Power BI Compare with previous row value

시간에 따라 변화하는 등의 시계열 데이터가 있을 때, 이전 값과 얼만큼 변화 했는지 확인하기 위해서 current row value와 previous row value를 비교 해볼 수 있다.

COVID19 Sample 데이터를 Power BI에 Load 해보자

COVID19-Sample

각 row 데이터를 식별하기 위해서 Index 열을 추가한다.
(참고, 별도의 설정 없이 기본으로 추가된 인덱스는 0부터 시작한다.)

Add Index

이제, 사용자 지정 열을 추가 하여서 Current row에 Previous row value를 추가한다.

사용자 지정 열

사용자 지정 열에는 Script를 추가 할 수 있는데 아래 스크립트를 넣어주면 Previous row에 있는 “인원[명]” 값을 Current row에 추가 할 수 있다.

try 
 #"추가된 인덱스" {[인덱스] + 1} [#"인원[명]"]
otherwise 0
Current row에 Previous row value가 추가됨.

두 개의 값의 비율을 계산하여 증감률을 계산해 낼 수 있다.
다시 한번 사용자 지정 열을 추가 하여 비율 계산 식을 추가한다.

사용자 지정 열
[#"인원[명]"] / [Previous value] * 100

다음과 같이 증감율에 대한 데이터를 확인해 볼 수 있다.

Rate(증감률) 열 추가

만든 증감율 데이터를 가지고 다음과 같이 시각화 할 수 있다.

코로나 차트

선 그래프는 2020년 7월부터 2021년 2월까지 신규 확진자 수를 나타내고 있으며 막대 그래프는 전일 대비 신규 확진자 증감률을 나타내고 있다. 막대 그래프의 색은 증감률 데이터가 100 이하로 감소하면 푸른 계열로 바뀌고, 증가하면 붉은 계열로 바뀌도록 되어 있다.

MS-SQL Stored procedure에 List(or Array) parameter 전달하기

Custom type을 정의하면 Stored procedure(이하 SP)에 List(or Array) 형태의 정보를 전달하여 처리 할 수 있다.

먼저, List로 전달할 정보를 담을 Custom table type을 정의한다.
객체 정보를 담는 Class를 정의한다고 생각하면 된다.

CREATE TYPE CodeList
AS TABLE
(
  Code varchar(32)  
);

SP에 변수를 type을 위에서 만든 Custom type으로 정의한다. 그러면, SP는 정의된 Table 정보를 받아 처리 할 수 있게 된다.

CREATE PROCEDURE GetInfoByCodes
	@pCodeList AS CodeList READONLY
AS 
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM InfoTable
	WHERE Code in (SELECT * FROM @pCodeList)	
END
GO

임의로 Custom table type으로 만든 변수를 만들어 다음과 같이 SP를 실행해 볼 수 있다.

DECLARE @pCodes CodeList
INSERT INTO @pCodes VALUES('A40274208')
INSERT INTO @pCodes VALUES('A10028014') 
INSERT INTO @pCodes VALUES('A56087115') 

EXEC GetInfoByCodes @pCodeList = @pCodes

.NET Framework 4.X 에서 Option Pattern 사용하기

Option Pattern은 Ubiquitous design pattern 중 하나로 web.config와 같은 Application의 모든 환경 설정 넣어서 사용하는 파일을 목적에 맞는 클래스들로 분리 시키고 인터페이스로 추상화하여 사용할 수 있도록 해준다.

이러한 이유로, Option Pattern을 사용하면 소프트웨어 엔지니어링 원칙중 2가지를 따를 수 있게 된다. 하나의 기능에 대한 환경 설정을 위한 클래스로 분리해서 사용하기 때문에 SRP(Single Responsibility Principle)를 따르게 되며, 인터페이스를 통해서 클래스의 값을 주입 받기 때문에 ISP(Interface Segregation Principle)를 따르게 된다.

기존에 web.config에 설정된 환경 변수들을 사용할때는 다음과 같이 String 타입인 XML 값을 읽어와야 했다. 그리고 필요에 따라 String 타입을 필요한 타입에 맞추어 Cast 해서사용해야 했다.

string SecretName = ConfigurationManager.AppSettings["SecretName"]

하지만, Option Pattern을 사용하다면, 미리 정의된 클래스 속성에 맞추어 Injection 과정에서 Cast가 진행되기 때문에 바로 사용할 수 있는 편리함이 있다. 그리고 미리 정의된 클래스 속성 이름을 참조하여 사용하기 때문에 항상 동일한 이름을 일관되게 코드에 적용 할 수 있게 되며, 참조 추적을 통해 얼마나 많은 곳에 해당 환경 설정이 사용되고 있는지 영향도 파악을 할 수 있게 되는 장점을 얻을 수 있다.

Option Pattern은 .Net Core부터는 Option pattern이 기본 패키지로 제공되기 때문에 쉽게 사용할 수 있다. 하지만 점점 EOS(End Of Service)가 공지되고 있는 .Net Framework는 그렇지 않기 때문에 만들어 사용해야 한다.

아래 구현된 소스코드는 .Net Framework 4.8 LTS 기준으로 작성되었고 Injector는 Simple Injector를 사용하였다. 전체 소스는 Github를 참고하길 바란다.

Web.Config

<appSettings>    
  <!--Application Settings-->
  <add key="SecretName" value="Name" />
  <add key="SecretKey" value="p@ssWord!" />    
</appSettings>

Options Class

public class SecretOptions
{    
    [Option("SecretName")]
    public string secretName { get; set; }
               
    [Option("SecretKey")]
    public string secretKey { get; set; }             
}

Options Interface

public interface IOptions<T>
{
    T Value { get; }
}

Options Concrete

public class Options<T> : IOptions<T>
{
    private readonly T model;
    private readonly List<string> AppSettingNames;
    
    public Options()
    {      
        AppSettingNames = ConfigurationManager.AppSettings.AllKeys.ToList();           

        T instance = Activator.CreateInstance<T>();

        model = (T)SetConfig(instance.GetType());
    }

    private object SetConfig(Type type)
    {
        object instance = Activator.CreateInstance(type);

        var instanceProperties = instance.GetType().GetProperties();

        foreach (var property in instanceProperties)
        {
            //If it is a hierarchical option class, it is searched recursively.
            if (property.GetCustomAttribute<OptionObjectIgnoreAttribute>() != null)
            {
                property.SetValue(instance, SetConfig(property.PropertyType));
            }

            //If an attribute does not have an 'OptionAttribute', the value is searched by the attribute's original name. 
            //but if there is, the value is searched by the set name at 'OptionAttribute'
            var name = AppSettingNames.FirstOrDefault(m => property.GetCustomAttribute<OptionAttribute>() == null ?
                                                            property.Name.Equals(m, StringComparison.InvariantCultureIgnoreCase) :
                                                            property.GetCustomAttribute<OptionAttribute>().keyName.Any(n => n.Equals(m, StringComparison.InvariantCultureIgnoreCase)));

            var value = ConfigurationManager.AppSettings.Get(name);

            if (!value.Equals(String.Empty))
            {
                property.SetValue(instance, Convert.ChangeType(value, property.PropertyType));
            }
        }

        return instance;
    }

    public T Value
    {
        get
        {
            return model;
        }
    }
}

Global.asax

var container = new Container();

container.RegisterMvcControllers(Assembly.GetExecutingAssembly());

//Injection form Web.config settings to Option Model  
container.Register<IOptions<SecretOptions>, Options<SecretOptions>>(SimpleInjector.Lifestyle.Singleton);

DependencyResolver.SetResolver(new SimpleInjectorDependencyResolver(container));

Calculating DTU in AWS RDS for MSSQL

On-Prem이나 VM 형태로 사용하던 MSSQL Server를 Azure SQL로 Migration을 한다고 가정해보자. 기존에 사용하던 처리 성능에 맞춰 Cloud 상에 알맞은 수준의 리소스를 준비하고 옮겨야 할 것이다.

기존에 사용하던 SQL Server는 CPU와 Memory, Disk I/O등 Hardware적인 요소로 처리 성능을 나타낸다. 하지만 Azure SQL에서는 DTU(Database Throughput Unit)라는 단위를 사용하여 처리 성능을 나타내기 때문에 성능 비교가 쉽지가 않다.

DTU는 Hardware 성능 요소인 CPU, Disk I/O와 Database Log flush 발생양을 이용하여서 계산해낸 단위이다. 때문에 MSSQL Server에서 각 Metric들을 추출해 낼 수 있다면, DTU를 계산해 낼 수 있다. DTU 계산은 이미 Azure DTU Calculator라는 계산기가 제공되고 있기 때문에 추출해낸 Metric 값들을 설명대로 잘 넣어 주기만 하면 쉽게 확인해 볼 수 있다.

Metric을 추출하는 방법은 대상이 되는 SQL Server에서 DTU Calculator에서 제공하는 PowerShell Script를 관리자 권한으로 실행시켜 주기만 하면 된다. Script는 CPU, Disk I/O, Database Log flush에 대한 Metric들을 DTU Calculator에서 요구하는 형식으로 추출해준다. 때문에 일반적으로 On-Prem이나 VM 형태로 MSSQL Server를 사용하고 있다면, 계산해 내는데 별다른 어려움이 없을 것이다.

하지만 AWS RDS for MSSQL 같은 경우 MSSQL Server를 AWS에서 Cloud Service로 제공하기 위해 Wrapping한 서비스다보니 SQL Server에 대한 관리자 권한을 얻을 수가 없다. 이러한 이유 때문에 DTU Calculator에서 제공하는 PowerShell Script를 사용할 수 가 없다.

이런 경우, DTU 계산에 필요한 Metric들을 AWS에서 별도로 추출해야 한다. AWS에는 Cloudwatch라는 서비스를 제공하고 있는데, AWS 서비스들에 대한 Performance Metric을 기록하고 제공하는 역할을 한다.

Cloudwatch에서 metric을 추출하기 위해서는 AWS CLI를 사용하는 것이 편리하다. AWS Console의 우측 상단에 있는 CLI 실행 아이콘을 눌러 AWS CLI를 실행해 보자.

AWS CLI가 실행 되었다면, 아래 조회 명령어(list-metrics)를 실행시켜 제공되는 metric들에 대한 정보를 확인해 보자.

aws cloudwatch list-metrics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=[RDS for MSSQL Name]

잘 실행되었다면, cloudwatch에서 제공하는 Metric 리스트들을 확인 할 수 있다. 리스트 중 DTU계산에 필요한 CPU Processor Time과 Disk Reads/sec, Writes/sec에 값에 해당 하는 Metric 다음과 같다. (참고로, Log Bytes Flushed/sec에 해당하는 metric은 제공되지 않는다.)

{
    "Namespace": "AWS/RDS",
    "MetricName": "ReadIOPS",
    "Dimensions": [
        {
            "Name": "DBInstanceIdentifier",
            "Value": "[RDS for MSSQL Name]"
        }
    ]
},
{
    "Namespace": "AWS/RDS",
    "MetricName": "WriteIOPS",
    "Dimensions": [
        {
            "Name": "DBInstanceIdentifier",
            "Value": "[RDS for MSSQL Name]"
        }
    ]
},
{
    "Namespace": "AWS/RDS",
    "MetricName": "CPUUtilization",
    "Dimensions": [
        {
            "Name": "DBInstanceIdentifier",
            "Value": "[RDS for MSSQL Name]"
        }
    ]
}

이제, 필요한 metric 정보에 대해서 확인 하였으니 수집해보자. 아래 metric 수집 명령어(get-metric-statistics)를 사용하면 수집된 metric정보가 csv파일로 저장된다.

aws cloudwatch get-metric-statistics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=[RDS for MSSQL Name] --metric-name=CPUUtilization --period 3600 --statistics Average --start-time 2021-01-01T00:00:00.000Z --end-time 2021-02-01T00:00:00.000Z | jq -r '.Datapoints[] | [.Timestamp, .Average, .Unit] | @csv' | cat > cpu.csv

aws cloudwatch get-metric-statistics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=[RDS for MSSQL Name] --metric-name=ReadIOPS --period 3600 --statistics Average --start-time 2021-01-01T00:00:00.000Z --end-time 2021-02-01T00:00:00.000Z | jq -r '.Datapoints[] | [.Timestamp, .Average, .Unit] | @csv' | cat > read.csv

aws cloudwatch get-metric-statistics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=[RDS for MSSQL Name] --metric-name=WriteIOPS --period 3600 --statistics Average --start-time 2021-01-01T00:00:00.000Z --end-time 2021-02-01T00:00:00.000Z | jq -r '.Datapoints[] | [.Timestamp, .Average, .Unit] | @csv' | cat > write.csv

생성한 csv 파일은 AWS CLI Storage에 저장 되어있다. 우측 상단의 Actions > Download File 메뉴를 사용하면 로컬 환경으로 다운 받을 수 있다.

3개의 파일 모두 다운 받아서 DTU 계산에 필요한 형식으로 맞춰준다.

  • % Processor Time = CPUUtilization
  • Disk Reads/sec = ReadIOPS
  • Disk Writes/sec = WriteIOPS
  • Log Bytes Flushed/sec에 해당하는 데이터가 없기때문에 0으로 넣어준다.

그림과 같은 형태로 구성될 것이다.

RDS for MSSQL metrics for DTU Calculator

이제, 한땀 한땀 준비한 데이터를 Azure DTU Calculator에 넣어 주기만 하면 되는데 한가지 주의할 점이 있다. 우리가 얻은 데이터는 RDS for MSSQL Server에 대한 정보이다. 때문에 여러 DB Instance에 대한 계산을 하는 Elastic Database 메뉴를 선택하여 계산 하도록 해야한다.

계산이 끝나면 아래와 같이 나오는데 이 결과를 통해서 AWS RDS에서 사용하던 성능 수준을 Azure SQL에서 그대로 사용하기 위해서 구성 해야하는 Service Tire/Performance Level을 확인 할 수 있다.

DTU Result