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.
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
waiting_trx_id
blocking_trx_id
blocking_pid
waiting_pid
Table locks
Storage engine data locks
Row-level locks
Locks that apply to internal resources
Metadata locks
SHOW GLOBAL STATUS\G
SHOW PROCESSLIST\G
SHOW MASTER STATUS\G
SELECT * FROM sys.session\G
SELECT * FROM performance_schema.session_status;
changes in server connection credentials
increasing data volumes
changes in application usage patterns
server migration to a new subnet
changes in the exploratory configuration
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.
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
waiting_trx_id
blocking_trx_id
blocking_pid
waiting_pid
答案：
blocking_pid
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
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.
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
The post MySQL 8.0 for Database Administrators OCP 课程习题8 first appeared on C1G军火库.