(─━┘_└━─)/ 머리만1톤(Head1Ton)

http://mysqldba.tistory.com/158

http://mysqldba.tistory.com/154

MySQL에서 character set과 collation을 설정하는 방법에 대해 작성한 문서입니다.

MySQL은 다른 DB와 다르게 collation이라는 속성을 가지고 있어서 문자 데이터 타입에 대해 어떻게 다룰 것인가를 명시합니다.

처음에 시작하는 분들은 이 개념을 좀 어려워 하십니다.

하지만, 그리 어렵게 생각하지 않으셔도 됩니다. 그냥 문자 데이터 타입을 다루는 방법을 지정했다고 생각하심 됩니다.

그리고 그냥 XXXX_ci만 아님 된다~~ 라고 간단히 생각 하셔도 됩니다.

뭐 깊게 들어가면 더 얘기할게 많지만...ㅋㅋ 그냥 여기서 끝~!!!!

http://mysqldba.tistory.com/125

MySQL Ver.5.1 까지는 reset slave라는 구문을 사용하여 Slave에서 replication 정보를 삭제하여 연결을 끊게 할 수 있었다. 

하지만, 몇몇 버전에서는 몇몇 설정 정보만 사라지고 완벽히 연결 정보를 모두 삭제할 수 없는 경우도 있었다. 

이와 같은 경우에

stop slave; CHANGE MASTER TO MASTER_HOST=''; reset slave; 

위와 같이 하면 깔끔하게 삭제 하는 것이 가능했다. 

MySQL Ver.5.5 부터는 위와 같이 하지 않아도 깔끔하게 삭제하는 것이 가능하다. reset slave 구문의 기능이 2가지로 나뉘었기 때문이다. 

하나는 기존과 같이 초기화 정보로 reset하는 reset slave와 replication 했다는 정보까지 없애주는 reset slave all;이다. 

즉, Ver. 5.5 부터는

stop slave; reset slave all;

하면 위의 구문과 같은 효과를 보게 된다. 

http://mysqldba.tistory.com/81

http://mysqldba.tistory.com/80

http://mysqldba.tistory.com/79

원문은 다음과 같습니다. 

http://dev.mysql.com/tech-resources/articles/mysql56-labs-july2011.html

 여기서는 5.6 버젼에 추가된 InnoDB와 Replication 기능에 대해 간단히 알아보도록 하자. 

InnoDB Improvements. 
 
InnoDB는 MySQL의 default Storage Engine으로 community와 customer의 요청에 따라 다음의 기능들이 추가되었다. 

InnoDB Full-Text Search

InnoDB에 저장된 text-based context에 대해 검색하고 찾을 수 있는 기능을 제공한다. 이 새로운 기능은 natural Language, Boolean, Query expansion(wildcard)와 proximity search 알고리즘과 옵션을 제공하여 사용할 수 있다. 

InnoDB Redo Log Files max size increased up to 2 TB. 

이제 InnoDB Redo Log 파일을 4G부터 2TB까지 생성하는것이 가능하다.  데이터변경 부하가 심한 서버의 경우 성능 개선에 좋은 영향을 줄거라 기대할 수 있다. 

InnoDB UNDO logs to reside in ther own tablespace. 

현재 InnoDB의 Undo Log은 InnoDB의 system tablespace내에 저장된다. 5.6에서부터는 undo log부분을 system tablespace에서 분리하여 따로 생성할 수 있게 되었다. 이 기능을 이용하여 undo 영역을 다른 디스크 영역에 분리하여 저장할 수 있다. 

See Sunny Bains' blog for details

InnoDB Buffer Pool options for pre-loading/warming on re-start

 MySQL이 restart가 되는 경우 자동적으로 buffer pool에 특정 데이터를 로딩할 수 있는 기능이 추가되었다. 이 dump process는 빠르고 경제적이기 때문에 서버에 적은 오버헤드를 주어서 사용하기에 좋다. 이 작업 자체는 background로 진행되기 때문에 start작업에 영향을 주지 않는다. 

See Vasil Dimov's blog for details.

InnoDB .ibd files imporved auto-extension

InnoDB의 innodb_file_per_table옵션을 사용했을 경우 각 table tablespace공간을 확장시키는 기능이 향상되었다. 이 설정을 사용하면 InnoDB의 많은 기능을 사용할 수 있다. 

See Inaam Rana's blog for details.

InnoDB Page Size from 4K to 64K

이 기능은 SSD나 flash에 최적화되어 InnoDB의 page를 설정할 수 있게 해준다. (아직은 현 버전에 추가되지 않았다.) 

See Kevin Lewis's blog for details.

The list above is not exhaustive, so for a complete rundown of all the new InnoDB features see Calvin Sun's InnoDB blog.

Replication Improvements 

MySQL Replication은 scalability와 high-availability를 위해 가장 많이 사용하는 기능이다. 이 부분도 community와 고객의 요청에 따라 다음과 같은 기능들이 추가되었다. 

Binlog API

C++로 사용가능한 Binlog API가 제공된다. 이 API를 이용하여 binary log event를 파싱한다거나 분석하여 필요한 데이터만 추출하는 일등이 가능하게되었다. 

Binlog Group Commit (Complates InnoDB Group Commit implemented in MySQL 5.5)

MySQL Replication의 성능 향상을 위해, Group Commit은 binary log를 동시에 update하고 한꺼번에 commit할 수 있게 허용한다. 
 
 사용자는 group commit작업의 빈도수를 다음과 같은 방법으로 컨트롤 할 수 있다. :

1. 한꺼번에 같이 작업할 transaction의 갯수를 설정한다. 
2. millisecond단위로 작업할 interval을 설정한다. 

현재 Binlog Group commit은 진행중안 작업안에서 snapshot으로 동작한다. 우리는 직접 benchmark를 진행하지 않았지만, 이 작업으로 성능에 좋은 영향을 줄 수 있다고 판단하고 있다. 관련해서 문의 사항이 있으면 MySQL development team에 문의하도록 한다. 

See Mats Kindahl's blog for details.

Durable Slave Reads

Master의 binary log를 Slave가 읽을때의 성능 개선을 위해 다음과 같이 2가지 옵션을 선택할 수 있게 되었다.  

• update가 적용되자 마다 binlog를 읽는다. (Master장애에  의한 event 분실을 감수하고 사용해야 한다)
  디스크에 작성된 binlog만 읽는다. 이 옵션은 read 기능에 대한 안전성을 높혀준다. 하지만, disk에 커밋된 후 slave가 읽어들이기 때문에 조금 느려질 수 있다. 
   

Follow Mats Kindahl's blog on all things MySQL Replication for details.

Enhanced Multi-Threaded Slaves

Slave에서 여러 Thread를 생성하여 동시에 여러 변경사항을 저장할 방법을 추가할 예정이다. 이미 2011년 4월에 preview를 진행했다.

See Luis Soares' blog for details. 

Next Steps:
추가적인 기능 개선이나 변경 사항은 다음에서 확인이 가능하다. 

이번에 MySQL News Letter에 소개된 글로 다음의 글을 번역하여 정리한 것이다. 

http://palominodb.com/blog/2011/10/05/change-views-definer-without-alter-view-how-fix-thousands-views


 MySQL의 View는 보안을 위해 DEFINER와 SQL SECURITY 설정을 통해 VIEW의 접근을 관리한다. 
만약 'user123'@'192.168.1.%'라는 계정을 사용하여 다음의 구문을 실행한다고 가정하자. 

CREATE VUEW view1 SELECT * FROM tbl1;

그럼 다음과 같은 구문으로 생성되어 저장된다. 

CREATE ALGORITHM=UNDEFINED DEFINER='user123'@'192.168.1.%'
SQL SECURITY DEFINER 
VIEW 'view1' AS select <ommitted> from 'tbl1';

 위와 같이 생성한 경우 다음과 같은 문제가 발생할 수 있다. 

'user123'@'192.168.1.%'  계정이 삭제되는 경우 view에 어느 계정도 접근할 수 없다.  

그렇기 때문에  이와 같은 경우 alter view 구문을 사용 하여 definer를 수정하던지 sql security를 수정하게 해줘야 한다.   view가 하나인 경우에는 크게 문제 되지 않지만, 만약 많은 view를 사용중이라면 이것은 굉장히 힘이 많이 드는 일일 수 있다.

이때에 다음과 같은 해결책을 사용하면 쉽게 해결할 수 있다. 

• /tmp/list_views.txt 리스트는 계정정보를 수정할 필요가 있는 것이다. 
• /tmp/view_backup.tar는 기존 frm 파일에 대한 백업본이다.
• 각 view 파일별로 frm 파일을 frm.old파일로 복사하고, sed구문을 사용하여 해당 내용을 변경하여 저장하고, frm.old 파일을 remove 한다.
• 모든 열린 테이블을 닫고 다시 열어서 변경된 내용이 적용되도록 한다. 

http://mysqldba.tistory.com/76

