I recently installed MySQL and the MySQL workbench on my Windows 10 PC. It’s working great. I loaded up about 2 GB of data in two 3 tables with roughly 2M rows total. Most of my simple (single table by ID or Full Text Index) queries ran really fast, and I was happy.
Then, I started working on queries that join tables. They were slow – like 26 seconds slow for a two table join by integer ID using a FK (simple stuff).
First, I checked my indices. I added several indices just trying things and hoping it’d work. That did not solve the slowness.
I had to tinker in Workbench quite a bit imagining what could be wrong and how do I identify the source problem, which turned out to be good learning exercise.
Eventually, I saw (on the Workbench Server’s dashboard) that InnoDB buffer usage went to 100% during my long running queries. I didn’t really know what that meant, but I was out of “good” ideas to try so I was happy to have any leads.
I asked the internet sages (search engines) about this “InnoDB buffer usage at 100%”, and I found some leads. It turns out that buffer size is set in “my.ini” (on Windows) or “my.cnf” (Not-windows). The default was 8MB, which is amazingly low for any modern computer. (MySQL on my phone would want a bigger value than that as a default.)
I found my.ini at this path on my Windows 10 PC:
C:\programdata\MySQL\MySQL Server 5.7\my.ini
Specifically, edit that file with notepad or notepad++. Look for this value:
I changed mine to 1G first, and now I’m using 2G. I don’t even think that’s a lot, but this is all on my dev-box so I don’t want to give it all of my RAM.
After you change that my.ini value, be sure to restart your MySQL service.
- Type services.msc in to the Run box on Windows to bring up your services manager.
- Find the MySQL service, and right click it.
- There should be a “restart” option, so click that.
After this change, my 26 second simple table join query went to sub-second time.