Mysql基础参数

 
  1. 引擎innodb的缓冲区大小
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 6442450944     |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 33554432       |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
 
  1. 查看mysql线程
[root@Slave-112 ~]# ps -ef |grep 3306
root       7071      1  0 Jun15 ?        00:00:00 /bin/sh /data/mysql/bin/mysqld_safe --datadir=/data/my3306/data/ --pid-file=/data/my3306/run/mysqld.pid
mysql      7872   7071  0 Jun15 ?        00:00:53 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/my3306/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/my3306/log/alert_3306.log --open-files-limit=65535 --pid-file=/data/my3306/run/mysqld.pid --socket=/data/my3306/run/mysql.sock --port=3306
root       9877   8439  0 00:10 pts/0    00:00:00 grep --color=auto 3306
[root@Slave-112 ~]# pstack 7872
Thread 31 (Thread 0x7fbd7fb91700 (LWP 8086)):
#0  0x00007fbd87cf340a in sigwaitinfo () from /lib64/libc.so.6
#1  0x0000000000f56eab in timer_notify_thread_func (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/mysys/posix_timers.c:77
#2  0x0000000000fc8f64 in pfs_spawn_thread (arg=0x25fd220) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#3  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#4  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 30 (Thread 0x7fbbe1c24700 (LWP 8104)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbe1c23dd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbe1c23dd0, m1=0x7fbbe1c23e88, m2=0x7fbbe1c23e80, request=0x7fbbe1c23e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbe1c23e50, m2=0x7fbbe1c23e80, m1=0x7fbbe1c23e88, global_segment=0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=0, m1=0x7fbbe1c23e88, m2=0x7fbbe1c23e80, request=0x7fbbe1c23e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 29 (Thread 0x7fbbe1423700 (LWP 8105)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbe1422dd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbe1422dd0, m1=0x7fbbe1422e88, m2=0x7fbbe1422e80, request=0x7fbbe1422e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbe1422e50, m2=0x7fbbe1422e80, m1=0x7fbbe1422e88, global_segment=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=1, m1=0x7fbbe1422e88, m2=0x7fbbe1422e80, request=0x7fbbe1422e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 28 (Thread 0x7fbbe0c22700 (LWP 8106)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbe0c21dd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbe0c21dd0, m1=0x7fbbe0c21e88, m2=0x7fbbe0c21e80, request=0x7fbbe0c21e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbe0c21e50, m2=0x7fbbe0c21e80, m1=0x7fbbe0c21e88, global_segment=2) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=2, m1=0x7fbbe0c21e88, m2=0x7fbbe0c21e80, request=0x7fbbe0c21e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=2) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 27 (Thread 0x7fbbe0421700 (LWP 8107)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbe0420dd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbe0420dd0, m1=0x7fbbe0420e88, m2=0x7fbbe0420e80, request=0x7fbbe0420e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbe0420e50, m2=0x7fbbe0420e80, m1=0x7fbbe0420e88, global_segment=3) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=3, m1=0x7fbbe0420e88, m2=0x7fbbe0420e80, request=0x7fbbe0420e50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=3) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 26 (Thread 0x7fbbdfc20700 (LWP 8108)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbdfc1fdd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbdfc1fdd0, m1=0x7fbbdfc1fe88, m2=0x7fbbdfc1fe80, request=0x7fbbdfc1fe50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbdfc1fe50, m2=0x7fbbdfc1fe80, m1=0x7fbbdfc1fe88, global_segment=4) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=4, m1=0x7fbbdfc1fe88, m2=0x7fbbdfc1fe80, request=0x7fbbdfc1fe50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=4) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 25 (Thread 0x7fbbdf41f700 (LWP 8109)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbdf41edd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbdf41edd0, m1=0x7fbbdf41ee88, m2=0x7fbbdf41ee80, request=0x7fbbdf41ee50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbdf41ee50, m2=0x7fbbdf41ee80, m1=0x7fbbdf41ee88, global_segment=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=5, m1=0x7fbbdf41ee88, m2=0x7fbbdf41ee80, request=0x7fbbdf41ee50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 24 (Thread 0x7fbbdec1e700 (LWP 8110)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbdec1ddd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbdec1ddd0, m1=0x7fbbdec1de88, m2=0x7fbbdec1de80, request=0x7fbbdec1de50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbdec1de50, m2=0x7fbbdec1de80, m1=0x7fbbdec1de88, global_segment=6) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=6, m1=0x7fbbdec1de88, m2=0x7fbbdec1de80, request=0x7fbbdec1de50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=6) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 23 (Thread 0x7fbbde41d700 (LWP 8111)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbde41cdd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbde41cdd0, m1=0x7fbbde41ce88, m2=0x7fbbde41ce80, request=0x7fbbde41ce50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbde41ce50, m2=0x7fbbde41ce80, m1=0x7fbbde41ce88, global_segment=7) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=7, m1=0x7fbbde41ce88, m2=0x7fbbde41ce80, request=0x7fbbde41ce50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=7) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 22 (Thread 0x7fbbddc1c700 (LWP 8112)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbddc1bdd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbddc1bdd0, m1=0x7fbbddc1be88, m2=0x7fbbddc1be80, request=0x7fbbddc1be50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbddc1be50, m2=0x7fbbddc1be80, m1=0x7fbbddc1be88, global_segment=8) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=8, m1=0x7fbbddc1be88, m2=0x7fbbddc1be80, request=0x7fbbddc1be50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=8) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 21 (Thread 0x7fbbdd41b700 (LWP 8113)):
#0  0x00007fbd890f7644 in __io_getevents_0_4 () from /lib64/libaio.so.1
#1  0x00000000010bc854 in LinuxAIOHandler::collect (this=0x7fbbdd41add0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2506
#2  0x00000000010bea94 in LinuxAIOHandler::poll (this=0x7fbbdd41add0, m1=0x7fbbdd41ae88, m2=0x7fbbdd41ae80, request=0x7fbbdd41ae50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2666
#3  0x00000000010bee4c in os_aio_linux_handler (request=0x7fbbdd41ae50, m2=0x7fbbdd41ae80, m1=0x7fbbdd41ae88, global_segment=9) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:2722
#4  os_aio_handler (segment=9, m1=0x7fbbdd41ae88, m2=0x7fbbdd41ae80, request=0x7fbbdd41ae50) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0file.cc:6268
#5  0x000000000125b78d in fil_aio_wait (segment=9) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fil/fil0fil.cc:5854
#6  0x0000000001160a30 in io_handler_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0start.cc:311
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 20 (Thread 0x7fbbdcc1a700 (LWP 8114)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbdcc199a0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0x26349d8, time_in_usec=<optimized out>, reset_sig_count=2) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x0000000001203623 in pc_sleep_if_needed (sig_count=2, next_loop_time=1592280643032) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0flu.cc:2690
#4  buf_flush_page_cleaner_coordinator (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0flu.cc:3191
#5  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#6  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 19 (Thread 0x7fbbdc419700 (LWP 8115)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0xcbcd928) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0xcbcd928, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x0000000001204ed7 in buf_flush_page_cleaner_worker (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0flu.cc:3496
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 18 (Thread 0x7fbbdbc18700 (LWP 8116)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0xcbcd928) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0xcbcd928, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x0000000001204ed7 in buf_flush_page_cleaner_worker (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0flu.cc:3496
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 17 (Thread 0x7fbbdb417700 (LWP 8117)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0xcbcd928) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0xcbcd928, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x0000000001204ed7 in buf_flush_page_cleaner_worker (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0flu.cc:3496
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 16 (Thread 0x7fbbea561700 (LWP 8132)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbea560db0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0xcbcbb28, time_in_usec=<optimized out>, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x000000000109a924 in lock_wait_timeout_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/lock/lock0wait.cc:501
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 15 (Thread 0x7fbbe9d60700 (LWP 8133)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbe9d5fac0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0x2634828, time_in_usec=<optimized out>, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x000000000115e8f5 in srv_error_monitor_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:1751
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 14 (Thread 0x7fbbe955f700 (LWP 8134)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbe955ee30) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0x26348b8, time_in_usec=<optimized out>, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x000000000115d985 in srv_monitor_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:1585
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 13 (Thread 0x7fbbe8d5e700 (LWP 8135)):
#0  0x00007fbd89307e3d in nanosleep () from /lib64/libpthread.so.0
#1  0x00000000010c3dc0 in os_thread_sleep (tm=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0thread.cc:279
#2  0x000000000115dfab in srv_master_sleep () at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2330
#3  srv_master_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2377
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 12 (Thread 0x7fbbe855d700 (LWP 8136)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x26345e8) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x26345e8, reset_sig_count=2) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x000000000115f98d in srv_purge_coordinator_suspend (rseg_history_len=2, slot=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2676
#4  srv_purge_coordinator_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2792
#5  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#6  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 11 (Thread 0x7fbbe7d5c700 (LWP 8137)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x2634708) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x2634708, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x000000000115d27e in srv_worker_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2520
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 10 (Thread 0x7fbbe755b700 (LWP 8138)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x2634678) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x2634678, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x000000000115d27e in srv_worker_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2520
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 9 (Thread 0x7fbbe6d5a700 (LWP 8139)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x2634798) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x2634798, reset_sig_count=5) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x000000000115d27e in srv_worker_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/srv/srv0srv.cc:2520
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 8 (Thread 0x7fbbead62700 (LWP 8140)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x2634948) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x2634948, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x00000000011fcecc in buf_dump_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0dump.cc:782
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 7 (Thread 0x7fbbe6559700 (LWP 8141)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbe6558dd0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0xcbd0c68, time_in_usec=<optimized out>, reset_sig_count=2) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x0000000001246297 in dict_stats_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/dict/dict0stats_bg.cc:428
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 6 (Thread 0x7fbbe5d58700 (LWP 8142)):
#0  0x00007fbd89304d12 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c380e in os_event::timed_wait (this=<optimized out>, abstime=0x7fbbe5d57ab0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:285
#2  0x00000000010c3b2e in os_event::wait_time_low (this=0xcc3d548, time_in_usec=<optimized out>, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:412
#3  0x00000000011af7f4 in ib_wqueue_timedwait (wq=0xcc3d418, wait_in_usecs=5000000) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/ut/ut0wqueue.cc:160
#4  0x000000000128f042 in fts_optimize_thread (arg=0xcc3d418) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/fts/fts0opt.cc:2900
#5  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#6  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 5 (Thread 0x7fbbe5557700 (LWP 8143)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x00000000010c365b in wait (this=0x2634a68) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:165
#2  os_event::wait_low (this=0x2634a68, reset_sig_count=1) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/os/os0event.cc:335
#3  0x00000000011f337c in buf_resize_thread (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/innobase/buf/buf0buf.cc:3019
#4  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#5  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 4 (Thread 0x7fbbe4a95700 (LWP 8147)):
#0  0x00007fbd89308361 in sigwait () from /lib64/libpthread.so.0
#1  0x00000000007c70bb in signal_hand (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/mysqld.cc:2125
#2  0x0000000000fc8f64 in pfs_spawn_thread (arg=0xcc45380) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#3  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#4  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 3 (Thread 0x7fbbd8d9d700 (LWP 8148)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x0000000000ec1e35 in native_cond_wait (mutex=<optimized out>, cond=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/thr_cond.h:140
#2  my_cond_wait (mp=<optimized out>, cond=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/thr_cond.h:195
#3  inline_mysql_cond_wait (src_line=879, src_file=0x15e7fa0 "/export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/rpl_gtid_persist.cc", mutex=<optimized out>, that=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/mysql/psi/mysql_thread.h:1184
#4  compress_gtid_table (p_thd=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/rpl_gtid_persist.cc:879
#5  0x0000000000fc8f64 in pfs_spawn_thread (arg=0xcd28030) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#6  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#7  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 2 (Thread 0x7fbbd859c700 (LWP 9732)):
#0  0x00007fbd89304965 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x0000000000defb38 in native_cond_wait (mutex=0x1e843a0 <Per_thread_connection_handler::LOCK_thread_cache>, cond=0x1e843e0 <Per_thread_connection_handler::COND_thread_cache>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/thr_cond.h:140
#2  my_cond_wait (mp=0x1e843a0 <Per_thread_connection_handler::LOCK_thread_cache>, cond=0x1e843e0 <Per_thread_connection_handler::COND_thread_cache>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/thr_cond.h:195
#3  inline_mysql_cond_wait (src_line=145, that=0x1e843e0 <Per_thread_connection_handler::COND_thread_cache>, mutex=<optimized out>, src_file=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/include/mysql/psi/mysql_thread.h:1184
#4  Per_thread_connection_handler::block_until_new_connection () at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:145
#5  0x0000000000defe3f in handle_connection (arg=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:337
#6  0x0000000000fc8f64 in pfs_spawn_thread (arg=0xcd28030) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#7  0x00007fbd89300dd5 in start_thread () from /lib64/libpthread.so.0
#8  0x00007fbd87db9ead in clone () from /lib64/libc.so.6
Thread 1 (Thread 0x7fbd89716780 (LWP 7872)):
#0  0x00007fbd87daf20d in poll () from /lib64/libc.so.6
#1  0x0000000000df1319 in Mysqld_socket_listener::listen_for_connection_event (this=0xcc44620) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/conn_handler/socket_connection.cc:852
#2  0x00000000007cc55d in connection_event_loop (this=0xcc2f4d0) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/conn_handler/connection_acceptor.h:66
#3  mysqld_main (argc=65, argv=0x24dc420) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql/mysqld.cc:5149
#4  0x00007fbd87cde3d5 in __libc_start_main () from /lib64/libc.so.6
#5  0x00000000007c2089 in _start ()
 
  1. 引擎innodb的重写               
 
mysql> show variables like "%write%";
+----------------------------------+----------------------+
| Variable_name                    | Value                |
+----------------------------------+----------------------+
| delay_key_write                  | ON                   |
| innodb_doublewrite               | ON                   |
| innodb_log_write_ahead_size      | 8192                 |
| innodb_write_io_threads          | 4                    |
| max_write_lock_count             | 18446744073709551615 |
| net_write_timeout                | 60                   |
| transaction_write_set_extraction | OFF                  |
+----------------------------------+----------------------+
 
  1. 引擎innodb的read_io线程
mysql> show variables like "%read%";
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 4                         |
| innodb_random_read_ahead                | OFF                       |
| innodb_read_ahead_threshold             | 56                        |
| innodb_read_io_threads                  | 4                         |
| innodb_read_only                        | OFF                       |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| net_read_timeout                        | 30                        |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | -1                        |
| pseudo_thread_id                        | 3                         |
| read_buffer_size                        | 131072                    |
| read_only                               | OFF                       |
| read_rnd_buffer_size                    | 262144                    |
| super_read_only                         | OFF                       |
| thread_cache_size                       | 50                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
| transaction_read_only                   | OFF                       |
| tx_read_only                            | OFF                       |
+-----------------------------------------+---------------------------+
 
 
  1. innodb_purge_threads
 
mysql> show variables like '%purge_thread%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
 
 
 
  1. innodb_purge_batch_size              
mysql> show variables like '%purge%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| gtid_purged                          |       |
| innodb_max_purge_lag                 | 0     |
| innodb_max_purge_lag_delay           | 0     |
| innodb_purge_batch_size              | 300   |
| innodb_purge_rseg_truncate_frequency | 128   |
| innodb_purge_threads                 | 4     |
| relay_log_purge                      | ON    |
+--------------------------------------+-------+
 
 
 
  1. innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_dump_now
mysql> show variables like '%dump%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | ON    |
| innodb_buffer_pool_dump_now         | OFF   |
| innodb_buffer_pool_dump_pct         | 25    |
+-------------------------------------+-------+
 
[root@Slave-112 ~]# service mysql stop
Shutting down MySQL.. SUCCESS!
 
[root@Slave-112 ~]# service mysql start
Starting MySQL........ SUCCESS!
 
 
[root@Slave-112 ~]# cat /data/my3306/log/iblog/ib_buffer_pool
16,2
16,1
16,3
119,68
119,67
119,66
119,65
119,64
112,3
112,2
112,1
112,0
104,3
104,2
 
第一列数据 即 space_id
第二列数据 即 page_no
 
 
  1. 查看错误日志名及路径       
mysql> show variables like '%error%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER                    |
| error_count         | 0                               |
| log_error           | /data/my3306/log/alert_3306.log |
| log_error_verbosity | 3                               |
| max_connect_errors  | 100                             |
| max_error_count     | 64                              |
| slave_skip_errors   | OFF                             |
+---------------------+---------------------------------+
 
  1. 查看慢查询日志
mysql> show variables like '%slow%';
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| log_slow_admin_statements | OFF                             |
| log_slow_slave_statements | OFF                             |
| slow_launch_time          | 2                               |
| slow_query_log            | ON                              |
| slow_query_log_file       | /data/my3306/log/mysql-slow.log |
+---------------------------+---------------------------------+
 
 
  1. 长查询的阈值long_query_time
mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
 
9和10 两个参数组合使用 
 
 
  1. 查看通用日志
mysql> show variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /data/my3306/data/Slave-112.log |
+------------------+---------------------------------+
 
 
 
  1. 查看连接数
mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 500             |
| max_user_connections     | 8000            |
+--------------------------+-----------------+
 
  1.  
mysql> show variables like '%query%';
+------------------------------+---------------------------------+
| Variable_name                | Value                           |
+------------------------------+---------------------------------+
| binlog_rows_query_log_events | OFF                             |
| ft_query_expansion_limit     | 20                              |
| have_query_cache             | YES                             |
| long_query_time              | 10.000000                       |
| query_alloc_block_size       | 8192                            |
| query_cache_limit            | 1048576                         |
| query_cache_min_res_unit     | 4096                            |
| query_cache_size             | 0                               |
| query_cache_type             | OFF                             |
| query_cache_wlock_invalidate | OFF                             |
| query_prealloc_size          | 8192                            |
| slow_query_log               | ON                              |
| slow_query_log_file          | /data/my3306/log/mysql-slow.log |
+------------------------------+---------------------------------+
 
连接/会话
mysql> show variables like '%binlog_cache_size%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
 
mysql> show variables like '%read_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)
 
mysql> show variables like '%read_rnd_buffer_size%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
 
 
 
mysql> show variables like '%join_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
 
mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
 
mysql> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
 
 
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 50    |
+-------------------+-------+
 
mysql> show variables like 'bulk_insert_buffer_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
+-------------------------+---------+
 
mysql> show variables like 'thread_stack';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| thread_stack  | 262144 |
+---------------+--------+
 
 
服务器/共享
mysql> show variables like 'query_cache';
Empty set (0.00 sec)
 
mysql> show variables like 'table_cache';
Empty set (0.01 sec)
 
mysql> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 1024  |
+------------------------+-------+
 
存储引擎/共享
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 6442450944 |
+-------------------------+------------+
1 row in set (0.00 sec)
 
mysql> show variables like 'innodb_additional_mem_pool_size';
Empty set (0.00 sec)
 
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
 
 
 
 
 
 
创建时间：2021-12-22 10:58
넶浏览量：0
ꄴ前一个： 无
ꄲ后一个： 无