Our application is INCREDIBLY write-heavy with highly transient data. Frankly, it doesn't even belong in Informix at all; it ought to be in something like MongoDB or even an in-memory DB. [I'm not kidding: 75-90K inserts per minute, involving BYTE and TEXT columns to boot.]
So when we run syspurge(1,0), it starts but pretty soon, user sessions (almost all inserts) keep piling up in a "waiting on lock" status. Here's the online.log result from a single execution of syspurge:
2024-03-27 16:05:14.548 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_13_30_00_000_2024_03_27_13_40_00_0002024-03-27 16:05:14.548 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_13_30_00_000_2024_03_27_13_40_00_0002024-03-27 16:08:53.283 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_13_40_00_000_2024_03_27_13_50_00_0002024-03-27 16:09:01.287 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_13_40_00_000_2024_03_27_13_50_00_0002024-03-27 16:10:26.692 Dynamically allocated new virtual shared memory segment (size 524288KB)2024-03-27 16:10:26.692 Memory sizes:resident:94208 KB, virtual:6815744 KB, message:0, bufferpool:22296576, no SHMTOTAL limit2024-03-27 16:12:27.312 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_13_50_00_000_2024_03_27_14_00_00_0002024-03-27 16:12:35.311 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_13_50_00_000_2024_03_27_14_00_00_0002024-03-27 16:16:10.348 Single-User Mode.2024-03-27 16:16:12.314 On-Line Mode2024-03-27 16:16:12.314 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_00_00_000_2024_03_27_14_10_00_0002024-03-27 16:16:12.315 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_00_00_000_2024_03_27_14_10_00_0002024-03-27 16:16:12.316 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_10_00_000_2024_03_27_14_20_00_0002024-03-27 16:16:12.317 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_10_00_000_2024_03_27_14_20_00_0002024-03-27 16:16:12.317 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_20_00_000_2024_03_27_14_30_00_0002024-03-27 16:16:12.318 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_20_00_000_2024_03_27_14_30_00_0002024-03-27 16:16:12.318 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_30_00_000_2024_03_27_14_40_00_0002024-03-27 16:16:12.319 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_30_00_000_2024_03_27_14_40_00_0002024-03-27 16:16:12.319 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_40_00_000_2024_03_27_14_50_00_0002024-03-27 16:16:12.319 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_40_00_000_2024_03_27_14_50_00_0002024-03-27 16:16:12.320 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_14_50_00_000_2024_03_27_15_00_00_0002024-03-27 16:16:12.320 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_14_50_00_000_2024_03_27_15_00_00_0002024-03-27 16:16:12.321 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_15_00_00_000_2024_03_27_15_10_00_0002024-03-27 16:16:12.321 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_15_00_00_000_2024_03_27_15_10_00_0002024-03-27 16:16:12.330 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_15_10_00_000_2024_03_27_15_20_00_0002024-03-27 16:16:12.330 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_15_10_00_000_2024_03_27_15_20_00_0002024-03-27 16:16:12.331 syspurge(): aeaudit:informix.lg2db_quotes detached lg2db_quotes_2024_03_27_15_20_00_000_2024_03_27_15_30_00_0002024-03-27 16:16:12.331 syspurge(): discarded aeaudit:informix.lg2db_quotes_2024_03_27_15_20_00_000_2024_03_27_15_30_00_000
You'll notice the onmode -j / onmode -m I had to do to "unstick" it.
So for this application, unfortunately, fragment by range doesn't really work for us.
[The script I have to run syspurge first inserts a lock record into a reference table; the clients look for a lock and pause their inserts when they see it. THEN it runs syspurge and finally removes the lock record. That's my ugly hack to get around the issue.]
