Since the question was answered, but no real solutions were offered in the form of example configs, I thought that I would offer my two cent worth here:
The issue of the long load time is mitigated by the use of cache. One must be careful not to bring their database server to a crawl by setting unrealistic levels of cache - this isn't something that you just throw a couple of gigs, or even hundreds of MegaBytes at - think about how large your entire database is, and then upon realizing that the size of the query is rather small, proceed accordingly. Be conservative (start small and work your way up, even 10MBytes makes a *HUGE* difference in performance, so don't think you need a 500M cache okay?).
Here's what my /etc/my.cnf.d/server.cnf looks like, for the applicable entries on MariaDB 10.x:
query_cache_size = 16M
query_cache_min_res_unit = 2k
Bear in mind that even 16MBytes for cache may be waaaay more than you need. The "sql_mode" entry isn't required on MariaDB because it's already enabled by default, so it's just there for good measure and to remind me of such.
Now, here's the important part. after you put this into the appropriate my.cnf file for your particular Linux distro or UNIX variant, restart your database server:
In one console window do the following:
tail -f /var/lib/mysql/<hostname>.err
In another console, restart mysqld and watch for errors in the console where you're following the error log:
If everything is good, then let's check:
mysql -uroot -p
MariaDB [(none)]> show status like 'Qcache%';
| Variable_name | Value |
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 16720008 |
| Qcache_hits | 89 |
| Qcache_inserts | 43 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 19 |
| Qcache_total_blocks | 54 |
8 rows in set (0.00 sec)
Kewl!!! Now let's hit your page in a browser.
Yes, the first query is going to be long enough for the page to render that 30 people could have lost interest thinking you're running a boyscout server and left you forever, but then go back to the home page and then hit the dedicated server product page again...
BOOM! Fricken' lightning! Pretty kewl, huh?
Now, this is important, so don't forget this part. During the time when you run your cronjob to update the inventory, the very first time someone hits your dedicated server product page, they're going to have to wait for that 30 seconds or so (One person said over two minutes), so make sure that you also include a curl or wget of that URL *After* the inventory update runs as part of that cronjob.This way, the first time anyone hits the page it will render at breakneck speed.
Not only that, but even with the modest cache settings I've included here as an example, your entire WHMCS install should fly on the second hit of any dynamic page that hasn't been updated or changed after the first HTTP request for it.
You can take this even further if you like, with memcached or redis and/or varnish, etc., but transparent reverse proxies and other concepts of load balancing are beyond the scope of my little tutorial here.
One final note - if you simply cut and paste commands you're going to get lost because, as I said, make sure the commands and file locations are specific to your particular Linux distro or UNIX variant - if you don't know your way around the CLI or understand where your files are and how to start and stop services on your box, then you may want to consider hiring out for the task - you can break it if you don't know what your doing, and although it's an easy fix, you should understand a lot more than just how to point and click in cPanel
I hope that helps, and I'm interested to hear what people have to say about the huge performance boost you're now going to realize.
Bradley D. Thornton
Manager Network Services