Solved

MySQL Delete takes too long

Posted on 2011-09-30
26
377 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

11 Experts available now in Live!

Get 1:1 Help Now