473,221 Members | 1,891 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

MySQLdb slow on MySQL 5

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.

Feb 23 '06 #1
12 2960
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
Feb 23 '06 #2

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.

Feb 23 '06 #3
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.
Feb 24 '06 #4
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
Feb 24 '06 #5
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.
Feb 24 '06 #6
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/
Feb 24 '06 #7
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.
Feb 27 '06 #8
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: 603.924.6079 ~ Fax: 603.924.8668
rgacote(at)AppropriateSolutions.com
www.AppropriateSolutions.com
Feb 27 '06 #9
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
Feb 28 '06 #10
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.
Feb 28 '06 #11
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.

Feb 28 '06 #12
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
Feb 28 '06 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Chris Stromberger | last post by:
When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using MySQLdb,...
1
by: Peter Nikolaidis | last post by:
Greetings, I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a Fink distribution of Python 2.2.2. I have seen only a few posts on the subject, some of them relate to...
2
by: Tim Williams | last post by:
I'm trying to write a simple python program to access a MySQL database. I'm having a problem with using MySQLdb to get the results of a SQL command in a cursor. Sometimes the cursor.execute works,...
21
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All...
2
by: Daniel Baggott | last post by:
Hi, I'm seeking assistance in troubleshooting connecting to MySQL (4.0.20) using Python (2.3.4) and MySQLdb (1.0.0). When I attempt to make a connection, I get the following exception:...
1
by: Steve | last post by:
Darwin steve.local 8.3.0 Darwin Kernel Version 8.3.0: Mon Oct 3 20:04:04 PDT 2005; root:xnu-792.6.22.obj~2/RELEASE_PPC Power Macintosh powerpc MacOSX 10.4.3 mysql Ver 14.7 Distrib 4.1.14, for...
2
by: Mondal | last post by:
Hi, I am using MySQL 5.0 beta and Active Python 2.4. I have the correct version of MySQLdb installed. The problem is that I can't connect to MySQL through the Active Python interactive window. ...
0
by: timothy.williams | last post by:
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...
8
by: geert | last post by:
Hi all, I have a mac mini running maocosx 10.5 leopard I want to deploy a django project on. My backend is MySQL, and I have it running as a 64- bit app. Of course, apache2 is also running as...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.