A small innodb_buffer_pool_size contributed to one customer slow MySQL query. The comparable SQL Server workload ran quickly.
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');Several earlier changes did not help. We found a megabyte-sized allocation and increased it into an appropriate gigabyte range. That workload improved. The story does not establish a repeatable benchmark or universal target size.
The pool shares RAM with the operating system, connections and other MySQL allocations. Size from the working set and total budget. Check paging and read behavior. A fixed percentage is unsuitable for some shared and small hosts.
On Windows, inspect the configuration used by the installed service. Don’t copy the historical Linux path. Modern MySQL supports online resizing. Coordinate runtime and persistent settings for the exact release.
Reference: MySQL online buffer-pool sizing.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
A larger buffer pool is not a universal sizing rule, it is an allocation within the complete host budget.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
