Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to monitor mysql performance, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

View the static parameters of the mysql server:

Show variables

View the dynamic running information of mysql:

Show status

1. Query the number of connections of users

-- show processlist; only lists the first 100 items. If you want to list them all, please use show full processlist.

Command: show processlist

If it is a root account, you can see the current connections of all users. If it is another ordinary account, you can only see the connection occupied by yourself. Log in with mysql-uroot-p123456 (note: usernames and passwords do not contain "")

Check the running status of the database

Command: show global status

Command: show status

Command: show status like'%% the following variable%'

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

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

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

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

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.

The number of times Flush_commands executed the FLUSH command.

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

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.

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

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.

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.

The maximum number of connections used simultaneously by Max_used_connections.

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.

The number of Open_tables open tables.

The number of files opened by Open_files.

Number of Open_streams open streams (mainly used for logging)

The number of tables that Opened_tables has opened.

The number of queries sent by Questions to the server.

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

The number of connections currently open by Threads_connected.

The number of threads that Threads_running is not sleeping.

How many seconds did the Uptime server work?

Note:

If the Opened_tables is too large, then your table_open_cache variable may be too small.

If the key_reads is too big, then your key_cache may be too small. The cache hit ratio can be calculated using key_reads/key_read_requests.

If the Handler_read_rnd is too large, then you probably have a large number of queries that require MySQL to scan the entire table or you have not used the join of key values correctly.

You can fine-tune the state returned by the "show status" command. Mainly pay attention to the values of the following variables, the smaller the better, preferably zero:

Created_tmp_disk_tables

Created_tmp_tables

Created_tmp_files

Slow_queries

Third, database performance query

1. Show status command to understand the execution frequency of various SQL

-- View the commands executed in the current session to count the com_XXX

Show session status like 'Com_%' in which session can be saved

Show status like 'Com_%'

-- displays global statistical usage

SHOW GLOBAL STATUS LIKE 'COM_%'

two。 Statistics for the status of the InnoDB storage engine

SHOW GLOBAL STATUS LIKE 'Innodb_%'

3. Check the number of attempts to connect to the mySQL server

Show global status like 'connections'

4. View server working time

Show global status like 'uptime'

5. View the name of the database where it is located

(1) use the select database () statement

Select database ()

(2) use the show tables statement to query the results, the first behavior Tables_in_***, here * is the name of the current database.

Show tables

(3) using the status statement, one line of the query result is currrent database:***. Here * * is the name of the current database.

Status

6. View the storage format information for the table

Show table status like'% test%'\ G

Displays information about each table in the currently used or specified database. The information includes the table type and the latest update time of the table

Show table status

-- displays the names of all tables in the current database

Show tables or show tables from database_name

7. View alarm information

Show warnings\ G

8. Query table structure information

Show create table test\ G

9. Query the base table under the current schema (schema)

Select * from information_schema.tables where table_type='BASE TABLE' and table_schema=database ()\ G

10. Query view information under the current schema (schema)

Select * from information_schema.views where table_schema=database ()\ G'

11. Query whether the database has the partitioning feature enabled

Show variables like'% partition%'\ G

Show plugins\ G

twelve。 Display index information

Show index from TABLE_NAME\ G

Show keys from TABLE_NAME

13. Query partition information under the current schema (schema)

Select * from information_schema.PARTITIONS where table_schema=database () and table_name='t3'\ G

14. Query tables under the current schema (schema)

Select * from information_schema.tables where table_name='t' and table_schema=database ()\ G

15. Shows how mysql uses indexes to process select statements and join tables

Https://www.cnblogs.com/yycc/p/7338894.html

Explain sql\ G

Explain partitions sql\ G

17. Create the structure of a new table based on a known table

Create table e2 like e

18. Change the structure of a known partitioned table to a normal table

Alter table e2 remove partitioning

19. Swap partition

Alter table e exchange partition p0 with table e2

20. View latch in the innodb storage engine

Show engine innodb mutex

