MySQL Health Report



2019-10-17 16:17:07


Contents
  1. Brief Overview
  2. Variables Overview
  3. Timeouts
  4. InnoDB Variables
  5. Global Buffers
  6. Session Buffers
  7. Statement distribution
  8. InnoDB Engine
  9. MyISAM Engine
  10. Slow Queries
  11. Indexes
  12. JOINs
  13. Locks
  14. Threads
  15. Temporary Tables
  16. Table Open Cache
  17. Sorting
top

Brief Overview


  • Version: 5.6.41-log
  • Vendor: MySQL
  • OS: Linux x86_64
  • Max possible memory usage: 35.6 GiB
  • MySQL Network bandwidth, Out: 45.2 KiB/s, In: 3.3 KiB/s
  • Reads / Writes ratio = 100.0/0.0 %
  • Connections: 2/s
  • Average connection period: 0.38s
  • Queries per connection: 9
  • Average data size sent per connection: 6.6 KiB
  • Aborted clients Rate: 0.0/s, Total: 19
  • Fraction of Stored Routines queries: 0%
  • Threads running: 1, Threads connected: 6
  • Max connections: 2000
top

Variables Overview


back_log 450 The number of outstanding connection requests MySQL can have. Increase for large number of connections.
binlog_format STATEMENT The supported values for type are: STATEMENT, ROW, MIXED.
event_scheduler

OFF

 Enables or disables, and starts or stops the Event Scheduler.
expire_logs_days 0 The number of days for automatic binary log file removal.
gtid_mode

OFF

 The variable specifies whether global transaction identifiers (GTIDs) are used to identify transactions.
innodb_log_file_size 48.0 MiB The size of each log file in a log group. Hint: Start with 128M – 2G.
innodb_page_size 16.0 KiB Specifies the page size for InnoDB tablespaces. Hint: Use default unless you know why it needs to be changed.
log_bin

OFF

 Shows the status of binary logging on the server.
long_query_time 10.000000 Queries longer than this value go to the Slow Query Log. Hint: Depends on your environment.
max_allowed_packet 4.0 MiB Limits maximum query size. Hint: 16MB should be enough for most cases.
max_connections 2000 The maximum permitted number of simultaneous client connections.
max_heap_table_size 16.0 MiB The maximum size to which user-created MEMORY tables are permitted to grow. Hint: Set the same value as tmp_table_size.
old_passwords 0 This variable controls the password hashing method used by the PASSWORD() function. Hint: Keep disabled for the sake of security.
open_files_limit 10000 The number of file descriptors available to mysqld. Hint: 65535. Ajust the system limits accordingly.
query_cache_type ON Set the query cache type (ON, OFF, DEMAND). Hint: OFF.
skip_name_resolve

OFF

 If it is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers. Hint: OFF, resolution is slow.
sync_binlog 0 How often the MySQL server synchronizes the binary log to disk. Hint: 1 is safer, 0 is faster (safe if you have RAID with BBU).
table_definition_cache 1400 The number of table definitions (from .frm files) that can be stored in the definition cache. Hint: For most cases set to number of tables plus 10%.
table_open_cache 2000 The number of open tables for all threads. Hint: Start with with 4096.
table_open_cache_instances 1 The number of open tables cache instances. Hint: 16 is the default value in version 5.7.
thread_cache_size 28 How many threads the server should cache for reuse. Hint: 50-100 should be good. Auto set on 5.6.
tmp_table_size 16.0 MiB The maximum size of internal in-memory temporary tables. Hint: See Temporary Tables section.
tmpdir /tmp The directory to use for creating temporary files.
tx_isolation REPEATABLE-READ The default transaction isolation level. Defaults to REPEATABLE-READ.
version 5.6.41-log The MySQL server version.
version_compile_machine x86_64 The type of the server binary.
version_compile_os Linux The type of operating system on which MySQL was built.
top

Timeouts


connect_timeout 10 The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
net_read_timeout 30 The number of seconds to wait for more data from a connection before aborting the read.
net_write_timeout 60 The number of seconds to wait for a block to be written to a connection before aborting the write.
wait_timeout 28800 The number of seconds the server waits for activity on a noninteractive connection before closing it.
top

InnoDB Variables


