MySQLTuner Internals

Table of contents

• MySQLTuner Internals
• Table of contents
• MySQLTuner steps
• MySQLTuner get login information steps
• MySQLTuner system checks
• MySQLTuner Server version checks
• Mysql error log file analysis
• MySQL Storage engine general information
• MySQLTuner security checks
• MySQLTuner CVE vulnerabilities detection
• MySQLTuner database information
• MySQLTuner index information
• MySQLTuner Connections information
• MySQLTuner server information
• MySQLTuner sort, join and temp table information
• MySQLTuner global buffer information
• MySQLTuner query cache checks
• MySQLTuner memory checks
• MySQLTuner slow queries checks
• MySQLTuner replication checks
• MySQLTuner InnoDB information
• MySQLTuner AriaDB information
• MySQLTuner MyISAM information
• MySQLTuner Galera information
• MySQLTuner TokuDB information
• MySQLTuner XtraDB information
• MySQLTuner Connect information
• MySQLTuner Spider information
• MySQLTuner RocksDb information
• MySQLTuner Thread pool information
• MySQLTuner performance schema and sysschema information
• MySQLTuner Cloud and SSH integration
• MySQLTuner Container and Systemd log integration

MySQLTuner steps

• Header Print
• Validate MySQLTuner version (check for updates)
• Setup Cloud mode (Azure/AWS) and SSH if needed
• Setup OS variables and commands (SSH prefixing)
• Get login information and setup MySQL connection
• Toss variables/status into hashes
• Validate MySQL and MariaDB versions (EOL check)
• Suggest 64-bit upgrade and architecture check
• Analyze mysqld error log file (Local, Docker, Podman, Kubectl, Systemd)
• Detect Cloud Environment (AWS, GCP, Azure, DigitalOcean)
• Detect Infrastructure (Storage type, Hardware architecture)
• Show parameters impacting performance during analysis
• Show information about databases (option: –dbstat)
• Show information about tables (option: –tbstat)
• Show information about indexes (option: –idxstat)
• Show information about views, triggers, and routines
• Show information about plugins (option: –plugininfo)
• Show enabled storage engines
• Display some security recommendations
• CVE vulnerabilities detection
• Calculate everything we need
• Print the server stats
• Print MyISAM stats
• Print InnoDB stats
• Print AriaDB stats
• Print Galera cluster stats
• Print replication info
• Query Anti-Pattern Detection (Performance Schema digests)
• Print Storage Engine specific stats (TokuDB, RocksDB, Spider, etc.)
• Print Performance Schema stats
• Sysbench result analysis (if provided)
• Historical Trend Analysis (if comparison file provided)
• Make recommendations based on stats
• Close reportfile if needed
• Dump result if debug is on

MySQLTuner get login information steps

• Is a login possible?
  • Cloud setup (Azure/AWS)?
  • SSH prefixing?
  • Force socket, pipe, or specific protocol?
  • Remote connection?
    • Specifying available RAM/Swap is required
    • Automatic detection if SSH is used
  • SSL CA certificate?
  • Got user/pass on command line?
  • mysql-quickbackup credentials available?
  • Plesk credentials available?
  • DirectAdmin credentials available?
  • Debian maintenance account credentials available?
  • Defaults file or defaults extra file?
  • Just try a login
    • If working, and .my.cnf isn’t there, WARNING
    • If working, and .my.cnf is there, okay
  • Prompt for creds on the console

MySQLTuner system checks

• Check whether more than 2GB RAM present if on 32-bit OS

• Check number of opened ports (warn when more than 9 ports opened)

• Check 80, 8080, 443 and 8443 ports if warning is raised if they are opened
• Check if some banned ports are not opened (option –bannedports separated by comma)
• Check if non kernel and user process except mysqld are not using more than 15% of total physical memory
• Check vm.swapiness
• Check /etc/security/limit.conf
• Check sysctl entries: sunrpc.tcp_slot_entries, vm.swappiness, fs.aio-fs-nr
• Check mount point
• Check Ethernet card
• Check load average

MySQLTuner Server version checks

• EOL MySQL version check

• Currently MySQL < 5.1 are considered EOL

• Using 5.5+ version of MySQL for performance issue (asynchronous IO)

Mysql error log file analysis

