MYSQL & Row Level Locking 162
An anonymous reader noted a press release talking about how NuSphere is planning on adding row level locking to a version of MySQL due out 1Q 2001. Anyone who's used MySQL for anything with more then a few hundred thousand records (like, say, Slashdot to pick an arbitrary example) knows that this is the biggest shortcoming MySQL has... you can work around replication and transactions, but when your whole DB freezes because of one piddley write, its more then just a little annoying.
Re:fractured IP addressing scheme (Score:1)
Btw, Bababooey to you!
other Open Source DB's? (Score:1)
didn't borland open up their db?
JediLuke
Re:fractured IP addressing scheme (Score:1)
Stop the madness. (Score:1)
The impression I get is there are DBAs out there who don't know anything else but the DB they were raised on let alone how to learn a new one. And there are people out there that believe anythign their highly paid DBA tells them. This is just the only way I'm able to rationalize anyone would suggest mom and pop PC retailer on the net should pay 3-15k on a database that may never see more than a couple thousand records. Esp when there are alternatives that are in some cases free that give them the functionality they need without starting them off in the red.
I think the quasi proffesionals who fail to realize the importance of mysql in the industry aren't real proffesionals at all.
P.S. didn't mean to not include any of the other free databases out there but I could not call myself an expert in them yet so I cannot argue there strengths as well as I could mysql/oracle/mssql.
Re:SHOW ME THE CODE (Score:2)
These type of future announcements are unsettling. It is not really much different than Microsoft announcing products months to years in advance... It means nothing until there's something to release. Also, there's a specific danger here in the open source world... Imagine if someone were planning to add this functionality to the open source version of MySQL but didn't because of this announcement...Then imagine that the company doesn't follow through...I'm not saying we have any specific reason to distrust NuSphere...but...
Re:Why don't they strip down PostgreSQL instead ? (Score:1)
Re:Why lock a row when you can use a transaction? (Score:3)
Both schemes work well, but the former is potentially must faster because there is no lock contention: Nobody can ever be writing to the same piece of data, and nobody can ever be reading something that being written, unless explicitly specified in the isolation level.
Re:MySQL != DB (nitpicking the nitpicker) (Score:1)
Um, not it's not. It's called a spreadsheet application. When people say "an Excel spreadsheet", they mean a spreadsheet created with Excel. Likewise, when somebody says "an Oracle database", they don't mean the application, they mean the database which Oracle is managing.
Sometimes, a tool and its function ARE the same thing (Hand me that level, I don't think this table is level). The tool that is used for leveling tables is, indeed, called a level... but Oracle, PostgreSQL, and others are not called databases, they are called database management systems.
Re:MySQL *IS* a Database (Score:1)
I just feel that it is possible to logically isolate the business logic (design ) while physically deploying it with the data store (implentation).
And yes, the reasons to do that include time-to-market. (Valid reasons for doing so do not include cost. Two cheap application servers and one mid-range database server will often outperform one high-range database/app server, depending on where the particular application's bottleneck is.) And yes, there is a tradeoff. That was the point that I was trying to make, even if I did not express it as well as I would have liked. It is a tradeoff, and as long as you get what you want (TTM) and you know what you're paying (scalability and portability) then it's acceptable.
Re:Row locking is already implemented - here's how (Score:2)
Re:But SyBase? (Score:2)
But you are indeed right that page-level locking can be made to work with a sound design when architected by someone that knows how to cope with this.
I'd expect it to work out best if updates are performed via a TP monitor (like Tuxedo) or a message queueing system (ala MQSeries); if updates are grouped together so they are done by a central "update process," then the number of users involved becomes less relevant.
The problem is that page locking mandates having "highly-qualified Sybase experts;" it tends to be easier to keep in stock "somewhat qualified Oracle folk," which means that the assumption of "perpetually good design" is not safe...
Re:Why lock a row when you can use a transaction? (Score:1)
InterBase actually pioneered [interbase2000.org] the algorithm, which they call "multi-generational". Here's from an InterBase marketing blurb:
- Multi-Generational Architecture and Row-Level Locking
So InterBase deals with conflicts through row-level locking. So it does not deal explicitly with "dependencies", but rather the first transaction to commit succeeds; later transactions will fail because the versions timestamps will mismatch.InterBase's unique Multi-Generational Architecture (MGA, also known as versioning) enables clients to access an InterBase server with high concurrency and throughput. Also, the manner in which InterBase stores multiple versions of a given data record means that each client sees a consistent snapshot of the database. A client can update data without interfering with other clients' views of the data. These features result in DBMS technology that is well-suited to handle the short-lived transactions common in OLTP, concurrently with the longer-lived transactions of OLAP.
The server implements true row-level locks, using an optimistic locking strategy. Any number of clients can share read access on a record simultaneously; contention occurs only when two clients attempt to update the same record. In that case, the client who was first issuing an update operation has the option to commit the operation, and the server returns an error to the other client(s)
The combination of versioning and row-level locks give InterBase exceptional throughput and concurrency, when compared with RDBMS implementations that use page-locks or exclusive locks for reading. Readers never block writers, and writers never block readers.
InterBase handles all versioning and locking transparently and automatically for the application. This relieves the developer from the manual locking control required in some other RDBMS products. A number of optional parameters for transactions permit developers to specify other locking behavior.
A more technical summary can be found here [dbginc.com]
Re:Hmmm this sounds exciting (Score:2)
MySQL is the perfect datastore for a mostly read database that needs to be fast, and doesn't need the ACID properties of a real database. If this describes your application then MySQL is probably the best solution for the job.
I find it a little disturbing that MySQL feels the need to change their database so that it becomes something that it is not. When they are done they almost certainly will be left with something slower than they have now for their current target audience, and it probably won't be as fast as PostgreSQL or Interbase for their new target audience.
Re:Why don't they strip down PostgreSQL instead ? (Score:1)
MySQL should go away now... hasn't PostgreSQL surpassed it in performance (by recent benchmarks)? Data consistency should not be an issue with any db, no matter how piddling the content seems to be!
Re:Mistakes are hard to clean up (Score:1)
or M$ $QL $erver
Unfortunately, when you choose MySQL, you're locked in. But you could still port slash to PostgreSQL with some clever scripting. But why would you want to? Slash includes workarounds for MySQL's shortcomings that real RDBMS's don't have.
Re:MySQL != DB (Score:4)
Re:fractured IP addressing scheme (Score:1)
Cheers,
--fred
Re:The real reason for this... (Score:2)
Re:ooooh yea. lets wait. . . (Score:1)
Postgres [postgresql.org] is not GPL'd - it's a BSD license, which makes it far friendlier to embedded systems developers than MySQL (eg: Cobalt RAQs).
Enough already (Score:5)
Why is it that MySQL receives so much converage for adding 'features' that most/all other DBMSs take for granted.
It's not even releational yet, supports the references keywork, does nothing. Recently found a MySQL database with duplicate primary keys in a table ???!!
If you need and OpenSource RDBMS PostgreSQL ( Also Open Source ) has had most if not all the important features for years. Yes it may be slightly slower, but it won't corrupt your data randomly, you don't need to take it offline to backup and it supports a proper set of SQL.
But SyBase? (Score:4)
and it seems to me that this should be something 'standard' with any DB that is expected to handle non-trival amounts of data.
Sybase has been the "standard" non-mainframe database in the financial world for many years, storing non-trivial volumes of data, and only in it's most recent version (11.9.2) has it had row-level locking.
In fact, row-level locking is a requirement driven more by the number of users, size of each row, size of pages, and the frequency and speed with which any given row will be updated.
Where users=>many, rowsize=>small, pagesize=>large, and update frequency/volume=>high, then row rather than page locking is required to keep performance high - otherwise processes get blocked waiting for a page to be released.
Where the above are not met, then page-level locking is fine - as long as your design is sound. Row level locking is not a substitute for a good design.
Re:Gee...high tech features here, eh? (Score:1)
pre-announced these features, and say they
will achieve them by using Berkeley DB as
a sort of wrapper. I believe there's a
version in beta now.
Re:MySQL *IS* a Database (Score:1)
> stored procedures and triggers are NOT
> requirements of a database.
That's a red herring - the whole point of the
discussion is MySQL's claim to be a _relational_
database, which it isn't.
> A few good reasons not to use triggers and
> stored procedures are:
>
> 1) Performance
> 2) Portability
Them ain't a few, them's a couple, and half
of them are wrong. When you use triggers and
stored procedures, you aren't interested in
portability. If portability is an overriding
concern, you'd use ODBC and never use the
server's native API at all. Of course, you'd
do that at the expense of performance, which
brings up the (dead wrong) second point.
Performance is exactly why you use triggers
and stored procedures. When used appropriately,
there isn't any way to gain the performance
enhancements they offer without using them,
unless you like the idea of making all of your
clients also behave as multi-threaded servers.
It doesn't make a bit of difference where the
code is stored, it's where it is executed that
makes a difference in performance. If you
don't grok that, you're missing one of the
main benefits of client/server architecture
versus client file-sharing.
Your column flag work-around for row-level
locking provides a textbook example of how to
slow a database to a crawl. You need to read
a good book on concurrency and deadlocks to
know why you're scheme is disaster bound, e.g.
(one of many) what happens when one of your
clients crash after setting and before
resetting your flag?
Re:Hmmm this sounds exciting (Score:2)
--
MySQL Isn't A Heavyweight Database System (Score:2)
Both are excellent database systems that support many more advanced features than just row-level locking, such as real-time English Natural Language Query systems.
Remember: You get what you pay for. Don't use a free database system then compain when it doesn't scale as well as the big boys
On a side note, IMHO the Dynamic locking on SQL Server works really well -- automatically adjusts to table, page, and row-level locking on the fly.
-----
Re:MySQL *IS* a Database (Score:1)
Triggers and stored procedures provide increased performance in specific situations. Thses usually involve very few client conections (not really enterprise class apps). Replication and mirroring aside (which you would only need for back up and fault tolerance, if you designed right) there should be only *1* data store, however, there are multiple clients. By concentrating your logic in the data store you are creating a bottle-neck instead of distributing the load across clients. This is why triggers and stored procedures are generally bad (for large apps). If you can't understand this, then there is nothing that can be done for you. See above discussion. Distributing the load across *multiple* clients as opposed to a *single* data store REDUCES lag. As far as a client dying unexpectedly, see the original post, use a timestamp for the flag and use a timeout. If the lock's expired then tough shit for whomever set it.
Re:MySQL != DB (Score:1)
Re:fractured IP addressing scheme (Score:1)
Locking in a database is not implemented by 'locking pointers'. The 'pointer' address can be
used to generate a lock object.
Re:Mistakes are hard to clean up (Score:1)
Even though I'm not familiar with Slashcode, i'm sure it would not be hard to transfer to another database package because of the Perl DBI 's generic database interface. As far as I know, you would just have to change up the connection code if Slashcode were written well.
Re:MySQL is a DB (Score:2)
wow (Score:3)
I can see why this is news.
This is what's happening (Score:1)
mysql does support Transactions (Score:1)
Access (Score:1)
Of course, I have built web services on MySQL, not on Access.
MySQL != DB (Score:2)
Re:MySQL *IS* a Database (Score:2)
But they can be rather useful tools. They can facilitate information hiding, and provide an object-like (keeping some behavior with the data) feel to the application. They can also make management of multiple systems and applications somewhat easier.
Let's say your company decides that the USPS just isn't good enough. The new policy is that all US customers get their shipments via UPS or FedEX. Suddenly all those "PO Box" addresses in the database are no longer valid.
So what do you do? Do you rewrite that legacy order entry application used by division X? As well as the new order entry app used by the rest of the company? (And let's not get into why division X can't use the new app, it's not under your control. Life is unfair. Other people get their way sometime, even if they're idiots. Just deal with it.) What about the new Web interface that the Consumer Technologies department has just released? Let's not forget the software that the sales department uses to give price quotes. And the Customer Service app. How many developers will that take? How long will it take to roll out after QA approves all of the changes? How many desktops, laptops, and servers are affected?
Personally, I'd just rather change one trigger or stored procedure on one or two servers. OK, so the QA people still have to test all of those different apps, but they'd have to anyway. This is still the easiest way to get it done.
Can stored procs and triggers affect performance? Yes. Especially poorly written triggers.
Can stored procs make it more difficult to migrate to another database? Yes, while making it easier to migrate the UI portion of the app to other platforms.
It's a tradeoff. Often it makes more sense to put some (all?) of the business/application logic into the database. It doesn't always make sense. That's a design decision. While designing a system, I'd rather have more tools at my disposal, and pick the best combination, rather than having my hands tied.
Re:Hmmm this sounds exciting (Score:2)
Sounds like it is about time (Score:1)
Any ideas on why it took so long to add this?
Eric Gearman
--
MySQl concurrent user limitation?? (Score:1)
Hmmm this sounds exciting (Score:1)
Re:Sounds like it is about time (Score:2)
you get what you pay for
Oh really [postgresql.org]?
"Free your mind and your ass will follow"
Re:Enough with the database stories! (Score:1)
hey, check it out, man, meeptool featured the Battlin' Siezure Robots [memepool.com]! what next, goatse.cx?!!! heh heh heh.
Re:ooooh yea. lets wait. . . (Score:1)
Why wait for the next version? (Score:3)
Or, if you need something quick and dirty, go with the quickest and dirtiest of them all: Access. (that is, if you have a Windows box available)
I'm being serious. MySQL has it's good uses, but if there's severe limitations in its use, why wait for it to get better? (if it will at all) I don't believe that the announcement is vaporware, but there's more problems with MySQL for serious usage than row-level locking. If you're not happy with it right now, I can point you to a handful of solutions that will fit your needs one way or another.
And if you are happy with it right now... well, then nevermind, have a nice day.
MySQL *IS* a Database (Score:1)
1) Reliably store data
2) Reliably retrieve data
Replication, transactions, row-level locking, stored procedures and triggers are NOT requirements of a database.
However, MySQL *DOES* have full transaction support through the use of Berkley DB tables.
A few good reasons not to use triggers and stored procedures are:
1) Performance
2) Portability
Application code should be kept in the application, not the data store (which is what a database is). Those of you who feel that MySQL is not a real DB do not know what a real DB is. Your views are tainted by Oracle marketing.
As far as row level locking goes; It can be easily implemented throught the use of a flag column in your tables. This can be used for passive or active locks. This flag could be:
1) userID
2) incremented integer
3) time-out (DATETIME)
4) userID+time-out
Consider reading a products [current] documentation before you say what it can and cannot do.
-- (my apologies for this semi-rant and poor speling) --
Re:MySQL *IS* a Database (Score:1)
Re:MySQL is _NOT_ a database (Score:1)
Re: So is NOTEPAD.EXE a database? (Score:1)
Hey, MSSQL6.5 doesn't support row-level locking (well, it does insert-row-level locking, but i don't think it supports it on updates, for example)
Re:Why lock a row when you can use a transaction? (Score:1)
transactions can let the dbms decide, but the dbms doesn't always pick the right locking mechanism for your application. How long do you hold the lock? On an insert, do you want a whole page locked, or just a single row?
Row level locking can speed up concurency. Some DBMSs will escalate the locks for you, and some do a better job than others. But to say you never have a reason to lock, it's simply silly. Try writing a 200+ client application and not worying about locking.
Re:Why don't they strip down PostgreSQL instead ? (Score:2)
That is the #1 gripe we have against PostGreSQL, and one of the main reasons we're using MySQL instead. While feature-rich, the design of the engine interferes with what we understand as good data model architecture.
The gripes against MySQL are missing features - better locking, transactions, stored procedures, and triggers. We learn to cope with them. And PostGreSQL doesn't offer stored procs at all, and requires triggers to be written in C - ugly, ugly, ugly. It ain't that much better, and in some ways it's worse.
--
Update Slash then? (Score:1)
Re:Row locking is already implemented - here's how (Score:1)
Re:But SyBase? (Score:2)
"NuSphere, a Progress Software Company" (Score:2)
Re:MySQL *IS* a Database (Score:2)
Re:MySQL is _NOT_ a database (Score:1)
You can't run MSSQL in a colo facility. Remote access is of the "not enough, too slow" variety. Sorry, the Russian Hacker variety doesn't count. I know 'cause a friend whose company got bought out ended up having to make a colo decision (they wanted his servers in their colo), and rather than do that with NT+IIS+ASP+SQL Server, he migrated to RedHat+Apache+Perl+MySQL, and has an order of magnitude improved performance.
Re:Enough already (Score:1)
Mmmm. In 14 months I've never lost any data to MySQL. And I don't have a *shitload* of data, but enough to worry about, nonetheless.
> you don't need to take it offline to backup
BACKUP TABLE, anyone?
> and it supports a proper set of SQL
At the end of the day, I use MySQL because it suits what I need to use it for. So do thousands of others; but then, if they need to use a different feature, then choose another system. There will always be trade-offs.
-flec
Re:MySQL concurrent user limitation?? (Score:1)
safe_mysqld -Omax_connections=1000 ?
Now, I've not had to use more than that, yet, but still with about 600 (active) connections I found no issues with speed.
Re:Enough already (Score:3)
Then, a story is posted saying how MySQL will address these issues in the next 6 months, and all of a sudden the same people start running around screaming at us to shut up, and why bother in the first place, because putting such functionality in is a stupid idea anyway.
Plain and simple, MySQL is faster and has multi-user (at the DBA level) support. It means different applications only get access to the SQL tables they need. It means you can give users their own databases easily. It's incredibly fast. All it lacks is a little transactional auditability and data integrity - for most applications run out here on the net, this isn't an issue, and if it is, you can normally work your way around it.
Now, MySQL is going to get the features that will make it fit snugly into the low-end RDBMS range of open source DBs, and we're going to be happy, because it means our data is going to be safe. We're even prepared to happily take a hefty performance hit if need be.
Unfortunately though, people seem to take this matter as seriosuly as their religion, and we end up with this sort of argument. This is all seeded from either jealousy, bigotry, or both. The argument as to which DB is best is one for you to decide all by yourself.
However, when I saw this story, a smile appeared on my face, because it's the one thing, and the only thing, that I actually wanted MySQL to incorporate at some point. Sure, the model and philosophy sounds a bit kludgey but I don't care because as long as it works, I'll be happy.
Re:Why don't they strip down PostgreSQL instead ? (Score:2)
If you haven't looked, PostgreSQL 7.0.2 is worth a look if the last version your worked with was 6.x.
Re:But SyBase? (Score:1)
This is a real, high performance, commercial quaility database. Why bother playing with a MySQL toy when you can have the real thing?
Re:MySQL != DB (Score:2)
And Excel is called a spreadsheet, even though it really is an application that lets a user edit a spreadsheet. What's your point?
People mix and match levels of metaphor all the time. Sometimes, a tool and its function ARE the same thing (Hand me that level, I don't think this table is level).
If your mother wants to use the Internet because "it's included in her iMac," what's the point in correcting that level of semantics?
Why don't they strip down PostgreSQL instead ? (Score:5)
I never understood why people have to re-invent the wheel every time.
Linux is slowly re-implementing what FreeBSD already have, (vm, or kqueue), and now MySQL is trying to turn itself into a real DB, althought it doesn't have the the foundations for this.
If someone (ie slashdot) have a problem with the absence of row-locking, then he shouldn't use MySQL at the first place. Maybe it is just me, but I would be more enthousiast to a 'optimise PostgreSQL' then to a 'compexify MySQL'. Probably because premature optimisation is the root of all evils.
Or maybe because the concept of a non-ACID (basic ?
Cheers,
--fred
Why not.. (Score:2)
Why lock a row when you can use a transaction? (Score:4)
MS SQL (Score:2)
MS SQL may or may not be a good database (it must be better than Access, right?
- Scott
------
Scott Stevenson
Re:MySQL != DB (Score:2)
Note: I do not use MySQL, I use postgreSQL. Of course, I frequently use Perl to interface with it, and that's not really a programming language, so what do I know, huh?
MySQL IS a database (Score:2)
Again IMHO, transactions are a crutch. At times, they are a useful crutch, but a crutch nonetheless. There is an excellent chapter (appendix?) in the MySQL manual discussing the absence of transactions and some of the workarounds.
Row locking is a whole 'nother issue. While you can implement row-locking with atomic SQL statements, it is more efficient for the DBM to do this. Besides, locks need to go away when the connection goes away, which manual locks do not. MySQL could fix this by allowing a connection to provide a query or queries that the server should always execute when the connection is closed, so that if something disrupts the connection, manual locks can be removed...
Re:But SyBase? (Score:2)
Sounds like the instructions on those assemble-it-all-yourself kits from K-Mart...
"Please be happy to waiting for most honorable deadlock to join the ancestors." ?
--
Re:Why wait for the next version? (Score:2)
I'd use it as a development tool, though. It takes bona fide SQL queries, and it is a database, so you can build some stuff around that and then just use another database as the real live thing.
MySQL + PHP is great for websites. But then again, if that's true, obviously everyone's learned to live without transactions and row-level locking, eh? And if you need something more powerful than the current MySQL... well then you might as well step up to Oracle at that point anyway, rather than wait for a better MySQL...
Re:Why don't they strip down PostgreSQL instead ? (Score:2)
And PostGreSQL doesn't offer stored procs at all, and requires triggers to be written in C - ugly, ugly, ugly
You can write them in TCL, Perl, SQL, PSQL,
Granted, non-C stored-procedure support is very limited (compared to Sybase or Oracle).
As other posted, a "server" can maintain multiple DB connections, though to my knowledge it is still impossible to perform inter-DB selections.. I don't think that Mysql can do this though, so the point it moot.
-Michael
SHOW ME THE CODE (Score:2)
Can we please not give notice/publicity to announcement-of-intent from companies? Show code, then collect your kudos and marketroid credits.
Unchecked, this could turn into a constant shower of press releases from hundreds of companies wanting a piece of the Linux pie. And when the majority amount to nothing, as most of these seem to, it just makes Linux look bad.
Re:The real reason for this... (Score:2)
Re:Enough already (Score:2)
Because slashdot is built upon MySQL. Slashdot editors post stuff that interest them. MySQL is obviuously and rightfully one of those topics.
Cheers,
--fred
Row locking is already implemented - here's how: (Score:3)
To do that, just create a new table called "locks" with 2 fields:
resource_name: char[80]
user_name: char[20]
and create a unique index on resource_name.
In order to perform a lock on a specific resource, like a table row, you just have to do this:
insert into locks values( "TABLENAME:ROWID", "USERID" );
Where TABLENAME is the name of the table and ROWNUNBER is the number/ID of the row you wish to lock.If this resource wasn't locked yet, then a new row will be inserted in the locks table.
However, if this resource was already locked, then it would create a duplicate entry in the resource_name column that would violate the unique index.
This way, the unique index would stop INSERT from inserting this line into the locks table.
You can create a very simple lock( table, rowid ) using this insert, that would return true if INSERT worked OK and false when INSERT fails.
To unlock a resource, you just have to remove the respective row from the locks table.
delete from locks where resource_name="TABLE:ROWID";
The user_name field can be used to clean all locks that were active when a user connection drops.
delete from locks WHERE user_name="USER";
This works becuase insert will check for duplicates in unique indexes in an atomic operation.
You simply cannot have two persons inserting the same lock in the locks table, because insert is ATOMIC.
fjp_at_linuxstart.com
Re:MySQL != DB (Score:2)
My bad... I was under the impression somehow that it was Public Domain, but I guess it's not.
"Free your mind and your ass will follow"
Re:But SyBase? (Score:2)
I would submit that ANY non-trivial RDBMS requires a highly-qualified expert-level DBA/Database Designer. Personally, I feel that Sybase is much more administrator friendly than Oracle. A "somewhat-qualified" Sybase DBA is less likely to hose the system than a "somewhat-qualified" Oracle DBA would be. A database designer has to take the idosyncracities of his target database into account when designing the schema -- this is true regardless of the RDBMS platform he is targeting. Unfortunatly, good database designers are a rare breed. It dosn't seem to be a subject which gets much attention in CS programs at the undergraduate level.
Re:Why don't they strip down PostgreSQL instead ? (Score:2)
It's like saying, why bother with XML when we have relational database at our disposal.. Not all solutions fit all problems. For some people, a simple hash-table lookup is all that's needed (berkly hashes, for example). For others, a simplied data comparator is needed, e.g. MySql. For others, data-integrity is needed, i.e. Postgres or commercial apps.
Mysql has speed as a stated goal.. They've hinted that they might implement Foreign keys soon, but ONLY as an option that can be implemented in such a way that it does not hinder performance one iota (see goal number 1).
It's not reinventing the wheel.. It's learning that not all wheels fit all vehicles (see bicycle verses formula-one racer).
I'm not sure about all the stated goals of PostgreSQL, but it seems to have, as it's goal to be a top performer with standards compliance - To be an all around champ. It can't win on all fronts / you have to make design choices. Postgres opted for version controlling rows instead of row-locking, it opted for one process per client instead of multi-threading, it opted for one file per meta data-entity, etc.
I use to use Mysql until my work expressed concerns for Data-Integrity. Now we use a mix of Oracle and Postgres (depending on the target app). The inability to perform several basic standards-based SQL operations in Mysql was very daunting. Roll-back was a critical issue, for example.
But, if I were a small-time ISP selling PHP and DB services, I'd go for Mysql.. Less overhead for me in terms of memory and CPU cycles.
In the Mysql documentation, it says that you can "learn" to work around the limitations of Mysql. Meaning that there are successful programming practices. Such is the case with Slashcode I'm sure. But from a business standpoint, it's difficult to justify the use of limiting technology, when there are other alternatives..
Performance-wise, Mysql has little overhead, and a small incremental cost, where-as Postgres has a decent amount of overhead and a moderate incremental cost. You'd think Mysql would come out on top, but Benchmarks [an old slashdot post] suggest that Mysql is unstable beyond a certain load level. Beyond that, if you use table locking, then you do not scale at all. Row-locking is slow for the big guys like Sybase (which I know much about). You take a hit on EVERY access just so that you can claim that you have regional locking, which facilitates concurrent access. Postgres side-stepped the issue (beit it for good or bad) through version-based rows, so they get to have a closer-to-linear performance curve.
Re:Hmmm this sounds exciting (Score:2)
Most ascii file schemes end up eating some shit usually. And Access
--
Re:Hmmm this sounds exciting (Score:2)
I have no idea and it bugs me too that the
If you don't want to use it, noone is forcing you people.
--
Re:Um, where have you been? (Score:2)
Wait! I wasn't ready.
Dang, I'm always missing out.
Pete
Re:Sounds like it is about time (Score:2)
Here is mine:
MySQL have been designed to be lightweight and fast for non-critical applications.
Users of MySQL have happilly fit this definition, but now, more and more of them are going higher profile. Instead of migrating to a more adequate tool, they want to migrate the tool itself. IMHO, a bad idea.
Locking is definitely not a trivial subject. I doubt that it can/should be implemented as an afterthought.
Cheers,
--fred
The real reason for this... (Score:2)
Speaking of which, has anyone test-driven the open-source version of Interbase (In either the Windows or the Linux)? How good is the source code?
Slashdot always freezes because of one piddly post (Score:4)
Of course, there's Bender: promises, promises. However, Bender still puts random spaces into long A HREF tags, so I can't refer to long URLs like Boston Globe pages. For such a good discussion site, Slashdot has some bad code issues.
Re:Pony up for Oracle (Score:2)
Re:MySQL *IS* a Database (Score:2)
Re:This is good (Score:2)
Yeah, right. tell that to MS. I think that we can be clear that what you (don't) pay for a product is not necessarily related to the value/power of the product.
You don't get what you pay for, you get what you choose, then you pay for your choice.
mySQL is not (currently) being touted as a real alternative to Oracle for large databases. It has it's uses, and that's not one of them.
`ø,,ø`ø,,ø!
Why lock at all? (Score:2)
Interbase doesn't lock anything, it simply makes a new version of the row that becomes the current when the transaction is comitted.
MySQL is, IMHO, a piece of crud compared to ANY real DBMS, as it doesn't have any of the features that are needed in a database (like foreign key constraints or transactions)
That said, if you don't compare MySQL to flat files it does come out on top, it's pretty neat as a structured storage, but it sucks so badly when you have more than one table that needs to be in a consistent state.
InterBase is *VERY* nice and pretty fast, but it has rusted quite a bit from v4 to v6 and it's DBD was completely useless until quite resently, after lots of hacking it rarely deadlocks now, but it (IB) is quickly on it's way out of our setup.
For a REAL database that scales the pants off anything that is Free software today (it can support SAP R/3 with it's 16000 tables) check out SAPDB:
http://www.sap-ag.de/solutions/technology/sapdb
I's set to come out under GPL(!) in April next year, but I've been using it for a while now and it seems to be a *really* nice DBMS.
Re:Row locking is already implemented - here's how (Score:4)
Re:MySQL *IS* a Database (Score:2)
Re:MySQL *IS* a Database (Score:2)
Re:Sounds like it is about time (Score:3)
Well, if you read the MySQL home page, they clearly state that they created a database for their own use. Apparently, they didn't need the feature.
Not everyone needs a total heavyweight database system. Since I'm the only one accessing my databases, I didn't even realize it didn't do row-level locking.
Everyone is so harsh toward people who write a product and release it for free...I'm just glad these tools are available because it allows me to experiment and learn without spending thousands of dollars for hardware and software.
--RJ
Re:Why not.. (Score:2)
If MySQL doesn't fit your needs, don't use it.
Grammar Police (Score:2)
I can deal with slashdot repeating the same story a few times, but when CmdrTaco repeatedly abuses the English language, it's more than just a little annoying. :-P
Re:Sounds like it is about time (Score:3)
Here's my guesswork:
Probably until recently, the average MySQL user hasn't taken it seriously enough to rely on it for large amounts of data, so it was unneccesary or perhaps even undesirable - row level locking is _slower_ to a point for small data sets.
More recently though, I expect more people are starting to use MySQL "seriously" as it matures, storing much more data and suddenly the need for row locking shows itself more clearly.
It will still be slower for very small data sets, but thats a small price to pay for the scalability benefits row locking brings.
fractured IP addressing scheme (Score:3)
Re:MySQL != DB (Score:2)
PostgreSQL is public domain... and it is actually a real RDBMS!
"Free your mind and your ass will follow"
SAP isn't a database.. (Score:2)
It's a application built on top of a database. and 1600000 tables is hardly a fair comparison. You can have 10,000 tables in mysql and not have a problem.
SAP runs great w/MS SQL Server or Oracle.
--------------------
I know I could use (Score:2)
how does PostgreSQL compare to Firebird? (Score:2)
Anyone got any experience with both they'd like to share?
How do they compare with MySQL w/ Berkeley DB tables?