List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:May 15 2007 4:19pm
Subject:Re: Issue with locking and INSERT DELAYED
View as plain text  
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 <edoardo.serra@stripped> 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/mysql?unsub=1
>
>

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