Rumors of ReliableSite.net WHMCS module's order pages taking minutes to load is grossly exagerated..

[tallship]

Hey there everyone,

 

I stumbled across a thread over at ReliableSite's forum concerning their dedicated server module taking forever to load. I had some discussions with a few WHMCS operators who had tried the module after setting themselves up as resellers but were disappointed that the order pages took forever to load.

 

I didn't experience this myself, but then I've been running UNIX machines and database servers since dirt was invented, so when someone reached out to me with their woes on the matter, the first place I went and looked was at their database config. Over the years I've come to realize that well over 90% of people just turn MySQL, MariaDB, or PostgreSQL on and figure that's all they need to do to rock Gibralter with speed and elegance where their dynamic, database driven sites are concerned.

 

We've all run across those people with a kazillion WordPress modules installed and chuckled at those excrutiating load times, before telling the customer to just, "remove all of the modules that you aren't using!"

 

Well, that's certainly true but there's things under the hood of your UNIX machine that you can do to alleviate many of the issues that even serial module installers experience.

 

Back to ReliableSite's WHMCS Dedicated Server Module. I did witness this little puppy taking forever to load order pages on someone's install and assured them that there's a simple fix that they should have implemented long before they ever installed any software on their webservers.

 

So without further ado, here's a reprint of that post I made on their forums, in the form of a cookbook tutorial for those who don't wish to simply visit the original post for the particulars:

http://support.reliablesite.net/community/a47/whmcs-inventory-module-slow-loading.aspx#137

 

<snip>

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:

 

 

sql_mode=""
query_cache_size = 16M
query_cache_type=1
query_cache_limit=1M
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.

 

Enjoy,

 

 

</snip>

 

Well like I said in the post in their support forums, I hope that helps!