Hi Jim,
On 8/20/2014 11:04 AM, Jim wrote:
Without going into specific details on queries...
Using mysql 5.1 as provided with CentOS6, I've noticed some queries
providing what I can best explain as inconsistent optimization. The
database can be quieted to just controlled queries and at times the same
query will return very quickly when at other times may take minutes.
I don't see the same behavior with mysql5.0 under CentOS5. The same
queries on the same data returns quickly consistently.
When the queries run slowly they show in a process list as either in a
"copy to temp table" or "sending data" state. At first I thought query
restructuring to avoid the copy to temp table was a path to a solution,
but now I don't think so since the same query changed so that it no
longer needs a temp table will sit in the "sending data" state for a
long time.
The queries do eventually come back with correct results, but it takes
minutes rather than milliseconds (sometimes slow; sometimes fast).
Have others seen this behavior? Any explanations?
Any reading to point to for further understanding?
Fluctuations in query times can be the results of configuration mistakes
(like creating a 1GB query cache or a tiny InnoDB Buffer Pool), or data
changes (did you add or remove or change a bunch of rows), or query
patterns (did you add or remove terms from your WHERE clauses, did you
change which columns were in your SELECT clause, ... ).
To know why a query is doing what it is doing, you need to ask the
Optimizer. The Optimizer is that part of the server that works out the
most efficient way to go get the data you are asking for and how to
process that data once it is pulled from disk or cache.
This is the purpose of the EXPLAIN operator. Just put that word before
SELECT and see what you get. An explanation of how to interpret an
EXPLAIN report is here in the manual (you are reading the manual, right?)
http://dev.mysql.com/doc/refman/5.1/en/explain.html
http://dev.mysql.com/doc/refman/5.1/en/execution-plan-information.html
That will give you a starting place. After that, you can refer to the
other sections of the "Optimization" chapter to see what you can or
should be changing to improve your performance.
http://dev.mysql.com/doc/refman/5.1/en/optimization.html
You should also need to learn a little bit about the topic of "index
statistics" as those are what the Optimizer uses to develop its
execution plans.
http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
http://dev.mysql.com/doc/refman/5.1/en/show-index.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html (search
for "ANALYZE TABLE determines index cardinality...")
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages
http://dev.mysql.com/doc/refman/5.1/en/optimizer-issues.html
Feel free to ask the list any questions that may arise in your research.
Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
