Writing BLOBs (IDS 9.21)
Posted in 2003
Topics: Installation, Setup & Upgrades, Connectivity: ESQL/C, 4GL & Embedded SQL, Versions, Editions & End-of-Life
I am hoping that someone else will have encountered this problem and knows how to solve it. We're working with IDS 9.21 set up as a real-time system (i.e., very high throughput). Part of this setup requires us to load lots of 150Kb blobs in a very short time. Our old loading programs ESQL-C (using shared memory communication) and ifx_lo_write to load the blobs into the database. However, this turned out to be very slow, because ifx_lo_write would only write 2kb/io, thus using up all the disk capacity at very low write speeds. At the time when we had this problem, Informix were unable to solve the problem. We finally discovered a work-around by using the FileToBlob function writing from files stored in /tmp/; for reasons unknown, filetoblob wrote much more kb/io, and was thus significantly faster. However, recently we have upgraded our hardware and run into the following problem: FileToBlob does not appear to scale. Despite having a disk array that is 3-4 times as fast as the old one, FileToBlob does not load significantly faster and only uses 20% of the disk write capacity (about 8 Mb/s through IDS) when writing 150Kb blobs. This is regardless of how many threads we load with - it refuses to write more than 8 Mb/s with this size of blob. Strangely, if we increase the blob size to 1.5 Mb (last ditch option -- too much work), then we can achieve 30-40 Mb/s and 100% disk usage, but not with smaller blobs. Using ifx_lo_write does scale, but since it is still writing 2kb/io, it is still too slow even on the new disk array. Has anyone encountered this problem and - if so - do you know how to fix it? One remains with a feeling that there must be some setting somewhere that can be tweaked to either get filetoblob to use the entire disk array or to get ifx_lo_write to write with more than 2kb/io, but our system is highly optimized and we have tried with all the obvious (IOVPs, buffering, etc) without any effect. Suggestions very welcome.