MySQL Forums
Forum List  »  Performance

Re: Sporadic Query "Freeze" with InnoDB, highly concurrent workload
Posted by: Rick James
Date: February 25, 2014 08:42PM

My feeling on "MySQL as a queue" -- "Don't queue it, just do it!"

The effort and cost of enqueuing and dequeuing tasks is often higher than the actual work being done. Hence MySQL slows down the process and consumes resources.

On the other hand, if your queue is "bursty" (suddenly you get lots of items to do, and it takes a while to get them done), a queuing mechanism may be necessary.

Assuming you keep the queue...

How many "workers" are there? Do they hang onto a transaction for the duration of their "work"? That is, are they locking the queue _and_ the other tables?

How many "enqueuers" are there? It might be advantageous to throttle how many threads can attempt to enqueue items.

How big does the queue get to? If the table gets especially big, then the following points could be important:

> `...UUID` char(48) NOT NULL,

What do those strings look like? Standard UUIDs are only 39 chars, and can be packed into BINARY(16). This looks strange: "b4572d-8448-4729-bf7d-9d1da100dfa1" -- it is only 34 chars, wasting the other 12 bytes of CHAR(48).

Can the DOUBLEs be turned into FLOATs? 8 bytes -> 4 bytes.

What is the value of innodb_buffer_pool_size?

> PRIMARY KEY (`runConfigID`),
> UNIQUE KEY `runConfigUUID` (`runConfigUUID`),

A PRIMARY KEY is a UNIQUE KEY, so the second index is totally redundant, and costs some effort on both INSERT and DELETE.

Four secondary keys start with `status`. Is there an UPDATE that changes STATUS? If so, that would be like changing 5 tables (the table, plus the 4 secondary indexes). Rethink whether you need all of them. Run EXPLAIN SELECT... to see if all of them are actually used.

> KEY `status2` (`status`,`priority`),

Both of those fields have low cardinality, so it is likely that this index will never be used. In particular,

> WHERE status="NEW" AND priority="HIGH" ORDER BY runConfigID LIMIT 5

would benefit from
INDEX(status, priority, runConfigID)
instead of what you have. (There is some dispute as to whether that is implicitly what the key is.)

I think your big UNION can be replaced by
WHERE status="NEW" ORDER BY priority DESC, runConfigID
or perhaps
WHERE status="NEW" ORDER BY 0+priority DESC, runConfigID

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Sporadic Query "Freeze" with InnoDB, highly concurrent workload
864
February 25, 2014 08:42PM


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.