'mysql'에 해당되는 글 16건

  1. 2018.12.19 복제 Replication
  2. 2018.12.19 show processlist 에 where 조건 사용하기
  3. 2018.12.19 샤딩 sharding
  4. 2018.12.18 트랜잭션 Transcation, 락 Lock, 격리수준 IsolationLevel
  5. 2018.12.17 파티셔닝 Partitioning
  6. 2016.06.16 Azure Linux VM에서 MySQL 성능 최적화
  7. 2013.10.28 보안 강도에 따른 해쉬함수 분류 ( sha2 512 in mysql, php )
  8. 2013.10.11 group_concat 중복제거하기
  9. 2013.09.27 MySQL 현재 접속자 보기 및 MYSQL 모니터링 명령어
  10. 2013.08.08 mysql 튜닝 최적화

복제 Replication

mysql 2018.12.19 17:24


Slave IO Thread

- 마스터 DBMS에 로그인해서 바이너리로그를 읽어 릴레이로그에 쓰는 쓰레드


Slave SQL Thread

- 릴레이로그에서 실제 슬레이브 DBMS에 반영하는 쓰레드


docker run -d -e MYSQL_ROOT_PASSWORD=sample --name=master mariadb:10.1

docker run -d -e MYSQL_ROOT_PASSWORD=sample --name slave mariadb:10.1


docker inspect master | grep "IPAddress"

docker inspect slave | grep "IPAddress"


docker exec -it master bash


cd /etc/mysql


my.cnf/my.ini


master

[mysqld]

pid-file = /var/run/mysqlid/mysqlid.pid

socket = /var/run/mysql/mysqlid.sock

datadir = /var/lib/mysql

log-error = /var/log/mysql/error.log 

symbolic-links = 0

bind-address = 0.0.0.0

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M 



docker exec -it slave bash


cd /etc/mysql


my.cnf/my.ini


slave

[mysqld]

pid-file = /var/log/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.dock

datadir = /var/lib/mysql

log-error = /var/log/mysql/error.log 

symbloic-links = 0

bind-address = 0.0.0.0

server-id = 2

relay-log = /var/log/mysql/relay.log

relay_log_purge = TRUE

read_only



sudo service mysql restart


$ docker commit -m "xxx" -a "aaa" master master_new

$ docker commit -m "xxx" -a "aaa" slave slave_new


$ docker images


$ docker run -d -e MYSQL_ROOT_PASSWORD=sample --name=master_new master_new

$ docker run -d -e MYSQL_ROOT_PASSWORD=sample --name=slave_new slave_new


$ docker rm -f master

$ docker rm -f slave


$ docker ps -a


$ docker inspect master_new | grep "IPAddress"

$ docker inspect slave_new | grep "IPAddress"


$ docker exec -it master_new bash


create user 'replicationuser1'@'%' identified by 'sample';


grant replication slave, replication client on *.* to 'replicationuser1'@'%' identified by 'slavepass';


flush privileges;


master 상태 확인

show master status;


master db 덤프 후 slave로 복사


# docker exec -it master_new bash

mysqldump -lock-all-tables -all-databases -master-data=1 -uroot -p > dump.sql


# docker cp master_new:/dump.sql . 

# docker cp dump.sql slave_new:/


slave


# docker exec -it slave_new bash


mysql-uroot -p < dump.sql


change master to master_log='mariadb-bin.001482' master_log_pos=4415249, master_host='172.17.0.x', master_port=3306, master_user='replicationuser1', master_password='slavepass';


show slave status;

-> Slave_IO_Running

-> Slave_SQL_Running


start slave;


show slave status;

-> Slave_IO_Running

-> Slave_SQL_Running



'mysql' 카테고리의 다른 글

복제 Replication  (0) 2018.12.19
show processlist 에 where 조건 사용하기  (0) 2018.12.19
샤딩 sharding  (0) 2018.12.19
트랜잭션 Transcation, 락 Lock, 격리수준 IsolationLevel  (0) 2018.12.18
파티셔닝 Partitioning  (0) 2018.12.17
Azure Linux VM에서 MySQL 성능 최적화  (0) 2016.06.16
Trackbacks 0 : Comments 0

show processlist 에 where 조건 사용하기

mysql 2018.12.19 13:05


SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where time > 10 and info is not null;


how to customize `show processlist` in mysql? 2009.05.30

http://stackoverflow.com/questions/929612/how-to-customize-show-processlist-in-mysql


Chapter 23 The INFORMATION_SCHEMA PROCESSLIST Table

https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.1/en/processlist-table.html


