1

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

5
  • 1
    Why do you think your config is the problem? Does your application use persistent connections or prepared statements? Oct 27 '20 at 15:27
  • 1
    because I think my server is capable of taking such load, I just don't know about database in this matter. My PHP script is very simple (it takes about 6 mb memory) so I don't use any of them, just simple connect, do 2x update, loop 100 rows update, finish - but want to run this script 10 times to faster update my data... I just updated new my.cnf I tried but none changed in clogging queries. (when scripts working when I try to run script manually, by entering website it takes so long and gives timeout - but it's not php fault, just query) link: pastebin.com/raw/2VmWZj4n Oct 27 '20 at 15:50
  • Edit the question and add the output of SHOW CREATE TABLE products ; The most likely explanation is that it is missing essential indexes. Oct 27 '20 at 16:32
  • 1
    posted TABLE in main thread, just want to say that table has 30.000 records, but contains almost only TINY INT, 1x text max 64k chars, so not all fields are set already - no big data here. I have no idea how I should set indexes for my UPDATE things (100x UPDATE in each run of script) and I SET different, dynamic numbers to shop1-10 of each UPDATE step. Any suggestions how to manage it would be appreciated. Oct 27 '20 at 20:25
  • Tom, Now that you have functioning indexes, Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Nov 28 '20 at 3:07
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.

7
  • 1
    I updated my queries from script at the end of my main thread. There are where clauses precisely - I just ORDER BY because I want older first. In simple run 100 rows are fetched in matter of 30-40s and its totally fine, the problem is when I run more scripts the timings are getting longer. There are no indexes. Exactly - first SELECT is getting ID's and then I do while loop with UPDATE state. Oct 27 '20 at 16:19
  • 1
    In a single run at webbrowser timings are: # 1 = 0.3s, # 2 = 0.5s, and after a loop its 45s whole execution time of script - this is why I'm so concerned that it's database thing by limiting number of queries or something. I don't know how to automatically run sets of queries like at phpmyadmin :( I understand that time would increase but not that bad slowing every next query. Oct 27 '20 at 16:29
  • No indexes, and you want to solve a performance problem through configuration? Apart from the missing bind variables - which forces the DB to analyse each statement over and over again, you compare numbers to strings. Oct 27 '20 at 16:48
  • 2
    @TomShephard - "indexes .. not needed as I don't run big queries" -- False. A slow query needs an index. A frequently run, but fast, query may need an index. Concurrency needs an index to help the separate threads from stopping each other. The first thing to look into for performance is indexes.
    – Rick James
    Oct 27 '20 at 22:14
  • 1
    @TomShephard - The SELECT is fast because it is using an index, namely the PRIMARY KEY(id). An UPDATE may benefit from an index. Ditto for a DELETE.
    – Rick James
    Oct 27 '20 at 22:16
1

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

0

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.

7
  • 1
    RAM is barely used (2 gb of 16 gb), so of course I can increase it. Oct 27 '20 at 17:18
  • 1
    (pool was 2 times I figured it asap after posting - I use 13G) UPDATE in while is mostly used but each run is 0.2-0.4s x 100 loops = about 45s total script run (when 1 instance, but I want to do it faster so I need more instances at once) available_worker_id it vary 1-10 because here I set my "worker" to set which query he should next work on. 30.000 rows in table. There are INSERT new rows once per day so totally not affect this. TABLE: pastebin.com/raw/qQPWR3mD I saw even when I run just 2 instances, time for each script execute is near double than SOLO, check DEBUG SQL at 1st post Oct 27 '20 at 18:10
  • 1
    Did it speed up after adding indexes? How many rows in the table?
    – Rick James
    Oct 27 '20 at 22:08
  • 1
    changed PRIMARY KEY to product_id (int) instead of url (varchar), added some indexes and everything works better now - I need to dive deeply into performance by indexes and other methods :-) Thanks to everyone for answers. Oct 28 '20 at 13:20
  • 1
    @TomShephard - My Cookbook on indexing: mysql.rjweb.org/doc.php/index_cookbook_mysql
    – Rick James
    Oct 28 '20 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.