Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 170GB of InnoDB index and Data.

I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.

So what should be the optimal size of innodb_buffer_pool.

UPDATE

we are going to migrate our this local database to ec2 so will set the RAM according to the current statistics of innodb that's why i need the size of buffer pool so we can have available RAM there.

File Per table is enabled.

I am using Linux machine.

share|improve this question
    
What is the available RAM on the machine not being used by other processes? Are you using Windows, Linux or something else? What version of MySQL are you using? Why is the table size limited to 28GB? –  Craig Efrein Jun 13 '12 at 8:52
    
The table size is not limited..I have given the size of table upto one point of time.it will increase as there will be inserts in future.. –  Abdul Manaf Jun 13 '12 at 12:26

2 Answers 2

up vote 10 down vote accepted

The biggest table you have makes up 16.47% (28/170) of the total data. Even if the table was highly written and highly read, not all 28G of the table is loaded in the buffer pool at one given moment. What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

Here is a more granular way to determine innodb_buffer_pool_size for a new DB Server given the dataset currently loaded in the current DB Server's InnoDB Buffer Pool.

Run the following on your current MySQL Instance (server you are migrating from)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Run the formula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

If IBPPctFull is 95% or more, you should set innodb_buffer_pool_size to 75% of the DB Server's RAM.

If IBPPctFull is less than 95%, run this formula : IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05. The number for IBPSize (in GB) is the number that more closely fits your actual working dataset.

Now, if IBPSize is still too big for the biggest Amazon EC2 RAM Config, use 75% of the RAM for the Amazon EC2 DB Server.

share|improve this answer
    
That's really great information..One more thing i need to know how the MySQL Server loads the data and indexes in buffer pool i have gone through the documentation and got that it uses LRU but how it loads them in memory like if i have 2GB for buffer pool but the table size which is requested in query is much more than that..?? –  Abdul Manaf Jun 13 '12 at 16:23
    
Only the data and index pages needed to satisfy the query are loaded into the buffer pool, not the entire table. Old data and index pages are rotated out based on the LRU algorithm listed in dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html –  RolandoMySQLDBA Jun 13 '12 at 17:27
    
Abdul, you should go with Rolando's answer which does answer your question better than mine. I am going to rewrite my own answer just because I need to be clearer. –  Craig Efrein Jun 14 '12 at 8:35

I am providing this answer as complementary information to Rolando's answer below.

Before the server is in production

Calculate innodb_buffer_pool_size based on the largest tables that are most often used by MySQL. To identity the largest tables based on their size in the database you could use this script:

select table_schema, table_name, round(data_length/1024/1024,2) as size_mb 
from information_schema.tables where table_schema like 'my_database' 
order by size_mb desc;


+--------------+-------------------------+---------+
| table_schema | table_name              | size_mb |
+--------------+-------------------------+---------+
| heavybidder     | user                 |   522.55|
| heavybidder     | bid                  |   121.52|
| heavybidder     | item_for_sale        |    10.52|
| heavybidder     | account_user         |    5.02 |
| heavybidder     | txn_log              |    4.02 |
| heavybidder     | category             |    0.02 |
+--------------+-------------------------+---------+

Now that we know which tables are the largest in our database, we need to determine which ones are the most frequently used. To do that, I would use a profiling program like Jet Profiler (JP) to look at which tables are being accessed the most. JP will show you which tables are being accessed the most frequently. Here is a screenshot from that section in JP

enter image description here

So with this in mind, I now know that the user and bid tables take about around 640MB of disk space, they are very frequently used according to JP and which means that MySQL is going to store their indexes and data in the buffer pool as Rolando mentions below in his comments.

To make sure MySQL had enough memory to store data for my largest and most frequently used tables, I would then define innodb_buffer_pool_size at 640MB.

There are some additional considerations, but they don't apply to the innodb_buffer_pool_size.

Is this a 32Bit or 64bit system? In a 32Bit system, you are limited to 4GB unless you activate PAE. In Windows, this means running Windows Enterprise or Datacenter editions.

How much memory do the other processes running on your system need? On a dedicated MySQL server, I will leave between 5% and 10% for the OS. In Windows you can use Process Explorer to analyze memory usage. In Linux, you have sysstat, free, htop, top and vmstat.

Is the database made up of only Innodb tables or a mixture of Innodb and MyISAM? If it is a mixture of the two, then I will set aside memory for the key_cache, join variables, query cache, etc. You can later calculate your MyISAM hit ratio once the server is in production.

After the server is in production

What is the current hit ratio for Innodb?

1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests).

What is the Key Cache Hit Ratio

1 - (Key_reads/Key_read_requests)

I typically try to get the ratio as close to 100% as possible.

How well do your tables fit in the buffer pool

You can also look at how well your table data fits in your buffer_pool by referring to this link, which provides a way to show "how many pages are in buffer pool for given table (cnt), how many of them are dirty (dirty), and what is the percentage of index fits in memory (fit_pct)." Applies to Percona server only

http://www.mysqlperformanceblog.com/2010/12/09/how-well-does-your-table-fits-in-innodb-buffer-pool/

share|improve this answer
1  
Craig : Great efforts ...thanks!!! –  Abdul Manaf Jun 14 '12 at 9:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.