-참고-
이글은 MySQL Reference 5.1의 내용을 번역한 겁니다. 
영문과 함께 번역한거 공유합니다. 

It is a common situation in database applications that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus, insertions into the clustered index do not require random reads from a disk.

Application에서 일반적으로 PK 정렬 순으로 새 데이터가 입력되는것이나 PK가 Unique identifier로 된것은 아주 일반적인 모스비다. 그래서 Clustered index의 insert는 디스크로 부터 random read를 요구하지는 않는다. 

On the other hand, secondary indexes are usually nonunique, and insertions into secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in InnoDB

다른말로 바꾸면, Secondary Index는 항상 non-unique이고 Secondary index는 상대적으로 random order로 입력이 일어난다는 것을 의미한다. 이것은 InnoDB에서 사용하는 특별한 동작방법없이는 많은 random disk I/O를 발생시킬수있다는 것을 말한다. 

If an index record should be inserted into a nonunique secondary index, InnoDB checks whether the secondary index page is in the buffer pool. If that is the case, InnoDB does the insertion directly to the index page. If the index page is not found in the buffer pool, InnoDB inserts the record to a special insert buffer structure. The insert buffer is kept so small that it fits entirely in the buffer pool, and insertions can be done very fast.

만약 non-unique Secondary index에 입력이 발생한다면 InnoDB는 secondary index 페이지가 buffer pool안에 있는지 없는지 체크하나. 만약 buffer pool안에 존재한다면, InnoDB는 즉시 그 페이지에 입력 작업을 진행한다. 만약 buffer pool안에서 해당 페이지를 찾지 못한다면, InnoDB는 특별한 insert buffer 구조체 안에 그 입력 내용을 저장한다. 이 insert buffer는 전체 buffer pool안에 맞게 작은 사이즈로 유지되고, 이 입력 작업은 매우 빠르게 진행될 수 있다. 

Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions into the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.

정기적으로 insert buffer는 데이터베이스안에 secondary index에 merge하는 작업을 진행한다. 디스크 IO작업을 saving하기 위해 index tree안의 같은 페이지에 속하는 여러 insert를 merge하는 것이 가능하다. insert buffer를 사용하면 약 15대의 insert속도를 빠르게 할 수 있음이 증명되어졌다. 

The insert buffer merging may continue to happen after the inserting transaction has been committed. In fact, it may continue to happen after a server shutdown and restart (see Section 13.6.6.2, “Forcing InnoDB Recovery”).

insert buffer merging 작업은 트랜잭션이 완료된 후에 진행될 수 있다. 사실 이 작업은 서버의 restart 후에도 계속 이어질 수 있다. 

Insert buffer merging may take many hours when many secondary indexes must be updated and many rows have been inserted. During this time, disk I/O will be increased, which can cause significant slowdown on disk-bound queries. Another significant background I/O operation is the purge thread (see Section 13.6.9, “InnoDB Multi-Versioning”).

insert buffer merging작업은 많은 secondary index에 update가 발생하고 많은 row가 insert될 때 많은 시간동안 동작될 수 있다. 이 작업이 진행되는 동안 Disk I/O는 증가될 것이기 때문에 디스크 관련 쿼리에 성능 영향을 줄 수 있다. 이것과 마찬가지로 I/O 작업으로 중요한 thread는 purge thread가 있다. 

http://mysqldba.tistory.com/73

시스템 variable 중 automatic_sp_privileges라는 게 있네요.... 

http://mysqldba.tistory.com/70

5.1.44부터 새롭게 추가된 variable 이네요.

http://mysqldba.tistory.com/55

http://mysqldba.tistory.com/36

http://mysqldba.tistory.com/54

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

http://mysqldba.tistory.com/

오래된 버젼이므로 참조만 할 것 (한글이니까 빨리 읽기 쉽게) - 자세한건 직접 다시 찾아보기!!!!!!!

이 섹션에서는 InnoDB-관련 명령어 옵션과 시스템 변수에 대해서 설명을 하기로 한다. 서버 스타트업 시점에 이 변수들을 명기함으로써 트루 또는 팰스 (true or false)인 시스템 변수를 활성화 시키거나, 또는 skip- 접두어를 사용해서 비활성화 시킨다. 예를 들면, InnoDB 체크섬을 활성화 또는 비활성화 시키기 위해서는, 명령어 라인에서 --innodb_checksums 또는 --skip-innodb_checksums 를 사용하거나, 옵션 파일에서 innodb_checksums 또는 skip-innodb_checksums를 사용한다. 수치 값을 가지는 시스템 변수는 명령어 라인에서 --var_name=value 형태를 사용하거나 또는 옵션 파일에서 var_name=value와 같이 사용하면 된다. 옵션 및 시스템 변수를 지정하는 것에 대한 보다 많은 정보는 Section 4.3, “Specifying Program Options”를 참고하기 바란다. 대부분의 시스템 변수는 런타임 시점에 변경시킬 수가 있다 (Section 5.2.4.2, “Dynamic System Variables”를 참조).

InnoDB 명령어 옵션:

--innodb 
서버가 InnoDB를 지원하도록 컴파일 되어 있다며, InnoDB 스토리지 엔진을 활성화 시킨다.  --skip-innodb를 사용하면 InnoDB를 비활성화 시킬 수가 있다.

--innodb_status_file 
InnoDB로 하여금 MySQL 데이터 디렉토리에 /innodb_status.라는 이름의 파일을 생성하도록 한다. InnoDB는 주기적으로 SHOW ENGINE INNODB STATUS의 결과를 이 파일에 작성한다.

InnoDB 시스템 변수:

innodb_additional_mem_pool_size 
InnoDB가 데이터 디렉토리 정보와 다른 내부 데이터 구조를 저장하기 위해 사용하는 메모리 풀의 크기 (바이트 단위). 여러분이 어플리케이션에서 테이블을 많이 가질수록, 여기에 할당해야 하는 메모리가 많이 필요하게 된다. 만일 InnoDB가 이 풀에 있는 메모리를 다 사용하게 되면, 이 엔진은 OS가 사용하는 메모리를 할당하기 시작하고, MySQL 에러 로그에 경고 메시지를 작성한다. 디폴트 값은 1MB가 된다.

innodb_autoextend_increment 
테이블스페이스가 가득 차게 되었을 때 자동-확장 테이블스페이스의 확장 크기 (MB단위). 디폴트는 8MB가 된다.

innodb_buffer_pool_awe_mem_mb 
이 엔진이 AWE 메모리에 있을 경우의 버퍼 풀의 크기. 이 변수는 32-비트 윈도우 시스템에만 적용된다. 만일 여러분이 사용하는 32-비트 윈도우 시스템이 4GB 이상의 메모리를 지원한다면, 소위 “Address Windowing Extensions”을 사용해서 InnoDB 버퍼 풀을 AWE 메모리에 할당할 수가 있다. 이 변수의 최대 가능 값은 63000이다. 만일 이 값이 0보다 크다면, innodb_buffer_pool_size는 InnoDB 가 AWE 메모리에 매핑되어 있는 mysqld의 32-비트 주소 스페이스에 있는 윈도우가 된다. innodb_buffer_pool_size의 가장 좋은 크기는 500MB이다.

AWE 메모리의 장점을 살리기 위해서는, 여러분 스스로 MySQL을 컴파일 할 필요가 있다. 이렇게 하기 위해 필요한 설정은 innobase/os/os0proj.c 소스 파일에서 찾아볼 수가 있다.

innodb_buffer_pool_size 
InnoDB가 자신의 테이블에 있는 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼의 크기 (바이트 단위). 이 값을 크게 설정하면 할수록, 테이블에 있는 데이터를 접속하는데 필요한 I/O가 덜 생긴다. 지정된 데이터베이스 서버에서는, 이 값으로 메인 메모리의 80%까지를 설정할 수가 있다. 하지만, 이 값을 너무 크게 설정하게 되면 OS와 메인 메모리 할당 경쟁이 생기기 때문에 적절하게 설정하는 좋다.

innodb_checksums 
InnoDB는 하드웨어 또는 데이터 파일의 오류에 대한 여분의 폴트 톨로런스 (fault-tolerance)를 보장하기 위해 디스크에서 읽는 모든 페이지에 대해서 체크섬 확인을 사용할 수 있다. 이러한 확인은 디폴트로 활성화 되어진다. 하지만, 거의 드문 경우지만 어떤 환경 (벤치마크를 구동 중에 있는 경우)하에서는 이러한 기능이 불필요할 수도 있으며, 이럴 경우에는 --skip-innodb_checksums를 사용해서 비활성화를 시킨다. 이 변수는 MySQL 5.0.3에서 추가되었다.

innodb_commit_concurrency 
동시에 실행되는 쓰레드의 숫자. 이 값이 0이 되면 동시성 제어 (concurrency control)가 비활성화 된다. 이 변수는 MySQL 5.0.12에서 추가되었다.

