• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

MySQL Delete takes too long

Hi,

I have a MySQL database that runs on a dedicated server running windows on a 6-core CPU with 8GB ram.

I  have a few scheduled jobs running which run complicated views and output the results to a table (which is queried by the website to avoid duplication of complicated calculations) - we'll call this tblMainTable

The scheduled jobs first delete any records that have been previously inserted of the same type and then inserts new records.  

But now we have heavy traffic on the tblMainTable (i.e more records and lots of inserts and updates constantly) the delete is taking ages - sometimes over 5 minutes!

There is approx 50,000 records in tblMainTable which are being updated constantly

The following statement is being used for the delete
DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 1;

Open in new window


Approx 29,000 records in tblMainTable meet the criteria above

I am now a bit lost, I've tried all I can think of and it seems the DELETE just takes ages and is causing a real problem.  I assume it is because the table is being changed so frequently but there must be a way to make this quicker.

Any help greatly appreciated!

Thanks
0
cp30
Asked:
cp30
  • 14
  • 12
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Do you have indices on the two columns in the WHERE clause? If you run EXPLAIN or DESCRIBE on the DELETE, what do you get?

Additionally, if you are practically deleting all the rows, you may consider doing a full truncation (http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html), then fill the table with the smaller, new subset.
0
 
cp30Author Commented:
Hey mwvisa1! :-)

Yes the 2 columns in the where clause are indexed, along with a few other columns which are utilized on the select from that table that the website uses.

II didn't think EXPLAIN or DESCRIBE worked on MySql?!?

Because the table is in constant use by the website and there are a few of these tasks that each delete and insert only their own type of record, I don't think the full truncation is an option.

i.e.

TASK 1 runs every minute and
DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 1;
INSERT NEW DATA INTO tblMainTable;

TASK 2 runs every minute and
DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 3;
INSERT NEW DATA INTO tblMainTable;

TASK 3 runs every 2 minutes and
DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 4;
INSERT NEW DATA INTO tblMainTable;



0
 
Kevin CrossChief Technology OfficerCommented:
Okay, is the index a combo index or just individual ones. Yes, EXPLAIN works in MySQL, so place EXPLAIN in front of the query and post results. If the deletes take 5 minutes, but you are deleting every minute then you are probably getting contentions locking tables or just the load is not allowing each delete to complete in enough time before starting the next delete batch. Are these InnoDb tables that have row-level locking? Otherwise, MySql locks entire table if I am not mistaken.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
cp30Author Commented:
CREATE TABLE `tblMainTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lastUpdate` datetime DEFAULT NULL,
  `lastUpdateEx` datetime DEFAULT NULL,
  `outcome` varchar(100) DEFAULT NULL,
  `rating` decimal(5,3) DEFAULT NULL,
  `Av` float DEFAULT NULL,
  `bb` float DEFAULT NULL,
  `bL` float DEFAULT NULL,
  `bkId` int(11) DEFAULT NULL,
  `exId` int(11) DEFAULT NULL,
  `url_b` longtext,
  `url_e` longtext,
  `eT` datetime DEFAULT NULL,
  `eI` varchar(100) DEFAULT NULL,
  `eD` varchar(100) DEFAULT NULL,
  `subTypeId` int(11) DEFAULT NULL,
  `typeId` int(11) DEFAULT NULL,
  `aggregatedKey` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `aggKey` (`aggregatedKey`(512)),
  KEY `rating` (`rating`),
  KEY `bkId` (`bkId`),
  KEY `exId` (`exId`),
  KEY `subTypeId` (`subTypeId`)
  KEY `typeId` (`typeId`)
) ENGINE=InnoDB AUTO_INCREMENT=1708981 DEFAULT CHARSET=latin1

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
You probably an index on the combination typed and subtypeid.
0
 
cp30Author Commented:
You mean add a new index on (typed,subtypeid)? I did try that with no noticeable difference.

How can I explain a delete though,

EXPLAIN DELETE FROM tblMainTable WHERE subTypeId = 2 AND typeId = 1;

Doesn't work? sorry if I'm being dense. ;-)

They are innodb I will try and find out if they have row level locking or not as I'm not sure

Cheers
0
 
cp30Author Commented:
Not sure how I check, but I guess it's row level locking as I'm using InnoDB
0
 
cp30Author Commented:
If you wanted me to run select with same criteria for explain then here it is.....

EXPLAIN SELECT * FROM tblMainTable WHERE subTypeId = 2 AND typeId = 1;

Open in new window


id      select_type      table      type      possible_keys      key      key_len      ref      rows      Extra
'1', 'SIMPLE', 'tblMainTable', 'ref', 'subTypeId,typeId', 'subTypeId', '5', 'const', '28677', 'Using where'

0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, try this:

EXPLAIN SELECT * FROM tblMainTable WHERE subtypeid = 2 AND typed = 1;

Note: the order of the index matters, so subtypeid should be first or swap the conditions in WHERE.
0
 
Kevin CrossChief Technology OfficerCommented:
Keep cross posting, sorry. I am on a mobile device and so not seeing this your posts.
0
 
cp30Author Commented:
No problem, I appreciate your help, I'm completely stuck and just have to get this working! :-(

