MySQL queries waiting for other queries to finish
Question: We have 30-40 different projects in Python and PHP that update, insert and
select more than 1 million rows of data in MySQL DB every day.

Currently we use InnoDB Engine for our tables.

The problem: we have peaks in MySQL when almost all projects are working and
lots of queries are processing in DB. There are main queries that are very
important to finish ASAP (high priority) and queries that can wait for finish
of main queries (less priority). But as they go to MySQL concurrent it causes
main queries to wait finishing of less priority queries.

Questions:

  1. Is there any possibility to release all lock in tables before executing main queries (so they can finish ASAP)? or create locks if it help?

  2. Can we pause the less priority queries execution when start execution main queries automatically?

  3. Can use HIGH_PRIORITY and LOW_PRIORITY in queries help?

  4. Are there some configurations in MySQL that can help?

  5. Can changing tables to MyISAM or other DB engine help?

Let me know your thoughts and ideas.


Answer:   1. No. You might try upgrading to MySQL 5.7 as it allows parallel replication within tables if the transactions do not interfere with each other.
  2. See <http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html> about how LOW PRIORITY has no effect.
  3. See #2.
  4. It would probably be better to look how you are doing your locking in your application - -are you locking rows up, making changes, unlock quickly or does the code do this in a leisurely fashion?
  5. MyISAM locks at the table level not the row level and MyISAM does not support transactions (Which is probably why you are locking records).

