CODEWITHSUNDEEP
mysqldatabaseinnodb
Ankit Gupta
Ankit Gupta

Reputation: 760

Buffer Pool Extension for MySQL

The main memory on the host is limited and hence the Bufferpool size for the mysql engine. Wondering whether any known solution for the MYSQL which allows the buffer pool extension to a nonvolatile random access memory (that is, solid-state drive or EBS volume in case of AWS infra) extension to the Database Engine.

[EDIT] I am able to find very old doc for sql server but didn't find anything on mysql. I am looking for any existing solution for mysql engine/innodb or good reference points for how to add that feature.

Reference

SQL Server doc

Upvotes: 0

Views: 309

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562651

MySQL does not have a feature like Microsoft SQL Server's buffer pool extension.

Most operating systems allow RAM to be extended by disk storage. It's called swap space. The idea is that if processes use more virtual memory than fits in the physical RAM of the system, some amount of storage space can be used.

The problem is, even the best SSD drive is about 10,000 times slower than RAM. If you rely on swap space on an SSD to supplement MySQL's buffer pool, then MySQL will be so slow as to be unusable.

At my work, when we had swap space configured on Linux servers, and MySQL would overallocate virtual memory and use some of the swap space, it caused the whole server to become so slow from frequent disk access that we almost could not ssh to the server to shut it down. So we disabled the swap space. It's better to fail completely than to grind to a halt without being able to recover.

That was for direct-attached SSD storage on physical servers in our datacenter. It would be much worse for an AWS EBS volume, because EBS is network-attached storage with higher latency.

You don't necessarily need as much buffer pool as the size of your entire dataset. By default, we choose a ratio of 1:10 for buffer pool to storage. For example, 10GB buffer pool is usually sufficient for 100GB data on disk. At least as a default starting place. Some workloads need more buffer pool, so we might adjust it later.

If your MySQL instance needs more buffer pool, then you really need to get a server with more physical RAM. There is no substitute that works.

This will cost more to get a server with more RAM.

If the application doesn't generate enough business value to pay for the server scale it needs, then it is not a viable application.

Upvotes: 3

Related Questions