'mysql' 카테고리의 다른 글

복제 Replication  (0) 2018.12.19
show processlist 에 where 조건 사용하기  (0) 2018.12.19
샤딩 sharding  (0) 2018.12.19
트랜잭션 Transcation, 락 Lock, 격리수준 IsolationLevel  (0) 2018.12.18
파티셔닝 Partitioning  (0) 2018.12.17
Azure Linux VM에서 MySQL 성능 최적화  (0) 2016.06.16
Trackbacks 0 : Comments 0

샤딩 sharding

mysql 2018.12.19 08:20


데이터베이스 샤딩 sharding


- 파티셔닝과 달리 별도의 샤드를 구축, 샤딩 플랫폼(스파이더)를 통해 구현

- 쓰기 성능이 지속적으로 향상됨

- Spider 기반 샤딩 구축


수직(Verical) 샤딩

- 샤드를 만들어 저장할 때 구분 기준을 연령/성별과 같은 기준으로 나눠 구성

- 테이블 단위로 분할


수평(Horizontal) 샤딩

- 동일한 시스템을 지역/국가별로 나눠서 구성하는 경우

- 동일한 테이블을 여러 개로 분할


범위(range) a-m /n-r / s-z

해시(hash) 해시함수 파티션별로 크기를 비슷하게 나눔

리스트(list) 특정한 컬럼을 기준

컴포지트(composite) range-hash/range-list


mariadb 다운로드

docker pull mariadb:10.1


스파이더 인스턴스 실행

docker run -d -e MYSQL_ROOT_PASSWORD=sample --name=spider mariadb:10.1


접속

docker exec -it spider bash


테스트인스턴스 실행

docker run -d -e MYSQL_ROOT_PASSWORD=sample --name=sample1 mariadb:10.1

docker run -d -e MYSQL_ROOT_PASSWORD=sample --name-sample2 mariadb:10.1


실행 인스턴스 확인

docker ps -a


테스트인스턴스 접속

docker exec -it sample1 bash

docker exec -it sample2 bash


IP주소 확인

docker inspect spider | grep "IPAddress"

docker inspect sample1 | grep "IPAddress"

docker inspect sample2 | grep "IPAddress"


docker exec -it spider bash

docker exec -it sample1 bash

docker exec -it sample2 bash


cd /user/share/mysql


스파이더 설치

mysql -u root < /usr/share/mysql/install_spider.sql


스파이더 엔진 설치확인

show engines;


https://mariadb.com/kb/en/mariadb/spider-storage-engine-overview/

http://yakolla.tistory.com/69


spider sample1,2 에 사용자추가 및 권한 할당

use mysql;

create user 'spider-test'@'%' identified by 'sample';

grant all on *.* to 'spider-test'@'%' with grant option;

flush privileges;


샤딩 플렛폼에 서버정보 등록하기

create server sample1

 foreign data wrapper mysql

options(

 host '172.17.0.x',

 database 'sampleDB',

 user 'spider-test',

 password 'sample',

 port 3306

);


create server sample2

 foreign data wrapper mysql

options(

 host '172.17.0.x',

 database 'sampleDB',

 user 'spider-test',

 password 'sample',

 port 3306

);


서버정보 확인하기

select * from mysql.servers;


spider 에 table 생성

create database sampleDB;

user sampleDB;

