high CPU with high user connection, mysql database

Question

I use a VPS with 56 CPU cores and 64 GB of memory, a with MySQL database, but the CPU usage is always high for my apps - user 10.000. This is in my mysqld.cnf. What's wrong with my settings?

innodb_buffer_pool_size=50G
innodb_change_buffering=all
innodb_log_file_size = 3125M
innodb_log_buffer_size = 3125M
innodb_file_per_table = ON
innodb_log_files_in_group =4
innodb_flush_method = O_DSYNC
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances = 50
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency=112
innodb_stats_on_metadata = OFF
innodb_thread_sleep_delay=1000
innodb_purge_threads=8
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 5000
innodb_io_capacity_max=15000
key_buffer_size         = 4G
max_allowed_packet      = 1G
thread_stack            = 5M
sort_buffer_size = 50M
read_buffer_size = 50M
read_rnd_buffer_size = 20M
myisam_sort_buffer_size = 20M
join_buffer_size = 1G   
myisam-recover-options  = BACKUP
max_connections        = 1000
max_user_connections = 500
thread_cache_size      = 1000
query_cache_limit       = 0
query_cache_size        = 0
long_query_time = 10
expire_logs_days        = 5
max_binlog_size   = 200M

Answer 1

innodb_log_buffer_size - I would keep that under 1% of RAM. (innodb_log_file_size looks OK.)

innodb_log_files_in_group there is some evidence that more that "2" degrades performance.

innodb_flush_method -- this depends on MySQL version and disk filesystem type. (Your choice is rarely picked by other DBAs.)

Did you also set innodb_adaptive_max_sleep_delay? I see that MariaDB abandoned innodb_thread_sleep_delay in 10.5 -- either it was obviated by some improvement, or it was deemed not useful. I don't know MySQL's stand on the two.

innodb_io_capacity_max=15000 -- Do you have a super-duper SSD?

key_buffer_size = 4G: Unless you are using MyISAM (you should not be), set this to only 50M.

thread_stack -- Leave at the default value

long_query_time = 1 to make better use of the slowlog. After a day, analyze it with pt-query-digest. SlowLog

But the real way to deal with high CPU (or Load Average) is to find the 'worst' queries (according to the slowlog) and work on improving them. (Random tuning of settings can only get you into trouble.)

For further analysis of the settings, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after running at least a day. ( http://mysql.rjweb.org/doc.php/mysql_analysis#tuning )