Mysql
MySQL 掛起 SELECT 來自帶有 blob 欄位的表
我設法創建了一個具有三個節點的 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 路由器，我找不到問題的解決方案，但我找到了一個很好的替代方案，稱為 proxysql。MySQL路由器的版本是8.0.22
您能否分享您用於路由器和伺服器的 MySQL 版本？
我對 MySQL 伺服器 v8.0.22 和路由器 v8.0.22 有同樣的問題。所以，我只是嘗試將路由器降級到 v8.0.21，一切正常。
MySQL 8.0.22 路由器中似乎存在一些錯誤。