Compress large read only table

[si-the-pie]

The map_leg_detail table describes the edges in the routing graph. Some of these are CSV fields (e.g. distances and elevations) which compress well.

The following technique (based on this) has been manually applied to the current routing edition, reducing the table size from 13G to just under 6G. After the compression procedure the table works in the same way as previously. The reduced data size should yield a modest performance boost, although no benchmarking has been performed.

This procedure could be added to the main build process, but is a bit fiddly as involves working as root user on the command line, possibly while the mysql service is stopped.

# [email protected]:#
cd /var/lib/mysql/routing190109

# File sizes before compression (data for routing190101 is 13G)
ls -lh map_leg_detail.*

# Check status
myisamchk -dvv map_leg_detail

# Pack (takes five minutes)
date
myisampack map_leg_detail
date

# Check compressed tables (takes three minutes)
date
myisamchk -rq map_leg_detail
date

# File sizes after compression
ls -lh map_leg_detail.*
# Data file is 5.9G (before was 13G)

# Flush tables
mysql -uroot -e "flush tables;"