Shulou(Shulou.com)06/01 Report--

1 、

Aborted_clients the number of connections that have been abandoned because the customer did not close the connection correctly. (monitoring)

Indicates that the client did not close the connection correctly, and the number of connections terminated was caused by 1. The client program did not call mysql_close () to close mysql connection 2. 0 before exiting. The dormancy time of the client exceeds the values of the mysql system variables wait_timeout and interactive_timeout, causing the connection to be terminated by the mysql process. The client program suddenly ends in the process of data transmission.

2 、

The number of times Aborted_connects has attempted a connection to the MySQL server that has failed. (monitoring)

Indicates the number of failed attempts to connect to the mysql server, can be combined with host_cache to analyze the problem, the cause: 1. Password error 2. Do not have permission to access, but try to access 3. Connection timeout 4. The connection information is incorrect

If you encounter information such as connection timeout, be sure to analyze what caused it.

Use tcpdump to grab packets on the MySQL server

[root@lichao] # tcpdump-I eth0 port 3306-s 1500-w tcpdump.log

Then on another MySQL server, access the MySQL database using an account that does not exist or the wrong password

# mysql-h xx.xx.xx.xx-u xxx-pxxxx

ERROR 1045 (28000): Access denied for user 'xxx'@'xx.xx.xx.xxx' (using password: YES)

# mysql-h xx.xx.xx.xx-u xxxx-pxxx

ERROR 1045 (28000): Access denied for user 'test'@'xx.xx.xx.xx' (using password: YES)

[root@lichao~] #

After executing the command, you can use CTRL + C to finish the packet capture analysis, and then view the analysis. The screenshot below shows:

[root@lichao] # tcpdump-I eth0 port 3306-s 1500-w tcpdump.log

Tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes

28 packets captured

28 packets received by filter

0 packets dropped by kernel

[root@lichao ~] # strings tcpdump.log

3 、

Binlog_cache_disk_use indicates that the number of temporary files used in the cache binary log due to insufficient memory designed by our binlog_cache_size (for monitoring)

4 、

Binlog_cache_use indicates the number of times binlog_cache_size cache is used

When the corresponding Binlog_cache_disk_use value is relatively large, we can consider increasing the corresponding binlog_cache_size value appropriately.

There are no big transactions, and dml can be set a little smaller if the transaction is not very frequent, and if the transaction is large and many, and the dml operations are also frequent, you can scale it up appropriately.

The former suggestion is 1048576-1m.

The latter suggestion is: 2097152-4194304, that is, 2Murray 4m

View by: show global status like 'bin%'

Note:

(1) max_binlog_cache_size represents the maximum amount of cache memory that binlog can use.

When we execute a multi-statement transaction, the memory used by all session exceeds the value of max_binlog_cache_size

There will be an error: "Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage"

(2) if the setting is too large, it will consume memory resources; if the setting is too small, the temporary file, namely disk, will be used.

Adjustment mode (two methods):

(1) configuration file sets my.cnf

Binlog_cache_size = 1m

(2) set global binlog_cache_size = 1048576

Explanation:

Show status like'% binlog%'

Stmt means statement.

Number of existing hard drives cached by Binlog_cache_disk_use (transaction type) binary log

The number of Binlog_cache_use (transaction class) binary log entries cached (in memory) Note that this is not the capacity, but the number of transactions. Every time a transaction commits, there is an increase.

Number of existing hard drives cached by Binlog_stmt_cache_disk_use (non-transactional) binary log

The number of Binlog_stmt_cache_use (non-transactional) binary logs cached (in memory) non-transactional statements are all stored here, such as MYISAM engine tables, where insert records are stored.

Parameter derivation:

The combination of binlog_cache_use and binlog_cache_disk_use can be used to resize binlog_cache_size.

Binglog_stmt_cache_use and binlog_stmt_cache_disk_use can be combined to resize binlog_stmt_cache_size.

5 、

Bytes_received/Bytes_sent (for monitoring)

Look at the total throughput of mysql

Bytes_received

The number of bytes received from all clients. Combined with bytes sent, it can be used as an evaluation index of database Nic throughput, per byte.

Bytes_sent

The number of bytes sent to all clients. Combined with bytes received, it can be used as an evaluation index of database Nic throughput, per byte.

6 、

Com_admin_commands [Com_xxx]

The Com_xxx statement count variable represents the number of times each xxx statement is executed. Each type of statement has a state variable. For example, Com_delete and Com_insert count the number of times DELETE and INSERT statements are executed, respectively.

Com_xxx includes:

Com_alter_db

Com_alter_db_upgrade

Com_alter_event

Com_alter_function

Com_alter_procedure

Com_alter_server

Com_alter_table

Com_alter_tablespace

Com_analyze

Com_assign_to_keycache

Com_begin

Com_binlog

Com_call_procedure

Com_change_db

Com_change_master

Com_check

Com_checksum

The number of transactions committed by Com_commit MySQL, which can be used to count TPS (transactions per second). Calculation formula: Com_commit/S+Com_rollback/S (for monitoring)

Com_create_db

Com_create_event

Com_create_function

Com_create_index

Number of stored procedures created by Com_create_procedure- (monitoring)

Com_create_server

Number of data tables created by Com_create_table- (monitoring)

Com_create_trigger

Com_create_udf

Com_create_user

Com_create_view

Com_dealloc_sql

The number of Com_delete MySQL deletions, which can be used to count qps. Calculation formula: questions / uptime or based on com_%: Com_select/s + Com_insert/s + Com_update/s + Com_delete/s (for monitoring)

Com_delete_multi

Com_do

Com_drop_db (for monitoring)

Com_drop_event

Com_drop_function

Com_drop_index

