Imagine we have a MYSQL DB that's data size is 500 MB.
If I will set the innodb_buffer_pool_size at 500MB (or more), is it correct to think that all the data will be cached in RAM, and my queries won't touch disk?
Is effective_cache_size in POSTGRESS is the same as MYSQL's buffer_pool and it also can help avoid reading from disc?
Is "effective_cache_size", a parameter to indicate the planner as to what OS is actually doing ?
http://www.cybertec.at/2013/11/effective_cache_size-better-set-it-right/
and for caching the tables, do we not need to configure "shared_buffers" ?
And with regards to MySQL, yes the "innodb_buffer_pool" size will cache the data for Innodb tables and preventing disc reads. Make sure its configured adequate to hold all the data in memory.
I believe you are on the right track in regards to MySQL innoDB tables. But you must remember that when measuring the size of a database, there are two components: data length and index length.
You also have no control over which databases are loaded into memory. If you want to guarantee a particular DB is loaded, then you must make sure the buffer pool is large enough to hold all of them, with some room to spare just in case.
MySQL status variables can then be used to see how the buffer pool is functioning.
I also highly recommend you use the buffer pool load/save variables so that the buffer pool is saved on shutdown and reloaded on startup of the MySQL server. Those variables are available from version 5.6 and up, I believe.
Also, check this out in regards to sizing your buffer pool.