create table shardingTest(id int not null auto_increment, name varchar(255) not null, address varchar(255) not null, primary key(id) engine=spider comment='wrapper "mysql", talbe "shardingTest"' partition by key(id) (partition sample1 comment = 'srv "sample1"', partition sample2 comment = 'srv "sample2"' );


sample1,2 에 talbe 생성

create database sampleDB;

user sampleDB;

create table shardingTest

(

 id int not null auto_increment,

 name varchar(255) not null, 

 address varchar(255) not null,

 primary key(id)

);


spider 접속 후 

user sampleDB;

insert into shardingTest(name, address) values ('kim', 'seoul');

insert into shardingTest(name, address) values ('lee', 'seoul');

insert into shardingTest(name, address) values ('park', 'seoul');

insert into shardingTest(name, address) values ('kim', 'busan');

insert into shardingTest(name, address) values ('lee', 'daegu');

insert into shardingTest(name, address) values ('park', 'jeju');


select * from shardingTest;


sample1,2에서도 각각 확인


select * from shardingTest;



VLDB Very Large DBMS


Sharding

- DBMS 내용 분할 (DB/테이블)

- Write 성능 향상


Replication

- 동일한 DBMS를 여러 개 유지(마스터/슬레이브)

- Read 성능 향상


'mysql' 카테고리의 다른 글

복제 Replication  (0) 2018.12.19
show processlist 에 where 조건 사용하기  (0) 2018.12.19
샤딩 sharding  (0) 2018.12.19
트랜잭션 Transcation, 락 Lock, 격리수준 IsolationLevel  (0) 2018.12.18
파티셔닝 Partitioning  (0) 2018.12.17
Azure Linux VM에서 MySQL 성능 최적화  (0) 2016.06.16
Trackbacks 0 : Comments 0

트랜잭션 Transcation, 락 Lock, 격리수준 IsolationLevel

mysql 2018.12.18 14:39


--


Transcation


select @@autocommit;


set autocommit = false;


create database sampleDB;


create table BusinessCard(Name varchar(255), Address varchar(255), Telephone varchar(255));


insert into BusinessCard values ('Bob', 'Seocho-dong 123', '123-4567');


commit;


rollback;


--


Lock


select engine, support from information_schema.engines where support='DEFAULT';


set default_storage_engine = InnoDb;

set default_storage_engine = MyISAM;


select engine from information_schema.tables where table_name = 'city' and table_schema='world';


alter table 'city' engine = InnoDB;


--


Isolation level


select @@tx_isolation;


set tx_isolation = 'READ-COMMITED';


commit;


--


트랜잭션의 격리수준 Transcation IsolationLevel


ACID ( Atomicity / Consistency / Isolation / Durability )

- 원자성 Atomicity : All or Nothing, Commit / Rollback

- 일관성 Consistency : 트랜잭션 전후에 데이터가 손상을 받으면 안됨, 같은 데이터가 다른 값을 가지면 안됨

- 고립성/격리수준 Isolation : 여러 개의 트랜잭션이 수행될 때 성능과 데이터 안정성간의 trade-off

- 지속성 Durability : 트랜잭션이 종료된 이후에도 데이터에 문제가 없어야 됨


Read Uncommitted

- 트랜잭션이 종료되지 않는 상태의 대상 데이터를 읽어가는 것

- 트랜잭션이 처리되는 도중에 다른 트랜잭션이 해당 데이터를 읽기를 허용 ( uncommitted date )

- 트랜잭션이 종료되면 데이터 값이 변경 될 수 있음

- 예를 들면 개표가 진행 중인 득표 현황으로 현재 값을 참고하지만 최종 값은 변경 될 수 있음

- 성능은 높지만 데이터의 안정성이 떨어짐


Read Committed

- 트랜잭션이 종료 된 상태의 데이터만 읽는 것 ( committed data )

- 오라클이나 일반적인 DBMS 의 기본 격리수준

- 현재 트랜잭션이 진행되면서 데이터를 참고할 때 다른 트랜잭션에서는 작업이 종료된 데이터만을 읽는 다는 것

- 이 경우 트랜잭션에서 동일한 쿼리는 여러 번 수행시킬 때 쿼리 값이 달라지는 경우가 생길 수 있음

- 이 경우를 해결해 주는 것이 Repeatable Read


Repeatable Read

- 트랜잭션이 진행되는 도중에는 쿼리를 반복 수행하더라도 결과 값은 계속 동일

- 즉, 데이터가 캐싱/버퍼링 됨

- DB 백업 시간이 오래 걸릴 경우 백업이 진행되는 도중에는 데이터가 수정되어도 백업 시작 시점의 데이터를 백업하도록 함

- 다른 트랜잭션이 업데이트하는 것은 금지하지만 레코드 추가하는 것은 허용하는 방식

- MariaDB 의 기본 격리 수준


Serializable

- 트랜잭션끼리 겹치는 일이 발생하지 않음

- 트랜잭션이 동시에 수행되는 것이 금지되고 순차적으로 수행됨

- 대신 성능이 가장 낮음, 안정성은 높음


CAP ( Consistency / Availability / Partition Tolerance )







Trackbacks 0 : Comments 0

파티셔닝 Partitioning

mysql 2018.12.17 14:51


create table BusinessCard

(id int not null, name varchar(255), address varchar(255), telephone varchar(255), createtime date)

  partition by range(year(createtime))(

  partition p0 values less than (2013),

  partition p1 values less than (2014),

  partition p2 values less than (2015),

  partition p3 values less than MAXVALUE

);


show create table BusinessCard\G


insert into BusinessCard values (1, 'kim','seoul',' 123-456', '2000-01-01');

insert into BusinessCard values (2, 'lee','seoul',' 123-456', '2014-01-01');


explain partitions select * from BusinessCard where createtime >= '2014-01-01' \G


파티션 추가/삭제

alter table BusinessCard add partition(

   partition p4 values less than (2005));


alter table BusinessCard drop partition p4;


파티션 분할/병합

alter table BusinessCard

 reorganize partition p3 into (

 partition p3 values less than(2015), 

 partition p4 values less than MAXVALUE

);


alter table BusinessCard

 reorganize partition p2, p3 into (

 partition p23 values less than (2014));




Trackbacks 0 : Comments 0

Azure Linux VM에서 MySQL 성능 최적화

mysql 2016.06.16 15:21


원문: 

https://azure.microsoft.com/ko-kr/documentation/articles/virtual-machines-linux-classic-optimize-mysql/



매개 변수


기본값



optmization

innodb_buffer_pool_size없음7G
innodb_log_file_size5M512M
max_connections1005000
innodb_file_per_table01
innodb_flush_log_at_trx_commit12
innodb_log_buffer_size8M128M
query_cache_size16M0




Trackbacks 0 : Comments 0

보안 강도에 따른 해쉬함수 분류 ( sha2 512 in mysql, php )

mysql 2013.10.28 15:09
[출처] KISA, 방송통신위원회 2012.9 개인정보의 기술적 관리적 보호조치 기준 해설서

보안강도

해쉬함수

안전성

80 비트 미만

MD5, SHA-1

권고하지 않음

80 비트

HAS-160

112 비트

SHA-224

2013년까지 권고

128 비트

SHA-256

2013년 이후에도 가능

192 비트

SHA-384

256 비트

SHA-512

sha2 512 in php 
echo hash('sha512', 'qwe123');

sha2 512 in mysql

 

SELECT SHA2('qwe123', 512);


sha2 512 in javascript


http://coursesweb.net/javascript/sha512-encrypt-hash_cs

 


Trackbacks 0 : Comments 0

group_concat 중복제거하기

mysql 2013.10.11 14:36


group by 로 그룹화 한 데이터의 내역을 확인 할 때 group_concat 을 사용합니다.

이때, 조회되는 데이터에서 중복을 정리하고자 할 때 DISTINCT 를 사용할 수 있습니다. 


ex)

