[MYSQL]DB 튜닝(innodb / my.cnf)

Programing 2016.11.14 16:33 Posted by 애플자라

### DB셋팅 / 2016.11.14


# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=1000

 

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0

 

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256

 

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=369M


# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8

 

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

 

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=738M

 

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=641M

 

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K

 

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K

 

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=26M

 

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

 

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=13M

 

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=1243M

 

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=622M

 

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=18

innodb_lock_wait_timeout = 1200

 

MyBatis란?

Programing 2016.05.09 10:01 Posted by 애플자라

MyBatis란?
MyBatis 는 개발자가 지정한 SQL, 저장프로시저 그리고 몇가지 고급 매핑을 지원하는 퍼시스턴스 프레임워크이다. MyBatis 는 JDBC 코드와 수동으로 셋팅하는 파라미터와 결과 매핑을 제거한다. MyBatis 는 데이터베이스 레코드에 원시타입과 Map 인터페이스 그리고 자바 POJO 를 설정하고 매핑하기 위해 XML 과 애노테이션을 사용할 수 있다.

[출처] myBatis 개념|작성자 yunyoejin


MariaDB Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MariaDB server

 

개발서버에 MariaDB 설치 후 로컬피시에서 원격으로 접속시 

 

 

SQL 오류 (1130) #0 구문: Host '192.168.110.108' is not allowed to connect to this MariaDB server

 

라는 경고창이 나타났다.

 

보안 때문인지 권한설정에 IP를 막는 기능이 존재한다.

나 같은경우는 root였으므로 

 

GRANT ALL PRIVILEGES ON *.* TO <username>@'%' IDENTIFIED BY <password>;

 

 

새로 계정을 만들어서 사용하여도 된다.

 

CREATE USER <username>@'%' IDENTIFIED BY <password>;

GRANT ALL PRIVILEGES ON *.* TO <username>@'%' WITH GRANT OPTION;

 

 

[MSSQL]MSSQL 2005 유지관리 계획 등록 오류

Database 2013.07.10 11:17 Posted by 애플자라

[GUID 에는 대시 4개를 사용하여 32개의 자릿수를 사용해야 합니다.]




# select @@version 정보


Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)



# Microsoft SQL Server Management Studio 정보


Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services 클라이언트 도구 2005.090.1399.00
Microsoft Data Access Components(MDAC) 6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5466
운영 체제 6.1.7601




----------------------------------------------------------------------------------------------------------------


SQL 서비스팩 4를 설치하고 난다음의 버전.



# select @@version 정보

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)



# Microsoft SQL Server Management Studio 정보

Microsoft SQL Server Management Studio 9.00.5000.00
Microsoft Analysis Services 클라이언트 도구 2005.090.5000.00
Microsoft Data Access Components(MDAC) 6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5466
운영 체제 6.1.7601





** 해결 **

서비스팩 4를 설치하고 난다음에는 특이사항없이 유지관리계획에 등록되며, 백업역시 잘~ 됩니다.

버전의 차이로 구동이 되지 않았던겁니다.

 

출처 - http://www.duck.pe.kr/219

[ORACLE]OUT JOIN

Database 2013.06.14 17:41 Posted by 애플자라

SELECT A.POST_ID I_POST_ID,
       A.NAME AS I_NAME,
       A.PHONE1 I_R_PHONE1,
       A.PHONE2 I_R_PHONE2,
       A.PHONE3 I_R_PHONE3,
       NVL (B.GUBUN, '신규') AS GUBUN1,
       A.REGISTER_DT I_REGISTER_DT,
       A.GUBUN GUBUN,
       A.EVENT_YN,
       ROW_NUMBER () OVER (ORDER BY REGISTER_DT DESC) RNM,
       A.CELLPHONE
  FROM EVENT A, APLUS_V_CUSTOMER_CELLPHONE B
AND A.CELLPHONE = B.CELLPHONE(+) AND A.NAME = B.CUSTOMER_NM(+)
ORDER BY A.REGISTER_DT DESC

 

A테이블보다 B테이블이 많을때 OUT JOIN

'Database' 카테고리의 다른 글

[ORACLE] 오라클 재구동  (0) 2014.08.13
[MSSQL]MSSQL 2005 유지관리 계획 등록 오류  (0) 2013.07.10
[ORACLE]OUT JOIN  (0) 2013.06.14
[ORACLE]LONG 데이터타입 -> VARCHAR2 데이터로 변환  (0) 2013.06.07
[ORACLE] MERGE INTO  (0) 2013.03.08
[ORACLE] 바이트수 확인  (0) 2013.03.07

[ORACLE]나이구하기쿼리

Database 2012.11.09 15:40 Posted by 애플자라