innodb_concurrency_tickets 
InnoDB에 동시에 들어갈 수 있는 쓰레드의 숫자는 innodb_thread_concurrency 변수로 알아볼 수가 있다. 여러 개의 쓰레드가 이미 컨커런시 한계에 도달하였다면, 하나의 쓰레드만이 큐에 들어갈 수 있다. 하나이 쓰레드가 InnoDB에 들어가게 되면, innodb_concurrency_tickets의 값과 일치하는 “자유 티켓”의 숫자가 주어지고, 쓰레드가 자신의 티켓을 사용하기 전 까지는 자유롭게 InnoDB에 들어가고 나올 수가 있다. 이런 후에는, 쓰레드는 다시금 일관성 검사를 하고 InnoDB에 다시 들어가려고 시도하게 된다. 이 변수는 MySQL 5.0.3에 추가되었다.

innodb_data_file_path 
개별적인 데이터 파일의 경로와 크기. 각각의 데이터 파일에 대한 전체 경로는 여기에서 지정된 경로에 innodb_data_home_dir를 연결해서 구성된다. 파일의 크기는 MB 또는 GB (1024MB)이며, M 또는 G를 붙인다. 파일 크기의 합은 최소 10MB이상이어야 한다. 만일 innodb_data_file_path를 지정하지 않는다면, 디폴트로 10MB 크기의 자동-확장 데이터 파일을 ibdata1라는 이름으로 하나 만들게 된다. 개별 파일의 크기 한계는 여러분이 사용하는 OS에 따라서 다르다. 대형 파일을 지원하는 OS에서는 4GB 이상의 파일도 만들 수가 있다. 또한, 로우 디스크 파티션을 데이터 파일 형태로 사용할 수도 있다. Section 14.2.3.2, “공유 테이블스페이스에 대해서 로우 (Row) 디바이스 사용하기”를 참조할 것.

innodb_data_home_dir 
모든 InnoDB 데이터 파일에 대한 디렉토리 경로의 공통 부분. 이 값을 설정하지 않게 되면, 디폴트는 MySQL 데이터 디렉토리가 된다. 빈 스트링을 가지고 이 값을 설정할 수도 있는데, 이렇게 되면, 여러분은 innodb_data_file_path에 있는 절대 경로를 사용할 수가 있다.

innodb_doublewrite 
디폴트로는, InnoDB은 모든 데이터를 두 번 저장하는데, 처음에는 이중 쓰기 버퍼 (doublewrite buffer)에 저장하고, 다음에는 실제 데이터 파일에 저장한다. 이 변수는 디폴트로 활성화된다. 벤치 마크 테스트를 하거나 또는 최고의 성능이 필요한 경우에는 --skip-innodb_doublewrite를 사용해서 이 변수를 비활성화 시킬 수 있다. 이 변수는 MySQL 5.0.3에서 추가되었다.

innodb_fast_shutdown 
만일 이 변수를 0으로 설정하면, InnoDB는 셧다운을 하기 전에 전체 퍼지 (full purge)와 삽입 버퍼 병합 (insert buffer merge)를 실행한다. 이러한 연산들은 몇 분의 시간이 걸리거나, 극단적인 경우에는 몇 시간이 걸릴 수도 있다. 만일 이 변수를 1로 설정하면, InnoDB는 셧 다운 시점이 이러한 연산들을 건너 띄게 된다. 이 변수의 디폴트 값은 1이다. 만일 여러분이 이 변수를 2로 설정한다면, InnoDB는 마치 MySQL이 크래시 된 것처럼 자신의 로그만을 플러시 하고 셧 다운은 진행하지 않는다; 실행되지 않은 트랜젝션은 잃어 버리게 되지만, 서버를 재 스타트업 시키면 크래시 복구는 실행된다. 이러한 값 2는 MySQL 5.0.5 이후에 사용되었으며, NetWare에서는 사용할 수 없다.

innodb_file_io_threads 
InnoDB에서의 파일 I/O 쓰레드의 숫자. 일반적으로, 이 변수는 디폴트로 4를 갖게 되지만, 윈도우에서는 이 숫자를 늘려 줌으로써 디스크 I/O가 좋아질 수가 있다. 유닉스에서는, 이 숫자를 늘려도 별 효과가 발생하지 않는다; InnoDB는 항상 디폴트 숫자만을 사용한다.

innodb_file_per_table 
이 변수가 활성화 되어 있다면, InnoDB는 데이터 및 인덱스를 공유 테이블스페이스를 이용하는 대신에 자신의 .ibd 파일을 사용해서 새로운 파일을 생성한다. 디폴트는 공유 테이블스페이스에 테이블을 생성하는 것이다. Section 14.2.3.1, “테이블 당 테이블스페이스 사용하기”를 참조할 것.

innodb_flush_log_at_trx_commit 
innodb_flush_log_at_trx_commit이 0으로 설정되면, 로그 버퍼는 초 당 한번씩 로그 파일이 기록이 되고 디스크 연산에 대한 플러시는 로그 파일에서 실행되지만, 트랜젝션 실행 시점에는 아무것도 실행되지 않게 된다. 이 값이 1 (디폴트)로 설정되면, 로그 버퍼는 각 트랜젝션이 실행될 때마다 로그 파일에 기록되고 로그 파일에서 디스크 연산에 대한 플러시가 실행된다. 2로 설정되면, 로그 버퍼는 각 실행 시점마다 파일로 기록되지만, 디스크 연산에 대한 플러시는 실행되지 않는다. 하지만, 로그 파일에 대한 플러시는 값이 2일 때에도 초당 한번씩 실행된다. 초당 1회의 플러시는 모든 초당 이루어진다고는 장담할 수가 없는데, 그 이유는 프로세스 스케쥴링 문제 때문이라는 점을 알아두자.

이 변수의 디폴트 값은 1이며, 이 값은 ACID와의 호환성을 위해 요구되는 값이다. 여러분은 이 값을 1 이외의 값으로 설정해서 보다 좋은 성능을 얻을 수는 있겠지만, 크래시가 나게 되면 한 순간에 모든 것을 잃어 버릴 수도 있다. 만일 이 값을 0으로 설정한다면, 어떠한 mysqld 프로세스 크래시라도. 만일 이 값을 2로 설정한다면, OS 크래시 또는 전원 불량을 통해서만 마지막 초 순간의 트랜젝션이 지워지게 된다. 하지만, InnoDB의 크래시 복구는 영향을 받지 않으며 따라서 크래시 복구는 변수 값에 상관없이 실행된다. 대다수의 OS와 몇몇 디스크들은 디스크에 대한 플러시 연산을 제대로 실행하지 못한다는 점을 알아두자.

Note: 트랜젝션을 사용하는 InnoDB을 이용한 리플리케이션 설정에서 내구성과 일관성을 최대로 확보하기 위해서는, 여러분이 사용하는 마스터 서버의 my.cnf 파일에서 innodb_flush_log_at_trx_commit=1, sync_binlog=1, innodb_safe_binlog (MySQL 5.0.3 이전 버전의 경우)를 사용해야 한다. (innodb_safe_binlog는 5.0.3 이후에는 필요가 없다.)

innodb_flush_method 
만일 fdatasync (디폴트)를 설정하면, InnoDB는 fsync()를 사용해서 데이터와 로그 파일을 플러시한다. 만일 O_DSYNC를 설정하면, InnoDB는 O_SYNC를 사용해서 로그 파일을 열고 플러시하지만, 데이터 파일을 플러시하기 위해서는 fsync()를 사용하게 된다. 만일 O_DIRECT 가 지정된다면 (몇몇 GNU/Linux 버전에서 사용 가능함), InnoDB는 O_DIRECT를 사용해서 데이터 파일을 열고, 데이터 파일과 로그 파일을 플러시하기 위해 fsync()를 사용한다. InnoDB는 fsync()를 fdatasync() 대신에 사용하고, 대부분의 유닉스에서 O_DSYNC를 사용하는 것이 문제가 있기 때문에 이것을 디폴트로 사용하지 않는다는 점을 알아두자. 이 변수는 유닉스에만 연관이 되는 변수이다. 윈도우의 경우, 플러시 방식은 항상 async_unbuffered이며 이것을 변경할 수는 없다.

이 변수가 다른 값을 가지게 되면 InnoDB performance에 상당한 영향을 미치게 된다. 예를 들면, InnoDB 데이터와 로그 파일이 SAN에 존재하는 시스템에서는, innodb_flush_method를 O_DIRECT에 설정하면 단일 SELECT 명령문의 성능을 저하시키게 된다.

innodb_force_recovery 
크래시 복구 모드.

Warning: 여러분이 깨져있는 데이터 베이스에서 테이블을 덤프 하고자 하는 위급한 상황에서만 이 변수를 0보다 크게 설정해야 한다! 이 변수가 취할 수 있는 값은 1에서 6이다. 각 변수의 값이 의미하는 것은 Section 14.2.8.1, “Forcing InnoDB Recovery”에서 설명하고 있다. 보다 안전성을 제공하기 위해서, InnoDB는 이 변수가 0보다 큰 경우에는 자신의 데이터를 변경하지 못하게 끔 하고 있다.

