Anyway, it occured to me that I could create a special file for each database user of limited size, and make a filesystem on top of that file. The file could be owned by the user. Then, each user could have a seperate database and that database would be at the location of the mountpoint of that filesystem.
It seems like a strange thing to do (an understatement, to be sure), but is there anything wrong with it? Are there performance considerations when using loopback devices like that? Are there dangers of corruption? How does journaling (ext3, reiser, etc) work in that regard?
My experience is that too many filesystem layers increase the risk of corruption (by amplifying the effects of bugs, there's nothing inherently wrong with it). For example, I tried running a loopback filesystem (both with and without encryption) on top of an XFS partition over LVM spread over four partitions. I could build the filesystem (ext2 or ext3) on the loopback filesystem, sync, unmount, (cleanly) reboot, and mount again and the loopback fsys was always corrupt. On the other hand, a loopback fsys on top of ext3 without LVM seemed to work fine, as did loopback over a raw disk partition (only useful for cryptoloop, obviously).
I have no recent experience with reiserfs, I became an xfs convert long ago. I also have no experience with a pgsql database specifically on a loopback filesystem, tho msql (eeew!) and mysql (customer's idea, not mine) didn't seem to mind. To the process a loopback filesystem *is* a filesystem, it shouldn't be able to tell the difference.
A journalling filesystem on the underlying partition will treat changes to the backing file just like changes to any other large file. If the loop filesys also has a journalling filesystem on it, unless you've specified an alternate journal location, the journal is written onto the backing file, and those changes (appearing as changes to a regular file) are handled by the underlying filesystem. One journaling filesys on top of another does slow things down more than, say, ext2 over ext3. I'd hope sync is smart enough to do the loopback before the underlying, but make sure and cleanly unmount the loopback before the underlying partition just in case.
Performance is definitely reduced, although not nearly as bad as with loopback AES encryption. Making the loopback file on a fresh filesystem helped performance substantially; if your backing file isn't reasonably contiguous, the thrashing gets to be a bit much.
Are you referring to IP-based virtual hosting? If so, you may wish to look into linux virtual machines (i.e., kernels run in userspace). It seems to be more secure than chroot-based virtual hosting (or at least easier to make secure), although memory consumption is much greater. Of particular interest to you would be the ability to limit disk usage on a per-virt basis.
-- Bill
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
