From owner-freebsd-database@FreeBSD.ORG Thu Jul 11 21:08:01 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 39831893; Thu, 11 Jul 2013 21:08:01 +0000 (UTC) (envelope-from artem.naluzhnyy@gmail.com) Received: from mail-we0-x22b.google.com (mail-we0-x22b.google.com [IPv6:2a00:1450:400c:c03::22b]) by mx1.freebsd.org (Postfix) with ESMTP id 7A409162D; Thu, 11 Jul 2013 21:08:00 +0000 (UTC) Received: by mail-we0-f171.google.com with SMTP id m46so7403294wev.2 for ; Thu, 11 Jul 2013 14:07:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type; bh=erX7BSvS/+JGXgu0ZR797YljNpENpjJTIq6rAuTDR6M=; b=U6sx+MLMZOPGLY4t5scTLwORo9g/QhB2xsyUD1SYktg9fhFMQjXjNnyFAhvIx2rv2o WXtlKFLD6Vs6XM20zV2eeQUB+P4K0rfufGxp/j4qcY924gQbvd+xdRUk1ceZm6M0KZuP M/fuZ3RGvh2CnVu9IFeckC7OmKwxjPswRaNAV6E5x1Hg6Lxs8fWjLoonLYgSiTpMmZze 951+GtzB0mU37nlAT22bESFbzydkSSw6yCThthJOHgDdmK9fwLNBgG8boib8ZX4PTfW/ C8d+X7Lwt+474pBHQ/AN8xGwrvePLZT9OAW2VsmvmEAChsKlmPSR1quax++0qHCMiLLa eJQQ== X-Received: by 10.181.11.227 with SMTP id el3mr16669415wid.31.1373576879571; Thu, 11 Jul 2013 14:07:59 -0700 (PDT) MIME-Version: 1.0 Sender: artem.naluzhnyy@gmail.com Received: by 10.216.203.68 with HTTP; Thu, 11 Jul 2013 14:07:19 -0700 (PDT) In-Reply-To: References: From: Artem Naluzhnyy Date: Fri, 12 Jul 2013 00:07:19 +0300 X-Google-Sender-Auth: Wv2vzzctBS3XMN8UzLrHMHLKl7Y Message-ID: Subject: Re: RAID10 stripe size and PostgreSQL performance To: Ivan Voras Content-Type: text/plain; charset=UTF-8 Cc: freebsd-fs@freebsd.org, freebsd-database@freebsd.org X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 11 Jul 2013 21:08:01 -0000 On Mon, Jul 8, 2013 at 6:16 PM, Ivan Voras wrote: > On 08/07/2013 14:40, Artem Naluzhnyy wrote: >> Is this expected behavior with more than twice higher pgbench tps on >> 1MB stripe size? > > No, it is not. > > For start, can you please repeat your benchmarks but with restarting the > PostgreSQL server between each pgbench run? Fresh OS installation without DB warning, reboot after pgbench DB initialization (DB size: 26 GB) before benchmarking: * 32 KB (half of the UFS bsize) - tps=198 * 64 KB - tps=226 * 128 KB (default for the RAID controller) - tps=298 * 1 MB (max for the RAID controller) - tps=347 > Also, you should make sure that the database is located on the same > location on the disk platters by e.g. creating a small partition which > is about 150% larger than your pgbench database (and your pgbench > database should be at least 2x larger than your RAM, if you are going to > benchmark IO and not memory caches), which is located at the same > position (byte offset) in your RAID10 volume. Unfortunately it's not that easy to make a custom partitioning. However, all tests were done just after the server reinstallation using exactly the same order of commands. The server has 24 GB RAM, so with 88 GB DB we have: * 32 KB stripe - tps=161 * 1 MB stripe - tps=258 The server is used for VoIP billing, there are also lots of plain-text log files dumping. Had it still better use 1 MB stripe size, or it might have some side effects on performance. -- Artem Naluzhnyy