innodb_adaptive_hash_index ON Whether the InnoDB adaptive hash index is enabled or disabled. Hint: Usually should be ON.
innodb_adaptive_max_sleep_delay 150000 Permits InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload.
innodb_buffer_pool_instances 1 The number of regions that the InnoDB buffer pool is divided into. Hint: For production server with a lot of RAM use multple instances, defaults to 8 on 5.6, and 16 on 5.7.
innodb_change_buffer_max_size 25
innodb_checksum_algorithm innodb InnoDB can use checksum validation on all tablespace pages read from disk to ensure extra fault tolerance against hardware faults or corrupted data files. Hint: crc32 is faster.
innodb_checksums ON InnoDB can use checksum validation on all tablespace pages read from disk to ensure extra fault tolerance against hardware faults or corrupted data files. Hint: Keep enabled.
innodb_doublewrite ON When enabled (the default), InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. Hint: ON for production servers.
innodb_file_format Antelope Antelope is an older one, Barracuda is the newer file format, which supports COMPRESSED and DYNAMIC row formats. Hint: Go with Barracuda.
innodb_file_per_table ON Tells InnoDB to use a separate .ibd file for each table. Hint: Use ON.
innodb_flush_log_at_trx_commit 1 Controls the balance between strict ACID compliance for commit operations and higher performance. Hint: 1 (flush & sync) safer, 2 (flush) – better performance, worser reliability, 0 (neither) – the faster, least reliable.
innodb_flush_neighbors 1 Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent. Hint: Use 0 for SSD, 0 or 2 for HDD is better.
innodb_io_capacity 200 If you use fast drives you might benefit from increasin of this parameters. Hint: SSD: 3000+, HDD: 100 IOPS
innodb_io_capacity_max 2000 If flushing activity falls behind, InnoDB can flush more aggressively than the limit imposed by innodb_io_capacity. Hint: Increasing allows to improve the performance for write-heavy workloads.
innodb_log_files_in_group 2 The number of log files in the log group. Hint: Leave default which is 2.
innodb_lru_scan_depth 1024 A parameter that influences the algorithms and heuristics for the flush operation for the InnoDB buffer pool. Hint: Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
innodb_max_dirty_pages_pct 75 InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.
innodb_max_dirty_pages_pct_lwm 0 Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio.
innodb_old_blocks_time 1000 Non-zero values protect against the buffer pool being filled by data that is referenced only for a brief period, such as during a full table scan. Hint: Go with 1000.
innodb_open_files 1000 How many files InnoDB will keep open while working in innodb_file_per_table mode.
innodb_random_read_ahead OFF Enables the random read-ahead technique for optimizing InnoDB I/O.
innodb_read_ahead_threshold 56 Controls the sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer pool.
innodb_read_io_threads 4 The number of I/O threads for read operations in InnoDB. Hint: Start with 4. More for heavy IO workdload.
innodb_stats_on_metadata OFF InnoDB updates non-persistent statistics. Hint: Keep OFF.
innodb_stats_persistent ON Specifies whether InnoDB index statistics are persisted to disk.
innodb_thread_concurrency 0 InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable. Hint: Let the server control the value unless you have concurrency issues.
innodb_thread_sleep_delay 10000 Defines how long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
innodb_write_io_threads 4 The number of I/O threads for write operations in InnoDB. Hint: Start with 4. More for heavy IO workdload.
top

Global Buffers


key_buffer_size 2.0 MiB Index blocks for MyISAM tables are buffered and are shared by all threads.
innodb_buffer_pool_size 2.0 GiB The memory area where InnoDB caches table and index data. Hint: Rule of thumb: 70-80% of RAM.
innodb_log_buffer_size 8.0 MiB The size of the buffer that InnoDB uses to write to the log files on disk. Hint: 4MB-128MB.
innodb_additional_mem_pool_size 8.0 MiB The size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
query_cache_size 50.0 MiB The amount of memory allocated for caching query results. Hint: Set to 0.
binlog_cache_size 32.0 KiB The size of the cache to hold changes to the binary log during a transaction.
top

Session Buffers


join_buffer_size 256.0 KiB The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Hint: 8M should be enough.
net_buffer_length 16.0 KiB Each client thread is associated with a connection buffer and result buffer.
read_buffer_size 128.0 KiB Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.
sort_buffer_size 256.0 KiB Each session that must perform a sort allocates a buffer of this size. Hint: Defaults to 256K on 5.6.
myisam_sort_buffer_size 8.0 MiB The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
read_rnd_buffer_size 256.0 KiB This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization. Hint: Start wit 16MB.
thread_stack 256.0 KiB The stack size for each thread.
top

Statement distribution


SELECT 24% 6/s
CHANGE_DB 10% 3/s
SET_OPTION 8% 2/s
UPDATE 5% 1/s

top

InnoDB Engine


It seems the Buffer pool usage is not optimal.

All pages dirty counter: 0/s

Readahead efficiency: 100.0%

Readahead rate: 1.6 KiB/s

The number of doublewrite operations rate: 0.15/s

Doublewrite buffer write hit ratio: 100.0%

Pool IO activity


top

MyISAM Engine


Key Buffer Size: 2.0 MiB

Read Requests: 0

Write Requests: 2

top

Slow Queries


long_query_time: 10

Slow queries rate: 0/s

top

Indexes


top

JOINs


top

Locks


Table Locks Waited: 0.0%

Number of times operations on InnoDB tables had to wait for a row lock: 0.0/s

top

Threads


Threads running: 1

Threads connected: 6

top

Temporary Tables


Created_tmp_disk_tables value is too high. Recommended: consider increasing join_buffer_size / sort_buffer_size, re-factor queries, tmpdir in RAM is also an option.

Tmp table created: 0/s

Created_tmp_disk_tables / Questions ratio: 0.0

Tmp disk tables created: 0/s

top

Table Open Cache


Open_tables / table_open_cache: 32.9%

Cache miss rate: 0.01/s

Cache overflows: 0.0/s

top

Sorting


Sort scan: 0/s

Sort merge passes: 0/s

Sorted rows: 70/s

top

Miscellaneous


Query cache efficiency: 62.0%

Query cache usage: 65.3%

Query cache hits / inserts ratio: 198.5%


generated in 0.0545s