Website traffic has risen sharply now the basic idea of our technology group is to add the cache layer to all the PHP and MySQL decoupling, such as member registration to write to the server memory and then unify the time to write to the database in the middle of the cache layer will be used to what technology is not contact with this again
Reply content:
If you think MySQL read and write is a bottleneck (MySQL query and write time is very long, or magnitude larger), can indeed introduce intermediate cache.
To build a cache between MySQL and PHP, it is recommended to use Redis directly as the intermediate cache service.
However, it is necessary to analyze why MySQL read and write time is very long, to be able to really solve the problem of your system, you can turn on the slow query log analysis of MySQL.
Read and write if there is a problem, you can try these aspects in the past:
(1) SQL statement is not reasonable, with too many table joins and other operations?
(2) Table is too many records, good millions of of the data, whether it is necessary to sub-database sub-table or partition?
(3) If a suitable index is used, does the SQL query statement take full advantage of the indexed field?
(4) The MySQL configuration parameters can be adjusted to optimize the performance of MySQL read and write, such as increasing innodb_buffer_pool_size, increase the cache hit rate, and so on.
(5) MySQL if it is a single deployment, you can consider the master-slave separation, or set up the master Master mutual preparation.
... ...
It is not too late to introduce Redis as the intermediate cache if it is still a bottleneck after MySQL has been optimized.
In fact, what I wrote above is just the tip of the iceberg, and there are great Doha places to optimize.
An article written earlier:
Hundreds of millions of web systems built-standalone to distributed cluster
Optimization is not difficult, but needs the remedy.
Main, your problem description, not to say symptoms, not to investigate the cause, jump directly to the formulation of the treatment plan: "All PHP and MySQL decoupling ...", this is really good? I'm here to pay attention to the problem, but ... Most of the memory caches are now optimized for reading ... A solution to write optimization???
I'm actually here to pay attention to the problem. We should first find out the crux of the problem, and should not blindly optimize.
Common writes cause slow system speed, which is often a count of such frequent writes, such as: every visit to a page, PV field +1, frequent write causes the query cache of MySQL is immediately invalid, equivalent to not play a role. This situation is suitable for a buffer (write very frequent, no data consistency requirements) to store the new PV, when the buffer is full, and then write once.
You said, "
all PHP decoupled from MySQL into the cache layer"is not very good, in fact, most of the situation your so-called cache layer is just the MySQL query caching function to waste their own takeover, the significance is not significant. And like the registration of this important data is not suitable for caching, once the cache is lost, it is more troublesome. You said this middle-tier is called queue, you can use the Redis list to do the best or first to optimize the read and write MySQL business logic, membership registration This function needs to add a cache, that run up more slow cache hang you this data don't? It's not a joke.
Cache optimization is read, write data do not go this astray. 1. Caching
Plan the change frequency and read and write frequency of the target data.
Read/write Frequency: The larger the change frequency value, the better the cache is; don't do it for a small value.
2. Optimizing SQL
Most of the primary systems do not have a high level of SQL optimization ...
The use of indexes, whether a single query is too large, or a query replaced by a single table query
MyISAM replaced by the innodb of this kind ...
3. Optimize the system architecture
Is there too much content involved in a single page, and is there any unnecessary data presentation?
Can you separate many functions from the system into different business modules?
4. Static page
Static pages naturally consume much less than dynamic pages.
5. Delayed read of cached data
Write queues, cache staging data (such as counters) belong to this
6. Get Data asynchronously
7. Distributed architecture
Database read and write separation, multiple database servers and even many PHP server reading this, all read and say:
Edited in 2014-05-07 sometimes the bottleneck is not necessarily in the database, first to figure out whether the front-end or back-end issues. If the front-end will do a variety of optimization processing, including caching static files, merging and compressing CSS, JS and other files, minimize the HTTP request and so on. If it is the back-end database read and write performance problems first do the next profiling find out where the bottleneck. If you really want to add a cache layer, it is recommended to use Redis to support data persistence. In addition, Message Queuing can be considered for asynchronous processing of communication to achieve the purpose of program decoupling. Message Queuing can handle sudden spikes in traffic, personally recommending RABBITMQ, and our company is also using it. There is a separate full-text search engine can be used to reduce the search pressure of the database, SOLR, Elasticsearch are easy to use, I use the SOLR.
