'DB'에 해당되는 글 18건

[Mysql 접속 후]

* 설정가능한 변수목록 확인 : show variables;

max_connections : MySQL 서버가 최대한 허용할 수 있는 클라이언트의 연결 수를 제한하는 설정이다. max_connection 값을 수천 수만으로 늘릴수록 MySQL 서버가 응답 불능 상태로 빠질 가능성이 높아지며 이 설정값을 낮출수록 MySQL 서버가 응답할 수 없게 될 확률이 줄어든다. 이 설정은 동적으로 변경할 수 있으므로 커넥션이 부족하다면 그때 변경해주면 된다.

thread_cache_size : 클라이언트와 서버와의 연결 그 자체를 의미하며 스레드는 해당 커넥션으로부터 오는 작업 요청을 처리하는 주체다. 최초 클라이언트로부터 접속 요청이 오면 MySQL 서버는 스레드를 준비해 그 커넥션에 작업 요청을 처리해 줄 스레드를 매핑하는 형태이다. 클라이언트가 종료되면 MySQL은 스레드를 스레드풀에 보관한다. Thread_cache_size 설정 변수는 최대 몇 개까지의 스레드를 스레드 풀에 보관할지 결정한다.

wait_timeout : MySQL 서버에 연결된 클라이언트가 wait_timeout에 지정된 시간 동안 아무런 요청 없이 대기하는 경우 MySQL 서버는 해당 커넥션을 강제로 종료해버린다. 이 설정값의 시간 단위는 초이며 기본값은 28800초(8시간)이다.

* 커넥션 상태값 확인 : show status like '%connect%';

Aborted_connects : MySQL 서버에 접속이 실패된 수
Max_used_connections : 최대로 동시에 접속한 수
Threads_connected : 현재 연결된 Thread 수

set global max_connections = 1000;
set global wait_timeout = 1800;

* 클라이언트 프로그램 접속 확인 : show status like '%clients%';

Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수

* 쓰레드 상태값 확인 : show status like '%thread%';

Threads_cached : Threads Cache의 Thread 수
Threads_connected : 현재 연결된 Thread 수
Threads_created : 접속을 위해 생성된 Thread 수
Threads_running : sleeping 되어 있지 않은 Thread 수

* 튜닝여부 판단

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

Connection Usage(%)가 100%라면 max_connections 수를 증가시켜야 한다. Connection 수가 부족할 경우 Too Many Connection Error가 발생한다.
DB서버의 접속이 많은 경우 wait_timeout을 최대한 적게 설정하여 불필요한 연결을 빨리 정리하는 것이 좋다. 그러나 Connection Miss Rate(%)가 1% 이상이면 wait_timeout을 좀 더 길게 설정한다.

MySQL 서버는 외부로부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속 시에 불필요한 부하가 발생한다. skip-name-resolve를 설정하고 접속시 IP기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속할 수 있다.

[참고자료]

RealMySQL (위키북스)

Oracle 에서 ' 싱글쿼테이션을 문자열에 붙여 SELECT 가 정상적인 방법으로는 되지 않았으며, 아래와 같이 SELECT를 해야 할 듯 하다. [']의 경우 아스키코드 문자는 39이며, 캐릭터코드는 CHR(39)가 된다고 함. ['] = [CHR(39)]

=> SELECT CHR(39)||'멍충이'||CHR(39)
    FROM DUAL

    SELECT * FROM TB_TEST WHERE SUBJECT LIKE '%'||CHR(39)||'%'

DB 데이터 이관중 무심코 지나칠 수 있는 캐릿터셋 Mysql 은 기본 latin1 캐릿터 셋을 써서 DB 입력시나 셀렉트시 깨짐현상이 일어날 수 있으며, 오늘 확인한 결과 where 절에 한글 조건이 먹지 않는 현상을 발견했다.

해결 방법으로는 DB 재생성 할때, utf8로 셋팅하여 데이터를 입력하고, Mysql 캐릭터셋을 설정한다.

[Mysql 캐릿터셋 확인]
> mysql -u 계정명 -p(접속)
mysql> SHOW VARIABLES LIKE 'collation%';
mysql> SHOW VARIABLES LIKE 'character_set%';

[/etc/my.cnf 확인]

> vi /etc/my.cnf(esc + shift + ; : wq)

=> 아래의 내용을 붙임

============================================================================

[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = set collation_connection = utf8_general_ci
init_connect = set names utf8

character-set-client-handshake=TRUE
skip-character-set-client-handshake

[client]
default-character-set = utf8

============================================================================

[DB 툴]
show variables like 'char%';

set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_results = null;

[Mysql 재기동]

> service mysqld restart

===> 재기동후 Mysql 캐릭터셋 확인

update  /*+ bypass_ujvc */

(select a.column as new_column, b.column as old_column 

from TEST_A a, TEST_B b

where a.key = b.key )

set new_column = old_column

/*+ bypass_ujvc */ : 1:1 대응을 보장한다.

                        키보존을 생략할수 있게 해주는 힌트이다.

<변수 셋팅>

iBatis 에서 IN 구문 처리시 파라미터 변수가 아래와 같이 되어있고 

SQL SELECT 시 이런 식으로는 SQL 인식이 되지 않는다. 오류도

뜨지 않는다.

String name = "('a', 'b', 'c')";

<![CDATA[

select *

from TEST

where NAME in #name#

]]>

IN 처리시 에는 아래와 같이 # 대신 $ 를 사용하여 인식가능 하도록 한다.

<![CDATA[

select *

from TEST

where NAME in $name$

]]>

<iBatis iterate>

ArrayList<String> searchWrdList = new ArrayList<String>();
searchWrdList.add("값");
searchWrdList.add("값");
빈.setSearchWrdList(searchWrdList);

private ArrayList<String> searchWrdList = null;

<![CDATA[ 컬럼명 IN ]]>
<iterate  property="searchWrdList" open="(" close=")" conjunction=",">
     #searchWrdList[]#
</iterate>

소스 분석중 숫자 0~9 값을 넣는 자료형 인가 싶은 tinyint(1) 이 뭔가 싶어 찾아보았는데

불린형(boolean) 형이라 함. 

0 은 fasle 를 반환

1 이상은 true를 반환

그래서 코드성 값을 넣으려면 char(1), tinyint(2) 자료형을 써줘야 함.

* 주로 서버 Oracle 과 localhost Oracle 의 캐릭터셋이 맞지 않아 발생되는 경우와 암호화된 필드의 숫자, 영문이 2Byte 차지하면서 DB오류를 발생시킬 수 있는 경우, 일본어 카타카나 DB처리 시 오류를 발생.빈칸이 발생하였다고  TRIM() 처리를 하여도 오류는 발생.

(ORA-29275 : 부분 다중 바이트 문자)

전각문자(2Byte) = > 반각문자(1Byte) : TO_SINGLE_BYTE()

ex) SELECT TO_SINGLE_BYTE('A 1 B 2 C 3')
FROM DUAL

반각문자(1Byte) = > 전각문자(2Byte) : TO_MULTI_BYTE()

ex) SELECT TO_MULTI_BYTE('A 1 B 2 C 3')
FROM DUAL

