MySQL Forums
Forum List  »  InnoDB

Strange Performance Problem
Posted by: Chris Chris
Date: July 04, 2008 04:21AM

Hello there,

I have a strange performance problem when using the InnoDB engine.
I have a DB with a x4 replicated set of sample data. When I run a query on this data it takes a very long time (~20s).
If I multiply up the data to x100 the query executes almost instantaneously! This is very strange!

I have now analysed this further with the MySQL profiler, and these are the results:

With just x4 sample data:

+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000007 |
| checking query cache for query | 0.000429 |
| checking permissions | 0.000016 |
| Opening tables | 0.000082 |
| System lock | 0.000011 |
| Table lock | 0.000272 |
| init | 0.000133 |
| optimizing | 0.000106 |
| statistics | 20.920164 | <------------------------ XXXXXXXXXXXXX
| preparing | 0.000105 |
| executing | 0.000004 |
| Sending data | 0.001588 |
| end | 0.000006 |
| query end | 0.000003 |
| storing result in query cache | 0.000005 |
| freeing items | 0.000018 |
| closing tables | 0.000007 |
| logging slow query | 0.000002 |
+--------------------------------+-----------+

And with x100 sample data:

+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| (initialization) | 0.000007 |
| checking query cache for query | 0.000417 |
| checking permissions | 0.000016 |
| Opening tables | 0.000072 |
| System lock | 0.000016 |
| Table lock | 0.000282 |
| init | 0.00016 |
| optimizing | 0.000106 |
| statistics | 0.006538 |
| preparing | 0.000125 |
| executing | 0.000006 |
| Sending data | 0.063439 |
| end | 0.000014 |
| query end | 0.000006 |
| storing result in query cache | 0.000006 |
| freeing items | 0.000037 |
| closing tables | 0.000013 |
| logging slow query | 0.000004 |
+--------------------------------+----------+

So, with a small amount of data, the "statistics" portion of the profile is extremely large, whereas with a large amount of data it is very small.

Strange, right?

Does anyone know what is going on here?

Incidentally, the query that I'm running is an inner join over about 10 tables.

Thanks a lot in advance,
Chris.



Edited 1 time(s). Last edit at 07/04/2008 04:22AM by Chris Chris.

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange Performance Problem
2681
July 04, 2008 04:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.