[PATCH] PostgreSQL DLR speedup
Ben Suffolk <[email protected]>
| Newsgroups | gmane.comp.mobile.kannel.devel |
|---|---|
| Message-ID | <[email protected]> |
Hi, I have been doing some benchmarking on the pgsql DLRs. I actually wrote a quick application as opposed to using kannel. The application simulated message sending and DLRs by looping a thousand times over the following :- 1. Adding a random number of messages into the DLR table (and storing them in an in memory hash) 2. Deleting a random number of messages from the DLR table (based on a random selection from the hash) 3. Updating a smaller random number of messages in the DLR table (again select from the hash). These test were carried out on a pretty old box running FreeBSd 6.1 and PostgreSQL 8.1. I ran the benchmark a number of times, and with the existing SQL I was anywhere between 100 and 140 messages a second. I updated the SQL (the delete and update) to remove the OID selection bit. I did not add any LIMIT so that it will still work with the earlier versions of pgsql. Again after running this a number of times I would average between 199 and 201 messages a second (It was a lot more consistent this time). Attached is the patch. Regards Ben
dlr_pgsql.patch
(application/octet-stream, 1.8 KB)
Index: gw/dlr_pgsql.c
===================================================================
RCS file: /home/cvs/gateway/gw/dlr_pgsql.c,v
retrieving revision 1.9
diff -u -5 -r1.9 dlr_pgsql.c
--- gw/dlr_pgsql.c 10 Jan 2006 12:53:29 -0000 1.9
+++ gw/dlr_pgsql.c 9 Nov 2006 16:06:33 -0000
@@ -218,12 +218,12 @@
static void dlr_pgsql_remove(const Octstr *smsc, const Octstr *ts, const Octstr *dst)
{
Octstr *sql;
debug("dlr.pgsql", 0, "removing DLR from database");
- sql = octstr_format("DELETE FROM %s WHERE oid = (SELECT oid FROM %s WHERE %s='%s' AND %s='%s' LIMIT 1);",
- octstr_get_cstr(fields->table), octstr_get_cstr(fields->table),
+ sql = octstr_format("DELETE FROM %s WHERE %s='%s' AND %s='%s';",
+ octstr_get_cstr(fields->table),
octstr_get_cstr(fields->field_smsc),
octstr_get_cstr(smsc), octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));
pgsql_update(sql);
@@ -234,14 +234,13 @@
static void dlr_pgsql_update(const Octstr *smsc, const Octstr *ts, const Octstr *dst, int status)
{
Octstr *sql;
debug("dlr.pgsql", 0, "updating DLR status in database");
- sql = octstr_format("UPDATE %s SET %s=%d WHERE oid = (SELECT oid FROM %s WHERE %s='%s' AND %s='%s' LIMIT 1);",
+ sql = octstr_format("UPDATE %s SET %s=%d WHERE %s='%s' AND %s='%s';",
octstr_get_cstr(fields->table),
octstr_get_cstr(fields->field_status), status,
- octstr_get_cstr(fields->table),
octstr_get_cstr(fields->field_smsc), octstr_get_cstr(smsc),
octstr_get_cstr(fields->field_ts), octstr_get_cstr(ts));
pgsql_update(sql);
octstr_destroy(sql);
}