From Chaehyun

Revision as of 04:36, 31 January 2013

• mysql innodb status
  • History list length 1616091 ??

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);
• ERROR 1148 (42000): The used command is not allowed with this MySQL version 에러 발생한 경우
  • mysql -u user -p --local-infile dbname
  • 위와 같이 mysql을 켜면 됨

csv file 로 바로 export 하기

• mysql -h fancy-secondary-east.thingcloud.info -u recengine -p django_fancy_db -e "select * from fancy_categoryitem order by id" --skip-column-names | tr '[[:blank:] ]' ','
• mysql -husershard0-secondary-east.thingcloud.info -urecengine -p -e "select thing_id,id from fancy_shard0.fancy_newthing" --skip-column-names | tr '[[:blank:] ]' ',' > newthing

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

• repair path
• http://forcecore.tistory.com/428

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에서 사용할 때

• easy_install mysql-python
• http://stackoverflow.com/questions/454854/no-module-named-mysqldb

innodb replication 설정

• http://wiki.kldp.org/wiki.php/MySQL%B8%AE%C7%C3%B8%AE%C4%C9%C0%CC%BC%C7#s-1.3
• http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21425&cat1=6&cat2=201&cat3=0&lang=k
• http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

순서대대로 봅시다

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;

1. • mysq> START SLAVE;

binary export

#!/bin/bash
for i in {0..93}
do
    mysql -uroot -p'*****' --default-character-set=utf8 -E -e "select source from codesprint.submit where id=$i into dumpfile '/tmp/answer/$i'"
    #!/bin/bash
    echo $i
done

설정 관련

• max_binlog_size=1073741824
  • binary log file이 이 크기보다 커지면, 이 로그 파일을 닫고, 다음 로그 파일을 연다
  • transaction이 큰 경우, 로그 파일이 이 크기보다 커질 수 있다.
  • 바이너리 로그는 갱신쿼리를 기록한 것으로 리커버리 및 replication에 사용되는 중요한 로그파일이다.
• innodb_log_file_size=5242880
  • 이 값이 클수록, buffer pool에서 checkpoint flush를 덜하게 되고, disk I/O를 덜하게 된다. 대신 crash가 났을 때 recovery가 더 오래 걸리게 된다
  • 이 로그 파일의 사이즈를 buffer_pool의 15% 정도로 설정하는 것이 바람직하다

루트 비번 분실

• mysqladmin -u root -p shutdown

사용자 비번 변경

• use mysql;
• update user set password=password('123456') where user='root';
• flush privileges; <--- 적용

외부에서 접근 가능하도록 변경

• use mysql;
• insert into user (Host, User, Password) values ('%', 'root', password('pass'));
• flush privileges;
• grant all privileges on *.* to root@'%' identified by 'password' with grant option;

inno db log size error

• InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
• InnoDB: than specified in the .cnf file 0 104857600 bytes!
• 위와 같은 에러가 발생하면,
  • sudo rm /var/lib/mysql/ib_logfile*
  • sudo /etc/init.d/mysqd restart

mysql 사용자 계정 설정

• grant all privileges on `dbuser_%`.* to dbuser@localhost identified by 'password' with grant option;
• FLUSH PRIVILEGES;