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
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
- 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.