MySQLdb slow on MySQL 5

[timothy.williams]

Hi.

I have a Python program that parses a file and inserts records into a
database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my
parser runs *really* slow. Writing out to CSV files is fine, but when I
try to insert the same records in a MySQL5 database, it slows to a
crawl. Using MySQL 4.1 seems fine. The data seems to be inserted
correctly, it's just really slow.

Has anyone else noticed a similar problem using MySQL5 and MySQLdb?

Thanks.

[Gerhard Häring]

ti**************@nvl.army.mil wrote:

Hi.

I have a Python program that parses a file and inserts records into a
database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my
parser runs *really* slow. Writing out to CSV files is fine, but when I
try to insert the same records in a MySQL5 database, it slows to a
crawl. Using MySQL 4.1 seems fine. The data seems to be inserted
correctly, it's just really slow.

Has anyone else noticed a similar problem using MySQL5 and MySQLdb?

Maybe you're running in autocommit mode. I. e. an implicit COMMIT is
done for each insert. This slows any database down.

Or are you calling commit() yourself too often?

-- Gerhard

[timothy.williams]

Gerhard Häring wrote:

Maybe you're running in autocommit mode. I. e. an implicit COMMIT is
done for each insert. This slows any database down.

Or are you calling commit() yourself too often?

-- Gerhard

I'm not calling COMMIT at all. I'm using MyISAM tables. I'll check on
autocommit. Thanks.

[Magnus Lycka]

ti**************@nvl.army.mil wrote:

I'm not calling COMMIT at all.

Then you must be using autocommit if your records stay in
the table. An DB-API 2.0 compliant adapter should turn off
autocommit by default! Does MyISAM even support proper
commit handling?

Oh well, Oracle will probably kill MySQL soon. Good! I'd
use a real database system. It hurts my Swedish heart that
one of the best known Swedish software products is so bad.

It's one thing for some more inventive product like Skype
to pave its own path, but to build something called an
SQL database with such a complete disregard of the SQL
standard seems like Microsoft in the 80's or 90's to me.

If you must have a Swedish database server, use Mimer,
otherwise PostgreSQL 8.2 is a good open source alternative,
and all the major vendors have gratis versions now.
Oracle is a resource hog, and a pain to download and
install though.

[Gerhard Häring]

Magnus Lycka wrote:

ti**************@nvl.army.mil wrote:

> I'm not calling COMMIT at all.
Then you must be using autocommit if your records stay in
the table. An DB-API 2.0 compliant adapter should turn off
autocommit by default! Does MyISAM even support proper
commit handling? [...]

No, it doesn't. And COMMIT is then simply a no-op if it doesn't raise an
error already.
Oh well, Oracle will probably kill MySQL soon.
Did you read too much Slashdot to spread such FUD?
[...] I'd use a real database system.

MySQL 5 could be described as one, according to the feature list, and if
you use a transactional table type.

I myself won't bother with it because PostgreSQL is still more
featureful that MySQL 5, has a much longer track record with these
features proven stable and a more liberal licensing.

-- Gerhard

[Magnus Lycka]

Gerhard Häring wrote:

Oh well, Oracle will probably kill MySQL soon.
Did you read too much Slashdot to spread such FUD?

:) I guess I should have used half a smiley, since I
was only half serious. Buying InnoBase OY was...an
interesting move. Cheap too! It's a while since I visited /.

For those who don't know, Oracle bought a small Finnish
company which developed the transaction-aware table-
backend for MySQL. As far as I understand, this means
nothing for GPL licensed MySQL installations, but if
MySQL sells commercial MySQL-database with transaction
support, I guess they need to share their incomes with
Oracle now, and I suppose Oracle is completely in charge
of future development and price setting for InnoDB. Of
course, MySQL has support for swapping table backends,
and the logical next step might be to use the major open
source transactional low level database Berkeley DB,
which is also dual licensed, and developed by SleepyCat.
Unfortuantely, Oracle just bought them... I'm sure there
are other ways out though. MySQL owns MaxDB, which was
previously SAP DB, a derivate ot the German Adabas D.
I'm not sure how easy it is to rip out the table back-
end of that though. I guess it's not built to be a
separate component.

Anyway, it's annoying with a product that so clearly do
things their own way, instead of the right way. It would
have been one thing if it had been something in a niche
of its own like SQLite, or something novel, such as an
object-relational database ten years ago, but what they've
done is often just non-standard without being better, and
in many aspects buggy and lacking features.

From a market point of view, I do think that they have
been refreshing, and it was probably good for PostgreSQL
to have competition too, but I would have liked a somewhat
stronger interest in the SQL standard from those guys.

From this perspective I don't think Monty and Mårten are
better than Bill and Bullmer over in Redmond. They create
vendor lock-in, and a warped view of how things are supposed
to work among many naive developers. While it's obviously
more of an industrial strength product (in its own way) it
does remind me a lot of MS Access / JET. "Let's do something
similar to SQL, and call it SQL, but don't bother looking
at any standards, let's just code what pops up in our minds."

