Which set of actions will allow you to free disk space back to the file system?

The ‘allplicationdb’ 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 the following 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> is -1 /var/lib/mysql/
-rw-rw—- 1 mysql mysql 744G Aug 26 14:34 ibdata1
-rw-rw—- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0
-rw-rw—- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1
…
You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and
storting it on your backup partition.
Shell> mysqldump – u root – p applicationdb data_archive > /backup/data_archive.sql
Mysql> DROP TABLE data_archive;

Which set of actions will allow you to free disk space back to the file system?

The ‘allplicationdb’ 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 the following 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> is -1 /var/lib/mysql/
-rw-rw—- 1 mysql mysql 744G Aug 26 14:34 ibdata1
-rw-rw—- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0
-rw-rw—- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1
…
You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and
storting it on your backup partition.
Shell> mysqldump – u root – p applicationdb data_archive > /backup/data_archive.sql
Mysql> DROP TABLE data_archive;

Which set of actions will allow you to free disk space back to the file system?

A.
Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the
file system:
Mysql> OPTIMIZE TABLE data_current, data_reports;

B.
Set 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_repors ENGINE=InnoDB;

C.
Take 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 ibdata1 ib_logfile0 ib_logfile1
Shell> /etc/init.d/mysql start
Shell> mysql – u root – p applicationdb < /backup/applicationdb.sql

D.
Enable compression on the table, causing InnoDB to release unused pages on disk to the file
system:
Mysql> SET GLOBLE innodb_file_per_table=1;
Mysql> SET GLOBLE innodb_file_format=Barramcuda;
Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Explanation:

Show Hint

Leave a Reply 14

Your email address will not be published. Required fields are marked *

Me

Me

C.

nitro , You didn’t noticed :

Note
InnoDB always needs the system tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system. When moving large InnoDB tables out of the system tablespace, where disk space is limited, you might prefer to turn on innodb_file_per_table and then recreate the entire instance using the mysqldump command.

Reply

vasben

vasben

I think a mix of C and D.

Shell> mysqldump -u root -p applicationdb > /backup/applicationdb.sql
mysql> drop database applicationdb;
Shell> /etc/init.d/mysql stop

Edit /etc/my.cnf and set innodb_file_per_table = 1

Shell> rm -f ibdata1 ib_logfile0 ib_logfile1
Shell> /etc/init.d/mysql start
mysql> create database applicationdb;
mysql> use applicationdb;
mysql> source /backup/applicationdb.sql

This is the only way I know to release the disk space occupied by the ibdata1 file.

Reply

vasben

vasben

Sorry, C is enough becouse we dumped and dropped the data_archive table, then removing the innodb tablespace will free the space previously occupied by the ibdata1 file.

Reply

jeanrock

jeanrock

but how about tables data_reports and data_current, as from beginning, just backup table table_archive only

Reply

LnL

LnL

c

D is wrong due to this:
“Table compression is also not available for the InnoDB system tablespace. The system tablespace (space 0, the ibdata* files) can contain user data, but it also contains internal system information, and therefore is never compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces, that is, created with the innodb_file_per_table option enabled. ”

source: https://dev.mysql.com/doc/refman/5.5/en/innodb-compression-usage.html

Reply