Conventional wisdom regarding Linux filesystems suggests that file defragmentation is not a necessary task. While this is true in general, file fragmentation isn't something we should allow to spiral out of control. PostgreSQL storage files are limited to 1 GB in size, yet we configured XFS to preallocate no more than 1 MB at a time.
This introduces the potential for data fragmentation on OLTP systems or any database cluster where several tables experience high turnover. To prevent this from adversely affecting sequential scans, and to promote good filesystem health in general, we need to track and potentially correct overly fragmented files.
XFS provides two tools suited to this activity. The first is xfs_db, which provides information about an XFS filesystem. The second is xfs_fsr, which allows us to defragment XFS while it is still mounted and active. This recipe will cover the basic usage of these tools to keep our high availability server performing well.
For this recipe, we want a formatted and active XFS filesystem. Follow the recipe in Formatting an XFS filesystem before continuing. It may also be a good idea to set up a dummy database where you mounted XFS. This way, you can run a pgbench test to create a lot of database write activity so that there is a small amount of data fragmentation. This is not required to follow along with this recipe.
Assuming pg1 is our current primary node and /dev/VG_POSTGRES/LV_DATA is the device we formatted with XFS, follow these steps there as the root user:
- Examine the current fragmentation status with this command:
xfs_db -f -c frag /dev/VG_POSTGRES/LV_DATA - Defragment the filesystem with
xfs_fsr:xfs_fsr -t 600 /dev/VG_POSTGRES/LV_DATA - View real-time fragmentation status afterwards:
xfs_db -f -c frag -r /dev/VG_POSTGRES/LV_DATA
We begin with the xfs_db utility to view the current fragmentation status of the filesystem. The -c parameter lets us specify a command that xfs_db should invoke. In this case, we want it to check the fragmentation status, so we set -c to frag. We set the -f parameter as it allows us to use xfs_db on a mounted filesystem.
Fragmentation status is calculated by counting the number of non-contiguous extents on all files and comparing that number to the total amount of files. To prepare for this, we continuously invoked pgbench to cause a high amount of fragmentation. Here is the fragmentation on our system:
As you can see, our filesystem is 41.52% fragmented. To correct this, we need to use xfs_fsr to reorganize any fragmented files. To do this, we only need to call xfs_fsr with either the device path or the path where the device is mounted. For the sake of consistency, we choose the former.
We can also limit the amount of time XFS spends fixing fragmentation with the -t parameter, which sets the run time in seconds. We chose 600 seconds for an even 10 minutes, but larger systems might require an hour or longer. By setting the -t parameter, we can run xfs_fsr regularly as a maintenance item, so fragmentation is regularly kept in check.
If we examine the filesystem again with xfs_db, our fragmentation should be significantly reduced. Let's consider the following screenshot:
Now our fragmentation is down to 0.65%, which is well within tolerances for good sequential access performance. However, you might have noticed that we added an -r setting just after the -c frag declaration.
Remember when we said XFS maintained an internal database? Due to caching and update intervals, parts of the XFS database are not always accurate. The -r option to the -c frag command tells XFS that we want real-time information about the filesystem, and not what is currently stored in the tracking database.
While we use the xfs_db command to obtain file fragmentation information, it can actually do much more. XFS maintains a small internal database which xfs_db can view or manipulate. Unfortunately, modifying XFS metadata can render the filesystem corrupt or otherwise unusable. We highly recommend never using xfs_db for anything but checking fragmentation statuses.
Only experts should ever use xfs_db command parameters other than frag.