[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: database transactions in smsd

From: alonso
Subject: Re: database transactions in smsd
Date: Sat, 21 Jul 2012 01:03:15 -0600
User-agent: Mozilla/5.0 (X11; Linux i686; rv:12.0) Gecko/20120428 Thunderbird/12.0.1

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.


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
> * 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)

reply via email to

[Prev in Thread] Current Thread [Next in Thread]