Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

from - http://www.readwriteweb.com/hack/2011/04/you-got-nosql-in-mysql-memcach.php

(correct me if does not make sense)

potential benefits

  1. saving complex query calculation into distributed environment

  2. avoid repeated query and probably benefits mosts when having multiple servers (more than 5 database servers) - query once, stored in memcached and reuse forever

innodb has internal buffer pool size,
does it make sense to have memcached to act as distributor?

however, i think access local RAM is much faster than in distributed environment

any thoughts?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

IMHO the two mechnanisms should be balanced correctly and never treated as mutually exclusive. The reason ???

memcached is well managed memory scheme for storing data using user-defined tags. This removes the need for heavy disk I/O.

InnoDB is an ACID-compliant storage engine that uses MVCC (multiversion concurrency control) to allow heavy transactional reads and writes. Given enough memory, you can store your working dataset all in RAM, eliminating disk I/O.

If you combine them each at full strength, their full strengths will nullify each other benefits at best. I can state with absolute certainly because InnoDB will cache all data and index pages accessed, while memcached will store the same user data.

Memcached will not store the index page information that InnoDB will have in its buffer pool.

Memcached will store only the exact pieces of data needed. InnoDB data pages may possibly contain data from other table rows.

InnoDB makes it easier to retrieve ranges of data better than memcached since memcached must pass through the DB layer as an additional step.

The two products must become like a married couple (Pardon the metaphor, I've been happily married 16 years). Each mate must fine tune their strengths to accommodate the other and strike up an ideal balance on everything. So, it is with InnoDB (strong wife) and Memcached (strong husband).

InnoDB can allocate as much RAM as needed based on the sum all all data and index pages or 75% of installed RAM, whichever is smaller. Memcached, while taking less upfront memory, may require some form of forecasting to figure out how much memory to prepare to handle.

Also keep in mind that when it comes to heavy reads, the data and index pages in InnoDB will become rather stale in usage (but up-to-date in content) because of accessing parts of RAM outside of the InnoDB buffer pool for the same data that also resides in the InnoDB buffer pool. Thus, writing to memcached, which then riccochets data to InnoDB, will still trigger eventual disk I/O. InnoDB's ability to provide fast reads is taken away by memcached. In the same flow of control, InnoDB is only needed when writing and logging small transactions to provide for crash recovery. (HHHMMMM this does sound like husband (he does things upfront and must acknowledge and love his wife) and wife (bails out husband but keeps his dignity), doesn't it ???)

CONCLUSION

If you properly forecast how much data memcached will be holding, you can scale InnoDB in percentages of memcached's total RAM needed (i.e., if you plan to use 8G of RAM for memcached, work with 6G or less for the InnoDB buffer pool). Combined, their memory usage must be less than total RAM installed. Once this balance is attained, then InnoDB and memcached will fully complement each other.

share|improve this answer
add comment

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.