Jump to content
Chad

Slow Database after upgrading to 7.6

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Edited by WHMCS ChrisD
Removed boxes and codes

Share this post


Link to post
Share on other sites

Try to change, in configuration.php

$disable_admin_ticket_page_counts = true;

That helps.

 

Share this post


Link to post
Share on other sites

You mean it should look like this?

 $disable_admin_ticket_page_counts = 'true;'

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

Edited by Chad

Share this post


Link to post
Share on other sites

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.

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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`;

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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 by Nesooo

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Hi John,

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

Regards

Cameron

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines