[mysql] 최대 성능 향상 방법

출처 :

---------------------------
mysql의최대성능향상방법
출처:tunelinux.co.kr
---------------------------

10.1버퍼크기조정
mysqld서버가사용하는기본버퍼크기는다음의명령으로알수있다.

shell>mysqld--help

이명령은모든mysqld옵션의목록과설정변수를보여준다.출력되는내용은기본값을
포함하고있으며다음과비슷하다.

Possiblevariablesforoption--set-variable(-O)are:
back_logcurrentvalue:5
connect_timeoutcurrentvalue:5
join_buffercurrentvalue:131072
key_buffercurrentvalue:1048540
long_query_timecurrentvalue:10
max_allowed_packetcurrentvalue:1048576
max_connectionscurrentvalue:90
max_connect_errorscurrentvalue:10
max_join_sizecurrentvalue:4294967295
max_sort_lengthcurrentvalue:1024
net_buffer_lengthcurrentvalue:16384
record_buffercurrentvalue:131072
sort_buffercurrentvalue:2097116
table_cachecurrentvalue:64
tmp_table_sizecurrentvalue:1048576
thread_stackcurrentvalue:131072
wait_timeoutcurrentvalue:28800

mysqld서버가현재가동중이면다음의명령을통해실제변수값을볼수있다.

shell>mysqladminvariables

각옵션은밑에서설명한다.버퍼크기,길이,스택크기는바이트이다.'K'(킬로바이트)
나'M'(메가바이트)를앞에붙여값을지정할수있다.예를들면16M는16메가바이트를
가리킨다.대소문자는구별하지않는다.16M와16m은같다.

-back_log
mysql이가질수있는최대연결요청의수.이것은mainmysql스레드가매우짧은시간
동안매우많은연결요청을받을때기능을한다.이때메인스레드가연결을체크하고새
로운스레드를시작하는데는약간의시간이걸린다.(그러나아주짧은시간임)back_log값
은mysql이순간적으로새로운요청에답하는것을멈추기전에이짧은시간동안얼마나
많은요청을쌓아두고있는지를지정한다.매우짧은시간동안매우많은연결이예상될때
만이값을증가시켜야한다.

다른말로이값은tcp/ip연결을받는listenqueue의크기이다.각운영체제마다이러한큐
의크기에한계가있다.Unixsystemcalllisten(2)매뉴얼페이지에자세한정보가있다.ba
ck_log값의한계는운영체제문서를확인해봐라.back_log를최대값보다더높여도효과가
없다.

-connect_timeout
Badhandshake에반응하기전에연결패킷을mysql서버에서기다리는시간.(초)

-join_buffer
(인덱스를사용하지않는조인의)full-join에서사용하는버퍼의크기.버퍼는두테이블사
이에서각full-join마다한번할당이된다.인덱싱을추가하지못할때조인버퍼를증가시
키면fulljoin의속도를향상시킬수있다.(일반적으로빠르게조인을하는가장좋은방법
은인덱스를추가하는것이다)

-key_buffer
인덱스블락은버퍼링되고모든스레드에서공유한다.키버퍼는인덱스블락에서사용하는
버퍼의크기이다.인덱스가많은테이블에서delete나insert작업을많이하면키버퍼값을
증가시키는것이좋다.더빠른속도를내려면LOCKTABLES를사용하자.[LockTables]
참고.

-max_allowed_packet
한패킷의최대크기.메시지버퍼는net_buffer_length바이트로초기화되지만필요하면최
대허용패킷바이트를증가시킬수있다.기본값은큰패킷을잡기에는작다.거대BLOB
컬럼을사용한다면값을증가시켜야한다.사용자가원하는최대blob만큼크게해야한다.

-max_connections
동시클라이언트숫자.mysqld가필요로하는파일지시자(descriptor)의숫자만큼값을늘려
야한다.밑에서파일디스크립터제한에대한내용을참고하자.

-max_connect_errors
호스트에서최대연결에러이상의interrupted연결이있으면더많은연결을위해호스트는
block화된다.FLUSHHOSTS명령으로호스트의block을해제할수있다.

-max_join_size
최대조인크기이상으로레크도를읽는조인을하면에러가난다.만약사용자가where문
을사용하지않고시간이많이걸리면서몇백만개의레코드를읽는조인을수행하려하면
이값을설정한다.

-max_sort_length
BLOB나TEXT값으로정열할때사용하는바이트의숫자.(각값중오직첫번째max_sort
_length바이트만사용된다.나머지는무시된다)

-net_buffer_length
질의에서통신버퍼가초기화되는크기.일반적으로바뀌지않지만매우적은메모리를가
지고있을때예상되는질의에맞게세팅할수있다.(이것은클라이언트에가는예상된sql
문의길이이다.질의문이이크기를넘으면버퍼는자동으로max_allowed_packet바이트까
지증가한다)

-record_buffer
순차적인검색을하는각스레드에서각검색테이블에할당하는버퍼크기.순차적인검색
을많이하면이값을증가시켜야한다.

-sort_buffer
정렬이필요한각스레드에서할당하는버퍼크기.orderby나groupby오퍼레이션을빠
르게하려면이값을증가시킨다.16.4[임시파일]참고.

-table_cache
모든스레드에서열수있는테이블의숫자.mysqld가필요로하는파일디스크립터의숫
자만큼이값을증가시켜라.mysql은각유일한오픈테이블에서두개의파일디스크립터가
필요하다.파일디스크립터제한을참고한다.테이블캐쉬가어떻게작동하는지는10.6[테
이블캐쉬]를참고한다.

-tmp_table_size
임시테이블이이값을넘으면mysql은"TheTabletbl_nameisfull"이라는에러메시지를
낸다.매우많은groupby질의를사용하면이값을증가시켜야한다.

-thread_stack
각스레드의스택사이즈.creash-metest(**역자주:데이터베이스의벤치마킹을하는테스
트입니다.말그대로데이터베이스를죽여주지요)에서잡히는많은제한은이값에달려있
다.기본값은일반적으로충분히크다.11장의[벤치마크]참조

-wait_timeout
연결을끊기전에연결활동(activity)을서버에서기다리는시간(초).

table_cache와max_connections는서버가열수있는최대파일갯수에영향을미친다.이
값을증가시키면운영시스템에서오픈파일디스크립터의per-process숫자의한계까지올
릴수있다.(**...imposedbyyouroperatingsystemontheper-processnumberof
openfiledescriptors.번역이이상하므로영문참고)
그러나많은시스템에서이한계를증가시킬수있다.이렇게하려면각시스템에서이한계
를변화시키는방법이매우다양하므로운영체제문서를참고해야한다.

table_cache는max_connections와관계가있다.예를들면200개의연결이있으면최소2
00*n의테이블캐쉬를가져야한다.여기서n은조인에서테이블의최대숫자이다.

mysql은매우유용한알고리즘을사용하기때문에일반적으로는매우적은메모리로사용
할수있으며메모리가많을수록성능이더많이향상된다.

많은메모리와많은테이블을가졌고중간정도숫자의클라이언트에서최대의성능을원한다
면다음과같이사용한다.

shell>safe_mysqld-Okey_buffer=16M-Otable_cache=128
-Osort_buffer=4M-Orecord_buffer=1M&

메모리가적고연결이많으면다음과같이사용한다.

shell>safe_mysqld-Okey_buffer=512k-Osort_buffer=100k
-Orecord_buffer=100k&

또는:

shell>safe_mysqld-Okey_buffer=512k-Osort_buffer=16k
-Otable_cache=32-Orecord_buffer=8k-Onet_buffer=1K&

매우많은연결이있을때mysqld가각연결마다최소한의메모리를사용하도록설정하지
않았다면"swappingproblems"문제가생길것이다.

mysqld에서옵션을바꾸었으면그것은서버의해당하는인스턴스에만영향을미친다는것
을기억하자.

옵션을바꾸었을때의효과를보기위해다음과같이해보자.

shell>mysqld-Okey_buffer=32m--help

마지막에--help옵션이들어간것을기억하자.그렇지않으면커맨드라인에서사용한옵
션의효력은출력에는반영되지않을것이다.


10.2메모리사용방법<메모리최적화>

아래에서설명하는목록은mysqld서버가메모리를사용하는방법에대해서나타내고있
다.메모리사용과관련된서버의변수이름이주어진다.


-키버퍼(변수key_buffer)는모든스레드에서공유한다.서버에서사용하는다른버퍼는
필요한대로할당이된다.

-각연결은각스레드마다의특정한공간을사용한다.스택(64k,변수thread_stack),연결
버퍼(변수net_buffer_length),result버퍼(변수net_buffer_length)등.연결버퍼와result
버퍼는필요할때max_allowed_packet까지동적으로증가된다.질의가수행될때현재의
질의문의복사문이또한할당이된다.
(**Whenaqueryisrunningacopyofthecurrentquerystringisalsoalloced.)

-모든스레드는같은기본메모리를공유한다.
-메모리맵은아직지원이안된다.(압축테이블을제외하고.그러나이것은다른이야기이
다)왜냐하면4GB의32비트메모리공간은대부분의대형테이블에서충분히크기가않기
때문이다.우리가64비트주소공간을가진시스템을가지게될때우리는메모리맵핑을
위한일반적인지원을추가할것이다.

-테이블에서순차적인검색을하는각요청은read버퍼에할당이된다.(변수record_buff
er)

-모든조인은한번에수행이되며대부분의조인은임시테이블을생성하지않고수행이
된다.대부분의테이블은메모리기반(HEAP)테이블이다.거대길이의레코드를가졌거나
BLOB컬럼을포함한임시테이블은디스크에저장이된다.현재의문제는메모리기반테
이블이tmp_table_size를초과했을때"Thetabletbl_nameisfull"이라는에러가생기는것
이다.가까운시일안에필요할때자동적으로메모리기반(HEAP)테이블을디스크기반(NI
SAM)테이블로바꾸도록고칠것이다.
이문제를해결하기위해서mysqld의tmp_table_size옵션을설정하여임시테이블크기를
늘이거나클라이언트프로그램에서SQL_BIG_TABLES라는sql옵션을설정하여야한다.7.
24SETOPTION을참고하자.
mysql3.20에서임시테이블의최대크기는record_buffer*16이다.3.20버전을사용하고있
다면record_buffer의값을증가시켜야한다.또한mysqld를시작할때--big-tables옵션을
사용하여항상임시테이블을디스크에저장할수있지만질의속도에영향을미친다.

-정열을하는대부분의요청은정렬버퍼와하나나두개의임시파일을할당한다.16.4의
[임시파일]을참고한다.

-대부분의파징(parsing)과계산은지역메모리에서이루어진다.작은아이템에는메모리o
verhead가필요없고일반적인느린메모리할당(slowmemoryallocation)과freeing(메모리
해제)는무시된다.메모리는오직예상지못한거대문자열에서할당이된다.(mallloc()과
free()사용)

-각인덱스파일은한번에열리며각병행수행되는스레드에서데이터파일은한번에열
린다.각병행수행스레드마다테이블구조,각컬럼의컬럼구조,3*n의버퍼크기가할
당된다.(n은최대레코드길이이며BLOB컬럼은해당하지않는다)BLOB는BLOB데이
터의길이에5에서8바이트를더한값을사용한다.

-BLOB컬럼을가진각테이블에서버퍼는거대BLOB값을읽을수있도록동적으로커
진다.테이블을검색하면버퍼는최대BLOB의값만큼버퍼가할당이된다.

-모든사용중인테이블의테이블핸들러는캐쉬에저장되며FIFO로관리가된다.일반적
으로캐쉬는64엔트리를갖는다.동시에두개의실행스레드에서테이블을사용하면캐쉬
는테이블의두엔트리를포함한다.10.6[테이블캐쉬]를참고한다.

-mysqladminflush-tables명령은사용하지않는모든테이블을닫고현재실행되는스레
드가끝날때모든사용중인테이블을닫는다고표시한다.이것은효과적으로사용중인메
모리를해제한다.


ps와다른시스템상황프로그램은mysqld가많은메모리를사용하고있다고보고할것이
다.이것은다른메모리주소의스레드-스택때문에생긴다.예를들면솔라리스의ps는스
택사이의사용하지않는메모리를사용하는메모리로간주한다.이것은swap-s를이용사
용가능한스왑을체크하여확인할수있다.우리는mysqld를상용메모리유출측정프로그
램으로테스팅해서mysqld에는메모리유출이없다.


10.3속도향상에영향을미치는컴파일/링크방법<컴파일시최적화하기>

다음테스트의대부분은리눅스와mysql벤치마크를가지고수행되었지만다른운영시스
템에도암시해주는것이있다.

static으로링크를할때가장빠른실행속도를얻을수있다.데이터베이스에연결하기위
해TCP/IP보다는유닉스소켓을사용하면더좋은성능을낼수있다.

리눅스에서pgcc와-O6을사용하면가장빠르다.'sql_yacc.cc'를이옵션으로컴파일하려면
gcc/pgcc는모든성능을내기위해많은메모리가필요하기때문에180M의메모리가필요
하다.또한mysql을설정할때libstdc++라이브러리를포함하지않기위해CXX=gcc라고설
정해야한다.

-pgcc를사용하고모두다-O6옵션으로컴파일하면mysqld서버는gcc로컴파일한것보
다11%빨라진다.

-동적으로링크하면(-static을사용하지않고)13%느려진다.
IfyouconnectusingTCP/IPratherthanUnixsockets,theresultis7.5%slower.
-유닉스소켓을사용하는것보다tcp/ip로연결하는것이7.5%느려진다.

-OnaSunsparcstation10,gcc2.7.3is13%fasterthanSunProC++4.2.
-OnSolaris2.5.1,MIT-pthreadsis8-12%slowerthanSolarisnativethreads.
(**번역을안한이후.리눅스랑상관없으니깐...**)

TcX에서제공한mysql리눅스배포판은pgcc로컴파일되었고정적으로링크되었다.


10.4HowMySQLusesindexes

prefix-andend-spacecompressed.Seesection7.26CREATEINDEXsyntax(Compatibil
ityfunction).

모든인덱스(PRIMARY,UNIQUEandINDEX())는B-trees에저장된다.문자열은자동적
으로앞뒤의공간(?)이압축된다.7.26[인덱스생성]참고.

인덱스의사용:
-WHERE문에서해당하는레코드빨리찾기
-조인을수행할때다른테이블에서레코드가져오기
-특정키에서MAX()나MIN()값찾기
-소팅이나그룹화할때인덱스키를사용하면테이블을정열하거나그룹화한다.키에DES
C가붙으면역순으로인덱스를읽는다.
-어떤경우에는데이터파일에묻지않고값을가져온다.어떤테이블에서사용하는모든
컬럼이숫자이고특정키로형성되어있으면빠른속도로인덱스트리에서값을가져올수
있다.

다음예제를보자.

mysql>SELECT*FROMtbl_nameWHEREcol1=val1ANDcol2=val2;


다중컬럼인덱스가col1과col2에있으면해당하는레코드를직접가져올수있다.분리
된단일컬럼인덱스가col1과col2에있으면최적화기는어떤인덱스가더적은레코드
를가졌는지확인하고레코드를가져오기위해그인덱스를사용하도록결정한다.

테이블이다중컬럼인덱스를가졌다면최적화기가레코드를찾는데어떤인덱스키를사용
할수있다.예를들면세가지컬럼인덱스(col1,col2,col3)를가졌다면(col1),(col1,col2)
(col1,col2,col3)인덱스를사용하여검색을할수있다.

MySQLcan'tuseapartialindexifthecolumnsdon'tformaleftmostprefixoftheinde
x.
SupposeyouhavetheSELECTstatementsshownbelow:
(**해석이잘안되는데예제를보시면무슨말인지알수있을것임**)

mysql>SELECT*FROMtbl_nameWHEREcol1=val1;
mysql>SELECT*FROMtbl_nameWHEREcol2=val2;
mysql>SELECT*FROMtbl_nameWHEREcol2=val2ANDcol3=val3;

Ifanindexexistson(col1,col2,col3),onlythefirstqueryshownaboveusestheindex.
Thesecondandthirdqueriesdoinvolveindexedcolumns,but(col2)and(col2,col3)are
notleftmostprefixesof(col1,col2,col3).

인덱스가(col1,col2,col3)로있다면위의질의중오직첫번째질의만인덱스를사용한다.두
번째및세번째질의은인덱스된컬럼이포함되어있지만(col2)와(col2,col3)는(col1,col2,c
ol3)인덱스에해당하지않는다.

MySQLalsousesindexesforLIKEcomparisonsiftheargumenttoLIKEisaconstant
stringthatdoesn'tstartwithawildcardcharacter.Forexample,thefollowingSELECT
statementsuseindexes:

mysql은또한LIKE의인수가와일드카드문자로시작하지않는상수문자열일이라면LIK
E비교문에서인덱스를사용한다.예를들어다음의SELECT문은인덱스를사용한다.

mysql>select*fromtbl_namewherekey_colLIKE"Patrick%";
mysql>select*fromtbl_namewherekey_colLIKE"Pat%_ck%";

첫번째문장에서는"Patrick"<=key_col<"Patricl"을가진레코드만고려된다.두번째문
장에서는"Pat"<=key_col<"Pau"을가진레코드만고려된다.


다음의SELECT문은인덱스를사용하지않는다:

mysql>select*fromtbl_namewherekey_colLIKE"%Patrick%";
mysql>select*fromtbl_namewherekey_colLIKEother_col;

첫번째문장에서LIKE값은와일드카드문자로시작하고있다.두번째문장에서는LIKE
값이상수가아니다.



10.5WHERE문에서최적화하기
(이번절은완전한내용을포함하고있지는않다.mysql은많은최적화방법이있다.)

Ingeneral,whenyouwanttomakeaslowSELECT...WHEREfaster,thefirstthingt
ocheckiswhetherornotyoucanaddanindex.Allreferencesbetweendifferenttables
shouldusuallybedonewithindexes.YoucanusetheEXPLAINcommandtodetermine
whichindexesareusedforaSELECT.Seesection7.21EXPLAINsyntax(Getinformat
ionaboutaSELECT).
일반적으로느린SELECT...WHERE문을빠르게하려면가장먼저확인해야할것이인
덱스추가문제이다.다른테이블사이에서모든레퍼런스(references참조)는일반적으로인
덱스에의해수행된다.SELECT문에서어떤인덱스를사용하는지결정하기위해EXPLAI
N명령을사용할수있다.7.21[Explain]을참고.

mysql에서수행하는최적화는다음과같다.


-불필요한삽입어제거

((aANDb)ANDcOR(((aANDb)AND(cANDd))))
->(aANDbANDc)OR(aANDbANDcANDd)

-상수폴딩(folding)

