Statistics are not recalculated for every SHOW TABLE STATUS and similar commands. Since MySQL 5.6.6, InnoDB statistics can be (and this is a default setting) persistent. It is possible to change this setting by changing innodb_stats_sample_pages to get a bit more stable and ‘closer to reality’ statistics – but it comes at the price of more I/O. Changes in InnoDB statistics, though, impact query execution plans. On the other hand, it’s rather obvious that such a small sample may introduce large variations in the final result.
#Mysql optimizer turn off update
In one way, this makes sense – the more index lookups you make, the longer it takes to update index statistics and more I/O is needed for this – not something you’d like to see. This is 128k of data to calculate stats for, let’s say, 100G index. To calculate statistics, InnoDB performs a lookup into 8 (yes, eight!) index pages. In addition to this, table statistics were updated when either 1/16th or 2 billion rows were modified in a table. But it also happened when SHOW TABLE STATUS, SHOW TABLES or SHOW INDEX were executed. It happened when someone explicitly executed ANALYZE TABLE or at the first time table was opened.
Historically index statistics were recalculated from time to time. Let’s first see how InnoDB statistics work and how we can change it. The optimizer makes decisions about the best index for a query, and this is based on index statistics provided to it by the InnoDB engine. One of the ways we can influence the way a query is going to be executed is by using index hints.
#Mysql optimizer turn off series
Our previous posts in the DBA series include Using EXPLAIN to improve SQL Queries, Database Indexing, Deep Dive pt-query-digest, Analyzing SQL Workload with pt-query-digest, Query Tuning Process, Configuration Tuning, Live Migration using MySQL Replication, Database Upgrades, Replication Topology Changes, Schema Changes, High Availability, Backup & Restore, Monitoring & Trending. This is the fourteenth installment in the ‘Become a MySQL DBA’ blog series. (Note that in MySQL 5.7, a lot of work has been done in order to improve this – users can modify the cost of different types of operations.) However, it is possible to impact how a query will be executed, and this is the topic of today’s blog. For instance, disk access may have different costs depending on the type of storage used – SSD drives will have quicker access times than spindles, and can perform more operations in a given time.
The optimizer makes decisions based on statistics and some fixed costs per operation, but it does not understand the differences in hardware. In some cases, it might not have enough information about the data and plan queries in a non-optimal way. It usually does a great job, but not all the time. MySQL uses a cost-based optimizer to determine the best way to execute a query.
0 kommentar(er)