SELECT *
FROM
(
SELECT CUSTOMER_NM,
        SUBSTR(REGIDENT_ID, 7, 1),
        REGIDENT_ID,
        TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X'),
        2012 - TO_NUMBER(DECODE(SUBSTR(REGIDENT_ID, 7, 1), '3', 20, '4', 20, 19) || SUBSTR(REGIDENT_ID, 0, 2)) AS AGE,
        REGISTER_DT
FROM CUSTOMER
WHERE SERVICE_CD1 ='Y'
AND ACTIVE_YN = '1'
AND TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X') IS NULL -- 숫자만 가져오는
--AND TRANSLATE (SUBSTR(REGIDENT_ID, 0, 2), 'X0123456789', 'X') IS NOT NULL
-- 문자만 가져오는
) X
WHERE AGE > 65

 

SELECT DECODE (
          SIGN (
             TRANSLATE ('4312a',
                        '0123456789abcdefghijklmnopqrstuvwxyz',
                        '000000000011111111111111111111111111')),1, '문자','숫자')T
  FROM DUAL;

SELECT TRANSLATE ('123AB', 'X0123456789', 'X') FROM DUAL

[ORACLE]휴일감안 전/후영업일자 조회

Database 2012.09.04 10:37 Posted by 애플자라

-- 기준일자와  휴일여부를 이용하여 전/전전/전전전, 후/후후/후후후 영업일자를 조회하기위한 sql

-- 인터넷 무쟈게 돌아댕겨도 전/후영업일자 조회하는 sql이 없어서 함. 만들어봤어요(내가 필요해서..ㅎㅎ)

-- dd: 특정일자, ck:2:비영업일, 1:영업일

with cal_t as(select '20110901' dd, '1' ck from dual union all
select '20110902' dd, '1' ck from dual union all
select '20110903' dd, '2' ck from dual union all
select '20110904' dd, '2' ck from dual union all
select '20110905' dd, '1' ck from dual union all
select '20110906' dd, '1' ck from dual union all
select '20110907' dd, '1' ck from dual union all
select '20110908' dd, '1' ck from dual union all
select '20110909' dd, '1' ck from dual union all
select '20110910' dd, '2' ck from dual union all
select '20110911' dd, '2' ck from dual union all
select '20110912' dd, '2' ck from dual union all
select '20110913' dd, '2' ck from dual union all
select '20110914' dd, '1' ck from dual union all
select '20110915' dd, '1' ck from dual union all
select '20110916' dd, '1' ck from dual union all
select '20110917' dd, '2' ck from dual union all
select '20110918' dd, '2' ck from dual union all
select '20110919' dd, '1' ck from dual union all
select '20110920' dd, '1' ck from dual union all
select '20110921' dd, '2' ck from dual union all
select '20110922' dd, '1' ck from dual union all
select '20110923' dd, '1' ck from dual union all
select '20110924' dd, '2' ck from dual union all
select '20110925' dd, '2' ck from dual union all
select '20110926' dd, '1' ck from dual union all
select '20110927' dd, '1' ck from dual union all
select '20110928' dd, '1' ck from dual union all
select '20110929' dd, '1' ck from dual union all
select '20110930' dd, '1' ck from dual
)
select a.dd
,a.ck
,case when a.ck = '1' then a.후영업일     else max(a.후영업일)     over(order by a.dd) end 후영업일
,case when a.ck = '1' then a.후후영업일   else max(a.후후영업일)   over(order by a.dd) end 후후영업일
,case when a.ck = '1' then a.후후후영업일 else max(a.후후후영업일) over(order by a.dd) end 후후후영업일
,case when a.ck = '1' then a.전영업일     else min(a.전영업일)     over(order by a.dd desc) end 전영업일
,case when a.ck = '1' then a.전전영업일   else min(a.전전영업일)   over(order by a.dd desc) end 전전영업일
,case when a.ck = '1' then a.전전전영업일 else min(a.전전전영업일) over(order by a.dd desc) end 전전전영업일  
from (
  select dd
  ,ck
  ,case when ck = '2' then null else lead(dd, 1) over(partition by ck order by dd) end 후영업일
  ,case when ck = '2' then null else lead(dd, 2) over(partition by ck order by dd) end 후후영업일
  ,case when ck = '2' then null else lead(dd, 3) over(partition by ck order by dd) end 후후후영업일  
  ,case when ck = '2' then null else lag(dd, 1)  over(partition by ck order by dd) end 전영업일
  ,case when ck = '2' then null else lag(dd, 2)  over(partition by ck order by dd) end 전전영업일
  ,case when ck = '2' then null else lag(dd, 3)  over(partition by ck order by dd) end 전전전영업일
  from cal_t
  where 1=1
 ) a
 order by a.dd

