Низкая производительность mysql из-за блокировки

У меня Mysql5.5 в системе Windows. С последнего месяца он работает медленно.

Вот запросы, которые выполняются чаще всего:

   begin
     update  roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1
      where  roomlogs.netId = pnetid
        and  roomlogs.`status` = 0
        and  roomlogs.enterTime>=DATE(ptime)
        and  roomlogs.enterTime<DATE(ptime) + interval 1 day;
     INSERT
       into  roomlogs (roomlogs.roomId, roomlogs.netId, roomlogs.enterTime,
                       roomlogs.exitTime, roomlogs.hidden, roomlogs.checkinId)
            value
                (pRoomId, pnetid,ptime, pexit, phidden, pcheckid);
     select  *
       from  roomlogs
       where  roomlogs.roomlogId = LAST_INSERT_ID();
   end

А ТАКЖЕ:

begin
    select  count(*) as total
        from  roomlogs
        where  roomId = proomId
          and  roomlogs.`status` = 0
          AND  roomlogs.enterTime >= CURDATE()
          AND  roomlogs.enterTime  < CURDATE() + INTERVAL 1 DAY
          and  roomlogs.hidden!=1;
end

Здесь индекс комнатных журналов первичный (roomlogId), NETID (netid, status, enterTime), ROOMID (roomID, status, enterTime)

Вот мой статус InnoDB, когда MySQL имеет большее количество запросов (около 100), выполняемых одновременно на сервере.

 =====================================
 2018-10-25 17:42:01 1728 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 20 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 148860 srv_active, 0 srv_shutdown, 271514 srv_idle
 srv_master_thread log flush and writes: 420374
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 80056
 OS WAIT ARRAY INFO: signal count 80039
 Mutex spin waits 6920930, rounds 7674964, OS waits 52940
 RW-shared spins 27042, rounds 803211, OS waits 26514
 RW-excl spins 1173, rounds 9109, OS waits 211
 Spin rounds per wait: 1.11 mutex, 29.70 RW-shared, 7.77 RW-excl
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 78706084
 Purge done for trx's n:o < 78706079 undo n:o < 0 state: running but idle
 History list length 2281
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 78705887, not started
 MySQL thread id 3000, OS thread handle 0x8e0, query id 4915274 localhost 127.0.0.1 root cleaning up
