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:
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
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.
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.
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
oke, the profiling shows again, a very lengthy query:
[attachment=0]Schermafbeelding 2010-12-07 om 00.06.57.png[/attachment]
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…
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.
+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 )
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.
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.
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.
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?
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.
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?
In our case: the problem occurred because an index ep_rev on servoy_element_properties was interfering with the solution load query.
The index will be dropped as part of Servoy 6 upgrade, as a workaround you can drop it using a db tool: drop index ep_rev
As far as I know, this is for Sybase 11, ONLY!! be aware, make backups!!