• Look for potential current error log file name

• Automatic detection for:

  • Docker containers
  • Podman containers
  • Kubectl/Kubernetes pods
  • Systemd journal (journalctl)
• Check permission on error log file
• Check size on error log file (warning if > 32MB)
• Check error and warning on error log file
• Find last start and shutdown on error log file

MySQL Storage engine general information

• Get storage engine counts/stats
  • Check for DB engines that are enabled but unused
  • Look for fragmented tables
  • Look for auto-increments near capacity
    • Look for tables with auto-increment with value near max capacity

MySQLTuner security checks

• Is anonymous user present?
• Users without passwords
• Users with username as password
• Users without host restriction
• Weak password check (possibly using cracklib later?)
• Using basic_passwords.txt as password database
• Password list checks can be avoided (option: –skippassword)

MySQLTuner CVE vulnerabilities detection

• option: –cvefile
• Check if your MariaDB or MySQL version contains CVE entries.

MySQLTuner database information

• Performance analysis parameter checks (metadata performance)

• Per database information

    * Tables number
  

  • Rows number
  • Total size
  • Data size
  • Percentage of data size
  • Index size
  • Percentage of index size
  • Views number
  • Triggers number
  • Routines number
  • Collation number
  • Check that there is only one collation for all tables in database
  • Check that there is only one collation for all table columns in database
  • Check that there is only one storage engine per user database

MySQLTuner index information

• Top 10 worth selectivity index
• Per index information
  • Index Cardinality
  • Index Selectivity
  • Misc information about index definition
  • Misc information about index size

MySQLTuner Connections information

• Highest usage of available connections
• Percentage of used connections (<85%)
• Percentage of aborted connections (<3%)

MySQLTuner server information

• Uptime: whether MySQL started within last 24 hours
• Bytes received and sent
• Number of connections
• Percentage between reads and writes
• Is binary log activated?
  • Is GTID mode activated?

MySQLTuner sort, join and temp table information

• Max memory temporary table size allowed.
• Percentage of sort using temporary table (<10%)
• Number of join performed without using indexes (<250)
• Percentage of temporary table written on disk (<25%)
• Thread cache (=4)
• Thread cache hit ratio (>50%) if thread_handling is different of pools-of-threads
• Table cache hit ratio (>2°%)
• Table cache definition should be upper that total number of tables or in autoresizing mode
• Percentage of open file and open file limit (<85%)
• Percentage of table locks (<95%)
• Percentage of binlog cache lock (<90%)

MySQLTuner global buffer information

• Key Buffer
• Max Tmp Table
• Per Thread Buffer
  • Read Buffer
  • Read RND Buffer
  • Sort Buffer
  • Thread stack
  • Join Buffer
  • Binlog Cache Buffers size if activated

MySQLTuner query cache checks

• Is Query cache activated?
  • Query Cache Buffers
  • Query Cache DISABLED, ALL REQUEST or ON DEMAND
  • Query Cache Size
  • Query cache hit ratio (cache efficiency)

MySQLTuner memory checks

• Get total RAM/swap
• Is there enough memory for max connections reached by MySQL?
• Is there enough memory for max connections allowed by MySQL?
• Max percentage of memory used (<85%)

MySQLTuner slow queries checks

• Percentage of Slow queries (<5%)

MySQLTuner replication checks

• Is server replication configured as slave?
• SQL replication thread running?
• IO replication thread running?
• Replication lag in seconds (Seconds_behind_master)
• Is Slave configured in read only?
• Replication type ROW, MIX, STMT
• Replication Semisync master
• Replication Semisync slave
• XA support activated
• Replication started?

MySQLTuner InnoDB information

• InnoDB Buffer Pool Size
  • If possible, innodb_buffer_pool_size should be greater than data and index size for Innodb Table
  • Innodb_buffer_pool_size should be around 75% to 80% of the available system memory.

