MySQL Bugs: #29234: Updates Time incremental

Bug #29234	Updates Time incremental
Submitted:	20 Jun 2007 10:52	Modified:	18 Jul 2007 14:53
Reporter:	ANTON PLANAS COLL	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	connector, time, UPDATE

Description:
Hi,

I'm working with java(1.6), connector/j(5.0.5) and mysql(5.0) when i'm execute a lot of updates the time between updates to increase severely.

for exemple each 1000 updates the time incremental 2 seg aproximaly.

I have to execute update about 300.000 registers.

How to repeat:
this is the function for updates....

public int updateElemento(Connection con, int id, String descripcion) throws Exception 
	{
		String Query = "";
		PreparedStatement pstm = null ;
		int rs = 0;
		Query = "UPDATE XXX SET YYY=? WHERE ZZZ LIKE ? LIMIT 1";
		pstm = con.prepareStatement(Query);
		pstm.setString(1,descripcion);
		pstm.setInt(2,id);
		rs=pstm.executeUpdate();
		pstm.close();
		return rs;

Andi this the main....

Connection...
while(.......)
{
[...]
for(int i=0;i<1000;i++){
updateElemento(Connection con, int id, String descripcion);
}
[...]
}
Desconnection..

Hi Barberan and thanks for your report. Can you tell me what is the reason for you to use PS when code is written like this? As I see, you are constantly freeing / preparing the same statement over and over again which defies it's very purpose (to prepare once and execute many times)...

Can you please test the following:
 - Don't use PS at all, write direct INSERT statement
 - Use PS but move it's creation / preparation to main function so that it's done just once
and inform me of result.

I should also mention that there were some changes in JDK 1.6 while c/J is still complying with JDK 1.5 (but I don't know the details).

Hi,

I tested the following:

1º test - Don't use PS at all, write direct INSERT statement
  *I had the same result, the time incremental too.

java code:
----------
public int updateElemento(Connection con, int id, String descripcion)
throws Exception     {
Statement st = con.createStatement();
int rs = 0;
rs = st.executeUpdate("UPDATE elementos2 SET descripcion_elemento='"+descripcion+"' WHERE id_elemento LIKE "+id+" LIMIT 1");
st.close();
return rs;

Andi this the main....

Connection...
while(.......)
{
[...]
for(int i=0;i<1000;i++){
updateElemento(Connection con, int id, String descripcion);
}
[...]
}
Desconnection.. 

time results: 
-------------
first 1000 updates -> 5 seconds
next 1000 updates -> 8 seconds
next 1000 updates -> 11 seconds
next 1000 updates -> 13 sec.
next 1000 updates -> 15 sec.

2º test - Use PS but move it's creation / preparation to main function so that
it's done just once.
  * with this option I have the same result, the time incremental too. I modify  code...

java code:
-----------
Connection...
        String Query = "";
        PreparedStatement pstm = null ;
        int rs = 0;
        Query = "UPDATE XXX SET YYY=? WHERE ZZZ LIKE ? LIMIT 1"; 
        pstm = con.prepareStatement(Query); 
while(.......)
{
[...]
 for(int i=0;i<1000;i++){
        pstm.setString(1,descripcion);
        pstm.setInt(2,id);
        rs=pstm.executeUpdate(); 
 }
[...]
}
pstm.close();
Desconnection.. 
 
time results: 
-------------
first 1000 updates -> 6 seconds
next 1000 updates -> 7 seconds
next 1000 updates -> 11 seconds
next 1000 updates -> 13 sec.
next 1000 updates -> 15 sec.
next 1000 updates -> 18 sec.

**I proved with connector 5.0.5, 5.0.6 and 5.1.0 too.

I cannot what is the problem, I will prove install jdk 1.5 and delete my jdk 1.6.

Barberan, waiting on test results with JDK 1.5

Hi again,

I install jdk 1.5 and I have same bad results.

first 1000 updates 5 seconds
next 1000 updates 8 sec
next 1000 updates 10 sec
next 1000 updates 14 sec
next 1000 updates 15 sec
next 1000 updates 19 sec

barberan

Barberan, thanks for testing.

Hi again,

I'm testing more....

if I put this code (with PS):

java code:
----------
[...]
 for(int i=0;i<1000;i++){
        pstm.setString(1,descripcion); <-** descripcion changes for iteration each.
        pstm.setInt(2,2); <-** id don't changes for iteration each
        rs=pstm.executeUpdate(); 
 }
[...]

the result are the following:

first 1000 updates -> 9 seconds
next 1000 updates -> 9 seconds
next 1000 updates -> 9 seconds
next 1000 updates -> 9 seconds
next 1000 updates -> 9 seconds

but if I execute this other test...

java code:
-----------

[...]
 for(int i=0;i<1000;i++){
        pstm.setString(1,"descripcion"); <-** descripcion don't changes for iteration each.
        pstm.setInt(2,id); <-** id changes for iteration each.
        rs=pstm.executeUpdate(); 
 }
[...]

the results was bad again.

ty for all

barberan

Barberan, I will need some more info. Please attach *full* test case (I want to see connection string and all), DDL script suitable for import with mysql cl client containing table structures and some data and my.ini/cnf file from your MySQL server.

Ok, I attach the java code and mysql schema, table...

The main program

Attachment: Importacion.java (application/octet-stream, text), 7.70 KiB.

necessary code

Attachment: Element.java (application/octet-stream, text), 415 bytes.

schema mysql

Attachment: elements2.txt (text/plain), 265 bytes.

the config mysql server by default.

Thanks barberan, looking into it again.

Barberan, I see a potential problem in JDBC-ODBC bridge. You shouldn't use the JDBC-ODBC bridge, as there is a Type-IV driver for MySQL, and JDBC-ODBC bridge is known to be very, very buggy. Can you repeat the problem without JDBC-ODBC bridge? If the bug is with the JDBC-ODBC bridge, which is software that comes from Sun Microsystems, not MySQL, we can not provide support for that. Even Sun advises that you not use the JDBC-ODBC bridge if there is a native JDBC driver for your database.

So, can you please test *without* JDBC-ODBC bridge and get back to me with new test case and results.

hi,

I use "sun.jdbc.odbc.JdbcOdbcDriver" with other database (eloquence'database).
First I connect at eloquence's database, I keep about 300.000 registers at one array and close this connection. (this part is fast about 1 minut).

Later (without  other connections opens) I use "com.mysql.jdbc.Driver" (by www.mysql.com) for I connect to Mysql's database and I take data array for updates, this part is when the time incremental succesfuly.

thanks

barberan

PD: when i update to mysql i dont use "sun.jdbc.odbc.JdbcOdbcDriver".

Barberan, ok. Let's examine your JVM settings. Can you please post complete line used to start it? Is it possible that some unexpected GC is slowing you down?

hi, 

I execute with:

/usr/share/jdk1.5.0_12/bin/java Importacion

I didnt change nothing about JVM settings.

the current JVM settings is default.

Barberan, thanks for info provided.

Barberan, using simple test case I was unable to repeat reported behavior...

-Xmx256M -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=&useServerPrepStmts=true

	createTable("`bug29234`", "(Id INT NOT NULL auto_increment PRIMARY KEY, Description VARCHAR(40)) ENGINE=InnoDB;");
	this.stmt.execute("INSERT INTO bug29234 VALUES (1,'Test row 1'),(2,'Test row 2'),(3,'Test row 3'),(4,'Test row 4'),(5,'Test row 5')");
	long startTime = System.currentTimeMillis();
	long endTime = System.currentTimeMillis();
	long ellapsedTime = endTime - startTime;
	for (int i=1; i<10000+1; i++)
	{
		this.pstmt = this.conn.prepareStatement("UPDATE bug29234 SET Description = ? WHERE Id LIKE ? LIMIT 1");
		this.pstmt.setInt(2, i+15000);
		this.pstmt.setString(1, "Test");
		this.pstmt.executeUpdate();
		this.pstmt.clearParameters();
		if((i%1000) == 0) {
			endTime = System.currentTimeMillis();
			ellapsedTime = endTime - startTime;
			System.out.println("Ellapsed time "+i+":"+ellapsedTime);
			startTime = System.currentTimeMillis();
		}
	}    		
//Int changing, String fixed

Connected to 5.0.44-max-nt-log
java.vm.version         : 1.5.0_11-b03
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_11-b03
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Connected to 5.0.44-max-nt-log

Description changing:
Ellapsed time 1000:24985
Ellapsed time 2000:21125
Ellapsed time 3000:24406
Ellapsed time 4000:26859
Ellapsed time 5000:23125
Ellapsed time 6000:22047
Ellapsed time 7000:24813
Ellapsed time 8000:22875
Ellapsed time 9000:24094
Ellapsed time 10000:23468

Time: 239,438
OK (1 test)
--------------------------------------------------
ID changing:
Ellapsed time 1000:1781
Ellapsed time 2000:1390
Ellapsed time 3000:1204
Ellapsed time 4000:1171
Ellapsed time 5000:1750
Ellapsed time 6000:1250
Ellapsed time 7000:1250
Ellapsed time 8000:1219
Ellapsed time 9000:1406
Ellapsed time 10000:1485

Time: 14,547

OK (1 test)

Please run your code through profiler and see where you loose time. I don't see bug here.