Description:
I look into MySQL PMM metrics for possible ways of optimization and tuning to squeeze even more performance from it and noticed a couple of oddities (at least on my view) that concerns me, but I cannot explain / judge if it’s okay or not, and if not, then would be the right steps to get it into better state. In particular
- I see low CPU utilization of mysql server, even PMM says me about it
- MySQL handlers chart shows high read_next metric value
- PMM says “More than 38% of the queries are causing temporary table creation on disk. Query and configuration review is recommended.” but I’m not sure how to find out what the right value for
tmp_table_sizeparameter? - “Top Percentage of File Openings to Opened Files” concerns me as well? How to understand this metric?
Version:
Version | 5.7.44-48 Percona Server (GPL)
PMM Current version: 2.44.0
Additional Information:
Here is the content of summary chart provided by PMM (pastebin and raw content too):
I’d be appreciated if someone experienced take a look and guide me what can be improved.
mysql: [Warning] Using a password on the command line interface can be insecure.
Percona Toolkit MySQL Summary Report
System time | 2025-03-02 15:20:58 UTC (local TZ: +03 +0300)
Instances
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
0 0
MySQL Executable
Path to executable | /usr/sbin/mysqld
Has symbols | No
Slave Hosts
No slaves found
Report On Port 3306
User | pmm@127.0.0.1
Time | 2025-03-02 18:20:58 (+03)
Hostname | <hidden>
Version | 5.7.44-48 Percona Server (GPL), Release 48, Revision 497f936a373
Built On | Linux x86_64
Started | 2025-03-02 03:02 (up 0+15:18:18)
Databases | 7
Datadir | /var/lib/mysql/
Processes | 35 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /var/run/mysqld/mysqld.pid (exists)
Processlist
Command COUNT(*) Working SUM(Time) MAX(Time)
Query 1 1 0 0
Sleep 30 0 10000 1000
User COUNT(*) Working SUM(Time) MAX(Time)
hiddenuser1 8 0 0 0
hiddenuser2 5 1 0 0
hiddenuser3 20 0 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
127.0.0.1 5 1 0 0
localhost 30 0 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
NULL 5 1 0 0
hiddendb1 20 0 0 0
hiddendb2 8 0 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
30 0 0 0
starting 1 1 0 0
Status Counters (Wait 10 Seconds)
Variable Per day Per second 11 secs
Aborted_clients 25
Bytes_received 15000000000 175000 450000
Bytes_sent 70000000000 800000 3000000
Com_admin_commands 25000
Com_begin 6000
Com_commit 6000
Com_delete 300000 3 1
Com_delete_multi 1250
Com_empty_query 50000
Com_insert 600000 7 1
Com_insert_select 25000
Com_replace 3000
Com_select 22500000 250 700
Com_set_option 500000 5 6
Com_show_binlogs 3
Com_show_create_db 25
Com_show_databases 4
Com_show_engine_status 9000
Com_show_keys 25
Com_show_master_status 3
Com_show_plugins 1500
Com_show_processlist 3
Com_show_slave_hosts 3
Com_show_slave_status 9000
Com_show_status 17500
Com_show_tables 8000
Com_show_variables 4500
Com_stmt_execute 50
Com_stmt_close 50
Com_stmt_prepare 50
Com_update 800000 9 5
Com_update_multi 9000
Connections 100000 1 3
Created_tmp_disk_tables 1500000 15 50
Created_tmp_files 5000
Created_tmp_tables 2250000 25 70
Flush_commands 1
Handler_commit 22500000 250 700
Handler_delete 500000 5
Handler_external_lock 90000000 1000 3000
Handler_read_first 3000000 35 80
Handler_read_key 2500000000 30000 50000
Handler_read_last 30000
Handler_read_next 22500000000 250000 175000
Handler_read_prev 60000000 700 1
Handler_read_rnd 80000000 1000 4000
Handler_read_rnd_next 700000000 9000 80000
Handler_rollback 300
Handler_update 2250000 25 10
Handler_write 35000000 450 250
Innodb_background_log_sync 90000 1
Innodb_buffer_pool_bytes_data 15000000000 175000
Innodb_buffer_pool_bytes_dirty 4000000 50 250000
Innodb_buffer_pool_pages_flushed 1250000 15
Innodb_buffer_pool_pages_made_young 1250
Innodb_buffer_pool_pages_old 350000 4
Innodb_buffer_pool_read_ahead 50000
Innodb_buffer_pool_read_requests 35000000000 400000 800000
Innodb_buffer_pool_reads 900000 10
Innodb_buffer_pool_write_requests 80000000 900 2500
Innodb_checkpoint_age 1500000 15 4000
Innodb_checkpoint_max_age 175000000 2000
Innodb_data_fsyncs 1000000 10
Innodb_data_read 15000000000 175000
Innodb_data_reads 1000000 10
Innodb_data_writes 3000000 35 7
Innodb_data_written 22500000000 250000 10000
Innodb_dblwr_pages_written 1000000 10
Innodb_dblwr_writes 250000 3
Innodb_ibuf_free_list 10000
Innodb_ibuf_segment_size 10000
Innodb_log_write_requests 2250000 25 8
Innodb_log_writes 1250000 15 7
Innodb_lsn_current 2500000000000 30000000 4000
Innodb_lsn_flushed 2500000000000 30000000 4000
Innodb_lsn_last_checkpoint 2500000000000 30000000
Innodb_master_thread_active_loops 60000 1
Innodb_master_thread_idle_loops 25000
Innodb_max_trx_id 5000000000 60000 15
Innodb_mem_adaptive_hash 1750000000 20000 1500
Innodb_mem_dictionary 150000000 1750
Innodb_os_log_fsyncs 60000
Innodb_os_log_written 2250000000 25000 10000
Innodb_pages_created 10000
Innodb_pages_read 1000000 10
Innodb_pages0_read 3500
Innodb_pages_written 1250000 15
Innodb_purge_trx_id 5000000000 60000 15
Innodb_row_lock_time 1
Innodb_row_lock_waits 20
Innodb_rows_deleted 500000 5
Innodb_rows_inserted 80000000 900 4000
Innodb_rows_read 25000000000 300000 300000
Innodb_rows_updated 700000 7 7
Innodb_num_open_files 3500
Innodb_available_undo_logs 200
Innodb_secondary_index_triggered_cluster_reads 8000000000 90000 175000
Innodb_secondary_index_triggered_cluster_reads_avoided 35000
Innodb_buffered_aio_submitted 50000
Key_read_requests 60
Key_reads 7
Open_table_definitions 2500
Opened_files 12500
Opened_table_definitions 8000
Opened_tables 1500000 20 15
Queries 25000000 300 700
Questions 25000000 300 700
Select_full_join 100000 1 3
Select_full_range_join 225000 2 15
Select_range 2250000 25 150
Select_range_check 1000
Select_scan 1750000 20 40
Sort_merge_passes 15000
Sort_range 1250000 15 40
Sort_rows 70000000 800 4000
Sort_scan 2250000 25 60
Ssl_accepts 70 1
Ssl_finished_accepts 70 1
Table_locks_immediate 125000 1 1
Table_open_cache_hits 40000000 500 1500
Table_open_cache_misses 1500000 20 15
Table_open_cache_overflows 1500000 20 15
Threads_created 100
Uptime 90000 1 1
Table cache
Size | 2395
Usage | 100%
Key Percona Server features
Table & Index Stats | Disabled
Multiple I/O Threads | Enabled
Corruption Resilient | Enabled
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Disabled
Replica Perf Logging | Disabled
Response Time Hist. | Enabled
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
Percona XtraDB Cluster
Plugins
InnoDB compression | ACTIVE
Query cache
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
Schema
Specify --databases or --all-databases to dump and summarize schemas
Noteworthy Technologies
SSL | Yes
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | Yes
Prepared statement count | 0
InnoDB
Version | 5.7.44-48
Buffer Pool Size | 16.0G
Buffer Pool Fill | 60%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 64.0M = 128.0M
Log Buffer Size | 16M
Flush Method | O_DIRECT
Flush Log At Commit | 2
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 8 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | READ-COMMITTED
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 873k
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 25
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 32xnot started
MyISAM
Key Cache | 392.0M
Pct Used | 20%
Unflushed | 0%
Security
Users | 8 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
Encryption
No keyring plugins found
Binary Logging
Noteworthy Variables
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect | SET NAMES utf8 COLLATE utf8_unicode_ci
init_file |
sql_mode |
join_buffer_size | 512M
sort_buffer_size | 48M
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 512M
tmp_table_size | 1G
max_allowed_packet | 16M
thread_stack | 128k
log |
log_error | /var/log/mysql/error.log
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
Configuration File
Config File | /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysqld/mysqld.sock
default-character-set = utf8
[mysqld_safe]
nice = 0
socket = /var/lib/mysqld/mysqld.sock
[mysqld]
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysqld/mysqld.sock
skip-external-locking
default-storage-engine = innodb
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover-options = BACKUP
explicit_defaults_for_timestamp = 1
expire_logs_days = 10
max_binlog_size = 100M
sql_mode = “”
query_cache_size = 32M
table_open_cache = 4096
thread_cache_size = 32
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 2M
sort_buffer_size = 2M
tmpdir = /tmp
max_heap_table_size = 32M
tmp_table_size = 32M
innodb_file_per_table
innodb_buffer_pool_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 64M
innodb_flush_method = O_DIRECT
innodb_strict_mode = OFF
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = “SET NAMES utf8 COLLATE utf8_unicode_ci”
skip-name-resolve
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8
[mysql]
[isamchk]
key_buffer = 16M
/etc/mysql/conf.d/logging.cnf
[mysqld_safe]
log-error = /var/log/mysql/error.log
[mysqld]
log-error = /var/log/mysql/error.log
/etc/mysql/conf.d/bvat.cnf
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 16M
innodb_buffer_pool_size = 14336M
max_connections = 125
table_open_cache = 14336
thread_cache_size = 128
max_heap_table_size = 128M
tmp_table_size = 128M
key_buffer_size = 196M
join_buffer_size = 24M
sort_buffer_size = 24M
bulk_insert_buffer_size = 2M
myisam_sort_buffer_size = 24M
/etc/mysql/conf.d/z_bx_custom.cnf
[mysqld]
query_cache_limit = 64M
innodb_buffer_pool_size = 17179869184
innodb_buffer_pool_instances = 64
max_connections = 200
max_heap_table_size = 512M
tmp_table_size = 1Gb
join_buffer_size = 512M
sort_buffer_size = 48M
key_buffer_size = 392M
query_response_time_stats = on
query_cache_type = off
query_cache_size = 0
innodb_read_io_threads = 8
Memory management library
jemalloc is not enabled in mysql config for process with id 2330