innodb_lock_wait_timeout 
InnoDB 트랜젝션의 타임아웃은 롤백이 진행되기 전에 락을 대기하는 시간이다. InnoDB 는 자동으로 자신의 락 테이블에 있는 트랜젝션 데드락 (deadlock)를 검사하고 트랜젝션을 롤 백 한다. InnoDB는 LOCK TABLES 명령문을 사용해서 락 세트를 알려준다. 디폴트는 50초이다.

innodb_locks_unsafe_for_binlog 
이 변수는 InnoDB 검색과 인덱스 스캔에서 다음-키 (next-key) 락킹을 제어한다. 디폴트로는, 0으로 설정 되는데 (비활성화), 이것은 다음-키 락킹이 활성화 됨을 의미한다.

보통의 경우, InnoDB는 “next-key locking”이라는 알고리즘을 사용한다. InnoDB는 테이블 인덱스를 검색 또는 스캔을 하는 시점에 이 알고리즘을 이용해서 하위-레벨 락킹을 실행하는데, 이렇게 되면 자신이 직면하는 모든 인덱스 레코그에서 공유 또는 배타적인 락을 설정하게 된다. 따라서, 하위-레벨 락은 실제로 인덱스 레코트 락이 된다. The locks that InnoDB가 인덱스 레코드에 설정하는 락은 인덱스 레코드보다 앞에 있는 ‘갭 (gap)”에도 영향을 준다. 만일 사용자가 공유 또는 배타적인 락을 인덱스의 레코드 R 에 가지고 있다면, 다른 사용자는 새로운 인덱스 레코드를 R 앞에 삽입할 수 없게 된다. 이 변수를 활성화 시키게 되면 InnoDB는 검색 또는 인덱스 스캔에서 다음-키 락킹을 사용할 수 없게 된다. 다음-키 락킹은 외래-키 제약과 이중 키 검사를 확인하는데 사용할 수 있다. 이 변수를 활성화 시키면 팬텀 (phantom) 문제가 발생할 수도 있음을 알아두자: 여러분이 나중에 선택된 열에 있는 몇몇 컬럼을 업데이트 하고자 하는 의도를 가지고, 100보다 큰 식별자 값을 사용해서 child 테이블에서 모든 칠드런 (children)을 읽고 락을 하기 원한다고 가정하자:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

id 컬럼에 인덱스가 존재한다고 가정한다. 쿼리는 id가 100보다 큰 첫 번째 레코드부터 인덱스를 스캔한다. 만일 인덱스 레코드에 설정되어 있는 락이 갭에 만들어져 있는 삽입의 락을 풀지 못한다면, 다른 클라이언트는 새로운 열을 테이블 안에 삽입할 수 있게 된다. 만일 동일한 트랜젝션 안에서 같은 SELECT를 실행한다면, 여러분은 쿼리가 리턴하는 결과에서 새로운 열을 볼 수가 있다. 이것은, 만일 새로운 아이템이 데이터베이스에 추가될 경우, InnoDB는 “serializability”를 보장하지 않는다는 것을 의미하는 것이다. 따라서, 만일 이 변수가 활성화 되어 있다면, InnoDB는 READ COMMITTED를 보장하게 된다. (“serializability” 충돌을 보장한다.)

MySQL 5.0.2 버전 이후로는, 이 옵션은 덜 안전한 기능을 제공하게 된다. UPDATE 또는 DELETE에 있는 InnoDB는 업데이트 또는 삭제를 하는 열만을 잠근다. 이를 통해서 데드락의 가능성은 많이 감소하기는 하지만, 아주 없어진 것은 아니다. 이 변수를 활성화 시킨다고 하더라도 UPDATE 과 같은 연산이 다른 열에 영향을 주는 경우조차도 다른 유사한 연산 (UPDATE과 같은 연산)을 앞지르지는 못한다는 점을 알아두자.:

CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;

INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT;

클라이언트 하나가 아래의 명령문을 실행한다고 가정하자:

SET AUTOCOMMIT = 0;

UPDATE A SET B = 5 WHERE B = 3;

그런 다음에는, 다른 클라이언트가 첫 번째 클라이언트의 명령문 다음에 나오는 아래의 명령문을 실행한다고 가정하자:

SET AUTOCOMMIT = 0;

UPDATE A SET B = 4 WHERE B = 2;

이와 같은 경우, 두 번째 UPDATE는 반드시 첫 번째 UPDATE의 실행 또는 롤백을 기다려야 한다. 첫 번째 UPDATE는 열 (2,3)에 배타적인 락을 가지고 있기 때문에, 두 번째 UPDATE이 열들을 스캐닝할 때 동일한 열에 대해서 배타적인 락을 획득하고자 시도하지만 성공하지는 못한다. 이것은, 두 번째 UPDATE가 우선 열에 대해서 배타적인 락을 획득하고 난 다음에 그 열이 결과 셋에 포함되어 있는지를 확인하기 때문이다. 만일 속해 있지 않다면, 필요 없는 락을 풀게 되고, 그 때에 innodb_locks_unsafe_for_binlog 변수가 활성화가 된다.

따라서, InnoDB는 첫 번째 UPDATE를 아래와 같이 실행한다:

x-lock(1,2)

unlock(1,2)

x-lock(2,3)

update(2,3) to (2,5)

x-lock(3,2)

unlock(3,2)

x-lock(4,3)

update(4,3) to (4,5)

x-lock(5,2)

unlock(5,2)

InnoDB는 두 번째 UPDATE를 아래와 같이 실행한다:

x-lock(1,2)

update(1,2) to (1,4)

x-lock(2,3) - wait for query one to commit or rollback

innodb_log_archive 
InnoDB 아카이브 파일을 로그할지를 결정. 이 변수는 여러 가지 이유로 존재하기는 하지만 더 이상 쓰여지지는 않는 변수다. 디폴트 값은 0이다.

innodb_log_buffer_size 
InnoDB가 로그 파일을 디스크에 쓰기 위해 사용하는 버퍼의 크기 (바이트). 사용 가능한 크기는 1MB 에서 8MB이다. 디폴트는1MB. 로그 버퍼가 크면 트랜젝션을 실행하기 전에 로그를 디스크에 쓰지 않고서도 대형 트랜젝션을 처리할 수가 있게 된다. 따라서, 만일 여러분이 대형 트랜젝션을 가지고 있다면, 로그 버퍼를 크게 해서 디스크 I/O를 절감하도록 한다.

innodb_log_file_size 
로그 그룹에 있는 각 로그의 크기 (바이트). 로그 파일의 조합 크기는 32-비트 컴퓨터에서는 4GB보다 작아야 한다. 디폴트는 5MB. 사용 가능한 크기의 범위는 1MB 에서 버퍼 풀의 1/N-th까지 이며, 여기에서 N 은 그룹에 있는 로그 파일의 수가 된다. 이 값이 클수록, 버퍼 풀에서 체크포인트 플러시 동작이 덜 필요하게 되며, 디스크 I/O를 줄여주게 된다. 하지만 로그 파일이 크게 되면, 크래시 발생시의 복구가 그 만큼 느려지게 된다.

innodb_log_files_in_group 
로그 그룹의 로그 파일의 숫자. InnoDB는 원형 방식 (circular fashion)으로 파일을 기록한다. 디폴트는 (권장 값)는 2.

innodb_log_group_home_dir 
InnoDB 로그 파일에 대한 디렉토리 경로. 만일 어떠한 InnoDB 로그 변수도 지정하지 않는다면, 디폴트로 ib_logfile0 및 ib_logfile1라는 이름의 5MB 파일 두 개를 MySQL 데이터 디렉토리에 생성한다.

innodb_max_dirty_pages_pct 
0에서 100 사이의 정수가 된다. 디폴트는 90. InnoDB의 메인 쓰레드는 아직 기록되지 않은 페이지의 퍼센트가 이 값을 초과하지 않게 하기 위해 버퍼 풀에 있는 페이지를 기록하고자 시도한다.

innodb_max_purge_lag 
이 변수는 퍼지 연산 (purge operation)이 래깅 (lagging)될 때 INSERT, UPDATE 및 DELETE 연산을 지연 시키는 방법을 제어한다 (Section 14.2.12, “다중-버전 구현”을 참조). 이 변수의 디폴트 값은0이며, 이것은 아무런 지연도 없음을 의미한다.

InnoDB 트랜젝션 시스템은 UPDATE 또는 DELETE 연산에 의한 삭제-표시된 인덱스 레코드를 가지고 있는 트랜젝션 리스트를 관리한다. 이 리스트의 길이는 purge_lag가 되도록 한다. purge_lag 가 innodb_max_purge_lag를 초과하게 되면, 각 INSERT, UPDATE 및 DELETE 연산은 ((purge_lag/innodb_max_purge_lag)×10)–5 밀리 초 만큼 지연된다. 이러한 지연 시간은 퍼지 배치가 시작되는 시점에, 매 초마다 계산된다. 퍼지될 열을 볼 수 있는 이전의 상수 읽기 뷰 (old consistent read view)로 인해 퍼지가 실행되지 않으면 연산은 지연되지 않는다.