Com_drop_procedure

Com_drop_server

Com_drop_table (for monitoring)

Com_drop_trigger

Com_drop_user

Com_drop_view

Com_empty_query

Com_execute_sql

Com_flush

Com_grant (for monitoring)

Com_ha_close

Com_ha_open

Com_ha_read

Com_help

The number of Com_insert MySQL inserts can be used to calculate the qps,qps formula: questions / uptime or based on com_%: Com_select/s + Com_insert/s + Com_update/s + Com_delete/s (for monitoring)

Com_insert_select

Com_install_plugin

Com_kill

Com_load

Com_lock_tables- lock table lock tables, unlock command unlock tables

Com_optimize

Com_preload_keys

Com_prepare_sql

Com_purge

Com_purge_before_date

Com_release_savepoint

Com_rename_table

Com_rename_user

Com_repair

Com_replace

Com_replace_select

Com_reset

Com_resignal

Com_revoke

Com_revoke_all

The number of transactions rolled back by Com_rollback MySQL, which can be used to count TPS (transactions per second). Calculation formula: Com_commit/S+Com_rollback/S (monitoring)

Com_rollback_to_savepoint

Com_savepoint

Com_select

Com_set_option

Com_show_authors

Com_show_binlog_events

Com_show_binlogs

Com_show_charsets

Com_show_collations

Com_show_contributors

Com_show_create_db

Com_show_create_event

Com_show_create_func

Com_show_create_proc

Com_show_create_table

Com_show_create_trigger

Number of times Com_show_databases---- executes show databases (monitoring)

Com_show_engine_logs

Com_show_engine_mutex

Com_show_engine_status

Com_show_errors

Com_show_events

Com_show_fields

Com_show_function_code

Com_show_function_status

Com_show_grants

Com_show_keys

Com_show_logs

Com_show_master_status

Com_show_new_master

Com_show_open_tables

Com_show_plugins

Com_show_privileges

Com_show_procedure_code

Com_show_procedure_status

Com_show_processlist

Com_show_profile

Com_show_profiles

Com_show_relaylog_events

Com_show_slave_hosts

Com_show_slave_status

Com_show_status

Com_show_storage_engines

Com_show_table_status

Com_show_tables

Com_show_triggers

Com_show_variables

Com_show_warnings

Com_signal

Com_slave_start

Com_slave_stop

Com_stmt_close

Com_stmt_execute

Com_stmt_fetch

Com_stmt_prepare

Com_stmt_reprepare

Com_stmt_reset

Com_stmt_send_long_data

Com_truncate (monitoring)

Com_uninstall_plugin

Com_unlock_tables (Monitoring 1)

The number of Com_update MySQL updates, which can be used to calculate the qps,qps formula: questions / uptime or based on com_%: Com_select/s + Com_insert/s + Com_update/s + Com_delete/s (monitoring)

Com_update_multi

Com_xa_commit

Com_xa_end

Com_xa_prepare

Com_xa_recover

Com_xa_rollback

Com_xa_start

7 、

The number of times Connections attempted to connect to the MySQL server.

8 、

Created_tmp_tables the number of implicit temporary tables that have been created when the statement is executed.

Created_tmp_disk_tables

About these two values are:

Mysql > show global status like 'created_tmp%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Created_tmp_disk_tables | 21197 | |

| | Created_tmp_files | 58 | |

| | Created_tmp_tables | 1771587 | |

+-+ +

Each time a temporary table is created, Created_tmp_tables increases. If a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% show variables where Variable_name in ('tmp_table_size',' max_heap_table_size')

+-+ +

| | Variable_name | Value |

+-+ +

| | max_heap_table_size | 268435456 | |

| | tmp_table_size | 536870912 | |

+-+ +

Only temporary tables below 256MB can be stored in memory, and hard disk temporary tables will be used if they are exceeded.

9 、

The number of latency insertion processor threads being used by Delayed_insert_threads.

The number of rows written by Delayed_writes in INSERT DELAYED.

The number of rows written by Delayed_errors with INSERT DELAYED for which some errors (possibly duplicate key values) have occurred.

10 、

Number of times Flush_commands executes FLUSH commands (monitoring)

11 、

The number of times Handler_delete requests to delete rows from a table. (monitoring)

The number of times Handler_read_first requests to read the first row in the table.

The Handler_read_key request number is based on the key reading line.

The number of times Handler_read_next requests to read a row based on a key.

The number of times Handler_read_rnd requests to read a row based on a fixed location.

The number of times Handler_update requests to update a row in the table. (monitoring)

The number of times Handler_write requests to insert a row into the table. (monitoring)

12 、

The number of blocks used by Key_blocks_used for keyword caching.

The number of times Key_read_requests requests to read a key value from the cache.

The number of times Key_reads physically reads a key value from disk. (monitoring)

Key_write_requests requests the number of times a keyword block is written to the cache.

The number of times Key_writes physically writes a key block to disk (monitoring)

13 、

The maximum number of connections used simultaneously by Max_used_connections. (monitoring)

Not_flushed_key_blocks keyblocks that have been changed in the key cache but have not been emptied to disk.

The number of rows that Not_flushed_delayed_rows is waiting to write in the INSERT DELAY queue.

14 、

The number of Open_tables open tables. (monitoring)

The number of files opened by Open_files. (monitoring)

Number of Open_streams open streams (mainly used for logging)

The number of tables that Opened_tables has opened

15 、

The number of queries sent by Questions to the server. (monitoring)

The number of queries that Slow_queries takes longer than long_query_time time.

The number of connections currently open by Threads_connected. (monitoring)

The number of threads that Threads_running is not sleeping. (monitoring)

How many seconds did the Uptime server work? (monitoring)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.