0

Up until lately, I used a 32 bit version of Mysql 5.7, bundled with a WAMP server (EasyPHP) on Windows for local development, all running on an SSD. I ran into database size issues, and had to upgrade to a 64 bit version which can handle bigger file sizes. Since no 64 bit version was available for the WAMP server, I opted for a separate install of the Mysql Community server 8.0. It now works fine with my bigger databases.

The default install location on Windows is in Program Files (the MSI installer did not offer to install anywhere else), which on my machine is a regular hard drive. To speed things up, I modified the Mysql configuration to store the data files on the SSD, which also works fine. However, importing the same 2GB SQL dump file takes a lot longer than the 32 bit version did before (30 minutes vs 8-10 previously).

The only major differences between the two SQL servers are:

  1. The server version (5.7 vs 8.0)
  2. The architecture (64bit vs 32bit)
  3. The install location (SSD vs regular hard drive)

I am tempted to dismiss 1) and 2), since I could not find any specific related speed issues online.

Thus my question: could it be that although the data files are stored on the SSD, that the server itself being installed on a regular hard drive slows things down? My searches regarding Mysql cache files showed that InnoDB temporary files are stored in the data folder, so that should be okay.

I have a nagging feeling that I'm missing something obvious.

  • How much RAM? Is the entire mysql tree on the SSD? (I probably need more details, too.) – Rick James Jul 18 '18 at 22:51
  • @RickJames, the Mysql server binaries are on the regular hard drive, and the whole data folder (what I assume you meant with the mysql tree?) is on the SSD. The system itself has 16 GB RAM. – AeonOfTime Jul 19 '18 at 6:04
  • Nothing obvious; I can't think of a reason for the setup to be slower; I would expect it to be noticeably faster. Test some IO on the hard drive vs the SSD -- to verify that the SSD is actually faster. – Rick James Jul 19 '18 at 16:17
  • Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf-ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report E) SHOW ENGINE INNODB STATUS; for server tuning analysis. – Wilson Hauck Jul 20 '18 at 18:56
0

As a comparison, I uninstalled the Mysql 8.0 server, and replaced it with a 5.7 64-bit version, with the same setup: Server binaries on the regular hard drive, data files on the SSD. The speed went back to 8-10 minutes to import the SQL files.

I can only assume that the issue is with the v8.0 server, but I cannot speculate as to why. Since I have no particular reason to need the v8.0, I will stay with the v5.7 for now.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.