(a->b>5ANDb=cANDa=5

-상수조건제거(상수폴딩때문에필요)

(B>=5ANDB=5)OR(B=6AND5=5)OR(B=7AND5=6)
->B=5ORB=6

-인덱스에서사용되는상수표현은한번에계산된다.
(Constantexpressionsusedbyindexesareevaluatedonlyonce.)

-WHERE절이없는단일테이블의COUNT(*)는테이블정보에서직접값을가져온다.
단일테이블에서사용된NOTNULL표현도이와같이수행된다.

-유효하지않은상수표현은미리제거된다.mysql은불가능하고해당하는레코드가없는
SELECT문을빠르게감지한다.

-GROUPBY나그룹펑션(COUNT(),MIN()...)을사용하지않으면HAVING은WHERE
에합쳐진다.
(**HAVING절에서는인덱스를사용하지못함.그러므로가능한HAVING절을사용하지
않는게속도면에서좋다**)

-각서브조인에서빠르게WHERE문을계산하고가능한한레코드를제외하도록간소하
게WHERE문이만들어진다.

-mysql은일반적으로최소한의레코드를찾기위해인덱스를사용한다.=,>,>=,<,<=,
BETWEEN그리고'something%'처럼앞이와일드카드로시작하지않는LIKE문등을
사용하여비교를할때인덱스를사용한다.(**10.4절에서설명하였듯이like를사용할때
와일드카드로시작하는like문을사용하면인덱스를사용하지않는다.일정한단어로만시
작하는컬럼에서자료를찾을때유용할것이다.**)

-Anyindexthatdoesn'tspanallANDlevelsintheWHEREclauseisnotusedtoopti
mizethequery.

다음의WHERE문은인덱스를사용한다.:

...WHEREindex_part1=1ANDindex_part2=2
...WHEREindex=1ORA=10ANDindex=2/*index=1ORindex=2*/
...WHEREindex_part1='hello'ANDindex_part_3=5
/*optimizedlike"index_part1='hello'"*/

다음의WHERE문은인덱스를사용하지않는다.:

...WHEREindex_part2=1ANDindex_part3=2/*index_part_1isnotused*/
...WHEREindex=1ORA=10/*Noindex*/
...WHEREindex_part1=1ORindex_part2=10/*Noindexspansallrows*/

-질의에서다른테이블보다모든상수테이블을먼저읽는다.상수테이블은다음과같다.
ㅇ빈테이블이나1개의레코드만있는테이블
ㅇWHERE문에서UNIQUE인덱스나PRIMARYKEY를사용하고모든인덱스
는상수표현으로된테이블

다음의테이블은상수테이블로사용된다.

mysql>SELECT*FROMtWHEREprimary_key=1;
mysql>SELECT*FROMt1,t2
WHEREt1.primary_key=1ANDt2.primary_key=t1.id;

-모든가능성을시도하여테이블을조인하는데가장좋은조인조합을찾는다.(ORDERB
Y나GROUPBY의모든컬럼이동일한테이블에서나오면조인을할때이테이블이먼저
선택된다)

-ORDERBY문과다른GROUPBY문이있을때,또는ORDERBY나GROUPBY가
조인큐의첫번째테이블이아닌다른테이블의컬럼을포함하고있으면임사테이블을만
든다.

-각테이블인덱스를찾고레코드의30%미만을사용하는(best)인덱스가사용된다.그런
인덱스가없으면빠른테이블검색이사용된다.

-어떤경우에는mysql은데이터파일을조회하지않고인덱스에서레코드를읽을수있
다.인덱스에서사용한모든컬럼이숫자라면질의를처리하는데단지인덱스트리만을사
용한다.

-각레코드가출력되기전에HAVING절에맞지않는레코드는건너뛴다.

다음은매우빠른질의의예이다:

mysql>SELECTCOUNT(*)FROMtbl_name;
mysql>SELECTMIN(key_part1),MAX(key_part1)FROMtbl_name;
mysql>SELECTMAX(key_part2)FROMtbl_name
WHEREkey_part_1=constant;
mysql>SELECT...FROMtbl_name
ORDERBYkey_part1,key_part2,...LIMIT10;
mysql>SELECT...FROMtbl_name
ORDERBYkey_part1DESC,key_part2DESC,...LIMIT10;

다음의커리는인덱스트리만을사용하여값을구한다.(인덱스컬럼은숫자라고가정):

mysql>SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_part1=val;
mysql>SELECTCOUNT(*)FROMtbl_name
WHEREkey_part1=val1andkey_part2=val2;
mysql>SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;

다음의질의는개별적인정열을하지않고정열된순서대로열을가져오는데인덱스를사
용한다:

mysql>SELECT...FROMtbl_nameORDERBYkey_part1,key_part2,...
mysql>SELECT...FROMtbl_nameORDERBYkey_part1DESC,key_part2DESC,...


10.6테이블열고닫는방법

open테이블의캐쉬는table_cache의최대값까지커질수있다.(기본값64;이값은mysql
d에서-0table_cache=#으로바꿀수있다)캐쉬가꽉찼을때,그리고다른스레드가테이
블을열려고할때,또는mysqladminrefresh나mysqladminflush-tables를사용할때를제
외하고는테이블은결코닫히지않는다.

테이블캐쉬가꽉차면서버는캐쉬엔트리를사용하도록조절하기위해다음의절차를사
용한다.

-가장먼저사용했던순서대로현재사용하지않는테이블을닫는다.
-캐쉬가꽉찼고어떤테이블도닫히지않지만새로운테이블을열어야한다면캐쉬가필
요한만큼임시적으로확장된다.
-캐쉬가임시적으로확장된상태이고테이블을사용할수없는상황으로가면테이블을
닫고캐쉬를해제한다.

테이블은각동시병행적인접근때마다열린다.동일한테이블에접근하는두개의스레드가
있거나같은질의에서테이블에두번접근하면(withAS)테이블을두번열여야한다는의
미이다.테이블의첫번째개방은두개의파일디스크립터를가진다.;추가적인테이블의개
방은하나의파일디스크립터를가질뿐이다.처음에개방에사용하는추가적은파일디스
크립터는인덱스파일에사용된다.;이디스크립터는모든스레드에서공유된다.


10.6.1데이터베이스에서많은수의테이블을만들때의단점

디렉토리에많은파일이있다면open,close그리고create오퍼레이션은느려질것이다.서
로다른많은테이블에서SELECT문을수행하면테이블캐쉬가꽉찰때약간의overhea
d가있을것이다.왜냐면개방된테이블이있다면다른테이블은닫혀야하기때문이다.테
이블캐쉬를크게해서이러한오우버헤드를줄일수있다.


10.7많은테이블을여는이유

mysqladminstatus를실행할때다음과같이나올것이다:

Uptime:426Runningthreads:1Questions:11082Reloads:1Opentables:12

단지6테이블을사용했는데이러한결과는당황스러울것이다.

mysql은멀티스레드를사용한다.그래서동시에같은테이블에서많은질의를할수있다.
같은파일에대하여다른상황을가지는두개의스레드에대한문제를줄이기위해테이블
은각동시병행적인스레드마다독립적으로개방된다.이것은테이타파일에서약간의메모
리와하나의추가적인파일디스크립터를사용한다.모든스레드에서인덱스파일은공유된
다.


10.8데이터베이스와테이블에서심볼릭링크사용

데이터베이스디렉토리에서테이블과데이터베이스를다른위치로옮기고새로운위치로심
볼릭링크를사용할수있다.이렇게하는것을원할경우가있다.예를들면데이터베이스
를더여유공간이많은파일시스템으로옮기는경우등.

mysql에서테이블이심볼링링크되었다는것을감지하면심볼링링크가가리키는테이블을
대신사용할수있다.realpath()call을지원하는모든시스템에서작동한다.(최소한리눅
스와솔라리스는realpath()를지원한다)realpath()를지원하지않는시스템에서동시에실
제경로와심볼릭링크된경로에접근하면안된다.이런경우에는업데이트된후에테이블
이모순될수있다.

mysql은기본값으로데이터베이스링크를지원하지않는다.데이터베이스간에심볼릭링크
를사용하지않는작동을잘할것이다.mysql데이터디렉토리에db1데이터베이스가있
고db1을가리키는db2심볼릭링크를만들었다고해보자:

shell>cd/path/to/datadir
shell>ln-sdb1db2

이제db1에tbl_a라는테이블이있다면db2에도tbl_a가나타날것이다.한스레드가db1.tbl
_a를업데이트하고다른스레드가db2.tbl_a를업데이트하면문제가생길것이다.

정말로이기능이필요하면,`mysys/mf_format.c'에서다음의코드를수정해야한다.:

if(!lstat(to,&stat_buff))/*Checkifit'sasymboliclink*/
if(S_ISLNK(stat_buff.st_mode)&&realpath(to,buff))

위코드를다음과같이수정한다:

if(realpath(to,buff))


10.9테이블에락거는방법

mysql의모든락은deadlock-free이다.언제나질의를시작할때한번에모든필요한락을
요청하고언제나같은순서대로테이블에락을걸어관리한다.

WRITE락을사용하는방법은다음과같다:

-테이블에락이없으면그테이블에write락을건다.
-이런경우가아니라면write락큐에락을요청한다.

READ락을사용하는방법은다음과같다:

-테이블에write락이없으면그테이블에read락을건다.
-이런경우가아니라면read락큐에락을요청한다.

락이해제되었을때락은write락큐의스레드에서사용할수있으며그러고나서read락
큐의스레드에서사용한다.

테이블에서업데이트를많이하면SELECT문은더이상업데이트가없을때까지기다린
다는것을의미한다.

이러한문제를해결하기위해테이블에서INSERT와SELECT오퍼레이션을많이사용하
는경우에다음과같이하면된다.임시테이블에레코드를입력하고한번에임시테이블에
서실제테이블로레코드를업데이트한다.

다음의예를보자:

mysql>LOCKTABLESreal_tableWRITE,insert_tableWRITE;
mysql>insertintoreal_tableselect*frominsert_table;
mysql>deletefrominsert_table;
mysql>UNLOCKTABLES;

만약어떤경우에SELECT문에우선권을주고싶다면INSERT옵션에서LOW_PRIORITY
orHIGH_PRIORITY옵션을사용할수있다.7.13[Insert]참고.(**LOW_PRIORITY를지
정하면클라이언트에서테이블을읽지않을때까지INSERT문수행이미루어진다.**)

단일큐를사용하기위해`mysys/thr_lock.c'의락킹코드를바꿀수있다.이런경우writ
e락과read락은같은우선권을가지며어떤애플리케이션에서는유용할수있다.

10.10테이블을빠르고작게배열하는방법<**테이블최적화**>

다음은테이블에서최대의성능을내는방법과저장공간을절약할수있는테크닉이다:

-가능한한NOTNULL로컬럼을선언한다.속도가빨라지며각컬럼마다1비트를절약할
수있다.
-default값을가질때유리하다.입력되는값이기본값과다를때만확실하게값이입력된
다.INSERT문에서첫번째TIMESTAMP컬럼이나AUTO-INCREAMENT컬럼의값을
입력할필요가없다.18.4.49[mysql_insert_id()]참고.
-가능한한테이블을작게만드려면더작은integer타입을사용하자.예를들면MEDIUM
INT가보통INT보다좋다.
-가변길이컬럼이없다면(VARCHAR,TEXTorBLOBcolumns),고정길이레코드포
맷이사용된다.이경우속도는더빠르지만불행히도(흑흑~)낭비되는공간이더많다.10.1
4[Rowformat]참고.
-mysql이질의를효과적으로최적화하기위해많은양의데이터를입력한후isamchk--a
nalyze를실행하자.이렇게하면동일한값을가진줄의평균숫자를가리키는각인덱스의
값을업데이트한다.(물론unique인덱스에서는항상1이다)
-인덱스와인덱스에따른데이타를정열하려면
isamchk--sort-index--sort-records=1을사용하자.(ifyouwanttosortonindex1).
인덱스에따라정렬된모든레코드를읽기위해unique인덱스를가졌다면이렇게하는것
이속도를빠르게하는가장좋은방법이다.
-INSERT문에서가능한다중값목록을사용하자.개별적인SELECT문보다훨씬빠르
다.데이타를테이블에입력할때LOADDATAINFILE을사용하자.많은INSERT문을
사용하는것보다보통20배빠르다.7.15[Load]참고.

많은인덱스를가진테이블에데이타를입력할때다음의과정을사용하면속도를향상시킬
수있다.
1.mysql이나Perl에서CREATETABLE로테이블을만든다.
2.mysqladminflush-tables실행.(**열린테이블을모두닫음**)
3.isamchk--keys-used=0/path/to/db/tbl_name사용.테이블에서모든인덱스사용을제
거한다.
4.LOADDATAINFILE를이용테이블에데이타를입력.
5.pack_isam을가지고있고테이블을압축하기원하면pack_isam을실행.
6.isamchk-r-q/path/to/db/tbl_name를이용인덱스를다시생성.
7.mysqladminflush-tables실행.

-LODADATAINFILE과INSERT문에서더빠른속도를내려면키버퍼를증가시킨
다.mysqld나safe_mysqld에서-Okey_buffer=#옵션을사용하면된다.예를들어16M는
풍부한램을가졌다면훌륭한값이다.
-다른프로그램을사용하여데이타를텍스트파일로덤프할때SELECT...INTOOUTFIL
E을사용하자.7.15[LOADDATAINFILE]참고.
-연속으로다량의insert와update를할때LOCKTABLE을사용하여테이블에락을걸
면속도를향상시킬수있다.LOADDATAINFILE그리고SELECT...INTOOUTFILE
는원자적이기때문에LOCKTABLE을사용하면안된다.7.23[LOCKTABLES/UNLOCK
TABLES]참고.

테이블이얼마나단편화되었는지점검하려면'.ISM'파일에서isamchk-evi를실행한다.1
3장[Maintenance]참고.



10.11INSERT문에서속도에영향을미치는부분<**insert최적화**>

insert하는시간은다음와같이구성된다:

Connect:(3)
Sendingquerytoserver:(2)
Parsingquery:(2)
Insertingrecord:(1xsizeofrecord)
Insertingindexes:(1xindexes)
Close:(1)

(숫자)는비례적인시간이다.이것은테이블을개방할때초기의overhead를고려하고있지
는않다.(매동시병행적으로수행되는질의마다발생)

ThesizeofthetableslowsdowntheinsertionofindexesbyNlogN(B-trees).


테이블의크기는NlogN(B-trees)에따라인덱스의입력이느려진다.(**말이좀이상.테
이블이커짐에따라인덱스생성도느려진다는뜻이겠죵**)

테이블에락을걸거나insert문에서다중값목록을사용하여입력속도를빠르게할수
있다.다중값목록을사용하면단일insert보다5배정도속도가빨라진다.

mysql>LOCKTABLESaWRITE;
mysql>INSERTINTOaVALUES(1,23),(2,34),(4,33);
mysql>INSERTINTOaVALUES(8,26),(6,29);
mysql>UNLOCKTABLES;

주요한속도차이는모든INSERT문이완료되고난후에한번에인덱스버퍼가쓰여기지
때문에생긴다.보통서로다른여러INSERT문이있으면많은인덱스버퍼플러쉬가있
을것이다.모든줄을단일문으로입력하면락은필요없다.

락킹은또한다중연결테스트의총시간을줄일수는있다.그러나어떤스레드에서는총
대기시간은증가할수있다.(왜냐면락을기다리기때문이다)
예를들어보자:

thread1does1000inserts
thread2,3,and4does1insert
thread5does1000inserts

락을사용하지않으면2,,34는1과5전에끝마칠것이다.락을사용하면2,3,4는아마도1
이나5전에끝나지않을것이다.그러나총시간은40%빨라진다.

INSERT,UPDATE,DELETE오퍼레이션은mysql에서매우빠르다.그렇기때문에줄에서
5개이상의insert나update를할때락을추가하면더좋은성능을얻을수있다.줄에매
우많은자료를입력한다면다른스레드에서테이블에접근하도록하기위해때때로(각100
0줄마다)UNLOCKTABLES를사용하는LOCKTABLES실행하면된다.이렇게하면좋
은성능을낼수있다.(**열심히입력을하고중간에락을풀었다가다시락을거는것
반복함**)

물론LOADDATAINFILE이더빠르다.



10.12DELETE문에서속도에영향을미치는부분<**DELETE문최적화**>

레코드를삭제하는시간은정확히인덱스숫자에비례한다.레코드를빠르게지우기위해
인덱스캐쉬의크기를증가시킬수있다.기본인덱스캐쉬는1M이다;빠르게삭제하기위
해증가되어야한다.(충분한메모리를가지고있다면16M로하자)


10.13mysql에서최대속도를얻는방법

벤치마킹을시작하자!mysql벤치마크스위트에서어떤프로그램을사용할수있다.(일반
적으로'sql-bench'디렉토리에있음)그리고입맞에맞게수정하자.이렇게하면당신의문
제를해결할수있는다른해결책을찾을수있으며당신에게가장빠른해결책을테스트할
수있다.

-mysqld를적절한옵션으로시작하자.메모리가많을수록속도가빠르다.
10.1[MySQLparameters]참고.
-SELECT문의속도를빠르게하기위해인덱스를만들자.
10.4[MySQLindexes]참고.
-가능한효율적으로컬럼타입을최적화하자.예를들면가능한NOTNULL로컬럼을정
의하자.10.10[Tableefficiency]참고.
---skip-locking옵션은SQL요청에서파일락킹을없앤다.속도가빨라지지만다음의과
정을따라야한다:
ㅇisamchk로테이블을체크하거나수리하기전에mysqladminflush-tables로모
든테이블을플러시해야한다.(isamchk-dtbl_name은언제나허용된다.왜냐하면이건단
순히테이블의정보를보여주기때문이다)
ㅇ동시에뜬두개의mysql서버가동일한테이블을업데이트하려한다면동일한
데이터파일에두개의mysql서버를띄우면안된다.

--skip-locking옵션은MIT-pthreads로컴파일할때기본값이다.왜냐면모든플랫
폼의MIT-pthreads에서flock()가완전하게지원이되지않기때문이다.

-업데이트에문제가있다면업데이트를미루고나중에하자.많은업데이트를하는것이
한번에하나를업데이트하는것보다더빠르다.
-FreeBSD시스템에서MIT-pthreads에문제가있으면FreeBSD3.0이후버전으로업데
이트하는것이좋다.이렇게하면유닉스소켓을사용하는것이가능하며(FreBSD에서유닉
스소켓이MIT-pthreads에서TCP/IP연결을사용하는것보다빠르다)그리고스레드패키
지가조정(intergrated?)되어야한다.
-테이블이나컬럼단계를체크하는GRANT는성능을떨어뜨린다.


10.14로우포맷과다른점은무엇인가?언제VARCHAR/CHAR을사용해야하는가?

mysql은실제의SQLVARCHAR타입이없다.그대신mysql은레코드를저장하고이것을
VARCHAR로에뮬레이트하는데세가지방법이있다.

테이블에VARCHAR,BLOB,TEXT컬럼이없으면고정rowsize를사용한다.그외에는
동적rowsize를사용한다.CHAR과VARCHAR컬럼은애플리케이션의관점에서동일하
게취급된다;둘다trailingspace는컬럼을가져올때제거된다.

isamchk-d를이용테이블에서사용하는포맷을체크할수있다.
(-d는"테이블묘사"를의미)

mysql은세가지다른테이블포맷을가지고있다;고정길이,다이나믹,압축.


고정길이테이블
-기본포맷.테이블에VARCHAR,BLOB,TEXT컬럼이없을때사용.
-모든CHAR,NUMERIC,DECIMAL컬럼은컬럼길이에space-padded이다.(**space-
padded를무엇이라고번역해야할지애매모호해서**)
-매우빠름
-캐쉬하기쉽다
-손상후복구가쉽다.왜냐면고정된위이에레코드가위치하기때문이다.
-많은양의레코드가지워졌거나운영시스템에서자유공간을늘리길원치않는다면(isa
mchk를이용)재조직화할필요없다.
-보통다이나믹테이블보다많은디스크공간을필요로한다.


다이나믹테이블
-테이블이VARCHAR,BLOB,TEXT컬럼을포함하고있을때사용.
-모든문자열컬럼은다이나믹하다.(4보다작은길이를가진문자열제외)
-컬럼이문자열컬럼에서비었거나(')숫자형컬럼에서0(NULL값을가진컬럼과동일
한것이아니다)을나타내는비트맵이모든레코드앞에선행된다.문자열컬럼에서trailin
gspace를제거한후zero의길이를가지거나숫자형컬럼이zero의값을가지면비트맵으
로표시되고디스크에저장되지않는다.비지않은문자는문자내용에길이바이트만큼추
가되어저장된다.
-보통고정길이테이블보다디스크공간절약.
-줄의길이를확장하는정보를가지고줄을업데이트하면줄은단편화될것이다.이런경
우더좋은성능을위해때때로isamchk-r을실행해야한다.통계적으로(?)isamchk-ei
tbl_name을사용하자.
-손상후복구가어렵다.왜냐면레코드가많은조각드로단편화되고링크(단편)가없어지
기때문이다.
-다이나믹사이즈테이블의예상되는열길이:
3
+(numberofcolumns+7)/8
+(numberofcharcolumns)
+packedsizeofnumericcolumns
+lengthofstrings
+(numberofNULLcolumns+7)/8

각링크마다6바이트가더있다.다이나믹레코드는업데이트로레코드가늘어날때마다링
크된다.각새로운링크는최소20바이트일것이며,그래서다음의확장은아마도동일한링
크로될것이다.그게아니라면다른링크가있을것이다.isamchk-ed로얼마나많은링
크가있는지체크할수있다.모든링크는isamchk-r로제거할수있다.(**??**)

Thereisapenaltyof6bytesforeachlink.Adynamicrecordislinkedwheneveranup
datecausesanenlargementoftherecord.Eachnewlinkwillbeatleast20bytes,soth
enextenlargementwillprobablygointhesamelink.Ifnot,therewillbeanotherlink.
Youmaycheckhowmanylinkstherearewithisamchk-ed.Alllinksmayberemoved
withisamchk-r.


압축테이블

-읽기전용테이블은pack_isam유틸리티로만들수있다.확장mysql이메일지원을구
입한모든고객은내부적인용도로pack_isam을사용할권리가주어진다.
-압축해제코드는모든mysql배포판에있으므로pack_isam이없는고객도pack_isam으
로압축된테이블을읽을수있다.(테이블이같은플랫폼에서압축되어있는한)
-매우적은디스크용량을사용.
-각레코드는개별적으로압축이된다.(매우적은액세스overhead)레코드의헤더는테
이블의가장큰레코드에따라(1-3바이트)고정된다.각컬럼은다르게압축이된다.압축
타입은다음과같다:

ㅇ일반적으로각컬럼마다다른Huffman테이블이다.
ㅇSuffic공간압축
ㅇPrefix공간압축
ㅇ0값을가진숫자는1비트로저장.
ㅇinteger컬럼의값이작은범위를가졌다면,컬럼은최대한작은타입으로저장
된다.예를들면BIGINT컬럼은모든값이0부터255라면TINIINT컬럼(1바이트)로저장
된다.
ㅇ컬럼이몇가지가능한값으로만구성되어있다면,컬럼타입은ENUM으로변환
된다.
ㅇ컬럼은위압축방법을조합하여사용한다.
-고정길이나다이나믹길이의테이블을다룰수있다.그러나BLOB나TEXT컬럼은다
룰수없다.
-isamchk로압축을해재할수있다.

mysql은다른인덱스타입을지원한다.그러나일반적인타입은NISAM이다.이것은B-tre
e인덱스이며모든키의갑을합하여(키길이+4)*0.67로인덱스파일의크기를대강계산
할수있다.(이것은모든키가정렬된순서로입력된가장나쁜경우이다)


Stringindexesarespacecompressed.Ifthefirstindexpartisastring,itwillalsobep
refixcompressed.Spacecompressionmakestheindexfilesmallerifthestringcolumnh
asalotoftrailingspaceorisaVARCHARcolumnthatisnotalwaysusedtothefull
length.Prefixcompressionhelpsiftherearemanystringswithanidenticalprefix.

문자열인덱스는공간이압축된다.첫번째인덱스부분이문자열이라면,prefix가압축된다.
문자열컬럼이다량의trailingspace를가졌거나언제나완전한길이를사용하지않는VA
RCHAR컬럼일때space압축은인덱스파일을더작게만든다.prefix압축은많은문자
열에동일한prefix가있을때유용하다.