Hi haifa;
There is no formula that will give you the proper innodb_buffer_pool_size in all cases.
If the server is dedicated to MySQL (i.e. no other major applications/services running on it like a web server), then it is generally recommended to set your innodb_buffer_pool_size to about 75-80% of the available memory on the server (the extra 20-25% goes to the rest of MySQL and system overhead). The exact percentage you use would depend on your system, so you would just need to set it and watch for memory contention / swapping. I typically start at 75% and then move it up a little if that proves to be stable over time.
If the server is not dedicated to MySQL, or you simply want to keep the extra memory free, then using the query you listed will give a reasonable value to start with for your innodb_buffer_pool_size. If you do use the query method, then just keep in mind that you will want to review it over time and make adjustments as your data size grows.
Now to answer the question of “is X innodb_buffer_pool_size large enough for my server”, that is more complex. You could have 1TB of data, but only access 1GB of it regularly, in which case you would only need a couple gigs in the buffer pool. Or you could have 20GB of data, and constantly access all of it, in which case you’d want as large a buffer pool as possible to cover the data + index size.
To see how well your server is using the InnoDB buffer pool, you can check a few different areas. Run “show engine innodb status \G” and check to see if you have any free buffers. If you consistently have a large percentage of free buffers over time compared to the buffer pool size, then the buffer pool might be too large. Then in the same “show engine innodb status \G” output, look for “evicted without access X.XX/s”. If that value is anything but 0, then your innodb_buffer_pool size is probably too small (basically it means data is getting loaded into the buffer pool but is never accessed again before it gets pushed out to make room for new data - a.k.a. buffer pool churn).
Aside from that, to get an idea of how often MySQL cannot satisfy a read request from the InnoDB buffer pool and has to go to disk, you can use this calculation: (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100. These two values come from “show global status”, and will give you a percentage of reads that end up going to disk. So if you have a high percentage here, then that means your InnoDB buffer pool is not doing as much as it could be.
As you can see, this can be (and is) a complex topic. However the basic point is simple; you need (or want) an innodb_buffer_pool_size big enough to fit all of your commonly accessed data plus indexes. If you do not have enough memory for that, then you will have churn. So the more memory you can spare for MySQL, the better. =)
-Scott