How can I do query level prioritization on MySQL when using InnoDB?
-
As of now I have a single DB setup (both reads and writes are done on this). Now I have an hourly cron which do heavy reads and writes on this and then I have users using the system. I will like to make sure that user queries are given precedence by MySQL over those issued by cron. Is their any way I can achieve this with the current setup? If not, then how can I change my setup to achieve this? PS: Currently, both cron and my App server also run on the same hardware as DB.
-
Answer:
Another possible solution: Setup Master to Master replication, direct cron to 1 master and users to other. This way, you can do both reads and writes.
Manoj Murumkar at Quora Visit the source
Other answers
Use a message queue to answer queries. The easiest thing to do is modify a data access layer to send the queries to the queue. If you want async queries, have the MQ insert the results into a table that you can access after the query has finished. The most straightforward approach is simply using three queues, each assigned various numbers of subscribes to control how many queries of each type can execute simultaneously. The high priority queue should have a high number here, such as 80% of the cores in the system. This ensures that high priority queries are scheduled as quick as possible, and with as much concurrency as possible. A regular priority queue could then be assigned to the vast percent of remaining cores, while the low priority queue may only be able to execute one or two queries at a time.
Justin Swanhart
No, MySQL has no feature to set the priority of a given thread's queries. Another common tactic, at least for read queries, is to set up a replication slave and run the cron job's queries on the slave. This doesn't work for writes of course, since you can't write to a slave and have those changes replicate upstream to its master. But you can at least move some reads off the master.
Bill Karwin
Related Q & A:
- How can I allow user to create posts in website using ASP.NET?Best solution by Programmers
- How can I optimize this dynamic SQL query in oracle with PL/SQL?Best solution by docs.oracle.com
- How Can I Connect To The Internet On My Psp Using A USB Cable?Best solution by askdavetaylor.com
- How can I make money at home using my computer without doing any type of multi-level marketing?Best solution by Yahoo! Answers
- How can I email a photo on Yahoo email without using an attachment?Best solution by Yahoo! Answers
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.