Wordpress UPDATE queries on MySQL database stuck

Question

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[0]:cur[0], unzip sum[0]:cur[0]
--------------
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      |         |               |
+----------------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

UPDATE `wp_custom_points_user`
    SET `total_points` = '18900'
    WHERE `user_id` = 188768

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

We need to see one more thing -- the code around the UPDATE. Is there a BEGIN... COMMIT transaction around it? What is the value of autocommit?

I suspect there is a long set of statements in a transaction. And it is the transaction that is causing the delays. The value (total_points) sounds like some kind of counter. I don't know why they did not do something simpler like

UPDATE ... SET total_points = total_points + 1  WHERE ...

Instead, they must be doing

BEGIN;
SELECT total_points ... FOR UPDATE;  -- it would be a bug without FOR UPDATE
some other stuff         -- this may be slow
UPDATE ...
COMMIT;

Also...

There is no obvious reason to have an auto_inc for the PK. They should change to this, which would speed up things:

CREATE TABLE `wp_custom_points_user` (
  `total_points` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
) ENGINE=InnoDB

These changes to the table definition you could do. However, if they depend on id for something else you could break something. So, the following should be safe, and achieve some speedup:

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,
  INDEX (`id`),                -- minimum needed for auto_increment
  PRIMARY KEY (`user_id`)      -- clustering to get some speedup
) ENGINE=InnoDB

That can be achieved via:

ALTER TABLE wp_custom_points_user
    DROP PRIMARY KEY,
    ADD  PRIMARY KEY(user_id),
    DROP INDEX ixd_uc_tzs_wp_custom_points_user,
    ADD INDEX(id);

Answer 2

This line picked up from SHOW ENGINE INNODB STATUS;

UPDATE `wp_custom_points_user` 
SET `total_points` = '18900' 
WHERE `user_id` = 188768

quotes around the value for total_points is killing performance and should be REMOVED for

UPDATE `wp_custom_points_user` 
SET `total_points` = 18900 
WHERE `user_id` = 188768

to avoid DATATYPE manipulation overhead in setting the value on the row.