On 3/8/07, Gary Sewell <[EMAIL PROTECTED]> wrote:
I'm having a problem with a single repair query seemingly taking over the
whole server. I'll try and explain….
Large table or 1.2m rows, running a repair on this causes the query queue to
grow and grow and finally max out.
The problem isn't table locks because no-one else is using the test database
I am repairing.
Assuming you're running both the test database and the live database
on the same physical server, it sounds like the problem could be that
the table repair is starving the disks I/O-wise, causing other queries
to run very slowly.
If at all possible, try and move the test database to its own physical
hard drive (or ideally, to its own hard drive on a separate disk
controller); that way, any disk-intensive table repairs won't affect
disk performance of the live database.
Or move the test database to its own server; running test and live
systems on the same physical server is asking for trouble!
Even the most simple
select queries on the live database are taking 500 secs!! I want the repair
to run but not to the detriment of other queries.
This problem will be a lot worse if the queries aren't well optimised;
have you checked them with EXPLAIN? 500 seconds does indeed sound a
bit excessive, even for a system under heavy disk I/O load.
Once the repair is over it can take about 3 minutes before the queue starts
to evaporate also, shouldn't it kick back into gear as soon as the repair is
over?
In my experience, It can take a while for the kernel to re-balance
things. Caching of MyISAM data pages is managed by the operating
system's page cache, and not by MySQL; if a large table has to be
re-built, this will most likely have an adverse effect on MyISAM
performance until things have balanced themselves out.
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
