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.

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)

(MySQL 5.6)

share|improve this question
    
index size equal or larger then the data it self for table C and D?? looks like you indexed every column within your table? –  Raymond Nijland Nov 5 '13 at 21:12
    
@RaymondNijland That is not so strange. –  ypercube Nov 5 '13 at 21:19
    
@ypercube true indeed not for InnoDB engine but still we have to little information about table structure.. –  Raymond Nijland Nov 5 '13 at 21:23
    
@RaymondNijland: no, certainly the minority of the columns is indexed. but it is conceivable that ill-chosen indexing causes bloating. but for this question this is not central. if a data/index ratio of 1:4 seems unhealthy then just assume improvement and a 4:1 ration with data simply 16 times as large. –  Raffael Nov 5 '13 at 21:31
    
@RaymondNijland: what further information do you think you need? –  Raffael Nov 5 '13 at 21:32
show 3 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.