sqlexec thread migration from CPUVP to JVP

Posted in 2006

Topics: Performance & Tuning, Server Administration, Migration, Import/Export & Data Conversion, Java & JDBC Development

Davorin Kremenjas — Wed, 5 Jul 2006 12:59:13 +0200 — source: Usenet: comp.databases.informix

Hello everyone. A few days ago I've had a situation at client's place regarding poor performance. They started using Java UDR's (for a very simple thing they were unable to do any other way). Java UDR was beeing called only once, during user's logging into application. Everything else was identical as before and application is already in production for a few years. This is what I observed together with client's DBA: if a user runs a Java UDR in his/her session, sqlexec thread for that session migrates it's execution to JVP. All successive atempts to run a query execute on JVP - and they execute a lot slower! I setup a simple test on my test instance like this: 1. Connect to database. 2. Run a "heavy" query. Result: sqlexec thread executes on cpu vp. 3. Run a simple Java UDR. 4. Run that same "heavy" query. Result: sqlexec thread executes on jvp. 5. Run a trivial C UDR designed to run on cpu vp (modifier: WITH(class='cpu')). 4. Run that same "heavy" query once again. Result: sqlexec thread executes on cpuvp. So I concluded that sqlexec migrates from one VP to another after it encopasses UDR's with explicitly set VP type. So, the client decided to use the "workaround" with calling C UDR to get sqlexec back to cpuvp. Since JVP's seems to execute a lot slower (much more threads in ready queue), with response times around 10 times longer, do you think I can consider this a bug/misfeature and place a call with Tech Support?

↪ replying to Davorin Kremenjas

DL Redden — Wed, 5 Jul 2006 06:15:50 -0700 (PDT) — source: Usenet: comp.databases.informix

Sounds like it to me. You even have a reproducible case which is what they'd want. The hard part it seems is done. ----- Original Message ---- From: Davorin Kremenjas <davorin.kremenjas@alfatec.hr> To: informix-list@iiug.org Sent: Wednesday, July 5, 2006 5:59:13 AM Subject: sqlexec thread migration from CPUVP to JVP Hello everyone. A few days ago I've had a situation at client's place regarding poor performance. They started using Java UDR's (for a very simple thing they were unable to do any other way). Java UDR was beeing called only once, during user's logging into application. Everything else was identical as before and application is already in production for a few years. This is what I observed together with client's DBA: if a user runs a Java UDR in his/her session, sqlexec thread for that session migrates it's execution to JVP. All successive atempts to run a query execute on JVP - and they execute a lot slower! I setup a simple test on my test instance like this: 1. Connect to database. 2. Run a "heavy" query. Result: sqlexec thread executes on cpu vp. 3. Run a simple Java UDR. 4. Run that same "heavy" query. Result: sqlexec thread executes on jvp. 5. Run a trivial C UDR designed to run on cpu vp (modifier: WITH(class='cpu')). 4. Run that same "heavy" query once again. Result: sqlexec thread executes on cpuvp. So I concluded that sqlexec migrates from one VP to another after it encopasses UDR's with explicitly set VP type. So, the client decided to use the "workaround" with calling C UDR to get sqlexec back to cpuvp. Since JVP's seems to execute a lot slower (much more threads in ready queue), with response times around 10 times longer, do you think I can consider this a bug/misfeature and place a call with Tech Support? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list