I have checked and subTypeId index is first in table and first in where clause

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
No. First in the index. Notice that it is using the subtypeid alone as an index in the explain plan. Do you ever just query on subtypeid or typed alone? To reduce confusion by the query optimizer, if you do not use those I would delete them and have just the one index containing BOTH with subtypeid listed first.
0
 
Kevin CrossChief Technology OfficerCommented:
To be clearer:
CREATE INDEX ix_tblMainTable_type ON tblMainTable(subTypeId, typeId);
0
 
cp30Author Commented:
Ok, I've actually realied that I don't need both keys as one is unique following a change a few weeks back.

so I have now....

DELETE FROM tblMainTable WHERE typeId = 1

Still takes forever.

Explain now shows the one possible key which is used as key

0
 
Kevin CrossChief Technology OfficerCommented:
What are the memory settings currently for InnoDb, i.e., have you done any tweaking of MySQL configuration, yet?
0
 
cp30Author Commented:
Ok

I may be getting somewhere here.

The stored procedure that is called by the scheduled tasks is wrapped in a transaction (I presume so that the old records do not disappear before the new ones are inserted).  I have removed this wrapper (shown in code window below for info) and now I do not seem to be getting any long running statements.

BEFORE:
SET SESSION TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED; 
START TRANSACTION;
  
  DELETE FROM tblMainTable WHERE typeId = 1;

    INSERT INTO odds.tblMainTable
    SELECT * FROM odds.vw_complicated
    WHERE rating > 0.9;

            
  COMMIT;	

Open in new window


NOW:
  
  DELETE FROM tblMainTable WHERE typeId = 1;

    INSERT INTO odds.tblMainTable
    SELECT * FROM odds.vw_complicated
    WHERE rating > 0.9;

Open in new window



So this is good, but now I need to know how I can delete the records and insert the new ones but without chance that my users will select from the table inbetween the rows being deleted and new ones inserted, maybe they should be in a transaction but that code was wrong before as it seemed to be causing the issue.  I'm not great with transactions and isolation levels, any ideas?

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
InnoDb_buffer_pool_size is a good one to look at. In addition, I usually recommend folks read gr8gonzo's article once we start talking performance tuning so here you go: http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0
 
Kevin CrossChief Technology OfficerCommented:
This may go back tomthe frequency of the transactions as that will lock the entire table if I am reading your last post correctly. Therefore, you have multiple transactions trying to lock the entire table whereas the no transaction code will only lock the rows being deleted.
0
 
cp30Author Commented:
Yes, I think you are probably right

Can you think of a way to remove rows of type 1 and insert new rows of type 1 without having period in between with no rows?
0
 
Kevin CrossChief Technology OfficerCommented:
You were doing it with transaction. Otherwise, you will have to live with the collision. I actually have that in one report that users wanted tables refreshed at the top of the hour. If the users forget and rn report right when thhe process starts they get zero results. Then it reminds them to refresh and magically the new data is there. :) it just depends on how the application responds to no rows returned and if that causes your customers alarm. If it does, you may need to consider a view on the original data versus refereshing a table every minute.
0
 
Kevin CrossChief Technology OfficerCommented:
I.e., query your view directly...work at performance tuning that versus the workaround which adds in this race conflict.
0
 
cp30Author Commented:
Yes,  querying the view directly is not really an option as the amount of data it calculates, table joins, unions etc will always take a good 30 seconds or more and users expect results on query within 1-2 seconds.

Can't return 0 rows either as users monitor and refresh the view regularly so it would be noticeable and confuse them.

Need to come up with a way around it, and by tomorrow morning! :-( Best get my thinking cap on!  
0
 
Kevin CrossChief Technology OfficerCommented:
But if your need for NO zero rows results in a transaction taking 5 minutes. Is that not the better option? It is relative to what we are talking about here. Good luck. :) Morning coffee always sparks good thought. *smile*
0
 
cp30Author Commented:
Hi,

Yes, annoying thing is before today it wasn't taking 5 minutes, and has been running for few months. very strange.

Here's what I've done for now.  

Added new column to tblMainTable (newRecord)
INSERT new records and set newRecord flag to 1
DELETE FROM tblMainTable where typeId=1 and newRecord = 0
UPDATE tblMainTable SET newRecord = 0 WHERE typeId=1

This way I should actually get duplicate records if anything, which is not great but is favoriable over records showing 0, I could even handle in the front end and remove older duplicates if it's a problem.

Maybe not ideal but think it is the best I'm going to get at the minute

Thanks you very much for your very responsive help, you helped me get the problem with your thought provoking questioning ;-)
0
 
Kevin CrossChief Technology OfficerCommented:
That will work and if the speed does not slow down too much because of the UNION, you can do something like this:

SELECT {columns you want}
FROM tblMainTable
WHERE typeId = 1
AND newRecord = 0
UNION
SELECT {columns you want}
FROM tblMainTable
WHERE typeId = 1
AND newRecord = 1
AND NOT EXISTS (
   SELECT 1
   FROM FROM tblMainTable lkup
   WHERE lkup.typeId = tblMainTable.typeid
   AND newRecord = 0
)
;

That will only bring back the data for the new row if OLD row does not exist.

Nice workaround by the way.

:)
0
 
cp30Author Commented:
Great help from a legend on EE, thanks for your speedy replies in my time of need. ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 14
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now