Hi --

We're looking at redesigning an existing web site and in the process porting it to Asp.net 2.0 and LLBLGen 2.x.

For budget reasons, we'll probably end up using MySQL 5.0. (We usually use SQL Server.)

A couple of questions:

1. Are there any known issues with the CoreLab MySQL driver and LLBLGen? Particularly for a heavier (several hundred concurrent users) traffic web site.

2. For future scalability, we might have to go to the master-slave MySQL architecture where basically all reads are done against a "slave" and all writes are done to the master database server. (For more info, see this blog post: http://krow.livejournal.com/434562.html). How would you all approach this?

Typically we use self servicing, but I'm thinking that adapter would be a better approach where we have a DataAccessAdapter for the master db and a separate DataAccessAdapter for the slave/read-only database.

I played around with creating a second dataaccess adapter and it was very straightforward. 2 examples follow:

Writer/Update code:

        using (IDataAccessAdapter writer = new DataAccessAdapter(false))
        {
            writer.SaveEntityCollection(this.LLBLGenProDataSource2Clients.EntityCollection);
        }

Reader Code:

        IRelationPredicateBucket relationBucket = new RelationPredicateBucket();
        relationBucket.PredicateExpression.Add(ClientsFields.Active == true);       

        IDataAccessAdapter reader = new DataReaderAdapter(false);
        reader.FetchEntityCollection(this.LLBLGenProDataSource2Clients.EntityCollection, relationBucket);

Has anyone used LLBLGen with MySQL and a master-slave set up before? If so, were there any gotchas?

Thanks.

I don't have experience with MySQL, but if you want a free database , you might as well check the SQL Server Express Edition, or the PostgreSQL if you want an Open Source database.

Walaa wrote:

I don't have experience with MySQL, but if you want a free database , you might as well check the SQL Server Express Edition, or the PostgreSQL if you want an Open Source database.

I can vouch for SQL Server 2005 Express as I use it for many production projects. It has absolutely everything we need for all our solutions. The only thing that I found that it doesn't have that I would like to have is built in web service endpoint support, but that's not even a database thing anyhow! There is a database max size of 4GB, but these apps will never get near that.

I highly recommend it's use... and it works great with LLBLGen.

Walaa wrote:

I don't have experience with MySQL, but if you want a free database , you might as well check the SQL Server Express Edition, or the PostgreSQL if you want an Open Source database.

Thanks for the info.

As far as why MySQL, the hosting provider has experience with managing MySQL and they will be responsible for administering it.

Also, I think we'll run into the disk space limits with SQL Server Express and it won't scale past 1 database server.

Thanks.

I worked with php and mysql before. mysql 5 is very solid for InnoDB. this contains foreign key constraints which is great. the stored procs is still in its infancy, so I would avoid them.

I cannot speak to the 3rd party driver that LLBL utilizes.

Postgres and MySQL are very simialr, so if your DBAs know MySQL, they will have no problem with Postgres. This doesn't require a seperate driver.

jmeckley wrote:

I worked with php and mysql before. mysql 5 is very solid for InnoDB. this contains foreign key constraints which is great. the stored procs is still in its infancy, so I would avoid them.

Also transaction support isn't good. InnoDB does solve some things but MySql can be left in a dangling state after something goes wrong during a transaction. I seriously doubt why one would ever opt for MySql when you have alternatives like PostgreSql or even firebird. Sure, selects are pretty fast in mysql, but it comes at a price: the rest isn't up to par. So if your app does a lot of selects and not that much inserts and if it inserts, it doesn't do multi-table inserts, sure, why not use mysql. But if you use the db for more than that, I'd advice to use a different db.

Otis wrote:

jmeckley wrote:

I worked with php and mysql before. mysql 5 is very solid for InnoDB. this contains foreign key constraints which is great. the stored procs is still in its infancy, so I would avoid them.

Also transaction support isn't good. InnoDB does solve some things but MySql can be left in a dangling state after something goes wrong during a transaction. I seriously doubt why one would ever opt for MySql when you have alternatives like PostgreSql or even firebird. Sure, selects are pretty fast in mysql, but it comes at a price: the rest isn't up to par. So if your app does a lot of selects and not that much inserts and if it inserts, it doesn't do multi-table inserts, sure, why not use mysql. But if you use the db for more than that, I'd advice to use a different db.

Hi,

I recently asked about opensource dbs where I said postgresql or firebird for possibly a very large database. You pointed me towards postgres (which I installed and have played around with).

I then looked at MySQL and it seemed on the surface that it might be a better choice (more documentation, lots of webcasts and examples, they are developing their own .net connector etc). Considering it has been used by popular sites yahoo, google and friendster being just 3 of them is it really that bad when put against postgresql?

Thanks,

John

MySql is no match for postgresql when it comes to reliability, solidness and other must-have features of a mature database engine.

Sure, some large companies use it, but mostly for readmany-write-hardly websites where transactions for example are not used or not that important.

But it's your data, of course, so it's up to you what you'd use to make sure your data stays your data and it indeed is a reliable set of data, not data which state might be 'undefined' because some transaction went haywire and you won't be able to figure that out. .

aha! now i think i've found the right place to ask my burning question:

does PostgreSQL support lightweight transactions via .NET 2.0 TransactionScope the way SQLServer does?

thanks,

KB

P.S. my general experience confirms your suggestion that PostgreSQL is a much better database than MySQL, especially given spatial (PostGIS) and statistical (R+) object-relational query extensions. not yet had the chance to hit postgres from .NET

hey otis, why aren't you listed here? you should be, no?

http://npgsql.projects.postgresql.org/projectswhichusenpgsql.html

cheers,

KB

Only sqlserver 2005 supports lightweigth transactions, and oracle said in 10gR2 it will be present as well, but that's about it. All other databases don't support it now and will likely also not support it in the future.

System.Transactions then falls back onto MTS/COM+ transactions and if the db doesn't support these as well(and most oss db's don't) then you can't use system.transactions at all: use ado.net transactions instead.

Good point about the site list, I'll make sure we're getting on that list