One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the disks are 4 5.4K disks in Software RAID5. Also note that the following changes were made in 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…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… the disks are 4 5.4K disks in Software RAID5. Also note that the following changes were 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…
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…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… it may be a good opportunity to run OPTIMIZE and thus reclaim some hard disk space? Taking real-world data, I have… On disk, that table’s .ibd file looks like this: # cd /var/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8… tablename.frm -rw-rw—- 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query, there…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… the optimizer not choose the faster one? That’s what this post is about. Let’s start with the MySQL query optimizer. The optimizer… has the lowest cost. The unit of cost for the MySQL query optimizer is a single random 4k data page read. In… the missing information. If the MySQL optimizer were right and each of these had to go to disk, our STRAIGHT_JOIN plan would…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… – make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs/Seeks…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and variable…core_fileblogpercona.commanual Created_tmp_disk_tablesblogpercona.commanual Created_tmp_…tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
…Optimal WT, RA PhysiclDev Type State Errors Vendor Model Size ========== ==== ======= ====== ======= ============ =========== Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk… IO alignment, not different MySQL configurations, I didn’t try out different MySQL versions or settings. …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot… how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10… data from other tables. No information about using disk for sort or temporary tables MySQL always calls it “filesort” even if it…
