CORE-12594 Slow Database after upgrading to 7.6

[Chad]

I just upgraded to 7.6 now and everything now loads very slowly, even using default Blend theme.

This is not normal. I emptied template cache, optimized database via phpMyAdmin. Same thing.

[dnerty]

That's my experience as well. After upgrading to 7.6 whmcs is terribly slow

Edited August 4, 2018 by WHMCS ChrisD
Removed boxes and codes

[dnerty]

Try to change, in configuration.php

$disable_admin_ticket_page_counts = true;

That helps.

 

[Chad]

You mean it should look like this?

 $disable_admin_ticket_page_counts = 'true;'

Anyway, both formats didn't do it for me.

Edited August 1, 2018 by Chad

[dnerty]

Open configuration.php in whmcs home folder

Try with

$disable_admin_ticket_page_counts = true;

Save it

That helped me. WHMCS is as fast as before.

Hope will help you.

 

 

[Miron]

Same issue here with v7.6 and adding this into config file solved slowness.

[Chad]

Didn't solve for me. Only happens on admin side, not client side. Takes 2 seconds to load pages.

[WHMCS John]

Hi there,

I'm sorry to hear you're experiencing slower performance after applying the 7.6 update.

Could you please enable the MySQL slow query log, reproduce the error, and share anything that gets logged?

[CSSHG]

Hi Guys,

Yes, we are seeing the same issue. I have logged a ticket with support.

Hopefully, they can shed some light on this problem.

[Chad]

No help from support in my case. They deny the slowness despite the obvious that others replicated this and it happened immediately after upgrading to 7.6

The server I'm running on has 2x Hex-Core with 72GB RAM, SSD and Mariadb 10.2. Sure isn't a server or hardware issue at all. It's their bugs.

[yggdrasil]

41 minutes ago, Chad said:

No help from support in my case. They deny the slowness despite the obvious that others replicated this and it happened immediately after upgrading to 7.6

The server I'm running on has 2x Hex-Core with 72GB RAM, SSD and Mariadb 10.2. Sure isn't a server or hardware issue at all. It's their bugs.

Is this the admin side, the client side or both? Are there any errors on your web server logs?

[JonOnTheWeb]

Having the same issue here, only the WHMCS admin is slow. We added the line in the config file "$disable_admin_ticket_page_counts = true;" which improved the speed a bit so it is more usable but still not great. We have a ticket open with WHMCS and suggest everyone does the same. Hopefully, a fix can be pushed out soon!

[WHMCS Peter]

Hi all,

As per @WHMCS John's message above, please enable the slow query log in MySQL and share the results. If you're not happy sharing the results here, please feel free to submit a ticket. The slow query log allows us to effectively troubleshoot if MySQL interaction is causing the slowdown of WHMCS on your servers.

@yggdrasil - Please feel free to share your ticket ID and I'll be happy to take a look at it. If you'd be happy to, it would be great if you could also provide the output of a MySQL slow query log to ensure we effectively troubleshoot the issue.

[Chad]

@yggdrasil

As I already stated, only admin side.

[Chad]

Let me re-summarize:

Slowness happened immediately after upgrading to 7.6. Prior on 7.5 and before, any page load in admin panel was lightning fast. Now, 2 seconds average any page at all in admin panel. Client side remains normal otherwise. Using default Blend theme. Already ran database repair, optimize via phpMyAdmin. Already ran "empty template cache" and cleared browser cache as well. Problem replicated in Firefox on Windows 10 and Safari on iPhone. Same slowness in admin panel. Adding below line to config file did not fix the problem at all.

 $disable_admin_ticket_page_counts = true; 

Server specs:

2 x Hex Core with 72GB RAM, SSD drives and only 2 domains hosted on the box.
PHP 7.2 with MySQL/MariaDB 10.2

MySQL settings:

[root@server ~]# cat /etc/my.cnf
[mysqld]
performance-schema=0

# GENERAL #
local-infile=0
default-storage-engine         = MyISAM

# MyISAM #
key-buffer-size                = 16G
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
max_connections                = 300
#sql-mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GR$
sysdate-is-now                 = 1

# BINARY LOGGING #
#log-bin                       = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 512M
max-heap-table-size            = 512M
query_cache_type               = 0
query-cache-size               = 0
query_cache_limit              = 0
join_buffer_size               = 1M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240
wait_timeout                   = 600
sort_buffer                    = 8M
read_buffer                    = 8M

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8G
innodb_buffer_pool_instances   = 8
performance_schema             = ON

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
slow_query_log                 = 1
long_query_time                = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
log-queries-not-using-indexes  = 1
max_allowed_packet=268435456
open_files_limit               = 50000

