1

After running the following command on a SQL database, the CPU usage has shot up to 100%. Queries can be taking upwards of 60 seconds. Here is the command:

ALTER TABLE database_splishuser CONVERT TO CHARACTER SET utf8mb4;

Does using the CONVERT TO CHARACTER SET command need further commands to ensure that the table is set up correctly after running it?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
wellthatssomething
  • 155
  • 1
  • 10
  • 1
    Are you searching or joining that table to other strings that are not converted? It may be doing lots of extra work to compare strings of different character sets during queries. – Bill Karwin Aug 13 '18 at 23:02
  • @BillKarwin yes! that was the issue. We figured it out. I'll go ahead and add an answer. – wellthatssomething Aug 14 '18 at 16:49
  • Good job on figuring it out. I'm glad if my suggestion helped. – Bill Karwin Aug 14 '18 at 17:40
  • Everything seems to be under control, except for "need further commands". No, no further commands are needed to change the charset. Unless you want to change the collation too. – Rick James Aug 20 '18 at 04:16

1 Answers1

2

The problem was that we converted one of our tables to a new collation, but a column in that table was frequently used in comparisons with another column from another table in our database. Since we had not converted that table yet, the comparisons took an extremely long amount of time (and usually did not finish, putting lots of strain on the DB).

Converting all the tables in our database to the same collation and character set solved the issue.

wellthatssomething
  • 155
  • 1
  • 10