Mysql: Is it possible to handle 1000 concurrent while having low cpu? 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:


Is there any measure one could take that I didn't know about to
reduce the CPU usage of search queries?
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.
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.