Hi, I have two nearly identical solutions (connected to different DBs) being used by 60-100 users scattered all around the state of Texas on all different kinds of connections–mostly in educational instituions.
Setup:
Servoy version 3.5.5-build 518, repository version 31
Repository is Servoy’s Sybase
Data is in MySQL 4.1.x
JDBC = MySQL J/Connect 5.1.x
Server: OS X 10.4.11 Running on Dual 2.3ghz G5 Xserve w/4gb RAM
Servoy Network Settings:
rmi.connection.timeout = 10
ApplicationServer.pingDelay = 30
Servoy DB Settings for MySQL:
Maximum connections active = 10
Maximum connections idle 5
Maximum prepared statements idle = 100
Everything else is pretty much using the 3.5.5 defaults.
and now, on to the trouble…
For a long time (back to 2.2.x) we were having similar issues to the ones described in this post–the “Cannot Show Form Data” error–which got way worse when we upgraded from Servoy 3.1.7 to 3.5.5. Worse to the point where it was no longer an isolated, transient event that would clear by relaunching the client. It became impossible for most users to connect to either solution. (Yes, there is an onShow method attached to the first screen of each solution.)
I followed the advice of that post and a Servoy Magazine article and changed by MySQL databases to use Query Validation with SELECT 1 as the query string.
The errors went away immediately, but now both solutions are running slow as molasses. Forms which pull data from relations seem to lockup while waiting for data–often causing users to force quit/end task the client thinking it’s crashed.
Even here in my office, which is connected to the server through gigabit ethernet to the floor above, the solutions are running very slow–even when I’m the only one logged in.
So what gives?
Have others experienced this slowdown when using Query Validation?
Is there something I can do to get my performance back?
Anything in the MySQL- or Servoy Server log?
I suspect that MySQL is closing connections while Servoy expects them to be open. I am no MySQL guru but I would go look for settings in MySQL to keep idle connections open longer (if possible).
ROCLASI:
I suspect that MySQL is closing connections while Servoy expects them to be open. I am no MySQL guru but I would go look for settings in MySQL to keep idle connections open longer (if possible).
This could be the reason for your troubles, you can change this setting in the my.cnf (usually located in /etc) adding this:
# Set timeouts at 36 hours
wait_timeout = 129600
interactive_timeout = 129600
I’m working on a new updated version of the Servoy&MySQL MiniHowTo cause the one available on my website is quite outdated, I will let you know when ready.
Check also the Queries performance in the Admin Pages for bottlenecks and try to monitor the connections usage, maybe you need to add some connections to the pool, the default setting is 10 active connection and 5 idle, try to raise those number and see if there is a speed gain. You can change those settings in the Admin pages (Database Servers).
MySQL Administrator is a good tool to monitor your DB.
ROCLASI:
I suspect that MySQL is closing connections while Servoy expects them to be open. I am no MySQL guru but I would go look for settings in MySQL to keep idle connections open longer (if possible).
This could be the reason for your troubles, you can change this setting in the my.cnf (usually located in /etc) adding this:
ngervasi:
Check also the Queries performance in the Admin Pages for bottlenecks and try to monitor the connections usage, maybe you need to add some connections to the pool, the default setting is 10 active connection and 5 idle, try to raise those number and see if there is a speed gain. You can change those settings in the Admin pages (Database Servers).
MySQL Administrator is a good tool to monitor your DB.
I changed my active/idle connections settings in Servoy server admin from 10/5 to 50/25 to see what happens. So far so good, but traffic is kinda light today so I’m not sure if it’s a really being tested yet.
I’ve looked through the performance screen in the Servoy server admin tool, but nothing seems to stand out as an obvious issue. What should I be looking for?
stephengray:
I changed my active/idle connections settings in Servoy server admin from 10/5 to 50/25 to see what happens. So far so good, but traffic is kinda light today so I’m not sure if it’s a really being tested yet.
I’ve looked through the performance screen in the Servoy server admin tool, but nothing seems to stand out as an obvious issue. What should I be looking for?
thanks,
-Stephen
25 idle connections are too much in my opinion, try setting it to 50 max, 10 idle. Every connection will eat up memory on the DB server so you don’t want to have too much idle connections laying around.
You can also use MySQL Administrator to monitor the connection usage during the day and figure out optimal settings (look at the graphs section).
In Servoy Admin Pages go to the performance page and have a look at the time that the queries take to get executed, check especially those labelled as “Relation” if you are experiencing slow downs in related data display and see if the times are acceptable.
Check also the MySQL error log for other possible slowdown causes.
stephengray:
I changed my active/idle connections settings in Servoy server admin from 10/5 to 50/25 to see what happens. So far so good, but traffic is kinda light today so I’m not sure if it’s a really being tested yet.
I’ve looked through the performance screen in the Servoy server admin tool, but nothing seems to stand out as an obvious issue. What should I be looking for?
thanks,
-Stephen
25 idle connections are too much in my opinion, try setting it to 50 max, 10 idle. Every connection will eat up memory on the DB server so you don’t want to have too much idle connections laying around.
You can also use MySQL Administrator to monitor the connection usage during the day and figure out optimal settings (look at the graphs section).
In Servoy Admin Pages go to the performance page and have a look at the time that the queries take to get executed, check especially those labelled as “Relation” if you are experiencing slow downs in related data display and see if the times are acceptable.
Check also the MySQL error log for other possible slowdown causes.
Not too concerned about memory and idle connections haven’t climbed over 5 today. I’ll keep an eye on it though.
Here’s today’s update, however, usage has been quite high today, server admin shows that the 50 connections have been reached and the solutions is starting to run a bit slow. Not as bad as before, but still slower than they should.
I’ve also noticed this error in the Server Log, which in the past was associated with the “Cannot show form data” error. So I’m not sure what’s going on here–any ideas?
time: 2008-04-09 13:57
thread: RMI TCP Connection(5354)-216.213.210.21
level: ERROR
category: com.servoy.j2db.util.Debug
message: null
java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:801)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:110)
at com.servoy.j2db.persistence.datasource.i.getConnection(Unknown Source)
at com.servoy.j2db.persistence.Server.getConnection(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor208.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:613)
I read somewhere that sometimes a connection pool does not close correctly, throwing such an error.
The closing goes on and on and…
From what you say, too many open connections, that could be the case here.