List:General Discussion« Previous MessageNext Message »
From:Edoardo Serra Date:May 15 2007 3:54pm
Subject: Issue with locking and INSERT DELAYED
View as plain text  
Hi Guys,
	we have a MySQL server acting as a backend for a VoIP provider.

We're using this MySQL server to collect CDRs and to extract some easy 
reports from them (including web access to CDRs for customers)

CDRs are inserted directly from Asterisk switches when a call ends.
We're using INSERT DELAYED to store CDR because we don't want a report 
query to get a lock on the CDR table and prevent CDRs from being 
inserted immediatly (I need the query to return immediatly to avoid 
strange interacions with Asterisk)

1) I see that MyISAM tables should support INSERT and SELECT query 
running simoultaneously but I was getting some INSERT locked during a 
slow SELECT (I don't think CDR table has some free space in it because 
it's used justo to insert rows)

2) I switched to INSERT DELAYED to solve the problem but sometimes I get 
some queries locked again

I did a SHOW FULL PROCESSLIST when I had some locked queries, here are 
running threads
(I omitted Sleeping threads, renumberet threads ids and omitted some 
columns)

[...]
Id - Command - State - Info

1 - Query  - Locked - SELECT count(*) as missed FROM cdr WHERE 
dialcause<>'ANSWER' AND (dst='2876543' OR 0) AND DATE(calldate)=CURDATE()

2 - Delayed insert - upgrading lock	

3 - Query	- Locked - SELECT count(*) as missed FROM cdr WHERE 
dialcause<>'ANSWER' AND (dst='' OR 0) AND DATE(calldate)=CURDATE()

4 - Query - Locked - SELECT count(*) as missed FROM cdr WHERE 
dialcause<>'ANSWER' AND (dst='' OR 0) AND DATE(calldate)=CURDATE()

5 - Delayed insert - Waiting for INSERT	

6 - Query - Sorting result - SELECT DATE_FORMAT(calldate, '%d-%m-%Y 
%H:%i:%s') AS data, accountcode AS utente, dst AS numero, billsec AS 
secondi, usercost FROM cdr WHERE cdIdCompany = '' AND calldate BETWEEN 
'2007-05-15 00:00:00' AND '2007-05-15 23:59:59' AND carriercost > 0 AND 
lastapp != 'SMS' ORDER BY calldate ASC

7 - Query - NULL - SHOW FULL PROCESSLIST
[...]

Thread #6 is running the slow report query but other SELECTs are Locked 
(1, 3, 4)

I see thread #2 is 'Upgrading lock', is that locking the other SELECTs ?
Why is that happening ? Shouldn't MyISAM support INSERTs without READ 
LOCKING the tables ?

I'm using MySQL 5.0.27 on a gentoo

Tnx in advance for help

Regards

Ing. Edoardo Serra
WeBRainstorm S.r.l.


Thread
Issue with locking and INSERT DELAYEDEdoardo Serra15 May
  • Re: Issue with locking and INSERT DELAYEDDan Buettner15 May
    • Re: Issue with locking and INSERT DELAYEDDan Nelson15 May