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

This article introduces the relevant knowledge of "how to master the database top tool of ps-top for MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Students who are familiar with Linux like the top command, which allows us to easily and quickly get the running status of the system with one click. In addition to the system status, many people may also want to get the running status of the database with one click, and many people will use show profile or performance_schema databases to get all kinds of information. But it's convenient and direct.

This article bug introduces a command line tool ps-top, which can collect information from the performance_schema database of MySQL 5.6 + and use this information to display the server load in real time. The data is displayed as a table or file name, showing information in real time when selecting, inserting, updating or deleting activities; the view shows user activity, showing the number of different hosts connected with the same user name and the active view of these users; and a statistical view of mutual exclusion, locking tables and sql phase timing.

Installation

Like most golang tools, ps-top is easy to install. You can download, compile, and install it directly with go get:

Go get-u github.com/sjmudd/ps-top/cmd/ps-top or go get-u github.com/sjmudd/ps-top/cmd/ps-stats

This will directly install the download tool and its dependencies, and the installed binaries are in the $GOPATH/bin/ directory. If the path is in the system PATH settings, you can run the program directly without specifying any specific path.

Visit MySQL

You can access MySQL in one of the following ways:

The file ~ / .my.cnf is used by default.

Develop the cnf document, through-- defaults-file=/path/to/.my.cnf.

Connect to the host using-- host=somehost-- port=999-- user=someuser-- password=somepass

Connect through native socket-- socket=/path/to/mysql.sock-- user=someuser-- password=somepass

If not specified, the user defaults to the content of $USER. If no port is specified, the default is 3306.

You can use the command line option-- use-environment ps-top or ps-stats, then look for the account information of MYSQL_DSN in the environment variable to connect to Mysql. The MYSQL_DSN format is:

User:pass@tcp (host:port) / performance_schema

This avoids the direct display of account numbers and passwords on the command line and prevents information disclosure.

MySQL/MariaDB configuration

The performance_schema database must be configured to support ps-top. This feature is enabled by default on MySQL. This feature is disabled on MariaDB > = 10.0.12. Therefore, additional configuration is required to enable, in / etc/my.cnf, set:

Performance_schema = 1

Then restart MariaDB to take effect.

Ps-top and ps-stats need to ask the SELECT permission of the performance_schema table. If the required tables cannot be accessed, they will not run.

Setup_instruments: check mutex_latency or stages_latency to modify the configuration as needed to ensure that the user has the permission to perform this operation. If the server,-- read-only, or does not have sufficient permissions to change these tables, these views may be empty. If the table is successfully updated at startup, the setup_instruments configuration restores it to its original settings before stopping ps-top.

View

Ps-top and ps-stats can display seven different data views, which are updated every second by default. The seven views are:

Table_latency: the table shows the activity for the time it takes for the operation to be performed.

Table_ops: displays the number of activities performed by MySQL.

File_io_latency: shows how much time MySQL spent on the file Iramp O.

Table_lock_latency: according to the order of table lock display

User_latency: displays the sort based on the time the user runs the query or the number of connections the user has to MySQL. It provides some information and shows the total idle time, because it indicates that there may be excessively long idle queries, and if there is a stack, the sum of the values here can be interesting.

Mutex_latency: sort by mutex delay display.

SQL Stages_latency: sort by time display in different SQL query phases.

You can change the polling interval and switch between modes.

Shortcut key

In ps-top mode, the following keys allow ps-top to browse or change its behavior in different modes.

H?: displays help.

Reduce the polling interval by 1 second (at least 1 second)

+ increase the polling interval by 1 second

Q-exit

T: switch [ABS] between displaying statistics since the start of the reset ps-top or explicitly resetting them (using'z') [REL] or displaying statistics collected from the MySQL.

Z: reset statistics. That is the counter you see related to the reset statistics.

Change the display mode: delay, operation, file Imax O, locking, user, mutex, phase and storage mode.

Left arrow: switch to the previous screen

Right Arrow: switch to the next screen

Standard output mode

Both ps-stats and ps-top have the same view output and are regularly sent to stdout. The default view is table_io_latency. You can use the same method as vmstat to adjust the collection interval and the number of times data is collected. The first parameter is delay (the default is 1 second), and the second parameter is the number of iterations to be performed, or run all the time if it is not provided. This mode is designed to use stdout as the output medium to monitor ps-top and collect data from it.

Related command line options include:

-- count=

Limit the number of iterations (default: run permanently)

-- interval=

Set the default polling interval (in seconds)

-- limit=

Limit the number of rows output (excluding headers)

-- stdout sends the output to stdout (not the screen)

-- view=

Determine the possible values that start at the top of the view (default: table_io_latency) to view when ps-: table_io_latency,table_io_ops,file_io_latency,table_lock_latency, user_latency,mutex_latency, and stages_latency.

-- totals displays only the total rows, not the details.

This is the end of the introduction to "how to master ps-top 's database top tool for MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.