X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 99C9FD1B16C for ; Thu, 13 May 2004 20:35:12 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47539-06 for ; Thu, 13 May 2004 20:34:50 -0300 (ADT) Received: from roam.unifiedmind.com (unifiedmind.com [209.164.72.58]) by svr1.postgresql.org (Postfix) with SMTP id 22BD1D1B1BD for ; Thu, 13 May 2004 20:34:47 -0300 (ADT) Received: (qmail 29203 invoked from network); 13 May 2004 23:57:11 -0000 Received: from unknown (HELO jamesthornton.com) (james@65.65.104.48) by 0 with SMTP; 13 May 2004 23:57:11 -0000 Message-ID: <40A40670.5030901@jamesthornton.com> Date: Thu, 13 May 2004 18:36:16 -0500 From: James Thornton User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Hadley Willan Cc: Bjoern Metzdorf , pgsql-performance@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [PERFORM] Quad processor options - summary References: <40A2A7FA.6060403@turtle-entertainment.de> <40A3E250.2020204@jamesthornton.com> <40A3EE5B.1050104@turtle-entertainment.de> <1084489156.5424.31.camel@atlas.sol.deeper.co.nz> In-Reply-To: <1084489156.5424.31.camel@atlas.sol.deeper.co.nz> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200405/139 X-Sequence-Number: 13455 Hadley Willan wrote: > To answer question 1, if you use software raid the chunk size is part of= =20 > the /etc/raidtab file that is used on initial container creation. 4KB is= =20 > the standard and a LARGE chunk size of 1MB may affect performance if=20 > you're not writing down to blocks in that size continuously. If you=20 > make it to big and you're constantly needing to write out smaller chunks= =20 > of information, then you will find the disk "always" working and would=20 > be an inefficient use of the blocks. There is some free info around=20 > about calculating the ideal chunk size. Looking for "Calculating chunk=20 > size for RAID" through google. "Why does the SAME configuration recommend a one megabyte stripe width?=20 Let=92s examine the reasoning behind this choice. Why not use a stripe=20 depth smaller than one megabyte? Smaller stripe depths can improve disk=20 throughput for a single process by spreading a single IO across multiple=20 disks. However IOs that are much smaller than a megabyte can cause seek=20 time to becomes a large fraction of the total IO time. Therefore, the=20 overall efficiency of the storage system is reduced. In some cases it=20 may be worth trading off some efficiency for the increased throughput=20 that smaller stripe depths provide. In general it is not necessary to do=20 this though. Parallel execution at database level achieves high disk=20 throughput while keeping efficiency high. Also, remember that the degree=20 of parallelism can be dynamically tuned, whereas the stripe depth is=20 very costly to change. Why not use a stripe depth bigger than one megabyte? One megabyte is=20 large enough that a sequential scan will spend most of its time=20 transferring data instead of positioning the disk head. A bigger stripe=20 depth will improve scan efficiency but only modestly. One megabyte is=20 small enough that a large IO operation will not =93hog=94 a single disk for= =20 very long before moving to the next one. Further, one megabyte is small=20 enough that Oracle=92s asynchronous readahead operations access multiple=20 disks. One megabyte is also small enough that a single stripe unit will=20 not become a hot-spot. Any access hot-spot that is smaller than a=20 megabyte should fit comfortably in the database buffer cache. Therefore=20 it will not create a hot-spot on disk." The SAME configuration paper says to ensure that that large IO=20 operations aren't broken up between the DB and the disk, you need to be=20 able to ensure that the database file multi-block read count (Oracle has=20 a param called db_file_multiblock_read_count, does Postgres?) is the=20 same size as the stripe width and the OS IO limits should be at least=20 this size. Also, it says, "Ideally we would like to stripe the log files using the=20 same one megabyte stripe width as the rest of the files. However, the=20 log files are written sequentially, and many storage systems limit the=20 maximum size of a single write operation to one megabyte (or even less).=20 If the maximum write size is limited, then using a one megabyte stripe=20 width for the log files may not work well. In this case, a smaller=20 stripe width such as 64K may work better. Caching RAID controllers are=20 an exception to this. If the storage subsystem can cache write=20 operations in nonvolatile RAM, then a one megabyte stripe width will=20 work well for the log files. In this case, the write operation will be=20 buffered in cache and the next log writes can be issued before the=20 previous write is destaged to disk." --=20 James Thornton ______________________________________________________ Internet Business Consultant, http://jamesthornton.com