MyISAM vs InnoDB

For discussion of topics specific to MythTV on linux
Post Reply
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

MyISAM vs InnoDB

Post by dlasher »

long story short, I've kept up (mostly) with myth versions, but my database is very very old. (First recording:
Wednesday July 28th, 2004
).

Code: Select all

Ubuntu 22 LTS
MythTV Version : v32.0+fixes.20220325.f69ce764b7-0ubuntu1
I've set my sights on improving database performance, since things like mythfilldatabase takes a LONG time, and contends the machine pretty heavily.

I've moved the DB over the MariaDB, which has been working great, some speed improvements (gives back the query cache mysql took away) - and now I'm trying to add a little redundancy via replication.

However, in spite of all the mysql config items being around the InnoDB engine, turns out quite a few of the mythdb tables (all but 1?) are "ENGINE=MyISAM" - which MariaDB/Galera doesn't replicate. It only replicates InnoDB tables. (unless I'm missing something obvious), so I have replication up and working, and a bunch of empty tables on the slaves. :(

I started down the path of converting tables from MyISAM to InnoDB, but evidently quite a few of the tables are missing primary keys, which makes converting them difficult, and gives fun error messages.

So couple thoughts:

1. Why MyISAM tables vs InnoDB tables? Given the performance benefits, I'm surprised it hasn't changed.
2. Has anyone successfully converted them all to InnoDB? (Mind sharing how you did it?)
3. I did a fresh v32 install on ubuntu 22 LTS, and the freshly created database has 90 of 91 tables as Engine=MyISAM


(Stolen from the web, because I didn't know - more here: https://blog.devart.com/myisam-vs-innodb.html)
The main differences between MyISAM and InnoDB
Overall, MyISAM is an older and less efficient storage engine than InnoDB. The most commonly noted differences between these two engines are as follows: InnoDB is more stable, faster, and easier to set up; it also supports transactions. It is the default storage engine of choice for a reason, and you can use the features of InnoDB with no need to worry about compatibility issues. If you need to store large amounts of data or ensure that transactions will work correctly, choose InnoDB.

The MyISAM engine is not very good at storing large amounts of data, because it stores everything in a single table. When you need to add data to the database, you have to lock the entire table, which can cause your database to stop working until it is unlocked. In the InnoDB engine, each row is stored separately in a separate table. This means that when you insert data into a MySQL database, you do not need to lock all rows.
This one also jumped out at me (thinking commflag)
The InnoDB storage engine is recommended for frequent operations with tables
Am I missing something here? seems obvious we should have left MyISAM behind a while ago?
...
First recording: Wednesday July 28th, 2004
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

My general direction in trying this:
* find all the tables w/o primary keys
* give them a new column ( auto-incrementing ID as primary key )
* convert table to InnoDB
* make sure nothing breaks
...
First recording: Wednesday July 28th, 2004
Gribnif
Junior
Posts: 88
Joined: Wed May 14, 2014 12:46 pm
United States of America

Re: MyISAM vs InnoDB

Post by Gribnif »

In general, it's not up to a consumer of a database like MythTV to define which engine is used. Rather, it's up to the administrator to decide. You can set the default engine by editing your /etc/my.cnf to include this line:

Code: Select all

default_storage_engine = InnoDB
But, as you probably expect, that only affects newly-created tables. To convert any existing tables, you'll want to do something like this (it's one long line):

Code: Select all

echo 'SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "` ENGINE=InnoDB;") FROM information_schema.TABLES WHERE engine="MyISAM" AND TABLE_SCHEMA = "mythconverg";' | mysql -u mythtv -p > /tmp/alter.sql
Examine the file /tmp/alter.sql to make sure it's not altering anything you don't want it to, then:

Code: Select all

mysql -u mythtv -p < /tmp/alter.sql
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

Quick update, part way into this process, finding a couple of things:

* 21 out of 91 tables don't have a PRIMARY KEY (like settings?) which prevents them from being directly converted to InnoDB

Code: Select all

credits.sql
diseqc_config.sql
displayprofiles.sql
dtv_privatetypes.sql
filemarkup.sql
housekeeping.sql
inputgroup.sql
internetcontent.sql
internetcontentarticles.sql
inuseprograms.sql
keyword.sql
pidcache.sql
programrating.sql
recordedartwork.sql
recordedcredits.sql
recordedrating.sql
recordmatch.sql
settings.sql
videometadatacast.sql
videometadatacountry.sql
videometadatagenre.sql
* I've dumped the database, imported it in a VM, then after a careful exam of the table data, made up a primary key and tested it. When nothing breaks, I'll make that change on my primary DB. So far so good. I have to modify the dump files to INSERT table(column1,column2,column3) or the import fails, but that's easy to fix. In the case of settings, I used something like this:

Code: Select all

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
I've converted about a dozen tables on my main DB, and no negative issues so far, but while 70 of the tables have primary keys (out of 91) it's appears to me that the developers still have a lean towards MyISAM in the table design, at least at the moment.
...
First recording: Wednesday July 28th, 2004
User avatar
kmdewaal
Developer
Posts: 641
Joined: Wed Dec 07, 2016 8:01 pm
Netherlands

Re: MyISAM vs InnoDB

Post by kmdewaal »

I did convert on my development laptop all tables in the mythconverg database to InnoDB without any problems, using the script from Gribnif.
This is done on a recent installation from scratch, using Fedora 36 with MariaDB.
Maybe it makes a difference how old the original install is?
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

kmdewaal wrote:
Sun Oct 30, 2022 6:15 pm
I did convert on my development laptop all tables in the mythconverg database to InnoDB without any problems, using the script from Gribnif.
This is done on a recent installation from scratch, using Fedora 36 with MariaDB.
Maybe it makes a difference how old the original install is?
That's good to hear, thanks for sharing. Thinking the fact you started on MariaDB helps as well. The default ubuntu install uses MySQL 8.0, so I'm converting from there as my starting point.
...
First recording: Wednesday July 28th, 2004
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

kmdewaal wrote:
Sun Oct 30, 2022 6:15 pm
I did convert on my development laptop all tables in the mythconverg database to InnoDB without any problems, using the script from Gribnif.
This is done on a recent installation from scratch, using Fedora 36 with MariaDB.
Maybe it makes a difference how old the original install is?
Would you mind sharing the structure part of your settings table? Is it created with primary key of some sort?
...
First recording: Wednesday July 28th, 2004
User avatar
kmdewaal
Developer
Posts: 641
Joined: Wed Dec 07, 2016 8:01 pm
Netherlands

Re: MyISAM vs InnoDB

Post by kmdewaal »

This is the settings table:

Code: Select all

MariaDB [mythconverg]> describe settings;
+----------+----------------+------+-----+---------+-------+
| Field    | Type           | Null | Key | Default | Extra |
+----------+----------------+------+-----+---------+-------+
| value    | varchar(128)   | NO   | MUL |         |       |
| data     | varchar(16000) | NO   |     |         |       |
| hostname | varchar(64)    | YES  |     | NULL    |       |
+----------+----------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

MariaDB [mythconverg]>

This shows the engine used per table:

Code: Select all

MariaDB [mythconverg]> SELECT TABLE_SCHEMA, TABLE_NAME,  ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = "mythconverg";
+--------------+--------------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME                     | ENGINE |
+--------------+--------------------------------+--------+
| mythconverg  | archiveitems                   | InnoDB |
| mythconverg  | bdbookmark                     | InnoDB |
| mythconverg  | capturecard                    | InnoDB |
| mythconverg  | cardinput                      | InnoDB |
| mythconverg  | channel                        | InnoDB |
| mythconverg  | channelgroup                   | InnoDB |
| mythconverg  | channelgroupnames              | InnoDB |
| mythconverg  | channelscan                    | InnoDB |
| mythconverg  | channelscan_channel            | InnoDB |
| mythconverg  | channelscan_dtv_multiplex      | InnoDB |
| mythconverg  | codecparams                    | InnoDB |
| mythconverg  | credits                        | InnoDB |
| mythconverg  | customexample                  | InnoDB |
| mythconverg  | diseqc_config                  | InnoDB |
| mythconverg  | diseqc_tree                    | InnoDB |
| mythconverg  | displayprofilegroups           | InnoDB |
| mythconverg  | displayprofiles                | InnoDB |
| mythconverg  | dtv_multiplex                  | InnoDB |
| mythconverg  | dtv_privatetypes               | InnoDB |
| mythconverg  | dvdbookmark                    | InnoDB |
| mythconverg  | dvdinput                       | InnoDB |
| mythconverg  | dvdtranscode                   | InnoDB |
| mythconverg  | eit_cache                      | InnoDB |
| mythconverg  | filemarkup                     | InnoDB |
| mythconverg  | gallery_directories            | InnoDB |
| mythconverg  | gallery_files                  | InnoDB |
| mythconverg  | gamemetadata                   | InnoDB |
| mythconverg  | gameplayers                    | InnoDB |
| mythconverg  | housekeeping                   | InnoDB |
| mythconverg  | inputgroup                     | InnoDB |
| mythconverg  | internetcontent                | InnoDB |
| mythconverg  | internetcontentarticles        | InnoDB |
| mythconverg  | inuseprograms                  | InnoDB |
| mythconverg  | iptv_channel                   | InnoDB |
| mythconverg  | jobqueue                       | InnoDB |
| mythconverg  | jumppoints                     | InnoDB |
| mythconverg  | keybindings                    | InnoDB |
| mythconverg  | keyword                        | InnoDB |
| mythconverg  | livestream                     | InnoDB |
| mythconverg  | logging                        | InnoDB |
| mythconverg  | music_albumart                 | InnoDB |
| mythconverg  | music_albums                   | InnoDB |
| mythconverg  | music_artists                  | InnoDB |
| mythconverg  | music_directories              | InnoDB |
| mythconverg  | music_genres                   | InnoDB |
| mythconverg  | music_playlists                | InnoDB |
| mythconverg  | music_radios                   | InnoDB |
| mythconverg  | music_smartplaylist_categories | InnoDB |
| mythconverg  | music_smartplaylist_items      | InnoDB |
| mythconverg  | music_smartplaylists           | InnoDB |
| mythconverg  | music_songs                    | InnoDB |
| mythconverg  | music_stats                    | InnoDB |
| mythconverg  | music_streams                  | InnoDB |
| mythconverg  | mythlog                        | InnoDB |
| mythconverg  | newssites                      | InnoDB |
| mythconverg  | oldfind                        | InnoDB |
| mythconverg  | oldprogram                     | InnoDB |
| mythconverg  | oldrecorded                    | InnoDB |
| mythconverg  | people                         | InnoDB |
| mythconverg  | pidcache                       | InnoDB |
| mythconverg  | playgroup                      | InnoDB |
| mythconverg  | powerpriority                  | InnoDB |
| mythconverg  | profilegroups                  | InnoDB |
| mythconverg  | program                        | InnoDB |
| mythconverg  | programgenres                  | InnoDB |
| mythconverg  | programrating                  | InnoDB |
| mythconverg  | recgrouppassword               | InnoDB |
| mythconverg  | recgroups                      | InnoDB |
| mythconverg  | record                         | InnoDB |
| mythconverg  | record_tmp                     | InnoDB |
| mythconverg  | recorded                       | InnoDB |
| mythconverg  | recordedartwork                | InnoDB |
| mythconverg  | recordedcredits                | InnoDB |
| mythconverg  | recordedfile                   | InnoDB |
| mythconverg  | recordedmarkup                 | InnoDB |
| mythconverg  | recordedprogram                | InnoDB |
| mythconverg  | recordedrating                 | InnoDB |
| mythconverg  | recordedseek                   | InnoDB |
| mythconverg  | recordfilter                   | InnoDB |
| mythconverg  | recordingprofiles              | InnoDB |
| mythconverg  | recordmatch                    | InnoDB |
| mythconverg  | roles                          | InnoDB |
| mythconverg  | romdb                          | InnoDB |
| mythconverg  | scannerfile                    | InnoDB |
| mythconverg  | scannerpath                    | InnoDB |
| mythconverg  | settings                       | InnoDB |
| mythconverg  | sportsapi                      | InnoDB |
| mythconverg  | sportscleanup                  | InnoDB |
| mythconverg  | sportslisting                  | InnoDB |
| mythconverg  | storagegroup                   | InnoDB |
| mythconverg  | tvchain                        | InnoDB |
| mythconverg  | tvosdmenu                      | InnoDB |
| mythconverg  | upnpmedia                      | InnoDB |
| mythconverg  | user_permissions               | InnoDB |
| mythconverg  | user_sessions                  | InnoDB |
| mythconverg  | users                          | InnoDB |
| mythconverg  | videocast                      | InnoDB |
| mythconverg  | videocategory                  | InnoDB |
| mythconverg  | videocollection                | InnoDB |
| mythconverg  | videocountry                   | InnoDB |
| mythconverg  | videogenre                     | InnoDB |
| mythconverg  | videometadata                  | InnoDB |
| mythconverg  | videometadatacast              | InnoDB |
| mythconverg  | videometadatacountry           | InnoDB |
| mythconverg  | videometadatagenre             | InnoDB |
| mythconverg  | videopart                      | InnoDB |
| mythconverg  | videopathinfo                  | InnoDB |
| mythconverg  | videosource                    | InnoDB |
| mythconverg  | videotypes                     | InnoDB |
| mythconverg  | weatherdatalayout              | InnoDB |
| mythconverg  | weatherscreens                 | InnoDB |
| mythconverg  | weathersourcesettings          | InnoDB |
| mythconverg  | websites                       | InnoDB |
+--------------+--------------------------------+--------+
113 rows in set (0.003 sec)

MariaDB [mythconverg]>
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

kmdewaal wrote:
Sun Oct 30, 2022 8:39 pm
This is the settings table:
Thank you, sorry, let me be more specific.

I did a fresh v32 install on ubuntu 22, which then installs MySQL 8.x as a dependency. Once the server is up and running, I dump the settings table, which looks like this:

Code: Select all

CREATE TABLE `settings` (
  `value` varchar(128) NOT NULL DEFAULT '',
  `data` varchar(16000) NOT NULL DEFAULT '',
  `hostname` varchar(64) DEFAULT NULL,
  KEY `value` (`value`,`hostname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

When trying to convert that table to InnoDB - I get the complaint about "no primary key" so I have to create/set one or the table won't import. I tried using value/hostname, but I had duplicate entries (cleaned up now), additionally no part of a primary key may be NULL, then I ran into issues with the values together being too large for a primary key, so I created a new one.

Code: Select all

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
 
That is working just fine.. but was hoping your install had a differently formatted primary key.

PSA: If you have duplicate rows, the fastest way to pare that down is to "SELECT DISTINCT" into a new table
Last edited by dlasher on Mon Oct 31, 2022 12:36 am, edited 1 time in total.
...
First recording: Wednesday July 28th, 2004
dlasher
Newcomer
Posts: 14
Joined: Thu May 05, 2022 3:09 am
United States of America

Re: MyISAM vs InnoDB

Post by dlasher »

Gribnif wrote:
Thu Oct 27, 2022 6:41 pm
In general, it's not up to a consumer of a database like MythTV to define which engine is used. Rather, it's up to the administrator to decide....
True, however, if you are using a version from a public repo (mythtv/ubuntu) it requires mysql, and creates the new database as MyISAM. Not taking that default takes a fair amount of work, so someone has decided. :)
...
First recording: Wednesday July 28th, 2004
Post Reply