Make memcached and MySQL work better

Source:Http://chaoqun.17348.com/2008/08/memcached_work_with_mysql

This is the fotolog experience. It is said that fotolog has deployed 51 memcached instances on 21 servers and cached up to GB of content on 21 servers, this number is much larger than that of databases on many websites. The original Article is a bunch of great strategies for using memcached and MySQL better together. Here I will continue to provide selective translation and supplement based on my understanding, thanks to Todd Hoff, we can always give us some learning cases. From here, we can also see the open attitude of foreign technologies, which is not like us. In fact, we are still waiting for the answer. Well, let's get started.

1. About memcached

Do not know this yet? When you go to the interview, you will suffer a loss. Please go to the official website to check your website (Facebook is in use), eaccelerator, or xcache (developed by Chinese people). These products have better standalone performance, if you need a distributed cache solution, use memcached.

Ii. How does memcached work with MySQL?

• Database sharding is used to solve the problem of database write extension. Database sharding is deployed on different servers, so that there is no primary server, and write operations become bottlenecks and possible "single point of failure ", generally, database sharding mainly divides services according to the business, and splits services as much as possible. Unrelated data is made into services independently.
• Front-end MySQL and a bunch of memcached servers to cope with read ProblemsProgramFirst, get data from memcached, and then get data from the database and save it in memcached. I have read an articleArticleSay that the application gets 95% of the data from memcache, 3% of the data is obtained from MySQL's query cache, and the remaining 2% is used for Table query. How far is the gap between your application?
• Solve the read problem through MySQL replication (master-slave)
  First, the MySQL database uses master-slave read/write splitting and multiple slave operations to handle application read operations.

Iii. Why not use MySQL query cache?

We all know that MySQL has a query cache that can cache the results of the last query, but it does not actually help much. below is the insufficiency of MySQL quety cache:

• Only one instance can be created.
  This means that the upper limit of the content you can store is the available memory on your server. How much memory can a server have? How much can you store?
• MySQL query cache becomes invalid as long as there is a write operation.
  As long as the database content changes slightly, other rows may be changed, and the query cache of MySQL will also become invalid.
• MySQL query cache can only cache database data rows
  This means that no other content is available, such as arrays, such as objects. In theory, memcached can cache any content or even file ^_^.

Iv. fotolog Cache Technology

