MySQL & Maria
[ MySQL & MariaDB ] OS Buffer Cache 공간 확인 ( Use. Percona )
- -
반응형
목차
1.Buffer/Cache 사용하는 파일 확인
2. Unmap_cache를 이용하여 Cache 반납
3.Buffer/Cache 확인
MySQL 에서 Buffer Cache를 얼만큼 사용하고 있는지 확인
- percona blog에 올라와있는 툴로 확인
- Cent 7.3의 경우 aclocal Version이 1.13이므로 MakeFile을 수정하고 make 실행
**[root@slave linux-ftools]# git clone <https://github.com/yazgoo/linux-ftools.git>
[root@slave linux-ftools]# yum -y install perl
[root@slave linux-ftools]# cd linux-ftools/
[root@slave linux-ftools]# ./configure**
**[root@slave linux-ftools]# make**
CDPATH="${ZSH_VERSION+.}:" && cd . && /bin/sh /root/linux-ftools/missing --run aclocal-1.11
**/root/linux-ftools/missing: line 54: aclocal-1.11: command not found**
WARNING: `aclocal-1.11' is missing on your system. You should only need it if
you modified `acinclude.m4' or `configure.ac'. You might want
to install the `Automake' and `Perl' packages. Grab them from
any GNU archive site.
cd . && /bin/sh /root/linux-ftools/missing --run automake-1.11 --gnu
/root/linux-ftools/missing: line 54: automake-1.11: command not found
WARNING: `automake-1.11' is missing on your system. You should only need it if
you modified `Makefile.am', `acinclude.m4' or `configure.ac'.
You might want to install the `Automake' and `Perl' packages.
Grab them from any GNU archive site.
CDPATH="${ZSH_VERSION+.}:" && cd . && /bin/sh /root/linux-ftools/missing --run autoconf
configure.ac:7: error: possibly undefined macro: AM_INIT_AUTOMAKE
If this token and others are legitimate, please use m4_pattern_allow.
See the Autoconf documentation.
make: *** [configure] Error 1
****[root@slave linux-ftools]# vim Makefile **==> Makefile 에서 ACLOCAL = aclocal-1.11을 1.13으로 변경 Cent7.3에는 1.11이 없고 1.13이있음**
[root@slave linux-ftools]# make
[root@slave linux-ftools]# sudo make install
**[root@slave data]# linux-fincore --only-cached $(find /data/data/ -type f)**
filename size total_pages min_cached page cached_pages cached_size cached_perc
-------- ---- ----------- --------------- ------------ ----------- -----------
/data/data/ibdata1 12,582,912 3,072 0 692 2,834,432 22.53
/data/data/ib_logfile1 50,331,648 12,288 0 12,288 50,331,648 100.00
/data/data/#ib_16384_0.dblwr 589,824 144 0 144 589,824 100.00
/data/data/#ib_16384_1.dblwr 8,978,432 2,192 0 2,192 8,978,432 100.00
/data/data/undo_001 130,023,424 31,744 0 14,029 57,462,784 44.19
/data/data/undo_002 100,663,296 24,576 0 2,080 8,519,680 8.46
/data/data/ib_logfile0 50,331,648 12,288 0 12,288 50,331,648 100.00
/data/data/#innodb_temp/temp_1.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_2.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_3.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_4.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_5.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_6.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_7.ibt 81,920 20 0 20 81,920 100.00
/data/data/#innodb_temp/temp_8.ibt 98,304 24 0 25 102,400 104.17
/data/data/#innodb_temp/temp_9.ibt 81,920 20 0 20 81,920 100.00
/data/data/#innodb_temp/temp_10.ibt 98,304 24 0 25 102,400 104.17
/data/data/mysql.ibd 26,214,400 6,400 0 2,293 9,392,128 35.83
/data/data/auto.cnf 56 1 0 1 4,096 100.00
/data/data/ca.pem 1,112 1 0 1 4,096 100.00
/data/data/server-key.pem 1,680 1 0 1 4,096 100.00
/data/data/server-cert.pem 1,112 1 0 1 4,096 100.00
/data/data/private_key.pem 1,680 1 0 1 4,096 100.00
/data/data/public_key.pem 452 1 0 1 4,096 100.00
/data/data/ibtmp1 12,582,912 3,072 0 3,072 12,582,912 100.00
/data/data/mysql-relay-bin.000042 373 1 0 1 4,096 100.00
/data/data/mysql.000005 955,024,831 233,161 0 81 331,776 0.03
/data/data/mysql.000009 1,073,744,968 262,145 0 35,002 143,368,192 13.35
/data/data/mysql.000016 199 1 0 1 4,096 100.00
/data/data/mysql-relay-bin.index 50 1 0 1 4,096 100.00
/data/data/lee/fts_0000000000000431_config.ibd 114,688 28 4 16 65,536 57.14
/data/data/lee/fts_0000000000000431_00000000000000c1_index_1.ibd 229,376 56 56 1 4,096 1.79
/data/data/lee/fts_0000000000000431_00000000000000c1_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000c8_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000c8_index_3.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000cf_index_1.ibd 229,376 56 56 1 4,096 1.79
/data/data/lee/fts_0000000000000431_00000000000000cf_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000cf_index_3.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/tb_test_repl.ibd 314,572,800 76,800 4 3,080 12,615,680 4.01
/data/data/lee/tb_finish.ibd 114,688 28 4 12 49,152 42.86
/data/data/sbtest/sbtest8.ibd 260,046,848 63,488 7 4 16,384 0.01
/data/data/mysql.000007 787,200,325 192,188 0 1 4,096 0.00
/data/data/sysbench/sbtest5.ibd 30,408,704 7,424 15 1 4,096 0.01
/data/data/sysbench/sbtest4.ibd 30,408,704 7,424 15 1 4,096 0.01
/data/data/sysbench/sbtest1.ibd 2,243,952,640 547,840 0 399,075 1,634,611,200 72.85
/data/data/sysbench/sbtest2.ibd 30,408,704 7,424 4 28 114,688 0.38
/data/data/employees/employees.ibd 23,068,672 5,632 4 5,628 23,052,288 99.93
/data/data/employees/departments.ibd 131,072 32 4 24 98,304 75.00
/data/data/employees/dept_manager.ibd 131,072 32 4 24 98,304 75.00
/data/data/employees/dept_emp.ibd 26,214,400 6,400 4 6,396 26,198,016 99.94
/data/data/employees/titles.ibd 28,311,552 6,912 4 6,908 28,295,168 99.94
/data/data/employees/salaries.ibd 109,051,904 26,624 0 26,624 109,051,904 100.00
/data/data/employees/lee.ibd 142,606,336 34,816 0 34,816 142,606,336 100.00
/data/data/mysql.000015 179 1 0 1 4,096 100.00
/data/data/mysql.000010 712,647,054 173,987 0 145,220 594,821,120 83.47
/data/data/mysql.000014 57,728,331 14,094 0 14,094 57,729,024 100.00
/data/data/mysql.index 195 1 0 1 4,096 100.00
/data/data/mysql.000013 628,004,665 153,322 0 135,961 556,896,256 88.68
/data/data/mysql.000006 179 1 0 1 4,096 100.00
/data/data/mysql.000008 179 1 0 1 4,096 100.00
/data/data/mysql.000012 960 1 0 1 4,096 100.00
/data/data/ib_buffer_pool 31,500 8 0 8 32,768 100.00
/data/data/mysql.000017 502,631,597 122,713 0 122,713 502,632,448 100.00
/data/data/test/test2.ibd 14,680,064 3,584 4 12 49,152 0.33
**/data/data/mysql-relay-bin.000043 500,559,005 122,207 0 122,207 500,559,872 100.00
/data/data/mysql.000011 597,986,769 145,993 0 145,993 597,987,328 100.00**
---
**total cached size: 5,132,869,632**
**[root@slave data]# free -mh**
total used free shared **buff/cache** available
Mem: 7.4G 1.4G 222M 5.3M **5.7G** 5.6G
Swap: 2.0G 11M 2.0G
UNMAP_CACHE
- MySQL Binary log / relay log가 가지고있는 Cache 반납시킴
###CACHE UNMAP --> binary log / relay log에대한 cache를 반납하도록함
**[root@slave ~]# git clone <https://github.com/dkwlfowh/mysql_cache_unmap.git>
[root@slave ~]# cd mysql_cache_unmap/lee/mysql_cache_unmap/lib/
[root@slave ~]# gcc -o unmap_cache unmap_cache.c
[root@slave ~]# cd /root/mysql_cache_unmap/lee/mysql_cache_unmap
[root@slave mysql_cache_unmap]# ./mysql_cache_unmap /data/data /data/data**
Bindary Log : /data/data
Relay Log : /data/data
-----------------------------------------------------------------------
Fri Nov 18 16:17:29 KST 2022 cache unmap.. start
-----------------------------------------------------------------------
Mem: 7732372 1506180 218020 5472 6008172 5886376
-----------------------------------------------------------------------
unmap processing.. /data/data/mysql.000005.. ok
unmap processing.. /data/data/mysql.000006.. ok
unmap processing.. /data/data/mysql.000007.. ok
unmap processing.. /data/data/mysql.000008.. ok
unmap processing.. /data/data/mysql.000009.. ok
unmap processing.. /data/data/mysql.000010.. ok
unmap processing.. /data/data/mysql.000011.. ok
unmap processing.. /data/data/mysql.000012.. ok
unmap processing.. /data/data/mysql.000013.. ok
unmap processing.. /data/data/mysql.000014.. ok
unmap processing.. /data/data/mysql.000015.. ok
unmap processing.. /data/data/mysql.000016.. ok
unmap processing.. /data/data/mysql.000017.. ok
unmap processing.. /data/data/mysql-relay-bin.000042.. ok
unmap processing.. /data/data/mysql.000005.. ok
unmap processing.. /data/data/mysql.000006.. ok
unmap processing.. /data/data/mysql.000007.. ok
unmap processing.. /data/data/mysql.000008.. ok
unmap processing.. /data/data/mysql.000009.. ok
unmap processing.. /data/data/mysql.000010.. ok
unmap processing.. /data/data/mysql.000011.. ok
unmap processing.. /data/data/mysql.000012.. ok
unmap processing.. /data/data/mysql.000013.. ok
unmap processing.. /data/data/mysql.000014.. ok
unmap processing.. /data/data/mysql.000015.. ok
unmap processing.. /data/data/mysql.000016.. ok
unmap processing.. /data/data/mysql.000017.. ok
unmap processing.. /data/data/mysql-relay-bin.000042.. ok
-----------------------------------------------------------------------
Mem: 7732372 1506328 2647932 5472 3578112 5886548
반납후 Cache 확인
- 반납후 Cache를 확인했을때 Binary log / Relay Log의 Cache가 반납된것을 확인
- OS Buffer/Cache 5.7G ⇒ 3.4G 감소
**[root@slave mysql_cache_unmap]# linux-fincore --only-cached $(find /data/data/ -type f)**
filename size total_pages min_cached page cached_pages cached_size cached_perc
-------- ---- ----------- --------------- ------------ ----------- -----------
/data/data/ibdata1 12,582,912 3,072 0 692 2,834,432 22.53
/data/data/ib_logfile1 50,331,648 12,288 0 12,288 50,331,648 100.00
/data/data/#ib_16384_0.dblwr 589,824 144 0 144 589,824 100.00
/data/data/#ib_16384_1.dblwr 8,978,432 2,192 0 2,192 8,978,432 100.00
/data/data/undo_001 130,023,424 31,744 0 14,029 57,462,784 44.19
/data/data/undo_002 100,663,296 24,576 0 2,080 8,519,680 8.46
/data/data/ib_logfile0 50,331,648 12,288 0 12,288 50,331,648 100.00
/data/data/#innodb_temp/temp_1.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_2.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_3.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_4.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_5.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_6.ibt 81,920 20 0 4 16,384 20.00
/data/data/#innodb_temp/temp_7.ibt 81,920 20 0 20 81,920 100.00
/data/data/#innodb_temp/temp_8.ibt 98,304 24 0 25 102,400 104.17
/data/data/#innodb_temp/temp_9.ibt 81,920 20 0 20 81,920 100.00
/data/data/#innodb_temp/temp_10.ibt 98,304 24 0 25 102,400 104.17
/data/data/mysql.ibd 26,214,400 6,400 0 2,293 9,392,128 35.83
/data/data/auto.cnf 56 1 0 1 4,096 100.00
/data/data/ca.pem 1,112 1 0 1 4,096 100.00
/data/data/server-key.pem 1,680 1 0 1 4,096 100.00
/data/data/server-cert.pem 1,112 1 0 1 4,096 100.00
/data/data/private_key.pem 1,680 1 0 1 4,096 100.00
/data/data/public_key.pem 452 1 0 1 4,096 100.00
/data/data/ibtmp1 12,582,912 3,072 0 3,072 12,582,912 100.00
/data/data/mysql-relay-bin.index 50 1 0 1 4,096 100.00
/data/data/lee/fts_0000000000000431_config.ibd 114,688 28 4 16 65,536 57.14
/data/data/lee/fts_0000000000000431_00000000000000c1_index_1.ibd 229,376 56 56 1 4,096 1.79
/data/data/lee/fts_0000000000000431_00000000000000c1_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000c8_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000c8_index_3.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000cf_index_1.ibd 229,376 56 56 1 4,096 1.79
/data/data/lee/fts_0000000000000431_00000000000000cf_index_2.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/fts_0000000000000431_00000000000000cf_index_3.ibd 294,912 72 72 1 4,096 1.39
/data/data/lee/tb_test_repl.ibd 314,572,800 76,800 4 3,080 12,615,680 4.01
/data/data/lee/tb_finish.ibd 114,688 28 4 12 49,152 42.86
/data/data/sbtest/sbtest8.ibd 260,046,848 63,488 7 4 16,384 0.01
/data/data/sysbench/sbtest5.ibd 30,408,704 7,424 15 1 4,096 0.01
/data/data/sysbench/sbtest4.ibd 30,408,704 7,424 15 1 4,096 0.01
/data/data/sysbench/sbtest1.ibd 2,243,952,640 547,840 0 399,075 1,634,611,200 72.85
/data/data/sysbench/sbtest2.ibd 30,408,704 7,424 4 28 114,688 0.38
/data/data/employees/employees.ibd 23,068,672 5,632 4 5,628 23,052,288 99.93
/data/data/employees/departments.ibd 131,072 32 4 24 98,304 75.00
/data/data/employees/dept_manager.ibd 131,072 32 4 24 98,304 75.00
/data/data/employees/dept_emp.ibd 26,214,400 6,400 4 6,396 26,198,016 99.94
/data/data/employees/titles.ibd 28,311,552 6,912 4 6,908 28,295,168 99.94
/data/data/employees/salaries.ibd 109,051,904 26,624 0 26,624 109,051,904 100.00
/data/data/employees/lee.ibd 142,606,336 34,816 0 34,816 142,606,336 100.00
/data/data/mysql.index 195 1 0 1 4,096 100.00
/data/data/ib_buffer_pool 31,500 8 0 8 32,768 100.00
/data/data/test/test2.ibd 14,680,064 3,584 4 12 49,152 0.33
/data/data/mysql-relay-bin.000043 500,559,005 122,207 0 122,207 500,559,872 100.00
---
**total cached size: 2,679,074,816**
[root@slave mysql_cache_unmap]# free -mh
total used free shared **buff/cache** available
Mem: 7.4G 1.4G 2.5G 5.3M **3.4G** 5.6G
Swap: 2.0G 11M 2.0G
참고문서
https://www.percona.com/blog/2019/08/29/using-linux-fincore-to-check-linux-page-cache-usage/
반응형
'MySQL & Maria' 카테고리의 다른 글
| [ MySQL ] ProxySQL Query 빈도수 통계 작업 (0) | 2023.01.06 |
|---|---|
| [ MySQL ] Memory ( Use. performance_schema / sys ) (0) | 2022.12.16 |
| [ MySQL ] MTS ( Multi Thread Slave ) (1) | 2022.11.17 |
| MySQL / MariaDB 이관 정합성 확인 (0) | 2022.11.03 |
| Thread Pool (0) | 2022.11.03 |
Contents
소중한 공감 감사합니다