문제가 있는 워크로드에 대한 전형적인 설정은 백만분의 1초 이며, 트랜젝션이 작고, 크기가 100바이트 정도이며, 테이블에는 100MB의 퍼지되지 않은 열만 허용할 수 있다는 가정을 한다.

innodb_mirrored_log_groups 
데이터베이스용으로 보관하기 위한 로그 그룹의 동일한 복사본의 숫자. 현재 버전까지는, 이것은 1로 설정해야 한다.

innodb_open_files 
이것은 InnoDB에 다중의 테이블스페이스를 사용하는 경우에만 관련이 있는 변수이다. 이 변수의 값은 InnoDB가 한번에 열어 둘 수 있는 .ibd 파일의 최대 숫자를 지정한다. 최소 값은 10. 디폴트는 300.

.ibd 파일용으로 사용되는 파일 설명자 (descriptor)는 InnoDB만을 위한 것이다. 이러한 설명자는 --open-files-limit 서버 옵션에 의해 지정되는 것들과는 별개의 것들이며, 테이블 캐시의 연산에는 영향을 주지 않는다.

innodb_safe_binlog 
InnoDB 테이블과 바이너리 로그의 내용물 사이에 일관성 보장을 추가한다. Section 5.12.3, “The Binary Log”를 참조할 것. 이 변수는 MySQL 5.0.3부터는 삭제되었다.

innodb_support_xa 
ON 또는 1 (디폴트 임)로 설정되면, 이 변수는 트랜젝션의 2-단계 실행 (two-phase commit)을 지원하도록 InnoDB를 활성화 시킨다. innodb_support_xa를 활성화 시키면 트랜젝션 준비 (transaction preparation)를 위한 여분의 디스크 플러시가 발생한다. 만일 여러분이 XA를 사용하는 것에 신경을 쓰지 않는다면, 디스크 플러시의 숫자를 줄이고 InnoDB 의 성능을 좋게 하기 위해서 이 변수를 0 또는 OFF로 설정하면 된다. 이 변수는 MySQL 5.0.3에서 추가되었다.

innodb_sync_spin_loops 
쓰레드가 지연되기 전에 (suspended) 풀어 주기 위해 InnoDB 뮤텍스 (mutex)를 기다리는 쓰레드의 대기 시간. 이 변수는 MySQL 5.0.3에서 추가되었다.

innodb_table_locks 
만일 AUTOCOMMIT=0라면, InnoDB는 LOCK TABLES를 존중하게 된다; MySQL은 다른 모든 쓰레드가 테이블에 대한 자신들의 모든 락을 풀기 전까지는 LOCK TABLE .. WRITE에서 리턴을 하지 않는다. innodb_table_locks의 디폴트 값은1이며, 이렇게 되면 LOCK TABLES은 AUTOCOMMIT=0경우에, InnoDB로 하여금 내부적으로 테이블을 잠그도록 한다.

innodb_thread_concurrency 
InnoDB는 이 변수가 주는 한계와 동등하거나 작게 InnoDB 내부에 OS 쓰레드의 숫자를 유지하고자 한다. 만일 성능상의 문제가 있다면, 그리고 SHOW ENGINE INNODB STATUS 가 세마포어를 기다리는 많은 수의 쓰레드를 내 보낸다면, 쓰레드 “thrashing”을 가지고 있는 것이며, 이 변수를 보다 작게 또는 보다 크게 설정해 보아야 한다. 만일 여러분이 사용하는 컴퓨터가 많은 수의 CPU와 디스크를 가지고 있는 것이라면, 컴퓨터의 자원을 보다 많이 사용할 수 있도록 이 값을 높게 설정하도록 한다. 권장하는 값은 여러분이 사용하는 시스템의 프로세스와 디스크의 전체 합이다.

이 변수의 범위는 0에서 100까지이다. MySQL 5.0.19 이전에는 20보다 크거나 같게 되면 무한 일관성 (infinite concurrency)으로 해석이 되었다. 5.0.19 버전 이후에는, 0을 무한으로 해석한다. 무한이라는 의미는 일관성 검사가 비활성화 되고 뮤텍스 (mutex)를 획득하고 풀기 위한 오버헤드를 제거된다는 것을 의미한다.

디폴트 값은 여러 번 변경되었다: MySQL 5.0.8 이전에는 8 이었으며, 5.0.8 에서 5.0.18까지는 20 (infinite), 5.0.19 에서 5.0.20까지는 0 (infinite), 그리고 5.0.21이후에는 8 (finite)이다.

innodb_thread_sleep_delay 
InnoDB 큐를 조이닝 (joining)하기 전에 InnoDB 쓰레드가 일시 정지 (sleep)하는 시간. 마이크로 초. 디폴트는 10,000. 0은 일시 정지를 비활성화 시킨다. 이 변수는 MySQL 5.0.3에 추가되었다.

sync_binlog 
만일 이 변수의 값이 양수 (positive)라면, MySQL 서버는 모든 sync_binlog 가 자신의 바이너리 로그를 기록한 후에 그 바이너리 로그를 디스크에 동기화 시킨다 (fdatasync()).자동 실행 모드 (autocommit mode)에서는 명령문 당 한 번씩 바이너리 로그를 기록하고, 그렇지 않은 경우에는 매 트랜젝션 별로 한 번씩 기록한다는 점을 알아두자. 디폴트 값은 0 이며, 이것은 디스크에 동기화를 시키지 않는다. 1의 값을 선택하는 것이 안전한데, 그 이유는 크래시가 발생할 경우에는 바이너리 로그에서 하나의 명령문/트랜젝션을 잃어버리게 되기 때문이다; 하지만, 이 값을 선택하면 성능이 덜 나오게 된다 (디스크가 배터리-백업 캐시를 가지고 있지 않는 한).

[출처] ::: MySQL Korea ::: - http://www.mysqlkorea.co.kr/

MySQL Korea 사이트의 컨텐츠 소유권은 MySQL Korea 에 있으므로
허락 없이 이를 무단전재 하는 경우 저작권법에 민형사적 책임을 지게 되므로
절대 무단 사용을 금해 주시기 바 랍니다
MySQL Korea 저작권 공지 : http://www.mysqlkorea.co.kr/sub.html?mcode=others&scode=04 

http://blog.naver.com/lovewj8318/220147579276

MySQL 스토어드 프로그램은

MySQL의 스토어드 프로그램(이 글에서 스토어드 프로그램은 Stored procedure와 Stored Function에 한함)은 MySQL 5.0버전부터 지원되기 시작했다. MySQL 5.0의 첫번째 릴리즈 버전이 2005년도 10월에 출시되었으니, MySQL에 프로시져가 도입된지 벌써 10년 정도의 시간이 지나가고 있지만 실제로 MySQL에서 프로시져의 인기는 그다지 높지 않다.
요즘은 MSSQL이나 Oracle에 익숙한 사용자(개발자와 DBA 모두)들이 MySQL을 배우거나 사용하고자 하는 경우가 많이 늘어나고 있는데, 많은 사용자들이 MySQL 만의 특징에 익숙치 않아서 혼란스러워하는 경우를 많이 보았다.
특히나 MySQL은 주로 Web 기반의 서비스에서 사용되다 보니, MSSQL이나 Oracle과 같은 RDBMS에서 효율적으로 제공하는 기능들이 MySQL에서는 그렇지 못한 것들이 자주 있다. 물론 때로는 그 반대인 경우도 흔히 볼 수 있다.
그중에서 가장 많은 이슈가 되고 있는 스토어드 프로그램의 특징을 간단히 살펴보고, 왜 MySQL에서는 Oracle이나 MSSQL에서와 같이 스토어드 프로그램을 활용할 수 없는지를 소개해보고자 한다.

스토어드 프로그램의 컴파일

다른 상용의 RDBMS에서와 같이 MySQL 서버에서도 스토어드 프로그램은 컴파일 과정을 거치게 된다. 물론 C/C++과 같이 물리적인 CPU가 직접 해석할 수 있는 이진 코드가 만들어지는 것은 아니지만, Java와 같이 어떤 형태의 목적 코드(Java의 바이트 코드와 같은)가 만들어지고
이 목적 코드는 메모리상에 저장되어서 나중에 재실행 요청시에는 준비된 바이트 코드가 실행된다. 즉 스토어드 프로그램의 소스 코드가 매번 실행될 때마다 파싱되고 분석되어서 실행되는 것이 아니란 것을 의미한다.

간단히 아래와 같은 프로시져를 생각해보자.

CREATE PROCEDURE sp_test(p CHAR(16))
BEGIN
    DECLARE x INT;
    SET x = 3;
    WHILE x > 0 DO
        SET x = x-1;
        INSERT INTO tab_test VALUES (x, p);
    END WHILE;
