mySQL glitch fixed?

[Anon]

I was talking to another developer and he mentioned the reason he develops a lot in postgresSQL is because at the time mySQL had a glitch whereby when a record was being edited it would lock at the table level not the record level.

Does anyone know if this glitch has been fixed?

J

[[deleted]]

That is not a glitch/bug, that was by done design. (I never understood the reasoning for this)

All versions before 4.0 have table-level locking. After 4.0 they finally have row-level-locking.

[Anon]

Don't forget though, that postgresql has MVCC, which makes even row level locking look primitive.

http://www.ca.postgresql.org/users-lounge/docs/7.1/user/mvcc.html

Basically, you don't generally have to do any locking in postgresql, as the database does it for you.

[[deleted]]

Transactions rule :-)

[Anon]

I was helping a friend convert a batch running on NT into a linux / PHP process that takes data from a winnt SQL server to a solaris box with Oracle. In discussing how the batch file ran and worked, I found out that it took the better part of a day to run. I asked him if he was commiting the data to the Oracle server as one large transaction or one row at a time. He then asked me "What's a transaction?"

It's amazing to me how many klunky database apps are written all over the place by folks who have veyr little understanding of databases, and approach everything as though a database was just a large ISAM file. (which is pretty much what MySQL is)

[[deleted]]

lol.. so true, so true.

And 'bad' databases like MySQL don't help much to change that :-)

[Anon]

Hey you guys! Stop making fun of mySQL!

Ok seriously, Im building a small application using mySQL and PHP. Hopefully it will have about 100,000 records when its finished, with a lot of traffic.

My host only supports mySQL, but should I consider a different database platform?

As far as interaction goes all that happens is that a user looks up a record and gets the record. I might add that a counter updates a column in the database +1 for each view, but that would be the most transactionwise I would do.

Ideas? Comments?

[[deleted]]

In your case, MySQL will do nicely.

About the updates, that will not be a big problem, but it will slow you down quite a bit. MySQL (if it is pre-4.0) will lock the entire table during the update. Luckily your update is very straightforward, so it shouldn't take too long.

But I would suggest that you do some benchmarking to see if your ideas are feasable with MySQL (write a simple version of what you want, then beat it over the head with a massive load of requests)

[Anon]

Ok, so what would you guys consider a heavy load for a mysql database? 1000 hits per minute?

Also, if it wasnt for the update, would mySQL be as good as any other database doing this?

I heard that it would certainly be faster than oracle, simply because the task was so simple.

[[deleted]]

The number of hits/minute is not important, because that will be deicded by how fast MYSQL can process the queries.

The problem lies with concurrency. If you expect to get an average of 20 concurrent users, beat your MySQL at double that amount, see how it copes with 40 concurrent users or even 60 or 80.

MySQL is very fast at simple things. But to be taht fast, it sacrifices a lot of things, including proper locking. This lack of proper locking is the first thing to cause problems when many users want to update the table at the same time, because every update query will force a table-level lock, which means they all end up waiting on each other's queries to unlock the table before they can lock the table for themselves.
Effectively, all queries are executed in sequence, one after the other.

'proper' databases like PostgreSQL (also free) have row-level locking, which means that each update only locks the rows that it is actually updating. If one query updates row 5, another query can update row 8, even if the update query for row 5 takes three hours. So queries don't have to wait for eachother, which means much better performance at high-concurrency.

That is why I advice a benchmark test.
If MySQL should crack, it's better to find out during a test than during the grand opening of the site :-)

[Anon]

Are concurrent users people actually writing data to the database? Or does it include people just reading from the database?

[[deleted]]

Concurrent users are all users that use the database at the same time.

I MySQL, if one user locks the table to do an update, and the update takes three weeks, all other queries to that table are put on hold for three weeks. That includes all SELECT queries.

[jsagara]

Vincent,

Can you recommend any good (great customer support, reasonably priced, various packages) hosting companies that provide PostgreSQL as an option?

[[deleted]]

Nope, sorry, I don't know of any good hosting other than what I do myself :-)

[Anon]

I think thats teh problem, sure PostgresSQL might be mega powerful (comparable with Oracle?) but at the end of the day mySQL is available all over the place.

Hmmm.

[[deleted]]

Yeah...

Just like Microsoft, they don't create what the public needs, they make what the public wants and the public has no brain, they only want the obvious: fast and easy to use.

Most MySQL users never do anything more complex than a 2, maybe 3 table join. (which they quickly abandon because MySQL craps out)

meanwhile, oracle users create entire applications inside Oracle, with only a very small frontend that just talks to a combo of stored procedures.

[Anon]

Hm, I noticed that. My 4-5 table joins are making the pages take 1/2 a second to process... thank goodness for caching 🙁