myisam

Storage Engines like InnoDB, Aria and MyISAM are Row Stores. They store rows one after the other in blocks or even directly in a single file (MyISAM). On the other hand a Column Store like MariaDB ColumnStore stores all the same attributes (columns) of the rows together in chunks.

This is how the table sales_fact looks like:

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

Because they do NOT reflect your problems.

One of our customers upgraded last month from MariaDB 10.2 to MariaDB 10.5. In the same change he also converted all his data warehouse (DWH)/BI tables from MyISAM to Aria. An all this, naturally, without testing. And it miserably failed! And then we were under heavy time pressure to make things working again...

What has changed:

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...

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 (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

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:

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:

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:

Introduction

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...

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.

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: