…how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…tables anyway) you can consider using –no-lock option with Percona Xtrabackup. Many other tools have similar option. This option can…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…rows in table2. So this means that we have traded many point index lookups to one or more index range lookups. This means MySQL can… let me briefly explain how hash join algorithm works. Suppose you have two tables t1 and t2…. I did not find much of a performance improvement from using Hash Join …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…a simple example. Suppose you have the following query executed on the InnoDB table…MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer… cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
…tables. I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL 5.5 which can…have joins with few tables, – it is not clear how far from optimal plans do we get by using a…MySQL 5.6 things are likely to get even better handling joins of many tables…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…how MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table…can use mysqldump –lock-all-tables instead which does not have this problem at the cost of having…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…table all queries have to be invalidated from the query cache… which can take a lot of time if you have… exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache…. Improve Manageability What is in the MySQL Query Cache ? How many hits did we get for this query…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…how many rows will be examined in total. This can be rather wrong. One thing to consider is number of rows analyzed for given table…rows Even if you have LIMIT which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…have this as a very stable value for others it might go back and forth a lot. How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql…: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: … us a good clue how many queries needed…
