Optimizing #MySQL performance on Windows

The MySQL community edition, when installed using default settings is limited to 128MB of memory, which makes it unobtrusive, and won’t hog resources if misused, which is fine. But sometimes you need a blast of performance, to help run queries fast, even if it gets greedy with memory.

Everybody knows that memory is faster than disk, so if you find that MySQL is using 100% (or maxing out), disk usage, and only using 128MB of memory, then you will benefit from giving MySQL more access to available memory. Lets say your desktop machine is 16GB, you can easily give it 10GB (10G) of memory without affecting system stability. Ypu can see all of this in task manager.

So, assuming you’re using Windows, you need to open notepad (or another text editor) as Adminsistrator and then open the file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Find the part that says

innodb_buffer_pool_size=….

and change it to

innodb_buffer_pool_size=10G

Then stop and start the MySQL service (Using Services.msc).

As you start running heavy queries, you should see the memory usage of MySQLd go up, and the disk usage go down.