Wordpress UPDATE queries on MySQL database stuck

I have an **Amazon 24XL server** - 96 Cores - 378 GB RAM - Database size 5.7G - Debian GNU/Linux 9 (stretch) - PHP 7.3.16 - mysql Ver 15.1 Distrib 10.3.22-MariaDB I have only one WordPress site where users read articles, there are 4-5 small plugins One plugins is used to add the points and rewards in my subscribers profile. When there are around 2500 Users on my site there are around 3000+ UPDATE queries that runs to update the table 'wp_custom_points_user' The issue is that the queries stuck in updating I can't find the way to fix those things However server has much RAM , CPU available but due to queries on the same table it stuck and caused 502 on my site. I am looking to optimize MySQL to cater concurrent update queries as they are taking longer to respond and I suspect there are some locks. However I have all tables with indexing and using InnoDB Here is the SHOW ENGINE INNODB STATUS; ===================================== 2020-04-06 13:45:46 0x7f934bf24700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 27 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 8196 srv_active, 0 srv_shutdown, 0 srv_idle srv_master_thread log flush and writes: 8196 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15589494 OS WAIT ARRAY INFO: signal count 41500023 RW-shared spins 0, rounds 154525943, OS waits 1459106 RW-excl spins 0, rounds 10328037, OS waits 40587 RW-sx spins 71982, rounds 547220, OS waits 3916 Spin rounds per wait: 154525943.00 RW-shared, 10328037.00 RW-excl, 7.60 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 351880322 Purge done for trx's n:o < 351880319 undo n:o < 0 state: running History list length 1 ... truncated... mpact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 4; hex 80004cd6; asc L ;; 4: len 4; hex 800186b0; asc ;; ------------------ ---TRANSACTION 351861705, ACTIVE 40 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 421774, OS thread handle 140270896170752, query id 8187691 localhost wpdatabayf Updating UPDATE wp_custom_points_user SET total_points = '18900' WHERE user_id = 188768 ------- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1468 page no 5 n bits 568 index PRIMARY of table wpdatabayf.wp_custom_points_user trx id 351861705 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 4; hex 80004cd6; asc L ;; 4: len 4; hex 800186b0; asc ;; ---BUFFER POOL 1 . . . . ---BUFFER POOL 31 Buffer pool size 8192 Free buffers 1725 Database pages 6096 Old database pages 2253 Modified db pages 183 Percent of dirty pages(LRU & free pages): 2.340 Max dirty pages percent: 75.000 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 5594, created 502, written 12349 0.00 reads/s, 0.00 creates/s, 0.74 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: 6096, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Process ID=72108, Main thread ID=140323093444352, state: sleeping Number of rows inserted 88380, updated 193228, deleted 34011, read 84305568368 11.44 inserts/s, 21.74 updates/s, 0.00 deletes/s, 12469107.55 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ UPDATE ====== SHOW CREATE TABLE wp_custom_points_user; +----------------------------------+------------------------------------------------------+ | Table | Create Table | +----------------------------------+------------------------------------------------------+ | wp_custom_points_user | CREATE TABLE wp_custom_points_user ( id int(11) NOT NULL AUTO_INCREMENT, total_points int(11) NOT NULL, user_id int(11) NOT NULL, PRIMARY KEY (id), UNIQUE KEY ixd_uc_tzs_wp_custom_points_user (user_id) ) ENGINE=InnoDB AUTO_INCREMENT=199180 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | +----------------------------------+------------------------------------------------------+ SHOW INDEX FROM wp_custom_points_user; +----------------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | wp_custom_points_user | 0 | PRIMARY | 1 | id | A | 171334 | NULL | NULL | | BTREE | | | | wp_custom_points_user | 0 | ixd_uc_tzs_wp_custom_points_user | 1 | user_id | A | 171334 | NULL | NULL | | BTREE | | | +----------------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+