**------------ SKIPPING SOME DATA HERE--------------**
 ---TRANSACTION 78701492, not started
 MySQL thread id 15, OS thread handle 0x12c4, query id 4889345 localhost 127.0.0.1 root cleaning up
 ---TRANSACTION 78697207, not started
 MySQL thread id 16, OS thread handle 0x408, query id 4889123 localhost 127.0.0.1 root cleaning up
 ---TRANSACTION 78701491, not started
 MySQL thread id 14, OS thread handle 0x11c8, query id 4889320 localhost 127.0.0.1 root cleaning up
 ---TRANSACTION 78697202, not started
 MySQL thread id 13, OS thread handle 0x10dc, query id 4889098 localhost 127.0.0.1 root cleaning up
 ---TRANSACTION 78706073, ACTIVE 1 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2989, OS thread handle 0x157c, query id 4916082 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706073 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78706056, ACTIVE 4 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2942, OS thread handle 0x15e0, query id 4916017 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706056 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78706045, ACTIVE 5 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2976, OS thread handle 0x125c, query id 4915976 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706045 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78706009, ACTIVE 9 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2946, OS thread handle 0xb98, query id 4915824 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706009 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78706004, ACTIVE 10 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2995, OS thread handle 0x1234, query id 4915806 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706004 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78705997, ACTIVE 12 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2948, OS thread handle 0x1708, query id 4915783 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705997 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78705985, ACTIVE 14 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
 MySQL thread id 2986, OS thread handle 0x1640, query id 4915747 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 ------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705985 lock_mode X waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
  0: len 4; hex 80000011; asc     ;;
  1: len 6; hex 0000028a4fc7; asc     O ;;
  2: len 7; hex c3000001ed0110; asc        ;;
  3: len 7; hex 726f6f6d313038; asc room108;;
  4: len 7; hex 7973756e313032; asc ysun102;;
  5: len 5; hex 999d1f25db; asc    % ;;
  6: len 5; hex 999d1d2a13; asc    * ;;
  7: len 4; hex 80000001; asc     ;;
  8: len 4; hex 80000000; asc     ;;
  9: len 4; hex 7fffffff; asc     ;;

 ------------------
 ---TRANSACTION 78705977, ACTIVE 16 sec fetching rows
 mysql tables in use 1, locked 1
 4610 lock struct(s), heap size 539064, 1001945 row lock(s)
 MySQL thread id 2938, OS thread handle 0x10bc, query id 4915725 localhost 127.0.0.1 root updating
 update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1  where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
       roomlogs.enterTime>=DATE(ptime) and 
       roomlogs.enterTime<DATE(ptime) + interval 1 day
 --------
 FILE I/O
 --------
 I/O thread 0 state: wait Windows aio (insert buffer thread)
 I/O thread 1 state: wait Windows aio (log thread)
 I/O thread 2 state: wait Windows aio (read thread)
 I/O thread 3 state: wait Windows aio (read thread)
 I/O thread 4 state: wait Windows aio (read thread)
 I/O thread 5 state: wait Windows aio (read thread)
 I/O thread 6 state: wait Windows aio (write thread)
 I/O thread 7 state: wait Windows aio (write thread)
 I/O thread 8 state: wait Windows aio (write thread)
 I/O thread 9 state: wait Windows aio (write thread)
 Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 23467 OS file reads, 931766 OS file writes, 524775 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf: size 1, free list len 39, seg size 41, 61 merges
 merged operations:
  insert 57, delete mark 5, delete 0
 discarded operations:
  insert 0, delete mark 0, delete 0
 Hash table size 4425293, node heap has 2003 buffer(s)
 30.00 hash searches/s, 19.95 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 7396884835
 Log flushed up to   7396884835
 Pages flushed up to 7396717673
 Last checkpoint at  7396717673
 0 pending log writes, 0 pending chkp writes
 164342 log i/o's done, 1.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total memory allocated 2197815296; in additional pool allocated 0
 Dictionary memory allocated 2793588
 Buffer pool size   131072
 Free buffers       101935
 Database pages     27102
 Old database pages 9967
 Modified db pages  377
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 498, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 22500, created 4602, written 613885
 0.00 reads/s, 0.10 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: 27102, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ----------------------
 INDIVIDUAL BUFFER POOL INFO
 ----------------------
 ---BUFFER POOL 0
 Buffer pool size   16384
 Free buffers       12818
 Database pages     3312
 Old database pages 1240
 Modified db pages  63
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 72, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2713, created 599, written 81358
 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: 3312, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 1
 Buffer pool size   16384
 Free buffers       12809
 Database pages     3319
 Old database pages 1215
 Modified db pages  27
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 15, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2770, created 549, written 78718
 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: 3319, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 2
 Buffer pool size   16384
 Free buffers       12717
 Database pages     3411
 Old database pages 1256
 Modified db pages  28
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 77, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2795, created 616, written 49290
 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: 3411, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 3
 Buffer pool size   16384
 Free buffers       12733
 Database pages     3395
 Old database pages 1246
 Modified db pages  51
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 45, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2780, created 615, written 91680
 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: 3395, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 4
 Buffer pool size   16384
 Free buffers       12812
 Database pages     3318
 Old database pages 1217
 Modified db pages  47
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 108, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2645, created 673, written 95064
 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: 3318, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 5
 Buffer pool size   16384
 Free buffers       12590
 Database pages     3538
 Old database pages 1294
 Modified db pages  51
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 35, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2905, created 633, written 89554
 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: 3538, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 6
 Buffer pool size   16384
 Free buffers       12563
 Database pages     3567
 Old database pages 1307
 Modified db pages  52
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 32, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 3127, created 440, written 56311
 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: 3567, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 ---BUFFER POOL 7
 Buffer pool size   16384
 Free buffers       12893
 Database pages     3242
 Old database pages 1192
 Modified db pages  58
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 114, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 2765, created 477, written 71910
 0.00 reads/s, 0.10 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: 3242, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 1 read views open inside InnoDB
 Main thread id 5080, state: sleeping
 Number of rows inserted 97780, updated 862074, deleted 219, read 468159566176
 1.55 inserts/s, 1.20 updates/s, 0.00 deletes/s, 3363648.47 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

