1

I have installed MySQL 8.0.18 Community with the same configuration (mysql-installer-community-8.0.18.0.msi installer, custom install with server + workbench, standalone server, legacy authentication, rest are the defaults ) on multiple Windows environments with surprisingly varying results.

3 out of 5 of these machines ran MySQL insanely slow when I was inserting a few million lines into the DB

  • Task Manger said that the disk activity time is 100% but barely moved any data (50-500 KB/s for half an hour)
  • I went into MySQL Workbench, noted that the data graphs on the Server Status window are not working,
  • the InnoDB Disk Writes section on the Dashboard says that the writing speed is about 40 KB/s.

Then I went to check the my.ini file and found this nonsense:

innodb_buffer_pool_size=8M

which was the default setting for some odd reason.

The other two machines had this line commented out in the ini file so they were working properly, MySQL was behaving as expected.

The bad environments:

  1. Desktop machine, Windows 10, 16 GB RAM 7th gen i5 CPU with 4 cores. MySQL was never installed on it before.
  2. Physical machine, Windows Server 2016, 44 GB RAM, some beefy Xeon CPU (don't remember the exact model but it was not bad), MySQL was never installed before
  3. VM (on the laptop described in good #2) with 16 GB ram and 6 vCPUs. MySQL was never installed before.

The good environments:

  1. Desktop machine, Windows 10, 16 GB RAM, 4th gen I5 CPU with 4 cores. MySQL 8.0.17 was installed previously, it was working properly. I did a clean install to 8.0.18 and the buffer pool size value was commented out
  2. Laptop, Windows 10, 32 GB RAM, 8th gen I7 CPU with 4 cores. Same story with MySQL.

I could not find anything in the MySQL documentation or on the web describing this behavior. I mean innodb_buffer_pool_size=8M is laughable, I see NO reason why would this be the default value

Does anyone have any idea why this would happen?

  • What's your question? – mustaccio Nov 14 at 23:07
  • 1
    @mustaccio "Does anyone have any idea why this would happen?". I want to make sure that this is a flaw in the installer and not an intended behavior. Also, if someone knows the reason behind it, maybe I can prepare for it in future installations. – digitaltos Nov 14 at 23:22
  • 1
    Sorry, your question was lost in the wall of text; I accept that there was a question. I suggest you conduct a controlled experiment by wiping out all traces of MySQL installation on all those machines, repeating installation, and checking contents of all copies of my.ini immediately after that. Currently I suspect you may have had older copies of MySQL on some machines, whose configuration files did not get replaced by the installation. – mustaccio Nov 14 at 23:28
  • 1
    @mustaccio My bad, I tried to describe this as best as I could. Bad environment #3, the VM was basically a clean Windows 10 install without any previous MySQL installations. The other 2 machines that produce the issue are not available for me unfortunately. I did a clean install on both good environments (checking C:\program files and C:\ProgramData after uninstalling the old MySQL) but I could not reproduce this there. – digitaltos Nov 14 at 23:52
  • @digitaltos For your 5 installed versions, it might be interesting to know what is displayed from each of them for SELECT @@version; Could it be you really have a 5.1 running? – Wilson Hauck Nov 15 at 14:42
1

8M is a very old default for MySQL. Yes, it is laughable. It is much too small for any instance today.

Either that my.ini was left over from some previous installation, or it is worth a bug report.

If you have at least 4GB of RAM, set innodb_buffer_pool_size to about 70% of RAM.

  • 1
    Thanks for your answer. The my.ini was definitely not a leftover, so it is probably the installer that causes this issue. The problem is that I cannot offer the MySQL guys a clear way to reproduce the issue, so I wanted to gather some info before reporting. – digitaltos Nov 14 at 23:20
  • @digitaltos - Even 5.5 had a default of 128M. I think (without proof) that 5.1 still had 8M as the default. – Rick James Nov 15 at 4:10
  • 1
    @rickjames You are correct on the 8M default, here is a URL to the page in the 5.1 reference manual. download.nust.na/pub6/mysql/doc/refman/5.1/en/… – Wilson Hauck Nov 15 at 14:39

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.