21. View the current lock engine information in the innodb storage engine

Show engine innodb status\ G

Show full processlist

Select * from information_schema.innodb_trx\ G

Select * from information_schema.innodb_locks\ G

Select * from information_schema.innodb_lock_waits\ G

-- Lock federation query, as follows:

Select r.trx_id waiting_trx_id

R.trx_requested_lock_id waiting_requested_lock_id

R.trx_mysql_thread_id waiting_thread

R.trx_query waiting_query

R.trx_state waiting_status

B.trx_id blocking_trx_id

B.trx_mysql_thread_id blocking_thread

B.trx_query blocking_query

B.trx_state blocking_status

From information_schema.innodb_lock_waits w

Information_schema.innodb_trx b

Information_schema.innodb_trx r

Where b.trx_id = w.blocking_trx_id

And r.trx_id = w.requesting_trx_id\ G

22.mysql shows things on and off

Open things: start transaction or begin

Close things: commit--- success and rollback-- rollback

23.mysql modifies the transaction isolation level

Users can use SET TRANSACTION statements to change the isolation level for a single session or for all new connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |

REPEATABLE READ | SERIALIZABLE}

Note: the default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword

Statement sets the default transaction level globally for all new connections created from that point, except for those that do not exist. You need SUPER permission to do this.

Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client is free to change the session isolation level (even

In the middle of the transaction, or set the isolation level for the next transaction.

You can query the global and session transaction isolation levels with the following statements:

SELECT @ @ global.tx_isolation

SELECT @ @ session.tx_isolation

SELECT @ @ tx_isolation

24.SHOW COLUMNS displays information about the columns in a given table. It also applies to views. SHOW COLUMNS displays only information about columns for which you have certain permissions.

-- displays the column names in the table

Show columns from table_name from database_name; or show columns from database_name.table_name

SHOW [FULL] {COLUMNS | FIELDS}

{FROM | IN} tbl_name

[{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

25. QPS (number of queries per second)

QPS = Questions (or Queries) / seconds

Mysql > show global status like 'Question%'

twenty-six。 TPS (transactions per second)

TPS = (Com_commit + Com_rollback) / seconds

Mysql > show global status like 'Com_commit'

Mysql > show global status like 'Com_rollback'

twenty-seven。 Key Buffer hit rate

Mysql > show global status like 'key%'

Key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

Key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

twenty-eight。 InnoDB Buffer hit rate

Mysql > show status like 'innodb_buffer_pool_read%'

Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

twenty-nine。 Query Cache hit rate

Mysql > show status like 'Qcache%'

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts)) * 100%

thirty。 Table Cache state quantity

Mysql > show global status like 'open%'

Compare open_tables and opend_tables values

thirty-one。 Thread Cache hit rate

Mysql > show global status like 'Thread%'

Mysql > show global status like 'Connections'

Thread_cache_hits = (1-Threads_created / connections) * 100%

thirty-two。 Locked state

Mysql > show global status like'% lock%'

Table_locks_waited/Table_locks_immediate=0.3% if this ratio is relatively large, it means that the blocking caused by the table lock is more serious.

Innodb_row_lock_waits innodb row lock, too large may be caused by gap lock

thirty-three。 Replication delay amount

Mysql > show slave status

View delay time

thirty-four。 Tmp Table status (temporary table status)

Mysql > show status like 'Create_tmp%'

The Created_tmp_disk_tables/Created_tmp_tables ratio had better not exceed 10%, if the Created_tmp_ tables value is relatively large

It may be that there are too many sorted sentences or the connected sentences are not optimized enough.

thirty-five。 Binlog Cache usage

Mysql > show status like 'Binlog_cache%'

If the Binlog_cache_disk_use value is not 0, you may need to increase the binlog_cache_size size

thirty-six。 Innodb_log_waits quantity

Mysql > show status like 'innodb_log_waits'

If the Innodb_log_waits value is not equal to 0, it indicates that innodb log buffer is waiting because of insufficient space.

Such as commands:

> # show global status

Although you can use:

> # show global status like%...%

To filter, but it is still necessary to figure out what each item means corresponding to the long list.

thirty-seven。 Check to see if the distributed transaction XA is started (ON by default):

Show variables like'% innodb_support_xa%'

Basic syntax of 38.MySQL XA transaction

XA {START | BEGIN} xid [JOIN | RESUME] initiates a xid transaction (xid must be a unique value; the [JOIN | RESUME] clause is not supported)

XA END xid [SUSPEND [FOR MIGRATE]] ends a xid transaction (the [SUSPEND [FOR MIGRATE]] clause is not supported)

XA PREPARE xid prepares and pre-commits xid transactions

XA COMMIT xid [ONE PHASE] commit xid transaction

XA ROLLBACK xid rolls back xid transactions

XA RECOVER views all transactions in the PREPARE phase

thirty-nine。 Determine whether the database memory has reached the bottleneck

Show global status like'% innodb%read%'\ G

forty。 View index usage

Show status like'% Handler_read%'

forty-one。 View the contention for uplink locks in the innodb storage engine:

Show status like'% innodb_row_lock%'

If lock contention is found to be serious, Innodb_row_lock_time_avg and Innodb_row_lock_waits are higher.

In addition, it is to set the tables and data rows where further lock conflicts occur in innodbDB Monitors, and analyze the reasons for lock contention!

Note: innodb row locking is achieved by locking index items on the index. This means that innodb uses row-level locks only if the data is retrieved through index conditions.

Otherwise, innodb will use table locks!

forty-two。 View the detailed definition of mysql server parameters

Mysqld-- verbose-- help | more

For example, if you want to know the settings of the current database character set, as follows:

Mysqld-- verbose-- help | grep character-set-server

forty-three。 Binary log

View binary log status

Show variables like'% log_bin%'

View all the binary log files on the current server

Show binary logs

Show master logs

View the current binary log file status

Show master status

Toggle binary log

Flush logs

forty-four。 Query the current time in the mysql database

Select now ()

forty-five。 Query the current time of the operating system in mysql database

System date

forty-six。 View database character set view MYSQL database server and database character set

Show variables like'% character%'

View the character sets supported by MYSQL

Show charset

View the character set of the library

Show create database test72\ G

View the character set of the table

Show table status from test72 like 'zs'\ G

View the character set of all columns in the table

Show full columns from table name

Show full columns from zs\ G

forty-seven。 Displays the names of all databases in mysql

Show databases

forty-eight。 Displays the permissions of a user, with results similar to the grant command

Show grants for user_name

forty-nine。 Display the different permissions supported by the server

Show privileges

fifty。 Displays whether the create database statement can create the specified database

Show create database database_name

fifty-one。 Displays the storage engine and default engine available after installation

Show engines

fifty-two。 Displays the status of the innoDB storage engine

Show engine innodb status\ G

fifty-three。 Count the size of each library

Select TABLE_SCHEMA,SUM (DATA_LENGTH) / 1024 as data_length,SUM 1024 as data_length,SUM (INDEX_LENGTH) / 1024 shock 10

24 as index_length,SUM (DATA_LENGTH+INDEX_LENGTH) / 1024 as sum_data_index from informatio

N_schema.tables where TABLESCHEMAT information schemas' and TABLESCHEMAX records mysql' group by TABL

E_SCHEMA

fifty-four。 The size of each table in the statistical library, taking the test library as an example

Select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,SUM (DATA_LENGTH+INDEX_LENGTH) / 1024Compact 1024Unip 102

4 as TOTAL_SIZE from information_schema.tables where TABLE_SCHEMA='test' group by TABLE_SCHEMA

fifty-five。 Count all database sizes

Select SUM (DATA_LENGTH+INDEX_LENGTH) / 1024 Universe 1024 as sum_data_index from information_schema.tab

Les

Thank you for reading this article carefully. I hope the article "how to monitor mysql performance" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.