Dear list, I've made a patch that modifies the way sqlbox uses to retrieve messages from the DB.
The current method was making a query to return a single record, process it, retrieve another one, process it, etc, etc. One by one. This works fine for moderate loads. However, under heavy usage, we've found that the load on the sql server and atomicity of the process caused a bottleneck on our production servers. To overcome that, I've patched sqlbox. What I did is to bring many records at once from the DB, implementing an internal queue based on a gwlib's List object. With this patch, sqlbox works as follows: 1. Retrieve N messages from SQL and store them on a List (N is configurable by the added "limit-per-cycle" config directive. 2. Retrieve messages from List and process them one by one as before. 3. Repeat ad infinitum. 4. If the query comes empty, sleep for 1 second (just as it was before). The default value for "limit-per-cycle" (if parameter is missing) is 10. That means that each SQL query retrieves 10 messages instead of 1, softening the load. It can be tweaked to whatever positive value you want. The patch is here: http://www.magicom-bcn.net/kannel/sqlbox-standalone-multi-20080227.patch We've experienced an increase of 100-150% in performance on our production servers, and way less load on the DB. We've found the optimum performance at 500 records per query, but I think that depends on the particular hardware deployment (over that number we've experienced worst numbers, probably memory/disk bottlenecks kicking in on our setup). NOTE: Only the mysql module is fixed, if there's interest I can fix pgsql as well. There's still some room for optimizations and security enhancements. For example the delete cycle still occurs after the select, effectively creating a window of opportunity to lose messages if the process is killed (limited to the "limit-per-cycle" at worst). The ideal solution would be to move the delete to a separate function and call it just after the sending (thus changing the scenario from losing N messages to sending one twice), but I still need to figure out a way to pass the sql_id between the mysql module and the core sqlbox (it's not on the Msg object I'm storing on the List. Any ideas?). Regards, -- Alejandro Guerrieri Magicom http://www.magicom-bcn.net/ LinkedIn: http://www.linkedin.com/in/aguerrieri
