slow DELETE query
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 | | | -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 | | +---++--+--+-+---+-+--++-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
Hi Matthew, have you any foreign keys, if yes can you show : - show create table cdr - show create other_table referenced in foreign keys and foreach the indexes Mathias Selon Matthew Simpson [EMAIL PROTECTED]: 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 | | | -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 | | +---++--+--+-+---+-+--++-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow DELETE query
DELETE LOW_PRIORITY But since your using myISAM the table will have to lock. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Matthew Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 11:22 AM To: mysql@lists.mysql.com Subject: slow DELETE query 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 | | | -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 | | +---++--+--+-+ ---+-+--++-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
Matthew Simpson wrote: 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? DELETE from cdr WHERE accountcode='' LIMIT 10 wait, and run again until there is nothing more to delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
Is MyISAM a bad choice for this kind of table? - Original Message - From: Dathan Pattishall [EMAIL PROTECTED] To: Matthew Simpson [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, May 20, 2005 1:48 PM Subject: RE: slow DELETE query DELETE LOW_PRIORITY But since your using myISAM the table will have to lock. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Matthew Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 11:22 AM To: mysql@lists.mysql.com Subject: slow DELETE query 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 | | | -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 | | +---++--+--+-+ ---+-+--++-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
Also try running the delete more frequently. It won't cause much of a lock if there is an index on accountcode and mysql doesn't find any rows to delete. gerald_clark wrote: Matthew Simpson wrote: 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? DELETE from cdr WHERE accountcode='' LIMIT 10 wait, and run again until there is nothing more to delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
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 | | | -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 1 bad records from a table you are creating 1 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
