The data in this instance is transient; no backup or replication will be required. It is currently under performing.
The database size is static and including indexes is 19G.
Total system memory is 32G.
After profiling the system, you highlight these MySQL status and global variables:
The OS metrics indicate that disk is a bottleneck.
Other variables retain their default values.
Which three changes will provide the most benefit to the instance? (Choose three.)
To improve the performance of the MySQL instance given that the disk is identified as the bottleneck, several changes can be made. Increasing the buffer pool size to 24G can reduce disk I/O by allowing more data to be cached in memory, which is beneficial given the 32G total system memory. Disabling innodb_doublewrite can also help reduce disk I/O, understanding that it introduces a risk of data corruption, which is acceptable in this case since the data is transient. Placing the innodb_undo_directory in /dev/shm, a memory-based filesystem, can further reduce disk I/O and improve performance, though it also comes with a potential risk of running out of space for undo logs under heavy write operations. These changes target the identified bottleneck and align with the given scenario where data durability is not a primary concern.
D. sync_binlog=0: This disables the synchronizing of the binary log to disk after every commit. This can reduce disk I/O, but it comes with a risk of losing transactions in case of a crash. Since your data is transient, this might be an acceptable risk. E. innodb_doublewrite=0: Disabling innodb_doublewrite can reduce disk I/O, but it comes with a risk of data corruption in the event of a crash. Since your data is transient and doesn’t require backup or replication, this might be an acceptable risk. G. innodb_undo_directory=/dev/shm: Moving the innodb_undo_directory to a memory-based filesystem like /dev/shm can reduce disk I/O and improve performance. However, this comes with a risk of running out of space for undo logs if the system has a high rate of updates/deletes/inserts.
why D? it log_bin has algredy disabled
Agree DEG is correct
why D? it log_bin has algredy disabled
DEG buffer pool is already larger than data trx makes things worse log file doesn't necessarily make things better
connections also doesn't do anything
this question is the same with number 125. should be CEG.
Agree. bin-log is disabled, D is wrong.
why D? it log_bin has algredy disabled
B. buffer_pool_size=24G: Increasing the buffer pool size to 24G can help reduce disk I/O by keeping more data in memory. This can improve performance, especially if disk is a bottleneck. E. innodb_doublewrite=0: Disabling the doublewrite buffer can reduce disk I/O and improve performance. However, this comes at the cost of increased risk of data corruption in the event of a crash. G. innodb_undo_directory=/dev/shm: Setting the undo directory to /dev/shm can improve performance by storing undo logs in memory instead of on disk.