5.Skill Check Monitoring MySQL

Examine this output: | HOST | USER | ROLE | ENABLED | HISTORY | | % | % | % | YES | YES | 1 row in set (#.## sec) Which command produces this?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

What can be configured by modifying the Performance Schema setup_actors table?

foreground threads that are monitored

stored procedures that are monitored

server metrics that are collected

thread classes that are instrumented

How does a Performance Schema provide insight into database activity? (Choose three)

By analyzing I/O wait statistics

By analyzing historical performance data

By interpreting the Performance Schema to DBAs for diagnostic use cases

By analyzing errors that occur on the system

By analyzing which queries are running

By analyzing the audit record of server activity in log files

Which Performance Schema instruments have no subcomponents? (Choose three)

idle

memory

statement

error

transaction

stage

Which command configures and enables slow query logging?

SET GLOBAL slow_query_log_file=’slow-query.log’;

SET GLOBAL log_output=’TABLE’;

SET GLOBAL slow_query_log=ON;

SET SESSION long_query_time=0.5;

Why is the General Query Log used in MySQL? (Choose two)

To record statements where execution time exceeds a specified threshold

To include update operations stored as row-based binary logs on slave servers

To discover queries with excessive execution time.

To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

Which command displays collected server metrics stored in the performance schema?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

Which MySQL logs can be stored in tables? (Choose two)

Slow query log

Error log

Binary log

General query log

Audit log

====

5.Skill Check Monitoring MySQL

Examine this output: | HOST | USER | ROLE | ENABLED | HISTORY | | % | % | % | YES | YES | 1 row in set (#.## sec) Which command produces this?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

答案:

SELECT * FROM setup_actors LIMIT 5\G;

What can be configured by modifying the Performance Schema setup_actors table?

foreground threads that are monitored

stored procedures that are monitored

server metrics that are collected

thread classes that are instrumented

答案:

foreground threads that are monitored

说明：
Performance Schema Setup Tables
You configure the Performance Schema by modifying the contents of the setup_% tables.
• setup_actors: Which foreground threads (client connections) are monitored
• setup_objects: Which database objects (tables, stored procedures, triggers, events)
are monitored
• setup_threads: Which thread classes are instrumented
• setup_instruments: Which server metrics the Performance Schema collects
• setup_consumers: Where the instrumented events are stored

How does a Performance Schema provide insight into database activity? (Choose three)

By analyzing I/O wait statistics

By analyzing historical performance data

By interpreting the Performance Schema to DBAs for diagnostic use cases

By analyzing errors that occur on the system

By analyzing which queries are running

By analyzing the audit record of server activity in log files

答案:
By analyzing I/O wait statistics

By analyzing historical performance data

By analyzing which queries are running

说明：
Performance Schema
• A set of in-memory tables that MySQL uses to track performance metrics
– Implemented as the PERFORMANCE_SCHEMA storage engine
— Operates on tables in the performance_schema database
• Helps provide insight into database activity. For example:
– Which queries are running
– I/O wait statistics
– Historical performance data
• Only available if support is configured during the build
– Always available in Oracle binary distributions
– If available, it is enabled by default.
— To enable or disable it explicitly, start the server with the
performance_schema variable set to an appropriate value.

Which Performance Schema instruments have no subcomponents? (Choose three)

idle

memory

statement

error

transaction

stage

答案:
idle
error
transaction

说明：
Top-Level Instrument Components
• idle: An instrumented idle event. This instrument has no subcomponents.
• error: An instrumented error event. This instrument has no subcomponents.
• memory: An instrumented memory event
• stage: An instrumented stage event
• statement: An instrumented statement event
• transaction: An instrumented transaction event. This instrument has no further
components.
• wait: An instrumented wait event

Which command configures and enables slow query logging?

SET GLOBAL slow_query_log_file=’slow-query.log’;

SET GLOBAL log_output=’TABLE’;

SET GLOBAL slow_query_log=ON;

SET SESSION long_query_time=0.5;

答案:
SET GLOBAL slow_query_log=ON;

Why is the General Query Log used in MySQL? (Choose two)

To record statements where execution time exceeds a specified threshold

To include update operations stored as row-based binary logs on slave servers

To discover queries with excessive execution time.

To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

答案:
To record the time and type of each connection and the process ID of all operations

To record all statements executed against all tables

说明：
General Query Log
• Is enabled by using the general_log server option
• Records connection information and details about statements received
– Records the time and type of each connection and the process ID of all operations
– Records all statements that are executed against all tables
– Excludes update operations stored as row-based binary log on slave servers
• Grows very quickly
– Enable it for short periods to gather a full record of all activities during those periods
• Has a large overhead
– Not feasible to enable it for long periods on busy production systems

Which command displays collected server metrics stored in the performance schema?

SELECT * FROM setup_objects LIMIT 5\G;

SELECT * FROM setup_actors LIMIT 5\G;

SELECT * FROM setup_consumers LIMIT 5\G;

SELECT * FROM setup_instruments LIMIT 5\G;

答案:

SELECT * FROM setup_instruments LIMIT 5\G;

说明：
Performance Schema Setup Tables
You configure the Performance Schema by modifying the contents of the setup_% tables.
• setup_actors: Which foreground threads (client connections) are monitored
• setup_objects: Which database objects (tables, stored procedures, triggers, events)
are monitored
• setup_threads: Which thread classes are instrumented
• setup_instruments: Which server metrics the Performance Schema collects
• setup_consumers: Where the instrumented events are stored

Which MySQL logs can be stored in tables? (Choose two)

Slow query log

Error log

Binary log

General query log

Audit log

答案：

Slow query log

General query log

说明：
Log File Characteristics
• Can use large amounts of disk space
• Can be stored in files
• Can be stored in tables
– General and slow query logs only
• Can be encrypted
– Audit and binary logs only
• Are written in text format
– Except binary log