Ratheesh K J wrote:
Hello All,
I run a select query to see its speed. It took around 5 seconds. Now i run the
same query simultaneously twice usng two instances of the client tool. It took
10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the
queries were running till 10 secs when i saw using mytop. In the 11th sec both
the queries ended. Running it thrice simultaneously, it took 15 secs for all
the three queries to complete.
In such a case should this query be considered as slow? We are actually
checking for queries which take longer than 12 secs and regarding such queries
as slow. The moment we find such a query, a mail is sent to the DBA saying that
the query is slow. So in a day there are more than 400 such slow query
notifications flowing into the mail box.
My questions are,
Should the simultaneous queries take so long?
Should'nt both queries have finished by 6 secs rather than 10 secs?
Without query caching enabled, yes it is perfectly normal that the time
spent is rising in a linear fashion, eg 4 simulatious would be 20
seconds, 5 25 secs and so on - this just means that your query run by
itself is able to utilize all available resources such as cpu time.
Look at it this way: 1 query will use 100% of the available cpu and it
takes 5 seconds. When you run two at the same time they each have 50%
cpu to use, and thus take 10 seconds (5 seconds * 100 / 50). With 3 they
each have 33,1/3% and take 15 seconds ( 5 seconds * 100 / 33,1/3) and so
on.
Is this a right strategy to track slow queries?
Yes and no. It is always wise to test your queries to see how the do
speed wise, but if you only measure time you aren't really getting the
full picture. You have to also look at what else the system is doing -
if a query is bottlenecked only by available cpu, it will run at very
different speeds depending on how busy the system is with other things -
try to bzip2 a 500MB file while running the query and see how much time
it takes then for instance ;)
And as always remember to use explain to see how mysql optimizes your
query so you can modify it if needed, especially complicated joins can
sometimes be alot faster if you tweak them a bit.
Any suggestions would help.
Thanks,
Ratheesh K J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
