1Z0-888 Question #57: Real Exam Question with Answer & Explanation

Sign in or unlock 1Z0-888 to reveal the answer and full explanation for question #57. The question stem and answer options stay visible for context.

Performance Tuning

Question

The 'applicationdb' is using InnoDB and consuming a large amount of file system space. You have a / backup partition available on NFS where backups are stored. You investigate and gather this information:
[mysqld]
datadir=/var/lib/mysql/
innodb_file_per_table=0
Three tables are stored in the InnoDB shared tablespace and the details are as follows:
The table data_current has 1,000,000 rows.
The table data_reports has 1,500,000 rows.
The table data_archive has 4,500,000 rows.

shell> ls -l /var/lib/mysql/
-rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdatal
-rw-rw---- 1 mysql mysql 480M Aug 26 13:34 ib_logfile0
-rw-rw---- 1 mysql mysql 480M Aug 26 13:47 ib_logfile1
...
You attempt to free space from ibdatal by taking a mysqldump of the data_archive table and storing it on your backup partition.
shell> mysqldump -u root -p applicationdb data_archive > /backup/data_archive.sql
mysql> DROP TABLE data_archive;
Unfortunately, this action does not free any actual disk space back to the file system and the server disk space is running out. Which set of actions will allow you to free disk space back to the file system?

Options

AEnable compression on the table, causing InnoDB to release unused pages on disk to the file system: mysql> SET GLOBAL innodb_file_per_table=1; mysql> SET GLOBAL innodb_file_format=Barracuda; mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
BExecute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system: mysql> OPTIMIZE TABLE data_current, data_reports;
CSet the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own: mysql> SET GLOBAL innodb_file_per_table=1; mysql> ALTER TABLE data_current ENGINE=InnoDB; mysql> ALTER TABLE data_reports ENGINE=InnoDB;
DTake a backup, stop the server, remove the data files, and restore the backup: shell> mysqldump -u root -p applicationdb > /backup/applicationdb.sql shell> /etc/init.d/mysql stop shell> cd /var/lib/mysql/ shell> rm ibdatal ib_logfil0 ib_logfile1 shell> /etc/init.d/mysql start shell> mysql -u root -p applicationdb < /backup/applicationdb.sql

Unlock 1Z0-888 to see the answer

You've previewed enough free 1Z0-888 questions. Unlock 1Z0-888 for full answers, explanations, the timed quiz mode, progress tracking, and the master PDF. Question stem and options stay visible so you can still see what's on the exam.

Unlock 1Z0-888 - $49.99 / 30 days Sign in

Topics

#InnoDB tablespace management#Disk space reclamation#Table compression#innodb_file_per_table

Full 1Z0-888 Practice