Percona Mysql Variables collect
Mysql Variables--Percona Monitor template for Cacti1. InnoDB Adaptive hash Index
This variable is enabled by default.The feature known as the adaptive hash index (AHI) lets InnoDB perform more like an in-memory database on systems with appropriate combinations of workload and ample memory for the buffer pool, without sacrificing any transactional features or> Links:http://dev.mysql.com/doc/refman/5.5/en/innodb-adaptive-hash.html
2. InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory.It manages the pool as a list, using a variation of the least recently used (LRU) algorithm.
InnoDB Buffer Pool Activity
The InnoDB Buffer Pool Activity shows activity inside the buffer pool: pages created, read, and written. You can consider it roughly equivalent to the Handler graphs. If you see a sudden change in the graph, you should try to trace it to some change in your application.
InnoDB Buffer Pool>
Unlike MyISAM, uses a buffer pool to cache both indexes androw data. The bigger you set this the less disk I/O is needed toaccess data in tables. On a dedicated database server you may set thisparameter up to 80% of the machine physical memory> 3. InnoDB Checkpoint Age
eg: show engine innodb status\G
---
LOG
---
Log sequence number 120483417682
Log flushed up to 120483389516
Last checkpoint at120283867762
Max checkpoint age 434154333
Checkpoint age target 420587011
Modified age 199424973
Checkpoint age 199549920
0 pending log writes, 0 pending chkp writes
371933445 log i/o's done, 369.29 log i/o's/second
The InnoDB Checkpoint Age shows the InnoDB checkpoint age, which is the same thing as the number of uncheckpointed bytes, and thus the amount of log that will need to be scanned to perform recovery if there’s a crash. If the uncheckpointed bytes begin to approach the combined> 4. InnoDB Current Lock Waits
The InnoDB Current Lock Waits graph shows the total number of seconds that InnoDB transactions have been wait-ing for locks. This is> 5.eg: show engine stat\G
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 , aio writes: 0 ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
10183 OS file reads, 399868020 OS file writes, 5126216 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 404.19 writes/s, 3.36 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:> merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table> 593.89 hash searches/s, 78.14 non-hash searches/s
InnoDB I/O
The InnoDB I/O Activity shows InnoDB’s I/O activity: file reads and writes, log writes, and fsync() calls. This might help diagnose the source of I/O activity on the system. Some of this can be influenced with InnoDB settings, especially innodb_flush_log_at_trx_commit.
InnoDB Pending
The InnoDB I/O Pending shows InnoDB’s pending synchronous and asynchronous I/O operations in various parts of the engine. Pending I/O is not> InnoDB Insert Buffer
The InnoDB Insert Buffer shows information about InnoDB’s insert buffer: inserts, merge operations, and merged records. This is not generally actionable, because the insert buffer is not user-configurable in standard MySQL. However, you can use it to diagnose certain kinds of performance problems, such as furious disk activity after you stop the server from processing queries, or during particular types of queries that force the insert buffer to be merged into the indexes. (The insert buffer is sort of a delayed way of updating non-unique secondary indexes.) If the insert buffer is causing problems, then Percona Server might help, because it has some configuration parameters for the buffer.
InnoDB Insert Buffer Usage
The InnoDB Insert Buffer Usage shows the total cells in the insert buffer, and the used and free cells. This is diagnostic only, as in the previous graph. You can use it to see the buffer usage, and thus correlate with server activity that might be hard to explain otherwise.
InnoDB Internal Hash Memory Usage
The InnoDB Internal Hash Memory Usage shows how much memory InnoDB uses for various internal hash structures: the adaptive hash index, page hash, dictionary cache, filesystem, locks, recovery system, and thread hash. This is available only in Percona Server, and these structures are generally not configurable. However, you might use it to diagnose some kinds of performance problems, such as much greater than expected memory usage. In standard InnoDB, the internal data dictionary tends to consume large amounts of memory when you have many tables, for example. Percona Server lets you control that with some features that are similar to MySQL’s table cache.
6. InnoDB Lock Structures
The InnoDB Lock Structures graph shows how many lock structures InnoDB has internally. This should correlate roughly to the number of row locks transactions are currently holding, and might be useful to help diagnose increased lock contention. There is no hard rule about what’s a good or bad number of locks, but in case many transactions are waiting for locks, obviously fewer is better.
7. InnoDB Log
eg: show variables like '%innodb%'
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 268435456 |
$results['unflushed_log']
= big_sub($results['log_bytes_written'], $results['log_bytes_flushed']);
The InnoDB Log Activity shows InnoDB log activity: the log buffer> 8. InnoDB Memory Allocation
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_size | 26843545600 |
+---------------------------------------+-------------+
The InnoDB Memory Allocation graph shows InnoDB’s total memory allocation, and how much of that is in the additional pool (as opposed to the buffer pool). If a lot of memory is in the additional memory pool, you might suspect problems with the internal data dictionary cache; see above for more on this. Unfortunately, in standard InnoDB it’s a bit hard to know where the memory really goes.
9.
| Innodb_current_row_locks | 0 |
| Innodb_row_lock_time | 14 |
| Innodb_row_lock_time_avg | 7 |
| Innodb_row_lock_time_max | 8 |
| Innodb_row_lock_waits | 2 |
| Innodb_rows_deleted | 180388150 |
| Innodb_rows_inserted | 230003654 |
| Innodb_rows_read | 222202010382 |
| Innodb_rows_updated | 750096 |
InnoDB Row Lock Time
The InnoDB Row Lock Time shows the amount of time, in milliseconds, that InnoDB has waited to grant row locks. This comes from the Innodb_row_lock_time status variable.
InnoDB Row Lock Waits
The InnoDB Row Lock Waits shows the number of times that InnoDB has waited to grant row locks. This comes from the Innodb_row_lock_waits status variable.
InnoDB Row Operations
The InnoDB Row Operations shows row operations InnoDB has performed: reads, deletes, inserts, and updates. These should be roughly equivalent to Handler statistics, with the exception that they can show internal operations not reflected in the Handler statistics. These might include foreign key operations, for example.
10. eg: show engine status\G
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2262226, signal count 197284039
Mutex spin waits 318818326, rounds 300667430, OS waits 883302
RW-shared spins 72317671, rounds 236072157, OS waits 882758
RW-excl spins 4777376, rounds 240638780, OS waits 371245
Spin rounds per wait: 0.94 mutex, 3.26 RW-shared, 50.37 RW-excl
InnoDB Semaphore Wait Time
InnoDB Semaphore Waits
InnoDB Semaphores
The InnoDB Semaphores shows information on InnoDB semaphore activity: the number of spin rounds, spin waits, and OS waits. You might see these graphs spike during times of high concurrency or contention. These graphs basically indicate different types of activity involved in obtaining row locks or mutexes, which are causes of poor scaling in some cases.
11. InnoDB Tables In Use
The InnoDB Tables In Use shows how many tables InnoDB has in use and how many are locked. If there are spikes in these graphs, you’ll probably also see spikes in LOCK WAIT and other signs of contention amongst queries InnoDB Transactions Active/Locked.
12. InnoDB Transactions
------------
TRANSACTIONS
------------
Trx> Purge done for trx's n:o < B45286D undo n:o < 0
History list length 2254
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread> show engine innodb status
---TRANSACTION B45C66F, not started
MySQL thread> The InnoDB Transactions shows information about transactions within InnoDB.
Total transactions ever created is the internal transaction counter.
The length of the history list shows how old the oldest unpurged transaction is. If this grows large, you might have transactions that are staying open a very long time. This means InnoDB can’t purge old row versions. It will get bloated and slow as a result. Commit your transactions as quickly as you can. The example graph is slightly outdated; a newer version of the templates has moved some of the items to the Ac-tive/Locked graph instead.
This template shows InnoDB transaction counts:
An active transaction is a transaction that’s currently open. It’s possible for transactions to be in “not started” status, which reallymeans that this connection toMySQL doesn’t actually have a transaction open. A transaction is active between BEGIN and COMMIT. It’s also active whilst a query is running,> A locked transaction is in LOCK WAIT status. This usually means it’s waiting for a row lock, but in some cases could be a table lock or an auto-increment lock. If you start to see lock waits, you need to check SHOW INNODB STATUS and search for the string “LOCK WAIT” to examine what’s waiting. Lock waits can come from several sources, including too much contention on busy tables, queries accessing data through scans on different indexes, or bad query patterns such as SELECT .. FOR UPDATE.
The current transactions are all transactions, no matter what status (ACTIVE, LOCK WAIT, not started, etc).
The number of read views open shows how many transactions have a consistent snapshot of the database's contents, which is achieved by MVCC.
13. MyISAM Indexes
The MyISAM Indexes shows information about how many logical and physical reads and writes took place to MyISAM indexes. Probably the most important one is the physical reads. The ratio between logical and physical reads is not very useful to monitor. Instead, you should look at the absolute number of physical reads per second, and compare it to what your disks are capable of. (RRDTool normalizes everything to units of seconds, so this graph’s MyISAM Key Cache absolute value is the number you need.)
14. Mysql Key Cache
The MyISAM Key Cache shows the> 15. Mysql Binary/Relay Logs
The MySQL Binary/Relay logs shows information about the space used by the server binary and> 16. Mysql Command Counter
The MySQL Command Counters shows counters for various MySQL commands. These are derived from the Com_ counters from SHOW STATUS. If there is a change in the graph, it indicates that something changed in the application.
17. Mysql Connections
The MySQL Connections graph shows information about the connection parameters and counters inside MySQL: connections permitted, connections used, connections aborted, clients aborted, current connections, and connections created. Probably the most interesting are the aborted clients and connections, which might indicate a malfunction-ing application that disconnects ungracefully, an> 18. Mysql Files and Tables
The MySQL Files and Tables graph shows status of MySQL’s table cache and file handles: the> 19. Mysql Handlers
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 419 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 397 |
+----------------------------+-------+
The MySQL Handlers shows the various Handler counters, which record how many operations MySQL has done through the storage engine API. Changes in indexing will probably show up clearly here: a query that used to do a table scan but now has a good index to use will cause different Handler calls to be used, for example. If you see sudden changes, it probably correlates with schema changes or a different mixture of queries.
20. Mysql Network Traffic
Mysql Network Traffic shows network traffic to and from the MySQL Server.
21. Mysql Processlist
The MySQL Processlist shows the number (count) of queries from SHOW PROCESSLIST in given statuses. Some of the statuses are lumped together into the “other” category. This is a “scoreboard” type of graph. In most cases, you should see mostly Other, or a few of the statuses like “Sending data”. Queries in Locked status are the hallmark of a lot of MyISAM table locking. Any mixture of statuses is possible, and you should investigate sudden and systemic changes.
22.
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
Mysql Query Cache
The MySQL Query Cache graph shows information about the query cache inside MySQL: the number of queries in the cache, inserted, queries not cached, queries pruned due to low memory, and cache hits.
Mysql Query Cache Memory
The MySQL Query Cache Memory shows information on the query cache’s memory usage: total> 23.
debug('Getting query time histogram');
$i = 0;
$result = run_query(
"SELECT `count`, total * 1000000 AS total "
. "FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME "
. "WHERE `time`'TOO LONG'",
$conn);
Mysql Query Response Time
The MySQL Query Response Time (Microseconds) displays a histogram of the query response time distribution available in Percona Server. Because the time units are user-configurable, exact unit labels are not displayed; rather, the graph simply shows the values. There are 14 time units by default in Percona Server, so there are 13 entries on the graph (the 14th is non-numeric, so we omit it).
Mysql Query Time Histogram
The MySQL Query Time Histogram (Count) displays a histogram of the query response time distribution avail-able in Percona Server. Because the time units are user-configurable, exact unit labels are not displayed; rather, the graph simply shows the values. There are 14 time units by default in Percona Server, so there are 13 entries on the graph (the 14th is non-numeric, so we omit it).
24. Mysql Select Types
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 27 |
+------------------------+-------+
The MySQL Select Types graph shows information on how many of each type of select the MySQL server has performed: full join, full range join, range, range check, and scan. Like the Handler graphs, these show different types of execution plans, so any changes should be investigated. You should strive to have zero Select_full_join queries!
25. Mysql Sorts
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
+-------------------+-------+
The MySQL Sorts shows information about MySQL sort operations: rows sorted, merge passes, and number of sorts triggered by range and scan queries. It is easy to over-analyze this data. It is not useful as a way to determine whether the server configuration needs to be changed.
26. Mysql Table Locks
| Table_locks_immediate | 188676134 |
| Table_locks_waited | 35 |
The MySQL Table Locks shows information about table-level lock operations inside MySQL: locks waited, locks granted without waiting, and slow queries. Locks that have to wait are generally caused by MyISAM tables. Even InnoDB tables will cause locks to be acquired, but they will generally be> 27. Mysql Temporary
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 2042|
| Created_tmp_tables | 29 |
+-------------------------+-------+
The MySQL Temporary Objects shows information about temporary objects created by the MySQL server: temporary tables, temporary files, and temporary tables created on disk instead of in memory. Like sort data, this is easy to over-analyze. The most serious one is the temp tables created on disk. Dealing with these is complex, but is covered well in the book High Performance MySQL.
28.Mysql Threads
| Threads_cached | 51 |
| Threads_connected | 2 |
| Threads_created | 53 |
29. Mysql Transaction Handler
$results['innodb_transactions'] = make_bigint(
$row, (isset($row) ? $row : null));
$txn_seen = TRUE;
MySQL Transaction Handler shows the transactional operations that took place at the MySQL server level.
-- Additional Options
Read More : Percona mysql installed dirs support-file/my-innodb-heavy-4G.cnf
Back_log
back_log is the number of connections the operating system can keep in
the listen queue, before the MySQL connection manager thread has
processed them. The back_logvalue indicates how many requests can be
stacked during this short time before MySQL momentarily stops answering new requests.
In other words, this value is the> If you have a very high connection rate and experience
"connection refused" errors, you might need to increasethis value.
Check your OS documentation for the maximum value of this parameter.
Attempting to set back_log higher than your operating system limit
will have no effect.
Skip-networking
Don't listen on a TCP/IP port at all. This can be a security
enhancement, if all processes that need to connect to mysqld run
on the same host.All interaction with mysqld must be made via Unix
sockets or named pipes.
Note that using this option without enabling named pipes on Windows
(via the "enable-named-pipe" option) will render mysqld useless!
Max_connections
Max_connect_errors
Table_open_cache
The number of open tables for all threads. Increasing this value
increases the number of file descriptors that mysqld requires.
Therefore you have to make sure to set the amount of open files
allowed to at least 4096 in the variable "open-files-limit" in
section .
Binlog_cache_size
The> during a transaction. If you often use big, multi-statement
transactions you can increase this value to get more performance. All
statements from transactions are buffered in the binary log cache and
are being written to the binary log at once after the COMMIT.If the
transaction is larger than this value, temporary file on disk is used
instead.This buffer is allocated per connection on first update.
Max_heap_table_size
Maximum allowed> is a protection against the accidential creation of a very large HEAP
table which could otherwise use up all memory resources.
Read_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read
through this buffer to avoid disk seeks. You can improve ORDER BY
performance a lot, if set this to a high value.
Allocated per thread, when needed.
Ft_min_word_len
Minimum word length to be indexed by the full text search index.
You might wish to decrease it if you need to search for shorter words.
Note that you need to rebuild your FULLTEXT index, after you have
modified this value.
Thread_stack
Thread stack> connection time. MySQL itself usually needs no more than 64K of
memory, while if you use your own stack hungry UDF functions or your
OS requires more stack for some operations, you might need to set this
to a higher value.
Binlog_format
binary logging format - mixed recommended.
Log_slave_updates
If you're using replication with chained slaves (A->B->C), you need to
enable this option on server B. It enables logging of updates done by
the slave thread into the slave's binary log.
Log
Enable the full query log. Every query (even ones with incorrect
syntax) that the server receives will be logged. This is useful for
debugging, it is usually. disabled in production use.
Log_warnings
Print warnings to the error log file.If you have any problem with
MySQL you should enable logging of warnings and examine the error log
for possible.explanations.
master-host
master-user
master-password
master-port
Read_only
Make the slave read-only. Only users with the SUPER privilege and the
replication slave thread will be able to modify data on it. You can
use this to ensure that no applications will accidently modify data on
the slave instead of the master.
Bulk_insert_buffer_size
MyISAM uses special tree-like cache to make bulk inserts (that is,
INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
INFILE) faster. This variable limits the> bytes per thread. Setting it to 0 will disable this optimisation.Do
not set it larger than "key_buffer_size" for optimal performance.
This buffer is allocated when a bulk insert is detected.
Myisam_repair_threads
If a table has more than one index, MyISAM can use more than one
thread to repair them by sorting in parallel. This makes sense if you
have multiple CPUs and plenty of memory.
Myisam_recover
Automatically check and repair not properly closed MyISAM tables.
Innodb_additional_mem_pool_size
Additional memory pool that is used by InnoDB to store metadata
information.If InnoDB requires more memory for this purpose it will
start to allocate it from the OS. As this is fast enough on most
recent operating systems, you normally do not need to change this
value. SHOW INNODB STATUS will display the current amount used.
Innodb_write_io_threadsand innodb_read_io_threads
Number of IO threads to use for async IO operations. This value is
hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a
larger number.
Innodb_force_recovery
If you run into InnoDB tablespace corruption, setting this to a nonzero
value will likely help you to dump your tables. Start from value 1 and
increase it until you're able to dump the table successfully.
Innodb_thread_concurrency
Number of threads allowed inside the InnoDB kernel. The optimal value
depends highly on the application, hardware as well as the OS
scheduler properties. A too high value may lead to thread thrashing.
Innodb_flush_log_at_trx_commit
If set to 1, InnoDB will flush (fsync) the transaction logs to the
disk at each commit, which offers full ACID behavior. If you are
willing to compromise this safety, and you are running small
transactions, you may set this to 0 or 2 to reduce disk I/O to the
logs. Value 0 means that the log is only written to the log file and
the log file flushed to disk approximately once per second. Value 2
means the log is written to the log file at each commit, but the log
file is only flushed to disk approximately once per second.
Innodb_log_files_in_group
Total number of files in the log group. A value of 2-3 is usually good
enough.
Innodb_max_dirty_pages_pct
Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
If it is reached, InnoDB will start flushing them out agressively to
not run out of clean pages at all. This is a soft limit, not
guaranteed to be held.
Open-files-limit## In section
Increase the amount of open files allowed per process. Warning: Make
sure you have set the global system limit high enough! The high value
is required for a large number of opened tables.
页:
[1]