MySQL thread states: update vs updating

Question

This should be a simple question, but surprisingly, I can't find much about it: the difference in MySQL thread states update vs updating.

The MySQL docs say:

• update: "The thread is getting ready to start updating the table."
• updating: "The thread is searching for rows to update and is updating them"

MariaDB says:

• update: "About to start updating table."
• updating: "Searching for and updating rows in a table."

I'm trying to squeeze the maximum out of a an application doing about 100k INSERT ON DUPLICATE KEY UPDATE rows per second (which is about 3000 prepared multi-value inserts per second), running on a 96 thread Amazon RDS db.m5.24xlarge. Much of the load is parallel.

RDS Performance insight shows no updating cost to the INSERT ON DUPLICATE KEY UPDATE, but only update. How can it only be getting ready, and not doing it?

What exactly is it getting ready for, and how are the states different from each other?

This server still has a query cache enabled BTW, which will be turned off and set to 0 on the next maintenance window, is the plan.

Answer 1

In my experience, if you see "Updating" the thread is waiting on a lock. You can demo this:

Window 1:

mysql> start transaction;
mysql> insert into mytable set x = 40;

Window 2:

mysql> update mytable set x = 42;
(hangs, waiting for lock)

Window 1:

mysql> show processlist;
+-----+-----------------+-----------+-------+---------+---------+------------------------+---------------------------+
| Id  | User            | Host      | db    | Command | Time    | State                  | Info                      |
+-----+-----------------+-----------+-------+---------+---------+------------------------+---------------------------+
| 118 | root            | localhost | test2 | Query   |       0 | init                   | show processlist          |
| 119 | root            | localhost | test2 | Query   |       4 | updating               | update mytable set x = 42 |
+-----+-----------------+-----------+-------+---------+---------+------------------------+---------------------------+

You can see "updating" is window 2, the session that is blocked on the UPDATE.

So I would conclude if the stage is "updating", then the most likely explanation is that you have lock contention.

---

If you see "update" in the processlist, it's going through many types of validation checks, such as making sure all columns have a value or a default value, preparing triggers, building rows, executing the inserts, evaluating constraints, writing logs, releasing locks, and forming the response to the user.

That's a lot of work, especially if the INSERT is inserting many rows, or if there are complex triggers to run.

The process stages in MySQL aren't very fine-grained. Some of them are very brief, but some cover a wide array of steps.

There may even be cases in the MySQL code where it sets a process stage, and then after the operation (INSERT in this case) is completed, it doesn't set the stage label to anything else for a while. So even if the INSERT is in fact complete, the stage still says "update" until some other part of MySQL code changes it. I don't know if this is one of those cases.

Ultimately to understand it fully, you may have to read the code. Here's where it sets the "update" stage, and all code following is part of the work to perform the insert(s). https://github.com/mysql/mysql-server/blob/8.0/sql/sql_insert.cc#L542