MySQL thread id 13168615, OS thread handle 0x7f28fac80b00, query id 120397240 192.168.0.213 root updating
UPDATE `TABLE` SET `rsInstall` = '123123', `rsOpen` = '123123' ##쿼리내용
WHERE `rsToday` = '123123'
AND `rsAdmIdx` = '123123'
AND `rsAffIdx` = '3123123'
AND `rsAdsIdx` = '123123'
AND `rsSubAffIdx` = '1123123'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9873 page no 170251 n bits 88 index `PRIMARY` of table `DB`.`TABLE` trx table locks 1 total table locks 2 trx id 47312201250 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 ##이부분이 지금 데드락에 빠진거고
*** (2) TRANSACTION: ##두번째 트랜잭션
TRANSACTION 47312201244, ACTIVE 0 sec fetching rows
MySQL thread id 13168659, OS thread handle 0x7f2914242b00, query id 120397235 192.168.0.213 root updating
UPDATE `TABLE` SET `rInstall` = '18123123, `rOpen` = '123123' ##쿼리내용
WHERE `rsToday` = '123123'
AND `rsAdmIdx` = '123123'
AND `rsAffIdx` = '123123'
AND `rsAdsIdx` = '123213'
AND `rsSubAffIdx` = '123123'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9873 page no 170251 n bits 88 index `PRIMARY` of table `DB`.`TABLE` trx table locks 1 total table locks 2 trx id 47312201244 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 ##여기도 데드락에 빠졌꼬
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9873 page no 170473 n bits 88 index `PRIMARY` of table `DB`.`TABLE` trx table locks 1 total table locks 2 trx id 47312201244 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 ##데드락을 해결하기위해 이부분이 롤백됨
*** WE ROLL BACK TRANSACTION (2)
위 두개의 트랜잭션이 데드락에 빠졌꼬 이를 해결하기 위해 두번째 트랜잭션이 롤백됐음 을 확인할 수 있다.
음. 난 dba가 아니라서 이걸보고 그다음 뭘 어떠케 해줘야 하는지 모르겠네 그래도 일단 dba한테 알려는 줄 수 있는 se가 되자
버전 : mysql Ver 15.1 Distrib 10.1.29-MariaDB, for Linux (x86_64) using readline 5.1
메모리 용량 : ~]# free
total used free shared buffers cached
Mem: 65856248 65592836 263412 4 92876 181636
-/+ buffers/cache: 65318324 537924
Swap: 8389624 6001720 2387904
위와같이 물리 메모리 전부 사용하고 스왑메모리까지 사용하고 있다.
우선 innodb buffer pool size 란 간단하게 innodb용 캐시 사이즈라고 생각하면 될듯하다.
https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_buffer_pool_instance 참고
cache
The general term for any memory area that stores copies of data for frequent or high-speed retrieval. In InnoDB, the primary kind of cache structure is the buffer pool.
buffer
A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the change buffer.
buffer pool
The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.
Several InnoDB status variables, INFORMATION_SCHEMA tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can avoid a lengthy warmup period after restarting the server, particularly for instances with large buffer pools, by saving the buffer pool state at server shutdown and restoring the buffer pool to the same state at server startup. See Saving and Restoring the Buffer Pool State.
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.
A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.
Competition for physical memory can cause paging in the operating system.
InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.
Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
The time to initialize the buffer pool is roughly proportional to its size. On instances with large buffer pools, initialization time might be significant.
참 IT 용어라는게.. 용어 ? 하여튼 buffer 하나 이해하는데 시간 적잖이 걸렸다. 그냥 뭔가 감은 오는데 제대로 이해하려면 확실히 이것저것 공부가 많이 된다.
대충 설명하자면 데이터를 디스크에 쓸 때 한번에 10개의 데이터를 한번에 쓰기위해 buffer라는 기능을 사용한다. 라고 이해했다
###innodb_buffer_pool_instances 이거는 buffer pool size 를 몇개의 쓰레드로 나눌지. buffer pool size 가 1GB 이상일때만 사용가능
내용 요약하자면 1.설정 안할경우 기본값은 128MB 2.최대값은 32bit의 경우 4GB(그러나 실제로는 더 낮을 수 있다고 한다.), 64bit의 경우 16엑사?바이트(첨봄, 테라>페타>엑사) 3.DB서버로만 이용할경우 물리 메모리의 80%를 권장한다. 4.할당값보다 10% 추가로 할당됨(디스크 파티션 나눌 때 10%정도 빠지는거랑 같은거)
공식홈페이지에 보면 innodb buffer 에대한 여러가지 기능(더블라이트나 뭐 하이튼 겁내 별게 많음)들을 사용하면 성능향상에 도움이 될 꺼같은데 언제 써야 도움이 되는지를 모르겠네...
하여튼 버퍼가 뭔지 알았으니까 이제 버퍼 사이즈를 조정할껀데.. 조정하는 이유를 먼저 알아야한다.(조정할 필요가 없는데 조정하면 안되니까)
조정하는이유는 메모리가 가득차서 스왑메모리영역까지 사용해버린다. 스왑사용하면 속도가 느려지니까 스왑사용안되도록 조치를 해야한다.(정상적으로 메모리가 사용이 되는거라 메모리부족한거면 메모리를 늘려야한다.) 보니까 버퍼 풀 사이즈가 큰거같다. 54GB로 잡혀있다. 이걸 줄여도 될까 자세히 한번 알아봐야겠따.
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
SHOW ENGINE INNODB STATUS\G 명령어로 엔진의 상태를 좀 봐봐야겠다. (innodb의 상태를 저장해주는 innodb 모니터링이라는게 있는데 이걸 불러오는 명령어가 show engine innodb status 이다.즉 innodb 상태 보는 명령어. 내가 지금 봐야하는 buffer pool memory 섹션외에도 디텍티드 데드락섹션, 트랜잭션 세션이나 세마포어 섹션을 확인하여 데드락의 원인을 파악할 수 있다. 이거 다하고 공부해야지)
나는 buffer pool and memory 섹션을 봐야한다.
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 60728279040; in additional pool allocated 0 ### 약 56GB 정도 할당된걸 확인할 수 있음
Adaptive hash index 1547957664 (917988664 + 629969000)
Page hash 7172584 (buffer pool 0 only)
Dictionary cache 231761997 (229498768 + 2263229)
File system 1032976 (812272 + 220704)
Lock system 143445008 (143436728 + 8280)
Recovery system 0 (0 + 0)
Dictionary memory allocated 2263229 ###딕셔너리에 할당된 메모리
Buffer pool size 3538936
Buffer pool size, bytes 57981927424
Free buffers 8189 ##나는 지금 요부분이 중요하다. 서버의 메모리가 부족하여 스왑메모리가 사용됐고 이걸 방지하기위해 불필요하게 사용되는 메모리는 없는지 찾고있는 상황이였고 디비의 innodb buffer poolsize가 전체 메모리의 약 85~90프로를 차지하고 있다. 따라서 innodb buffer pool size 를 줄여야 하는데.. 줄여도 되는 상황일까 ? free buffers 가 8189 이다.
Database pages 3492297
Old database pages 1288984
Modified db pages 141927
Percent of dirty pages(LRU & free pages): 4.054
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 117, single page 0
Pages made young 1465646, not young 3638134
6.33 youngs/s, 1.00 non-youngs/s
Pages read 3411009, created 1266390, written 13049586
0.67 reads/s, 19.33 creates/s, 228.46 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3492297, unzip_LRU len: 0
I/O sum[100408]:cur[2856], unzip sum[0]:cur[0]
결론
free buffer가 없으니 그냥 냅뒀따가 메모리 추가해야겠다. buffer pool size 의 쓰레드를 8개로 나눠서 사용하고 있는데 이거를 좀 더 잘게 나눠보고싶다. 근데 실 사용 서버이고 내가 갑이 아니라서 괜히 문제생기면 안되니까 걍 메모리 추가해야겠따.
Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect.
Commandline:--log-queries-not-using-indexes
Scope: Global
Dynamic: Yes
Data Type:boolean
Default Value:OFF
위와같은 내용이 있다. 어쩌구 저쩌구 용량 관계없이 쓰여진다 뭐 이런 내용인듯. 기본값은 OFF라는데 왜 이서버들은 ON이냐
MariaDB [(none)]> SHOW VARIABLES LIKE '%log_queries_not_%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
확인해보니 켜져있다.
MariaDB [(none)]> set global log_queries_not_using_indexes = OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE '%log_queries_not_%';