use MySQL server as "cache"

Question

I know this is a weird question but I have 2 servers, one a vps and another, a mysql host. Now my issue is that I want to try and lower the load on the vps as it is a startup 128mb vps.

Slight problem, what would be required to make the MySQL server work as if it were a cache, I have no access to anything on the MySQL server save the databases im given.

Will this require a separate database? please tell me so if it is possible i can ask my host to add another database.

Thanks!

@wyzard Of the page data being read. so this would perhaps include raw php code — user115422
– user115422, Commented Nov 17, 2012 at 2:41

Botond Balázs · Accepted Answer · 2012-11-17 03:30:23Z

2

If you host a website on the VPS that uses MySQL as the database, a typical request goes like this:

the client makes a request to the web server running on your VPS
your script running inside the web server makes a database query
the database returns the query results
your script formats and prints the results
the results appear in the browser (client)

There is no way to make a request hit the database without going through the web server first.

If you want to lighten the load on the VPS you can make your script run faster. The most time-consuming part of most web applications is making database queries and waiting for the results. You can, in a lot of cases, avoid that by caching the results of your database queries on the VPS using an in-memory cache like memcached. Be careful with the memory settings though - be sure to set the maximum allocated memory to a sufficiently low setting because your server has very little memory.

Here is a basic example of caching the result of a SELECT query:

$cache = new Memcached();
$cache->addServer('localhost', 11211);

$article_key = "article[$article_id]";
$article = $cache->get($article_key);

if ($article === FALSE) {
    $statement = $conn->prepare("SELECT * FROM articles WHERE id = :id");
    $statement->exec(array('id' => $article_id));
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);
    if (count($result) === 0) {
        // return a 404 status
    }
    $article = $result[0]; 
    $cache->set($article_key, $article);
}

// display the article

edited Nov 17, 2012 at 3:30

answered Nov 17, 2012 at 2:43

Botond Balázs

2,5001 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

user115422 Over a year ago

is there any minimum for memcached memory? i can only afford 10-20mb tops

user115422 Over a year ago

and is memcached the best option?

Botond Balázs Over a year ago

I don't think there is a minimum. Memcached is a pretty good and time-tested cache, it is a very reliable solution. Please tell me what kind of web application you are hosting. What database operations are the most frequent (is it read or write-heavy)? How many images and other static assets are you serving? That way I can give you more specific advice.

user115422 Over a year ago

every page refresh there is an insert, an update and on some pages it is several loops of information being fetched, if you want to see it in action, some of the heavy pages would be content (e.g.: pamzam.com/507ee090d02bc) and user profiles (e.g.: pamzam.com/user/muqman) i hope this helps give an idea as to what i need

Botond Balázs Over a year ago

The site loads pretty fast for me. Have you experienced any performance problems? Maybe there isn't even a need to optimize it. What kind of load do you expect (how many requests/second on average)? You can test the site using a web benchmark tool like "ab" (Apache Benchmark). See how many req/s you can make and monitor the memory usage while making the requests. If you don't see anything anomalous, I don't think you should worry about performance.

|

Collectives™ on Stack Overflow

use MySQL server as "cache"

1 Answer 1

22 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related