cannot update record, get stuck

Question

i have an issue with my current database with mysql.

i have over 100 connection waiting on a select record. when i execute:

show processlist;

the select query is a big query and the others are smaler queries and inserts, updates.

i have one database with 100 tables and the select is using 5 joins.

is there a way to temporary stop the process and let the other processes run, once all the processes are completed, then the select can continue running?

Tetralogy

EXPLAIN your query, maybe you have index problems – Vaporescence

How can for example an INSERT wait on a SELECT? I don't want to believe any well configured modern SQL server can exhibit this bug... – Hook

@PéterVarga: in MyISAM, reads can block writes (and vice versa). – Byelostok

Wow :( though I thought InnoDB was the default these days.. – Hook

@BookOfZeus can you please explain? – Tetralogy

@mariobros read this: dev.mysql.com/doc/refman/5.0/en/explain.html – Tetralogy

@BookOfZeus thanks ill check that – Tetralogy

did you check if the complex select is inside an explicit (and not necesary) transaction? – Biyearly

Answer 1

i will recommend to let the query do what it needs to do, if you stop any or kill processses or queries you might have data integrity errors which can lead to major errors.

BookOfZeus and tfb785 are right, first of all you probably have indexes errors. the explain will tell you what exactly is the problem and what to look for. for example if you have 5 joins and you get row counts like, 100,000 and 100 and 1 and 1 and 1 you will multiple 100,000 * 100 which can be super slow.

read carefully what the explain tells you and optimize your query based on it.

innodb can be a good option if you if the tables are accessed very often because its row locking insted of table locking for myisam.

I would say first try to optimize your query, maybe you wont need to alter your table engine to fix the issue. if you still have issues then you might consider moving to innodb.

Gnarly

oh f**k you are right i have: 1,314,145 on 1 table, 1,292 on a second table, 11 on a 3rd table and the rest are 1. so it means 1,314,145 * 1292 * 11 * 1 * 1? – Tetralogy

that is your problem, make sure the indexes are good and the keys are (pk, key and fk) are setup correctly. – Gnarly

thanks tyler, change the engine to innodb didn't work, but i had a index problem, now it fix thanks again – Kriegspiel

Answer 2

Is there a way to temporary stop the process and let the other processes run, once all the processes are completed, then the select can continue running?

I believe you are using MyISAM as a storage engine.

In MyISAM, the read queries block the concurrent write queries. This makes sure that the writes won't change the data in process and you don't get half of your recordset updated and another half not updated.

There is no way to stop the SELECT query the way you ask: the writes could make the recordset returned inconsisent. While it may be not the actual case in your setup and theoretically writes might not affect the reads (say, different records are read and written), MyISAM is not aware of that and it always blocks whole tables, just in case.

If you need writes not to block reads, switch to InnoDB (though there are cases when writes will block reads too).

Byelostok

Answer 3

You can't stop the running query and to give time to the other queries. But there are three ways to solve your problem:

First way: Change the isolation level http://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html These can also mean that you have to change your storage engine eg. to InnoDB.

Second way: Try to speedup your long running select with indices. This is a so called time memory trade off. You speedup your query with more memory for holding the indices trees.

Third way: Speedup your long running select with rearranging the query (joins and selects). Analyze the cost of all query parts.

Blankbook