Slow commit

[HEGEDUS, Ervin]

Hello there,

I'm using two machines:

machineOLD: P4 2.8GHz, ICH5 with an IDE disc, Ubuntu 8.10, MySQL 5.0.67,
python-mysql 1.2.2-10

machineNEW: Xeon 4 core, P410i with RAID10 SATA, Ubuntu 9.10 (alpha6), MySQL
5.0.83, python-mysql 1.2.2-7

There are several scripts, which parses a packed "datafile", and
loads records to a MySQL database. The scipt was developed on machineOLD. Now
it comes the machineNEW, but it very-very slow.

I started to search why is it slow. Here is a sample code:

(I don't use autocommit basicly)

cursor.execute("""INSERT INTO my_table_edit VALUES (0, 1,
%d)')""" % (myvar))

conn.commit()

cpid = cursor.lastrowid

cursor.execute("""INSERT INTO my_table_pub VALUES (%d, 1,
%d)')""" % (cpid, myvar))

cursor.execute("""INSERT INTO my_table_onl VALUES (%d, 1,
%d)')""" % (cpid, myvar))

conn.commit()

The commit's are very-very slow. If I comment those lines, the scripts runtime
will be radically less...

For example: if I committed querys as above, 1000 records has loaded until
32sec on machineOLD, and 2min48sec on machineNEW.

If I commented commit(), and uses just one commit() at the end of script, that
runs on machineOLD till 16.3sec, and 5.9sec on machineNEW.

Okay, I understand why faster the script whit just one commit, but I don't
understand why slower the machineNEW than machineOLD with many commit?

Any help would be appreciated.

Thanks:

a.

[HEGEDUS, Ervin]

One important thing: if I dumped a database, and loaded back, machineNEW about
three-times faster than machineOLD. Every other query (SELECT, VIEW...)
terrible faster on machineNEW than machineOLD. Just INSERT DML, if I use
commit often.

Thanks a lot:

a.

[Leo Hourvitz]

Are you sure the default storage engine is the same on both machines? If
machineOLD is defaulting to MyISAM and machineNEW is defaulting to InnoDB,
then machineOLD would actually be ignoring most transactions.

In any case, you probably want to comment out all the commits except the last
one and carry on unless there's some explicit reason you need them; typically
in a datafile import situation you do want the whole thing committed at once
so there are no lingering partial imports.

[HEGEDUS, Ervin]

Hello,

yes, I'm sure I'm using same engine, which is InnoDB on all machines.

But.

Sometimes I need to access the partial import in tables.... generally I need
the row-commit.

Thanks:

a.

[HEGEDUS, Ervin]

Ok, I think I found the problem: on the disc controller there was switched off
the write cache - I turned on, and now everytings good.

Thanks:

a.