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
Last activity: Apr 22, 2025, 03:01 AM