Hi Edoardo -
I think you've been misinformed; MyISAM tables do not support simultaneous
read and write operations. MyISAM is a multiple reader/single writer, table
locking design. You may want to switch to InnoDB tables for that
functionality.
http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html
Dan
On 5/15/07, Edoardo Serra <[EMAIL PROTECTED]> wrote:
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
