# MySQL PMM Metric-based Tuning Recommendation **URL:** \ **Category:** MySQL & MariaDB\ **Tags:** pmm, mysql, percona\ **Created:** [March 2, 2025, 3:33pm UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002 "2025-03-02T15:33:21Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Yauheni\_Freeman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yauheni_freeman/32/20344_2.png) [@Yauheni\_Freeman](https://forums.percona.com/u/Yauheni_Freeman)\ **Post date:** [March 2, 2025, 3:33pm UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/1 "2025-03-02T15:33:21Z")
## 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_size` parameter? - “Top Percentage of File Openings to Opened Files” concerns me as well? How to understand this metric? ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/e/1/e16bfbc0a5dc737c29b11347b957bf1a32df51d5.png) ## Version: ```auto 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](https://pastebin.com/BRcPJ2Hx) 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 | 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 # The End ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [March 3, 2025, 8:00pm UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/2 "2025-03-03T20:00:01Z")
I see “query\_cache\_type” in your config files. This indicates you are still on 5.7 (almost 2 years dead). I do not recommend changing any parameters until after you upgrade to 8.0 because you might end up changing them again. > join\_buffer\_size = 512M > sort\_buffer\_size = 48M > key\_buffer\_size = 392M These are HUGE! Set these back to defaults. Each of these buffers is allocated as needed. It does not benefit you from having them set so high. ---
**Author:** ![Yauheni\_Freeman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yauheni_freeman/32/20344_2.png) [@Yauheni\_Freeman](https://forums.percona.com/u/Yauheni_Freeman)\ **Post date:** [March 4, 2025, 9:16am UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/3 "2025-03-04T09:16:07Z")
> [@matthewb](#): > > This indicates you are still on 5.7 (almost 2 years dead) Right. Unfortunately, upgrade is still in “planned” state. > [@matthewb](#): > > These are HUGE! Set these back to defaults. Each of these buffers is allocated as needed. It does not benefit you from having them set so high. Okay. I’ll try it out. A couple of questions: - Currently, to decide if change really help I look into query response time. Is it okay? Or there is better way? In my case unfortunately, the query response time is under 100ms and doesn’t change much. - What do you think about other concerns - 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\_size parameter? - “Top Percentage of File Openings to Opened Files” concerns me as well? How to understand this metric? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [March 4, 2025, 1:16pm UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/4 "2025-03-04T13:16:12Z")
> [@Yauheni\_Freeman](#): > > - I see low CPU utilization of mysql server, even PMM says me about it Great. Plenty of room to grow. > [@Yauheni\_Freeman](#): > > - MySQL handlers chart shows high read\_next metric value Perfect. `read_next` indicates queries are executing with indexes. > [@Yauheni\_Freeman](#): > > but I’m not sure how to find out what the right value for tmp\_table\_size parameter In 5.7, don’t go larger than 1GB because the parameter is per-temp table. If 400 temp tables are created at 400MB each, that’s 156GB of memory consumed! ---
**Author:** ![Yauheni\_Freeman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yauheni_freeman/32/20344_2.png) [@Yauheni\_Freeman](https://forums.percona.com/u/Yauheni_Freeman)\ **Post date:** [March 11, 2025, 8:01am UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/5 "2025-03-11T08:01:29Z")
![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/d/cdc81e7e816a4816eaacdb2e9905080a7c2281bc.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/8/a81144f54776affef79f39e14e747d3b06c22b3f.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/2/7/277ac1452a11671bb3749a73bc3279952dca9ac2.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/9/a997368e2f699efe0097383b6be936ce81d0ac5f.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/4/6/4637e7f6a29282b66de1d2ffd239ab796f48ab81.jpeg) What would you recommend to get those metrics green? Especially, I’m not sure how to understand “Queries” metric. What does it mean? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [March 11, 2025, 2:09pm UTC](https://forums.percona.com/t/mysql-pmm-metric-based-tuning-recommendation/37002/6 "2025-03-11T14:09:28Z")
Some graphs will never be green as the color in the graph is set to red. You can make copies of the dashboards and change the color if you’d like.