mysql의 SSD 디스크 파티션의 최적 튜닝가이드 따라하기
참조1)
가이드에서 최고의 perfomance라고 하는 디스크 파티션 옵션과 mysqldb page size를 수정해서
최적의 성능을 찾아본다.
참조2) https://blog.naver.com/theswice
THESWICE : 네이버 블로그
DBMS Blog
blog.naver.com
#Mount options
◦ext4 (rw,noatime, nodiration, nobarrier, data=ordered)
◦xfs (rw, noatime, nodiratime, nobarrier, logbufs=8, logbsize=32k)
◦SSD specific ◾trim
◾innodb_page_size = 4k
◾innodb_flush_neighbors = 0
#SSD --> innodb_flush_method = O_DIRECT 란?
I/O 처리량에 영향을 줄 수 있는 InnoDB 데이터 파일 및 로그 파일로 데이터를 플러시하는데 사용되는 방법을 정의합니다.
#innodb_flush_method 옵션 종류
fsync : InnoDB는 fsync() 시스템 호출을사용하여 데이터와 로그 파일을 플러시합니다. fsync가 기본 설정입니다.
O_DSYNC : InnoDB는 O_SYNC를 사용하여 로그 파일을 열고 플러시하고 fsync()를 사용하여 데이터 파일을플러시합니다. InnoDB는 O_DSYNC를 직접 사용하지 않습니다. 왜냐하면 많은 종류의 유닉스에서 문제가 발생했기때문입니다.
O_DIRECT : InnoDB는 O_DIRECT (또는 Solaris의 경우 directio())를 사용하여 데이터 파일을 열고 fsync()를 사용하여 데이터와 로그 파일을 모두 플러시합니다.
O_DIRECT_NO_FSYNC : InnoDB는 I/O를 플러시하는 동안 O_DIRECT를 사용하지만 나중에 fsync() 시스템호출을 건너 뜁니다. 이 설정은 일부 유형의 파일 시스템에는 적합하지만 다른 유형에는 적합하지 않습니다.
예를 들어 XFS에는 적합하지 않습니다. 사용하는 파일 시스템에 fsync()가 필요한지 여부가 확실하지 않은 경우 (예 : 모든 파일 메타 데이터를 유지하는 경우) 대신 O_DIRECT를 사용하십시오.
# 현재 페이지 사이즈 확인
mysql> show global variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
MySQL 인스턴스에서 모든 InnoDB 테이블 스페이스의 페이지 크기를 지정한다. 64k, 32k, 16k (기본값), 8k 또는 4k 값을 사용하여 페이지 크기를 지정할 수 있습니다.
또는 페이지 크기 (바이트) (65536, 32768, 16384, 8192, 4096)를 지정할 수 있습니다.
innodb_page_size는 MySQL 인스턴스를 초기화하기 전에만 구성 할 수 있으며 나중에 변경할 수 없습니다.
값을 지정하지 않으면 인스턴스는 기본 페이지 크기를 사용하여 초기화됩니다.
MySQL 5.7에서는 32k 및 64k 페이지 크기에 대한 지원이 추가되었습니다. 32k 및 64k 페이지 크기의 경우 최대 행 길이는 약 16000 바이트입니다.
innodb_page_size가 32KB 또는 64KB로 설정된 경우 ROW_FORMAT = COMPRESSED는 지원되지 않습니다. innodb_page_size = 32k의 경우, Extent 크기는 2MB입니다.
innodb_page_size = 64k의 경우, Extent 크기는 4MB입니다. innodb_log_buffer_size는 32k 또는 64k 페이지 크기를 사용할 때 최소 16M (기본값)으로 설정해야 합니다.
기본 16KB 페이지 크기 이상은 광범위한 워크로드, 특히 대량 스캔을 포함하는 테이블 스캔 및 DML 조작과 관련된 조회에 적합합니다.
단일 페이지에 많은 행이 포함될 때 경합이 문제가 될 수 있는 많은 작은 쓰기를 포함하는 OLTP 작업 부하에 대해 더 작은 페이지 크기가 더 효율적일 수 있습니다.
더 작은 페이지는 일반적으로 작은 블록 크기를 사용하는 SSD 저장 장치에서도 효율적일 수 있습니다.
InnoDB 페이지 크기를 저장 장치 블록 크기에 가깝게 유지하면 디스크에 다시 쓰여지는 변경되지 않은 데이터의 양이 최소화됩니다.
첫 번째 시스템 테이블 공간 데이터 파일 (ibdata1)의 최소 파일 크기는 innodb_page_size 값에 따라 다릅니다.
# innodb_flush_neighbors 옵션 설명
buffer pool에 있는 내용을 flush 할때 같은 extent에 있는 다른 dirty page 들까지도 같이 flush 할지 설정할 수 있다.
팁 : HDD를 사용하는 경우에 인접하는 dirty 까지 모두 flush 하면 IO부하를 줄일 수 있다. 반면 SSD를 사용할 경우에는 seektime 이 더이상 중요한 팩트가 아니므로 설정을 꺼놓는 것이 좋다
SSD를 사용 중이면 플러시는 중복 조작입니다 (SDD는 순차적이지 않습니다).
mysql> set global innodb_flush_neighbors = 0;
1) SSD 파티션 생성
SSD 마운트 옵션 : xfs / rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k
mysql cnf 튜닝옵션 : innodb_page_size = 4k (4096)
mysql vers : 5.7.19
OS : CentOS 6.10이고 xfs 지원범위를 확인하고 SSD 파티션 생성한다.
[root@localhost ~]# mkfs.xfs -f /dev/sdb1
-bash: mkfs.xfs: command not found
CentOS xfs 지원범위
1) kernel-2.6.18-194.el5.x86_64 이상의
2) 최대크기 100TB 까지 지원
3) OS 설치 후 생성가능
4) root와 boot 파티션에는 지원불가
yum install kmod-xfs xfsdump xfsprogs dmapi
[root@localhost ~]# yum install kmod-xfs xfsdump xfsprogs dmapi
Loaded plugins: fastestmirror, security
Setting up Install Process
base | 3.7 kB 00:00
base/primary_db | 4.7 MB 00:37
extras | 3.4 kB 00:00
extras/primary_db | 29 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 8.9 MB 00:02
No package kmod-xfs available.
No package dmapi available.
Resolving Dependencies
--> Running transaction check
---> Package xfsdump.x86_64 0:3.0.4-4.el6_6.1 will be installed
---> Package xfsprogs.x86_64 0:3.1.1-20.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=====================================================================================================================================================
Package Arch Version Repository Size
=====================================================================================================================================================
Installing:
xfsdump x86_64 3.0.4-4.el6_6.1 base 252 k
xfsprogs x86_64 3.1.1-20.el6 base 725 k
Transaction Summary
=====================================================================================================================================================
Install 2 Package(s)
Total download size: 977 k
Installed size: 3.9 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): xfsdump-3.0.4-4.el6_6.1.x86_64.rpm | 252 kB 00:01
(2/2): xfsprogs-3.1.1-20.el6.x86_64.rpm | 725 kB 00:05
-----------------------------------------------------------------------------------------------------------------------------------------------------
Total 128 kB/s | 977 kB 00:07
경고: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>
Package: centos-release-6-10.el6.centos.12.3.x86_64 (@anaconda-CentOS-201806291108.x86_64/6.10)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : xfsprogs-3.1.1-20.el6.x86_64 1/2
Installing : xfsdump-3.0.4-4.el6_6.1.x86_64 2/2
Verifying : xfsprogs-3.1.1-20.el6.x86_64 1/2
Verifying : xfsdump-3.0.4-4.el6_6.1.x86_64 2/2
Installed:
xfsdump.x86_64 0:3.0.4-4.el6_6.1 xfsprogs.x86_64 0:3.1.1-20.el6
Complete!
[root@localhost ~]# mkfs.xfs -f /dev/sdb1
meta-data=/dev/sdb1 isize=256 agcount=4, agsize=5242711 blks
= sectsz=512 attr=2, projid32bit=0
data = bsize=4096 blocks=20970841, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0
log =internal log bsize=4096 blocks=10239, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@localhost ~]# fdisk -l
Disk /dev/sda: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000834d3
Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 1370 10485760 83 Linux
/dev/sda3 1370 2415 8392704 82 Linux swap / Solaris
/dev/sda4 2415 13055 85466112 5 Extended
/dev/sda5 2415 2937 4194304 83 Linux
/dev/sda6 2937 3459 4194304 83 Linux
/dev/sda7 3459 3590 1048576 83 Linux
/dev/sda8 3590 3721 1048576 83 Linux
/dev/sda9 3721 13055 74975232 83 Linux
Disk /dev/sdb: 85.9 GB, 85899345920 bytes
255 heads, 63 sectors/track, 10443 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x6f525b46
Device Boot Start End Blocks Id System
/dev/sdb1 1 10443 83883366 83 Linux
[root@localhost /]# mount /dev/sdb1 /svc2
[root@localhost /]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/sda2 ext4 9.8G 310M 9.0G 4% /
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/sda1 ext4 477M 39M 413M 9% /boot
/dev/sda7 ext4 976M 1.3M 924M 1% /home
/dev/sda9 ext4 71G 52M 67G 1% /svc
/dev/sda8 ext4 976M 1.3M 924M 1% /tmp
/dev/sda5 ext4 3.9G 1.6G 2.2G 42% /usr
/dev/sda6 ext4 3.9G 139M 3.5G 4% /var
/dev/sdb1 xfs 80G 33M 80G 1% /svc2
// /etc/fstab에 등록
//xfs rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k2) my.cnf 페이지 사이즈 수정
default는 16k이다.
innodb_page_size = 4096 수정 후 재시작
mysql> show global variables like '%page%';
+--------------------------------------+-----------+
| Variable_name | Value |
+--------------------------------------+-----------+
| innodb_log_compressed_pages | ON |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_page_cleaners | 4 |
| innodb_page_size | 16384 |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| large_page_size | 0 |
| large_pages | OFF |
+--------------------------------------+-----------+
10 rows in set (0.00 sec)DB가 안올라온다...;;;
[mysql@localhost error]$ service mysqld start
Starting MySQL...The server quit without updating PID file [실패]/mysqldb_data/localhost.localdomain.pid).
error log)
2020-03-26T10:12:13.476719+09:00 0 [ERROR] InnoDB: Data file './ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 4096
2020-03-26T10:12:13.476792+09:00 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2020-03-26T10:12:13.476813+09:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-03-26T10:12:14.082225+09:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-03-26T10:12:14.082252+09:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-03-26T10:12:14.082257+09:00 0 [ERROR] Failed to initialize plugins.
2020-03-26T10:12:14.082260+09:00 0 [ERROR] Aborting
//에러로그를 확인하여 로그버퍼관련 파일 백업하고 재시작한다.
[mysql@localhost mysqldb_data]$ cd backup/
[mysql@localhost backup]$ ls
db.opt ib_buffer_pool ib_logfile0 ib_logfile1 ib_logfile2 ibdata1 ibdata2 ibdata3 ibdata4 ibtmp1
[mysql@localhost mysqldb_data]$ service mysqld start
Starting MySQL........................... [ OK ]
//뜨긴하는데 이런 에러가 난다..
2020-03-26T12:01:55.070009+09:00 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2020-03-26T12:01:55.070315+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070397+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070451+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070460+09:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2020-03-26T12:01:55.070521+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070575+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070583+09:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
// 접속해서 스키마별 테이블을 확인해보니 테이블에 데이터 매핑이 안된다. 제대로 열리는 테이블이 없다.;;;3) 구글링
innodb_page_size는 dynamic으로 변경할 수 없고 재시작하더라도 tablespace를 비우고 다시 데이터를 import (mysqldump) 해야한다는 것을 알게되었다. 어찌 생각해보면 당연했다. 16k -> 4k로 줄이는데 기존 데이터와 index가 문제가 될 것 같긴 했다;
You must, however, set the page size before the InnoDB tablespace is initialized.
All tablespaces (including per-table tablespaces, general tablespaces, undo tablespaces, temp tablespaces, etc.) must use the same page size.
You set the page size to 8KB by putting this line in your /etc/my.cnf file, in the [mysqld] section:
innodb_page_size=8K
You need to do this before the InnoDB tablespaces are initialized. If you want to change the page size later:
If you want to create a MySQL Instance with a new size for innodb_page_size
1.you must setup a new datadir with no data (but do not start mysqld)
2.set the innodb_page_size to 32768 in the my.ini for that new instance
3.start mysqld against that new my.ini and new datadir
4.mysqldump the data out of the old instance
5.load the mysqldump into the new instance
Give it a try and let us know !!!
//기존 16k 페이지인 DB에서 mysqldump로 전체 백업
[mysql@localhost svc]$ mysqldump -uroot -p@12345678 --routines --events --all-databases > local_ALL_DB.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost svc]$ mysql -uroot -p < local_ALL_DB.sql
Enter password:
ERROR 1071 (42000) at line 27529: Specified key was too long; max key length is 768 bytes
error log)
[Warning] InnoDB: Cannot add field `xxx` in table `SUB`.`TALBE1` because after adding it, the row size is 2017 which is greater than maximum allowed size (1982) for a record on index leaf page.
[Warning] InnoDB: Cannot add field `xxxx` in table `SUB`.`TALBE2` because after adding it, the row size is 2008 which is greater than maximum allowed size (1982) for a record on index leaf page.
[Warning] InnoDB: Cannot add field `xxxxx` in table `SUB`.`TALBE3` because after adding it, the row size is 1998 which is greater than maximum allowed size (1982) for a record on index leaf page.
안된다. 이유는 기존 16k 에서 생성한 varchar의 데이터가 innodb_large_prefix = on 으로 cnf가 설정되어있으면
767 byte에서 3072byte까지 늘어나게 되는데 이미 테이블에 생성된 데이터는 3072 byte 까지 컬럼에 데이터를 쌓게 되어있는 것을 강제로 4k로 변경하다보니 too long size 에러가 나는 것이었다.
4) 실패
기존 데이터로 4k 테스트를 해보고 싶었지만 쌓인 데이터를 에러때마다 건건이 변경하지 않는한 restore가 불가능했다.
innodb_page_size는 한번 설정하면 변경하기 아주 어렵다는 것을 확인했다는 것에 만족하고 테스트 종료한다.
5) innodb_page_size 테스트관련 참조사이트
https://mariadb.com/resources/blog/does-innodb-page-size-matter/
InnoDB Page Size: Benchmarking Performance | MariaDB
Does InnoDB page size matter? We benchmark performance and look at response times to see if it does. View our results (with graphs).
mariadb.com
그래프를 다시 보니 이게 16k 32k 64k 테스트였다.. 스레드개수를 4k라고 착각했다니;;
(xfs /4k는 데이터 마이그레이션이 필요해서 날잡아서 나중에 다시한다.)
6) 결론
SAS를 SSD로 바꾸면서 최적의 튜닝옵션을 찾기위한 테스트였다.
일단 SSD의 성능이 우수한 것은 확인했고, 파일시스템 XFS를 제대로 테스트 못한 것은 아쉽다.
다만 현재 varchar를 3072byte까지 확장하여 데이터를 쌓은 DB에서 XFS/4K는 현실적으로 쓰기 어렵고 4k로 테스트한 것은 아니지만 XFS 자체가 그렇게 높은 성능을 보이는 것도 아니었다. 아래는 성능 테스트 결과값이다.
6-1) SAS VS SSD
SSD 압승이다. SATA2에서 10분이상 걸리던 것이 Ext4나 XFS나 6분정도로 수행되는 것으로 보면
디스크만 바꿔도(SAS -> SSD) 40%이상 개선효과가 있는 것을 확인했다.
6-2) Ext4 VS XFS default page size =16k Test!!
아래는 파일시스템 Ext4와 XFS를 테스트한 값이다.
테스트 query는 기존에 cost를 많이 써서 개선이 필요한 쿼리로 테스트했다. 많은 function과 많은 join이 있는..
| 횟수 | Ext4 / page size 16k | XFS / page size 16k |
| 1 | 5분 4초 | 6분 43초 |
| 2 | 5분 3초 | 5분 43초 |
| 3 | 5분 10초 | 5분 59초 |
| 4 | 5분 7초 | 5분 48초 |
| 5 | 5분 59초 | 5분 39초 |
| 6 | 5분 3초 | 5분 39초 |
| 7 | 5분 8초 | 6분 50초 |
| 8 | 5분 5초 | 6분 39초 |
| 9 | 5분 14초 | 5분 43초 |
| 10 | 5분 10초 | 5분 39초 |
결과적으로 보면 mysql에서 page size가 16k (default)라면 굳이 XFS 쓸 필요 없을 거 같다.
'RDB > mysql' 카테고리의 다른 글
| [error] mysqldump: Got errno 28 on write (0) | 2020.04.02 |
|---|---|
| SSD 파티션 튜닝 2 (0) | 2020.03.31 |
| [에러] mysql DB disk 폴더 이동후 재시작 (0) | 2020.03.26 |
| mysqldump Table wildcard % 백업하기 (0) | 2020.03.16 |
| [error] This function has none of DETERMINISTIC, NO SQL .. (0) | 2020.03.04 |
dung beetle
취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기
