WHMCS Inventory module slow loading
Problem reported by David Baker - January 27 at 12:03 PM
Not A Problem
We've implemented the new WHMCS inventory module but the client pages for the dedicated servers take forever to load. We've tried splitting into different groups to lessen the amount of server inventory but still pages take more than 30 seconds to load. Other pages that are using normal shared hosting products load quickly so we now it has something to do with the Inventory API.
Any ideas as to why they are loading so slow or thoughts on correcting it?

2 Replies

Reply to Thread
Radic D. Replied
January 30 at 6:32 PM
Employee Post
This is not an issue with the module.
This is a known issue with WHMCS when there are a lot of products and configurable options for those products. There will be 1 very slow load after the sync process completes, but once the page loads once, everything will be cached and the load times should be instant thereafter. 
Bradley D. Thornton Replied
March 23 at 3:18 PM
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:
/etc/rc.d/rc.mysqld restart
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
Thank you for choosing NorthTech!
Business Internet, IT, and Telephony since 1985.

Reply to Thread