WHMCS keeps getting slower as the number of tickets increases

[stormy]

My WHMCS install has been getting slower over the past few months. Specifically, submitting a ticket reply from the admin could take anything from 5 to 20 seconds.

 

I have a well tuned, dedicated server with hardly any load at all for my WHMCS install.

 

Here's all I tried (nothing helped):

 

• Upgraded from v5.1.x to v6.3.x.
  
• Upgraded from PHP 5.4 to 5.6.
  
• Upgraded from MySQL 5.5 to 5.6.
  
• Upgraded from MySQL to MariaDB.
  

After some consulting with support, I trimmed the number of tickets on my dev install, deleting older tickets. The number of tickets went from 25,000 to 14,000. I tried submitting a ticket reply, and it was instant.

 

My conclusions:

 

• There's probably threshold number of tickets where performance takes a heavy hit.
  
• There should be some kind of archival system for WHMCS. I can keep backups of my database, but this is useful information that should be accessible without slowing down the whole system.
  

Have you experienced this problem as well?

Do you have some kind of archival system for old WHMCS data?

[stormy]

Sharing my fix (adjust your dates accordingly). Run these SQL queries in phpMyAdmin:

 

DELETE FROM  `tbltickets` WHERE  `lastreply` <=  '2012-12-31 00:00:00'
DELETE FROM  `tblticketreplies` WHERE  `date` <=  '2012-12-31 00:00:00'

[twhiting9275]

The problem with this is you lose those tickets and replies. that's not a terribly good solution, honestly.

 

Software itself isn't going to affect performance based on quantity of tables in MySQL.

The biggest performance hit is going to be you, your server, your configuration, and age of hardware.

Removing 11,000 tickets will, of course result in faster results, because there's less to deal with. That's a given.

 

You need to consult with a server / db administrator. Optimize your tables, optimize your MySQL (mariadb) configuration, optimize your server configuration. That's the only way this is going to get better. Otherwise, eventually, you'll just run into this again.

[stormy]

I am a sysadmin myself, and I've consulted this matter with several sysadmins with more experience than me. The dedicated server is absolutely fine, and it's in fact over spec'd. Intel Xeon E3-1230 @ 3.30GHz, 16 GB of RAM, SSD RAID. MariaDB is absolutely optimized.

 

WHMCS suggested that removing tickets would speed it up, so I tried it.

[twhiting9275]

If you're seeing tables hanging as you grow the db, then Maria isn't optimized.

[stormy]

I'm not. No slow queries either. It's the software itself that gets slow. Still can't figure out why.

[Si]

Yea, I'm the same. I've updated to version 7.0 and I've noticed my admin area is taking 10-20 seconds to load any pages. Nightmare to work with and yet I'm on my own dedicated server with everything optimised (over optimised beyond the minimum needed). Optimised MariaDB and no db errors.

[WHMCS Edward]

Have you tried opening a ticket with our support team? If so, please PM me your ticket number and I'll ask one of our more senior engineers to take a look at it for you.

 

-Eddy

[WHMCS John]

Hi,

Admin area page load times sounds like a different matter to time taking to save a new response to the database. The former can potentially be improved by disabling the "Sidebar Statistics" permission under Setup > Staff Management > Administrator Roles > Edit.

[dgbaker]

We are running into the same issue with ticket open and replies and with invoice capturing. We've tried optimizing tables etc... without luck. This started just 2 days ago out of the blue. We are on 6.3.1 still.

[stormy]

I'm on 6.3.1 as well. However, the news aren't as good as my initial report.

 

My dev install, which has a slightly lower amount of tickets, runs smoothly after pruning the old tickets. It went from 20 seconds to instant ticket reply submission.

 

However, the production install doesn't. It improved only marginally. Either there's a number of tickets that acts as a tipping point, or something else is at play. I don't want to keep deleting tickets to see if it improves though.

[dgbaker]

For you is it only with tickets? We see the issue with slowness on credit card capture as well. The capture itself is quick it's the whmcs update and redirect after capture that is slow.

[stormy]

For you is it only with tickets? We see the issue with slowness on credit card capture as well. The capture itself is quick it's the whmcs update and redirect after capture that is slow.

 

Our payment gateway is external, so we don't capture credit cards. We too have long suspected of slowness in the callback part, but can't easily test it.

[dgbaker]

Well figured one thing, when we use the mobile app aWHMCS there is zero lag replying to tickets. I suspect something is going screwing with possibly the htaccess file and redirects.

[stormy]

The mobile app works via API, so it's a totally different way of using WHMCS.

[dgbaker]

I have it resolved for us. Do you have IPv6 enabled on the server? We did and once we disabled it whmcs works perfectly for replies etc...

 

Here's how to disable the protocol on a Red Hat-based system:

 

Open a terminal window.

Change to the root user.

Issue the command sysctl -w net.ipv6.conf.all.disable_ipv6=1

Issue the command sysctl -w net.ipv6.conf.default.disable_ipv6=1

 

To re-enable IPv6, issue the following commands:

 

sysctl -w net.ipv6.conf.all.disable_ipv6=0

 

sysctl -w net.ipv6.conf.default.disable_ipv6=0