Re: PK Generation in MySQL
Re: PK Generation in MySQL
- Subject: Re: PK Generation in MySQL
- From: Chuck Hill <email@hidden>
- Date: Thu, 18 May 2006 12:02:52 -0700
On May 18, 2006, at 11:58 AM, Robert Walker wrote:
Understood, I'm doing something very similar to that in that same
project. For us, unfortunately, we brought MySQL to it's knees
with the shear number and frequency of insert/update calls to the
database. After switching to InnoDB (which performed better than
MyISAM in our case) and a lot of database tuning we decided to try
OpenBase. Obviously, we would have liked to use MySQL for cost
reasons, but just couldn't get the needed performance.
PostgreSQL or FrontBase would be better choices for cost reasons.
And performance and conformance to standards. Yes, FrontBase is now
free.
Chuck
In fact (as I now recall) we had a very similar problem. I don't
believe ours was with PK generation, but we were getting some
duplicate entries in the database due to "race conditions" on the
inserts. In our case we had to catch the exception and recover by
making a second attempt on the failed insert. It may be necessary
for you to do something similar.
I'll watch this thread. If there is a better fix for this issue,
I'd sure like to know.
On May 18, 2006, at 2:35 PM, Randy Wigginton wrote:
Thanks. The issue is not MySQL speed. I’m extremely happy with
the performance of MySQL. I am gathering information from a
webservice; thus, it is the gating operation. If I have two
separate threads querying the webservice, I get the results twice
as fast. During all of this my load goes up to maybe.. 0.03
From: webobjects-dev-bounces+cawineguy=email@hidden
[mailto:webobjects-dev-bounces
+cawineguy=email@hidden] On Behalf Of Robert Walker
Sent: Thursday, May 18, 2006 2:24 PM
To: Development WebObjects
Subject: Re: PK Generation in MySQL
Randy,
Generally PK generation is a function EOF not MySQL. There are
plug-ins that support database native PK generation, as is the
case with "OpenBasePKPlugIn." but I'm not aware of anything
similar for MySQL.
In any case, unless you're using something special, this problem
likely has nothing to do with MySQL. The problem would exist no
matter what data store is used.
This may have something to do with the batched PK generation used
by EOF, or improper use of locking on your EOEditingContexts.
Is it absolutely necessary to multi-thread your inserts? If
you're expecting better performance from MySQL, I don't think
you'll get what you expect. MySQL will just queue up the inserts
and do them in sequence anyway. At least this is my experience
with MySQL. In fact we had to go to OpenBase for a project just
because MySQL insert/update performance was abysmal. OpenBase
performed at least 10x faster than MySQL on insert/update.
Also, it's my understanding that EOF is not threaded anyway.
Could be wrong on this point.
On May 18, 2006, at 1:50 PM, Randy Wigginton wrote:
This seems like a surprisingly obvious bug, so I figured I’d
better ask what I’m doing wrong.
I have some code that writes records to a MySQL DB. The PK is a
simple INT, and the field is the only one the record locks on.
Everything works fine in a single thread… but when I start a
second thread writing to the table, sooner or later one thread
gets a “duplicate entry ‘187451’ for key 1.”
Do I need to do something special for MySQL to get PK generation
working correctly?
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
Coming in 2006 - an introduction to web applications using WebObjects
and Xcode http://www.global-village.net/wointro
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems. http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden