…optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal… Presentations. Application tuning for Innodb Especially when coming from MyISAM background there would be some changes you would like …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… some search is required, so count(*) can’t be optimized away for MyISAM Tables. To show percents for the values we need… well implemented inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and… lot of other black magic to keep things as optimal as possible though performance is still far from our goal of real…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last… their database 100% in RAM (even for Disk tables such MyISAM or Innodb) others keep database on SSD which has completely… CPU and IO cost. Focus on Execution Methods Performance problems can be due to optimizer picking the wrong plan, such as doing…
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 … and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I’ve…(10) unsigned NOT NULL, `slack` varchar(50) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… see how Falcon performs in comparison to InnoDB and MyISAM. The second goal of benchmark was a popular myth that MyISAM is faster… bad performance. We hope the performance of LIMIT queries will be fixed before release. MyISAM shows stable result. InnoDB is better than MyISAM by… scales pretty bad and there is a big room for optimization. READ_PK_RANGE Query: SELECT min(dob) FROM $tableName WHERE…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… each buffer hit or miss takes. If you approach application performance optimization from the standpoint of response time measurements, which you should… machine that’s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB, some MyISAM, which is more important to…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case MyISAM… with MyISAM. QA has to be performed as part of the move. Performance Innodb has a lot to offer in terms of performance – Performance benefits… would not switch table to MyISAM because it gives 5% performance improvement but I can perfectly use MyISAM (or Archive) for logging. Innodb…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… I wrote about how I measured the performance on a join between a few tables…’s start with the MySQL query optimizer. The optimizer tries to choose the best join …’fact’\G *************************** 1. row *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… build the functionality you need and get the performance you need. Because I’ve built two such … cost of repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-…s still another reason to use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable:…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…(for the same database) you join the better performance you will get. As for any rules … KEY (`id`), KEY `d` (`d`,`group_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18007591 DEFAULT CHARSET=latin1 mysql> …(and newer MySQL versions will stop using this optimization method if there are too many combinations to…
