2019-10-12 18:39:44

---

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_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	Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. Hint: increase for a server with heavy write activity, or decrease it for a MySQL server with unchanging data.
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.
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.

---

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.

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.

---

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

---

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)

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

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

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

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).

---

Key Buffer Size: 16.0 MiB

Read Requests: 0, Write Requests: 0

---

long_query_time: 1

Slow queries rate: 65/s

Checking the slow queries log recommended.

Checking the slow queries log recommended.

---

---

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

---

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

---

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

---

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

---

Open_tables / table_open_cache: 100.0%

Opened table definitions rate: 2/s

---

Sort scan: 57/s

Sort merge passes: 0/s

Sorted rows: 82/s

Query cache efficiency: 32.6%

Query cache usage: 83.9%

Query cache hits / inserts ratio: 100.7%

---

generated in 0.0389s