MySQL Forums
Forum List  »  InnoDB

Re: Deadlock leading to huge number of active connections
Posted by: Rick James
Date: October 18, 2014 12:13PM

Let's look at the possible solutions:

* Deal with deadlocks when they happen. That is retry. This must be done regardless.

* Avoid deadlocks. This may not be possible because of of how different the DELETEs are.

* Minimize the frequency of deadlocks. This may be possible by making the schema more efficient. I will focus on this 'solution'...

First, some questions:
-- How big is the table? (SHOW TABLE STATUS)
-- What is the value of innodb_buffer_pool_size?
-- How much RAM do you have?
-- What is Max_used_connections? (SHOW GLOBAL STATUS)
-- Are you CPU-bound? I/O bound?
-- Are the DELETEs part of a transaction? If so we must see the whole transaction.

If the table is big (etc), then I will push for normalizing the VARCHARs in order to shrink the table.

If you are always ignoring rows with (external_id IS NULL), then see if you can remove them from the table.

> KEY `index1` (`tag`),
> KEY `tag_and_push` (`tag`,`pushid`),

The first of these KEYs is redundant; DROP it.

You do not have a PRIMARY KEY on an InnoDB table. This is potentially 'bad'. Is some field a 'natural' PK? Or maybe some combination of columns? If you have a natural PK, let's discuss how it may (or may not) benefit the performance. If not, then how many rows do you eventually expect to ever INSERT? (More than 4 billion?)

> delete from user_notifications where deviceid=? and external_id is null

There is no good index for this query. Do these:
* Normalize deviceid so that it is not a too-long string, but rather an INT (or maybe MEDIUMINT or SMALLINT).
* INDEX(deviceidid, external_id)

The other DELETE has an acceptable INDEX. Normalizing `pushid` would make it better.

> DEFAULT CHARSET=utf8

Are your devices, tags, etc non-English? If so, keep utf8; if not, consider using ascii or latin1.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Deadlock leading to huge number of active connections
912
October 18, 2014 12:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.