ZFS and Informix
A user planning a 96GB Informix "hotel" on ZFS asked whether to rely on ZFS's ARC or Informix's own buffer pool (avoiding double caching), whether Informix's cache uses anything beyond LRU, and whether big nightly batch scans trash the buffer pool. Replies pointed to Informix light scans (relaxed in newer versions via BATCHEDREAD_TABLE, though dirty read is still required) and to Art Kagel's filesystem rant. Consensus advice: for OLTP, give memory to Informix's buffer pool rather than the filesystem cache, since the engine writes through with O_SYNC/O_DIRECT and small writes amplify at array level; Kagel reported no degradation up to ~20GB of buffers. No formal benchmarks were offered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello! There's not much information regarding ZFS and Informix, naturally, so I thought I would throw this out there. We have had some discussions internally about how to configure ZFS for our new Informix hotel. Our hotel is going to have 96 GB of memory and we're going to have two instances, each with about 40 databases without any joins between databases (each database is more or less in it's own cage). So we're trying to decide whether we trust ZFS's ARC more than we trust Informix's own buffercache (we naturally don't want to be buffering data twice). I am well aware that an I/O operation is going to be more expensive than an in-buffer transaction, but we weren't sure whether Informix would be able to do a sequential scan through 40 GBs of pages faster than ZFS could find pages in the ARC. Also, there's some logic in the ZFS ARC to toss out sequential scans. Does the Informix buffer have similar logic? For example, if you have batch jobs running at night, are they ruining the buffer or is Informix simply disregarding them as an anomaly? If you know of any benchmarks out there, I would certainly be interested. Perhaps IBM has some internally? If so, we could take this up with our local rep.
LUKE SIMMONS Wrote: ============================================================================= Hello! There's not much information regarding ZFS and Informix, naturally, so I thought I would throw this out there. We have had some discussions internally about how to configure ZFS for our new Informix hotel. Our hotel is going to have 96 GB of memory and we're going to have two instances, each with about 40 databases without any joins between databases (each database is more or less in it's own cage). So we're trying to decide whether we trust ZFS's ARC more than we trust Informix's own buffercache (we naturally don't want to be buffering data twice). I am well aware that an I/O operation is going to be more expensive than an in-buffer transaction, but we weren't sure whether Informix would be able to do a sequential scan through 40 GBs of pages faster than ZFS could find pages in the ARC. Also, there's some logic in the ZFS ARC to toss out sequential scans. Does the Informix buffer have similar logic? For example, if you have batch jobs running at night, are they ruining the buffer or is Informix simply disregarding them as an anomaly? If you know of any benchmarks out there, I would certainly be interested. Perhaps IBM has some internally? If so, we could take this up with our local rep. ============================================================================= Response: With regards to Informix's handling of large sequential scans and their potential impact on buffers, read up on Informix Light Scans. http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm .perf.doc%2Fids_prf_237.htm HTH, Dave Griffen
Check out Art's rant on this topic... http://informix-myview.blogspot.com/2010/07/new-journaled-filesystem-rant.html
This is interesting, but, if I understand correctly, light scans occur only when there is not enough space in the buffer pool to cache the pages, which, in our case, isn't really the problem. I was mostly curious if the cache uses any other algorithm other than LRU, which I'm kind of assuming now after reading a bit more that it doesn't. I'm still curious whether there is any benchmarking out there on how fast the buffer pool in Informix is, perhaps maybe not on ZFS, but head-to-head against Oracle or some other database vendor. I'm especially curious about how well it scales in response time when you go from a couple of GBs up to say 64.
This is an interesting post and I can't really say that I disagree with any one thing, but it's important with these discussions that you don't get locked down into looking at the trees, but rather take a step back and look at the forest that is your application and environment. With a ZFS intent log, I can write all of my pages directly to an SSD disk, read everything from a 180GB L2ARC on a stripped I/O Fusion cards and suddenly the redundancy of journaling data twice or performing a read on fragmented blocks doesn't seem like much of a problem. Plus, I can snapshot databases, backup snapshots and clone databases in a couple of seconds. With ZFS and Butrfs and I would be surprised if Redmond doesn't have something similar in the pipeline, it would be nice to see Art tackle these issues in another blog post, because these type of file systems aren't JFS, UFS, Ext3 or Ext4.
How does Informix stack up against Oracle? I have never heard of any head-to-head private performance benchmark that Informix did not win on performance alone. I have personally witnessed several that Informix did win. Buffer pool performance is not dependent on the filesystem underlying the disk storage of the database's chunks, not for Informix or any other product. Total performance, yes, but that's because of the differences in file IO performance not the buffer pool. Informix's buffer pool scales very well. However, what kind of application is this? OLTP? DSS? DW/DM? 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 3:18 PM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > This is interesting, but, if I understand correctly, light scans occur only > when there is not enough space in the buffer pool to cache the pages, > which, > in our case, isn't really the problem. I was mostly curious if the cache > uses > any other algorithm other than LRU, which I'm kind of assuming now after > reading a bit more that it doesn't. > > I'm still curious whether there is any benchmarking out there on how fast > the > buffer pool in Informix is, perhaps maybe not on ZFS, but head-to-head > against > Oracle or some other database vendor. I'm especially curious about how > well it > scales in response time when you go from a couple of GBs up to say 64. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bafbf8b15e304b8deaf48
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.
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
Most if not all the large buffer caches I see at customer reveal a 99.xx with xx typically higher than 50 (OLTP systems) So although possible, you can't reach much higher than that... Note that this are Informix engine statistics. Regarding LIGHT scans, latest versions, with BATCHEDREAD_TABLE activated have less restrictions (the table doesn't need to be bigger than buffer cache). But there are still some things impossible to overcome (you must be in DIRTY READ isolation for example) Regards. On Mon, Feb 13, 2012 at 9: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. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00248c6a6a42c8d02f04b8e284db