… about the MRR optimization available in MySQL 5.6 here: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html and as… the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer… in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5… of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set (0… having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…referring to the amazing MySQL manual, especially the option and variable reference table…..commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual partitionblogpercona.commanual …
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
…more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to…and averaged results. MySQL 5.1.21-beta was used for tests. mysql> select count(*) from dt2 where slack like “a%”; +———-+ | count(*) | +———-+ | 4705992 | +———-+ …
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking permissions | 0.000005 | … snip … | init | 0.000021 | | optimizing…), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query… queries, which you know do not need to be cached. Counting query cache efficiency There are few ways you can look…
Post: Time for Zero Administration effort at MySQL ?
January 8, 2012 By 4 Comments
Preparing Optimizing MySQL Configuration talk for Percona Live in Washington,DC I noticed how many Variables did MySQL get over years – it is pushing 400 these days even if we do not count variables/options which… natural step I would like to see taken is having MySQL to chose sensible values for them automatically, based on system…
Post: Finding an optimal balance of I/O, CPU, and RAM for MySQL
May 16, 2011 By 4 Comments
… MySQL scales as you add more memory to the server. Vadim recently benchmarked the effects of increasing memory and CPU core count… storage — specifically, the Virident tachIOn drive — has more bandwidth than MySQL can fully utilize at present. Therefore, to decrease the price… done. You can read the full details in our Scaling MySQL With Virident Flash Drives and Multiple Instances of Percona Server…
