MySQL comes with four databases, which are:
- information_schema
- performance_schema
- mysql
- test
This article introduces the performance ﹣ schema library.
Sketch
Performance schema library is a new storage engine in MySQL 5.5. It is mainly used to collect database server performance parameters, monitor the resource consumption, resource waiting and other situations of MySQL server in a lower level operation process.
The storage engine of all tables in the performance schema Library of MySQL is performance schema, but users cannot create tables with performance schema as the storage engine.
The performance schema library function can be summarized as follows:
- Provide details of process waiting, including locks, mutex variables, and file information;
- Save historical event summary information to make detailed judgment for providing MySQL server performance;
- It is very easy to add or delete monitoring event points, and you can change the monitoring CYCLE of mysql server at will, for example (CYCLE, MICROSECOND)
From the above information, DBA s can get a more detailed understanding of the possible causes of performance degradation.
The details are as follows (summarized from the network):
1. Performance schema provides a way to check the internal execution of the server in real time when the database is running. The table in the performance schema database uses the performance schema storage engine to pay attention to the metadata information during the server operation.
2. Performance schema monitors the internal operation of the server by monitoring the events of the server. An event is anything that happens in the internal activities of the server and the corresponding time consumption. Use this information to determine where the relevant resources in the server are consumed? First of all, events can be function calls, operating system waits, the stage of SQL statement execution (such as parsing or sorting stage during SQL statement execution), or the whole SQL statement and SQL statement collection. The collection of events can provide the synchronous calling information of disk files, table I / O, table lock and other resources in the server.
3. The events in the performance ﹣ schema are different from those written to the binary log (events describing data modification), the event scheduler (this is a kind of storage program). The events in performance schema record the consumption, time-consuming and times of some activities performed by the server.
4. The events in the performance schema are only recorded in the performance schema of the local server. When the data in these tables changes, they will not be written to the binlog or copied to other servers through the replication mechanism.
5. Information recorded in tables related to current active events, historical events, and event summaries. It can provide the execution times of an event and the usage time. And you can use to analyze the associated activities of a particular thread, object, such as Mutex or file.
6. The performance schema storage engine uses "checkpoints" in the server source code to collect event data. There is no separate thread to detect the code of performance ﹣ schema implementation mechanism itself, which is different from other functions (such as replication or event scheduler).
7. The collected event data is stored in a table in the performance ﹣ schema database. These tables can be queried with the SELECT statement, or the table records in the performance schema database can be updated with the SQL statement (for example, dynamically modify several configuration tables at the beginning of the setup * of the performance schema, but note: the changes of the configuration table will take effect immediately, which will affect data collection).
8. The data in the performance schema's table will not be persisted in the disk, and will be saved in memory again. Once the server is restarted, these data will be lost (including all data under the entire performance schema inside the configuration table).
9. Event monitoring is available in all platforms supported by MySQL, but the timer types used to count event time overhead may vary in different platforms.
Use show variables like 'performance schema' to view the status of performance schema in MySQL service:
MariaDB [(none)]> show variables like 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+ 1 row in set (0.001 sec)
It is not started by default. You need to manually add enable in the configuration file:
[mysqld] performance_schema=ON
Briefly describe the Storage Engine:
What is the storage engine of MySQL database? Find out the engine before you do.
The engine is the core of the engine, the engine is the core of the vehicle, so the engine is the core of driving the car movement, different engines have different ways of working.
By analogy, MySQL's storage engine is the core of data processing in mysql, that is, the way of processing. Different storage engines have different storage mechanisms, indexing techniques, locking levels and ultimately provide a wide range of different functions and capabilities.
At present, the most commonly used storage engines of MySQL are MyISAM and InnoDB:
- MyISAM: has high insertion and query speed, but does not support transactions
- InnoDB: the default database of Mysql after version 5.5, the preferred engine for transactional databases, supports ACID transactions and row level locking
Inclusion table
At present, all tables contained in the performance ﹣ schema library are as follows:
MariaDB [mysql]> use performance_schema; Database changed MariaDB [performance_schema]> show tables; +----------------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | host_cache | | hosts | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | users | +----------------------------------------------------+ 52 rows in set (0.002 sec)
Some common tables:
- setup: configure table, configure various MySQL information
- Instance: instance table, which stores instances of various objects
- Wait: wait event information table
- Stage: execution stage record form
- Statement: event Declaration Table
- Connection: connection information table
- Summary: statistics table (each dimension)
- Performance? Timers: statistical time unit table supported by the system
- threads: monitor thread table
- users: user table
Details of common tables
- ——setup table - (configuration table)
View all setup related tables:
MariaDB [performance_schema]> show tables like "%setup%"; +----------------------------------------+ | Tables_in_performance_schema (%setup%) | +----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +----------------------------------------+ 5 rows in set (0.001 sec)
1) Setup? Actors: configure user monitoring information and monitor all users.
2) setup_consumers: configure the consumer type of events, that is, which statistics tables the collected events are written to.
3) setup_instruments: configure specific instruments, mainly including four categories: idle, stage/xxx, statement/xxx, wait/xxx. (idle indicates the idle time of socket, stage class indicates the statistics of each execution stage of statement, statement class counts the information of statement dimension, wait class counts various waiting events, such as IO, mutux, spin_lock,condition, etc.)
4) setup_objects: configure the monitoring objects. By default, the tables in mysql, performance_schema and information_schema are not monitored, while all tables in other DB are monitored.
5) setup_timers: configure statistical time units.
- ——Instance - (instance table)
View all instance related tables:
MariaDB [performance_schema]> show tables like "%instance%"; +-------------------------------------------+ | Tables_in_performance_schema (%instance%) | +-------------------------------------------+ | cond_instances | | //events_waits_summary_by_instance | | file_instances | | //file_summary_by_instance | | mutex_instances | | rwlock_instances | | socket_instances | | //socket_summary_by_instance | +-------------------------------------------+ 8 rows in set (0.001 sec)
1) Cond Ou instances: conditional wait for object instances
The table records the object of the condition variable used in the system, and object "installation" begin is the memory address of the object.
2) File instances: File instances
The table records the objects that have files opened in the system, including ibdata files, redo files, binlog files, user's table files, etc. the open count shows the current number of files opened. If the file has not been opened again, it will not appear in the table.
3) Mutex? Instances: mutex synchronization object instances
Table records all records of mutex objects used in the system, where name is: wait/synch/mutex / *. Locked by thread ID shows which thread is holding mutex. If no thread holds mutex, it is NULL.
4) Rwlock? Instances: read / write lock synchronization object instance
Table records all records of the read-write lock object used in the system, where name is wait/synch/rwlock / *. Write? Locked? By? Thread? ID is the thread? ID of the object being held. If there is no thread holding, it is NULL. Read? Locked? By? Count records how many readers hold read locks at the same time. (you can know which thread is waiting for the lock through the events \ waits \ current table; you can know which thread holds the lock through rwlock \ instances. The defect of rwlock · instances is that it can only record the thread holding the write lock, but it can't do anything for the read lock.
5) Socket Ou instances: active session object instances
The table records thread ID, socket ID, IP and port. Other tables can associate with socket instance through thread ID to obtain IP port information, which can be connected with applications.
- ——Wait - (wait event information table)
View all Wait related tables:
MariaDB [performance_schema]> show tables like "%wait%"; +-----------------------------------------------+ | Tables_in_performance_schema (%wait%) | +-----------------------------------------------+ | events_waits_current | | events_waits_history | | events_waits_history_long | | //events_waits_summary_by_account_by_event_name | | //events_waits_summary_by_host_by_event_name | | //events_waits_summary_by_instance | | //events_waits_summary_by_thread_by_event_name | | //events_waits_summary_by_user_by_event_name | | //events_waits_summary_global_by_event_name | | //table_io_waits_summary_by_index_usage | | //table_io_waits_summary_by_table | | //table_lock_waits_summary_by_table | +-----------------------------------------------+ 12 rows in set (0.001 sec)
1) Events > waits > current: records the events that the current thread is waiting for
2) Events ﹣ waits ﹣ history: records the last 10 events each thread has been waiting for
3) Events > waits > History > long: recorded 10000 events generated by all threads recently
- ——Stage - (execution stage record form)
View all Stage related tables:
MariaDB [performance_schema]> show tables like "%stage%"; +------------------------------------------------+ | Tables_in_performance_schema (%stage%) | +------------------------------------------------+ | events_stages_current | | events_stages_history | | events_stages_history_long | | //events_stages_summary_by_account_by_event_name | | //events_stages_summary_by_host_by_event_name | | //events_stages_summary_by_thread_by_event_name | | //events_stages_summary_by_user_by_event_name | | //events_stages_summary_global_by_event_name | +------------------------------------------------+ 8 rows in set (0.001 sec)
1) Events > stages > current: records the execution stage of the current thread
2) Events ﹣ stages ﹣ history: 10 history records of the execution stage of the current thread
3) Events > stages > History > long: Records 10000 history records of the current thread's execution stage
- Statement (event Declaration Table)
View all Statement related tables:
MariaDB [performance_schema]> show tables like "%statement%"; +----------------------------------------------------+ | Tables_in_performance_schema (%statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | //events_statements_summary_by_account_by_event_name | | //events_statements_summary_by_digest | | //events_statements_summary_by_host_by_event_name | | //events_statements_summary_by_thread_by_event_name | | //events_statements_summary_by_user_by_event_name | | //events_statements_summary_global_by_event_name | +----------------------------------------------------+ 9 rows in set (0.001 sec)
1) Events menu statements menu current: thread menu ID + Event menu ID can uniquely identify a record. The statements table records only the top-level requests, SQL statements or commands, one row per statement. Event name is in the form of statement/sql /, or statement/com/
2)events_statements_history
3)events_statements_history_long
- ——Connection - (connection information)
1) users: record the number of user connections
2) hosts: records the number of host connections
3) accounts: records the number of user host connections
- ——Summary - (statistics table of different dimensions)
To view Summary related tables:
MariaDB [performance_schema]> show tables like "%Summary%"; +----------------------------------------------------+ | Tables_in_performance_schema (%Summary%) | +----------------------------------------------------+ | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_summary_by_event_name | | file_summary_by_instance | | objects_summary_global_by_type | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +----------------------------------------------------+ 25 rows in set (0.002 sec)
1) Events > waits > summary > Global > by > Event > Name: aggregate by waiting event type, one record per event
2) Events ﹣ waits ﹣ summary ﹣ by ﹣ instance: aggregate by waiting event object. There may be multiple instances of the same waiting event. Each instance has a different memory address. Therefore, event ﹣ name + object ﹣ instance ﹣ begin uniquely determines a record.
3) Events ﹣ waits ﹣ summary ﹣ by ﹣ thread ﹣ by ﹣ event ﹣ Name: count by each thread and event. Thread ﹣ ID + event ﹣ name uniquely determines a record.
4) Events ﹣ stages ﹣ summary ﹣ global ﹣ by ﹣ event ﹣ Name: aggregate by event stage type, one record for each event, the table structure is the same as above.
5) Events > stages > summary > by > thread > by > Event > Name: stage statistics by each thread and event. The table structure is the same as above.
6) Events > statements > summary > by > Digest: aggregate according to the statement of the event.
7) Events > statements > summary > Global > by > Event > Name: aggregate according to the statement of the event. The table structure is the same as above.
8) Events > statements > summary > by > thread > by > Event > Name: aggregate by thread and event statements.
9) File? Summary? By? Instance: statistics by event type (physical IO dimension)
10) File? Summary? By? Event? Name: specific file statistics (physical IO dimension)
11) Table [IO] waits [summary] by table: aggregate I/O operations of each table according to wait/io/table/sql/handler (logical IO latitude)
12) Table IO its summary by index usage: similar to table IO its summary by table, statistics by index dimension
13) Table? Lock? Waits? Summary? By? Table: aggregates table lock wait events, including internal lock and external lock
So far.
