0

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.

  • Is there any way you can take a .csv dump of your file and import that instead of a series of SQL statements? I know that for PostgreSQL, the \COPY command (.csv text) is way quicker than the pg_dump process for the same data. Personal opinion: If this is your first project with an Open Source database, you should consider PostgreSQL. It's a very slick product and very competent technically! p.s. welcome to the forum! :-) – Vérace May 4 at 1:12
  • I am not sure I want to change the server type at this point. I could export the data into a csv then use LOAD DATA FROM FILE. but I am hoping I don't have to stop the import process as that would start me back at square 1. I am hoping I can tune up some of the settings to make the insert statements process faster. I will take your advice and try PostgreSQL at some point but not this time – Junior May 4 at 1:16
  • Understood re. server change. It's only about 10GB, so it can't take that long, can it? I've no idea how long this takes on MySQL, but I'd be surprised if it took more than an hour or two. I"ve inserted 10M tiny records on an SSD on an 8GB RAM laptop in ~ 30s (not with MySQL though). My strong recommendation is to learn which technique is better now, because as sure as I'm sitting here typing, you'll have to do something similar again. If you expose yourself to both methods on MySQL, you'll be able to make better (technically and speed wise) decisions next time! Good luck with project! – Vérace May 4 at 1:22
  • Is it swapping? – Rick James May 4 at 2:43
  • @MikeA Changing GLOBAL VARIABLES dynamically will only be used for CONNECTIONS made AFTER you can verify with SELECT @@global_variable to confirm that MySQL has your new value in place. Any process running with a PID will not be affected by a value SET after the process has started. Any process running will continue with process starting values until the process has completed. – Wilson Hauck May 4 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.