0.0/s
,
Total: 19
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. |
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. |
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. |
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. |
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. |
SELECT | 24% | 6/s |
CHANGE_DB | 10% | 3/s |
SET_OPTION | 8% | 2/s |
UPDATE | 5% | 1/s |
Buffer Pool Size: 2.0 GiB, usage: 39%
It seems the Buffer pool usage is not optimal.
Buffer Pool Read efficiency: 99%
All pages dirty counter: 0/s
Readahead efficiency: 100.0%
Readahead rate: 1.6 KiB/s
Average time to lock a row: 0 millisec
The number of doublewrite operations rate: 0.15/s
Doublewrite buffer write hit ratio: 100.0%
innodb_log_file_size
average time to cycle over the log: 19 hours, 59 mins (log_group_capacity: 96.0 MiB)
rows inserted
rows deleted
rows updated
Write requests to data that had to hit disk: 35% (4/s)
Write requests to log that had to hit disk: 1% (waits 0.0%)
Logical R/W requests rate
8.9 KiB/s
read142.7 KiB/s
written1.4 KiB/s
log writtenRows deleted / Rows inserted: 2%
Key Buffer Size: 2.0 MiB
Read Requests: 0
Write Requests: 2
key buffer read efficiency
key buffer write efficiency
key buffer usage
key buffer read rate
long_query_time: 10
Slow queries rate: 0/s
Slow queries / Queries ratio: 0%
Slow queries / Questions ratio: 0%
Efficiency: 45%
Average size of a full table scan: 1k
Average rows read per select: 2k
... without using indexes: 0% [Select_full_join]
... without keys that check for key usage after each row: 0% [Select_range_check]
... used a range search on a reference table: 0% [Select_full_range_join]
... that did a full scan of the first table: 3% of SELECTs [Select_scan]
... that used ranges on the first table: 1% of SELECTs
In most cases this is not a critical issue.
Table Locks Waited: 0.0%
Number of times operations on InnoDB tables had to wait for a row lock: 0.0/s
The threads cache hit rate: 100.0%
Frequency of threads creating: 0.0/s
Threads running: 1
Threads connected: 6
Created disk tmp tables ratio (tmp_table_size efficiency): 23%
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
Open table rate: 0/s
Open_tables / table_open_cache: 32.9%
Cache miss rate: 0.01/s
Cache overflows: 0.0/s
Sort scan: 0/s
Sort merge passes: 0/s
Sorted rows: 70/s
Query cache efficiency: 62.0%
Query cache usage: 65.3%
Query cache hits / inserts ratio: 198.5%
That's all! We'd love to hear your feedback. Drop us a line!