Should I change thesql platform for higher innodb_buffer_pool_size

backupdatabase-design

Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should I move database from Xampp to Workbench? Then I could set maybe 10G buffer.

So, here is my plan:

  1. use mysqldump to create a backup file.
  2. uninstall Xampp totally (otherwise MySQL will install in Xampp directory again, I tried before)
  3. install new MySQL and workbench.
  4. recover the dump file into new platform
  5. set innodb buffer pool size to >10G.

Is this practical?

Best Answer

With a 160 GB database, it does not sound like a 10 GB cache is overkill, but it depends to some extent on the size of the active data as well.

I suggest that you look at Rolando's post: How large should be mysql innodb_buffer_pool_size?

In the particular case he discusses, there was too much memory allocated for the cache. However, he offers considerable detail on what to measure and how to refine the size of the cache.

Some code snippets from Rolando's post:

Compute the Cache Size:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

After you reset the buffer size and restart the server, give it some time, then run this to see how usage is holding up:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;
Related Question