On Nov 12, 2012, at 7:37 AM, Shaun Thomas wrote: > Hey everyone, > > We recently got bit by this, and I wanted to make sure it was known to the general community. > > In new(er) Linux kernels, including late versions of the 2.6 tree, XFS has introduced dynamic speculative preallocation. What does this do? It was added to prevent filesystem fragmentation by preallocating a large chunk of memory to files so extensions to those files can go on the same allocation. The "dynamic" part just means it adjusts the size of this preallocation based on internal heuristics. > > Unfortunately, they also changed the logic in how this extra space is tracked. At least in previous kernels, this space would eventually be deallocated. Now, it survives as long as there are any in-memory references to a file, such as in a busy PG database. The filesystem itself sees this space as "used" and will be reported as such with tools such as df or du. > > How do you check if this is affecting you? > > du -sm --apparent-size /your/pg/dir; du -sm /your/pg/dir > > If you're using XFS, and there is a large difference in these numbers, you've been bitten by the speculative preallocation system. > > But where does it go while allocated? Why, to your OS system cache, of course. Systems with several GB of RAM may experience extreme phantom database "bloat", because of the dynamic aspect of the preallocation system, So there are actually two problems: > > 1. Data files are reported as larger than their actual size and have extra space around "just in case". Since PG has a maximum file size of 1GB, this is basically pointless. > 2. Blocks that could be used for inode caching to improve query performance are reserved instead for caching empty segments for XFS. > > The first can theoretically exhaust the free space on a file system. We were seeing 45GB(!) of bloat on one of our databases caused directly by this. The second, due to the new and improved PG planner, can result in terrible query performance and high system load since the OS cache does not match assumptions. > > So how is this fixed? Luckily, the dynamic allocator can be disabled by choosing an allocation size. Add "allocsize" to your mount options. We used a size of 1m (for 1 megabyte) to retain some of the defragmentation benefits, while still blocking the dynamic allocator. The minimum size is 64k, so some experimentation is probably warranted. > > This mount option *is not compatible* with the "remount" mount option, so you'll need to completely shut everything down and unmount the filesystem to apply. > > We spent days trying to track down the reason our systems were reporting a load of 20-30 after a recent OS upgrade. I figured it was only fair to share this to save others the same effort. > > Good luck! Oh hey, I've been wondering for a while why our master dbs seem to be using so much more space than their slaves. This appears to be the reason. Thanks for the work in tracking it down! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
