-- db1.t有200GB
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
net_buffer
中,默认为 16K ,控制参数为 net_buffer_length
net_buffer
,然后调用网络接口发出去net_buffer
,然后继续取下一行并写入 net_buffer
EAGAIN
或者 WSAEWOULDBLOCK
,表示本地网络栈 socket send buffer
写满net_buffer_length
,因此不会达到200Gsocket send buffer
也不可能达到200G,如果 socket send buffer
被写满,就会暂停读取数据-- 16384 Bytes = 16 KB
mysql> SHOW VARIABLES LIKE '%net_buffer_length%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
socket receive buffer
中的内容的场景Sending to client
,表示服务端的网络栈写满了mysql --quick
,会使用 mysql_use_result
方法,该方法会 读取一行处理一行
Sending to client mysql_store_result net_buffer_lengthSending data
Sending data
不等同于 正在发送数据CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t VALUES (1,1);
session A | session B |
---|---|
BEGIN; | |
SELECT * FROM t WHERE id=1 FOR UPDATE; | |
SELECT * FROM t LOCK IN SHARE MODE; (Blocked) |
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 713722 | Waiting on empty queue | NULL |
| 37 | root | localhost | test | Sleep | 35 | | NULL |
| 38 | root | localhost | test | Query | 15 | Sending data | SELECT * FROM t LOCK IN SHARE MODE |
| 39 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+
Buffer Pool
中管理的SHOW ENGINE INNODB STATUS
中的 Buffer pool hit rate 990 / 1000
,表示命中率为99%Buffer Pool
的大小由参数 innodb_buffer_pool_size
控制,一般设置为物理内存的 60%~80%
Buffer Pool
一般都会小于磁盘的数据量,InnoDB将采用 LRU
算法来淘汰数据页-- 134217728 Bytes = 128 MB
mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
Buffer Pool
中新申请一个数据页Px,加到链表头部
Buffer Pool5:3
的比例把整个LRU链表分成 young
区和 old
区LRU_old
指向old区的第一个位置,即靠近链表头部的 5/8
是 young
区,靠近链表尾部的 3/8
是 old
区LRU_old
innodb_old_blocks_time
控制old
区old
区young
区, 最终很快被淘汰-- 1000ms = 1s
mysql> SHOW VARIABLES LIKE '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000 |
+------------------------+-------+
mysql> SHOW ENGINE INNODB STATUS\G;
----------------------
BUFFER POOL AND MEMORY
----------------------
-- 137428992 Bytes = 131.0625 MB
Total large memory allocated 137428992
Dictionary memory allocated 432277
-- innodb_buffer_pool_size = 134217728 / 16 / 1024 / 1024 = 8192
-- 6957 + 1223 = 8180 ≈ Buffer pool size
Buffer pool size 8191
Free buffers 6957
Database pages 1223
-- 1223 * 3 / 8 = 458.625 ≈ Old database pages
Old database pages 465
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
-- made young : old -> young
-- not young : young -> old
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1060, created 163, written 666
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1223, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
《MySQL实战45讲》