Let's assume a query like the following as typical:
select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = C.d_id where year(A.`date`) = 2012 and B.x = 0 and C.y = 1 and D.z = "a" group by `date` ;
The number of records handled here after the join will exceed 5 million - possibly more than 10 million - theoretically no limit.
The respective tables A,B,C,D (all InnoDB) can be considered as tables recording business transactions of the kind you would expect - but if you need further more specific details please ask. The table data and the indexes are sized as follows (in GB):
|data|index| ------------- A|15 |6 | B|2 |1 | C|1 |1 | D|1 |4 |
The server I am referring to is used for Warehouse purposes and hence practically only querying is critical - while write operations are of no big concern.
Is it possible to say something about how much memory would have to be available to handle such a load effectively. Effectively means that all data involved for a query is most likely fitting into the RAM and no swapping, filesorting etc. is necessary to run the query.
I am asking for rule of thumb and if other indicators are necessary for an estimate you're welcome to ask me or simply state them.
For example ... does the RAM have to exceed the
- sum of the involved indexes
- sum of involved indexes + size of tables
- ... + margin for handling a large GROUPing
Currently innodb_buffer_pool_size is set to 15 GB (which as I understood determines the actually used memory for InnoDB) and tmp_table_size is set to 64 MB (which is as I understood essential for GROUP BY performance)