The buffer pool is where InnoDB keeps data and indexes in memory. If your working set fits, queries are answered from RAM. If it does not, the same queries read the disk - which is between a hundred and a thousand times slower for the same work.

See what you have

mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS gb"

The default is 128MB. On a server with 8GB of RAM that is the setting leaving the most performance unclaimed of anything on the machine.

How big your data actually is

SELECT ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS gb\n  FROM information_schema.tables WHERE engine = 'InnoDB';

Choose the number

  • Dedicated database server - 60 to 70% of RAM.
  • Shared with PHP and the web server - 25 to 40%, and watch for swapping.
  • Data smaller than that - size it to the data plus a little; more is wasted.
# /etc/mysql/mysql.conf.d/mysqld.cnf\ninnodb_buffer_pool_size = 4G\ninnodb_buffer_pool_instances = 4      # one per GB, up to 8\ninnodb_log_file_size = 512M

Check whether it is big enough

mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%'"

Divide reads that had to touch the disk by total read requests. Under about 1% is healthy; consistently higher means the pool is too small for the working set.

Do not size it so the machine swaps. A database swapping is slower than a database with a small buffer pool, by a lot - see RAM, swap and when swap is a symptom.
Before buying more memory, check that the queries are sane. One missing index can make a working set look ten times larger than it is - see when to add an index.