Sample Header Ad - 728x90

Slow select queries but CPU usage always less than 1%

0 votes
1 answer
347 views
We recently moved to a bigger server (64G, 16core Amazon EC2) and I was hoping some increased performance. However, there are plenty of slow queries (some even 50ms) but CPU usage remains very very low. Same configuration on previous server (32GB, 8 core) used up to 30-40% of CPU and queries were executed a lot faster. Any tips on how to take better advantage of CPU?
## File updated on 25 Jun 2023
##
[mysqld]

datadir=/media/db
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=512M
tmpdir=/dev/shm/
# Disabling symbolic-links is recommended to prevent assorted security risks
local-infile=0
log-warnings=2
skip-external-locking

symbolic-links=0
skip-name-resolve=1

expire_logs_days    = 1
bind-address        = 127.0.0.1
max_binlog_size         = 200M
log-bin=bin.log
log-bin-index=bin-log.index
binlog_format=row
binlog_cache_size = 100M

thread_stack        = 292K
myisam_sort_buffer_size = 64M
thread_cache_size = 200

myisam-recover         = BACKUP
max_connections        = 300
max_user_connections = 200
table_open_cache       = 10240 
table_definition_cache = 4096
thread_concurrency     = 16
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
key_buffer_size = 200M

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 1k
query_cache_size = 1024M


innodb_buffer_pool_size= 20G
tmp_table_size=256M
max_heap_table_size=256M
#Disabling timeouts
wait_timeout=50
interactive_timeout=50
connect_timeout=10
open-files-limit               = 65535
innodb_flush_log_at_trx_commit = 0
### Never remove 3 below - must for utf8mb4 charset
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_log_buffer_size= 2G
read_rnd_buffer_size = 8M 
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 25 
innodb_write_io_threads = 16 
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 24
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT 
innodb_buffer_pool_instances = 20
innodb_autoinc_lock_mode = 2
#slow_query_log = 1
#slow-query_log_file = /var/log/mysql-slow.log
#long_query_time = 0.5

# Recommended in standard MySQL setup

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Asked by JM John (13 rep)
Jul 12, 2023, 09:05 AM
Last activity: Apr 22, 2025, 03:01 AM