Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

Examine this statement which executes successfully: "#mysqladmin –uroot –p kill 14" What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

---

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

答案：
it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

答案：
adding more servers to the environment
writing software (application or storage engine) to use multiple locations

说明:
Scaling Up and Scaling Out
• Scaling up:
– Add more CPU, storage, or main memory resources to increase the processing
capacity of any single node.
– In general, scaling up is less complicated than scaling out because of the difficulty in
writing software that performs well in parallel.
• Scaling out:
– Add more servers to the environment to enable more parallel processing.
– Software (application or storage engine) needs to be written to use multiple locations.
– Examples:
— Sharded database
— Replication for analytics or backups
— InnoDB Cluster
— NDB storage engine in MySQL Cluster

Examine this statement which executes successfully: "#mysqladmin –uroot –p kill 14" What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

答案：
blocking_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

答案：
Table locks
Metadata locks

说明：
How MySQL Locks Rows
MySQL locks resources in the following ways:
• Server-level data locks:
– Table locks
– Metadata locks
• Storage engine data locks:
– Row-level locks
– Handled at the InnoDB layer
• Mutexes:
– Lower-level locks that apply to internal resources rather than to data
— Examples: Log files, AUTO_INCREMENT counters, and InnoDB buffer
pool mutexes
– Used for synchronizing low-level code operations, ensuring that
only one thread can access each resource at a time

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

答案：
SHOW PROCESSLIST\G
SELECT * FROM sys.session\G

说明:
Establishing a Baseline
• Define what is normal:
– The baseline is something to compare against when you encounter a problem.
– Over time, changes in the baseline provide you with useful information for capacity
planning.
• Record operating system metrics: filesystem, memory, and CPU usage
– top, iostat, vmstat , sysstat , sar on Linux- or UNIX-based systems
– Resource Monitor and Performance Monitor on Windows
• Record MySQL status and configuration:
– SHOW PROCESSLIST or sys.session to see the running processes
– mysqladmin extended-status to see status variables
— Use -iseconds –relative to record value deltas.
• Profile application use-case response times:
– Log in, search, create, read, update, and delete.

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

答案：
increasing data volumes
changes in application usage patterns

说明：
Measuring What You Manage
• Establish a performance baseline to measure the system’s normal
variable values.
• After every configuration change, measure the variables again and
compare against your baseline.
– Hardware and software upgrades
– Exploratory configuration changes
– Changes in the infrastructure
• Measure variables regularly to update the baseline.
– Changes in application usage patterns
– Data growth over time
• Whenever you encounter a problem, compare values with the baseline.
– When you precisely define a problem, the solution often becomes
obvious
obvious