MySQL 8.021 = 100% CPU when many UPDATE queries to 1 table

Question

I just install fresh LEMP stack at Freebsd 12 with MySQL 8.021

Noticing high usage of CPU I tested my queries in normal conditions individual and every is about 0.2s execute time - which is absolutely fine for me, but...

My PHP script run VERY SIMPLE queries:

1. update set value=0 in client_table;
2. update set value=1 in client_table;
3. loop 10 queries - update set about 10 fields in client_table, each query = 1 specified row with WHERE clause; (when I loop 100 queries its more than 60s and I got timeout because of cloudflare also... - their limit 90s) in general I dont want to exceed 60s because of cron I use 10 instances of script by crontab every 1 minute

1 instance run: 20-25s 10 instance run: 40-50s EACH script so at least double

The fact is when I raise number of queries from 50 of each instance to 100 it getting almost 3 time more than 1 instance run.

mysql table has almost 30.000 records, about 20 fields tinyint - int, 5x datetime, 1 text max 64k chars - most of fields are empty now so definitely not much data there

- top PID, USERNAME, THR PRI NICE SIZE RES STATE C TIME, WCPU COMMAND 67805 mysql, 60 20 0 4454M 1147M select 0 3:29 106.81% mysqld

ps. at most CPU is 20% user, because WCPU shows all cores usage (six) so maybe it's not really big CPU usage, rather clogging when much queries to 1 table at same time?

I use Contabo VPS with 6 virtual cores and 16GB of RAM and a 400GB ssd disk

I think my script even with loops and all that stuff shouldnt be that much harmful for MySQL... I hear about 8.018 but from ports its 8.021 actually.. so all things should be fixxed.

my.cnf Edit: ** The modified config I tried: (switched now for mysql 5.7 - but same situation so I doubt it's mysql VERSION issue) **

Maybe you have any performance tips for my config file to serve big more queries to 1 table in same time or maybe u experienced such problems?

My queries from script:

# 1 update
UPDATE products SET available_worker_id = 0
    WHERE available_worker_id = 1

# 2 update
UPDATE products SET available_worker_id = '1'
    WHERE check_available_date <= NOW()
      AND available_worker_id = 0
      AND product_id > 0
    ORDER BY check_available_date ASC LIMIT 100

# 3 update
SELECT product_id, next_check_avail FROM products
    WHERE available_worker_id = '1'
    ORDER BY check_available_date ASC LIMIT 100

while ($item = mysqli_fetch_assoc($result)) {
    $mysqli->query("UPDATE products SET shop1-shop10 = $TINY_INT, available_date = NOW(), check_available_date = '".date("Y-m-d H:i:s", time() + $item['next_check_avail'])."', available_worker_id = 0
    WHERE product_id = $product_id
    LIMIT 1")
}

** DEBUG SQL: (2 instances running at once) ** Max_used_connections 4 Max_used_connections_time 2020-10-27 19:03:02 Delayed_insert_threads 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Slow_launch_threads 0 Threads_cached 3 Threads_connected 1 Threads_created 4 Threads_running 1

10 instances: Max_used_connections 11 Threads_connected 11 Threads_created 11 Threads_running 10

---

TABLE: https://pastebin.com/raw/qQPWR3mD

Answer 1

As a rule, Databases do not run slowly. Queries do.

update set value=0 in client_table;

Look at the actual query (not the paraphrasing above).
Look at the "where clause" on that query.
Is it supported by indexes or is your database having to scan through the entire table to find the record you want to update? That would easily account for the excessive CPU load (you may well find that the entire table is sitting in memory).

Running updates in a loop is also questionable.
Have you retrieved a list of record 'ID's and are now looping through them, making a change to each? Relational DBMSs like to work on "sets" of rows that can extend to millions of rows. "Sniping" at individual rows where you could process them all in a single, set-based, query will be slower.

Answer 2

Indexes for commonly used fields in query and change my database schema PRIMARY KEY from URL (VARCHAR) to PRODUCT_ID (INT) helped much!

Answer 3

For innodb_buffer_pool_size, I see 3G and 13G; which is it? (Your comment implies that it is not even using 3G. Note: mysqld grows the buffer_pool until it reaches the limit.)

available_worker_id -- Does this have only two values? And there is often a big shift between 0 and 1? How many rows in the table?

UPDATE products SET available_worker_id = 0
    WHERE available_worker_id = 1

is quite costly, what is the purpose of this?

How often is check_available_date > NOW()? (It impacts that the optimal index will be.)

Does the table have INDEX(available_worker_id, check_available_date)? That index might be helpful.

Are you using InnoDB?

Which of the three UPDATEs is the program spending the most time in? We should focus on it.

How many rows in products? Does it fluctuate? If it is a hundred rows, there's not much to optimize. If there is a million rows, the queuing needs to be redesigned.

These look much too large:

max_connections                 = 100000   # UPD
back_log                        = 512
thread_cache_size               = 2000

Please provide SHOW GLOBAL STATUS LIKE 'Max_used_connections';.

Turn off the Query cache; it is part of the CPU load.