Database configuration questions after a server crash

[djbaxter]

The forum in question has been restored from backup and is currently running but I am trying to ensure everything is correctly configured and stable.

However, I am checking everything for any anomalies and I am wondering about the following in the MySQL/MariaDB database:

• Server connection collation utfmb4_unicode_ci
• Collation for all tables utf8mb4_general_ci
• in phpMyAdmin, the bottom line below the list of tables says 221 tables Sum ~35,806,670 MyISAM latin1_swedish_ci 19.8 GiB 47.3 KiB - again, I'm wondering about the latin1_swedish_ci since that's the only place I see this - is that a holdover from a vBulletin 4 import?

Are those values okay or should the collations be identical?

All tables are InnoDB except the following:

• xf_attachment_view MEMORY
• xf_search_index MyISAM
• xf_session MyISAM
• xf_session_activity MEMORY
• xf_session_admin MyISAM
• xf_session_install MyISAM
• xf_thread_view MEMORY

Why are some of those tables MyISAM? Should they be that way or should they also be converted to InnoDB?

For the MEMORY tables, are they best left that way?

 

[djbaxter]

Bumping because I posted this on the weekend

 

[bzcomputers]

It appears your tables types are identical to mine for XenForo 2.1.

The only thing that appears off is your default server charset which appears to be set to Latin1 for that server. You'll probably also see this referenced under your phpMyAdmin "Database server" notes (shown when you initially open phpMyAdmin).

Check these settings are included under [mysqld] in my.cnf:

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

I think you'll find at least the first one missing or set to latin1.

 

[iCafe]

MEMORY Hash based, stored in memory, useful for temporary tables.
MyISAM is often faster than InnoDB in terms of raw performance (mostly because it is not ACID). Therefore accessing MyISAM consumes less resources than InnoDB.

On the other hand, MyISAM only supports table-level locking: in a highly concurrent environment, latency increases.