It's one thing if Oracle fails to comply to all standards,
with strange things as treating empty strings as nulls. At
least they can claim that their product had a large installed
base before the standards were set... Hm... That's actually
a rather crappy excuse too... That's what varchar2 was for.
MySQL 5 could be described as one, according to the feature list, and if
you use a transactional table type.
Neither you nor I choose DBMS by ticking off checkboxes
in a list...
I myself won't bother with it because PostgreSQL is still more
featureful that MySQL 5, has a much longer track record with these
features proven stable and a more liberal licensing.

How surprising! ;^) Me too by the way... I've had quite a
happy cooperation with Oracle, Informix, Ingres, Mimer,
Sybase and DB2 too. They all have their problems, but I
prefer them to MySQL any day.

[Jarek Zgoda]

Magnus Lycka napisa³(a):

For those who don't know, Oracle bought a small Finnish
company which developed the transaction-aware table-
backend for MySQL. As far as I understand, this means
nothing for GPL licensed MySQL installations, but if
MySQL sells commercial MySQL-database with transaction
support, I guess they need to share their incomes with
Oracle now, and I suppose Oracle is completely in charge
of future development and price setting for InnoDB. Of
course, MySQL has support for swapping table backends,
and the logical next step might be to use the major open
source transactional low level database Berkeley DB,
which is also dual licensed, and developed by SleepyCat.
Unfortuantely, Oracle just bought them... I'm sure there
are other ways out though. MySQL owns MaxDB, which was
previously SAP DB, a derivate ot the German Adabas D.
I'm not sure how easy it is to rip out the table back-
end of that though. I guess it's not built to be a
separate component.

They just hired Jim Starkey. Perhaps MySQL AB hopes he will write a
transactional engine for MySQL, as he previously wrote one for Interbase
(which is known to be one of the best a man could imagine).

Anyway, we got far off topic, so we better go somewhere else.

--
Jarek Zgoda
http://jpa.berlios.de/

[Magnus Lycka]

Jarek Zgoda wrote:

They just hired Jim Starkey. Perhaps MySQL AB hopes he will write a
transactional engine for MySQL, as he previously wrote one for Interbase
(which is known to be one of the best a man could imagine).

Anyway, we got far off topic, so we better go somewhere else.

Ok, to bring us a bit back on track again, I'm
curious about Firbird, since we want an embedded
SQL engine for some automated testing tasks, and
SQLite is a bit too lite for our needs.

We use our own DB API, so we won't really need any
Python DB-API adapter, but I'm still interested
in experiences from Pythonistas using Firebird--
especially embedded.

[Ray Cote]

At 5:07 PM +0100 2/27/06, Magnus Lycka wrote:

<SNIP>
I'm still interested
in experiences from Pythonistas using Firebird--
especially embedded.

Works great.
Python and Firebird embedded (at least on Windows) is very simple to use.
Not currently using it on other platforms.
--Ray

--

Raymond Cote
Appropriate Solutions, Inc.
PO Box 458 ~ Peterborough, NH 03458-0458
Phone: [phone] ~ Fax: [phone]
rgacote(at)AppropriateSolutions.com
www.AppropriateSolutions.com

[Claudio Grondi]

Ray Cote wrote:

At 5:07 PM +0100 2/27/06, Magnus Lycka wrote:

<SNIP>
I'm still interested
in experiences from Pythonistas using Firebird--
especially embedded.

Works great.
Python and Firebird embedded (at least on Windows) is very simple to use.
Not currently using it on other platforms.
--Ray

May I ask what do I need to use embedded Firebird from Python on Windows?

Which Firebird download
(http://www.firebirdsql.org/download/...mbed_win32.zip
?)
and which Python module
(http://kinterbasdb.sourceforge.net/ ?)

Do I understand it right, that I can use Firebird database in the same
way as I can use the Berkeley one provided in Python standard distribution?

Claudio

[Magnus Lycka]

Claudio Grondi wrote:

Do I understand it right, that I can use Firebird database in the same
way as I can use the Berkeley one provided in Python standard distribution?

That depends on what you mean by "the same way"...

As with Berkeley, you can use it in-process, and don't need a
separate server process. On the other hand, it's a full SQL
database, not a "persistent mapping" as Berkeley DB etc. It's
better to compare it with SQLite, but it's not as lite. That
is, it support more of the SQL standards. Firebird can also be
used as a database server, not just as an embedded database.

[haxier]

All the info you need is in the kinterbasdb module. I've worked with it
under windows and Linux and... "it just works". Really well indeed. I'd
recommend it a lot.

http://kinterbasdb.sourceforge.net/d...ded_using_with

--
Asier.

[Claudio Grondi]

haxier wrote:

All the info you need is in the kinterbasdb module. I've worked with it
under windows and Linux and... "it just works". Really well indeed. I'd
recommend it a lot.

http://kinterbasdb.sourceforge.net/d...ded_using_with

--
Asier.

Thanks - you have provided exactly the information I asked for.

Thanks also to Magnus (yes I asked for the in-process feature).

Claudio