• Non-deterministic cache: you are not sure whether the data cache you want is in or not, and you do not know if it has expired. So you may tentatively ask memcached, what data do you want? I don't want to get expired data. memcached tells you that you are happy to give it to you. If not, you have to get it from the database or somewhere else, this is a typical application of memcached. Mainly used in:

  1. complex data needs to be read multiple times, and your database performs sharding. It is a huge overhead to obtain and combine data from multiple databases, you can fetch the data and store it in memcached.

  2. a good alternative to MySQL query cache, so that other database departments have changed, as long as they have not changed (pay attention to the database update issue, which will be mentioned later)

  3. cache the relationship or list, such as the list of multiple articles under a topic.

  4. data that is called by multiple pages and is slow to obtain, or data that is slow to update, such as the ranking of articles

  5. If the cache overhead exceeds the overhead for re-retrieval, do not cache it.

  6. Tag Cloud and automatic recommendations (similar to Google sugest)

  For example, if a user uploads an image and the user's friends page lists the image, cache it.

  Potential problems:

  Memcached mainly consumes server memory, which consumes a small amount of CPU. Therefore, fotolog deploys memcached on their application servers (which seems to be the same ), they encountered a CPU usage rate of 90% (How can it be so high? What's wrong?), memory recycling (this is a big problem), and so on.

• Status cache stores the current status of the Application Service in memcached. It is mainly used in:

  1. Expensive operations with high overhead

  2. For sessions Sessions, check that the sessions are stored in the database. It is cheaper to store memcached. If the memecached server is down, log on again.

  3. Record Online User Information (we did the same)

• indeed, all content in some specific databases is cached in memcached. A dedicated application service is provided to ensure that all your data is stored in memcached, other application services directly obtain data from memcached instead of the database, because all databases have been saved to memcached and kept in sync. Mainly used in:

  1. read stretching, all reads are obtained from memcached, and the database has no load

  2. "never expire" (relative) data, such as administrative planning data, changes very small

  3. frequently called content

  4. user Authentication Information

  5. user summary

  6. USER parameter settings

  7. list of commonly used media files, such as images

  8. the user logs on, does not go to the database, and only uses memcached (I personally think this is not very good, the login information still needs to be persistent, and the effect is also good with bdb)

  usage:

  1. multiple specialized cache pools are not a large cache server. Multiple cache pools ensure high availability, and one cache instance is suspended from other cache instances, all the cache instances have crashed and are taken to the database (it is estimated that the database cannot resist ^_^)

  2. all cache pools are maintained by programs. For example, when the database is updated, the program automatically synchronizes the updated content to multiple cache instances.

  3. after the server is restarted, the cache is started first than the website. This means that when the website is started, all the caches are available.

  4. read requests can be load balanced to multiple cache instances, with high performance and high reliability

  potential problems:

  1. you need enough memory to store so much data

  2. data Records Data in rows, while memcached stores data in objects. Your logic is to convert data in rows and columns into cached objects.

  3. it is very troublesome to maintain multiple cache instances. fotolog uses Java/hibernate and they write a client to poll

  4. managing multiple cache instances increases the application overhead, but these overhead is nothing compared with the benefits of multiple caches.

• The Master/Slave cache data appears in the cache magically. When there is an update in the database, the cache is immediately filled, and the updated data is more likely to be called (such as a new article, of course there are many people looking at it), a kind of deformation of non-deterministic cache (the original article is it's non-deterministic caching with a twist. I think this translation is strange ). Mainly used in:

  1. pre-fill cache: Make memcached call MYSQL as little as possible if the content is not displayed.

  2. "push" cache: When you need to copy data across data centers

  Procedure:

  1. parse the binary logs of database updates. memcached is also updated when the database is updated.

  2. Execute user-defined functions, set trigger call UDF update, specific reference http://tangent.org/586/Memcached_Functions_for_MySQL.html

  3. using blackhole strategy, Facebook also uses MySQL's blackhole storage engine to fill the cache, and writes the data written to blackhole to the cache. Facebook uses this to set data expiration and cross-border replication, the advantage is that database replication does not take MySQL, which means there is no binary log and there is not much CPU usage (ah? Does memcached store binary logs and then replicate them to different databases? Experienced comrades can add on this topic .)

• The file system caches the file directly in memcached. Wow, it's BT enough to reduce the burden on NFS. It's estimated that only those popular images will be cached.
• Some page content is cached. If some parts of the page are difficult to obtain, it is better to cache the original data of the page to directly call the content of the page.
• Application-level replication updates the cache through APIS. The API execution details are as follows: 1. an application writes data to a cache instance. This cache instance copies the content to other cache instances (memcached synchronization)

  2. automatically obtain the cache pool address and number of instances

  3. Update multiple cache instances at the same time

  4. If a cache instance goes down, jump to the next instance until the update is successful.

  The entire process is very efficient and cost-effective.

• Other Tips 1. multiple nodes to cope with "single point of failure" 2. when a node is down and another service is automatically replaced with its IP address, the client does not need to update the IP address of memcached.

  3. memcached can be accessed through TCP/UDP, and continuous connections can reduce the load. The system is designed to support 1000 connections at the same time.

  4. Different Application Services and different cache server groups

  5. Check whether your data size matches your allocated cache. For more information, see http://download.tangent.org/talks/Memcached%20Study.pdf.

  6. do not consider data row caching and cache complex objects.

  7. Do not run memcached on your database server. Both are memory-consuming monsters.

  8. Do not be troubled by TCP latency. The local TCP/IP has optimized the memory replication.

  9. Try to process data in parallel

  10. Not all memcached clients are the same. Study the corresponding language carefully (as if PHP and memcached work well together)

  11. Try to expire the data instead of making the data invalid. memcached can set the expiration time.

  12. select a good cache ID key, such as the version number added during the update.

  13. Store the version number in memcached.

I will not translate my last comments. It seems that MySQL proxy is working on a project to automatically synchronize MySQL and memcached. For more information, see