I have an application that is connected to a SQL Server Express database. The database grew and the database is almost at 10GB which is the max limit that SQL Server Express allows for.
I am trying to switch to MySQL server instead of with only InnoDB engine. I installed MySQL 8.0.16 community edition on a Windows Server 2016 VPS. I am using the migration wizard in MySQL Workbench to migrate the database over. But the data import is painfully slow. I understand that it's going to take time to transfer the 10GB databases, but I am hoping there are things I can do to make the data transfer a bit faster.
Now, both SQL and MySQL servers are running on the same VPS. Which makes the connection speed neglectable. But, since I have two database servers running on the same machine, the machine is willing to slap me across the face if it could. The server is dying for more resources that I can't offer. My plan is to remove SQL Server from the machine once the migration is complete.
What temporary changes I can make to either MySQL or SQL Server to make this process runs a bit faster.
Since my database server is not yet in production, I ran the following two command on the MySQL Server "hoping this will speed it up even for a little"
SET GLOBAL FOREIGN_KEY_CHECKS=0; SET GLOBAL UNIQUE_CHECKS = 0;
I have also disabled the keys on all of my large tables using the following commands
ALTER TABLE `Table1` DISABLE KEYS; ALTER TABLE `Table2` DISABLE KEYS;
All the commands I used were executed after the import was on in the process. I am not sure if the changes I made will affect the current connection or not (i.e, "the import wizard connection")
When I check the health of my server, I see about 1.5GB of free memory that I could probably give to the MySQL server, but not sure what is the most beneficial setting for me to increase to help with this import. Running
SHOW PROCESSLIST shows that the migration wizard sends bulk large
INSERT INTO .... statements to the server wait until it is committed and then sends another insert.
What other steps can I do now to help the process move faster? Are there online configuration variables I can increase to help speed up the
INSERT statements without restarting the insert process as that will only put me behind even more.