"Matthew Simpson" <[EMAIL PROTECTED]> wrote on 05/20/2005 02:21:54 PM:
> I use Mysql to store call detail records from telephone calls. I have > around 20 voice switches that send the call detail records in real time > using INSERT statements. > > I am having a problem where I need to delete "junk" call records that get > generated [old call records, call records with no accountcode, etc.], but > when I try to run the DELETE query, SQL grinds to a halt which causes my > voice switches to halt because they can't run the INSERT queries. Is this > because of table locking? An example delete query: > > DELETE from cdr WHERE accountcode='' > > Is there a way to make the DELETE query run at a lower priority and allow > the INSERTs? > > Here is the table description: > > mysql> describe cdr; > +-------------+--------------+------+-----+---------------------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------------+--------------+------+-----+---------------------+-------+ > | uniqueid | varchar(32) | | | | | > | userfield | varchar(255) | | | | | > | accountcode | varchar(20) | | MUL | | | > | src | varchar(80) | | MUL | | | > | dst | varchar(80) | | | | | > | dcontext | varchar(80) | | | | | > | clid | varchar(80) | | | | | > | channel | varchar(80) | | | | | > | dstchannel | varchar(80) | | | | | > | lastapp | varchar(80) | | | | | > | lastdata | varchar(80) | | | | | > | calldate | datetime | | | 0000-00-00 00:00:00 | | > | duration | int(11) | | | 0 | | > | billsec | int(11) | | | 0 | | > | disposition | varchar(45) | | | | | > | amaflags | int(11) | | | 0 | | > +-------------+--------------+------+-----+---------------------+-------+ > type is MyISAM > > indexes: > mysql> show index from cdr; > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | > Cardinality | Sub_part | Packed | Comment | > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+---------+ > | cdr | 1 | cdr_idx | 1 | src | A | > NULL | NULL | NULL | | > | cdr | 1 | cdr_idx | 2 | dst | A | > NULL | NULL | NULL | | > | cdr | 1 | cdr_idx | 3 | calldate | A | > NULL | NULL | NULL | | > | cdr | 1 | cdr_idx | 4 | accountcode | A | > NULL | NULL | NULL | | > | cdr | 1 | i1 | 1 | accountcode | A | > NULL | NULL | NULL | | > | cdr | 1 | i1 | 2 | calldate | A | > NULL | NULL | NULL | | > +-------+------------+----------+--------------+------------- > +-----------+-------------+----------+--------+---------+ > > This is an important question: How often do you pare out the older records? Once a day, once a week? once an hour? There is an optimization in the MyISAM table handler that allows for concurrent INSERT statements BUT ONLY if there are no deletion gaps in the data. By chopping out 10000 "bad" records from a table you are creating 10000 deletion gaps. That means that all of your new INSERT statements have to start locking until the gaps fill in again (assuming a fixed-width table). What may work better for you is to keep tables in MyISAM but create one for every "cleanup" period you use. When you nedd to query across multiple "cleanup" periods use a MERGE table. For instance, if you purge "bad" records daily, then you need to create one table for each day and if you need to be able to query a week's worth of data at any one time, define a MERGE table that covers those 7 days. This way you never need to delete rows from your "current" table and your INSERT statements can't get blocked. When it comes time to shift your "window" to the next day, change the MERGE definition to include the new day's table but not the one from 8 days ago. Does that plan make sense as a solution for you? It's a bit more overhead (more tables and some scripting to keep your MERGE window up to date) but I think it will solve your locking problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
