[Vtigercrm-developers] vt7.1 Slow?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Vtigercrm-developers] vt7.1 Slow?

Alan Lord (News)
Has anyone else tested this yet?

I've just done a test migration to 7.0.1 of a reasonably large customer
database: ~200 users, 4.5million rows in vtiger_crmentity.

As an admin user navigation around lists and Detail View pages is "OK"
but still much slower than I would expect or would see on 6.5.0 or their
original 5.4.0 system.

As a non-admin user however, it is utterly abysmal. From a list view of
Accounts, when I click on one to open it, it takes over 30 seconds!!!

As an admin user it takes about 4 seconds.


And before anyone questions the server hardware:

Intel® Xeon® E5-1650 v3 Hexa-Core Haswell

RAM 256 GB DDR4 ECC RAM
Hard Drive 2 x 480 GB SSD SATA 6 Gb/s

This is a *really* fast server. Installation of a clean vtiger 7 takes
less than one minute.

Al

_______________________________________________
http://www.vtiger.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vt7.1 Slow?

S T Prasad
Hi Alan,
Thats serious horsepower you have.
There probably is some bad query at play. Any clues from your logs


With best regards,

S.T.Prasad (Skype: [hidden email])
Founder and Chief Shikari
http://www.vtigress.com
The Purr-fect mate for vTiger
Certified Solution Partner for Asia and Africa

On Wed, Jun 7, 2017 at 6:54 PM, Alan Lord <[hidden email]> wrote:
Has anyone else tested this yet?

I've just done a test migration to 7.0.1 of a reasonably large customer database: ~200 users, 4.5million rows in vtiger_crmentity.

As an admin user navigation around lists and Detail View pages is "OK" but still much slower than I would expect or would see on 6.5.0 or their original 5.4.0 system.

As a non-admin user however, it is utterly abysmal. From a list view of Accounts, when I click on one to open it, it takes over 30 seconds!!!

As an admin user it takes about 4 seconds.


And before anyone questions the server hardware:

Intel® Xeon® E5-1650 v3 Hexa-Core Haswell

RAM 256 GB DDR4 ECC RAM
Hard Drive 2 x 480 GB SSD SATA 6 Gb/s

This is a *really* fast server. Installation of a clean vtiger 7 takes less than one minute.

Al

_______________________________________________
http://www.vtiger.com/


_______________________________________________
http://www.vtiger.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vt7.1 Slow?

Alan Lord (News)
On 07/06/17 14:56, S T Prasad wrote:
> Hi Alan,
> Thats serious horsepower you have.
> There probably is some bad query at play. Any clues from your logs

There are two queries which seem to be root cause of this:

The first creates a temporary table which in itself doesn't take any time:


> mysql> create temporary table IF NOT EXISTS vt_tmp_u271_t6(id int(11) primary key) ignore (SELECT 271 as id) UNION (SELECT vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN vtiger_role ON  vtiger_role.roleid=vtiger_user2role.roleid WHERE vtiger_role.parentrole like 'H1::H2::H4::%') UNION (SELECT groupid FROM vtiger_groups WHERE  groupid IN (56)) UNION  (SELECT shareduserid FROM vtiger_tmp_read_user_sharing_per WHERE userid=271 AND tabid=6) UNION (SELECT  vtiger_tmp_read_group_sharing_per.sharedgroupid FROM vtiger_tmp_read_group_sharing_per WHERE userid=271 AND tabid=6);
>
> Query OK, 188 rows affected (0.00 sec)
> Records: 188  Duplicates: 0  Warnings: 0

But the second, takes over 35 seconds:

I can't cut and paste it due to customer confidentiality but here is a
smudged screenshot of me running the query on the command line of the
MySQL server. Note the time at the bottom of the page ;-)


Al

_______________________________________________
http://www.vtiger.com/

