Mysql – Why is MySQL caching data on our dynamic PHP site

cacheMySQL

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that have been completely removed from the database, yet they do not show updated records or even missing data.

If we have the patience of waiting several (undefined) hours, the data will eventually show up. I've added the regular no-cache headers via PHP on all of the pages, but it doesn't seem to affect the MySQL output.

The PHP portion of the site is instantly updateable though, and shows HTML etc changes immediately. Is there a way to force MySQL to only draw fresh data from its queries? I thought this was the way it worked anyways!

Thanks,
Chris

P.S. Here are the php codes I'm using to try to force a no cache situation:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // Always modified
header("Cache-Control: private, no-store, no-cache, must-revalidate"); // HTTP/1.1 
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0

Best Answer

You could do SELECT SQL_NO_CACHE. This is used by mysqldump to always dump fresh data to a text file. This may also help flush the innodb buffer pool if the table being SELECTed is InnoDB.

If you cannot maniuplate the SELECT statements, then set query_cache_type to 0. That will make all SELECTs behave like SELECT SQL_NO_CACHE. You can do this without restarting mysql:

Step 1) Add this to the /etc/my.cnf

[mysqld]
query_cache_type=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;

FINAL WORD

Doing this may increase read I/O. You may also want to consider setting innodb_max_dirty_pages_pct = 0 to keey the innodb buffer pool with the freshest data that is as fully flushed to disk as possible.

Step 1) Add this to the /etc/my.cnf

[mysqld]
query_cache_type=0
innodb_max_dirty_pages_pct=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;

UPDATE 2011-05-20 11:17

It is possible all the DB Connections have timed out from PHP's vantage point.

See if restarting MySQL and apache will do it.

Also, make sure that the timeout values for MySQL, PHP, and Apache all match up

Also, do this 

netstat | grep [m3][y3][s0][q6] | grep TIME_WAIT

This helps see if any dead DB Connections have not been release by the OS

Related Question