Reading the settings from the database needs to be cached

[Phorum]

I recently got a query analyzer running for my database machine and I've been looking at the queries from my site over a couple of days. What I noticed is that the query that fetches the stuff from the settings table is eating up a ridiculous amounts of bandwidth between my database and web server. Currently the settings alone is sending 25 times more data then the second biggest (traffic-wise) query, which is the one that fetches forum posts.

I am running a fair amount of modules (almost 30), so the hooks entry is a big chunk of this, and a few "official" addons (spamhurdles, event_logging and such) has added a few lines and I got a handful of custom profile fields. The query to fetch it all comes out as about 25kb of data from mysql, which adds up to a lot, but I don't really think my situation is that unique here, I would imagine anyone would see a significant chunk of their mysql<->web server traffic being the settings table.

Most of the stuff in the settings table are things you would set and forget (why is the default settings fetched on every page request?) and the only ones that is really anything to think about at all before caching would be ''status'', ''hooks'' and the ''mod_'' ones.

Setting all settings up on a short cache TTL, like 5 minutes would help a lot, but even just caching all except the ones above would be a good start, or caching them on different durations. Best of course would be to set all this on a really long TTL and make sure the cache gets expired when the fields gets updated.

Personally, since I run with replication, I would probably opt for the solution to cache it all on a very long TTL and then move the fetch-settings query to a DB_MASTERQUERY to make sure I don't recache old stuff (such as when I disable the forums) due to replication lag.

If the devs are interested I could email some more details and actual numbers about what I've come up with. :)

Reported by: Mathias Imported from TRAC: http://trac.phorum.org/ticket/834

[Phorum]

I thought about this too but in the end I decided against it, as I'm modifying the settings on the fly. Still it could be useful to cache the original (pre-modification ;)) settings this way.

for the default settings ... we had a ticket for them but they are used outside the admin too #211

By: ts77

[Phorum]

oh well, and here is another problem for you ... the cache setting (which cache to use and how) is in that very part that should be cached ^^

By: ts77

[Phorum]

Hmm, yeah, the cache settings bit is a problem, since I won't know where to find it until I read the settings. A bit of a catch-22 there I guess.

But speaking of that. The "what to cache" setting could be assumed to be "on" for settings if caching is on and go look for it. The "how to" cache bit really should be moved to a config file anyway IMO, especially since there now, for memcached, is no way to configure where my memcached server lives. I'd like to see it work more like the DB settings (do you really move around which directory you cache to if you use file-caching?).

If the settings is moved to a file, either together with what to cache or not, then this would work in that regard. I'm not really too concerned with what is loaded or stored in the settings table, I just want to see it cached because when it is the single biggest source of traffic from mysql by a huge margin and something that (under normal production use) probably doesn't change very often (or at all) it's just such a waste of network traffic. :)

Of course it should be added as an option, much like all other caching stuff, but if there is one more thing that should be cached, this is it, in my opinion.

By: Mathias

[Phorum]

By: ts77