END

위의 프로시져가 컴파일되면, 아래와 같은 목적 코드가 만들어지게 된다.
목적 코드에서는 단순히 스토어드 프로그램의 코드에서 SET 이나 WHILE과 같은 문장들을 sp_instr_set이나 sp_instr_jump 등과 같은 인스트럭션으로 변환된 형태로 관리하게 된다.
여기에서 한 가지 기억해야 할 것은 컴파일된 스토어드 프로그램의 목적 코드에서 SQL 문장은 그대로 문자열로 남아있게 된다는 것이다. 즉 MySQL의 스토어드 프로그램은 컴파일이 되어도 내부에 사용된 SQL 문장들을 바로 실행할 수 있는 실행 계획이나 Parsed-Tree 형태로 관리하는 것이 아니란 것을 의미한다.

---------+-----------------------------------------------------
Position | Instruction
---------+-----------------------------------------------------
 0       | sp_instr_set(1, '3')
 1       | sp_instr_jump_if_not(5, 'x>0')
 2       | sp_instr_set(1, 'x-1')
 3       | sp_instr_stmt('INSERT INTO tab_test VALUES (x, p)')
 4       | sp_instr_jump(1)
 5       | <end>
---------+-----------------------------------------------------

스토어드 프로그램 캐시

Oracle이나 MSSQL의 스토어드 프로그램은 전역의 스토어드 프로그램 캐시 공간(Memory)에 관리된다. 물론 MySQL 서버의 스토어드 프로그램도 컴파일되면 스토어드 프로그램 캐시(소스 코드에서는 이를 sp_cache라고 함)에 관리한다.
하지만 MySQL의 스토어드 프로그램 캐시는 전역이 아니라 Thread 단위로 관리된다. 여기서 Thread라 함은 사실은 Connection 기반으로 관리됨을 의미한다. 만약 Thread pool을 사용한다 하더라도, 실제 Linux의 Thread 단위가 아니라 Connection 단위의 메모리 공간(THD)에 관리되는 것이다.

큰 차이가 아닌 것 같지만, 사실 스토어드 프로그램 캐시가 전역이나 세션(로컬) 단위냐에 따라서 장단점은 크게 달라진다.

• 전역 스토어드 프로그램 캐시
•  장점 : 메모리 절약, 스토어드 프로그램의 컴파일과 최적화 회수가 적음
•  단점 : 여러 클라이언트가 동시에 컴파일된 스토어드 프로그램을 참조하므로 동기화 비용이 필요하며, Re-Enterant와 Thread-safe한 데이터 구조체 및 구현 필요(뒷 부분은 사실 운영이 아니라 구현상의 이슈이므로, 사용자인 우리에게는 별로 중요하지 않음)
• 로컬 스토어드 프로그램 캐시
• 장점 : 클라이언트간의 공유 이슈가 없으므로 잠금이 없고 빠른 처리 가능, 구현이 쉬움
• 단점 : 많은 메모리 공간이 필요하고, 클라이언트 컨넥션 단위로 스토어드 프로그램의 컴파일 필요

MySQL의 스토어드 프로그램 캐시 공간은 Connection 단위로 관리된다는 것은 컨넥션이 새로 생성되면 필요한 모든 프로시져의 컴파일이 필요하다는 것을 의미한다.
만약 Connection pool이나 PHP의 Persistent-connection을 사용하지 못하고 매번 Connection을 생성해야 하는 경우라면, 매번 스토어드 프로그램이 실행될 때마다 스토어드 프로그램을 (mysql.proc 테이블에서) 읽어서 컴파일을 해야 하므로 최악의 성능을 내게 될 것이다.
그렇다고 Connection pool이나 Persistent-Connection 환경이라고 안전한 것은 아니다. 많은 스토어드 프로그램이 사용되는 서비스에서 MySQL 서버에 연결된 컨넥션이 10000개라고 가정하면 엄청난 메모리 공간이 필요하게 될 것이다.
하지만 성능 향상을 고려한다면, 스토어드 프로그램 캐시 메모리 공간을 적게 설정할 수도 없는 진퇴양난의 상황에 빠지게 될 수도 있다.

스토어드 프로그램의 무효화

MySQL 서버의 스토어드 프로그램 캐시 공간은 컨넥션간 서로 공유되는 전역 공간이 아니라, 컨넥션 단위로 관리된다는 것을 앞에서 살펴보았다.
사실 스토어드 프로그램 캐시가 컨넥션 단위로 관리되기 때문에 발생하는 문제점이 또 있는데, ALTER나 CRETE 등과 같은 DDL을 이용해서 스토어드 프로그램의 코드를 변경하는 경우이다.
만약 컨넥션이 10000개가 만들어져서 각각의 컨넥션에서 sp_test라는 프로시져를 사용하고 있다고 가정해보자. 이때 DBA가 ALTER PROCEDURE나 DROP PROCEDURE + CREATE PROCEDURE를 실행했다고 가정해보자.
그럼 어떤 현상이 발생하게 될까 ?

프로시져를 변경하는 컨넥션에서는 단순히 해당 프로시져의 정보를 mysql DB에 있는 proc 테이블에 변경 저장하고, 해당 프로시져의 버전을 1 증가시키고 완료된다. 이때 해당 프로시져의 버전은 글로벌하게 전역 메모리 공간에 관리된다.
그리고 모든 서비스 컨넥션에서는 프로시져를 실행하기 전에 항상 로컬 스토어드 프로그램 캐시에 괸리되는 프로시져의 버전과 전역 공간의 프로시져 버전을 확인해서, 로컬 스토어드 프로그램 캐시의 버전이 낮으면 로컬 스토어드 프로그램 캐시에 저장되어 있던 컴파일된 목적 코드를 버리고 다시 컴파일을 수행한다.
이렇게 컴파일이 완료되면, 비로소 해당 프로시져를 실행할 수 있게 되는 것이다.

그나마 다행인 것은, 변경된 프로시져가 자주 실행되지 않는다면 모든 컨넥션이 한번에 동일 스토어드 프로그램을 컴파일하기 위해서 상당한 시간을 소모하지 않을 것이다. 하지만 스토어드 프로그램이 아주 빈번하게 모든 컨넥션에서 활용된다면 어떤 상황이 발생하게 될까 ?
이런 경우라면 일부러 사용량이 별로 없는 새벽 시간에 스토어드 프로그램을 배포해야 할 지도 모르겠다.

(참고로, Oracle의 MySQL 개발팀에서는 Production MySQL 서버에서 스토어드 프로그램을 갱신하는 것은 상당히 드문 케이스이며, 별로 심각하게 고려되지 않는 상황이라고 소개하고 있다. ㅠㅠ)있다

메모리 부족 예방

MySQL 서버의 스토어드 프로그램은 컨넥션 단위로 로컬 캐시 영역에 관리되기 때문에, 컨넥션이 많고 사용되는 스토어드 프로그램이 많다면 많은 메모리 공간이 필요할 것이다. 때로는 메모리 부족 현상으로 운영 체제가 MySQL 서버를 강제 종료시킬 수도 있다.
여기에서 스토어드 프로그램의 개수가 많고 적음은 상대적이며, Production MySQL 서버에 장착된 메모리 크기와 여러가지 상황에 따라서 의존적이므로 각 DBA가 적절하게 판단해야 할 것으로 보인다.

MySQL 서버에서는 이런 메모리 과다 사용을 막기 위해서 MySQL 5.5부터 stored_program_cache라는 시스템 변수를 제공하고 있다. 이 변수는 기본 값이 256이며, 설정하는 값의 의미는 스토어드 프로그램 캐시에 저장할 스토어드 프로그램의 개수이다.
스토어드 프로그램 하나 하나의 크기에 의해서도 메모리 사용량이 많이 좌우될 것으로 보이므로, 사실 256이라는 수치가 적절한지 큰 값인지는 판단하기 쉽지 않아 보인다.

만약 스토어드 프로그램 캐시에 저장된 스토어드 프로그램의 개수가 256을 넘게 되면, MySQL 서버는 현재 컨넥션의 스토어드 프로그램 캐시 내용을 모두 무효화시키고 다시 스토어드 프로그램을 하나씩 컴파일해서 저장하게 된다.
물론 스토어드 프로그램이 256개 이상이고 순서대로 하나씩 사용된다면, 위의 무효화 -> 컴파일 과정을 계속 반복하게 될 것이다.

정리하면...

MySQL 스토어드 프로그램의 내부적인 처리 방식을 간단히 살펴보았는데, MySQL의 스토어드 프로그램을 Oracle이나 MSSQL의 그것과 동일하게 생각해서는 안되는 이유를 간략히 정리해보면...
1) 스토어드 프로그램 자체는 컴파일되어서 목적 코드로 관리되지만, 내부의 SQL문장을 파스된 형태(실행계획이나 Parsed-Tree 형태)로 관리하지 않는다.
2) 컴파일된 스토어드 프로그램 목적 코드는 각 컨넥션 단위로 관리되기 때문에 Oracle이나 MSSQL보다 많은 메모리 공간이 필요하다.
3) 스토어드 프로그램이 변경될 때마다, 모든 컨넥션에서 기존 목적 코드의 무효화 및 신규 프로시져의 컴파일 과정일 필요하다.