[출처] 휴일감안 전/후영업일자 조회|작성자 마중물

====================================================================뷰테이블 만들어서 사용

CREATE OR REPLACE FORCE VIEW APLUSORA.APLUS_V_WORK_DAY
(
   DD,
   ISWORK,
   B_DD

)
AS
     WITH CAL_T AS
(
    SELECT WORK_DAY DD, ISWORK FROM IFT_WORKING_DAY
    WHERE  WORK_DAY BETWEEN '20120700' AND '20121231'
)
SELECT A.DD
,A.ISWORK
,CASE WHEN A.ISWORK = 'Y' THEN DD ELSE MIN(A.B_DD)     OVER(ORDER BY A.DD DESC) END B_DD
FROM (
  SELECT DD
  ,ISWORK
  ,CASE WHEN ISWORK = 'N' THEN NULL ELSE LAG(DD, 1)  OVER(PARTITION BY ISWORK ORDER BY DD) END B_DD
  FROM CAL_T
  WHERE 1=1
 ) A;


SELECT TXDATE,권유자,권유자변경일,사번,계좌명,펀드계좌번호,AAA,BBB,AAA-BBB AS CCC
FROM
(
    SELECT
      A.TXDATE
     --,LENGTH(RECOMMEND_ID)
     ,A.RECOMMEND_NAME 권유자
     ,A.RECOMM_DATE 권유자변경일
     ,A.RECOMMEND_ID 사번
     ,A.CUSTOMER_NAME 계좌명
     ,A.FUND_ACCOUNT_NO 펀드계좌번호
     ,NVL(A.PAYMENT_AMOUNT_TOTAL,0) AAA
     ,NVL((SELECT PAYMENT_AMOUNT_TOTAL FROM IFK_FUND_RECOMMENDER_141
           WHERE TXDATE = CASE WHEN A.RECOMM_DATE-1 < '20120706'  THEN '20120706'
           ELSE (SELECT DECODE(ISWORK,'Y',DD,B_DD) AS WORK FROM APLUSORA.APLUS_V_WORK_DAY
                    WHERE DD = TO_CHAR(TO_DATE(A.RECOMM_DATE)-1,'YYYYMMDD')) END 
           AND FUND_ACCOUNT_NO = A.FUND_ACCOUNT_NO),0) BBB
    FROM APLUSORA.IFK_FUND_RECOMMENDER_141 A
    WHERE LENGTH(A.RECOMMEND_ID) =6
    AND A.TXDATE BETWEEN '20120706' AND '20120831'
    --AND A.RECOMM_DATE='20091001'
    AND A.RECOMMEND_ID NOT IN ('090001','090002')
) X

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

[ORACLE]SQL*Loader 사용법

Database 2012.08.24 10:39 Posted by 애플자라

문법
      SQLLDR [keyword=] value  [ [keyword=] value ]...

 

예제
      SQLLDR scott/tiger control='c:\xxx.ctl' log='xxx.log' direct=true ERRORS=99999999

 

키워드

  USERID
  오라클 사용자 이름과 암호를 지정 합니다.

  CONTROL
  콘트롤 파일 이름, SQL*Loader을 수행하기 위해서는 항상 지정해 주어야  합니다.

  LOG
  로그 파일 이름을 지정 합니다. (기본 이름은 controlfile.log)

  BAD
  거부된 레코드 모두를 저장하는 배드 파일 이름을 지정 합니다.

  DATA
  입력 데이터 파일 이름을 지정 합니다.

  DISCARD
  Load시 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)

  DISCARDMAX
  버림(discard)의 최대 허용 갯수를 지정 합니다.

  ERRORS
  허용하는 배드 레코드의 최대 수를 지정 합니다.

  DIRECT
  TRUE로 설정되면 SQL*Loader는 DIRECT PATH를 사용.
  반대의 경우는 기본 값인 CONVENTIONAL PATH를 사용 합니다.

  PARFILE
  추가 파라미터 파일을 지정 합니다.

  PARALLEL
  DIRECT 로드에서만 적합한 이 파라미터는 다중 병렬 DIRECT로드가
  수행되도록 지정 합니다.

  FILE
  병렬 DIRECT로드의 경우 임시 세그먼트가 생성될 파일을 지정 합니다.

-- SQLLoader 로그

제어 파일:    S_DATAFILE.CTL
데이터 파일:    NEW_ADDRESS_sam.txt
  잘못된 파일: C:\SQLLOADER\NEW_ADDRESS_sam.BAD
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  1000 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

최대 오류 수 초과 - 상기 통계는 부분적인 실행을 반영한 것입니다 --이것 때문에 찾게된
테이블 NEW_ADDRESS_20120823:
  2895500 행 로드되었습니다.
  데이터 오류 때문에 51 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다

 

