May 292013
 
Article MySQL

Sometimes, when working with large volumes of data, such as some geographical information databases, the disk space used can become an issue. To reduce the disk space requiered to hold this data, MySQL can use compressed tables. Although compression is available for both InnoDB and MyISAM tables, the compression ratio achieved with MyISAM is much higher.

This post explains how to setup table compression for tables using MyISAM storage engine.

Drawbacks of table compression

The main drawback in compressing MyISAM tables is that compressed tables are read-only.

compressed InnoDB tables remain read-write, but as already mentioned, the compression ratio achieved is much lower.

Compressing a MyISAM table

A MyISAM table “TABLENAME” in a database “DBNAME” can be compressed with the commands:

  • The utility myisampack performs the compression
  • “myisamchk -rq” regenerates indexes
  • Finally, “mysqladmin flush-tables” forces mysql to start using the new table.

We must make sure that no application will try to make changes to the table while it is being compressed. The safest way is to stop the database, compress the table and start again the database. If this is not an option, we can execute myisampack with the option “–wait”. This only works if the database is running with external locking enabled.

The status of the table, before and after compression, can be checked with the command:

myisampack options

myisampack accepts several options. The most common are:

  •  --help-? 
    Print a help message and exit.
  •  --backup-b 
    Make a backup of each table, as  TABLE_NAME.OLD.
  •  --force-f 
    Generate a compressed table even if the final size is greater than the original, or in cases where there exists already an intermediate file resulting from a previous execution of  myisampack. (myisampack creates an intermediate file named   TABLE_NAME.TMD during the compression. If the command ends unexpectedly, the intermediate file could be left behind in the database directory) .
  •  --join=big_tbl_name-j big_tbl_name
    Merges all the tables specified in the command line into a single table named  big_tbl_name. All tables to be merged must share the same structure (the same names and data types for columns, indexes, etc.) The destination table must not exist previously. The join operation does not create a file.frm. Once it finishes, a .frm file of one of the source tables must be copied as big_tbl_name.frm.
  •  --silent-s
    Silent mode. Only error messages are output
  •  --test-t
    Do not perform the compression, just check that it is possible
  •  --tmpdir=path-T path
    Use the specified directory to create temporary files in it
  •  --verbose-v
    Output detailed messages on the progress of the operation and the final result.
  •  --wait-w
    Wait until the table is not in use.

Uncompress a MyISAM table

The uncompression of a table can be achieved with the –unpack option of the myisamchk command:

—-

 Posted by at 3:55 pm

 Leave a Reply

(required)

(required)