inconsistent optimization
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? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: inconsistent optimization
Well, Try to start checking the IOPs vs Disc. Check your iowait and the cache size. Could you send a create table and the query for us? Atenciosamente, *Eduardo Fontinelle* *Chief Technology Officer | G**erencianet* Phone: +55 (31) 3603-0812 2014-08-20 12:04 GMT-03:00 Jim j...@lowcarbfriends.com: 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? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: inconsistent optimization
Jim/Jaime What engine are you implementing?/ Qual mecanismo de MySQL que você está implementando? Saludos desde Sud America Martín Date: Wed, 20 Aug 2014 13:54:46 -0300 Subject: Re: inconsistent optimization From: edua...@gerencianet.com.br To: j...@lowcarbfriends.com CC: mysql@lists.mysql.com Well, Try to start checking the IOPs vs Disc. Check your iowait and the cache size. Could you send a create table and the query for us? Atenciosamente, *Eduardo Fontinelle* *Chief Technology Officer | G**erencianet* Phone: +55 (31) 3603-0812 2014-08-20 12:04 GMT-03:00 Jim j...@lowcarbfriends.com: 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? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: inconsistent optimization
innodb On 8/20/2014 1:22 PM, Martin Gainty wrote: Jim/Jaime What engine are you implementing?/ Qual mecanismo de MySQL que você está implementando? Saludos desde Sud America Martín Date: Wed, 20 Aug 2014 13:54:46 -0300 Subject: Re: inconsistent optimization From: edua...@gerencianet.com.br To: j...@lowcarbfriends.com CC: mysql@lists.mysql.com Well, Try to start checking the IOPs vs Disc. Check your iowait and the cache size. Could you send a create table and the query for us? Atenciosamente, *Eduardo Fontinelle* *Chief Technology Officer | G**erencianet* Phone: +55 (31) 3603-0812 2014-08-20 12:04 GMT-03:00 Jim j...@lowcarbfriends.com: 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? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: inconsistent optimization
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
