2) mysql 8에서 달라지는 점 data dictionary를 사용하면서 기존에 파일로 있었던 .frm / .par / .trn / .trg 등의 파일이 사라진다. mysql 데이터베이스의 데이터들은 mysql.ibd 테이블 스페이스에 저장이 된다고 합니다.
- mysql 8에서는 데이터사전이란 개념이 들어가면서 mysql.ibd로 통합되고 .trg는 더이상 사용하지 않는다고 한다.
mysql 데이터베이스의 테이블들을 show create table 명령으로 확인해 보면 아래와 같습니다. /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 더이상 MyISAM 엔진을 사용하지 않습니다.
InnoDB 엔진 이외의 엔진들에 대한 metadata 저장은 Serialized Dictionary Information (SDI) 파일에 JSON으로 저장합니다. 기본 설치후에 mysql 데이터베이스 디렉토리에 general_log와 slow_log 테이블의 .sdi 파일을 확인할 수 있습니다.
# ls -lh
합계 738G
drwxr-x--- 2 mysql mysql 12K 2020-10-13 13:43 xxDB
drwxr-x--- 2 mysql mysql 20K 2020-02-28 17:35 xxxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-22 18:49 xxxx
drwxr-x--- 2 mysql mysql 20K 2021-02-16 14:08 xxx
drwxr-x--- 2 mysql mysql 12K 2021-02-16 15:05 zzDB
drwxr-x--- 2 mysql mysql 4.0K 2021-02-02 18:56 ttDB
drwxr-x--- 2 mysql mysql 40K 2021-02-23 10:07 xxxxDB
-rw-r----- 1 mysql mysql 56 2020-02-28 17:45 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 backup
-rw-r----- 1 mysql mysql 36K 2021-02-10 15:22 ddl_log.log
-rw-r----- 1 mysql mysql 558K 2020-11-23 11:23 ib_buffer_pool
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:10 ib_logfile0
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:10 ib_logfile1
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:07 ib_logfile2
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:10 ibdata1
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:10 ibdata2
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:08 ibdata3
-rw-r----- 1 mysql mysql 5.9G 2021-02-23 10:10 ibdata4
-rw-r----- 1 mysql mysql 730G 2021-02-23 10:06 ibtmp1
-rw-r----- 1 mysql mysql 2.8K 2020-02-28 17:43 mvno-my.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:35 mysql
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 performance_schema
-rw-r----- 1 mysql mysql 6 2020-11-23 11:23 privacy-db.pid
drwxr-x--- 2 mysql mysql 12K 2020-02-28 17:43 sys
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:43 test
-rw-r----- 1 mysql mysql 22 2020-02-28 17:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 578 2020-02-28 17:43 xtrabackup_info
-rw-r----- 1 mysql mysql 730G 2021-02-23 10:06 ibtmp1
//헐 이게 뭐냐?? ibtmp1에 대해 알아봐야겠다.
* ibtmp1 이란?
[MySQL Internals] Temporary Tablespace 압축되지 않고 사용자가 생성한 임시 테이블과 디스크에 생성되는 내부적인 임시 테이블들이 shared temporary tablespace 에 생성됩니다. innodb_temp_data_file_path 옵션으로 상대 경로, 이름, 사이즈, 데이터파일의 속성을 설정할 수 있습니다. 아무것도 설정하지 않으면 기본적으로 innodb_data_home_dir 경로에 ibtmp1:12M:authextend 속성으로 생성됩니다.
[Note] MySQL 5.6에서는 압축되지 않은 테이블에 대한 임시 테이블 스페이스는 개별 file-per-table 테이블 스페이스에 생성되었었습니다. 또는 innodb_file_per_table 설정이 안되어 있다면 시스템 테이블 스페이스에 생성됩니다. 5.7의 Temporary Tablespace 는 기존 개별 file-per-table 테이블 스페이스를 생성하고 삭제할 필요가 없기 때문에 성능 이점을 가집니다. 또한 전용 Temporary Tablespace가 있기 때문에 temp table 에 대한 metadata 를 시스템 테이블에 생성할 필요가 없어집니다.
[Mysql 5.7에서 임시테이블에 대한 성능개선]
Mysql ver 5.7.2에서 일반 임시 테이블과 압축 임시테이블 그리고 거기에 연관된 오브젝트들을 위한 새로운 타입의 Undo Log가 소개되었다. 임시 테이블의 내용은 Crash Recovery에서 사용되지 않기 때문에 redo log가 필요하지 않다. 즉, 임시테이블의 정보는 서버가 운영 중일때, 롤백해야 하는 상황에서만 필요하다. 리두로그를 만들지 않는 Undo Log는 해당 임시테이블과 거기에 관련된 오브젝트를 위한 redo logging으로 인해 발생하는 Disk I/O 를 피할수 있기 때문에 성능에 도움을 준다. 임시테이블에 대한 Undo log는 임시테이블 스페이스에 위치한다. 기본으로 생성되는 임시테이블 스페이스 파일은 ibtmp1이라는 이름을 가진다. 이것은 따로 지정하지 않으면 Data Directory에 위치하게 되고, 이것은 Mysql이 Startup 될 때 자동으로 재성생된다. 사용자의 요구에 따라 위치를 변경할 수 있는데 이때 사용하는 시스템 변수는 innodb_temp_data_file_path이다.
--> 아~ 임시파일을 저장하는 파일이군..
redo log를 쓰지않으니 Disk I/O 이슈는 없었던 것이고, DB를 재시작하게 되면 삭제하고 다시 생성하는 것으로 확인했다.
쿼리가 수행 도중에 MAX 에 도달하게 되면 table is full 에러를 내면서 쿼리는 실패됩니다. --> 잠깐만요..; 뭐라고요? 하지만 무제한으로 tablespace 를 제공할 수는 없기 때문에 적절한 사이즈를 정해야 합니다.
--> DB는 분기별로 정기 PM이 있어서 굳이 넣을 필요가 있을까 싶다.. 그냥 재시작한다.
* DB shutdown 후 파일확인
# ls -lh
합계 8.1G
drwxr-x--- 2 mysql mysql 12K 2020-10-13 13:43 xxDB
drwxr-x--- 2 mysql mysql 20K 2020-02-28 17:35 xxxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-22 18:49 zzz
drwxr-x--- 2 mysql mysql 20K 2021-02-23 18:35 xxDB
drwxr-x--- 2 mysql mysql 12K 2021-02-16 15:05 xxDB
drwxr-x--- 2 mysql mysql 4.0K 2021-02-02 18:56 xxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-23 12:53 xxxxDB
-rw-r----- 1 mysql mysql 56 2020-02-28 17:45 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 backup
-rw-r----- 1 mysql mysql 36K 2021-02-10 15:22 ddl_log.log
-rw-r----- 1 mysql mysql 2.7M 2021-02-24 08:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 256M 2021-02-24 08:21 ib_logfile0
-rw-r----- 1 mysql mysql 256M 2021-02-24 03:05 ib_logfile1
-rw-r----- 1 mysql mysql 256M 2021-02-24 08:21 ib_logfile2
-rw-r----- 1 mysql mysql 500M 2021-02-24 08:21 ibdata1
-rw-r----- 1 mysql mysql 500M 2021-02-24 08:21 ibdata2
-rw-r----- 1 mysql mysql 500M 2021-02-24 03:13 ibdata3
-rw-r----- 1 mysql mysql 5.9G 2021-02-24 08:21 ibdata4
-rw-r----- 1 mysql mysql 2.8K 2020-02-28 17:43 mvno-my.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:35 mysql
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 performance_schema
drwxr-x--- 2 mysql mysql 12K 2020-02-28 17:43 sys
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:43 test
-rw-r----- 1 mysql mysql 22 2020-02-28 17:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 578 2020-02-28 17:43 xtrabackup_info
//DB shutdown 후 확인해보니 파일이 사라졌다. DB를 내릴때 같이 삭제하는 로직인 것을 알수 있다.
DB를 다시 startup 하고 ls -lh로 파일을 확인해보니 ibtmp1 파일이 12M 로 생성된 것을 확인할 수 있었다.
# Adding multiple columns AFTER a specific column in MySQL (mysql 5.7.19)
after 를 사용하게되면 algorithm = copy로 처리하게되서 테이블의 용량에 따라 컬럼 추가시에는 성능이슈가 발생할 수도 있다.
예를 들어 테이블 TMP_LOAD_20201203에 x0 컬럼 뒤에 컬럼 5개를 추가한다고하면
#잘못된 예)
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x1 VARCHAR(8) DEFAULT NULL AFTER x0;
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x2 VARCHAR(1) DEFAULT NULL AFTER x1;
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x3 VARCHAR(1) DEFAULT NULL AFTER x2;
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x4 VARCHAR(1) DEFAULT NULL AFTER x3;
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x5 VARCHAR(1) DEFAULT NULL AFTER x4;
// 컬럼 하나 추가할때마다 임시테이블을 만드는 작업을 하고 있어 느리다.
#수정 예)
ALTER TABLE TESTDB.TMP_LOAD_20201203
ADD COLUMN x1 VARCHAR(8) DEFAULT NULL AFTER x0,
ADD COLUMN x2 VARCHAR(1) DEFAULT NULL AFTER x1,
ADD COLUMN x3 VARCHAR(1) DEFAULT NULL AFTER x2,
ADD COLUMN x4 VARCHAR(1) DEFAULT NULL AFTER x3,
ADD COLUMN x5 VARCHAR(1) DEFAULT NULL AFTER x4;
// 임시테이블 한번만 만들고 나머지는 컬럼모두 추가~ 아래꺼가 훨씬 빠르다.
after column 을 쓰게되면 algorithm = inplace 를 사용할 수 없다. mysql 8에서도 맨뒤에 컬럼을 추가하는 경우를
Please watch Percona’s Chief Evangelist, Colin Charles as he presents as he presents MariaDB 10.3 vs. MySQL 8.0. - Percona의 수석 에반젤리스트인 Colin Charles가 MariaDB 10.3 대 MySQL 8.0을 제시하면서 발표하는 모습을 지켜봐 달라.
Are they syntactically similar? Where do these two languages differ? Why would I use one over the other? - 그들은 구문론적으로 비슷한가? 이 두 언어는 어디에서 다른가? 왜 내가 하나를 다른 하나보다 더 쓰겠어?
MariaDB 10.3 is on the path of gradually diverging from MySQL 8.0. One obvious example is the internal data dictionary currently under development for MySQL 8.0. This is a major change to the way metadata is stored and used within the server, and MariaDB doesn’t have an equivalent feature. Implementing this feature could mark the end of datafile-level compatibility between MySQL and MariaDB. - MariaDB 10.3은 MySQL 8.0에서 점차 멀어지는 경로에 있다. 한 가지 분명한 예가 현재 MySQL 8.0에 대해 개발 중인 내부 데이터 사전이다. 이는 서버 내에서 메타데이터를 저장하고 사용하는 방식을 크게 변화시킨 것으로 마리아DB에는 동등한 기능이 없다. 이 기능을 구현하면 MySQL과 MariaDB 간의 데이터 파일 레벨 호환성이 종료될 수 있다.
ex) 예를들면 MariaDB 를 mysql 로 이관한다면 예전에는 스키마 통째 mysqldump 로 이관이 가능했지만
이제는 불가능하고 테이블의 데이터만 이관가능하는 얘기가 된다.
There are also non-technical differences between MySQL 8.0 and MariaDB 10.3, including: - MySQL 8.0과 MariaDB 10.3 사이에는 다음과 같은 비기술적인 차이점도 있다.
Licensing: MySQL offers their code as open-source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise. MariaDB can only use the GPL, because their work is derived from the MySQL source code under the terms of that license. - 라이센스: MySQL은 그들의 코드를 GPL에 따라 오픈 소스로 제공하고, MySQL Enterprise의 형태로 비 GPL 상업 배포 옵션을 제공한다. MariaDB는 GPL만 사용할 수 있다. GPL의 작업은 해당 라이센스의 조건에 따른 MySQL 소스 코드에서 파생되기 때문이다.
Support services: Oracle provides technical support, training, certification and consulting for MySQL, while MariaDB has their own support services. Some people will prefer working with smaller companies, as traditionally it affords them more leverage as a customer. - 지원 서비스: Oracle은 MySQL에 대한 기술 지원, 교육, 인증 및 컨설팅을 제공하며, MariaDB는 자체 지원 서비스를 보유하고 있다. 전통적으로 고객으로서 더 많은 지렛대를 제공하기 때문에 일부 사람들은 중소기업과 일하는 것을 선호할 것이다.
Community contributions: MariaDB touts the fact that they accept more community contributions than Oracle. Part of the reason for this disparity is that developers like to contribute features, bug fixes and other code without a lot of paperwork overhead (and they complain about the Oracle Contributor Agreement). However, MariaDB has its own MariaDB Contributor Agreement -- which more or less serves the same purpose. - 지역사회 공헌: 마리아DB는 그들이 오라클보다 지역사회 공헌을 더 많이 받아들인다는 사실을 강조한다. 이러한 불균형의 이유 중 일부는 개발자들이 많은 문서 업무 부담 없이 기능, 버그 수정 및 기타 코드를 제공하기를 좋아하기 때문이다(그리고 그들은 Oracle 기부자 계약에 대해 불평한다). 그러나 마리아DB는 마리아DB 기부자 협정이 있는데, 이것은 거의 같은 목적을 위해 사용되고 있다.
Colin will take a look at some of the differences between MariaDB 10.3 and MySQL 8.0 and help answer some of the common questions our Database Performance Experts get about the two databases. - Colin은 MariaDB 10.3과 MySQL 8.0의 차이점을 살펴보고, 두 데이터베이스에 대해 데이터베이스 성능 전문가들이 얻는 일반적인 질문에 답하는 데 도움을 줄 것이다.
테이블 반영 스키마 검수 중에 longtext 형으로 선언한 컬럼이 있어 확인해보니 약 4GB 까지나 되는 문자를 집어넣을 수 있게 되어있다.
컬럼 타입이 너무 큰데.. MEDIUMTEXT로 줄여야 하지 않나?..
이걸 써도 성능에 이슈가 없는지 확인해 보았다.
TINYTEXT
256 bytes
TEXT
65,535 bytes~64kb
MEDIUMTEXT
16,777,215 bytes~16MB
LONGTEXT
4,294,967,295 bytes~4GB
TEXT : 최대 65,535 개의 문자의 저장이 가능한 가변 길이 문자형. 필드 설정시 최대 크기를 지정하게 되어 있지 않음. 예를 들면 address text( 520 ) NOT NULL 가 아니라
address text NOT NULL로 해야오류 나지 않음 설계자가 520 byte의 문장을 입력하였다면 실제 저장 길이는 520+2 byte가 됨
MEDIUMTEXT, LONGTEXT도 최대 저장 가능 크기 외에는 성격이 같음
BLOB : BLOB는 데이타를 이진 데이타로 취급하는 것을 제외하고는 TEXT 자료형과 성격이 같다.
MEDIUMTEXT : 최대 16,777,215 개의 문자 저장 가능한 가변 길이 문자형, MEDIUMBLOB : MEDIUMBLOB는 데이타를 이진 데이타로 취급하는 것을 제외하고는
MEDIUMTEXT 자료형과 성격이 같다.
LONGTEXT : 최대 4,294,967,295 개의 문자 저장 가능한 가변 길이 문자형,
- 가변길이다. 별이슈 없을 거 같긴한데.. 그래도 좀 더 확인해본다. LONGBLOB : LONGBLOB는 데이타를 이진 데이타로 취급하는 것을 제외하고는 LONGTEXT 자료형과 성격이 같다.
MySQL: Is there a lack of performance by using LONGTEXT instead of MEDIUMTEXT?
The only difference is the length field in the row data. Using MEDIUMTEXT instead of LONGTEXT saves 1 byte per record. If you have 100 million records, that saves 100 MB. There was a time when that was a significant amount of disk space. 유일한 차이점은 행 데이터의 길이 필드다. LONGTEXT 대신 MEDITEXT를 사용하면 레코드당 1바이트가 저장된다. 1억 개의 레코드를 가지고 있다면, 100MB를 절약할 수 있다. 그것이 상당한 양의 디스크 공간이었던 시절이 있었다.
The difference could also be significant if you're running up against the size limit for database rows. The text of *TEXT data is stored in files external to the table data, so it doesn't count against the limit, but I believe the size field is in the table so it does count. 데이터베이스 행의 크기 제한에 따라 차이가 클 수도 있다. *TEXT data의 텍스트는 테이블 데이터 외부의 파일에 저장되기 때문에 한계에 반하는 것은 아니지만, 나는 테이블 안에 크기 필드가 있기 때문에 셀 수 있다고 생각한다.
But if neither of these is an issue, go ahead and use the largest type, to future-proof your 하지만 이 두 가지가 모두 문제가 되지 않는다면, 미래에 대비할 수 있도록 가장 큰 유형을 사용하십시오.
2. 결론
longtext 형 사용해도 된다.
다만 text형을 선언한다는 것 자체가 varchar(4000) 이상(default page size = 16K 일경우)의 문자를 사용하겠다는 의미라서 컬럼이 무지 크다는 것은 항상 생각하고 있어야 한다.
-> default page size 에 따라서 varchar 선언할 수 있는 사이즈가 달라진다. DB를 구축할 때부터 검토해야하며
한번 선언한 size는 바꾸기 매우 어렵다. ssd 파티션 튜닝할 때 4K로 해보려다 고생한 기억이 있다;
varchar형에서 사용했던 like 검색을 대체할 인덱스(fulltext, ngram parser, 스핑크스, 엘라스틱서치..등등) 선언은 반드시 필요하고,
서브쿼리로 longtext가 있는 테이블을 사용을 지양해야하며,
JOIN을 쓴다면 인덱스 스캔여부를 확인하고, longtext 형 컬럼을 굳이 쓰지 않아도 된다면
left(컬럼,10) 등으로 잘라서 가져오는 것이 DB에 부하를 줄일 수 있다는 것까지 확인하였다.
mysql에 thread_cache_size라는 것이 있다. 이것의 역할은 connection당 스레드를 생성하고 해제할 때 메모리를 할당하고 캐시할 메모리(192K or 256K)를 미리 생성해놓고 적절하게 관리하는 용도이다. 일종의 connection pool기능을 하는 역할인데 현재 서비스 DB를 확인해보니 cache miss rate이 17% 정도된다.
mysql> show global variables like 'thread%';
+-------------------+---------------------------+
| Variable_name | Value |
+-------------------+---------------------------+
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
+-------------------+---------------------------+
mysql> show status like 'thread_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 106 |
| Threads_created | 62769 |
| Threads_running | 3 |
+-------------------+-------+
mysql> show status like 'connections%';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Connections | 357651 |
+---------------+--------+
1 row in set (0.00 sec)
//cache miss rate(%) = (Threads_created / connections) * 100 --> (62769/357651) * 100 = 17.5%
스레드를 수정하는 것이라서 바로 반영하기엔 부담이 많이 되었고 miss rate 17%가 이게 좋은 것인지 나쁜것이지 판단이 안되었다. 여러가지로 자료조사를 해보니 국내 사이트에서는 튜닝한 사례를 소개한 글은 찾기 어려웠고.. 시간이 좀 걸리긴 했지만 percona site와 High Performance MySQL 책에서 어느정도 답을 찾을 수 있었다.
->따라서 1차적으로 thread_cache_size를 default에서 16으로 바꾸는 게 좋겠다고 판단했다.
2) High Performance MySQL 내용 #thread_cache_size Setting this variable has no immediate effect—the effect is delayed until the next time a connection is closed. At that time, MySQL checks whether there is space in the cache to store the thread. If so, it caches the Thread for future reuse by another connection. If not, it kills the Thread instead of caching it. In this case, the number of threads in the cache, and hence the amount of memory the thread cache uses, does not immediately decrease; it decreases only when a new connection removes a thread from the cache to use it. (MySQL adds threads to the cache only when connections close and removes them from the cache only when new connections are created.) 이 변수를 설정하는 것은 즉각적인 효과는 없다. 그 효과는 다음 번에 연결(connection)이 닫힐 때까지 지연된다. 이때 MySQL은 캐시에 스레드를 저장할 공간이 있는지 확인한다. 공간이 있다면 또 다른 연결(connection)에 의해 나중에 재사용할 수 있도록 스레드를 캐싱한다. 공간이 없다면 그것을 캐싱하는 대신 스레드를 죽인다. 이 경우 캐시에 있는 스레드 수와 그에 따른 사용 메모리양이 즉시 감소하지는 않는다. 새 연결이 캐시에서 스레드를 제거하여 사용할 때만 감소한다. (MySQL은 연결이 닫힐 때만 캐시에 스레드를 추가하고 새 연결이 생성될 때만 캐시에서 스레드를 제거함)
#thread_cache_size You can compute a reasonable value for this variable by observing the server’s behavior over time. Watch the Threads_connected status variable and find its typical maximum and minimum. You might want to set the thread cache large enough to hold the difference between the peak and off-peak usage, and go ahead and be generous, because if you set it a bit too high it’s not a big problem. You might set it two or three times as large as needed to hold the fluctuations in usage. 당신은 시간이 지남에 따라 서버의 동작을 관찰함으로써 이 변수에 대한 합리적인 값을 계산할 수 있다. Threads_connected 상태 변수를 보고 일반적인 최대값과 최소값을 찾으십시오. 스레드 캐시를 최대 사용량과 최소 사용량 간의 차이를 충분히 수용할 수 있을 정도로 크게 설정하고, 계속하여 관대하게 처리하십시오. 왜냐하면 너무 높게 설정하는 것은 그리 큰 문제가 되지 않기 때문이다. 사용량의 변동을 억제하기 위해 필요한만큼의 두 세 배정도로 크게 설정할 수도 있다. -> 이것에서 많은 힌트를 얻었다. 여기에서 설명하는 내용을 정리해보면 평소 connection의 수와 max connection의 수의 차만큼 thread_cache_size를 할당하는 것으로 확인하였다. 그리고 아래는 그것에 대한 예를 들고 있다.
For example, if the Threads_connected status variable seems to vary between 150 and 175, you could set the thread cache to 75. But you probably shouldn’t set it very large, because it isn’t really useful to keep around a huge amount of spare threads waiting for connections; a ceiling of 250 is a nice round number (or 256, if you prefer a power of two). You can also watch the change over time in the Threads_created status variable. If this value is large or increasing, it’s another clue that you might need to increase the thread_cache_size variable. 예를 들어, Threads_connected 상태 변수가 150과 175 사이에 차이가 나는 경우, 스레드 캐시를 75로 설정할 수 있다. 하지만 아마도 당신은 그것을 그리 크게 설정해서는 안 될 것이다. 엄청난 양의 여분의(spare) 스레드가 접속을 기다리게 하는 것은 별로 유용하지 않기 때문이다. 최대 한계값 250은 좋은 근사치값이다. (또는 만약 제곱을 원한다면 256) Thread_created 상태 변수에서도 시간에 따른 변화를 볼 수 있다. 이 값이 크거나 증가하면 Thread_cache_size 변수를 늘려야 할 수도 있다는 또 다른 단서다.
Check Threads_cached to see how many threads are in the cache already. A related status variable is Slow_launch_threads. A large value for this status variable means that something is delaying new threads upon connection. This is a clue that something is wrong with your server, but it doesn’t really indicate what. 캐시에 이미 있는 스레드 수를 보려면 Thread_cached 를 확인하세요. 관련 상태 변수는 Slow_launch_threads이다. 이 상태 변수의 큰 값은 연결 시 어떤 것이 새 스레드를 지연시키고 있음을 의미한다. 이것은 서버에 문제가 있다는 단서지만, 실제로 무엇을 나타내지는 않는다.
It usually means there’s a system overload, causing the operating system not to schedule any CPU time for newly created threads. It doesn’t necessarily indicate that you need to increase the size of the thread cache. You should diagnose the problem and fix it rather than masking it with a cache, because it might be affecting other things, too. 이는 대개 시스템 과부하가 발생하여 운영 체제가 새로 생성된 스레드의 CPU 시간을 스케줄(일정을 계획)하지 못하게 한다는 것을 의미한다. 반드시 스레드 캐시의 크기를 늘릴 필요가 있음을 나타내는 것은 아니다. 다른 것에도 영향을 줄 수 있기 때문에 캐시로 가리기보다는 문제를 진단하고 고쳐야 한다.
#The Thread Cache The thread cache holds threads that aren’t currently associated with a connection but are ready to serve new connections. When there’s a thread in the cache and a new connection is created, MySQL removes the thread from the cache and gives it to the new connection. When the connection is closed, MySQL places the thread back into the cache, if there’s room. If there isn’t room, MySQL destroys the thread. As long as MySQL has a free thread in the cache it can respond rapidly to connection requests, because it doesn’t have to create a new thread for each connection. 스레드 캐시는 현재 연결과 연결되지 않았지만 새 연결을 제공할 준비가 된 스레드를 보관한다. 캐시에 스레드가 있고 새 연결이 생성되면 MySQL은 캐시에서 스레드를 제거하여 새 연결에 부여한다. 연결이 닫히면 MySQL은 공간이 있는 경우 스레드를 캐시에 다시 넣는다. 공간이 없으면 MySQL은 스레드를 파괴한다. MySQL이 캐시에 사용가능한(free) 스레드가 있는 한 각 연결에 대해 새 스레드를 만들 필요가 없기 때문에 연결 요청에 빠르게 응답할 수 있다.
The Thread_cache_size variable specifies the number of threads MySQL can keep in the cache. You probably won’t need to configure this value unless your server gets many connection requests. To check whether the thread cache is large enough, watch the Threads_created status variable. thread_cache_size 변수는 MySQL이 캐시에 보관할 수 있는 스레드 수를 지정한다. 서버가 많은 연결 요청을 받지 않는 한 이 값을 구성할 필요가 없을 것이다. 스레드 캐시가 충분히 큰지 확인하려면 Threads_created 상태 변수를 확인하세요.
We generally try to keep the thread cache large enough that we see fewer than 10 new threads created each second, but it’s often pretty easy to get this number lower than 1 per second. A good approach is to watch the Threads_connected variable and try to set Thread_cache_size large enough to handle the typical fluctuation in your workload. For example, if Threads_connected usually stays between 100 and 120, you can set the cache size to 20. If it stays between 500 and 700, a thread cache of 200 should be large enough. 우리는 일반적으로 초당 10개 미만의 새로운 스레드가 생성되는 것을 볼 수 있을 정도로 스레드 캐시를 크게 유지하려고 노력하지만, 종종 이 숫자를 초당 1보다 낮게 설정하는 것은 꽤 쉽다. 좋은 접근방식은 Threads_connected 변수를 관찰하고 일반적인 워크로드의 변동을 처리할 수 있을 만큼 thread_cache_size를 크게 설정하는 것이다. 예를 들어, 일반적으로 Threads_connected의 수가 100에서 120 사이인 경우 캐시 크기를 20으로 설정할 수 있다. 500에서 700사이에 머문다면 thread_cache_size는 200정도로 충분히 커야 한다.
Think of it this way: at 700 connections, there are probably no threads in the cache; at 500 connections, there are 200 cached threads ready to be used if the load increases to 700 again. Making the thread cache very large is probably not necessary for most uses, but keeping it small doesn’t save much memory, so there’s little benefit in doing so. Each thread that’s in the thread cache or sleeping typically uses around 256 KB of memory. 이렇게 생각해 보십시오. 700개의 연결에서 캐시에 스레드가 없을 수 있으며, 500개의 연결에서, 부하가 다시 700개로 증가할 경우 사용할 수 있는 캐시된 스레드가 200개 있다. 스레드 캐시를 매우 크게 만드는 것은 아마도 대부분의 용도에 필요하지 않을 것이지만, 작게 유지하는 것은 많은 메모리를 절약하지 못하므로 그렇게 하는 것이 별로 이득이 되지 않는다. 스레드 캐시에 있거나 절전 모드인 각 스레드는 일반적으로 약 256KB의 메모리를 사용한다.
This is not very much compared to the amount of memory a thread can use when a connection is actively processing a query. In general, you should keep your thread cache large enough that Threads_created doesn’t increase very often. If this is a very large number, however (e.g., many thousand threads), you might want to set it lower because some operating systems don’t handle very large numbers of threads well, even when most of them are sleeping 이는 연결이 쿼리를 능동적으로 처리할 때 스레드가 사용할 수 있는 메모리 양에 비하면 그리 많지 않다. 일반적으로 스레드_생성된 스레드 캐시가 자주 증가하지 않을 정도로 충분히 큰 스레드 캐시를 유지해야 한다. 그러나 이 수가 매우 많은 경우(예: 수천 개의 스레드) 일부 운영 체제는 많은 수의 스레드를 잘 처리하지 못하기 때문에 대부분의 스레드가 절전 모드일 때 조차도 이 스레드를 낮게 설정하는 것이 좋다. -> 예상치 못한 메모리 leak이 발생할 수도 있다고 한다. 반영후 2주정도 확인해서 적정값을 찾아야 한다. 1%내외로 유지하는 것이 목표
2. 서비스 DB 반영
1차 반영) percona 엔지니어의 얘기대로 일단 defalut에서 16으로 변경하고 cache miss rate이 떨어지는지 확인함
SET global thread_cache_size = 16;
mysql> show global variables like 'thread%';
+-------------------+---------------------------+
| Variable_name | Value |
+-------------------+---------------------------+
| thread_cache_size | 16 |
| thread_handling | one-thread-per-connection |
+-------------------+---------------------------+
mysql> show status like 'connections';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Connections | 490123 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show status like 'thread_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 4 |
| Threads_connected | 106 |
| Threads_created | 74044 |
| Threads_running | 3 |
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 125 |
| Threads_created | 83512 |
| Threads_running | 5 |
+------------------------------------------+-------+
8 rows in set (0.00 sec)
//cache miss rate(%) = (Threads_created / connections) * 100 --> (74044/490123) * 100 = 15.10%
//-> cache miss rate이 떨어지는 것을 확인함
#주의점! -> Slow_launch_threads 값을 반드시 확인해야함
# Slow_launch_threads 이 값이 크다면 어떠한 원인으로 새 스레드를 지연시키고 있음을 의미한다. 이것은 서버에 문제가 있다는 단서지만, 실제로 무엇인지 나타내지는 않는다. 이는 대게 시스템 과부하가 발생하여 운영체제가 새로 생성된 Thread의 CPU time을 스케줄(일정을 계획)하지 못하게 된다는 것이다. 반드시 스레드 캐시의 크기를 늘릴 필요가 있음을 나타내는 것은 아니다. 다른 것에도 영향을 줄 수 있기 때문에 캐시로 가리기보다는 문제를 진단하고 고쳐야 한다.
2차)
1주일정도 모니터링해보니 cache_miss rate 떨어지고 시스템에 영향이 없어 High Performance MySQL 내용대로 추가 반영를 계획함 peak time시 thread_connected를 확인하여 그 차만큼 thread_cache_size를 늘린다. mysql> show status like 'thread_%'; 로 확인해보니 현재 DB의 connection thread는 110~150사이였다.
바로 48을 반영하고 싶었지만 절반인 24를 할당하여 한번 더 확인하기로 하였다.
SET global thread_cache_size = 24;
cache miss rate(%) = (Threads_created / connections) * 100 -> 3.7% -> 3시간단위의 max와 표준편차를 이력추척해보았는데 3.7%정도 나온다.
조금 더 내려본다. 보통 max가 150정도 low가 110이라 40정도 생각한다.
이진법으로 확인해서 24의 두배인 48로 올려보고 다시 로그를 모니터링 해본다.
3차)
SET global thread_cache_size = 48; -> High Performance MySQL저자 말대로 나도 이진법을 믿는 사람중에 하나일까?ㅎ
cache miss rate(%) = (Threads_created / connections) * 100 -> 0.35% -> 이제 적정값을 찾았다. 모니터링하면서 miss rate가 감소하는지 확인한다.
3. 반영결과
thread_cache_size tunnig
한달 안에 1%로 가는 것이라고 생각했지만, 중간에 1번의 장애라는 변수(Threads_created 값이 급증;)가 생겨서 실제 수지는 그렇게 바로 내려가지 않았다. 하지만 현재도 시간단위 추적해보면 1% 내외이고 적정한 튜닝으로 확인하였다. 언젠간 1%로 내려가겠지.. 아래는 반영후 모니터링 값이다. 요즘 장애가 많이 줄었고, 여유로운 cpu usage를 볼때마다 뿌듯하다.
In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required Connection pools are used to enhance the performance of executing commands on a database Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established If all the connections are being used, a new connection is made and is added to the pool Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.
소프트웨어 엔지니어링에서 연결 풀은 데이터베이스에 대한 향후 요청이 필요할 때 연결을 재사용할 수 있도록 유지 관리하는 데이터베이스 연결의 캐시다. 연결 풀은 데이터베이스에서 명령을 실행하는 성능을 향상시키는 데 사용된다. 각 사용자에 대한 데이터베이스 연결, 특히 동적 데이터베이스 기반 웹 사이트 응용프로그램에 대한 요청은 비용이 많이 들고 자원을 낭비한다. 연결 풀링에서는 연결이 생성된 후 풀에 배치하고 다시 사용하므로 새 연결을 설정할 필요가 없다. 모든 연결을 사용하는 경우 새 연결이 만들어지고 풀에 추가된다. 또한 연결 풀링은 데이터베이스에 대한 연결을 설정하기 위해 사용자가 기다려야 하는 시간을 줄인다.
--> mysql에서는 connection(thread)을 미리 생성해놓고 재사용하는 용도 thread_cache_size가 이 개념과 비슷하다.
#thread pool
In computer programming, a thread pool is a software design pattern for achieving concurrency of execution in a computer program. Often also called a replicated workers or worker-crew model,[1] a thread pool maintains multiple threads waiting for tasks to be allocated for concurrent execution by the supervising program. By maintaining a pool of threads, the model increases performance and avoids latency in execution due to frequent creation and destruction of threads for short-lived tasks.[2] The number of available threads is tuned to the computing resources available to the program, such as a parallel task queue after completion of execution.
컴퓨터 프로그래밍에서, 스레드 풀은 컴퓨터 프로그램에서 실행의 동시성을 달성하기 위한 소프트웨어 설계 패턴이다. 종종 복제된 작업자 또는 작업자 스크루 모델이라고도 하며, 스레드 풀은 감독 프로그램에 의해 동시 실행이 할당되기를 기다리는 여러 개의 스레드를 유지한다. 이 모델은 스레드 풀을 유지함으로써 성능을 높이고 단명 작업에 대한 스레드 생성 및 파괴가 빈번해 실행 지연을 방지한다. 사용 가능한 스레드 수는 실행 완료 후 병렬 작업 대기열과 같이 프로그램에서 사용할 수 있는 컴퓨팅 리소스에 맞춰 조정된다.
--> connection(thread)을 미리 생성해놓고 재사용하면서 DB 트랜잭션을 관리하여 부하를 줄인다.
mysql에서만 보면 connection pool의 개념에서 부하를 관리할 수 있게 좀 더 나아간 버전(thread_pool_size)으로 보면 될 듯하다.
3. Mysql Thread pool
mysql thread pool
mysql은 클라이언트에서 접속을 요청할 때마다 해당 요청을 수행하는 스레드를 생성한다.
스레드(connection)를 생성할 때 스레드에서 필요한 thread_stack 등 기본적으로 사용할 메모리를 같이 할당한다.
(평소에는 그렇지 않겠지만 이러한 작업이 반복되다 적정임계치가 넘어서게 되면 DB에 부하를 발생시킨다.)
mysql commuity 버전은 thread pool을 사용할 수 없고
mysql enterprise 버전과 MariaDB에서만 사용가능하다.
mysql에서 스레드풀 사용이 유료 라이선스인 이유는 동시접속이 몰려 부하가 발생했을 때 알 수 있는데 아래 그림이 그것을 설명한다.
1) MySQL 서버가 쿼리 수행을 위한 충분한 CPU와 메모리 리소스를 확보할 때까지 쿼리 수행을 기다림 2) 커넥션에 대한 진행중인 트랜잭션 쿼리의 우선순위를 매김 3) 쓰레드를 그룹으로 나누어 그룹당 하나의 액티브 쓰레드를 관리하는데 목표를 두고 동작 4) 쿼리가 지연처리 되거나 오랜시간 수행될 때 데드락을 피함
5. 결론
엄청나게 트랜잭션이 몰리는 시스템이면 thread pool을 쓰는 게 좋겠지만 오픈소스 DB를 사용하는 목적에 기본적으로 비용절감이라는 것이 있기 때문에 적절하게 thread_cache_size 튜닝을 한다면 어느정도 시스템 안정성을 보장할 수 있다.
-> Clone DB인 MariaDB에서는 무료버전에서도 thread pool을 사용할 수 있다. 개인적으로 상용서비스에서 mysql에서 MariaDB로 가는게 조금 꺼려지는 이유는 장애시 로그에 대한 러퍼런스를 찾기가 쉽지 않다. mysql보다 관련내용이 많이 부족하다. 비슷하지만 detail에서는 조금 다르다.
현재도 상용서비스에 Mysql , MariaDB 다 쓰고 있지만 장애 때 MariaDB쪽에 문제가 생기면 조금 더 부담이 되는 것은 사실이다. ㅎㅎ
