Solved

MySQL Delete takes too long

Posted on 2011-09-30
26
386 Views
Last Modified: 2012-05-12
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
Comment
Question by:cp30
  • 14
  • 12
26 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36894979
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
 

Author Comment

by:cp30
ID: 36894988
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36894998
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:cp30
ID: 36894999
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895003
You probably an index on the combination typed and subtypeid.
0
 

Author Comment

by:cp30
ID: 36895010
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
 

Author Comment

by:cp30
ID: 36895039
Not sure how I check, but I guess it's row level locking as I'm using InnoDB
0
 

Author Comment

by:cp30
ID: 36895062
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895072
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895084
Keep cross posting, sorry. I am on a mobile device and so not seeing this your posts.
0
 

Author Comment

by:cp30
ID: 36895095
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895108
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895112
To be clearer:
CREATE INDEX ix_tblMainTable_type ON tblMainTable(subTypeId, typeId);
0
 

Author Comment

by:cp30
ID: 36895143
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895181
What are the memory settings currently for InnoDb, i.e., have you done any tweaking of MySQL configuration, yet?
0
 

Author Comment

by:cp30
ID: 36895203
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895206
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895231
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
 

Author Comment

by:cp30
ID: 36895245
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895267
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895271
I.e., query your view directly...work at performance tuning that versus the workaround which adds in this race conflict.
0
 

Author Comment

by:cp30
ID: 36895290
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895329
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
 

Author Comment

by:cp30
ID: 36895361
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36895385
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
 

Author Closing Comment

by:cp30
ID: 36989660
Great help from a legend on EE, thanks for your speedy replies in my time of need. ;-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySql hide the stored procedures 2 48
update joined tables 2 44
mysql update statement 3 21
mysql date time 14 27
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now