WARNING: Buffer pool size is too small for read ahead operation
John Dargan asked whether to ignore or fix the warning that the buffer pool was too small for read ahead during large loads. Eric Rowell said it was his call: ignore, enlarge the buffer pool or change the load method. Art Kagel suggested reducing read ahead, such as AUTO_READAHEAD 8,1. Mark Scranton recommended express mode loads without indexes or constraints.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------
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 ------------------------------
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 ------------------------------
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 ------------------------------