또한 MySQL은 Web 기반의 단순 쿼리를 고속으로 처리해주는 용도로 많이 활용된다. 그래서 Facebook이나 Twitter 등의 SNS 회사들은 WebScaleSQL이라는 목표로 MySQL 코드 패치를 수행하고 있기도 하다.
이런 방향성으로 본다면, 스토어드 프로그램과 같은 복잡한 절차적 코드(Compound-statement block)를 확장이 어려운 MySQL 서버에 둔다는 것은 적절치 않을 수 있다.
Oracle이나 MSSQL에서는 모든 처리를 DBMS 서버로 집중화하고 서버를 통합(Consolidation) 것이 목표였다면, MySQL의 목표는 그 반대로 볼 수 있다. MySQL은 라이센스 비용이 없으니깐 말이다.
물론 라이센스 비용 이야기는 어떤 형태의 기술 지원을 받는냐에 따라 이야기가 달라지겠지만, 그래도 Oracle이나 MSSQL의 라이센스 비용에 비할바는 아닐 것이다.

리적으로 떨어진 같은 dbms인 mysql db들 간의 join과 subquery 사용은 가능했었습니다

analyze table
1. optimizer가 사용하는 통계정보의 갱신처리이다.
2. InnoDB에서는 자발(자동)적으로 통계 정보를 갱신하기 때문에 별필요가 없다고 한다.
   MyISAM의 경우는 카디날리티가 정확하게 갱신되어 있지만
   InnoDB는 아주 부정확하고 analyze table을 실행하면 빈번이 값이 바뀐다.
   InnoDB의 경우 자동으로 갱신되는 조건은 아래와 같다.
   - 전에 인덱스 통계정보를 갱신한후 테이블의 전체행수의1/16이 갱신된경우
   - 전에 인덱스 통계정보를 갱신한후 20억행이상이 갱신된경우
3. analyze table은 랜덤으로 페이지를 8회추출해서 그 페이지내에 포함된 행 데이타를 조사해서
   인덱스의 통계정보를 근사치로 갱신한다. 근사치이지만 옵티마이저가 사용하기 위한 통계정보로는
   충분하다고 한다. 만약 페이지가 InnoDB 버퍼 풀에 있지 않다면 디스크 Read가 발생하게 되므로 8회되로 제한했는지 모르겠다.
4. MySQL 5.1의 InnoDB에서는 8회로 고정되어있다.
   plugin을 사용하면 innodb_stats_sample_pages 옵션을 조정하여 변경할 수 있다.

optimize table
1. InnoDB의 경우 fragmentation이 발생할 빈도가 높지는 않다. 물론 추기형이 아니므로 vacuum은 필요없다.
2. InnoDB는 MVCC구조이므로 DELETE의 경우 불필요한 로그, 데이타가 남게 되므로 이런 경우 정리가 필요하게 된다.
3. optimize table은 프라이머리키 순서로 데이타를 재배치한다. 이로 인해 인덱스의 정리도 가능해진다.
4. optimize table은 ALTER TABLE t1 ENGINE INNODB;과 동일하다.
5. MySQL은 ALTER TABLE작업은 
   임시 테이블 생성 > 임시 테이블로 복제 > 기존 테이블 DROP > 임시 테이블의 이름을 기존 테이블의 이름으로 변환
   하는 식으로 수행한다. 인덱스가 추가, 갱신등으로 많은 시간을 허비하므로 인덱스를 삭제후 alter table 그후 인덱스 재생성
   식으로 빠르게 수행하도록 한다.
6. 4의 내용을 기반으로 5의 방식을 도입하면 optimize table을 보다 고속으로 수행할수 있다.
   PK이외의 인덱스를 삭제 > optimize table > 인덱스 재생성
   
   
http://blog.naver.com/parkjy76/30098128371
http://blog.naver.com/parkjy76/30135775298
http://blog.naver.com/parkjy76/30135863796

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

InnoDB의 delete optimize table테스트

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

$ ls -al
-rw-rw----  1 mysql mysql      8640 Feb 24 16:16 cate_item.frm
-rw-rw----  1 mysql mysql  50331648 Feb 24 17:33 cate_item.ibd


mysql> delete from cate_item where store_id in(3, 5, 7);
Query OK, 150000 rows affected (3.55 sec)
---------------------------------------------------------------
-rw-rw----  1 mysql mysql      8640 Feb 24 16:16 cate_item.frm
-rw-rw----  1 mysql mysql  50331648 Feb 24 17:33 cate_item.ibd


mysql> optimize table cate_item;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test.cate_item | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.cate_item | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18.11 sec)
---------------------------------------------------------------
-rw-rw----  1 mysql mysql      8640 Apr 18 15:59 cate_item.frm
-rw-rw----  1 mysql mysql  37748736 Apr 18 16:00 cate_item.ibd

http://blog.naver.com/parkjy76/30136379533

하나의 테이블에 타입별로 순위 지정을 해야 하는 경우.

create table jjh_test (
id            bigint , 
char_type     int    ,
score         int
) engine = innodb;

insert into jjh_test values (1,1,1999);
insert into jjh_test values (1,2,1999);
insert into jjh_test values (1,3,1000);
insert into jjh_test values (1,4,3699);

insert into jjh_test values (2,1,4999);
insert into jjh_test values (2,2,2999);
insert into jjh_test values (2,3,200);
insert into jjh_test values (2,4,2699);

insert into jjh_test values (3,1,5999);
insert into jjh_test values (3,4,5699);

insert into jjh_test values (4,1,999);
insert into jjh_test values (4,2,6999);
insert into jjh_test values (4,3,2000);

insert into jjh_test values (5,2,2999);
insert into jjh_test values (5,3,3000);
insert into jjh_test values (5,4,4699);

insert into jjh_test values (6,2,999);
insert into jjh_test values (6,3,100);
insert into jjh_test values (6,4,4699);

insert into jjh_test values (7,2,2999);
insert into jjh_test values (8,2,1999);

-- dense_rank() Over () 동일 -- 즉 동일 랭킹 있으면 그 다음 랭킹은 바로 다음.. 연결 된다 (1,2,2,3... 이렇게)
-- Limitcbt 는 상위 사용자 자르기 위하여 사용 하였으며 그럴 필요가 없다면 제외 하면 됨.

set @rank :=0, @char_type :=null , @score :=null , @cnt :=0 , @nat_id :=null , @cnt:=0 , @cnt1:=0 , @nat_id1 :=null ,@rank_new:=0 ,@v_rank:=0 , @limitcnt:=0 ,@nat_id_limit :=null ;
   
select   id, char_type ,score ,
   greatest (
   @rank := if (@char_type=char_type and @score=score,@rank,if(@char_type <> char_type,1,@rank+1)), 
   least(0,@score :=score),
   least(0,@char_type :=char_type)) as rank ,
   greatest (
   @limitcnt := if (@nat_id_limit=char_type ,@limitcnt+1,if(@nat_id_limit <> char_type,1,@limitcnt+1)), 
   least(0,@nat_id_limit :=char_type) ) as limitcnt
   from 
      (
              select id , char_type , score
            from jjh_test 
                    order by char_type , score desc 
             
     )V1

-- Rank() Over 동일 순위가 연속적이지 않는다. (1,2,2, 4..)  
-- 
set @rank :=0, @char_type :=null , @score :=null , @cnt :=0 , @nat_id :=null , @cnt:=0 , @cnt1:=0 , @nat_id1 :=null ,@rank_new:=0 ,@v_rank:=0 , @limitcnt:=0 ,@nat_id_limit :=null ;
select id , char_type , score  , rank , cnt , cnt1 , 
      greatest ( 
         @rank_new := if (@v_rank <> rank , rank+cnt , @rank_new) , 
          least(0,@v_rank:=rank)
                ) as rank_new     
from  
(
SELECT id , char_type , score , rank , cnt ,
   greatest ( 
           @cnt1 := if (@nat_id1=char_type , @cnt1=@cnt1+cnt , if (@nat_id <> char_type , 0 , cnt)),
     least(0,@nat_id:=char_type))as cnt1  
 from  
    (    
 select id  , char_type , score , rank ,
  greatest (
      @cnt := if(@char_type=char_type and @rank_new=rank , @cnt+1 , if ( @char_type <> char_type , 0 ,@cnt)),
      least(0,@char_type:=char_type),
      least(0,@rank_new:=rank)) as cnt ,limitcnt
      from 
      ( 
       select   id, char_type ,score ,
           greatest (
           @rank := if (@char_type=char_type and @score=score,@rank,if(@char_type <> char_type,1,@rank+1)), 
           least(0,@score :=score),
           least(0,@char_type :=char_type)) as rank ,
           greatest (
           @limitcnt := if (@nat_id_limit=char_type ,@limitcnt+1,if(@nat_id_limit <> char_type,1,@limitcnt+1)), 
           least(0,@nat_id_limit :=char_type) ) as limitcnt
           from 
           (
             select id , char_type , score
             from jjh_test 
             order by char_type , score desc 
                 
          )V1
       )A
      -- where limitcnt < X;  --각 케릭터 별로 상위 X명으로 제한 한다면....
 )B     
)C      

