MySQL Bugs: #91081: Absurd write amplification loading sql dumps with low buffer pool size

Bug #91081	Absurd write amplification loading sql dumps with low buffer pool size
Submitted:	30 May 2018 22:14	Modified:	9 Nov 2020 22:23
Reporter:	Karl Johansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S4 (Feature request)
Version:	8.0.11	OS:	Windows
Assigned to:		CPU Architecture:	x86

Description:
When installing MySQL with the installer on Windows, unless you're really careful and not select "developer machine" (which is the default) the installer will set the buffer pool size to 8 mb, even though the default as specified in the manual is 128 mb. Without thinking about this, I then went ahead and loaded a 10 GB dump file which I had exported from the machine being upgraded.

When I realized my mistake and increased the buffer pool size (thanks for letting us do this without restarting!) the insert speed increased by over 10x and the write amplification decreased. Interestingly though at this point the MySQL process had written over 500 GB of data according to the windows task manager yet the size of the datadir was only 3 gb.

I can add that I've installed MySQL probably hundreds of times and still I make this mistake sometimes. Also the experience for people who don't understand this will be severely degraded and might negatively affect MySQL's reputation.

How to repeat:
Install MySQL using the installer with the "developer machine" preset and then load a large SQL dump

Suggested fix:
Increase default buffer pool size to 128gb for the default installation so it matches what is specified in the manual

Hi Mr. Johansson,

Thank you for your feature request.

I think that itt it would be a useful feature.

Verified as reported.

Posted by developer:
 
Fixed as of the upcoming MySQL Installer 1.4.36 release, and here's the proposed changelog entry from the documentation team:

Defaults set by MySQL Installer for the innodb_buffer_pool_size,
table_open_cache, and max_allowed_packet variables were resized to align
with the server default values.

Thank you for the bug report.