Memory resident database
The poster asked whether anyone runs a 1 TB Informix 11.5/11.7 database entirely in AIX RAM, and what tuning or operational surprises to expect (backups/logging still on disk, restore after every outage). One reply recalled similar experiments at lastminute.com on Solaris under 10.x. Art Kagel steered him away from a home-grown RAM setup: for the OLAP side use Informix Warehouse Accelerator (an in-memory compressed copy, x86/64 Linux only, can be on the same or a separate box), and for OLTP acceleration use the Solid DB Cache for Informix; both plug into the existing instance transparently. No in-RAM implementation experience was reported; the poster said he'd investigate those options.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anybody have a production database that is 100% in RAM? I am considering putting a 1 Tb Informix 11.5 (or 11.7) database in AIX RAM. I know that backup and logging will probably have to go to disk and I will have to restore every time the machine goes down. But with compressed backups, that might not be too bad. Anyway, if anyone has done this, what issues have you had to deal with? What tuning surprises came up? Thanks, L.S.
On 13/09/2011 21:00, LIGHT SCANS wrote: > Does anybody have a production database that is 100% in RAM? I am > considering putting a 1 Tb Informix 11.5 (or 11.7) database in AIX > RAM. I know that backup and logging will probably have to go to disk > and I will have to restore every time the machine goes down. But with > compressed backups, that might not be too bad. Anyway, if anyone has > done this, what issues have you had to deal with? What tuning > surprises came up? I'm pretty sure they played with this at lastminute.com on Solaris, but after my time there. It would have been 10.x not 11 There may or may not be other people here who know more... -- Clive
Is this an OLTP server or Data Warehouse/Decision Support? If it is the latter, or a combo server, take a serious look at the new Informix Warehouse Accellerator. I keeps a compressed copy of tables you are interested in querying with complex DW style queries (as many tables as needed and as much data as memory will hold with 50-70% compression) and it processes complex queries 100-1500 times as fast as the basic Informix engine can for any complex queries or even simple queries that return significant numbers of rows with filtering. Even using RAM disk, Informix cannot reach that level or performance on its own! If this is for a pure OLTP environment take a look at the Solid DB Cache for Informix. It accelerates OLTP transactions by moving the active portions of your tables into memory data structures optimized for in-memory access. Both solutions work seamlessly with your existing Informix instance and you submit queries to Informix just as you do now, but qualifying queries are forwarded to the IWA or Solid engine for processing. The Solid DB Cache for Informix can also use its own interface, either way it has access to both its own in-memory copy of the data and other data residing in the underlying Informix engine. 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 Tue, Sep 13, 2011 at 4:00 PM, LIGHT SCANS <light_scans@yahoo.com> wrote: > Does anybody have a production database that is 100% in RAM? I am > considering putting a 1 Tb Informix 11.5 (or 11.7) database in AIX > RAM. I know that backup and logging will probably have to go to disk > and I will have to restore every time the machine goes down. But with > compressed backups, that might not be too bad. Anyway, if anyone has > done this, what issues have you had to deal with? What tuning > surprises came up? > > Thanks, > L.S. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
It is an OLAP data warehouse that our vice presidents want to add new OLTP requirements to (with near zero wait time). We looked into compression but Informix can only do that on a table by table basis. And we have a just few tables where that would apply (the largest is 60 Gb). Therefore my rough estimate is that compression would only save us about 150 Gb (because if I unloaded all my data without indexes I would have a 300 Gb file). The Accelerator, based on a quick review, seems to be a RAM copy on a separate server of our choosing. This adds complexity and other issues like network speed. I assume Art is right because I have learned a lot from him (and others in this newgroup) for many years. But since the OLTP queries are simple, the main issue I see with "my" single, local, home-grown RAM system is that the OLAP queries will force OLTP queries to wait (even on RAM!). I guess the Accelerator would act as a copy that we could just let the OLTP queries run on. But we are lucky that we could make our OLAP queries run at night and OLTP during the day.
Actually, it would be the other way around. You would reformat the database for OLTP and let the Warehouse Accelerator handle the OLAP queries in memory. The Warehouse Accelerator can run on the same machine as the server IFF the server is running on Intel Linux (IWA only runs on Linux x86/64) and has enough memory to make it worth your while. Otherwise it can run on a separate Linux machine (regardless of the platform on which the server is running). Believe me, the speed increases for your OLAP queries would more than make up for any network faster than 300baud (tee hee - but you get the point)! If you need to go the other way, OLAP from disk and OLTP from memory, then you need the Solid DB Cache for Informix, which I also described, instead. 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 Tue, Sep 13, 2011 at 6:30 PM, LIGHT SCANS <light_scans@yahoo.com> wrote: > It is an OLAP data warehouse that our vice presidents want to add new > OLTP requirements to (with near zero wait time). We looked into > compression but Informix can only do that on a table by table basis. > And we have a just few tables where that would apply (the largest is > 60 Gb). Therefore my rough estimate is that compression would only > save us about 150 Gb (because if I unloaded all my data without > indexes I would have a 300 Gb file). The Accelerator, based on a > quick review, seems to be a RAM copy on a separate server of our > choosing. This adds complexity and other issues like network speed. > I assume Art is right because I have learned a lot from him (and > others in this newgroup) for many years. But since the OLTP queries > are simple, the main issue I see with "my" single, local, home-grown > RAM system is that the OLAP queries will force OLTP queries to wait > (even on RAM!). I guess the Accelerator would act as a copy that we > could just let the OLTP queries run on. But we are lucky that we > could make our OLAP queries run at night and OLTP during the day. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Great advice. I will look more deeply into the options you suggest. Thanks! L.S.