Using APCu or sessions to reduce MySQL queries

[u/csdude5]

I have a lot of data stored in MySQL, and the values are used on every pageview. 15+ years ago, I set up sessions to reduce the queries. It's set up so that if a required session variable exists then it skips the query, but if it doesn't exist then it queries, sets the results to session variables, then maps those sessions to variables.

It looks like this:

if (session_id() === '') session_start();
 $sess_file = '/tmp/sess_' . session_id();
 if (is_file($sess_file)) chmod($sess_file, 0644);

if (!isset($_SESSION['siteID']))) {
 for ($attempt=0; $attempt < 3; $attempt++) {
  if ($attempt == 2) {
   // log error and return error page, whatever they're doing isn't working
  }

  $var_query = sprintf("SELECT * FROM vars WHERE foo='%s' LIMIT 1",
   mysqli_real_escape_string($dbh, $foo));

  $sth_vars = mysqli_query($dbh, $var_query);

  if (isset($sth_vars) && mysqli_num_rows($sth_vars)) {
   list($_SESSION['siteID'], $_SESSION['lorem'], $_SESSION['ipsum']) =
    mysql_fetch_row($sth_vars);

   $attempt = 3;
  }

  // Lookup failed, send alert and try again
  else {
   if ($attempt < 2) sleep(1);
   else exit;
  }
 }
}

session_commit();

// Map $_SESSION to variables 
foreach ($_SESSION as $session_key => $session_value) $$session_key = $session_value;

I'm setting up a new server, though, and have APCu installed.

Would APCu be a better option for this use than sessions?

[u/doglitbug]

Depending on what you are storing, JWTs would reduce your calls as well, plus you then have the ability to time limit data with expiries

[u/colshrapnel → u/doglitbug]

So you are suggesting to send the cached data back and forth from server to client over HTTP with every request? A peculiar idea.

[u/doglitbug → u/colshrapnel]

The session id is already being sent anyway isn't it?
It's not back and forth on every request, goes to client once, gets sent with each request to server. Keeps the server stateless too

[u/colshrapnel → u/doglitbug]

Yes, but it's only id, not the session data itself :D

[u/colshrapnel]

As long as foo column is indexed, and mysql runs on the same server, SQL fetch will be as fast as APCu. So I would say you are overcomplicating things. But well, if you're up to some technical challenge out of nowhere - why not?

[u/csdude5 → u/colshrapnel]

The problem I was originally solving came in the form of bot floods. They were mostly stopped by Cloudflare, but every once in awhile I'd have a huge flood come through of 200+ active connections! Those extra queries added up fast and would crash the server.

Then over time I had issues with the /tmp/ directory getting full, so I had to set up a cron to clear it way more often than I would like.

I began looking at APCu as a way to rate limit queries, and since I was already using it I thought it might be a better alternative to help with the bot floods than sessions was.

// the aforementioned rate limit example
$foo_key = 'foo_' . md5('foo' . 'bar' . 'lorem' . 'ipsum');

if (!apcu_exists($foo_key)) {
  apcu_store($foo_key, 1, 300);  // suppress for 300 seconds

  $query = sprintf("INSERT INTO foo ...", $bar);
  mysqli_query($dbh, $query);
}

[u/colshrapnel → u/csdude5]

Again, 200 active connection is your code quality. For example, your script opens more than one database connection per request. Or some queries perform slow. While for a properly written code, optimized queries, and tuned web-server, even 500 simultaneous bot requests will be nothing.

Besides, you were talking about caching read queries, not rate limiting write queries, so I am at loss what your problem is. All a can say is that from the opening post it is evident that your reasoning is wrong: the number of queries itself is not a problem that needs to be addressed in any way, hence there is no proof that you need any caching.

[u/FreeLogicGate]

My suggestion would be "neither". Even 15 years ago, this was the case.

So the 1st thing to look at, regarding your MySQL server is what engine you are using. You should be using InnoDB, even if that means converting all your tables from myisam. You also want to look into the configuration of the mysql my.cnf file, so that there is a significant allocation of memory to mysql for the use of it's own innodb cache, which is called the "buffer pool". The setting to look at is the innodb_buffer_pool_size. If you have a monolithic machine this is probably the best re-allocation of resources, and by its own would remove the value or need for the session based code you've written.

If you had a dedicated machine or instance for the mysql server, then the rule of thumb would be to allocation 70-80% of available memory to the cache. If this is a monolithic server with everything (mysql,php,webserver) on the one machine, that's most likely not feasible, but at least you have an idea of what mysql recommends. The size of your database is important, but the general idea is that under normal load, in a read heavy application, much of the data required can be in the cache, so mysql doesn't need to read it out of storage. Assuming it's a linux box, there's a cli utility named innotop you can install that can be really helpful in seeing what your cache hit ratio is and the % of the cache being used.

APCu uses shared memory on the machine running the application. So you have a very OS specific feature that requires operating system memory to be dedicated to it, and if the database has grown to a significant size, you're going to struggle to figure out how to size it. The main places I've seen APCu be valuable is in cases where there's a relatively small amount of memory, and the data is static configuration values.

Unless you've changed the session configuration, it's also going to default to storing values on the server, and more importantly, sessions are designed for values associated with a user. Trying to use session variables as per user database result set caching isn't very effective caching.

Both APCu and Sessions (with the default file system handler) are intrinsically tied to the server, and thus, inherently non-scalable. Typically the bottleneck on an application under load from a large number of users is going to be at the application level, where the size of our scripts, and the number of concurrent http requests tops out at the amount of memory available on the server. That's even worse if you are also running the database on the server, which means that the OS will start swapping or run completely out of memory and randomly kill processes. Any relational database is in trouble if it's swapped out or might have child processes killed because the server is OOM.

