…, yet the impact of FLUSH TABLES WITH READ LOCK can be quite large because of the time it … is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could …MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means… made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched… one number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is ‘Handler_mrr_init…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… following query executed on the InnoDB table: SELECT non_key_column FROM …tuples. If the buffer size is large enough only a single range lookup …MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… is a large discrepancy in the sizes, is this an indicator that it may be a good opportunity to run OPTIMIZE and thus reclaim some hard disk space? Taking real-world data, I have for example a table that looks… disk, that table‘s .ibd file looks like this: # cd /var/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… selectivity might show benefit from using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory… of few large tables, which is completely disk bound can be very slow. One of the reasons elevating this problem in MySQL is…% or rows or less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested loops is…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
…external storage. 500MB/sec would be exceptional for traditional MySQL as MySQL optimizer with its nested loops joins does not favor sequential …because operationally working with large table and data sizes in MySQL can be painful, lacking online operations to alter table, defragmentation etc. Yes …
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table‘s… or an application constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many a large tables, this can affect your server especially if…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… constants) and so ORDER BY can be optimized even if it is done by second table: mysql> explain select test.i from test… JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain… should write another article about ORDER BY without limit and large tables soon.
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… trick MySQL into removing smaller files with DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table; Unfortunately as it turned out each of the administrative commands like ALTER TABLE or OPTIMIZE TABLE one…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to … fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL…
