Thanks Mike! Yes, the query is certainly using tmp tables for the longest of times. I will try and implement the solution you provided and see how it goes. If I do, I will update you with the results.
Regards, Bhavin. ----- Original Message ----- From: "mos" <[EMAIL PROTECTED]> To: "Bhavin Vyas" <[EMAIL PROTECTED]> Sent: Friday, July 26, 2002 2:21 PM Subject: Re: Slow queries > At 05:53 PM 7/24/2002, you wrote: > >Hello, > >I am running mysql in our production environment, which have a few reporting > >tables with millions of rows. Certain queries cascade 2 such tables that > >have millions of rows and there by, there are extremely slow, sometimes > >taking over 10 minutes. > >However, we are ok with that since the size of the data is extremely large. > >However, the problem that we are facing is that when these queries are > >running, other queries, *unrelated to the 2 large tables* are also put on > >hold, so they can't be executed either and basically mysql becomes > >unresponsive until I kill the SLOW query and then everything is back to > >normal. > >Anybody knows why this happens and if there is a solution for this (like > >maybe, reprioritizing the slow query to a very low priority or something). I > >would think that mysql would independently try to execute the other queries > >but that does not seem to be happening. > > > >Thanks, > >Bhavin. > > Bhavin, > If MySQL has to create temporary tables to do the join, then of > course things are going to slow down quite a bit. I've been thinking about > a solution and this may help (I haven't tried it). Why not create a RAM > disk and have your MySQL TmpDir point to that? RAM is pretty cheap and a > 30-100MB RAM drive would speed up table joins quite a bit because it can be > written to 10x faster than a hard drive. There are a lot of 3rd party > software RAM drives out there for Windows and Linux. You would have to > specify a RAM drive large enough to hold all your temporary files. There > are also RAM drive PCI cards and external RAM drive devices but these cost > a lot more than a software RAM drive. These devices also let you have a RAM > drive of 4g or more without taking memory away from the OS. Their data is > persistent between reboots via an external battery. > > MONTY: It would be nice if TmpDir could accept more than 1 > directory, like a delimited string of directories as in > "o:\RAM;m:\MySQLTmp1;o:\MySQLTmp2" so when one runs out of disk space, it > looks for another directory in the path. > > Mike > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
