You are here


MyISAM locking and who is the evil?

Taxonomy upgrade extras: 

Yes, I know, MyISAM is deprecated and unofficially discontinued by the vendors. But we still have from time to time customers using MyISAM and even evangelize for MyISAM...

And to be honest in some cases MyISAM has even advantages (beside some huge disadvantages) over other Storage Engines (simple file copy, footprint, single-query latency, ...). But most of our customers are not aware of these advantages and are using MyISAM just because they did it since ever...

Flush time is set

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

For further information consult MariaDB or MySQL documentation.

Switching from MySQL/MyISAM to Galera Cluster

Taxonomy upgrade extras: 

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script ( long time ago already. Because we have made many of those switches recently we have extended its functionality.

MySQL tmpdir on RAM-disk

Taxonomy upgrade extras: 

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

Ändern von MyISAM Tabellen nach InnoDB und beheben der SELECT COUNT(*) Situation

Es ist ein bekanntes Problem, dass das Ändern der Storage Engine von MyISAM nach InnoDB Probleme verursachen kann [ 1 ], wenn Abfragen der folgenden Art auftreten:

SELECT COUNT(*) from table;

Glücklicherweise kommt dieser Typ von Abfragen selten vor. Und wenn, kann die Abfrage meist einfach weggelassen oder darum herum gearbeitet werden, indem man die Anzahl Zeilen in der Tabelle schätzt. Zum Beispiel mit:

Change MyISAM tables to InnoDB and handle SELECT COUNT(*) situation

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems [ 1 ] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

How MySQL behaves with many schemata, tables and partitions


Recently a customer claimed that his queries were slow some times and sometimes they were fast.

First idea: Flipping query execution plan caused by InnoDB could be skipped because it affected mainly MyISAM tables.

Second idea: Caching effects by either the file system cache caching MyISAM data or the MyISAM key buffer caching MyISAM indexes were examined: File system cache was huge and MyISAM key buffer was only used up to 25%.

I was a bit puzzled...

How good is MySQL INSERT TRIGGER performance

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

How many warm MyISAM key blocks do you have?

When you are working with MyISAM [ 1 ] tables MySQL provides a feature called the Midpoint Insertion Strategy [ 2 ]. You can enable it with the parameter key_cache_division_limit [ 3 ].

By default, the key cache management system uses a simple LRU [ 4 ] strategy for choosing key cache blocks to be purged:

MyISAM key buffer

Subscribe to RSS - myisam