Cayenne, MySQL and PK generation strategies

[Marek Stanisław Wawrzyczny <ma...@internode.on.net>]

Hi all,

It has been a while since I last posted in this forum.

First, a bit of a background. We have an existing MySQL schema that was driven by a number of WebObjects applications. These obviously rely on EOF to handle PK generation.
We have decided to refactor the schema in a new database. The new web applications are Cayenne based.

We now have a bit of discussion on how to handle PK generation in the new schema. My personal preference is auto incrementing PK columns. It certainly allows for a more generic approach to the handling of primary keys - it is not necessary for people to be aware of any additional infrastructure should records be inserted outside of any applications.

Others have expressed concern with MySQL handling of the LAST_INSERT_ID() functionality where there used to be (still is?) a problem with MySQL's threading implementation in high concurrency/volume environments. (I presume this is precisely the mechanism used by Cayenne to populate IDs within the Entities?)
Another cited advantage being suggested is that this approach allows for a vendor-independent PK generation strategy.

I guess my vested interest here is to dismiss arguments against the use of the auto incrementing PK strategy :) but that said, I'm very interested to hear whether anyone has done any performance analysis or has had particular problems with either strategy.


Cheers,

Marek Wawrzyczny
ish group

Re: Cayenne, MySQL and PK generation strategies

[Joe Baldwin <jf...@earthlink.net>]

Marek,

We are using Cayenne, MySQL with InnoDB storage engine, both configured for MySQL PK generation.  We had some problems which I am convinced was associated with the documented table locking bugs in early versions of MySQL 5.0.x.  It appears that there may have been multiple problems which do seem have been resolved in MySQL 5.1.x  (You can read up on this by googling the MySQL bug reports.)

With respect to these issues, the most current stable version (currently 5.1.49) is recommended.  (Warning: there appears to be some sort of conceptual 'holy-war' with 5.0.x vs the 5.1.x zealots; so I would just stick with the most recent stable version recommendation and the associated bug fixes as the argument)

I cannot speak to comparing the Cayenne PK generation strategy vs MySQL, but I believe both are very reliable.  I think that the only reason that you might consider using MySQL vs Cayenne PK-generation is if you are ever going to perform non-Cayenne inserts.  If you are always going to use an ORM, then you can use either method.

If you do any performance tests comparing the two, I would be interested in hearing the results.

Hope this helps,
Joe



On Jul 26, 2010, at 10:40 PM, Marek Stanisław Wawrzyczny wrote:

> Hi all,
> 
> It has been a while since I last posted in this forum.
> 
> First, a bit of a background. We have an existing MySQL schema that was driven by a number of WebObjects applications. These obviously rely on EOF to handle PK generation.
> We have decided to refactor the schema in a new database. The new web applications are Cayenne based.
> 
> We now have a bit of discussion on how to handle PK generation in the new schema. My personal preference is auto incrementing PK columns. It certainly allows for a more generic approach to the handling of primary keys - it is not necessary for people to be aware of any additional infrastructure should records be inserted outside of any applications.
> 
> Others have expressed concern with MySQL handling of the LAST_INSERT_ID() functionality where there used to be (still is?) a problem with MySQL's threading implementation in high concurrency/volume environments. (I presume this is precisely the mechanism used by Cayenne to populate IDs within the Entities?)
> Another cited advantage being suggested is that this approach allows for a vendor-independent PK generation strategy.
> 
> I guess my vested interest here is to dismiss arguments against the use of the auto incrementing PK strategy :) but that said, I'm very interested to hear whether anyone has done any performance analysis or has had particular problems with either strategy.
> 
> 
> Cheers,
> 
> Marek Wawrzyczny
> ish group

Re: Cayenne, MySQL and PK generation strategies

[Andrus Adamchik <an...@objectstyle.org>]

On Jul 27, 2010, at 5:40 AM, Marek Stanisław Wawrzyczny wrote:

> We now have a bit of discussion on how to handle PK generation in  
> the new schema. My personal preference is auto incrementing PK  
> columns. It certainly allows for a more generic approach to the  
> handling of primary keys - it is not necessary for people to be  
> aware of any additional infrastructure should records be inserted  
> outside of any applications.

I am using auto-increment with MySQL (InnoDB/MyISAM). Haven't seen any  
problems so far (which may not mean much).

> Others have expressed concern with MySQL handling of the  
> LAST_INSERT_ID() functionality where there used to be (still is?) a  
> problem with MySQL's threading implementation in high concurrency/ 
> volume environments. (I presume this is precisely the mechanism used  
> by Cayenne to populate IDs within the Entities?)

This mechanism is abstracted by MySQL JDBC driver, so Cayenne is  
unaware of it and is using standard JDBC API to get the keys. The  
underlying driver is based on LAST_INSERT_ID() of course. According to  
MySQL docs it should handle concurrent inserts just fine. Not sure if  
the reality is different. Googling only shows some pretty old  
complaints about concurrent inserts on MyISAM.

Andrus