Re: database transactions in smsd

[alonso]

Hi. As I raised the number of messages sent I started to have MySQL
deadlocks again. Eventually I figured out that the solution is to have
an index on the processed field of the outbox table. Otherwise the command

    SELECT id, number, text, dreport FROM outbox \
                         WHERE processed='0' AND CURTIME() >= not_before \
                         AND CURTIME() <= not_after LIMIT 1 FOR UPDATE

locks the entire outbox table while the message is sent.


The reason why in the beginning I did not have an index on that field is
because in the file smsd/sms.tables.mysql.sql the statement that creates
that index is commented out:

-- CREATE INDEX outbox_processed_ix ON outbox (processed);

Perhaps this should not be commented out? That way users create the
index and avoid problems later.

Thanks

Alonso Acuña


On 07/18/2012 06:21 AM, Daniele Forsi wrote:
> 2012/7/18 alonso:
>
>> I think it would be worthwhile to mention somewhere in the documention
>> that running the old MySQL with 2 or more smsd running and sending
>> messages at the same time will not give good performance as only one
>> message will be sent at a time while the others wait for the lock to be
>> released.
> I added to http://wiki.gnokii.org/index.php/SMSD#Troubleshooting
> * long delays during sending might be caused by smsd trying to lock
> the entire table instead of just the row that is being sent while your
> application or another copy of smsd is using the same table (this
> happens with mysql 4.1, while mysql 5.0 and postgresql are reported to
> work as expected)