主页 / dba / 问题 / 282498
Accepted
mbouzahir
Asked: 2021-01-04 13:46:24 +0800 CST

MySQL 挂起 SELECT 来自带有 blob 字段的表

  • 772

我设法创建了一个具有三个节点的 mysql 8 InnoDB 集群（一台专用服务器中的 VPS，每个 VPS 中的 25GB 内存，服务器中的总 RAM 96GB）和 MySQL 路由器 8 一切正常，但是当我尝试从下表从另一台服务器冻结，但当我将结果限制为 26 以下的数字时它可以工作

SET NAMES utf8;
SET time_zone = '+00:00';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `v7_addad`;
CREATE TABLE `v7_addad` (
  `id` int NOT NULL AUTO_INCREMENT,
  `body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `page_id` int NOT NULL,
  `done` tinyint NOT NULL DEFAULT '0',
  `book` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

例如 ：

select * from v7_addad limit 25; # this works
select * from v7_addad limit 26; # this freezes

顺便说一句，当 MySQL 路由器与应用程序位于同一服务器时，它不会冻结。

只想添加我从本地主机测试的这个，即使没有限制我也可以得到结果。所以可能是配置或网络问题。但我不知道如何解决这个问题

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

key_buffer_size         = 5G
table_open_cache               = 3072
table_definition_cache         = 4096
innodb_buffer_pool_size        = 15G
innodb_buffer_pool_instances = 2
innodb_log_file_size         = 2047M
max_allowed_packet           = 64M

'SHOW PROCESSLIST' 当“冻结”发生时 mysql> SHOW PROCESSLIST;

| Id     | User                       | Host                         | db     | Command | Time   | State                                                  | Info                             |
|      6 | event_scheduler            | localhost                    | NULL   | Daemon  | 285970 | Waiting on empty queue                                 | NULL                             |
|     10 | system user                |                              | NULL   | Connect | 285970 | waiting for handler commit                             | Group replication applier module |
|     13 | system user                |                              | NULL   | Query   | 285970 | Slave has read all relay log; waiting for more updates | NULL                             |
| 619140 | micadmin                   | LinuxSQL01.**.***.**.*:51896 | db0003 | Sleep   |    535 |                                                        | NULL                             |
| 620298 | micadmin                   | LinuxSQL01.**.***.**.*:54218 | db0003 | Sleep   |      3 |                                                        | NULL                             |
| 620304 | mysql_router1_g2wcum04fipm | LinuxSQL01.**.***.**.*:54230 | NULL   | Sleep   |      0 |                                                        | NULL                             |
| 620305 | micadmin                   | LinuxSQL01.**.***.**.*:54234 | NULL   | Query   |      0 | init                                                   | SHOW PROCESSLIST                 |

'SHOW ENGINE INNODB STATUS' 当“冻结”发生时

mysql > 显示引擎 INNODB 状态；

| Type   | Name | Status
| InnoDB |      | 
=====================================
2021-01-03 23:44:29 0x7f85843b4700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 59377 srv_active, 0 srv_shutdown, 226562 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 642
OS WAIT ARRAY INFO: signal count 972
RW-shared spins 449, rounds 449, OS waits 0
RW-excl spins 227, rounds 6734, OS waits 207
RW-sx spins 1, rounds 1, OS waits 0
Spin rounds per wait: 1.00 RW-shared, 29.67 RW-excl, 1.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 298328
Purge done for trx's n:o < 298327 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421696252935760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252933192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252934904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252934048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252932336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252931480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252930624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421696252929768, 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: 18446744073709551516
5244 OS file reads, 1279100 OS file writes, 997056 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.69 writes/s, 3.23 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 3984439, node heap has 2 buffer(s)
Hash table size 3984439, node heap has 1 buffer(s)
Hash table size 3984439, node heap has 1 buffer(s)
Hash table size 3984439, node heap has 2 buffer(s)
Hash table size 3984439, node heap has 1 buffer(s)
Hash table size 3984439, node heap has 3 buffer(s)
Hash table size 3984439, node heap has 4 buffer(s)
Hash table size 3984439, node heap has 7 buffer(s)
0.23 hash searches/s, 1.62 non-hash searches/s
---
LOG
---
Log sequence number          2270625140
Log buffer assigned up to    2270625140
Log buffer completed up to   2270625140
Log written up to            2270625140
Log flushed up to            2270625140
Added dirty pages up to      2270625140
Pages flushed up to          2270624443
Last checkpoint at           2270624443
413027 log i/o's done, 1.36 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 16483614720
Dictionary memory allocated 1956558
Buffer pool size   983040
Free buffers       977582
Database pages     5437
Old database pages 2006
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 209, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5172, created 268, written 603288
0.00 reads/s, 0.00 creates/s, 0.00 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: 5437, unzip_LRU len: 0
I/O sum[258]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   491520
Free buffers       488755
Database pages     2754
Old database pages 996
Modified db pages  1
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 209, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2596, created 161, written 384262
0.00 reads/s, 0.00 creates/s, 0.00 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: 2754, unzip_LRU len: 0
I/O sum[129]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   491520
Free buffers       488827
Database pages     2683
Old database pages 1010
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2576, created 107, written 219026
0.00 reads/s, 0.00 creates/s, 0.00 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: 2683, unzip_LRU len: 0
I/O sum[129]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3803, Main thread ID=140203552966400 , state=sleeping
Number of rows inserted 9, updated 56291, deleted 0, read 7084971
0.00 inserts/s, 0.23 updates/s, 0.00 deletes/s, 21.69 reads/s
Number of system rows inserted 56323, updated 1572, deleted 56302, read 128455
0.23 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql debian

2 个回答

  1. Best Answer

    问题是因为 MySQL 路由器，我找不到问题的解决方案，但我找到了一个很好的替代方案，称为 proxysql。MySQL路由器的版本是8.0.22

    • 0

  2. 您能否分享您用于路由器和服务器的 MySQL 版本？

    我对 MySQL 服务器 v8.0.22 和路由器 v8.0.22 有同样的问题。所以，我只是尝试将路由器降级到 v8.0.21，一切正常。

    MySQL 8.0.22 路由器中似乎存在一些错误。

    • 0

相关问题