June 18, 2013

Post: Troubleshooting MySQL Upgrade Performance Regressions

February 15, 2012 By Leave a Comment

… baseline. Do not change storage engine at the same time eather… consisting of several query types. Though it is rather …tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL… options (might be even defaults) or some more subtle …

Filed Under: Insight for DBAs, MySQL

Post: Efficient Boolean value storage for Innodb Tables

April 23, 2008 By 6 Comments

… boolean flag storage in Innodb (not accounting for standard row overhead) if we use BIT(1), TINYINT or ENUM types but can…(0) default NULL, `c8` char(0) default NULL, `c9` char(0) default NULL, `c10` char(0) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show table status like…

Filed Under: Insight for Developers Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…MySQL 5.6, MyISAM was the only storage engine…DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 We also have identical tables…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: How much overhead is caused by on disk temporary tables

August 16, 2007 By 26 Comments

… used medium size table and types which can be presented both in MEMORY and MyISAM storage engine. The benchmarks are done with MySQL 5.0… for testing. CREATE TABLE `gt` ( `i` int(10) unsigned NOT NULL, `c` char(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> select * from…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 with all default… table scans access the table by primary key.  This forces the storage engine… 2. row *************************** id: 1 select_type: SIMPLE table: lineorder type: ref possible_keys: LO…

Filed Under: MySQL Tagged With: , , , , ,

Post: Should you move from MyISAM to Innodb ?

January 12, 2009 By 59 Comments

… MySQL server – in particular Optimizer may have harder time because costs between storage engines may not be well balanced or replication of mixed table types… default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Falcon Storage Engine Design Review

January 12, 2007 By 19 Comments

… spinning done, at least for some types of locks where conflicts will be … MySQL Storage Engine level for MySQL – reading rows in their physical order can be applied to most storage engines…for example changing default value needs table rebuild even though for other storage engines it often could …

Filed Under: Insight for DBAs Tagged With:

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… size mrr_buffer_size. Both buffer sizes default to 256K in MySQL 5.6 and MariaDB 5.5 … are performed in a single call to the storage engine and the counters Handler_read_key and Innodb_… query execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: JOIN Performance & Charsets

October 21, 2008 By 2 Comments

…storage engine: CREATE TABLE `t1` ( `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`char_id`) ) ENGINE=InnoDB DEFAULT…(0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row … *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_…

Filed Under: Uncategorized Tagged With: , , ,

Post: MySQL Optimizer and Innodb Primary Key

October 3, 2006 By 9 Comments

…ENGINE=InnoDB DEFAULT CHARSET=latin1; “myisam” is same table created with MyISAM storage engine used to show difference: MySQL Optimizer correctly knows Innodb tables…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts
Newer Posts »