2

I have a large database (~450gb) which I need to convert from utf8 to utf8mb4.

I'm aware of the steps I need to take, but is this practical on tables containing this much data? I'm mainly concerned that CONVERT TO CHARACTER SET utf8mb4 will be too intensive, as it's fairly slow in my test environment (on just a fraction of the rows).

Community
  • 1
Jonathan
  • 188
  • 1
  • 1
  • 8
  • What are your non-functional requirements: time spent, downtime, space available? Is coping rows to new database a viable option? – Stoleg Aug 23 '16 at 14:54
  • @Stoleg If downtime is needed then that's fine. Would copying rows be less intensive than CONVERT TO? – Jonathan Aug 23 '16 at 14:57
  • Copying rows will be more manageable and fail proof. If you have many tables then you can set up a routine: create table with new collation, copy all rows, drop old table, rename. If you have large table then you can copy rows in chunks. There are some optimizations possible. How long do you estimate will conversion take? – Stoleg Aug 23 '16 at 15:04
  • @Stoleg I'm unsure how long conversion will take, which I guess is my problem. Any idea on what MySQL does behind the scenes during a CONVERT TO CHARACTER SET utf8mb4? If it has to modify every single value, then I expect we're looking at many hours (if not days). – Jonathan Aug 23 '16 at 15:26
  • On large datasets control is more important then performance. Do you have several big tables (e.g. 10 tables =90% of DB) or many tables of similar size? – Stoleg Aug 23 '16 at 15:28
  • Dup of http://stackoverflow.com/questions/39104373/convert-large-database-from-utf8-to-utf8mb4 . – Rick James Aug 23 '16 at 23:50
  • Create+copy+rename -- that is what ALTER does. – Rick James Aug 23 '16 at 23:51
  • @RickJames Your link is for this thread, no for another answer. Sometimes you need more control than one long running command. So OP can consider doing those steps manually. – Stoleg Aug 24 '16 at 07:30
  • Does convert lock the table during conversion? – Ruchit Patel Dec 19 '20 at 06:56

0 Answers0