CODEWITHSUNDEEP
mysqlnginxphp-5.6mysql-5.7
user2094178
user2094178

Reputation: 9444

How does cache work when MySQL is remote?

I have been working on the server migration of a legacy ecommerce application using PHP 5.6.
The switch involved two Dedicated 32 servers from Linode.
One server is for NginX + PHP and the other is for MySQL only.
The legacy application leverages memcached.

After the switch, I can see a heavy internal traffic caused due to private inbound and outbound connections.
So far this element didn't cause any problem on performance.

However, I was under the impression that the queries would be cached on the local machine, and not on the remote.
Because if the query is cached on the remote host, it sill has to transmit the result set over the private network, instead of retrieving from RAM or the local SSD.

Am I assuming this wrong?

It may be that I am missing the point where the private inbound traffic is more beneficial for overall performance when compared to a local cache.

Upvotes: 1

Views: 963

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

MySQL has a feature called the Query Cache, but this caches query result sets in the mysqld server process, not on the client. If you run the exact same query again after the result has been cached in the Query Cache, it will copy the result from the Query Cache and avoid the cost of running the query again. But this will not avoid the time to transfer the result across the network from mysqld to your PHP application.

Also keep in mind that the MySQL Query Cache is being deprecated and retired.

Alternatively, your application may store data from query results in memcached, but typically this would be done by the application code (I know there are UDF's to read and write memcached from MySQL triggers, but this is a bad idea).

If your memcached service is not on the same host as your PHP code, it would result in network transfer twice: Once when querying the data from MySQL the first time, then again transferring the data into memcached, then later every time you fetch the cached data out of memcached.

PHP also has some features to do in-memory caching, such as APCu. I don't have any experience with this, and it's not clear from a brief scan of the documentation where it stores cached data.

PHP is designed to be a "shared nothing" language. Every PHP request has its own data, and data doesn't normally last until the next request. This is why a cache is typically not kept in PHP memory. Applications rely on either memcached or the database itself, because those will hold data longer than a single PHP request.

If you have a fast enough network, it shouldn't be a high cost to fetch items out of a cache over a network. The performance architects at a past job of mine developed this wisdom:

"Remote memory is faster than local storage."

They meant that if the data is in RAM on a server, then reading it from RAM even with the additional overhead of transferring it across a network is usually better than reading the data from persistent (disk) storage on the local host.

Upvotes: 2

Related Questions