http://cafe.naver.com/mysqlpg/385

http://dbadmin.tistory.com/5

---------
  목차
---------

 MySQL 5.5의 새로운 점
  - 성능과 확장성의 증대
   * InnoDB가 기본 스토리지 엔진이 됨
   * 트랜잭션 중의 메타데이타 락킹 메커니즘이 발전 됨
   * 윈도우 플랫폼에서의 성능이 향상 됨
  - 가용성의 증대
   * 반만 싱크 맞추는 방식의 리플리케이션
   * 리플리케이션 하트비트
  - 편의성의 증대
   * SIGNAL/RESIGNAL
   * 파티션 옵션이 다양해짐
   * 퍼포먼스 스키마
 InnoDB의 새로운 점
  - 성능 향상
   * 복구 성능이 향상됨
   * 하나의 인스턴스에 여러개의 버퍼풀을 잡아줄 수 있음
   * 롤백 세그먼트를 어려개 사용할 수 있음
   * 리눅스 플랫폼에서의 어싱크I/O지원
   * 버퍼링을 변경하는 방식이 확장됨
  - 확장성 증대
   * 로그 시스 뮤텍스의 발전
   * 플러쉬 리스트 뮤텍스의 분리
   * 퍼지 스케쥴링의 향상
  - 더 나아진 도구와 분석법
   * 퍼포먼스 스키마에서의 InnoDB 통계정보

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

MySQL 5.5의 새로운 점

- 성능과 확장성의 증대

* InnoDB가 기본 스토리지 엔진이 됨
 MySQL 5.5부터는 InnoDB가 기본 스토리지 엔진으로 체택되었습니다. 그동안은 MyISAM이 기본 스토리지 엔진으로 사용되었기에, InnoDB의 강점인 ACID-트랜잭션, 외래키 지원, 크래쉬 리커버리등의 기능을 바로 활용하기에는 약간의 불편함이 있었습니다. 하지만 이제는 기본 스토리지 엔진이 InnoDB로 변경되어 새로운 사용자도 쉽게 InnoDB의 특징들을 경험할 수 있게 되었습니다. 
 더불어 MySQL 5.5에서 체택된 InnoDB 스토리지 엔진의 버전은 1.1로 향상되었습니다. InnoDB 1.1에서의 새로운 특징은 다음 글에 정리하도록 하겠습니다.

* 트랜잭션 중의 메타데이타 락킹 메커니즘이 발전 됨
 만약 어떤 테이블이 트랜잭션에 참조되고 있다면, 트랜잭션이 종료되기 전까지는 해당 테이블에 대한 DDL, DROP TABLE, ALTER TABLE등의 작업을 할 수가 없게 되었습니다. 과거에는 전체 트랜잭션이 아닌, SQL구문 하나가 종료되는 순간에 메타데이타 락이 해제되었었습니다. 
 예를 들면, 하나의 세션에서 트랜잭션이 진행 중이고, 그 와중에 잘못된 SQL구문을 입력했습니다. 이 때에 다른 세션에서 DDL문을 입력하였다면, 예전 버전에서는 트랜잭션 중에도 DDL문이 먹혔기에, 테이블 구조가 변경되며 잘못된 SQL 구문이 적용되게 될 수도 있었습니다. 하지만 이제는 트랜잭션 도중에는 메타데이타에 대한 락을 지속적으로 보유하고 있으므로 DDL문이 먹히지 않게 되었습니다.

* 윈도우 플랫폼에서의 성능과 규모가 향상 됨
 윈도우 환경에서 속도와 가용 규모가 향상되었습니다. 
   한 신문기사의 내용 발췌입니다.
벤치마크 테스트 결과에 따르면 마이SQL 5.5 RC는 마이SQL 5.1과 비교해 윈도상에서 읽고 쓰는 작업 성능은 1천500% 향상했고 읽기 작업은 500%까지 강화됐다. 리눅스 기반의 경우 읽고 쓰는 능력은 360%, 읽기만 하는 작업은 200% 강화됐다고 오라클은 전했다
  좌우간 여기선 윈도우 안쓰니 패쓰...


- 가용성의 증대

* 반만 싱크 맞추는(semi-synchronous) 방식의 리플리케이션
 우선 전체를 싱크맞추는 경우와, 전혀 싱크를 맞추는 않는 방식을 이야기 하겠습니다. 
 만약 전체 싱크를 맞추는 (fully-synchronous) 리플리케이션 구성이 되어있다면, 마스터에 연결된 세션이 커밋을 날렸을 때에, 이에 연결되어있는 모든 슬레이브에서도 커밋을 적용 완료 해야지만 트랜잭션 완료 메세지를 세션에 반납하는 방식입니다. 따라서 트랜잭션 시간이 길어지게 되는 문제가 있었습니다. 
 전혀 싱크를 맞추지 않는 (asynchronous) 방식으로 리플리케이션이 구성되어 있다면, 슬레이브에 트랜잭션 내용을 적용 완료했는지의 여부와 상관없이, 마스터에서는 트랜잭션을 완료하고, 계속적으로 새로운 트랜잭션이 진행됩니다. 이 경우에는 트랜잭션 완료시간은 빨리지겠지만, 가용성에서는 문제가 있을 것입니다.
 이번에 새로이 등장한 반만 싱크를 맞추는 개념(semi-synchronous)은, 이 두 구성의 절충점 정도라 보시면 됩니다. 모든 리플리케이션 슬레이브에 대한 싱크가 맞추어지기를 기다리는 것이 아니라, 최소, 단 하나의 슬레이브에서만 싱크가 맞추어 졌다면 트랜잭션을 완료시키는 방식입니다.

* 리플리케이션 하트비트
 리플리케이션 구성의 노드 가용성을 체크하기 위해서 하트비트 메커니즘이 사용되어 있습니다. 여기에 하트비트 체크 시간을 변경할 수 있게 되었습니다.

  STOP SLAVE;
  CHANGE MASTER TO master_heartbeat_period= milliseconds;
  START SLAVE;
  SHOW STATUS like 'slave_heartbeat period'
  SHOW STATUS like 'slave_received_heartbeats'

- 편의성의 증대
* SIGNAL/RESIGNAL
 SIGNAL/RESIGNAL명령은 스토어드-프로시저, 사용자 정의함수, 트리거 등을 작성할때에 예외처리를 어떻게 할지에 대한 제어를 가능하게 끔 해줍니다. 
 SIGNAL명령은 자바와 같은 다른 언어에서의 THROW, RAISE 구분과 비슷하게 활용할 수 있습니다. 이를 통해 에러 구문들을 말끔히 정리하는 것이 가능할 것입니다.
 RESIGNAL명령을 통해서는 에러를 통과시키거나, 기본 에러 정보를 수정할 수 있게끔 해줍니다.

* 파티션 옵션이 다양해짐
 컬럼 레벨의 파티셔닝이 가능하게 되었습니다. RANGE COLUMNS, LIST COLUMNS옵션을 CREATE TABLE명령문에 끼워 넣음으로써 설정이 가능합니다. 
 RANGE와 LIST파티셔닝의 차이점은 오라클에서와 같습니다. 
 자세한 구현법은 http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html 페이지에서 확인가능합니다.

* 퍼포먼스 스키마
 퍼포먼스 관리를 위한 performance_schema라는 이름의 스키마를 추가적으로 사용할 수 있습니다. 이 스키마에는 MySQL 내부적인 디테일한 성능 정보를 담고있는 테이블들이 저장됩니다. 현재 시점에서의 성능 뿐 아니라, 과거의 정보들까지도 기록해 둘 수 있습니다. 
 퍼포먼스 스키마에는 굉장히 다양한 내용이 들어 갈 수 있으므로, 마찬가지로 직접 해당 사이트를 (http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html) 살펴보시는 것이 도움될 것 같습니다.

sh: 0: 어쩌구 나오면

Ubuntu - source command not found 나올때

Ubuntu는 성능 향상을 이유로 dash 를 기본 쉘로 쓰고 있다. 

이 경우 bash 에서 사용하는 source 가 안먹는데

dash 를 bash로 바꾸려면 아래의 명령어를 입력한후 <아니오> 를 선택하면 된다. 

역으로 bash 에서 다시 dash 로 가려면 <예> 를 선택한다.

$ sudo dpkg-reconfigure dash

그 대신 해당경로에서 실행하거나 절대경로를 써줘야한다.