IDS Forum

Re: ZFS and Informix

Posted By: Art Kagel
Date: Monday, 13 February 2012, at 5:06 p.m.

In Response To: Re: ZFS and Informix (LUKE SIMMONS)

I can't speak directly to the relative merits of Informix cache algorithms
versus ARC. However, I do have one point to make in favor of using the
database's own cache to best advantage (and that means adding as much
memory to it as possible). Informix, and any modern well designed database
system, operates only on pages in its buffer cache. At the very least that
means that at the time that data is being updated the server can only
update a page that is already in its own cache. If the page is not there,
then the server must read it in from the system. This is either a physical
IO or a read from the system cache depending on whether you are using RAW
disk or not. Since we are discussing ZFS filesystem based chunks, that
means no RAW, so read from the system cache. If you have kept the server's
cache relatively small then those modified pages will have to be written
out to disk frequently reducing the write cache hit percentage and the
filesystem cache will not replace that since Informix, and Oracle and most
other RDBMS's, write through the filesystem cache using either o_sync or
o_direct mode IO so any page write at the server level is a physical write
to disk. Beyond that keep in mind that your filesystem's page size is
likely not as small as an Informix page which is 2K or a multiple of 2K and
the underlying disk arrays are most likely striped and configured with a
block size of at least 32K and often (incorrectly) as large as 1MB, so
those small writes at the server level will cause much page rewriting at
the array and disk level reducing the available disk and channel
bandwidth.

I asked about the type of server because in data warehouse systems, things
are different. There is much less writing and IOs are larger both in and
out bound. For an OLTP server, everything above is critical to performance.

My conclusion: Let Informix (or Oracle for that matter) manage the cache.
Filesystem caches are optimized for filesystems and whole file IO.
Database caches are optimized for database IO. Scaling? Can't speak to
over 64GB of Informix cache, but I have managed servers with over 10million
buffers so that's in the 20GB range with no noticeable performance
degradation. Indeed, the number of buffers were built to that level over
time to improve performance.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Mon, Feb 13, 2012 at 4:09 PM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote:

> I'm well aware that the buffer pool has nothing to do with the underlying
> file
> system. We're just trying to figure out what scales better. We know that
> ZFS's
> ARC scales very well (the algorithms come from IBM !!). I trust though the
> people that have been out in the trenches working with Informix and perhaps
> benchmarking performance with other databases, such as Oracle (I'm mostly
> interested in Oracle in this case because the documentation is extensive on
> ZFS and Oracle).
>
> Referring though specifically to the buffer pool, is there anything out
> there
> that shows how well it scales. If not, what's your hunch? Does the buffer
> pool
> take a performance hit the larger you scale it, or do you think it remains
> steady?
>
> This is an OLTP database.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--e89a8f22c68142e69b04b8dfb048

Messages In This Thread

• ZFS and Informix
  LUKE SIMMONS -- Monday, 13 February 2012, at 7:28 a.m.
  • Re: ZFS and Informix
    DAVE GRIFFEN -- Monday, 13 February 2012, at 2:03 p.m.
    • Re: ZFS and Informix
      LUKE SIMMONS -- Monday, 13 February 2012, at 3:18 p.m.
      • Re: ZFS and Informix
        Art Kagel -- Monday, 13 February 2012, at 3:54 p.m.
        • Re: ZFS and Informix
          LUKE SIMMONS -- Monday, 13 February 2012, at 4:09 p.m.
          • Re: ZFS and Informix
            Art Kagel -- Monday, 13 February 2012, at 5:06 p.m.
          • Re: ZFS and Informix
            Fernando Nunes -- Monday, 13 February 2012, at 8:29 p.m.
  • Re: ZFS and Informix
    RON PRIVETT -- Monday, 13 February 2012, at 2:21 p.m.
    • Re: ZFS and Informix
      LUKE SIMMONS -- Monday, 13 February 2012, at 3:46 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.