SEARCH
TOOLBOX
LANGUAGES
Mysql

Mysql

From Chaehyun

Revision as of 08:38, 5 October 2012 by Xlos (Talk | contribs)
Jump to: navigation, search
  • mysql innodb status
    • History list length 1616091 ??

Contents

innoDB purge 관련

  • innoDB 에서는 sql에 의해 row를 지우더라도, 실제 database에서 row가 즉시 물리적으로 삭제 되지 않음
  • 삭제를 위한 undo log record가 innodb에서 버려졌을 때, 그제서야 해당되는 row와 index rocord를 db에서 물리적으로 삭제함
  • 이러한 삭제 작업을 purge라고 칭함. 그리고 이 purge 작업은 굉장히 빠르고, 보통은 sql의 삭제와 동일한 time order를 가짐
  • my.cnf
  • innodb_max_purge_lag = 8192
    • http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
    • purge 작업이 지체되었을 때, 얼마나 기다릴 것인지 조절하는 설정
    • 기본은 0임. (delay 없음)
    • 값이 있을 때는, ((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds 만큼, insert, update, delete 작업을 delay 시킨다.
  • show innodb status 하면 innoDB monitor output을 볼 수 있음
    • History list length 값이 바로 purge_lag의 값임
  • http://mysqlha.blogspot.com/2008/07/how-do-you-know-when-innodb-gets-behind.html
  • History list length가 커지면, background thread에 의해 실행될 pending된 io가 많아진다는 것을 의미함. background thread가 이 io를 수행하는 속도는 서버의 현재 상태 (busy or idle)와 서버가 초당 100개의 IO를 처리할 수 있다는 가정에 따라 결정된다.
  • primary key 순서대로 스캔하는 thread와 지우는 thread가 동시에 있을 때, 스캔하는 thread는 delete thread에 의해 삭제 되었지만, 아직 purge되지 않은 row를 만날 수 있다. 만약 백그라운드에서 돌고 있는 purge가 delete 작업을 따라잡지 못하면, 스캔 작업은 생각했던 것 보다 굉장히 느릴 것이다. 그러므로 innodb_max_purge_lag을 설정하여, insert, update, delete 작업을 delay 시켜 버린다.
  • innodb_buffer_pool_size 를 키우자. 메모리의 70~80% 까지. (현재 우리는 1GB로 설정되어 있음)
  • SET GLOBALinnodb_max_dirty_pages_pct=0 를 통해 dirty page 비율을 조절할 수 있음
    • 기본값은 90
  • http://blogs.innodb.com/wp/2011/04/mysql-5-6-multi-threaded-purge/
  • 5.5 이전 버전에서는 innodb master thread가 purge와 flushing을 같이 수행한다. 그러므로 서버에 로드가 많이 걸리고 dirty page가 많이 나오면, master thread가 대부분의 시간을 flushing을 하는데 써 버리기 때문에 purging이 되지 않는 문제가 있었음

data 옮기기

  • data를 삭제하기 보다는 새로 insert를 하는 게 더 낫다
  • select ~ where~ into outfile "output"
  • loaddata infile "table" into table Document
    • insert 보다 30배 빠르다는데?
    • unique 확인을 끄는 설정을 켜면 더 빠름
  • mysqldump의 경우, sql로 만드므로, dump file이 커지고, data를 넣을 때, insert를 실행하기 때문에 느리다

설정 값 바꾸기

  • show global variable;
  • set global innodb_max_purge_lag = 0
    • 이러면 바뀜

disk 상태 보기

  • iostat -k 1
  • 설치하려면
    • yum install sysstat

다중 출력

  • mysql -E -e "select~" 라고 하면 수직으로 출력됨

InnoDB MVCC (Multi Version Concurrency Control)

  • http://intomysql.blogspot.com/2010/12/mvcc-multi-version-concurrency-control.html
  • 동시에 하나의 레코드에 대해서 여러개의 버전이 관리됨
  • 특정 레코드가 어떤 트랜잭션에 의해서 변경되고 있는 경우, 다른 트랜잭션은 이전 버전의 레코드만 읽을 수 있음
    • 특별한 잠금 없이 레코드를 읽을 수 있음 (Consistent non-locking read라고 표현)
  • 데이터 페이지에는 항상 가장 최근 버전의 레코드가 저장됨 (Commit되지 않은 경우라 하더라도)
  • 레코드의 변경이 가해지면, 이전 버전은 시스템 테이블 스페이스의 Undo 영역으로 복사됨
  • Undo log의 종류
    • INSERT : Insert 에 의해서 발생한 Undo log 레코드이며 트랜잭션이 완료되면 불필요하므로 삭제됨
    • UPDATE : Update 또는 Delete에 의해서 발생한 Undo log 레코드. 트랜잭션이 완료되어도, MVCC를 위해서 보관되어야 함.
  • 최종적으로 불필요한 Undo log 레코드는 Purge thread가 모두 제거하게 됨
  • Update 작업이 매우 빈번한 경우, Purge thread가 불필요한 Undo log 제거를 제때에 처리하지 못할 수 있음
    • 이런 경우 "History length" 가 계속 증가하게 됨 (SHOW ENGINE INNODB STATUS 명령으로 확인 가능)
  • Undo log 포맷
    • Primary key
    • 트랜잭션 아이디 (현재 레코드를 변경한 트랜잭션의 아이디)
    • 변경 이전 값

java에서 접근할 때

  • jdbc:mysql://reople.com/movie?useUnicode=true&characterEncoding=utf8&user=xlos&password=pass;
    • xml에서는 & 대신에 & amp; 라고 써야함
  • 혹은
    • db.addConnectionProperty("useUnicode", "true");
    • db.addConnectionProperty("characterEncoding", "UTF-8");

mysql이 안 뜰 때 (ubuntu 기준)

  • /var/log/syslog 확인
  • /etc/mysql/my.conf 확인

csv file 바로 import 하기

  • load data infile '/tmp/item.txt' into table item_to_item_batch fields terminated by ',' lines terminated by '\n' (item_id, recommended, score);
  • ERROR 29 (HY000): File '/mnt/users/chaehyun/temp/item.txt' not found (Errcode: 13) 발생 시
    • 해당 파일을 /var/lib/mysql/fancy/item.txt 로 옮김
  • 한글이 깨질 경우,
    • load data infile 'item_meta.txt' into table item character set utf8 fields terminated by '\|' lines terminated by '\n' (item_id, name, cat1, cat2, author, download, keywords);
  • ERROR 13 (HY000): Can't get stat of '/home/skplanet/users/chaehyun/data/input.txt' (Errcode: 13) 에러 발생한 경우
    • load data local infile '/home/skplanet/users/chaehyun/data/input.txt' into table path fields terminated by ',' lines terminated by '\n' (src,dst,time);

Table './codesprint/path' is marked as crashed and should be repaired 오류 발생

hash 로 indexing 하기

  • CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY;

memory table 최대 크기 설정

  • max_heap_table_size 시스템 변수는 MEMORY 테이블이 얼마나 커질 수 있는지를 나타낸다. 이 변수는 CREATE TABLE로 생성된 것을 포함해서 모든 MEMORY 테이블에 적용된다. 하지만, 내부 MEMORY 테이블의 경우에는 실제 최대 크기가 tmp_table_size와 연결된 max_heap_table_size로 결정된다: 두 변수 중에 작은 값이 적용된다. 내부 MEMORY 테이블의 크기가 제한치를 초과하게 되면, MySQL은 자동으로 디스크 기반 MyISAM 테이블로 변환 시킨다.

python에서 사용할 때

innodb replication 설정

순서대대로 봅시다

  1. master 서버에서 /etc/my.cnf 에 추가
    • [mysqld]
    • log-bin=mysql-bin
    • server-id=1
    • sudo /etc/init.d/mysqld restart
  2. slave 서버에서
    • [mysqld]
    • server-id=2
    • sudo /etc/init.d/mysqld restart
  3. master 서버에서 계정 생성
    • mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
    • mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  4. master 서버에서 기존 data dump
    • mysql> FLUSH TABLES WITH READ LOCK; //잠시 커밋을 막고
    • mysql > SHOW MASTER STATUS; //여기 나온 숫자들을 기록
      • File이랑 Position
      • 혹시 기존에 binary logging을 한적이 없다면, filename : position : 4로 설정
    • mysqldump --all-databases --master-data >dbdump.db
    • mysql> UNLOCK TABLES;
    • dump file을 slave로 복사
  5. slave 서버에서 
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
    • 한 번만 설정해두면 됨..
Retrieved from "http://chaehyun.kr/w/Mysql"