Итак, здесь вы можете видеть, что транзакция ожидала блокировки записи. Также последняя транзакция с идентификатором 78705977 блокирует 1001945 строк, что должно быть, поскольку она не обновляет столько строк. Также, почему для получения строк типа 16 секунд требуется время?

Вот таблица журналов комнат:

CREATE TABLE `roomlogs` (
   `roomId` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
   `netId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
   `enterTime` datetime DEFAULT NULL,
   `exitTime` datetime DEFAULT NULL,
   `roomlogId` int(11) NOT NULL AUTO_INCREMENT,
   `status` int(11) DEFAULT '0',
   `hidden` int(11) DEFAULT '0',
   `checkinId` int(11) DEFAULT '-1',
   PRIMARY KEY (`roomlogId`),
   KEY `NETID` (`netId`,`status`,`enterTime`),
   KEY `RoomLogIndex` (`roomId`,`status`,`enterTime`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1315726 DEFAULT CHARSET=utf8

Слишком много трассировки стека и слишком мало запросов. Я не думаю, что, не видя своего кода SQL, вы получите здесь содержательный ответ.

Tim Biegeleisen 26.10.2018 06:28

Здесь я уменьшил дерево стека и предоставил SQL-запросы.

Nilesh 26.10.2018 06:34

почему так много потоков параллельно меняют журналы с одним и тем же нетидом?

aschoerk 26.10.2018 07:09

Это делается с помощью autocommit = ON? Если нет, то когда придет COMMIT?

Rick James 26.10.2018 18:17

Пожалуйста, предоставьте SHOW INDEXES FROM roomlogs; - я хочу лучше понять netId. Когда update работает, сколько строк он меняет (обычно)?

Rick James 26.10.2018 18:17
0
5
2 363
4

Ответы 4

Обычно конфликт не должен происходить из-за атрибута status, который обновляется одновременно. Но из-за MVCC разные транзакции не распознают, что записи больше не должны быть изменены. Поэтому все они видят одни и те же записи и блокируют их, даже если им не нужно этого делать из-за уже измененного статуса.

Подозреваю, что для вас важно, чтобы было сделано единственное изменение.

В настоящий момент транзакция A (например) блокирует все записи, поскольку они кажутся измененными, в соответствии с состоянием начала данных транзакции.

Как только все записи могут быть заблокированы A, потому что все транзакции, запущенные параллельно в начале, были зафиксированы, транзакция A блокирует и извлекает эти записи, обнаруживает, что статус уже был изменен, и вносит изменения в оставшиеся.

После этого изменения первоначально заблокированный набор записей разблокируется.

Остатков может быть не так много, но споры велики.

Решение, которое я вижу: Кажется, есть много параллельных потоков, меняющих записи одним и тем же netid. Можно ли этого избежать?

в противном случае я никогда не пробовал этого, но, возможно, может помочь изменение уровня изоляции: См .: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Любое решение для этого?

Nilesh 26.10.2018 07:11

Я только что добавил дополнительный вопрос

aschoerk 26.10.2018 07:11

См .: stackoverflow.com/questions/51852498/…

aschoerk 26.10.2018 07:31

Привет, @aschoerk, Спасибо за ответ. Здесь Status будет иметь значение по умолчанию 0, и этот запрос изменит его на 1. Статус никогда не меняется с 1 на 0. Таким образом, в этой транзакции A не будет иметь какой-либо оставшейся записи, которую изменил другой запрос, потому что он выбирает со статусом. Кроме того, есть ли у вас причина, по которой последняя транзакция с идентификатором 78705977 блокирует 1001945 строк? Поскольку обычно количество записей, обновляемых любым запросом, составляет около 10-15.

Nilesh 26.10.2018 08:45

Не знаю, что еще делала эта транзакция. Вызывает недоумение то, что некоторые транзакции ждут более 10 секунд, чтобы изменить одну запись. Только ли это заявление вызывает разногласия, или есть ли другие изменения во время этих транзакций?

aschoerk 26.10.2018 08:57

Я обновил информацию о вызове хранимой процедуры и индексе, которая может оказаться полезной.

Nilesh 26.10.2018 09:10

Вы также очень часто меняете индекс, что также может привести к разногласиям. Вас интересуют только записи со статусом 0, либо не индексируйте записи со статусом 0 (не уверен, возможно ли это с mysql), либо удалите статус из индекса. Я бы предпочел последнее. Сколько записей создает нетид в день?

aschoerk 26.10.2018 10:46

Позвольте нам продолжить обсуждение в чате.

Nilesh 26.10.2018 19:49

Я (пока) не понимаю, почему update спотыкается сам о себе. Но я вижу некоторые вещи, которые могут ускорить работу Другие, тем самым косвенно помогая:

Пункт 1, более быстрый выбор

 select  *
   from  roomlogs
   where  roomlogs.roomlogId = LAST_INSERT_ID();

Можно (возможно) заменить на

 SELECT LAST_INSERT_ID(),
        pRoomId, pnetid, ptime, pexit, phidden, pcheckid;

Внимание: я предполагаю, что это единственные столбцы и порядок правильный. Пожалуйста, предоставьте SHOW CREATE TABLE roomlogs, чтобы нам не приходилось гадать.

Пункт 2, охватывающий указатель

INDEX (roomID, status, enterTime)

->

INDEX (roomID, status, enterTime, hidden)

Это будет «индекс покрытия», ускоряющий SELECT COUNT(*) ....

Пункт 3, лучшая оптимизация

Если hidden имеет только значения 0 и 1, то

hidden != 1

->

hidden = 0

Оптимизатор может обрабатывать = лучше, чем !=. На этом этапе это еще лучший индекс для пункта 2:

INDEX (roomID, status, hidden, enterTime)

Примечание: я не рекомендовал это в пункте 2 из-за !=.

Элемент 4, тип данных:

Если status не является какой-то формой INT, тогда у меня будет ответ. Опять же нужен SHOW CREATE TABLE roomlogs

Я обновил таблицу создания. Кроме того, поскольку статус - это индекс, и мы его обновляем, заставляет ли запросы обновления ждать друг друга?

Nilesh 26.10.2018 19:46

@Nilesh - Правильный вопрос. Я не знаю, что блокируется в такой ситуации.

Rick James 26.10.2018 19:57

Есть ли способ узнать? Кроме того, у меня нет логического обоснования того, что для последней транзакции с ID 78705977 блокировка 1001945 строк также занимает 16 секунд для выборки? Поскольку обычно записи, которые будут обновляться при любом запросе, будут около 10-15.

Nilesh 26.10.2018 21:02

@Nilesh - Сколько строк задействовано в UPDATE? Похоже на тысячи ??

Rick James 26.10.2018 21:07

После оценки условия where в обновлении оно должно быть около 10-15. P.S .: Мой текущий уровень изоляции - ПОВТОРНОЕ ЧТЕНИЕ.

Nilesh 26.10.2018 21:11

Что-то попробовать:

begin
    START TRANSACTION;
        SELECT *
            FROM roomlogs
            WHERE  netId = pnetid
              AND  `status` = 0
              AND  enterTime >= DATE(ptime)
              AND  enterTime  < DATE(ptime) + interval 1 day;
            FOR UPDATE;    -- This might cause different locking action
            -- WHERE clause is equivalent to UPDATE's
        -- The following are as before (or as further optimized):
        UPDATE ...
        INSERT ...
        SELECT ...
    COMMIT;
end;

Возможная причина: в более старых версиях MySQL в UPDATE и DELETE использовался другой, менее сложный оптимизатор. Следовательно, обновление мог бы будет решением.

Проверить на наличие ошибок. Вы можете получить тайм-аут или тупик. Либо нужно обрабатывать.

Здесь в хранимой процедуре я транзакцию не использовал. тогда я должен включить транзакцию сейчас? вы можете увидеть мою хранимую процедуру в вопросе. от начала до конца блока!

Nilesh 26.10.2018 22:05

@Nilesh - Я надеялся, что вы ответите на мой вопрос о autocommit. Если это ON, а у вас нет START TRANSACTION, то каждый оператор представляет собой транзакцию сама по себе. Некоторые программы успешно работают в этом режиме. Однако я считать вашему приложению нужны явные транзакции с несколькими состояниями. Да, это то, что я предлагаю в этом ответе (наряду с некоторыми другими отличиями). В частности, FOR UPDATE не оказывает никакого влияния, если он не находится внутри транзакции.

Rick James 27.10.2018 00:40

В настоящее время моя автоматическая фиксация отключена.

Nilesh 27.10.2018 01:55

@Nilesh - Упс. Это означает, что вам рано или поздно понадобится сделать COMMIT. Я предпочитаю никогда не использовать OFF - опасаюсь, что забуду COMMIT. Вместо этого я использую BEGIN (START) как напоминание о том, что мне нужно сделать COMMIT.

Rick James 27.10.2018 02:34

(Да, я знаю, что это мой третий ответ. Но он в другом направлении.)

Подумайте о том, чтобы убрать status со стола. Это тем более, что необходимо изменить несколько строк. Также, поскольку у вас есть datetime, но статус связан с датой.

В новом столе будет

roomid ...
status TINYINT UNSIGNED NOT NULL (default 0?)
date  DATE NOT NULL   -- comes from  DATE(enterTime)
PRIMARY KEY(roomid)

Вы бы JOIN к столу, когда вам нужно увидеть статус.

Вы должны обновить строку один, когда вам нужно изменить статус. И совсем не обязательно прикасаться к другому столу.

Эта идея, вероятно, потерпит неудачу, если вам нужно, чтобы некоторые строки имели статус = 0, а некоторые - статус = 1 одновременно. Однако, если только «более старые» строки имеют = 0, то добавление времени в таблицу будет означать, что «статус равен 0 перед time». Это сделало бы JOIN немного более сложным, но все же выполнимым.

Расскажите, пожалуйста, что "означает" status. И подумайте об изменении имени столбца, чтобы отразить семантику.

(1) Значение статуса по умолчанию - 0, и любой запрос обновит его с 0 до 1. Никакой запрос не обновит его с 1 до 0. (2) Статус будет зависеть от netId, roomId и времени входа.

Nilesh 26.10.2018 21:22

Кроме того, влияет ли уровень изоляции REPEATABLE READ на систему запирания? Следует ли мне изменить это на READ COMMITED?

Nilesh 26.10.2018 21:23

@Nilesh - Может быть. Это мая зависит от того, сохраняете ли вы «транзакцию» так, как она была у вас, или как я предложил в другом моем ответе.

Rick James 26.10.2018 21:35

Возможно stackoverflow.com/questions/52923678/… вопрос ясно показал эффекты read_committed против REPEATABLE_READ

aschoerk 26.10.2018 23:22

Помогает ли изменение уровня изоляции предотвратить блокировки запросами на обновление? Согласно моему исследованию, из-за заявления об обновлении это может быть блокировка следующего ключа. dev.mysql.com/doc/refman/8.0/en/… и dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Nilesh 26.10.2018 23:34

@aschoerk - Мне особенно нравится этот Вопрос. Однако неясно, относится ли это к вопросу это.

Rick James 27.10.2018 00:47

Другие вопросы по теме