MySQL Health Report



2019-10-12 18:39:44

Contents

top

Brief Overview


top

Variables Overview


back_log 80 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 10 The number of days for automatic binary log file removal.
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 1.000000 Queries longer than this value go to the Slow Query Log. Hint: Depends on your environment.
max_allowed_packet 32.0 MiB Limits maximum query size. Hint: 16MB should be enough for most cases.
max_connections 151 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

OFF

This variable controls the password hashing method used by the PASSWORD() function. Hint: Keep disabled for the sake of security.
open_files_limit 16364 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 400 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.
thread_cache_size 8 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 10.1.26-MariaDB-0+deb9u1 The MySQL server version.
version_compile_machine x86_64 The type of the server binary.
version_compile_os debian-linux-gnu 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_hash_index_partitions 1 Specifies the number of partitions to use in the adaptive hash search process. Hint: The default value is good for most cases.
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 8 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_buffer_pool_populate OFF XtraDB will preallocate pages in the buffer pool on starting up so that NUMA allocation decisions are made while the buffer cache is still clean. XtraDB only.
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.000000 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.001000 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 2000 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 16.0 MiB Index blocks for MyISAM tables are buffered and are shared by all threads.
innodb_buffer_pool_size 1.0 GiB The memory area where InnoDB caches table and index data. Hint: Rule of thumb: 70-80% of RAM.
innodb_log_buffer_size 16.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 16.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 2.0 MiB Each session that must perform a sort allocates a buffer of this size. Hint: Defaults to 256K on 5.6.
myisam_sort_buffer_size 128.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 192.0 KiB The stack size for each thread.

top

Statement distribution


SELECT 28% 155/s
STMT_CLOSE 25% 140/s
STMT_EXECUTE 25% 140/s
STMT_PREPARE 25% 140/s
CHANGE_DB 17% 95/s
SET_OPTION 1% 8/s

top

InnoDB Engine


All pages dirty counter: 0/s

Readahead efficiency: 100.0%

Readahead rate: 16.0 KiB/s

The number of doublewrite operations rate: 0.79/s

Doublewrite buffer write hit ratio: 100.0%

The Redo Log Occupancy: 0.0% (checkpoint age: 1.1 KiB, redo log: 96.0 MiB)

Semaphores

Spin rounds per wait: 7.5 mutex (rate: 30/s)

RW-shared: 28.0 (rate: 23/s)

RW-excl: 28.0 (rate: 2/s)

Pool IO activity


It seems you are using the database as a queue. Usually it's not the best solution. Using a real queueing system is recommended (ActiveMQ, RabbitMQ, or Gearman).


top

MyISAM Engine


Key Buffer Size: 16.0 MiB

Read Requests: 0

Write Requests: 0

top

Slow Queries


long_query_time: 1

Slow queries rate: 65/s

Checking the slow queries log recommended.

Checking the slow queries log recommended.


top

Indexes



top

JOINs


Unless you have small tables, adding indexes and queries optimization is recommended.


top

Locks


Table Locks Waited: 0.0%

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

Deadlocks rate: 0.0/s


top

Threads


Threads cache hit ratio could be better, it means the server is creating new threads. Recommended: the thread_cache_size value tuning.

Threads running: 1

Threads connected: 9


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: 15/s

Created_tmp_disk_tables / Questions ratio: 0.02

Tmp disk tables created: 5/s


top

Table Open Cache


Open_tables / table_open_cache: 100.0%

Opened table definitions rate: 2/s


top

Sorting


Sort scan: 57/s

Sort merge passes: 0/s

Sorted rows: 82/s

top

Miscellaneous


Query cache efficiency: 32.6%

Query cache usage: 83.9%

Query cache hits / inserts ratio: 100.7%



generated in 0.0147s