Page 1 of 2

Very slow login in Servoy 5.2.2

PostPosted: Tue Nov 23, 2010 5:21 pm
by ionelioras
Hi everybody,

I migrated our Servoy application from 5.1.4 to 5.2.2. The database is still Sybase and the OS is Windows.
The login takes very long. I looked in the performance table from the Application Server and I saw a Repository SQL query that takes minimum 9 minutes(last time 10:14:594). In Servoy 5.1.4 everything worked well. The query is this:

Code: Select all
select e.object_uuid, sge.element_access, sge.group_id
from servoy_group_elements sge
inner join servoy_groups sg on sge.group_id=sg.group_id
inner join servoy_elements e on sge.element_id=e.element_id
inner join servoy_releases r on e.revision=r.revision and e.element_id=r.element_id
where ((sge.root_element_id = ? and r.release_number = ?) or[...]or (sge.root_element_id = ? and r.release_number = ?))
order by sge.element_id asc

Does anybody know when Servoy executes this query or why takes it so long?
Thank you!
Best regards, Ionel Ioras

Re: Very slow login in Servoy 5.2.2

PostPosted: Wed Nov 24, 2010 12:53 am
by Harjo
We also saw this once, and yesterday, I got this report also from a third (servoy-development) company.


00000616.png
00000616.png (63.7 KiB) Viewed 8787 times


I will ask, if they filed an issue in the support system....

Re: Very slow login in Servoy 5.2.2

PostPosted: Wed Nov 24, 2010 10:56 am
by martinh
Harjo,

I've seen similar situations before, where 1 query took very long time.
Check your database and see if there is a page lock or table lock, which causes that the servoy query is waiting for the lock to be released.
If you kill this lock directly in your database and then you see that the query continues, then you have found the problem (that was my case)

And then find out why this page or table lock was there, because that is not always very evident.

Martin

Re: Very slow login in Servoy 5.2.2

PostPosted: Wed Nov 24, 2010 1:00 pm
by ionelioras
There were no locks in the login/start application process. But after using a fresh empty repository server and reimporting the solutions, this query takes now 47 ms.

Re: Very slow login in Servoy 5.2.2

PostPosted: Wed Nov 24, 2010 1:01 pm
by Harjo
Martin,

we had this every time we start the the server the first time!
so no clients yet, this is Servoy doing IMHO

Re: Very slow login in Servoy 5.2.2

PostPosted: Mon Dec 06, 2010 10:56 pm
by Harjo
This problem returns, even after setting up a new repository
We had this on our production-server and we replaced the the repository db, and all was fine, but now a few weeks later and a dozen releases further, this issue is back.

after every update we do, and than start a new client, Sybase is pumping for 10-15 minutes to 50%, all clients, are stalled & must wait before this process is finishing

I hope Servoy could take a look at this...

Re: Very slow login in Servoy 5.2.2

PostPosted: Mon Dec 06, 2010 11:31 pm
by msedita
Yes we have the same or a similar problem, after every update the first client login takes a long time, and the CPU utilization is very high.

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 12:21 am
by Harjo
I can't find what is going on. I dont see a query that takes long in the servoy-performance tab :-(

will see what profiling Sybase will show me for info........

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 1:12 am
by Harjo
oke, the profiling shows again, a very lengthy query:
Schermafbeelding 2010-12-07 om 00.06.57.png
Schermafbeelding 2010-12-07 om 00.06.57.png (17.78 KiB) Viewed 8563 times


select ep.element_id,ep.content_id,ep.property_value from servoy_releases as r,servoy_element_properties as ep where ep.revision = r.revision and ep.element_id = r.element_id and r.root_element_id = :p0 and r.release_number = :p1 order by ep.element_id asc,ep.content_id asc,ep.sequence asc

There is definitly something changed within Servoy, we never had this before......

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 1:44 am
by mboegem
msedita wrote:Yes we have the same or a similar problem, after every update the first client login takes a long time, and the CPU utilization is very high.


+1, had this for longer time (also previous to 5.2.2) thought it was our solution, which is quite big, but as everyone suffers from this...
Fix would be nice, because now we have to take down our solution before updating, just to make sure no users login...
Although it's only a couple of minutes, it would be nice to not doing this... especially as it is one of the nice things about Servoy (when it works ;-) )

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 2:25 am
by msedita
A couple of other observations. We have four servers running, all identical Amazon EC2 instances, identical solutions (but different database contents). One server takes much longer that then rest to process a solution update, and that is the one that also takes the longest to startup the first client. It is also the one in production longest, with the most cumulative updates. So I suspect that the more a solution is updated, the longer it takes to update further, and to start up.

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 11:08 am
by mboegem
msedita wrote:A couple of other observations. We have four servers running, all identical Amazon EC2 instances, identical solutions (but different database contents). One server takes much longer that then rest to process a solution update, and that is the one that also takes the longest to startup the first client. It is also the one in production longest, with the most cumulative updates. So I suspect that the more a solution is updated, the longer it takes to update further, and to start up.


The more versions are stored in the repo, the longer the updates take. That's for sure.
Our solution has about 12 modules and I try to cleanup my repo (just create a fresh db file) every 15 updates.
Also the size of solutions seems to matter.

But even with a fresh repo file, I still have the startup issue.

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 6:31 pm
by msedita
Question to mboegem - what do you mean by "create a fresh db file"? Do you mean replacing servoy_repository.db with a copy that has no data in any table?

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 6:47 pm
by rgansevles
Hi,

There are 2 repository queries discussed here that are causing some delay in startup (or when a new solution is imported).

The one from Harjo is found to be an index in the servoy repository that interferes with another index resulting in a bad query execution plan for loading big solutions.
This index will be dropped in the Servoy 6 upgrade.

The other query from Ionel seems to have been fixed by creating a new repository.
If this query gets slower over time again, we will do more investigation in to that.

Rob

Re: Very slow login in Servoy 5.2.2

PostPosted: Tue Dec 07, 2010 7:24 pm
by Harjo
Rob, excellent work!

thanks for all the effort!!

ps for now we have manually removed the (troubling) index and starting a client after import is instant again! :D