MySQL Full Unicode Support - SLOW!!

MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Number of replies: 18
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

As part of testing for moving to 3.2 / 3.3 I have been testing the mysql_collation.php script that modifies the collation of the entire database.

My issue is that I have currently been watching it go by for 24 hours and it's only really got halfway through. 

Taking a big site down for days to do this is going to involve some very shouty meetings. 

Has anybody got any thoughts or brilliant ideas? 

EDIT:

I'm also a bit concerned that the conversion burns up disk space. This may be a consideration for users without much free space. 

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

Something is wrong - this is normally a few second operation...

Average of ratings: -
In reply to Emma Richardson

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Hi Emma,

Are we talking about the same thing...

mdl_local_corehr_log                     - CONVERTED
    personnelno                          - CONVERTED
    coursecode                           - CONVERTED
    trainingstatus                       - CONVERTED
    startdate                            - CONVERTED
    enddate                              - CONVERTED
    wsstatus                             - CONVERTED
mdl_lock_db                              - CONVERTED
    resourcekey                          - CONVERTED
    owner                                - CONVERTED
mdl_log                                  - CONVERTED
    ip                                   - CONVERTED
    module                               - CONVERTED
    action                               - CONVERTED
    url                                  - CONVERTED
    info                                 - CONVERTED
mdl_log_27022014                         - CONVERTED
    ip                                   - CONVERTED
    module                               - CONVERTED
    action                               - CONVERTED
    url                                  - CONVERTED
    info                                 - CONVERTED
mdl_log_display                          - CONVERTED
    module                               - CONVERTED
    action                               - CONVERTED
    mtable                               - CONVERTED
    field                                - CONVERTED
    component                            - CONVERTED
mdl_log_queries                          - CONVERTED
    sqltext                              - CONVERTED
    sqlparams                            - CONVERTED
    info                                 - CONVERTED
    backtrace                            - CONVERTED
mdl_logstore_standard_log                - CONVERTED
    eventname                            -
Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Chris Fryer -

Hi Howard,

This is interesting.  I wasn't aware that any conversion was necessary to upgrade to 3.3.  Is this documented yet?

Our charset is utf8 and our collation is utf8_general_ci (which is a bit of a pain since I specified utf8_unicode_ci as the DEFAULT COLLATE when creating the DB).

I'd be interested to know what your collation was beforehand.  Also, how many records are there in mdl_logstore_standard_log?  We have around 53.3 million rows, and I would expect that to take a long time to process, should any changes be necessary.

Average of ratings: -
In reply to Chris Fryer

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't believe it's *necessary* just recommended. That means it'll be necessary soon.

Documentation is here... MySQL_full_unicode_support

I have a site with about 30,000 users and around 8,000 courses. It looks like this conversion may take several days. Which is a big hassle. 

My collation currently is utf8_unicode_ci

Barracuda is a prerequisite for this but this is a different thing. The purpose is to handle the full range of Unicode characters so your students can use emojis. If you try to insert an emoji now (you can try it here) it breaks horribly. 

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Ken Task -
Picture of Particularly helpful Moodlers

Well, if you are up to an experiment ...

How about setting up a slave to the master DB on another dedicated DB server AND configuring the slave to have the needed collation settings?

https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-diffengines.html

Theory ... even though I couldn't find info concerning collation specifically, if diff engines could be used, why not collation?    Remember ... am not a true DB admin! smile

Once 'fixed' ... turn off slave replication.   Would still have the issue of downtime while re-importing an SQL dump back onto the Production server.   But from there on out, the script will have less to contend with on production setup.

OR if really a risk taker, flip the config of master and slave.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Ooft.... I'm just trying this on a dev box at the moment. For people with big sites (who are likely to be risk averse) this is looking like a big hit.

I'm wondering about dumping the database and doing a find/replace on the resulting SQL. However, I've had several bad experience doing that in the past. 

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Ken Task -
Picture of Particularly helpful Moodlers

Sorry ... didn't mean to 'hit below the belt', but ... it's always been upwards. :|

I too had an issue with migrating a Windows to Linux site that had large DB (not as large as yours but large enough) ... It was not only a move of platform but a move of DB ... MySQL to MariaDB.   MariaDB hadn't been tweaked yet.   Grumble, grumble.  

Thank goodness I had a dedicated DB box.  Did go the route of using nano for search and replace on an sql before importing ... no issues ... but it sure took a long time .... 30 minutes if I re-call correctly.  In the meantime, site in maintenance mode.  Don't think that can be helped.

Even had to remove rows in the large logs tables .... all those past a certain epoch time stamp.

I'd gladly give up some very old logs for moving forward.

'spirit of sharing', Ken



Average of ratings: -
In reply to Ken Task

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Not at all... all suggestions welcome. 

I'm now at 48 hours and still only got to L (for logs).  Not seeing the funny side sad

In fact, I think it's worth a MDL-58729

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Just for the record I gave up. 

After four full days it was still in the middle of the logs, had consumed nearly all the disk space and was slowing the machine to a near halt. 

It's really not happening on the production site. 

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Just to note that Marina, from Moodle HQ, has added some comments to MDL-58729. Basically, they will look at this after they have deal with more urgent things relating to the 3.3. release.

Average of ratings: Useful (1)
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Sergio Rabellino -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Slightly OT: but if i change the mysql/mariadb config to support full-unicode, a moodle not configured for fully support utf8m4 will suffer from these changes ?
In short: can i have two moodle on the same dbserver, one with full support and one without it ?

@howard: do you have file_per_table enabled and the moodle db converted to this "format", so a .idb for each table ?
Average of ratings: -
In reply to Sergio Rabellino

Re: MySQL Full Unicode Support - SLOW!!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

As far as I know the config.php setting needs to match the database. That is, you need to convert the database and then change the config.php setting. If they don't match I imagine you will have problems of some sort. 

I do have innodb_file_per_table set to 'on'.

Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by sara HHH -

Hi Howard,


i have the same problem.i have updated  my moodle to 3.3. and  this error comes :  mysql_full_unicode_support

(Please consider changing your settings to 'utf8mb4'.)

shall i change  admin/cli/mysql_collation.php from hier:https://github.com/abgreeve/moodle/compare/760ed1dfde...wip-MDL-58729-33

and then run the cli skript (

$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
)


many thanks for your help


Average of ratings: -
In reply to Howard Miller

Re: MySQL Full Unicode Support - SLOW!!

by Andrew Normore -

Our mdl_log table was like 7 million records. I hear you there.

You might consider breaking off your MySQL db in to sections for conversion, uploading the converted table, then swapping them out. 

Average of ratings: -