Strange Performance Problem
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.
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.