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

database-designMySQLmysql-8.0performancequery-performance

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

Best Answer

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.

Related Question