[UNION]

select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SBRCMxxx
UNION
select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SBRCMxxx
UNION
select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SWQTMxxx

[UNION ALL]

select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4)
from TB_SWQTMxxx

- UNION : 중복제거 하여 데이터 SELECT 한다.

- UNION ALL : 중복 제거 없이 있는 그대로의 데이터를 이어붙여 SELECT 한다.

- UNION 은 중복제거 작업을 하기 때문에 UNION ALL 보다 속도는 느리다.

- 필드 개수와 형식이 같아야 한다.

select substr('0' || TO_CHAR(LEVEL - 1), -2, 2) rtime, TO_CHAR(LEVEL - 1), LEVEL, substr('0' || TO_CHAR(LEVEL - 1), -2, 2)
    from
        (select TO_DATE('20130601', 'YYYYMMDD') MAKE_DATES
          from DUAL 
        )  CONNECT BY LEVEL <= 24

- LEVEL 이용하여 원하는 행(날짜)을 리턴 시키는 용도로 많이 사용함.

- 24시간 리턴 SQL

- 처음 CONNECT BY LEVEL 은 리턴시킬 행 갯수, 다음 CONNECT BY LEVEL 은 DEPT.

with MODEL_DESC as (
select MODEL_TYPE, MODEL_DEV_NM
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, MODEL_DEV_NM
from TB_SWQTMxxx
)
select  substr(max(SYS_CONNECT_BY_PATH (REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4) , ',')), 2) as MODEL_DEV_NM
FROM (
SELECT MODEL_DEV_NM, ROWNUM as RNUM
 from MODEL_DESC
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1

with MODEL_DESC as (
select MODEL_TYPE, MODEL_DEV_NM
from TB_SBRCMxxx
UNION ALL
select MODEL_TYPE, MODEL_DEV_NM
from TB_SWQTMxxx
)
select  MODEL_TYPE, substr(max(SYS_CONNECT_BY_PATH (REGEXP_REPLACE(SUBSTR(SUBSTR(MODEL_DEV_NM, 1, LENGTH(MODEL_DEV_NM)-4),5),'[0-9]','#') || SUBSTR(MODEL_DEV_NM,-4,4) , ',')), 2) as MODEL_DEV_NM
from (
    select MODEL_TYPE, MODEL_DEV_NM, ROW_NUMBER() OVER (PARTITION by MODEL_TYPE order by MODEL_DEV_NM) RNUM
    from(
    select MODEL_DEV_NM, MODEL_TYPE, ROWNUM as RNUM
     from MODEL_DESC
    )
)
START WITH RNUM = 1 CONNECT BY PRIOR RNUM = RNUM - 1
and PRIOR MODEL_TYPE = MODEL_TYPE
group by MODEL_TYPE
order by MODEL_TYPE

- 조회된 SQL 쿼리의 열을 구분자를 이용하여 행으로 나열하고 싶을때 사용

- 연결하려는 문자열의 수가 많아질 경우 쿼리의 performance 문제 발생 여지가 있음.

- Oracle 10g 부터 사용