1

We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5.

The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database structure is bad.

The client doesn't care about query optimization, he just wants to lower CPU usage, he runs locust, a load testing tool, give it 1500 users and 1000 concurrent, sees the CPU at in the 90s % and says this is bad. The search functionality is the problem he said.

Again I'm telling you, the queries are bad, I just got hired and doing what I can, but is he looking at the right metrics?

What have I tried to do other than fixing queries:

Adding indexes, there were no indexes, also my.cnf

innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown =1
innodb_buffer_pool_load_at_startup =1
## innodb_buffer_pool_instances should match the number of cores on the server

innodb_log_file_size = 2047M
innodb_flush_method=O_DIRECT
sync_binlog=0
innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24

## set innodb_buffer_pool to 50% to 70% of the ram on the server so if the server have 32G, then set it to 16G. 50% is a good start
## turn off slow query log, if you're not logging, and only log for small durations when you want to monitor things
slow_query_log =0 

I tried to optimize what I can from the slow query log as well. Also trying to use proxySQL, which I heard it intercepts the query and sends 1/10 of the queries to the server, because of caching.

The client has a streaming app, during peak events, he hits 7k users, not sure if that counts as concurrent users and highly doubt that all of them would search. They have an on-premise server

DL380 Gen10 2x Intel Xeon Silver 4110 / 2.1 GHz 64 GB RAM 300 GB HDD

My questions:

  1. Is there any measure one could take that I didn't know about to reduce the CPU usage of search queries?
  2. If I were to optimize all the queries, would I expect lower CPU under such a load test? They want the CPU usage to drop to like 50%, I just feel that they're looking at the wrong metrics. They have the right to do so because it used to be that whenever cpu got to 100%, server would go down.
  • 1
    You're running a load test tool, which purposely stress tests & uses CPU on purpose, the queries are admittedly bad, yet someone is complaining about high CPU usage? This all makes zero sense. The answer to your question is: Tune your queries – Philᵀᴹ Oct 22 at 9:38
  • 1
    @Philᵀᴹ if all the queries get tuned, would you expect cpu usage in the 50s% for 1000 concurrent users? Isn't mysql going to use that much cpu even then? – Lynob Oct 22 at 9:41
  • Depends on what the test is doing and if the application will become the bottleneck. The server could still run at 100 percent but the test could finish faster. – Tom V - Team Monica Oct 22 at 9:58
  • 1
    @RaymondNijland He is a manager, I can't argue with him because I'm a developer and because the queries are indeed bad, rewriting them is a must but I'm not sure I'll be able to show him lower cpu, because I've seen healthy databases with 100% cpu. The webserver and the database are running on the same machine, is this a problem? – Lynob Oct 22 at 13:16
  • 1
    "He is a manager, I can't argue with him because I'm a developer " Right the typical manager / developer sparring sessions.. Manager says something is bad because it is slow.. Developer give the manager right, analyse the code which worked maybe for 3 year fine and fast and says it is bad and the code needs to be rewritten.. Manager will not believe it as it worked for 3 years fine and disallowes rewritting it.. – Raymond Nijland Oct 22 at 13:21
2

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

I rarely need to go down the list more than 3 items to make a dramatic improvement.

If you are weak on knowing what indexes to add, don't simply "index ever column". See http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Is there any measure one could take that I didn't know about to reduce the CPU usage of search queries?

No. High CPU is fixed by using selected composite indexes and/or reformulating the queries. Show us the queries. "Search" is too vague a term.

Here's an extreme anecdote: The CPU was pegged at 100%. The slowlog pointed at one query. It had WHERE DATE(foo) = '...'. By making that "sargable", the CPU dropped to 2%.

As for benchmarks... Beware. They tend to see how many connections they can run before running out of horsepower. That is, the results are guaranteed to say 100% CPU! The real metric they get out is "queries/second" or "maximum connections".

Furthermore, it has repeatedly been documented that if you push mysql beyond some number of concurrent users, throughput goes down! A decade ago, that number was about 6; now it is more like 64. But 1000? No way. It is spinning its wheels because all 1000 are stumbling over each other.

How to handle 1000 users? Replication feeding multiple Slaves. With such, you could have 1000 slaves, each running a mere 10 connections; CPU will be very low. Etc.

  • To explain the "sargable" term which was used here.. A few "sargable" versions off WHERE DATE(date_time) = '2019-10-23' could be WHERE DATE(date_time) LIKE '2019-10-23%' (evil auto casting) or the some better ones WHERE date_time => '2019-10-23' AND date_time < '2019-10-23' + INTERVAL 1 DAY (should be preprocessed/optimized as '2019-10-24' anyway) ... WHERE date_time => '2019-10-23' AND date_time < '2019-10-24' – Raymond Nijland Oct 23 at 18:33
  • @RaymondNijland - When the curve flattens, it means that latency is suffering, but the pages continue to be processed. The processes are stumbling over each other, taking longer to finish. When the curve takes a nose dive, both latency and throughput suffer. – Rick James Oct 23 at 23:42
  • ... also keep in mind that left scale is webpages per second so the latency could also be caused by other things aswell.. but removed mine comments about this anyway as the benchmark also feels a bit to much staged and unclear what and how it was benchmark – Raymond Nijland Oct 24 at 8:49
1

The queries are bad

Then they need fixing. You could add extra hardware but you will get linear improvements at best, fixing the queries could potentially give several orders of magnitude of improvement by removing huge scans and such. Though I'm only guessing here as we have no information about the actual queries.

and there are far too many of them to optimize

There may be far to many to optimise immediately, but you can take metrics and attack the worst offenders first. This won't always be the complex ones either: a simple sub-optimal query that is run many times in a given period will be better to attack first than a massive complex report that is run once per month.

the database structure is bad.

This may impede improving the queries, especially if the bad design isn't just a lack of good index choices. Working on both together may be the best plan in the long run, but it will take more work to see the first beneficial results. Again you give no specifics so we can only give vague answers/guesses.

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