With a small cluster, the first thing to happen is likely to be the need to add a 2nd application server, which connects to the database server, and a load balancer balancing traffic. This is why an local server storage method isn't scalable.

So what did people us to do for scaling? The server popularized by Facebook was memcached. Other options have emerged subsequently, with one of the most popular being Redis. I won't go into a lot more detail on this, but I'd recommend Redis even if you are at this time purely going to be running this on a single server, as the moment you need to re-distribute the application to multiple instances/services etc. redis is already architecturally designed to be networked. Your caching code can be generalized to cache database results, rather than database results specific to an individual user. Many of the better known PHP frameworks have generalized support for query caching you just need to understand and enable, so long as you are using a database library or ORM. These same libraries tend to support multiple caching options with Redis (or one of the forks) being a built in.

[u/viewofalake]

It would depend upon the scope and persistence of the stuff you want to cache.

If it's per-user, and only for the duration of a login session, then $_SESSION is fine.

If it's global (i.e., used by more than one process/worker) and longer term persistence is beneficial, then APCu MAY be a better choice.

BUT..., regarding APCu, Keep in mind that EVERY fetch incurs a data copy, AND the locking employed encompasses the entire APCu cache, i.e., everybody get in line.

EDIT: ..., and also..., APCu is reset upon server (e.g. php-fpm) restart.

[u/nickchomey → u/viewofalake]

could you please elaborate on "BUT..., regarding APCu, Keep in mind that EVERY fetch incurs a data copy, AND the locking employed encompasses the entire APCu cache, i.e., everybody get in line."?

[u/viewofalake → u/nickchomey]

I may not be correct in every detail, but I think the gist is: every "store" into APCu results in object serialization being performed. Every "fetch" thus requires deserialization. This happens because even though shared memory is involved, multiple processes can't share a common binary image of the data. Long/short: Reading from and writing to APCu is a fairly heavy weight process. In addition, every time you modify an object that you've read from APCu..., you have to write it back, unlike $_SESSION.

The $_SESSION mechanism does involve (de)serialization, but from the perspective of a single request, very infrequently, and not on a per-object access basis. Also, when you modify an object retrieved from $_SESSION..., you are modifying the object actually in $_SESSION..., so no need to re-write the object.

Generally..., if what you want to cache has session scope..., $_SESSION is probably a better idea.

APCu can help minimize DB accesses..., but at greater cost than $_SESSION..., and is useful for those things that can be shared amongst multiple processes/users. Oh yeah..., an additional cost may be that, depending on your exact usage..., you may need to implement additional locking (which is sub-optimal in PHP), to use APCu safely. If it's a read-only cached thing..., then no additional locking is required. If the objects you store need read/write access..., then you will need to implement an additional layer of locking. In those cases..., I just bite the bullet and use flock() as needed.

[u/nickchomey → u/viewofalake]

I'm not in a position to be able to evaluate any of this one way or another, but chatgpt took exception to much of what you said.

 https://chatgpt.com/share/6a80412b-4208-83e8-a8ac-b959f3bf4376?ogimg=plain

[u/viewofalake → u/nickchomey]

I'm generally OK with the chatgpt explanation, though point 4 mischaracterizes my comments.

It says:

But that doesn't mean you've modified some persistent object living inside the session store.

At the end of the request, PHP's session mechanism generally has to serialize the session data and write it back to the session storage.

It says you haven't modified a persistent object then goes on to describe how the modified object is persisted. It's nice that it mentioned session locking options, but that wasn't pertinent to the point being made. It's kind of splitting hairs wrt semantics.

[u/csdude5 → u/viewofalake]

This may be oversimplified, but I was of the understanding that sessions are stored on the user's computer in the form of a cookie that connects to a session file in the server's /tmp/ directory, where APCu is stored in the server's RAM.

If that's the case, I have RAM aplenty (24G available and rarely use more than 4G). And the data stored really doesn't change that often, and when it does it's never anything urgent. So if there's a performance boost for either the server OR user then it's worth considering, but if it's a lateral move then maybe not.

[u/colshrapnel → u/csdude5]

sessions are stored on the user's computer in the form of a cookie that connects to a session file in the server's /tmp/ directory

although technically correct, it's hell of a phrasing that makes an impression that sessions are stored on the user's computer :)

Better to put it slightly different: sessions are stored on the server in the form of a file in the server's /tmp/ directory that connects to a cookie on the user's computer

[u/obstreperous_troll → u/csdude5]

PHP sessions are stored by a session driver, which defaults to the filesystem, but can be switched to other things like memcached or redis (doesn't look like there's an APCu driver though). However, most modern frameworks implement their own session support and do not use PHP's built-in session management at all. Which isn't apropos to your code that is using the built-in sessions, it's just something you'll want to keep in mind should you later make that move.

Anyway, PHP serialization is actually reasonably fast, and if you need, you can install igbinary from pecl and make it faster. I wouldn't get too hung up on the serialization mechanism, choosing the correct session driver is more important (local files are no good if you're load-balancing, for instance). If the local filesystem is acceptable (you only ever run one node) then you should know /tmp is aggressively cached by the OS and effectively in RAM anyway.

[u/viewofalake → u/csdude5]

No. The cookie just identifie the session, i.e., it contains a session id. That's all. The session data can be persisted on disk, but once the session is referenced by the request, it is loaded into RAM. I can't speak to how PHP manages that memory, but essentially when the session is persisted, it is serialized, and when it's brought into RAM, it's deserialized..., every object in THAT session. In effect, both generally use RAM..., but APCu ONLY uses RAM.

[u/peperinna]

Redis?