Screenshot from 2017-06-07 15-26-29.png (589K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vt7.1 Slow?

Alan Lord (News)
It would appear to be the "ORDER BY" clause which is slowing down the
query mentioned in the previous post (and there *is* an index on the
modifiedtime column).

But what I really don't get, is why it takes so long to load a single
record? Why are these two queries being generated *after* the ListView
has already been displayed and I click on one record to view it?

Can anyone else migrate a system with a decent amount of data in it and
try this please? As an admin user it's not too bad, but still pretty
poor, but as a non-admin this is unusable.


Al


On 07/06/17 15:31, Alan Lord wrote:

> On 07/06/17 14:56, S T Prasad wrote:
>> Hi Alan,
>> Thats serious horsepower you have.
>> There probably is some bad query at play. Any clues from your logs
>
> There are two queries which seem to be root cause of this:
>
> The first creates a temporary table which in itself doesn't take any time:
>
>
>> mysql> create temporary table IF NOT EXISTS vt_tmp_u271_t6(id int(11)
>> primary key) ignore (SELECT 271 as id) UNION (SELECT
>> vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN
>> vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN
>> vtiger_role ON  vtiger_role.roleid=vtiger_user2role.roleid WHERE
>> vtiger_role.parentrole like 'H1::H2::H4::%') UNION (SELECT groupid
>> FROM vtiger_groups WHERE  groupid IN (56)) UNION  (SELECT shareduserid
>> FROM vtiger_tmp_read_user_sharing_per WHERE userid=271 AND tabid=6)
>> UNION (SELECT  vtiger_tmp_read_group_sharing_per.sharedgroupid FROM
>> vtiger_tmp_read_group_sharing_per WHERE userid=271 AND tabid=6);
>>
>> Query OK, 188 rows affected (0.00 sec)
>> Records: 188  Duplicates: 0  Warnings: 0
>
> But the second, takes over 35 seconds:
>
> I can't cut and paste it due to customer confidentiality but here is a
> smudged screenshot of me running the query on the command line of the
> MySQL server. Note the time at the bottom of the page ;-)
>
>
> Al
>
>
> _______________________________________________
> http://www.vtiger.com/
>


_______________________________________________
http://www.vtiger.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vt7.0.1 Slow? [No more than 6.5.0]

Alan Lord (News)
The migration of this system went from 5.4.0 to 6.5.0 to 7.0.1

I just went and logged into the 6.5.0 interim stage and this one is just
as slow.

So I am going to assume that this is not specifically to do with v7.
More with the change introduced in v6 when the ORDER BY modifiedtime was
introduced in list views.

I'm not sure how I'm going to address this yet (other than just removing
the ORDER BY clause obviously), but wanted to make sure it wasn't
misinterpreted.

It still begs the question WHY are list view style queries running when
I want to load a single record...

Al


On 07/06/17 15:54, Alan Lord wrote:

> It would appear to be the "ORDER BY" clause which is slowing down the
> query mentioned in the previous post (and there *is* an index on the
> modifiedtime column).
>
> But what I really don't get, is why it takes so long to load a single
> record? Why are these two queries being generated *after* the ListView
> has already been displayed and I click on one record to view it?
>
> Can anyone else migrate a system with a decent amount of data in it and
> try this please? As an admin user it's not too bad, but still pretty
> poor, but as a non-admin this is unusable.



_______________________________________________
http://www.vtiger.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vt7.1 Slow?

S T Prasad
In reply to this post by Alan Lord (News)
There is a strapon query for non admin. Maybe that is hogging time?

With best regards,

S.T.Prasad (Skype: [hidden email])
Founder and Chief Shikari
http://www.vtigress.com
The Purr-fect mate for vTiger
Certified Solution Partner for Asia and Africa

On Wed, Jun 7, 2017 at 8:24 PM, Alan Lord <[hidden email]> wrote:
It would appear to be the "ORDER BY" clause which is slowing down the query mentioned in the previous post (and there *is* an index on the modifiedtime column).

But what I really don't get, is why it takes so long to load a single record? Why are these two queries being generated *after* the ListView has already been displayed and I click on one record to view it?

Can anyone else migrate a system with a decent amount of data in it and try this please? As an admin user it's not too bad, but still pretty poor, but as a non-admin this is unusable.


Al



On 07/06/17 15:31, Alan Lord wrote:
On 07/06/17 14:56, S T Prasad wrote:
Hi Alan,
Thats serious horsepower you have.
There probably is some bad query at play. Any clues from your logs

There are two queries which seem to be root cause of this:

The first creates a temporary table which in itself doesn't take any time:


mysql> create temporary table IF NOT EXISTS vt_tmp_u271_t6(id int(11) primary key) ignore (SELECT 271 as id) UNION (SELECT vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN vtiger_role ON  vtiger_role.roleid=vtiger_user2role.roleid WHERE vtiger_role.parentrole like 'H1::H2::H4::%') UNION (SELECT groupid FROM vtiger_groups WHERE  groupid IN (56)) UNION  (SELECT shareduserid FROM vtiger_tmp_read_user_sharing_per WHERE userid=271 AND tabid=6) UNION (SELECT  vtiger_tmp_read_group_sharing_per.sharedgroupid FROM vtiger_tmp_read_group_sharing_per WHERE userid=271 AND tabid=6);

Query OK, 188 rows affected (0.00 sec)
Records: 188  Duplicates: 0  Warnings: 0

But the second, takes over 35 seconds:

I can't cut and paste it due to customer confidentiality but here is a smudged screenshot of me running the query on the command line of the MySQL server. Note the time at the bottom of the page ;-)


Al


_______________________________________________
http://www.vtiger.com/



_______________________________________________
http://www.vtiger.com/


_______________________________________________
http://www.vtiger.com/
Loading...