From owner-freebsd-fs@FreeBSD.ORG  Wed Mar  5 02:42:36 2014
Return-Path: <owner-freebsd-fs@FreeBSD.ORG>
Delivered-To: freebsd-fs@freebsd.org
Received: from mx1.freebsd.org (mx1.freebsd.org
 [IPv6:2001:1900:2254:206a::19:1])
 (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits))
 (No client certificate requested)
 by hub.freebsd.org (Postfix) with ESMTPS id A43CC3D0
 for <freebsd-fs@freebsd.org>; Wed,  5 Mar 2014 02:42:36 +0000 (UTC)
Received: from blade.simplesystems.org (blade.simplesystems.org [65.66.246.74])
 (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
 (No client certificate requested)
 by mx1.freebsd.org (Postfix) with ESMTPS id 5D938FA7
 for <freebsd-fs@freebsd.org>; Wed,  5 Mar 2014 02:42:35 +0000 (UTC)
Received: from freddy.simplesystems.org (freddy.simplesystems.org
 [65.66.246.65])
 by blade.simplesystems.org (8.14.4+Sun/8.14.4) with ESMTP id s252eb9k020123;
 Tue, 4 Mar 2014 20:40:38 -0600 (CST)
Date: Tue, 4 Mar 2014 20:40:37 -0600 (CST)
From: Bob Friesenhahn <bfriesen@simple.dallas.tx.us>
X-X-Sender: bfriesen@freddy.simplesystems.org
To: Olav Gjerde <olav@backupbay.com>
Subject: Re: Is LZ4 compression of the ZFS L2ARC available in any
 RELEASE/STABLE?
In-Reply-To: <CAJ7kQyFf19Un_TS=kW=T21HT+oabhsUhJij5oixQ2_uh0LvHRA@mail.gmail.com>
Message-ID: <alpine.GSO.2.01.1403042037290.1717@freddy.simplesystems.org>
References: <CAJ7kQyGTOuynOoLukXbP2E6GPKRiBWx8_mLEchk90WDKO+o-SA@mail.gmail.com>
 <53157CC2.8080107@FreeBSD.org>
 <CAJ7kQyGQjf_WbY64bLVX=YfmJUfAd8i22kVbVhZhEWPMg7bbQw@mail.gmail.com>
 <5315D446.3040701@freebsd.org>
 <CAJ7kQyFf19Un_TS=kW=T21HT+oabhsUhJij5oixQ2_uh0LvHRA@mail.gmail.com>
User-Agent: Alpine 2.01 (GSO 1266 2009-07-14)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-4.2.2
 (blade.simplesystems.org [65.66.246.90]);
 Tue, 04 Mar 2014 20:40:38 -0600 (CST)
Cc: freebsd-fs@freebsd.org
X-BeenThere: freebsd-fs@freebsd.org
X-Mailman-Version: 2.1.17
Precedence: list
List-Id: Filesystems <freebsd-fs.freebsd.org>
List-Unsubscribe: <http://lists.freebsd.org/mailman/options/freebsd-fs>,
 <mailto:freebsd-fs-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-fs/>
List-Post: <mailto:freebsd-fs@freebsd.org>
List-Help: <mailto:freebsd-fs-request@freebsd.org?subject=help>
List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-fs>,
 <mailto:freebsd-fs-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Wed, 05 Mar 2014 02:42:36 -0000

On Tue, 4 Mar 2014, Olav Gjerde wrote:

> I managed to mess up who I replied to and Matthew replied back with a good
> answer which I think didn't reach the mailing list.
>
> I actually have a problem with query performance in one of my databases
> related to running PostgreSQL on ZFS. Which is why I'm so interested in
> compression for the L2ARC Cache. The problem is random IO read were
> creating a report were I aggregate 75000 rows takes 30 minutes!!! The table
> that I query has 400 million rows though.
> The dataset easily fit in memory, so if I run the same query again it takes
> less than a second.

Make sure that your database is on a filesystem with zfs block-size 
matching the database block-size (rather than 128K).  Otherwise far 
more data may be read than needed, and likewise, writes may result in 
writing far more data than needed.

Regardless, L2ARC on SSD is a very good idea for this case.

Bob
-- 
Bob Friesenhahn
bfriesen@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/