쿼리 깔끔하게 정리하기

Database 2012.03.14 11:16 Posted by 애플자라

Google 에서 SQL Formatter 로 검색하면 많은 SQL문장 정리 싸이트들이 나온다.

그 중에서 나는 http://www.sqlinform.com가 제일 괜찮은 것 같다.

자바 애플릿으로 Java 언어 등에서 String 으로 연결된 문자열을 그냥 던져줘도 알아서 SQL문만 뽑아내서 정리해준다.
반대로 SQL 문장을 Java 문자열로 연결해주기도 한다.

[ORACLE] Toad Make Code Statement

Tips 2009.09.11 19:15 Posted by 애플자라

SQL Editor > Make Code Statement

  글쓴이 : 김홍선


  기능 설명

  Make Code Statement는 SQL Editor에 입력된 쿼리를
  Java, C++, VB와 같은 언어의 소스 코드 내에서 바로 사용할 수 있도록
  문자열 변수로 만들어 주는 기능을 한다.

  예를 들어보자.

  Java 소스 코드내에, 실행되는 쿼리를 입력하는 식으로 개발을 진행하고 있다고 하자.
  아래와 같이 쿼리를 만들어줘야 한다고 하면,
  (이 예제는 Eclipse를 사용했다.)
  
  


  Toad에서 쿼리를 실행하여 결과를 테스트 한 후에, 소스 내의 해당위치에 붙여넣고
  " 나 + 와 같은 문자(사용언어에 따라 다르다.)를 입력해야 한다.
  쿼리가 긴 경우에는 사용하는 에디터가 어느정도 지원해 준다고 해도,
  상당히 번거로운 작업이 아닐수 없다.
  이럴때 " 나 + 와 같은 문자를 Toad의 SQL Editor상에서 한꺼번에 붙여주는
  기능을 하는 것이 Make Code Statement 이다.


  테스트에 사용된 Toad 버전

  8.5.x.x


  사용법

  아래와 같이 쿼리를 입력하고,
  메뉴에서 SQL Editor > Make Code Statement 를 클릭하면,

  


  아래와 같이 Toad 하단에 쿼리를 포함한 문장이 클립보드에 복사되었다는
  메세지가 나타난다.

  


  클립보드에 복사된 내용을 확인하기 위해서,
  아래와 같이 에디터를 실행하고 Ctrl+V 로 붙여넣기를 해보자.
  (여기서는 에디터로 editplus를 사용했다.)

  


  쿼리에 추가적으로 " 나 + 와 같은 문자가 붙어서 소스코드내에서
  쉽게 사용할수 있는 형태가 되었다.


  즉, 정리를 해보면,
  1) 쿼리를 입력하고
  2) Make Code Statement를 클릭하고 (클립보드에 저장된다.)
  3) 사용하는 언어툴의 에디터에 붙여넣기
  를 하면 되겠다.


  단축키

  Ctrl+M 을 눌러서 실행한다.


  관련 팁 설명

  • 언어마다 포맷을 설정할 수 있다.

  위에서는 Java에 관해서 예를 들었다.
  Java에서 문자열을 만들고 이어주기 위해서 "와 +를 사용했지만,
  다른 언어를 사용할 경우 이런 문자열을 이어주는 문자들이 다를 수 있다.
  Toad는 여러 언어에 대해서 Make Code 기능을 제공한다.
  아래와 같이 해보자.
  
  메뉴에서 View > Options 를 클릭하면 아래와 같은 옵션창이 뜬다.
  왼쪽 트리에서 SQL Editor -> Make Code 를 클릭하고,
  언어마다 포맷을 설정한다.

  


  아래는 Java에 대해서 설정을 해 주는 화면이다.

  


  이것저것 설정을 변경해보면서, 어떻게 문자열들을 만들어 주는지 확인해보자.



  • 반대로 "나 +를 제거하려면?

  소스에서 사용할 수 있도록 "나 +를 붙여주는 것과 반대로,
  "나 +를 제거해서 Toad의 SQL Editor에서 바로 실행할 수 있는 쿼리로
  만들어 주는 기능, 즉 Make Code와 반대되는 Strip Code 기능도 제공한다.

  소스에서 가져온 "나 +가 붙은 쿼리를 SQL Editor에 붙여넣고,
  메뉴에서 SQL Editor -> Strip Code Statement를 클릭하거나
  단축키 Ctrl+P 키를 입력하면, "나 +가 제거되고 실행할 수 있는 쿼리만이 남는다.

  자세한 내용은 Strip Code Statement 기능을 소개하는
  다른 글에서 설명하도록 하겠다.


출처 - http://www.soqool.com/servlet/board?cmd=view&cat=140&subcat=1410&seq=861