Many developers encounter MySQL running at 100% CPU usage on just one core while other cores remain idle. This happens because MySQL's default configuration isn't optimized for multi-core systems. The issue stems from how MySQL handles threading and query execution.

Here are the essential parameters to examine in your my.cnf or my.ini file:

[mysqld]
# Enable multiple threads for InnoDB
innodb_thread_concurrency = 8
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# General thread optimization
thread_handling = pool-of-threads
thread_cache_size = 16

# Query parallelization
optimizer_switch = 'parallel_union=on'

For a 4-core server, consider these settings:

# Number of CPU cores x 2 for optimal performance
innodb_thread_concurrency = 8

# Match this to your number of CPU cores
innodb_buffer_pool_instances = 4

# For parallel query processing
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

After restarting MySQL, check thread utilization with:

SHOW STATUS LIKE 'Threads_running';
SHOW ENGINE INNODB STATUS\G

You should see multiple threads active across different cores using Linux's top or htop command.

For MySQL 8.0+, consider enabling the parallel query execution feature:

SET GLOBAL optimizer_switch='parallel_query=on';
SET GLOBAL max_parallel_workers=4;
SET GLOBAL max_parallel_workers_per_gather=2;

Implement regular monitoring with a script like this:

#!/bin/bash
while true; do
    mysql -e "SHOW PROCESSLIST" | grep -v Sleep
    top -bn1 | grep mysqld
    sleep 5
done

Many developers encounter a frustrating scenario where their MySQL server maxes out a single CPU core while other cores remain underutilized. This typically happens because:

• MySQL's default configuration prioritizes single-threaded execution
• Certain operations (like complex queries) can't be parallelized by default
• The thread pool isn't properly configured for multi-core environments

Here are the essential my.cnf parameters to modify:

[mysqld]
# Enable parallel query execution
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 0  # Let OS handle concurrency

# Optimize for multi-core
thread_handling = pool-of-threads
thread_pool_size = 4           # Match your core count
thread_pool_max_threads = 100

# Other performance tweaks
innodb_adaptive_hash_index_partitions = 8
innodb_buffer_pool_instances = 4

For MySQL 8.0+, you can enable true parallel query processing:

SET GLOBAL optimizer_switch='parallel_query=on';
SET GLOBAL max_parallel_workers=4;
SET GLOBAL max_parallel_workers_per_gather=2;

Then verify with:

SHOW VARIABLES LIKE '%parallel%';

Testing a complex analytical query on a 4-core server:

Use these commands to verify multi-core usage:

# Check thread distribution
SHOW PROCESSLIST;

# Monitor CPU usage per thread
SELECT THREAD_ID, PROCESSLIST_ID, 
       FORMAT_PICO_TIME(THREAD_OS_ID) AS OS_ID,
       PROCESSLIST_INFO AS query
FROM performance_schema.threads
WHERE PROCESSLIST_ID IS NOT NULL;

For large tables, consider partitioning to force parallel execution:

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

This allows parallel scans across different partitions.

If you're still seeing single-core dominance:

1. Check for table locks with SHOW OPEN TABLES WHERE In_use > 0
2. Examine slow queries with the slow query log
3. Consider moving to MySQL Cluster or Percona Server for better multi-threading