select count(DISTINCT id), group_concat(DISTINCT id) from members group by name




Trackbacks 0 : Comments 0

MySQL 현재 접속자 보기 및 MYSQL 모니터링 명령어

mysql 2013.09.27 16:26
출처 : http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139

가. 모니터링 및 초기화 명령어
  • show status - MySQL 데이타베이스의 현재 상황
  • show Processlist - MySQL 프로세스 목록
  • show variables - 설정 가능한 모든 변수 목록
  • flush logs - MySQL의 로그파일 초기화
  • flush status - MySQL 상태정보 초기화
  • flush thread - 쓰레드 캐시에 저장된 쓰레드 초기화
  • flush tables - MySQL에 캐싱된 테이블 초기화
  • flush privileges - 권한정보 재 설정
나. Connection 튜닝
1. status 
  • Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
  • Aborted_connects - MySQL 서버에 접속이 실패된 수
  • Max_used_connections - 최대로 동시에 접속한 수
  • Threads_cached - Thread Cache의 Thread 수
  • Threads_connected - 현재 연결된 Thread 수
  • Threads_created - 접속을 위해 생성된 Thread 수
  • Threads_running - Sleeping 되어 있지 않은 Thread 수
2. system variables
  • wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
  • thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
  • max_connections - 최대 동시 접속 가능 수
그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%connect%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| Aborted_connects     | 3782    |
| Connections          | 2961108 |
| Max_used_connections | 90      |
| Threads_connected    | 1       |
+----------------------+---------+
4 rows in set (0.01 sec)

mysql> show status like '%clients%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 2160  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 7     |
| Threads_connected      | 1     |
| Threads_created        | 1364  |
| Threads_running        | 1     |
+------------------------+-------+
6 rows in set (0.00 sec)

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%
3. 튜닝
  • Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
  • DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
  • Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
  • MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
다. Memory 튜닝
1. status
  • key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
  • key_reads - Key Block 읽기 요청시 Disk을 읽은 수
  • key_read_requests - Key Block 읽기 요청수
2. system variables
  • key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
  • table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
  • myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
  • join_buffer_size - 조인을 위한 메모리 버퍼 크기
  • record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
  • record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
  • sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
  • tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
  • key_cache_block_size - block 의 크기(bytes, 기본값 1024)
