Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  WARNING: Buffer pool size is too small for read ahead operation

    Posted 23 days ago

    When I am loading millions of records into a database and I occasionally see a warning message about the buffer pool size being "too small for read ahead operation", should my reaction be: (a) ignore the warning because it only occurs with unusually large loads of data, or (b) increase the size of the buffer pool?



    ------------------------------
    John Dargan
    ------------------------------


  • 2.  RE: WARNING: Buffer pool size is too small for read ahead operation

    Posted 22 days ago

    This would be your call.  The Read Ahead message is letting you know that there is not enough free buffer to perform the operation for at least one query that is running while you're loading the data. So, you really have a few options.

    a) Ignore as your overall performance is OK enough during the load times.

    b) Increase Buffer Pool because performance while loading data is not OK

    c) Change the method you are using to load the data to see if you can reduce the impact on the buffer space
         Link to an OLD FAQ with Options: Loading and Unloading with Informix



    ------------------------------
    ~~~~~~~~~
    Eric Rowell
    ------------------------------

    Original Message


  • 3.  RE: WARNING: Buffer pool size is too small for read ahead operation

    Posted 17 days ago

    Another option would be to reduce the readahead size. The default of 128 pages refreshed after 64 pages have been used is way too aggressive anyway since I am sure that you are using a quality SAN for storage and the drives are doing readahead and the controllers are doing readahead and the SAN itself is going readahead so to my mind there is little value to wasting cache space inside Informix on massive readahead. I typically recommend that my clients set AUTO_READAHEAD 8,1, minimal readahead.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 4.  RE: WARNING: Buffer pool size is too small for read ahead operation

    Posted 4 days ago

    I may be a little rusty, but if you use "Express Mode" for the loads "light scans/light appends" will be used so the Virtual Portion will be used, not the buffer cache(s). The table can't have indexes/constraints so you have to disable or drop them otherwise "Deluxe Mode" will be used by default. If you're doing massive loads: 1) you don't want indexes in place anyway - it'll kill your time due to the index maintenance; 2) if you disable the indexes/constraints, they have to be rebuilt anyway once you re-enable them. Don't do this - you want to rebuild them yourself so you can tweak that process via PDQ/parallelism anyway. 

    Let me know if you have any questions - here or direct email/cell. This was one of the "things" I spent a lot of time explaining to clients over the years, and to be honest was pretty fun to do projects that required this type of operation. 

    Mark Scranton
    mark@markscranton.com
    765-729-2601



    ------------------------------
    Thanks! 
    Mark Scranton
    The Mark Scranton Group
    mark@markscranton.com
    ------------------------------

    Original Message


Related Content