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:
- update set value=0 in client_table;
- update set value=1 in client_table;
- 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
SHOW CREATE TABLE products ;
The most likely explanation is that it is missing essential indexes.