… reason of MyISAM performing better than Innodb. In this article I’ll get in a bit more details about packed keys and performance implications…) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0 – 8176K As… is almost 3 times faster. With PACK_KEYS=1 we got join by integer key performing almost 5 times slower than it was…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… discussed MyISAM key cache has serious mutex contention issue as there is global mutex which is held for the time of key_block… multiple key caches . We use “keycache per table” this case and caused number of context switches to drop almost tenfold and performance almost doubled. Another interesting result was – adding PACK_KEYS=0 to most actively updated tables…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… data distribution but good enough for performance gotcha illustration purposes. The benchmark shows following results for MyISAM using MySQL 5.0.45… As you can see Falcon perform extremely poorly when single query executed being 1/3rd of MyISAM and 1/6th of Innodb… is comparison of MyISAM Innodb and Falcon results in the graphical form: I’ve created bug, for MyISAM key cache contention issue and…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… do I read these results ? CHAR keys are indeed slower for joins compared to integer keys Performance degradation can range from few percent to couple of times for Innodb tables MyISAM Tables may suffer significantly if key compression…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…` smallint(5) unsigned NOT NULL default ’0′, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`,`state_id`,`city`) ) In… bad performance. We hope the performance of LIMIT queries will be fixed before release. MyISAM shows stable result. InnoDB is better than MyISAM by…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let’s trust MyISAM‘s creators… true performance optimization method. I’ve shown that if we make some assumptions that are hard to prove, we can compare Key… might be causing a performance problem. But I haven’t shown you how to choose an appropriate key_buffer_size. Let’s…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… important than performance for small application. As load increases you might convert certain tables to MyISAM and other storage engines for performance reasons… row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in OS cache. It is performance issue as…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… have key_buffer, not “myisam_key_buffer” for example. The other excurse Monty used to make was – it is not really MyISAM specific. key_buffer… rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables will be badly corrupted…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… MyISAM does compression for character keys which makes it perform slower for random lookups MyISAM generally has lower processing overhead due to its simplicity MyISAM still a bit better by primary key…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
…http://www.mysqlperformanceblog.com/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/, and queries I posted on …DestCityName`,`OriginCityName`), KEY `Year_4` (`Year`,`Carrier`,`DepDelay`), KEY `Origin` (`Origin`,`Year`,`DepDelay`) And I measured load time for both MyISAM and …
