In the last episode (Mar 13), Pelle Eliasson said:
> Let's say I have two connections to a MySQL db test. First I send a
> really heavy select statement on one connecction. Let's say it takes
> an hour to execute. What will happen if I after that sends an easy
> select on the other connection. Will I get a short rersponse time on
> that one or will it take long time to execute. Is it possibly to send
> a query that eats all the CPU and "Blocks" the database so other
> quries don't get executed? Or will the fact that there are several
> read threads garanty that all questions will be executed within a
> certain time. And if so How long is this time. Is this time dependent
> of the OS. I will run this on FreeBSD3.2 or 3.3 or Linux.
As long as there are no updates or other locks on the table, and you
aren't swapping, your fast query should finish quickly. If your system
is not configured right and mysql starts swapping while processing the
slow query, it will definitely slow down. We run a job-tracking
database here that is mainly fast queries, but weekly/monthly summary
reports can lock tables for 5-10 minutes.
For queries lasting more than 5 minutes or so, locks are your biggest
problem. Updates to the table have priority over selects, so if you
run big_query, then try and update a table that big_query is using,
then all other selects from that table will have to wait until
big_query and the update query finish.
You can work around this locking problem by using the INSERT/UPDATE
LOW_PRIORITY and SELECT HIGH_PRIORITY statements.