Date: Fri, 11 Nov 2005 19:24:04 +0100 (CET) From: Oliver Fromme <olli@lurza.secnetix.de> To: freebsd-database@FreeBSD.ORG Subject: Re: suggested block size for a frequently updated postgresql? Message-ID: <200511111824.jABIO4Sh074288@lurza.secnetix.de> In-Reply-To: <8664qzcz82.fsf@dryice.3322.org>
next in thread | previous in thread | raw e-mail | index | archive | help
Dryice Liu <dryice@hotpop.com> wrote: > I'm planning on setting up a postgresql server, the database data > files are on their own slice so I can tune the file system for pgsql. > > The database is pretty large. On my test server, some data files get > larger than 1G and is splitted by pgsql. Also the database will be > updated frequently. > > I'm planning on setting the slice with a bigger block size/fragment > size but not sure if that's a good idea. I know the default on FreeBSD > is 16K/2K, I'm planning on something like 1M/128K. FreeBSD doesn't support bsize/fsize that large. In fact, I wouldn't trust anything other than 16K/2K and 8K/1K to work reliably under load. I also remember Matt Dillon mentioned that larger block sizes are less-than-optimal for the VM system. Besides, I don't think that PostgreSQL would benefit from such huge fragment and block sizes. Let me explain ... PostgreSQL does two things on the file system: First, it records all modifications to the WAL file. This is a sequential process, and I wouldn't expect the blocksize to have any significant effect. Second, it updates the actual table data in the data files. This consists of appending data to the files and random access inside the files, which would definitely not benefit from a larger block size. Also take into account that the regular "vacuum" process of postgresql is random access inside the data files. For more details on that I recommend you ask in the Post- greSQL list (pgsql-novice). There are very knowledgeable and responsive people there. So, the bottom line is: I recommend you leave the bsize and fsize at the default 16K/2K. However, you probably should reduce the inode density from the default, i.e. use the -i option with some high value such as 262144 (that's 2^18). But don't make this value too high either ... I remember someone tried to set it to 64 million or some- thing, which broke his FS. Best regards Oliver -- Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing Dienstleistungen mit Schwerpunkt FreeBSD: http://www.secnetix.de/bsd Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way. "With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead." -- RFC 1925
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200511111824.jABIO4Sh074288>