bind-address=127.0.0.1

MySQL Slow Query Logs:

# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.000140  Lock_time: 0.000031  Rows_sent: 5  Rows_examined: 5
# Rows_affected: 0
use user_billing;
SET timestamp=1533245423;
select 0, `title` from `tblticketstatuses`;
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.000131  Lock_time: 0.000026  Rows_sent: 5  Rows_examined: 10
# Rows_affected: 0
SET timestamp=1533245423;
select `title` from `tblticketstatuses` order by `sortorder` asc;
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.000137  Lock_time: 0.000020  Rows_sent: 5  Rows_examined: 10
# Rows_affected: 0
SET timestamp=1533245423;
select `id`, `name` from `tblticketdepartments` where `id` in ('6', '3', '12', '4', '2') order by `order` asc, `order` asc;
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.000078  Lock_time: 0.000015  Rows_sent: 1  Rows_examined: 4
# Rows_affected: 0
SET timestamp=1533245423;
select `title` from `tblorderstatuses` where `showpending` = '1';
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.003381  Lock_time: 0.000025  Rows_sent: 1  Rows_examined: 3968
# Rows_affected: 0
SET timestamp=1533245423;
select count(*) as aggregate from `tblorders` inner join `tblclients` on `tblclients`.`id` = `tblorders`.`userid` where `tblorders`.`status` in ('Pending');
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.009275  Lock_time: 0.000022  Rows_sent: 3  Rows_examined: 3950
# Rows_affected: 0
SET timestamp=1533245423;
select count(id) as count, status from `tblclients` group by `status`;
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.009296  Lock_time: 0.000029  Rows_sent: 6  Rows_examined: 5110
# Rows_affected: 0
SET timestamp=1533245423;
select count(id) as count, domainstatus from `tblhosting` group by `domainstatus`;
# User@Host: user_dbuser[user_dbuser] @ localhost []
# Thread_id: 11513  Schema: user_billing  QC_hit: No
# Query_time: 0.001413  Lock_time: 0.000025  Rows_sent: 8  Rows_examined: 800
# Rows_affected: 0
SET timestamp=1533245423;
select count(id) as count, status from `tbldomains` group by `status`;

 

[CSSHG]

We have tried the work ar

On 8/1/2018 at 11:14 PM, dnerty said:

Open configuration.php in whmcs home folder

Try with

$disable_admin_ticket_page_counts = true;

Save it

That helped me. WHMCS is as fast as before.

Hope will help you.

We have tried this and it works. But this is not a solution in our mind as we need to be able to see the number of tickets waiting etc. We have sent all our logs to WHMCS support and are waiting for a response.  Lets hope they get this sorted ASAP

 

[Nesooo]

I also confirm this, we did try to disable all plugins, hooks and still same, huge slow after upgrade to 7.6 version in admin area.

This code bellow did help, but still slower load then before, but it is stupid if we need to disable this to get performance boost.

$disable_admin_ticket_page_counts = true; 

Edited August 3, 2018 by Nesooo

[Chad]

Agreed and their support denies any slowness. Liars.

[JonOnTheWeb]

The WHMCS team have opened a bug case for this to be fixed: #CORE-12594

[WHMCS Lawrence]

If the provided variable didn't resolve this, that would seem to suggest a different issue than what is being experienced by others here. Looking at the MySQL slow query logs, none of these queries are running slowly (they are all completing within thousands of a second). If my colleagues are indicating that no slowness is being displayed, this means that it doesn't occur when we attempt to reproduce it. If it continues, please follow up on your ticket with a video confirming as such with your browser network console open and showing the load times for each asset being loaded. We appreciate your patience and understanding in this matter.

[WHMCS John]

Hi all,

A hotfix has been made available to improve the efficiency of the support ticket statistic calculations in the admin area:

After applying this hotfix, please do let us know if the expected performance is restored.

[CSSHG]

Hi John,

I have applied the patch and all looks good.  Thanks for getting it sorted out quickly.

Regards

Cameron

[rkatz0]

18 hours ago, WHMCS John said:

Hi all,

A hotfix has been made available to improve the efficiency of the support ticket statistic calculations in the admin area:

After applying this hotfix, please do let us know if the expected performance is restored.

I am happy this issue was addressed quickly, that brings us one step closer to being able to apply the new update. Thank you WHMCS!

[ocastaned]

On 8/1/2018 at 8:14 AM, dnerty said:

Open configuration.php in whmcs home folder

Try with

$disable_admin_ticket_page_counts = true;

Save it

That helped me. WHMCS is as fast as before.

Hope will help you.

 

 

That helped me. Thanks