Two different collation for MySQL tables after database server change

Hi,

my hoster moved my Elgg database to another MySQL server (server cluster). I had some serious intermittent performance problems since then. Partly (according to the hoster's support) the bad performance was caused because the new server was overloaded by the number of requests. This part of the problem should be resolved by a hardware upgrade (currently going on).

But I also noticed a delay in server response (especially noticeable at login) even under the best circumstances since the database migration, i.e. when the load was not too high. Then I noticed that two Elgg tables (elggeocode_cache, elgghmac_cache) and also MylSAM suddenly shows latin1_swedish_ci as collation while I am sure everything was utf8_general_ci before the migration. The other tables still are utf8_general_ci and also the MySQL connection collation is utf8_general_ci.

Could the mix in the collations decrease performance?

The support of my hoster gave me the following reply:

I do not believe your collation will be too much of an issue. It should not affect sort performance as the server was designed for this sort of thing.
 
 Collation settings are usually backed up as part of a mysqldump process so you should not see any major issues migrating this db to another host if need be.
 
 For further support on the proper collation settings for your site script, contact your script vendor or script support.
 
 If your site works right now, there's a really good chance messing with it could break it. For now, I do not recommend doing anything.

I'm not sure what to do now. Also not sure if there could occur problems in case the database ever has to be restored with the current backups or in case of an update from Elgg1.6.1 to 1.7.x.

May I ask for advice? Any help would be very welcome.

  • hmmm...
    interesting situation ;-)
    collations shud not affect perf 
    do you have backup of yr db b4 the server relocate ?
    do you have shared ?
    or vps ?
    or dedicated server ?
    my advice -> do not goto v1.7 yet ;-)

  • Shared hosting. Should do for now as the site is not yet that large. Speed was fully alright before the database migration. Maybe it will return to normal as soon as they are finished with the hardware upgrade and maintenance at last.

    I still have a backup made before the database was moved, but it's been 4 weeks ago and I just noticed the collation mismatch this week. Stupid me! But if I would use this backup all new stuff, members, etc. would be lost. I guess I'll have to live with the current situation for now. I don't know what would happen if the collation of the table would be changed. It seems possible to do that although I've not yet found instructions I fully trust. The encoding is (hopefully) still utf8 anyway. Maybe the collation really doesn't affect performance.

    I won't upgrade to 1.7 yet. Maybe 1.7.1...depends on plugins compatibility and I've not even started to test it.