私のプラットフォーム:
8 cores, CPU benchmark > 10k (Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz)
32GB of RAM
2x 7200 SATA enterprise HDD, 128MB cache, each of them have physical 512b block size
実際のファイルシステム (md3 上の /var) は XFS (128GB)、RAID レベル 0 (ストライプ)、チャンク サイズ 64 kB です。
xfs_info:
meta-data=/dev/md3 isize=256 agcount=16, agsize=2097024 blks
= sectsz=512 attr=2, projid32bit=1
= crc=0 finobt=0 spinodes=0
data = bsize=4096 blocks=33552384, imaxpct=25
= sunit=128 swidth=256 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=0
log =internal bsize=4096 blocks=16384, version=2
= sectsz=512 sunit=8 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
サーバーはApache+nginx+redis+Mariadbの組み合わせです
/var パーティションは、ディスクの中心に近いデータベース専用です。
Filesystem Size Used Avail Use% Mounted on
/dev/md2 16G 6.1G 10G 38% /
/dev/md3 128G 14G 115G 11% /var
/dev/md0 32G 34M 32G 1% /tmp
/dev/md5 3.5T 3.1T 372G 90% /home
/dev/md2 on / type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,usrquota,grpquota) [/]
/dev/md3 on /var type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota) [/var]
/dev/md0 on /tmp type xfs (rw,noexec,noatime,nobarrier,nodiratime,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota)
/dev/md5 on /home type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota) [/home]
現在、InnoDB データ サイズは 3～4 GB に急増しています。
すぐに、InnoDB に約 8 GB、MyISAM に 2～3 GB、Aria に約 2 GB 未満のデータを追加する予定です。Mariadb 10.2 から 10.3 にアップグレードした後、DB が CPU をどんどん消費し始め、一部の DB 応答が 4～5 秒になるようです。DB スロー ログは現時点では何もキャッチしていません。
SSD や余分なメモリを追加せずに、将来に備えてシステムを準備したいと考えています。Redis サービスはありますが、データベース クエリを 100% キャッシュすることはできません。Redis はすでに 1 秒あたり約 550 件のリクエストを処理しています。
innodb_page_size の値を変更しようと考えています (innodb_page_size のデフォルト オプションは現在 16k です)。データをエクスポートしてインポートし直さずに変更できますか? 実行する価値はありますか? 私のシステムにとって、これはもっと良いオプションですか? HW を変更せずにシステムをリセットするための推奨事項はありますか? 速度低下は /home パーティションの 90% が原因ですか? ディスク ヘッドは Web ファイルのために最後のディスクに移動し、先頭に戻る必要がありますか? RAID ディスクのチャンク サイズは 64k から増加/減少しますか?
iostat:
avg-cpu: %user %nice %system %iowait %steal %idle
53.68 17.72 4.94 0.84 0.00 22.81
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 44.93 954.33 359.22 131137332 49362235
sdb 42.35 948.70 352.07 130364493 48378490
md2 0.64 9.86 2.63 1354830 361356
md3 51.90 118.53 668.17 16287740 91814945
md0 0.41 3.04 17.59 418198 2416612
md5 22.25 1743.19 19.57 239537550 2689559
現在の DB 設定は次のようになります。
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
server_id = 1
default_time_zone = America/Toronto
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
character-set-server = utf8
collation-server = utf8_bin
tmpdir = /tmp
event-scheduler = ON
performance_schema = ON
query_response_time_stats = ON
slave-net-timeout = 360
net_write_timeout = 120
# MyISAM #
key-buffer-size = 1024M
myisam-recover-options = FORCE,BACKUP
myisam_sort_buffer_size = 8M #128M is default on Mariadb 10.3; range 4096-18446744073709547520;
aria_pagecache_buffer_size = 512M
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
bind-address = 127.0.0.1
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/log/mariadb/server-id-1-mysql-bin
expire-logs-days = 14 #0 is default for Mariadb 10.3; range 0-99;
sync-binlog = 600 #0 is default on Mariadb 10.3; 0 = OS depend; 1 = one write per commit/statement;
# CACHES AND LIMITS #
tmp-table-size = 1G
max-heap-table-size = 1G
query-cache-type = 2 # 0=disabled, 1=enabled, 2=on demand
query-cache-size = 64M #1M is default on Mariadb 10.3
query_cache_limit = 1M #1m is default on Mariadb 10.3
query_cache_min_res_unit = 512 #512 is default on Mariadb 10.3
max-connections = 128
thread-cache-size = 128 #128 is default on Mariadb 10.3
open-files-limit = 32768 #16k is default on Mariadb 10.3
table-definition-cache = 512 #400 is default on Mariadb 10.3
table-open-cache = 4096 #2000 is default on Mariadb 10.3
join_buffer_size = 1M #256K is default on Mariadb 10.3
sort_buffer_size = 2M #2M is default on Mariadb 10.3
thread_cache_size = 128 # 128 is default on Mariadb 10.3; recommended 100 - ((show status like Threads_created / show status like Connections) * 100)
thread_concurrency = 10 # 10 is default on Mariadb 10.3
# INNODB #
###
#performance increase, but very dangerous
skip-innodb-doublewrite #default if off on Mariadb 10.3
innodb-checksum-algorithm = none #crc32 is default on Mariadb 10.3
innodb-compression-level = 0 #6 is default on Mariadb 10.3
###
#innodb-log-block-size = 4096 #512 is default on Mariadb 10.3; recommended by disk aligment (blockdev --getbsz /dev/sdXX)
#innodb_page_size = 4096 #16K is default on Mariadb 10.3; range (4k or 4096, 8k or 8192, 16k or 16384; Mariadb > 10.1 can be up also 32k/64k); ATTENTION - InnoDB DATA EXPORTl+DROP+IMPORT REQUIRED
innodb-defragment = 1
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 512M # recommended = innodb-buffer-poolsize / 4
innodb_log_buffer_size = 64M #16M is default on Mariadb 10.3
innodb-flush-log-at-trx-commit = 0 #0 = best performance, 1 = best durability, 2 = better performance
innodb_flush_log_at_timeout = 1 #1 is default on Mariadb 10.3
innodb-file-per-table = 1 #1 is default for Mariadb 10.3
innodb-buffer-pool-size = 4G # recommended = >total size of InnoDB data
innodb_buffer_pool_instances = 4 # recommended = RAM chips count;
innodb_thread_concurrency = 0 #0 is default on Mariadb 10.3
innodb_flush_log_at_timeout = 1 #1 is default on Mariadb 10.3
innodb_sort_buffer_size = 1M #1M is default on 10.3 Mariadb
innodb_read_io_threads = 4 #4 is default on Mariadb 10.3
innodb_write_io_threads = 4 #4 is default om Mariadb 10.3
# LOGGING #
long_query_time = 2
log-error = /var/log/mariadb/mariadb-error.log
log-queries-not-using-indexes
slow-query-log
####filter options: admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log-slow-filter = admin,full_join,filesort_on_disk,tmp_table,tmp_table_on_disk
log_slow_verbosity = innodb,query_plan,explain
log_slow_rate_limit = 100 #1 is default on Mariadb 10.3
slow-query-log-file = /var/log/mariadb/mariadb-slow.log
上：
top - 11:00:37 up 1 day, 14:08, 2 users, load average: 6.69, 6.50, 6.98
Tasks: 372 total, 11 running, 358 sleeping, 0 stopped, 3 zombie
%Cpu0 : 57.0 us, 9.7 sy, 21.0 ni, 9.3 id, 0.3 wa, 0.0 hi, 2.7 si, 0.0 st
%Cpu1 : 41.9 us, 7.0 sy, 40.5 ni, 10.3 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 60.1 us, 4.3 sy, 24.9 ni, 10.6 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 67.4 us, 8.0 sy, 17.6 ni, 7.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 96.0 us, 0.7 sy, 3.0 ni, 0.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu5 : 77.4 us, 4.0 sy, 18.6 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 77.7 us, 2.3 sy, 19.9 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu7 : 96.7 us, 1.3 sy, 1.7 ni, 0.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 32746416 total, 755700 free, 8179264 used, 23811452 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 22176368 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
653 mysql 20 0 9890372 2.8g 15288 S 574.1 9.0 756:18.11 mysqld
29788 user2 25 5 447196 68176 48376 S 21.6 0.2 0:00.65 php70-cgi
29790 user2 25 5 447196 68280 48404 S 21.6 0.2 0:00.65 php70-cgi
29807 user2 25 5 442972 63084 45284 R 17.3 0.2 0:00.52 php70-cgi
29811 user2 25 5 442904 56644 40128 R 12.3 0.2 0:00.37 php70-cgi
1137 user1 20 0 876800 83452 5900 S 8.0 0.3 1:56.75 php-fpm
29815 user1 25 5 442536 31244 11192 R 8.0 0.1 0:00.24 php72-cgi
29813 user1 25 5 437760 26280 11008 R 5.0 0.1 0:00.15 php72-cgi
29817 user2 25 5 436752 41688 30368 R 4.0 0.1 0:00.12 php70-cgi
18803 netdata 20 0 15648 4856 912 R 1.0 0.0 3:23.39 apps.plugin
29795 user1 25 5 430876 19464 11052 S 1.0 0.1 0:00.03 php72-cgi
29799 user1 25 5 430876 19288 10880 S 1.0 0.1 0:00.03 php72-cgi
29818 user1 25 5 430876 19464 11052 R 1.0 0.1 0:00.03 php72-cgi
29819 user1 25 5 430876 19344 10912 R 1.0 0.1 0:00.03 php72-cgi
842 nginx 20 0 47744 4468 2376 S 0.7 0.0 0:08.39 nginx
5511 root 20 0 728880 49836 48040 S 0.7 0.2 0:23.29 rsyslogd
5804 netdata 20 0 270380 28012 5560 S 0.7 0.1 21:09.58 python
27760 netdata 20 0 9688 1536 1220 S 0.7 0.0 0:00.14 bash
490 root 20 0 122064 67104 66668 S 0.3 0.2 6:54.47 systemd-journal
843 nginx 20 0 47988 4700 2388 S 0.3 0.0 0:13.04 nginx
848 nginx 20 0 48692 5420 2380 S 0.3 0.0 0:40.13 nginx
936 user1 20 0 751612 14992 2824 S 0.3 0.0 0:02.90 php-fpm
1006 user1 20 0 863532 71616 6964 S 0.3 0.2 1:59.43 php-fpm
1205 redis 10 -10 1806528 1.5g 1184 S 0.3 4.9 15:36.72 redis
3819 user1 20 0 751480 14840 2752 S 0.3 0.0 0:00.60 php-fpm
5569 netdata 20 0 234140 66072 1688 S 0.3 0.2 8:13.37 netdata
18784 user1 20 0 893804 101080 6264 S 0.3 0.3 1:47.93 php-fpm
24478 apache 25 5 824688 19604 4072 S 0.3 0.1 0:00.21 httpd
24938 apache 25 5 824596 19408 3952 S 0.3 0.1 0:00.17 httpd
29529 root 20 0 162248 2616 1600 R 0.3 0.0 0:00.09 top
29821 user2 25 5 302320 12804 8356 R 0.3 0.0 0:00.01 php70-cgi
...
マイトップ:
MySQL on localhost (10.3.9-MariaDB-log) up 0+04:30:25 [10:59:02]
Queries: 893.2k qps: 56 Slow: 3.0 Se/In/Up/De(%): 63/00/05/00
qps now: 112 Slow qps: 0.0 Threads: 3 ( 8/ 22) 73/00/04/00
Key Efficiency: 100.0% Bps in/out: 8.8k/114.7k Now in/out: 11.5k/70.2k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
1 system us 0 Daemon InnoDB purge coordinator
2 system us 0 Daemon InnoDB purge worker
3 system us 0 Daemon InnoDB purge worker
4 system us 0 Daemon InnoDB purge worker
5 system us 0 Daemon InnoDB shutdown handler
64 netdata localhost 0 Sleep
49426 root localhost mysql 0 Query show full processlist
7 event_sch localhost 7000 Daemon Waiting on empty queue
mysqlチューナー:
>> MySQLTuner 1.7.10 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.9-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb-error.log(9K)
[OK] Log file /var/log/mariadb/mariadb-error.log exists
[OK] Log file /var/log/mariadb/mariadb-error.log is readable.
[OK] Log file /var/log/mariadb/mariadb-error.log is not empty
[OK] Log file /var/log/mariadb/mariadb-error.log is smaller than 32 Mb
[OK] /var/log/mariadb/mariadb-error.log doesn't contain any error.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 11.3M (Tables: 6)
[--] Data in MyISAM tables: 334.7M (Tables: 593)
[--] Data in InnoDB tables: 3.1G (Tables: 663)
[--] Data in MEMORY tables: 124.1K (Tables: 6)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4h 25m 12s (893K q [56.160 qps], 113K conn, TX: 1G, RX: 136M)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 31.2G
[--] Max MySQL memory : 7.1G
[--] Other process memory: 3.9G
[--] Total buffers: 6.6G global + 3.7M per thread (128 max threads)
[--] P_S Max memory usage: 100M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.8G (21.66% of installed RAM)
[OK] Maximum possible memory usage: 7.1G (22.84% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (3/893K)
[OK] Highest usage of available connections: 19% (25/128)
[OK] Aborted connections: 0.00% (2/113504)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 562K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 115K sorts)
[!!] Joins performed without indexes: 194
[OK] Temporary tables created on disk: 15% (12K on disk / 80K total)
[OK] Thread cache hit rate: 99% (25 created / 113K connections)
[OK] Table cache hit rate: 98% (1K open / 1K opened)
[OK] Open file limit used: 7% (1K/16K)
[OK] Table locks acquired immediately: 99% (330K immediate / 330K locks)
[OK] Binlog cache memory access: 100.00% (42935 Memory / 42935 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 100.3M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.3.9-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (202M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/81.9M
[OK] Read Key buffer hit rate: 100.0% (115M cached / 6K reads)
[!!] Write Key buffer hit rate: 77.2% (5K cached / 4K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/3.1G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk : 32 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (39286543837 hits/ 39286642011 total)
[!!] InnoDB Write Log efficiency: 76.58% (77174 hits/ 100771 total)
[OK] InnoDB log waits: 0.00% (0 waits / 23597 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 512.0M/808.0K
[OK] Aria pagecache hit rate: 99.7% (3M cached / 11K reads)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb-error.log file
Restrict Host for user@% to user@SpecificDNSorIp
MySQL was started within the last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 1.0M, or always use indexes with JOINs)
インノDB:
=====================================
2018-08-30 10:56:41 0x7fccb833c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14238 srv_active, 0 srv_shutdown, 1184 srv_idle
srv_master_thread log flush and writes: 15422
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 186890
OS WAIT ARRAY INFO: signal count 9143467
RW-shared spins 0, rounds 31440402, OS waits 142956
RW-excl spins 0, rounds 13095374, OS waits 61503
RW-sx spins 2037, rounds 40312, OS waits 453
Spin rounds per wait: 31440402.00 RW-shared, 13095374.00 RW-excl, 19.79 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 95899652
Purge done for trx's n:o < 95899643 undo n:o < 0 state: running but idle
History list length 20
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421993893611928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421993893606216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
99820 OS file reads, 433239 OS file writes, 130428 OS fsyncs
0.20 reads/s, 16384 avg bytes/read, 17.40 writes/s, 5.80 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 270, seg size 272, 344 merges
merged operations:
insert 87, delete mark 267, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1106407, node heap has 919 buffer(s)
Hash table size 1106407, node heap has 1995 buffer(s)
Hash table size 1106407, node heap has 4 buffer(s)
Hash table size 1106407, node heap has 983 buffer(s)
Hash table size 1106407, node heap has 919 buffer(s)
Hash table size 1106407, node heap has 2357 buffer(s)
Hash table size 1106407, node heap has 1394 buffer(s)
Hash table size 1106407, node heap has 735 buffer(s)
378057.19 hash searches/s, 663309.94 non-hash searches/s
---
LOG
---
Log sequence number 58746155240
Log flushed up to 58746154730
Pages flushed up to 58746153668
Last checkpoint at 58746143988
0 pending log flushes, 0 pending chkp writes
27080 log i/o's done, 1.20 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4429185024
Dictionary memory allocated 4705071
Buffer pool size 262144
Free buffers 135207
Database pages 116025
Old database pages 42906
Modified db pages 16
Percent of dirty pages(LRU & free pages): 0.006
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 98, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 99142, created 16883, written 406172
0.20 reads/s, 0.00 creates/s, 16.20 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: 116025, unzip_LRU len: 3216
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65536
Free buffers 33684
Database pages 29131
Old database pages 10773
Modified db pages 4
Percent of dirty pages(LRU & free pages): 0.006
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 31, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24938, created 4193, written 123109
0.00 reads/s, 0.00 creates/s, 4.60 writes/s
Buffer pool hit rate 1000 / 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: 29131, unzip_LRU len: 741
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65536
Free buffers 33601
Database pages 29203
Old database pages 10799
Modified db pages 3
Percent of dirty pages(LRU & free pages): 0.005
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 43, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25078, created 4125, written 91633
0.00 reads/s, 0.00 creates/s, 1.80 writes/s
Buffer pool hit rate 1000 / 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: 29203, unzip_LRU len: 815
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 65536
Free buffers 33966
Database pages 28826
Old database pages 10660
Modified db pages 4
Percent of dirty pages(LRU & free pages): 0.006
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24528, created 4298, written 74115
0.20 reads/s, 0.00 creates/s, 2.60 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: 28826, unzip_LRU len: 807
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 65536
Free buffers 33956
Database pages 28865
Old database pages 10674
Modified db pages 5
Percent of dirty pages(LRU & free pages): 0.008
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24598, created 4267, written 117315
0.00 reads/s, 0.00 creates/s, 7.20 writes/s
Buffer pool hit rate 1000 / 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: 28865, unzip_LRU len: 853
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Process ID=653, Main thread ID=140512678807296, state: sleeping
Number of rows inserted 816, updated 48716, deleted 818, read 17466709916
0.00 inserts/s, 1.60 updates/s, 0.00 deletes/s, 1058084.38 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
答え1
my.cnf または my.ini [mysqld] セクション (RPS = 1 秒あたりのレート) について考慮すべき提案
# 20180901 1346 from mysqlservertuning.com
# log_error=/var/lib/mysql/2cd908c24352-error.log # from default of stderr, for log
# max_connect_errors=10 # from 1000000 - why give a cracker/hacker more than 10 shots?
# tmp_table_size=320M # from 1G to stay under 1% RAM
# max_heap_table_size=320M # from 1G should be same as TTS
# thread_cache_size=100 # from 128 for CAP suggested in V8 refman to avoid OOM
# innodb_io_capacity=15000 # from 200 to allow more IOPS for your SSD
# read_buffer_size=256K # from 128K to reduce handler_read_next RPS
# read_rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS
# key_cache_age_threshold=3600 # from 300 seconds to delay AGE OUT and reduce key_reads RPS
# innodb_buffer_pool_instances=1 # from 8 to minimize impact of innodb_lru_scan_depth on CPU every second
# thread_concurrency=14 # from 10 to encourage multi processing when possible with your 8 cores
# min_examined_row_limit=1 # from 0 to reduce slow query log clutter
毎日、現在の my.cnf または my.ini を 20180827hhmm-my.cnf などの日付付きのファイル名で \history に保存し、最後に作業していた my.cnf または my.ini にすばやく戻ることができるようにします。
このブロック (先頭の日付と当社の Web サイト名を含む) を [mysqld] セクションの末尾にコピーし、先頭の # とスペース文字を削除して 1 日あたり 1 回の変更を有効にし、次の変更に進む前に監視します。
混乱を避けるために、先頭に # とスペース バーを付けて、以前の同じ名前の変数を無効にします。5 年後も、おおよその日付とともに my.cnf または my.ini の変更履歴が残ります。
通常、1 日あたり 1 つの変更のみが行われ、次の変更に移る前に監視されます。変更が有害と思われる場合は、最後に動作していた my.cnf または my.ini に戻って、お知らせください。