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.
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.
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?
Can we pause the less priority queries execution when start execution main queries automatically?
Can use HIGH_PRIORITY and LOW_PRIORITY in queries help?
Are there some configurations in MySQL that can help?
Can changing tables to MyISAM or other DB engine help?
Let me know your thoughts and ideas. If you need more info, I can provide.
Thanks in advance