join_buffer_size causes high cpu load I am playing and testing with my.cnf and at the moment I have these server specs:
12 CPU vCore
500 GB SSD
24 GB RAM
1 GB Swap
and this is the input of my my.cnf:
# GENERAL #
# user                           = mysql
# default-storage-engine         = InnoDB



# DATA STORAGE #
# datadir                        = /var/lib/mysql/


#TIMEOUTS#
wait_timeout                    = 600


#NO DNS#
# skip-name-resolve


# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP


# SAFETY #
max_connections = 250
# max-allowed-packet             = 16M
# max-connect-errors             = 1000000


# CACHES AND LIMITS #
tmp_table_size = 512M
max_heap_table_size = 512M
join_buffer_size = 384K
query_cache_size = 0
query_cache_type = 0
# thread-cache-size              = 32
# open-files-limit               = 65535
# table-definition-cache         = 4096
# table-open-cache               = 1024


table_definition_cache = -1
performance_schema = ON


# INNODB #
innodb_lock_wait_timeout = 600
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_stats_on_metadata = 0
innodb_buffer_pool_instances = 10
innodb_file_per_table = 1

#LOG QUERIES#
# long_query_time                = 3
# log_slow_queries               = 1
# slow_query_log_file            = /var/lib/mysql/mysql-slow.log


[mysqld]
# Skip reverse DNS lookup of clients
# skip-name-resolve

I just made the exprience, that as soon as join_buffer_size exceeds 512k, suddenly the CPU load goes up to 100%. How can this be explained?
Please try the experiment again; this time capture these for the two values of join_buffer_size:
 EXPLAIN SELECT ... (including the query)
 how long the query took
 SHOW CREATE TABLE ...  -- of reach table in the query

Meanwhile, I will guess at this explanation.  The Optimizer has several ways it might perform a query.

Ignore the join_buffer and use lookups (indexed or not) for joining.
Use the join_buffer:  Load the second table completely into a hash in RAM, then reach into it for lookups.

The potential problem include

The Optimizer may not have the &quot;right&quot; statistics for deciding between the two choices.  (There is no reliable way to fix this for all cases.)
There may be a LIMIT or something else that makes loading the entire table the &quot;wrong&quot; approach.
Once the join_buffer is loaded, the rest of the query is CPU-bound.  When not using the join_buffer, the query may be I/O-bound and is likely to take longer (elapsed time).  (Hence my request for the timings.)
Etc.