From Chaehyun

(Difference between revisions)

Jump to: navigation, search

Latest revision as of 16:19, 26 February 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 설정

순서대대로 봅시다

master 서버에서 /etc/my.cnf 에 추가
- [mysqld]
- log-bin=mysql-bin
- server-id=1
- sudo /etc/init.d/mysqld restart
slave 서버에서
- [mysqld]
- server-id=2
- sudo /etc/init.d/mysqld restart
master 서버에서 계정 생성
- mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
- mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
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로 복사
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;

- 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';
grant all privileges on movie.* to dbuser@localhost identified by 'password';
FLUSH PRIVILEGES;
- grant인 경우 따로 해 줄 필요 없음

특정 쿼리 취소 (cancel slow query)

show processlist;
kill query 21445;
- http://johnstanfield.com/?p=223

@@ Line 1: / Line 1: @@
 * mysql innodb status
 ** History list length 1616091 ??
-== innoDB perge 관련 ==
+== innoDB purge 관련 ==
 * innoDB 에서는 sql에 의해 row를 지우더라도, 실제 database에서 row가 즉시 물리적으로 삭제 되지 않음
 * 삭제를 위한 undo log record가 innodb에서 버려졌을 때, 그제서야 해당되는 row와 index rocord를 db에서 물리적으로 삭제함
@@ Line 13: / Line 13: @@
 * 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 옮기기 ==
@@ Line 29: / Line 37: @@
 == 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
+== 순서대대로 봅시다 ==
+# master 서버에서 /etc/my.cnf 에 추가
+#* [mysqld]
+#* log-bin=mysql-bin
+#* server-id=1
+#* sudo /etc/init.d/mysqld restart
+# slave 서버에서
+#* [mysqld]
+#* server-id=2
+#* sudo /etc/init.d/mysqld restart
+# master 서버에서 계정 생성
+#* mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
+#* mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
+# 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로 복사
+# slave 서버에서
+#* 한 번만 설정해두면 됨..
+<pre>
+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;
+</pre>
+#* mysq> START SLAVE;
+== binary export ==
+<pre>
+#!/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
+</pre>
+== 설정 관련 ==
+* 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';
+* grant all privileges on movie.* to dbuser@localhost identified by 'password';
+* FLUSH PRIVILEGES;
+** grant인 경우 따로 해 줄 필요 없음
+= 특정 쿼리 취소 (cancel slow query) =
+* show processlist;
+* kill query 21445;
+** http://johnstanfield.com/?p=223

Navigation

SEARCH

TOOLBOX

LANGUAGES

Mysql

From Chaehyun

Latest revision as of 16:19, 26 February 2013

Contents

innoDB purge 관련

data 옮기기

설정 값 바꾸기

disk 상태 보기

다중 출력

InnoDB MVCC (Multi Version Concurrency Control)

java에서 접근할 때

mysql이 안 뜰 때 (ubuntu 기준)

csv file 바로 import 하기

csv file 로 바로 export 하기

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

hash 로 indexing 하기

memory table 최대 크기 설정

python에서 사용할 때

innodb replication 설정

순서대대로 봅시다

binary export

설정 관련

루트 비번 분실

사용자 비번 변경

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

inno db log size error

mysql 사용자 계정 설정

특정 쿼리 취소 (cancel slow query)