• InnoDB Buffer Pool Instances

  • MySQL needs 1 instance per 1Go of Buffer Pool
  • innodb_buffer_pool instances = round(innodb_buffer_pool_size / 1Go)

  • innodb_buffer_pool instances must be equal to or lower than 64

  • A bug in MySQL 5.6 causes SHOW VARIABLES to report an innodb_buffer_pool_instances value of 8 when innodb_buffer_pool_size is less than 1GB and only one buffer pool instance is present (Bug #18343670).

• InnoDB Buffer Pool Usage

  • If more than 20% of InnoDB buffer pool is not used, raise an alert.
• InnoDB Buffer Pool Log Size
  • InnoDB total log file size should be 25% of innodb_buffer_pool_size
• InnoDB Read efficiency
  • Ratio of read without locks
• InnoDB Write efficiency
  • Ratio of write without locks
• InnoDB Log Waits
  • Checks that no lock is used on Innodb Log.
• InnoDB Chunk Size
  • Check InnoDB Buffer Pool size is a multiple of InnoDB Buffer Pool chunk size * InnoDB Buffer Pool instances

MySQLTuner AriaDB information

• Is Aria indexes size greater than page cache size?
• Page cache read hit ratio (>95%)
• Page cache write hit ratio (>95%)

MySQLTuner MyISAM information

• Key buffer usage (>90%)
• Is MyISAM indexes size is greater than key buffer size ?
• Key buffer read hit ratio (>95%)
• Key buffer write hit ratio (>95%)

MySQLTuner Galera information

• wsrep_ready cluster is ready
• wsrep_connected node is connected to other nodes
• wsrep_cluster_name is defined.
• wsrep_node_name is defined.
• Check thet notification script wsrep_notify_cmd is defined
• wsrep_cluster_status PRIMARY /NON PRIMARY.
  • PRIMARY : Coherent cluster
  • NO PRIMARY : cluster gets several states
• wsrep_local_state_comment: Node state
  • SYNCED (uptodate),
  • DONOR (sending information to another node)
  • Joiner (try to reach cluster group)
  • SYNCED state able to read/write
• wsrep_cluster_conf_id configuration level must be identical in all nodes
• wsrep_slave_thread is between 3 or 4 times number of CPU core.
• gcs.limit should be equal to wsrep_slave_threads * 5
• gcs.fc_factor should be equal to 0.8
• Flow control fraction should be lower than 0.02 (wsrep_flow_control_paused < 0.02)
• wsrep_last_commited committed level must be identical in all nodes
• Look for tables without primary keys
• Look for non InnoDB tables for Galera
• Variable innodb_flush_log_at_trx_commit should be set to 0.
• Check that there are 3 or 5 members in Galera cluster.
• Check that xtrabackup is used for SST method with wsrep_sst_method variable.
• Check variables wsrep_OSU_method is defined to TOI for updates.
• Check that there is no certification failures controlling wsrep_local_cert_failures status.

MySQLTuner TokuDB information

• tokudb_cache_size
• tokudb_directio
• tokudb_empty_scan
• tokudb_read_block_size
• tokudb_commit_sync
• tokudb_checkpointing_period
• tokudb_block_size
• tokudb_cleaner_iterations
• tokudb_fanout

MySQLTuner XtraDB information

• wsrep_node_name
• wsrep_cluster_name
• wsrep_cluster_address

MySQLTuner Connect information

• connect_type
• connect_user
• connect_host

MySQLTuner Spider information

• spider_nodes
• spider_direct_io

MySQLTuner RocksDb information

• rocksdb_block_cache_size
• rocksdb_max_open_files

MySQLTuner Thread pool information

• thread_pool_size between 16 to 36 for Innodb usage
• thread_pool_size between 4 to 8 for MyISAM usage

MySQLTuner performance schema and sysschema information

• Check that Performance schema is activated for 5.6+ version
• Check that Performance schema is deactivated for 5.5- version
• Check that Sys schema is installed
• Sys Schema version
• Top user per connection
• Top user per statement
• Top user per statement latency
• Top user per lock latency
• Top user per full scans
• Top user per row_sent
• Top user per row modified
• Top user per io
• Top user per io latency
• Top host per connection
• Top host per statement
• Top host per statement latency
• Top host per lock latency
• Top host per full scans
• Top host per rows sent
• Top host per rows modified
• Top host per io
• Top 5 host per io latency
• Top IO type order by total io
• Top IO type order by total latency
• Top IO type order by max latency
• Top Stages order by total io
• Top Stages order by total latency
• Top Stages order by avg latency
• Top host per table scans
• InnoDB Buffer Pool by schema
• InnoDB Buffer Pool by table
• Process per allocated memory
• InnoDB Lock Waits
• Threads IO Latency
• High Cost SQL statements
• Top 5% slower queries
• Top 10 nb statement type
• Top statement by total latency
• Top statement by lock latency
• Top statement by full scans
• Top statement by rows sent
• Top statement by rows modified
• Use temporary tables
• Unused Indexes
• Full table scans
• Latest file IO by latency
• File by IO read bytes
• File by IO written bytes
• File per IO total latency
• File per IO read latency
• File per IO write latency
• Event Wait by read bytes
• Event Wait by write bytes
• Event per wait total latency
• Event per wait read latency
• Event per wait write latency
• Top 15 most read indexes
• Top 15 most modified indexes
• Top 15 high select latency index
• Top 15 high insert latency index
• Top 15 high update latency index
• Top 15 high delete latency index
• Top 15 most read tables
• Top 15 most modified tables
• Top 15 high select latency tables
• Top 15 high insert latency tables
• Top 15 high update latency tables
• Top 15 high delete latency tables
• Redundant indexes
• Tables not using InnoDb buffer
• Top 15 Tables using InnoDb buffer
• Top 15 Tables with InnoDb buffer free
• Top 15 Most executed queries
• Latest SQL queries in errors or warnings
• Top 20 queries with full table scans
• Top 15 reader queries (95% percentile)
• Top 15 row look queries (95% percentile)
• Top 15 total latency queries (95% percentile)
• Top 15 max latency queries (95% percentile)
• Top 15 average latency queries (95% percentile)
• Top 20 queries with sort
• Last 50 queries with sort
• Top 15 row sorting queries with sort
• Top 15 total latency queries with sort
• Top 15 merge queries with sort
• Top 15 average sort merges queries with sort
• Top 15 scans queries with sort
• Top 15 range queries with sort
• Top 20 queries with temp table
• Last 50 queries with temp table
• Top 15 total latency queries with temp table
• Top 15 queries with temp table to disk
• Top 15 class events by number
• Top 30 events by number
• Top 15 class events by total latency
• Top 30 events by total latency
• Top 15 class events by max latency
• Top 30 events by max latency

MySQLTuner Cloud and SSH integration

• Cloud mode: Activated with --cloud or --azure.
• SSH Support: Allows running system commands on a remote host via SSH.
• SSH Authentication: Supports identity files (--ssh-identity-file) and passwords (--ssh-password using sshpass).
• Remote Resource Detection: Automatically detects RAM and Swap on the remote host if SSH is used.

MySQLTuner Container and Systemd log integration

• Docker/Podman: Automatically detects logs from the most relevant database container based on ports and names.
• Kubectl: Supports retrieving logs from Kubernetes pods.
• Systemd: Supports retrieving logs from the systemd journal using journalctl.
• Explicit Container: Can be specified using --container <type>:<name>.

MySQLTuner Infrastructure Awareness

• Storage Type: Detects SSD/NVMe vs HDD by checking /sys/block/*/queue/rotational.
• Hardware Architecture: Detects ARM64/Graviton vs x86_64.
• tuning Adjustments: Suggestions for innodb_flush_neighbors and innodb_io_capacity based on storage type.

MySQLTuner Cloud Autodiscovery

• AWS: Detects RDS/Aurora via @@version_comment and @@aurora_version.
• GCP: Detects Cloud SQL via @@version_comment and @@cloudsql_instance_id.
• Azure: Detects Flexible Server via @@version_comment.
• DigitalOcean: Detects Managed MySQL via @@version_comment.

MySQLTuner Query Anti-Pattern Detection

• Full Scans: Analyzes events_statements_summary_by_digest for queries with high no_index_used or no_good_index_used.
• Disk Temp Tables: Identifies queries creating excessive internal disk-based temporary tables.

MySQLTuner Sysbench Integration

• Metrics: Parses sysbench output for QPS, TPS, and latency (Avg/95th/Max).
• Comparison: Includes baseline performance metrics in the final report.

MySQLTuner Historical Trend Analysis

• JSON Snapshots: Ingests JSON output from previous runs via –compare-file.
• Comparisons: Provides trends for QPS and Data Growth between snapshots.