SQLite vs Redis

Thank you for using this with a large site! I don’t have large site benchmarks to offer you, so I would be grateful if you would send me yours, maybe by posting them here.

As for SQLite doing memory caching, my code uses Write-Ahead Logging in SQLite. This provide a mixture of RAM caching and file system use. Using this plugin drives up per-php-fpm-process RAM usage and IOPS. That could get costly in server-provisioning charges.

What’s the “Total cost of operation” including RAM, IOPS, and administrative time / hassle? I hope you’ll contribute some real-world experience to helping understand that.

The plugin’s Statistics panel shows descriptive statistics about time taken for cache operations. I find the 95th percentile stats useful. If you can think of anything else the plugin should measure, please let me know.

It would also be great to hook up the stats measurement in the plugin to monitoring tools like Nagios or Cacti or whatever. I don’t know enough about those tools to specify that interface myself. If somebody who DOES know about that kind of tool will tell me what to implement, I’ll do it.

I created a screenshot of the performance stats. I have nothing to compare it to, but it’s humming along fine so far. It’s not very scientific, but the site doesn’t seem to be slower or faster than with the Redis object cache.

The cache and wall files are around 22MB each (after running for half a day).

Another plus for this plugin is that it also works on the staging copy of my site. The Redis object cache always complains about a foreign drop-in or something; plus, you don’t want to pollute your cache with staging data anyway. This plugin is nicely contained in the current directory tree. In addition, I reckon it’s easier to migrate a site to another server; it will even migrate the cache ?? It just makes things less complicated.

• This reply was modified 1 year, 7 months ago by eriky82.
• This reply was modified 1 year, 7 months ago by eriky82.

Thanks for this information!

And, be careful to put your cache on local SSD / HDD storage, not a remote, shared, or NFS drive. /tmp is a good choice. Explanation about how to do that is here.

Good point. I do use fast SSD memory. In addition, the VPS has 16G of memory, a large part of which is used as disk cache thanks to Linux’s excellent disk caching mechanism. So I’m guessing a frequently accessed SQLite file will be in memory most of the time. If you have enough spare memory, I bet this can be as fast as using Redis.

I think the hidden dot files are safe enough, but it’s good to know we can use something like /tmp or even a memory disk.

Again, thanks for the information.

A question for you, as a large-site administrator: The plugin does not (yet) contain any way to limit the size of the cache to a preset number of MiB. That’s something redis does automatically. Is that a feature I need to add?

I could easily implement a least-recently-written scheme to discard old entries, or a random-choice scheme. Least-recently-used will necessarily be a bit slower because it requires a database update for each cache READ to keep track of time-of-use. That seems wasteful to me.

The current version does age out old cache entries. It removes transients at their time of expiry, and other entries after a configurable number of hours.

My site doesn’t have an insane amount of posts. The cache is 26MB right now (that’s the largest I’ve seen it grow so far). I think sites with many pages and posts would eventually hit memory limits if you don’t limit the cache size.

So yes, perhaps such a feature would be desirable. Ideally you’d have a choice between LRU eviction and random eviction ??

On the other hand: if your site is that large, maybe you’re better off using Redis. So you could opt to keep this plugin plain and simple and put up a warning somewhere about the cache size being unbounded.

I’ve been using the latest version for a few days now. Seems to work fine.

I set the cache to a max of 20MB. The file size grows to 28MB. The “All Groups” item size grows to 19.385 MB (30,002 items). So it seems you’re looking at the items size and not the file size, correct?

Yes, the size of the cache keys plus values. The size of the actual cache file will be larger, because of

1. the index on item timestamps.
2. any performance measurements (which go into the same SQLite database file).

The plugin VACUUMs the database whenever it purges older entries to keep the keys+values size under the target maximum size.

Thanks for the feedback.

• This reply was modified 1 year, 6 months ago by OllieJones.