mysql> show status like '%key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Com_preload_keys       | 0         |
| Com_show_keys          | 2945      |
| Handler_read_key       | 365020739 |
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 222601    |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 847204435 |
| Key_reads              | 4195954   |
| Key_write_requests     | 25034738  |
| Key_writes             | 16452136  |
+------------------------+-----------+
10 rows in set (0.00 sec)


Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100

* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

3. 튜닝
  • key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
  • Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.
라. 적용
system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.
[www@smson www]$ vi /etc/my.cnf  # The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout = 120

~~~

[root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart


마. 참고


Trackbacks 1 : Comments 0

mysql 튜닝 최적화

mysql 2013.08.08 16:14


[출처] http://lonmoon.tistory.com/324


mysql> show status;

- Aborted_clients : 클라이언트 프로그램이 비정상적으로 종료된 수
- Aborted_connects : MySQL서버에 접속이 실패된 수
- Max_used_connections : 최대로 동시에 접속한 수
- Threads_cached : Thread Cache의 Thread 수
- Threads_connected : 현재 연결된 Thread 수
- Threads_created : 접속을 위해 생성된 Thread 수
- Threads_running : Sleeping 되어 있지 않은 Thread 수


mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

 

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |  종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+

 

mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 0                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| pseudo_thread_id                        | 33865                     |
| thread_cache_size                       | 8                         |  thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
| thread_concurrency                      | 16                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
+-----------------------------------------+---------------------------+

 

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |   최대 동시 접속 가능 수
+-----------------+-------+

 

 

Cache Miss Rate(%) =  Threads_created / Connections * 100
 mysql> show status like '%thread%';
 mysql> show status like '%connect%';

 1.08% = 753 / 69671 * 100
 1.1% = 11 / 962 * 100

Connection Miss Rate(%) = Aborted_connects / Connections * 100
 mysql> show status like '%connect%';

 0.02% = 14 / 69671 * 100


Connection Usage(%) = Threads_connected / max_connections * 100
 mysql> show status like '%thread%';
 mysql> show variables like '%connect%';
 
 7.28% = 11 / 151 * 100
 2% = 4 / 200 * 100

 

 

. 튜닝

Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.

DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다.

그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.

Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.

MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다.

skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

 

 

. Memory 튜닝

mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_assign_to_keycache | 0       |
| Com_preload_keys       | 0       |
| Com_show_keys          | 0       |
| Handler_read_key       | 0       |
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 307043  | Key Cache에서 사용되고 있지 않은 Block 수
| Key_blocks_used        | 12864   |
| Key_read_requests      | 1305206 | Key Block 읽기 요청수
| Key_reads              | 198507  | Key Block 읽기 요청시 Disk을 읽은 수
| Key_write_requests     | 55      |
| Key_writes             | 55      |
+------------------------+---------+

1. status

key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
key_reads - Key Block 읽기 요청시 Disk을 읽은 수
key_read_requests - Key Block 읽기 요청수

 


mysql> show variables like '%key%';     
+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| delay_key_write          | ON                   |
| foreign_key_checks       | ON                   |
| have_rtree_keys          | YES                  |
| key_buffer_size          | 402653184            | 인덱스를 메모리에 저장하는 버퍼의 크기
| key_cache_age_threshold  | 300                  |
| key_cache_block_size     | 1024                 |
| key_cache_division_limit | 100                  |
| max_seeks_for_key        | 18446744073709551615 |
| ssl_key                  |                      |
+--------------------------+----------------------+


2. system variables

key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
join_buffer_size - 조인을 위한 메모리 버퍼 크기
record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
key_cache_block_size - block 의 크기(bytes, 기본값 1024)

 

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
 mysql> show status like '%key%';
 mysql> show variables like '%key%';
  
 0.211 = 1 - ((307095 * 1024) / 402653184)
 0.189 = 1 - ((850422 * 1024) /  1073741824)

 

Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
 mysql> show status like '%key%';

  15% = 207265/1374958 * 100
  17% = 9709 / 54200 * 100
  2699 / 12898 * 100
  

 

Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100
* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

 = 1 - 8455/

 


3. 튜닝

key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.

Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다.

1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다.

또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다.

하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.

 


.적용

[www@smson www]$ vi /etc/my.cnf 
# The MySQL server

[mysqld]
skip-name-resolve
key_buffer_size = 1024M

max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M

wait_timeout = 120


참고:  mysql 튜닝 툴
wget http://mysqltuner.com/mysqltuner.pl
chmod +x ./mysqltuner.pl
./mysqltuner.pl

Trackbacks 0 : Comments 0