List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:August 20 2014 7:37pm
Subject:Re: inconsistent optimization
View as plain text  
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
Thread
inconsistent optimizationJim20 Aug 2014
  • Re: inconsistent optimizationEduardo Fontinelle - Gerencianet Pagamentos20 Aug 2014
  • Re: inconsistent optimizationshawn l.green20 Aug 2014
RE: inconsistent optimizationMartin Gainty20 Aug 2014
  • Re: inconsistent optimizationJim20 Aug 2014