Author Topic: Convert tables from MyISAM to InnoDB  (Read 278 times)

stinga

  • Sr. Member

  • Offline
  • ****
  • 266
    • CoastFM
Convert tables from MyISAM to InnoDB
« on: August 11, 2018, 01:32:24 PM »
Has anyone done this?

Looking at all the queries RDJ generates I don't think there would be an issue doing this.

Is would help with locking, and is just better....

Code: [Select]
nostromo Sat Aug 11 12:28:07 2018 [information_schema]: select TABLE_SCHEMA,TABLE_NAME,ENGINE from tables where TABLE_SCHEMA='radiodj';
+--------------+-------------------+--------+
| TABLE_SCHEMA | TABLE_NAME        | ENGINE |
+--------------+-------------------+--------+
| radiodj      | carts             | MyISAM |
| radiodj      | carts_list        | MyISAM |
| radiodj      | category          | MyISAM |
| radiodj      | events            | MyISAM |
| radiodj      | events_categories | MyISAM |
| radiodj      | genre             | MyISAM |
| radiodj      | history           | MyISAM |
| radiodj      | playlists         | MyISAM |
| radiodj      | playlists_list    | MyISAM |
| radiodj      | queuelist         | MyISAM |
| radiodj      | requests          | MyISAM |
| radiodj      | rotations         | MyISAM |
| radiodj      | rotations_list    | MyISAM |
| radiodj      | song_type         | InnoDB |
| radiodj      | songs             | MyISAM |
| radiodj      | subcategory       | MyISAM |
+--------------+-------------------+--------+
16 rows in set (0.00 sec)
Helps run CoastFM in Penzance UK
Running 4 x 1.8.2.0
mariaDB and tracks on a central server.

stinga

  • Sr. Member

  • Offline
  • ****
  • 266
    • CoastFM
Re: Convert tables from MyISAM to InnoDB
« Reply #1 on: October 07, 2018, 05:56:53 PM »
Either...
1 - nobody has done this
2 - nobody knows what I am asking.... :-)
Helps run CoastFM in Penzance UK
Running 4 x 1.8.2.0
mariaDB and tracks on a central server.

plauri

  • Hero Member

  • Offline
  • *****
  • 1154
    • Funky Corner Radio
Re: Convert tables from MyISAM to InnoDB
« Reply #2 on: October 08, 2018, 12:27:06 PM »
Either...
1 - nobody has done this
2 - nobody knows what I am asking.... :-)

number 2  :bash:  :D
Funky Corner Radio
http://www.funkycorner.it
http://www.radiofunkycorner.com

stinga

  • Sr. Member

  • Offline
  • ****
  • 266
    • CoastFM
Re: Convert tables from MyISAM to InnoDB
« Reply #3 on: October 08, 2018, 12:49:16 PM »
Thought so! :-)
Helps run CoastFM in Penzance UK
Running 4 x 1.8.2.0
mariaDB and tracks on a central server.

Calypso

  • Sr. Member

  • Offline
  • ****
  • 258
Re: Convert tables from MyISAM to InnoDB
« Reply #4 on: October 12, 2018, 06:02:51 PM »
Thought so! :-)

None of both; it's option 3 (I'll explain later).

At least I know what you're talking about, and I've done it; my databases are InnoDB. Also because I have the database running on a stretched Percona cluster, so you can't put it into ISAM.

The reason it's not done regularly is that most users of RadioDJ use the database as a local install on their playout system, and the benefits of InnoDB are on systems with the surplus on capacity that normal computers have these days not really notable.

However, it differs a fraction, and if you're in for it, you can use it.

stinga

  • Sr. Member

  • Offline
  • ****
  • 266
    • CoastFM
Re: Convert tables from MyISAM to InnoDB
« Reply #5 on: December 05, 2018, 02:55:50 PM »
Was there an option 3?

We are experiencing table locking issues I believe so I am converting to innodb... slowly.... :-)
Helps run CoastFM in Penzance UK
Running 4 x 1.8.2.0
mariaDB and tracks on a central server.

stinga

  • Sr. Member

  • Offline
  • ****
  • 266
    • CoastFM
Re: Convert tables from MyISAM to InnoDB
« Reply #6 on: December 06, 2018, 07:03:12 PM »
Update:

I realised the query
Code: [Select]
SELECT COUNT\( ID \) FROM `songs` WHERE `enabled`='1'was going to be an issue so I have modified the sql query so that we don't do that any more.
Helps run CoastFM in Penzance UK
Running 4 x 1.8.2.0
mariaDB and tracks on a central server.