Posted by
Hemos
on from the lock-yer-rows-gentlemen dept.
egerlach writes "All you DB admins out there might be interested to know that MySQL 4.0 has finally been released! It's only 4.0.0 alpha, but you can download it here. You can also check out a full list of changes."
"The new version is intended as a platform for building mission critical, heavy load database solutions"
Still no proper transactions, no subselects, no
foreign keys or views. How can this be a "mission critical" SQL database? I still don't get why people
use it. Sure, for some situations you can get
it to work, but why bother?
Re:But why?
by
chris.bitmead
·
· Score: 3, Informative
What a troll. They do a performance test of
postgresql without running the vacuum analyzer
to update statistics. Then they "wonder" (as
if they didn't know) why postgresql is slow.
Duh!
Re:But why?
by
killmenow
·
· Score: 4, Informative
we had a requirement to be able to run in a pure Windows or Solaris 8.x environment. MySQL is the only major, free DBMS that fits that bill.
Umm, how about Interbase (or Firebird for that matter)? Definitely qualifies as "major" IMO, is being actively developed, and runs on Windows, Linux, Solaris, and others...
We had enough code of our own to write, we didn't have time to fix anyone else's product
So basically, you were just looking to get a DB you didn't have to pay for, didn't have to support, and didn't have to contribute anything back to. In other words: trying to get something for nothing...you're right, that is often the "truth in the industry."
Re:But why?
by
micromoog
·
· Score: 5, Informative
SubSelects: You don't need to do these, it can be worked around pretty easily.
Views: Good ridance. A maintenance nightmare.
You clearly have no knowledge whatsoever of databases. Maybe you don't use subselects in CS102: Intro to Databases, but there are many things that can ONLY be accomplished with subselects. The simplest subselects would be worked around using temporary tables (which unnecessarily complicates the application), and the more complex requirements cannot be worked around at all (go look up "nested subquery").
Views are one of the greatest things ever to happen to a real-world database system. Various applications like their data organized in different ways, and the creation of views allows a single dataset to be used for all. The workaround is to create and populate separate tables (now that's a "maintenance nightmare").
The bottom line: a "mission-critical" database needs to support these things, and the others listed above. People would much rather use a different system that supports these features than rewrite their existing applications around the limitations of MySQL.
Let me preface my reply by saying I do in fact admin a fairly large MySQL installation, and it performs better than one would expect with its limitations. However, that is no reason to gloss over its deficiencies.
>>no proper transactions
>Yes it does! If you use certain table types
All of which are fairly new and account for only a small portion of the installations, meaning that they are no where near as well tested as the default table type (MyISAM).
Not to mention that none of them seem to have reliable benchmarks available. And to make matters worse, InnoDB has a big banner on their front page comparing themselves to a "leading database" but if you click on the link and read through the text, they state:
"Note that the tests were not run in exactly
the same way for the other database: the
comparison does not satisfy strict standards."
Publicizing the results of an admittedly flawed benchmark is unprofessional and, in my opinion, highly unethical.
>>no subselects
>This is a nice feature, but *not* necessary.
>Many times a proper JOIN can be used instead.
>Alternately you just use multiple SQLs.
>However, this is the one missing feature of
>MySQL that I want the most.
Agreed - it is possible to work around this issue. Though it does increase client code complexity.
>>no foreign keys
>You don't need foreign keys to maintain
>referential integrity. A proper GUI, among many
>other things, can enforce this anyway. It is a
>nice feature, but definitely not needed in a
>well designed system. Further they slow down
>performance and I have seen projects where they
>are not used because of this.
Yes it is possible to do integrity checks programmatically. However, this does nothing for manual administration, and requires implementation for every piece of code that might modify the database.
In most intstances I would consider not using foreign keys to be a poor decision, particularly after dealing with the mess created by a database where they decided to use programmatic checks for integrity.
As a side note, there is partial implementation of foreign keys in the InnoDB table handler, though it has some fundamental flaws to it. It drops constraints on an ALTER table, it allows you to drop referenced tables, and it lacks features such as CASCADE ON DELETE.
>>views
>These can be nice too, but I personally never
>use them. They are simply not required in any
>project I've ever seen. Actually I think views
>are confusing because they mask the real tables.
>I think this is a style issue more than anything
>else, YMMV.
Views make it possible for you to modify the schema of a database without having to touch your client code.
Views are also a wonderful way to present a simplified view to your programmers, rather than expecting them to know, e.g. how to do a full outer join on three or four tables with a sub-select thrown in just to make it a little more confusing.:)
>>How can this be a "mission critical" SQL
>>database?
>How about better performance [mysql.com].
As I have pointed out before, the benchmarks on mysql.com are for a single thread of access only. Which does not mimic the real world environment of the vast majority of database installations. Unless that is going to be your method, those benchmarks are essentially useless.
To their credit, representatives from MySQL AB have promised a more robust test in the future. But until that's out, I cannot put any stock in their published benchmarks.
In other news, PostgreSQL announced that it has all the features that MySQL 4.0 has, has had them for a long time, is GPL'd, and is very stable (as in not alpha). Get it here
OK, maybe that wasn't announced, but it should have been.
Re:In other news
by
chris.bitmead
·
· Score: 4, Informative
Actually, it's not GPLed, its BSDed. i.e.
nearly, almost public domain.
Re:In other news
by
Kattare
·
· Score: 3, Insightful
Sometimes speed is everything...
Re:In other news
by
brunes69
·
· Score: 3, Interesting
I never said it wasn't GPL. I'm just tyring to point out that a much better and more robust open source database already exists, and frankly I do't understand why people would continue to use a product so limited as MySQL (it doesnt even support sub-selects!).
Re:In other news
by
tzanger
·
· Score: 5, Insightful
Sometimes speed is everything...
Which MySQL hasn't got, no matter how you look at it.
Postgres has shown time and time again that it blows the shit out of MySQL for any kind of select statement where your 'where' clause is even just slightly more complicated than 'x = y'. Even for simple selects, Postgres performance scales waaaaay better than MySQL. We're not talking hundreds of clients, either; we're talking dozens. MySQL falls flat on its face under heavy load.
And if your application just requires the simplest of simple selects, a hashed flat file is faster than MySQL because you don't actually have to parse up the SQL statement and return the result in a formatted fashion!
Face it: MySQL is a neat toy but that's about it. All this pandering about what 4.0's got now and how they think it's mission-critical-ready is bullshit, plain and simple. We've got an open-source, free ACID-compliant database already. MySQL has lost, they just don't know it yet.
Re:In other news
by
dhogaza
·
· Score: 4, Informative
Actually the InnoBase table type removes the biggest reason why MySQL traditionally has shit its pants under heavy load, as it provides row-level locking and non-blocking writes rather than the old table-level locking required with MySQL's original table type.
So it should be much better in this regard.
I don't know from personal experience, though. I use PostgreSQL instead because it's got important features like referential integrity checking and even better, a development team who understand why such features are important.
Re:In other news
by
SuiteSisterMary
·
· Score: 4, Interesting
You ever read slashdot lately, you go to the home page, and you're not logged in? So you read a story, and you try to change the threshold and sort order, and it either goes to main page, or to a 'recent topics' page, or back to the default view of the story?
That's mySQL having fallen over.
Slashdot, who's admins "reboot the MySQL server" *shudder* to fix things.
-- Vintage computer games and RPG books available. Email me if you're interested.
Re:In other news
by
tzanger
·
· Score: 3, Interesting
*cough cough* slashdot
You're giving/. as an example of a rock-solid stable system? I surely hope you're kidding./. Has fallen up and down more times than I care to remember.
IIRC,/. heavily caches both stories and the front page to avoid load on the MySQL server. Before this was done the crashes were a lot more visible. Now, you just don't see new comments until the cache is refreshed. This is both a good and a bad thing, but it does not show that MySQL works well under load.
/. Also shows its MySQL troubles when you try to log in or change your viewing prefs; if the SQL server is down, you get the threaded (ick) cached page instead of what you want.
here is a phpbuilder test that helps back up my claims.
Does this really warrant a 4.0 release?
by
jedrek
·
· Score: 5, Interesting
I hate to be a spoilsport, but does this really warrant a 4.0 release? I don't really see anything in the changelog that would support a 1.+ release. Maybe a.1+, but not a 1.+.
Hm... I keep going over it and see stuff like 'Removed all Gemini hooks from MySQL' or 'New character set latin_de which provides correct German sorting'. The only major things I'm seeing right now are the SSL support, support for UNION and boolean fulltext search.
Am I missing something?
Transactions, foreign keys
by
blackcat++
·
· Score: 5, Informative
I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
Re:Transactions, foreign keys
by
jamie
·
· Score: 3, Informative
"I haven't looked at the slashdot code recently, but there were a dozen and a half different methods behind the database functions that could have been optimized to better utilize MySQL."
We welcome specific suggestions and criticisms. Please
submit a Slash bug report
and let us know which methods you're talking about. Thanks!
Re:Transactions, foreign keys
by
krow
·
· Score: 3, Informative
To date we had one bug in Innodb that I have found (and we found one other in replication, but no one would have noticed that other then the fellow who has to keep this stuff running...). We have had some growing pains that we have went to. In our first week of operation we had a number of hardware problems bring us down (we have since moved hardware) which was only related to the DB's because MySQL was running on that Hardware. This http://www.tangent.org/~brian/talks/dbsummit_scali ng/ take you to some slides that I did for a talk I gave on Slashdot's DB.
-- You can't grep a dead tree.
Re:Windows Frontend?
by
robwills
·
· Score: 3, Informative
SciBit make Mascon, which is excellent.
I use their Free Mascon product with my intranet servers and it is robust, quick, many features and a solid interface.
If I paid for the full version, I could design/alter tables & indexes. Because I don't do that often, I will do it by hand using the cli client or use phpMyAdmin which is pretty cool too.
The real reason for this release
by
joshv
·
· Score: 5, Insightful
From the release notes:
"Removed all Gemini hooks"
See:
http://www.mysql.com/news/article-75.html
And you will understand. There probably are not enough changes to warrant a +1 increment in the release number, but this gives them a new version that does not support NuSphere's attempt to usurp MySQL's copyright and trademark rights.
You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.
This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent. I know, it sounds harsh, but it's true. Not using foreign keys is like writing an application with just one long main() procedure. A. You're assuming that only one gui and no users will ever access this database. B. If the GUI isn't perfect, your data is garbage. C. It makes future analysis and upgrades very difficult, if not impossible.
These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.
Wow. Views are useful for security, and for performance.
No offense guy, but you know absolutely nothing about databases. You really should not be giving out advice on them. Pick up a BASIC database theory book and read it. When you're done, read it again. You have a lot to learn.
If I have complete control over every application that accesses the database then I can ensure that garbage data is not put in.
That is exactly the point. In the real world, you cannot ever have this 100% complete control. Even if you have somehow been given supreme executive database authority in your company, one day you will quit or get run over by a bus. Your successor will be handed a requirement to implement this new little tool that the sales force desperately needs that just happens to require access to this "protected" database. If you had done the job right, almost all the new developer would need to know would be completely described in the database itself. With referential integrity relegated to clients, the developer first has to completely understand the database and all of the client GUI code everywhere.
Let's say he's real good and figures out all of the GUI code. Then the next day, the marketing guys come up with a new requirement for a little app that they need that also accesses the same database. Now you have to completely understand 2 other applications before coding this new one. And it just keeps getting worse and worse as time goes on.
There are tradeoffs involved. Performance problems can often (but not always) be handled by throwing more/better/faster hardware at it. The tradeoff I most often see is that it's usually easier and much quicker to slap client code together than it is to do a good solid system design.
I went to this link, and found the name slashdot's senior mysql guy: Brian Aker (aka krow). Seems slashdot has added code for doing better sql dumps in MySQL. If you recall back a few several hundred/. stories, you might remember that he also hacked out a method to have stored-procedure calls in MySQL. Also take note that when slashdot upgraded to version 2.1 of their infamous slashcode, Brian rewrote the schemas for InnoDB style. I'd say that we slashdot folks will see new toys based on some of this new technology because/. is so entrenched with mysql.
See.......... slashdot really is good for something... =)
Yes it does, if you install and use the latest InnoDB table handler (see www.innodb.com )
Lets see if they get it right this time
by
SnapperHead
·
· Score: 5, Informative
MySQL has been missing some very important key parts, which makes life very difficault working cross database.
timestamp field doesn't follow SQL standards
Sub-selects
Triggers would be nice
select disinct on (field_name)... would also be nice
To sum it all up, I hope they plan on following SQL standards this time around.
-- until (succeed) try { again(); }
Open Source DB Mad-Libs
by
Diomedes01
·
· Score: 3, Funny
[MySQL / PostgreSQL / Interbase] is [better / worse] than [MySQL / PostgreSQL / Interbase] because it [has / doesn't have] [row-level locking / transactions / foreign keys / large [development/user] base]
In all honesty, why can't people just realize that competition is a good thing? Instead of having one database that tries to be all things for all people, the Open Source Community has several database applications available; choose whatever best suits your needs!
--
"To hope's end I rode and to heart's breaking:
Now for wrath, now for ruin and a red nightfall!"
Hot and incremental backup
by
Anonymous Coward
·
· Score: 3, Interesting
got this from the InnoDB Todo list on their webpage. Look closely to the end of this statement:
"Hot and incremental backup: you will be able to backup your InnoDB database with a background process without setting any locks on tables and without disturbing the normal processing of your queries. The backup process makes a consistent copy of the InnoDB database, to which you can apply the MySQL binlog when you need point-in-time recovery.
The backup program will be separate from MySQL/InnoDB, and it will be non-free, with an annual license fee of 250 euros. Please contact Heikki.Tuuri@innodb.com for further information"
How about a competition? (was Re:Proof, please)
by
King+Babar
·
· Score: 4, Insightful
Postgres has shown time and time again that it blows the shit out of MySQL for any kind of select statement where your 'where' clause is even just slightly more complicated than 'x = y'.
I'm using MySQL to perform selects that join five tables, including one with over 800,000 rows and one with over 5 million rows. It can perform these selects in a fraction of a second, more than adequate performance for my application.
I might be missing something, but I don't see
anything contradictory in these two statements.
If your "where" clause is sufficiently easy and you can take advantage of indexing, you would expect any decent system to
perform reasonably well when it is returning a
handful of rows. Five table joins would only
be scary when you can't toss out the vast majority of your 5 million rows at step one.
MySQL has also been very reliable for me.
One thing that has just occurred to me in this,
round 5,408 of the MySQL-versus-PostgreSQL flame
fest is to ask if anybody has tried to replace
MySQL with PostgreSQL or vice versa in any project that both sides would consider "worthy" in some sense. One problem here, of course, is that if you really wanted an ACID solution, you weren't (aren't?) likely to choose MySQL in the first place. But what if you wanted to use PostgreSQL with the Slashcode; would this port be doable in anything like a reasonable amount of time? If done, could it then be shown that the result was
any different (for better or for worse) than what
you get with the current system?
Another idea would be to borrow an idea from the
functional programming community and do a "database programming contest" like the the annual
ICFP Programming Contest. You could try to find a sponsor and give out cash prizes, or you could get *really* serious (like the ICFP people do) and award year-long bragging rights.
I mean, both PostgreSQL and MySQL fans would probably go to great lengths to avoid having to
refer to the other product as, for example, "the relational database system of choice for for discriminating hackers" in public. Not to mention the possibility that both
of them would have to acknowledge the supremacy
of DB2 or Oracle or something.:-)
--
Babar
Re:Any support for Foreign keys yet?
by
teg
·
· Score: 3, Insightful
Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?
You don't absolutely need foreign keys to implement one-to-many relationsships - you can just do it the standard way and drop the foreign key constraint. Of course, your app needs to make sure that it updates and deletes entries when necesarry (and when did NTH^WNTNU stop using Oracle? That's what we used when I had the database course there, and MySQL is a poor choice for a course in databases If there's one place you'd like to really have foreign keys, subselects, transactions, views etc, a university course on databases is it - and PostgreSQL clearly has better SQL support than MySQL)
Re:This version doesn't compile
by
Peter+H.S.
·
· Score: 3, Insightful
GCC is *not* the culprit. Code that doesn't compile on GCC 3 is broken code, that violates the basics of the C language.
True, and I knew that you knew that too, so I did not write it. But a lot of code still doesn't work with gcc 3.x. So as another pointed out, a bug report to the mysql crew is in order. (if you have the time).
Re:Proof, please
by
tzanger
·
· Score: 3, Interesting
I find the people here slamming MySQL are often doing so based on their theoretical, rather than practical knowledge. But perhaps you are different. I'd like to see you back up your claim.
Here is a recent (MySQL 3.23.26beta and PGSQL 7.1 CVS pre-beta) benchmark. Now I know that benchmarks are the devil's tools, but he really seemed to try and make it a balanced, true-type of benchmark. It is interesting to look at his 1999 benchmark between the two too, where MySQL appeared faster on simple selects and non -concurrent writes to tables. I forget (and don't see it mentioned) if Tim Purdue actually turned off the fsync action that Postgres leaves on by default. If not, it could have explained some of the slowness of Postgres at that time.
My personal experience with older MySQL is that it is unstable and buggy. We used it for our RADIUS backend for about 3 years and it fell over regularly without much effort. About 18 months ago I replaced it with GNU-RADIUSd and Postgres and -- with four times the load -- it has yet to fail. This wasn't super-high-end stuff either. We're talking about 300 dialup lines with a couple RADIUS daemons making SQL calls to update user logins and time spent when logged out. With MySQL it was 48 lines and a single daemon and I was restarting MySQL so much I wrote a script to do it for me (about five times a week or so).
I am glad to hear that MySQL works for you; chacon son gout, as the French say (when they don't have accents handy). However based on my experience and the experiences of those who at least appear to be doing unbiased benchmarking, and also based on my need for referential integrity, ACID compliance and robustness, MySQL loses. Hell even those using it for pure speed are losing too, since it isn't the fastest, despite what MySQL, Inc. claims.
Stupidity runs rampant in our industry
by
Morocco+Mole
·
· Score: 4, Interesting
Well said! I agree with you!
On my last contract I was unable to convince project leads of the value of transactions. Even though my resume clearly shows 10 years of Oracle and 6 years of SQL Server I couldn't convince a bunch of idiots {with an admitted combined total of SQL Server experience of less than 4 weeks} that being able to transactionally update a patient record and the related information about which medications had been administered was a good idea. Their stated reason for not wanting views, transactions, foreign keys, and stored procedures? "Our database is small - only a thousand or so patients per hospital. Transactions would reduce performance. We don't want to use stored procedures because some day we might want to port the database. What's a view? What's a foreign key?"
So after a few weeks of gently, but fruitlessly, trying to explain that stored procedures and views will guarantee the performance you want, that foreign key constraints and transactions will guarantee the integrity that your medical device database must have - I finally couldn't take it anymore.
So one day in a meeting I said: "Can a patient be hurt if a medication is administered twice? What if the power goes down while updating a patient's treatment record and information about a treatment is lost?"
"Yes a patient could be harmed by duplicate treatments, but that won't happen..."
So I said: "I cannot help you..." And I walked off the gig. I dunno what came of that project but I did hear from a friend that 6 months later they had a GUI that featured several screens that took between 30 seconds to a full minute to bring up one screenful of information. People just don't get the golden rule: Code defensively and keep your business logic as CLOSE to the disk as you can! There are alot of astoundingly ignorant people out there and you just can't stop them all...
--Richard
Still on the wrong track!
by
MattRog
·
· Score: 5, Insightful
If you're listening MySQL -- who really cares about SSL and unicode. Granted I've had them in my RDBMS that I use but still you're missing the big picture.
The people who post funny things like 'MySQL Mad libs' are on to something - Slashdot is full of 'arm chair' DBAs (and programmers and network engineers and etc. etc. etc.) who know just enough to think they know exactly what is wrong and how to fix it. It's kind of like the people who have used a computer for a while and start trying to troubleshoot their friend's computers - 'You may have run out of Megahertz' or 'You probably need more memory to download files faster'. Yes, subqueries, locking issues, etc. are very important. However, these are 'surface' level flaws in MySQL. There are some very, very large problems in the underlying architecture which need to be addressed. Since they're not as glamorous (or as understood) as subqueries and pretty much ignored by 90% of the users they never see the light of day - but they impact almost 100% of the users in one way or another.
Fix things like this:
Filesystem buffered writes. Transactional support is great - it allows me to roll-back aborted transactions. However, due to the inability to control whether or not my tables are write-buffered means that MySQL may *think* it has performed a write even though it is still in the write-cache. I can then turn off the system and voila -- corruption! Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache, but I have a simple solution. Devise a way to selectively turn off buffered writes for certain tables / databases. This way if I know I have a critical table which has a lot of writes I can turn buffering off and be ASSURED that writes will be performed when asked. I suspect a lot of 1040 and other table corruptions are caused by something like this. Yes, performance will take a hit but I think it is a very acceptable trade-off for data corruption. Obviously all system tables should NOT be buffered.
Inability to use more than one index on a table in a query -- most enterprise RDBMS' can use more than one index on a table for a query. This can easily save a table scan or the use of a single, less-efficient index. Given an example query - 'SELECT bob FROM sometable WHERE somecol = 45 and somecol2 = 44' - if I have two indexes on sometable (somecol and somecol2) it can join the two indexes together and
Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col). This also greatly speeds up BETWEEN clauses. And yes, to people who know a little bit of SQL but don't know as much about clustered indexes -- you can create an index with a bobcol ASC but clustering the actual data is faster and more efficient if you are grabbing data which is not on the index. For example, SELECT * FROM table ORDER BY username ASC will not be as efficient as the same query clustered on the username. If you had a sorted index on username it will probably read the index sequentially and then visit the table. That extra operation = more disk seeks = more time / cpu to execute (and it really adds up as the table size increases). However, if you are doing something like 'select username, password from user order by username' it would be better to create a sorted index on username ASC, password. That way it will read the index only and not visit the table at all.
on-line backups. In today's internet world your site has to be 24/7. This means you cannot have significant performance problems (or even offline-ing your dB!) when you make a dump -- Sybase, etc. have done this from as far back as I can remember.
backups to something other than CSV files. MS SQL, Sybase, Oracle, they all dump to a compressed binary file. Saves a TON of space and is MUCH FASTER to dump and load. I can dump a 6GB Sybase DB in under 5 minutes. Loading it all (from scratch) and then bringing the DB online is about the same amount of time.
Ability to specify the number of files to dump to. What happens if you have a dump which is larger than 2GB? Most linux distros cannot handle a single file of 2GB or more. Give users a way to, within the dump statement, split the dump over two files. Sybase has the 'STRIPE ON' clause (originally to dump to two tape drives at once but works fine on filesystem files as well) to split the dump equally over an unlimited number of files.
cleaner way to view query plans of statements. EXPLAIN... is great and all, but the resulting table is a PAIN to read. What I want to know is simple:
Query is using XYZ, ABC tables. Table XYZ is using index 123 which is sorted so I do not need to create a temp table to sort ASC.
Since you have all the columns in your select statement in the index I do not have to visit the actual table - I can pull it all from the index. Because of this, I will read the index from start to finish.
ABC is using index 23dsf which is not sorted so I must create a temp table to sort that. Also, since it is a join, I do not need to perform an index scan but a positioned search (table scan is to a WHERE clause with no index AS index scan is to an index which is not selective enough or needs to read all columns.)
Simple, easy and pretty much even a NOVICE can see that their query is a good performer or a bad performer.
Along with more in-depth EXPLAIN, also provide me with a way to see what the optimizer is doing with the query. In MS SQL and Sybase you have 'trace flags' which you can turn on before your query to see EXACTLY what Sybase is doing - why does it think this index is better than this other one, why is it table scanning when you think it should index sort, etc. Give me an easy way to say 'verbose on; explain xxx;'.
Ability to delve deeply into performance of the system. If there is one job a DBA must know it's how to tell what the heck is going on when something is slow. Currently MySQL gives you meaningless info like 'slow queries'. Great, I see 200,000 of them. What queries are they? What good is it in a large application which may contain 3000 lines of SQL to tell me the raw number of queries which are slow? I want to know the EXACT SQL of the query(s) which are slow and I want to find the one taking up the most CPU time and blocking all the rest. I want to know how MySQL is managing it's data cache so I can see if I need more ram (e.g. it is swapping lots of data to/from the cache) or if I am I/O bound. Don't tell me to look at 'free' or 'top' - half the time it is wrong because you (MySQL) tell it misleading figures. I want *you* to tell me exactly what you are doing since you would know best! If you've ever seen a sp_sysmon output from Sybase ASE you'd know what I'm talking about.
Hire people who are accomplished relational algebra freaks. Pay them a lot of money - it is well worth it!!! If I see another changelog entry like this I'm going to scream:
Optimized queries of type: SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #
So does that mean these queries were NOT AT ALL optimized before? It doesn't read 'FURTHER optimized'.
"ORDER BY... DESC can now use keys."
Does that mean it was table scanning each time? Jebus! Get these wiz-bangs to overhaul your query optimizer. Hands down this is one of the most important things in the database -- knowing how to use the database statistics and knowing when to use a merge-join vs. a hash-join etc. are CRITIAL to database performance. Trust me when I say that this is a MAJOR priority - especially over SSH or Unicode since it impacts EACH AND EVERY statement which MySQL parses.
Small things which can really raise the respect of MySQL in the eyes of people who matter - the CTOs and CIOs of the world who might be considering something like Postgres or MySQL but opt for MS SQL, Sybase, or Oracle because of the more advanced (but less flashy) features.
Of course, integrated row (or in the least page) locking and full support of subqueries and the like are also VERY HIGH priorities.
I think addressing these issues will vastly improve MySQL -- they're not flames really, just gripes:D
Slashdot Mirror
Still no proper transactions, no subselects, no foreign keys or views. How can this be a "mission critical" SQL database? I still don't get why people use it. Sure, for some situations you can get it to work, but why bother?
In other news, PostgreSQL announced that it has all the features that MySQL 4.0 has, has had them for a long time, is GPL'd, and is very stable (as in not alpha). Get it here
OK, maybe that wasn't announced, but it should have been.
I hate to be a spoilsport, but does this really warrant a 4.0 release? I don't really see anything in the changelog that would support a 1.+ release. Maybe a .1+, but not a 1.+.
Hm... I keep going over it and see stuff like 'Removed all Gemini hooks from MySQL' or 'New character set latin_de which provides correct German sorting'. The only major things I'm seeing right now are the SSL support, support for UNION and boolean fulltext search.
Am I missing something?
I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
SciBit make Mascon, which is excellent.
I use their Free Mascon product with my intranet servers and it is robust, quick, many features and a solid interface.
If I paid for the full version, I could design/alter tables & indexes. Because I don't do that often, I will do it by hand using the cli client or use phpMyAdmin which is pretty cool too.
From the release notes:
"Removed all Gemini hooks"
See:
http://www.mysql.com/news/article-75.html
And you will understand. There probably are not enough changes to warrant a +1 increment in the release number, but this gives them a new version that does not support NuSphere's attempt to usurp MySQL's copyright and trademark rights.
-josh
You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.
This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent. I know, it sounds harsh, but it's true. Not using foreign keys is like writing an application with just one long main() procedure. A. You're assuming that only one gui and no users will ever access this database. B. If the GUI isn't perfect, your data is garbage. C. It makes future analysis and upgrades very difficult, if not impossible.
These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.
Wow. Views are useful for security, and for performance.
No offense guy, but you know absolutely nothing about databases. You really should not be giving out advice on them. Pick up a BASIC database theory book and read it. When you're done, read it again. You have a lot to learn.
I went to this link, and found the name slashdot's senior mysql guy: Brian Aker (aka krow). Seems slashdot has added code for doing better sql dumps in MySQL. If you recall back a few several hundred /. stories, you might remember that he also hacked out a method to have stored-procedure calls in MySQL. Also take note that when slashdot upgraded to version 2.1 of their infamous slashcode, Brian rewrote the schemas for InnoDB style. I'd say that we slashdot folks will see new toys based on some of this new technology because /. is so entrenched with mysql.
See.......... slashdot really is good for something... =)
It isn't a lie if you belive it.
Yes it does, if you install and use the latest InnoDB table handler (see www.innodb.com )
until (succeed) try { again(); }
[MySQL / PostgreSQL / Interbase] is [better / worse] than [MySQL / PostgreSQL / Interbase] because it [has / doesn't have] [row-level locking / transactions / foreign keys / large [development/user] base]
In all honesty, why can't people just realize that competition is a good thing? Instead of having one database that tries to be all things for all people, the Open Source Community has several database applications available; choose whatever best suits your needs!
"To hope's end I rode and to heart's breaking: Now for wrath, now for ruin and a red nightfall!"
got this from the InnoDB Todo list on their webpage. Look closely to the end of this statement:
"Hot and incremental backup: you will be able to backup your InnoDB database with a background process without setting any locks on tables and without disturbing the normal processing of your queries. The backup process makes a consistent copy of the InnoDB database, to which you can apply the MySQL binlog when you need point-in-time recovery.
The backup program will be separate from MySQL/InnoDB, and it will be non-free, with an annual license fee of 250 euros. Please contact Heikki.Tuuri@innodb.com for further information"
I might be missing something, but I don't see anything contradictory in these two statements. If your "where" clause is sufficiently easy and you can take advantage of indexing, you would expect any decent system to perform reasonably well when it is returning a handful of rows. Five table joins would only be scary when you can't toss out the vast majority of your 5 million rows at step one.
One thing that has just occurred to me in this, round 5,408 of the MySQL-versus-PostgreSQL flame fest is to ask if anybody has tried to replace MySQL with PostgreSQL or vice versa in any project that both sides would consider "worthy" in some sense. One problem here, of course, is that if you really wanted an ACID solution, you weren't (aren't?) likely to choose MySQL in the first place. But what if you wanted to use PostgreSQL with the Slashcode; would this port be doable in anything like a reasonable amount of time? If done, could it then be shown that the result was any different (for better or for worse) than what you get with the current system?
Another idea would be to borrow an idea from the functional programming community and do a "database programming contest" like the the annual ICFP Programming Contest. You could try to find a sponsor and give out cash prizes, or you could get *really* serious (like the ICFP people do) and award year-long bragging rights.
I mean, both PostgreSQL and MySQL fans would probably go to great lengths to avoid having to refer to the other product as, for example, "the relational database system of choice for for discriminating hackers" in public. Not to mention the possibility that both of them would have to acknowledge the supremacy of DB2 or Oracle or something. :-)
Babar
Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?
You don't absolutely need foreign keys to implement one-to-many relationsships - you can just do it the standard way and drop the foreign key constraint. Of course, your app needs to make sure that it updates and deletes entries when necesarry (and when did NTH^WNTNU stop using Oracle? That's what we used when I had the database course there, and MySQL is a poor choice for a course in databases If there's one place you'd like to really have foreign keys, subselects, transactions, views etc, a university course on databases is it - and PostgreSQL clearly has better SQL support than MySQL)
GCC is *not* the culprit. Code that doesn't compile on GCC 3 is broken code, that violates the basics of the C language.
True, and I knew that you knew that too, so I did not write it. But a lot of code still doesn't work with gcc 3.x. So as another pointed out, a bug report to the mysql crew is in order. (if you have the time).
I find the people here slamming MySQL are often doing so based on their theoretical, rather than practical knowledge. But perhaps you are different. I'd like to see you back up your claim.
Here is a recent (MySQL 3.23.26beta and PGSQL 7.1 CVS pre-beta) benchmark. Now I know that benchmarks are the devil's tools, but he really seemed to try and make it a balanced, true-type of benchmark. It is interesting to look at his 1999 benchmark between the two too, where MySQL appeared faster on simple selects and non -concurrent writes to tables. I forget (and don't see it mentioned) if Tim Purdue actually turned off the fsync action that Postgres leaves on by default. If not, it could have explained some of the slowness of Postgres at that time.
My personal experience with older MySQL is that it is unstable and buggy. We used it for our RADIUS backend for about 3 years and it fell over regularly without much effort. About 18 months ago I replaced it with GNU-RADIUSd and Postgres and -- with four times the load -- it has yet to fail. This wasn't super-high-end stuff either. We're talking about 300 dialup lines with a couple RADIUS daemons making SQL calls to update user logins and time spent when logged out. With MySQL it was 48 lines and a single daemon and I was restarting MySQL so much I wrote a script to do it for me (about five times a week or so).
I am glad to hear that MySQL works for you; chacon son gout, as the French say (when they don't have accents handy). However based on my experience and the experiences of those who at least appear to be doing unbiased benchmarking, and also based on my need for referential integrity, ACID compliance and robustness, MySQL loses. Hell even those using it for pure speed are losing too, since it isn't the fastest, despite what MySQL, Inc. claims.
Well said! I agree with you!
On my last contract I was unable to convince project leads of the value of transactions. Even though my resume clearly shows 10 years of Oracle and 6 years of SQL Server I couldn't convince a bunch of idiots {with an admitted combined total of SQL Server experience of less than 4 weeks} that being able to transactionally update a patient record and the related information about which medications had been administered was a good idea. Their stated reason for not wanting views, transactions, foreign keys, and stored procedures? "Our database is small - only a thousand or so patients per hospital. Transactions would reduce performance. We don't want to use stored procedures because some day we might want to port the database. What's a view? What's a foreign key?"
So after a few weeks of gently, but fruitlessly, trying to explain that stored procedures and views will guarantee the performance you want, that foreign key constraints and transactions will guarantee the integrity that your medical device database must have - I finally couldn't take it anymore.
So one day in a meeting I said: "Can a patient be hurt if a medication is administered twice? What if the power goes down while updating a patient's treatment record and information about a treatment is lost?"
"Yes a patient could be harmed by duplicate treatments, but that won't happen..."
So I said: "I cannot help you..." And I walked off the gig. I dunno what came of that project but I did hear from a friend that 6 months later they had a GUI that featured several screens that took between 30 seconds to a full minute to bring up one screenful of information. People just don't get the golden rule: Code defensively and keep your business logic as CLOSE to the disk as you can! There are alot of astoundingly ignorant people out there and you just can't stop them all...
--Richard
If you're listening MySQL -- who really cares about SSL and unicode. Granted I've had them in my RDBMS that I use but still you're missing the big picture.
... is great and all, but the resulting table is a PAIN to read. What I want to know is simple:
... DESC can now use keys."
:D
The people who post funny things like 'MySQL Mad libs' are on to something - Slashdot is full of 'arm chair' DBAs (and programmers and network engineers and etc. etc. etc.) who know just enough to think they know exactly what is wrong and how to fix it. It's kind of like the people who have used a computer for a while and start trying to troubleshoot their friend's computers - 'You may have run out of Megahertz' or 'You probably need more memory to download files faster'. Yes, subqueries, locking issues, etc. are very important. However, these are 'surface' level flaws in MySQL. There are some very, very large problems in the underlying architecture which need to be addressed. Since they're not as glamorous (or as understood) as subqueries and pretty much ignored by 90% of the users they never see the light of day - but they impact almost 100% of the users in one way or another.
Fix things like this:
Filesystem buffered writes. Transactional support is great - it allows me to roll-back aborted transactions. However, due to the inability to control whether or not my tables are write-buffered means that MySQL may *think* it has performed a write even though it is still in the write-cache. I can then turn off the system and voila -- corruption! Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache, but I have a simple solution. Devise a way to selectively turn off buffered writes for certain tables / databases. This way if I know I have a critical table which has a lot of writes I can turn buffering off and be ASSURED that writes will be performed when asked. I suspect a lot of 1040 and other table corruptions are caused by something like this. Yes, performance will take a hit but I think it is a very acceptable trade-off for data corruption. Obviously all system tables should NOT be buffered.
Inability to use more than one index on a table in a query -- most enterprise RDBMS' can use more than one index on a table for a query. This can easily save a table scan or the use of a single, less-efficient index. Given an example query - 'SELECT bob FROM sometable WHERE somecol = 45 and somecol2 = 44' - if I have two indexes on sometable (somecol and somecol2) it can join the two indexes together and
Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col). This also greatly speeds up BETWEEN clauses. And yes, to people who know a little bit of SQL but don't know as much about clustered indexes -- you can create an index with a bobcol ASC but clustering the actual data is faster and more efficient if you are grabbing data which is not on the index. For example, SELECT * FROM table ORDER BY username ASC will not be as efficient as the same query clustered on the username. If you had a sorted index on username it will probably read the index sequentially and then visit the table. That extra operation = more disk seeks = more time / cpu to execute (and it really adds up as the table size increases). However, if you are doing something like 'select username, password from user order by username' it would be better to create a sorted index on username ASC, password. That way it will read the index only and not visit the table at all.
on-line backups. In today's internet world your site has to be 24/7. This means you cannot have significant performance problems (or even offline-ing your dB!) when you make a dump -- Sybase, etc. have done this from as far back as I can remember.
backups to something other than CSV files. MS SQL, Sybase, Oracle, they all dump to a compressed binary file. Saves a TON of space and is MUCH FASTER to dump and load. I can dump a 6GB Sybase DB in under 5 minutes. Loading it all (from scratch) and then bringing the DB online is about the same amount of time.
Ability to specify the number of files to dump to. What happens if you have a dump which is larger than 2GB? Most linux distros cannot handle a single file of 2GB or more. Give users a way to, within the dump statement, split the dump over two files. Sybase has the 'STRIPE ON' clause (originally to dump to two tape drives at once but works fine on filesystem files as well) to split the dump equally over an unlimited number of files.
cleaner way to view query plans of statements. EXPLAIN
Query is using XYZ, ABC tables. Table XYZ is using index 123 which is sorted so I do not need to create a temp table to sort ASC.
Since you have all the columns in your select statement in the index I do not have to visit the actual table - I can pull it all from the index. Because of this, I will read the index from start to finish.
ABC is using index 23dsf which is not sorted so I must create a temp table to sort that. Also, since it is a join, I do not need to perform an index scan but a positioned search (table scan is to a WHERE clause with no index AS index scan is to an index which is not selective enough or needs to read all columns.)
Simple, easy and pretty much even a NOVICE can see that their query is a good performer or a bad performer.
Along with more in-depth EXPLAIN, also provide me with a way to see what the optimizer is doing with the query. In MS SQL and Sybase you have 'trace flags' which you can turn on before your query to see EXACTLY what Sybase is doing - why does it think this index is better than this other one, why is it table scanning when you think it should index sort, etc. Give me an easy way to say 'verbose on; explain xxx;'.
Ability to delve deeply into performance of the system. If there is one job a DBA must know it's how to tell what the heck is going on when something is slow. Currently MySQL gives you meaningless info like 'slow queries'. Great, I see 200,000 of them. What queries are they? What good is it in a large application which may contain 3000 lines of SQL to tell me the raw number of queries which are slow? I want to know the EXACT SQL of the query(s) which are slow and I want to find the one taking up the most CPU time and blocking all the rest. I want to know how MySQL is managing it's data cache so I can see if I need more ram (e.g. it is swapping lots of data to/from the cache) or if I am I/O bound. Don't tell me to look at 'free' or 'top' - half the time it is wrong because you (MySQL) tell it misleading figures. I want *you* to tell me exactly what you are doing since you would know best! If you've ever seen a sp_sysmon output from Sybase ASE you'd know what I'm talking about.
Hire people who are accomplished relational algebra freaks. Pay them a lot of money - it is well worth it!!! If I see another changelog entry like this I'm going to scream:
Optimized queries of type: SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #
So does that mean these queries were NOT AT ALL optimized before? It doesn't read 'FURTHER optimized'.
"ORDER BY
Does that mean it was table scanning each time? Jebus! Get these wiz-bangs to overhaul your query optimizer. Hands down this is one of the most important things in the database -- knowing how to use the database statistics and knowing when to use a merge-join vs. a hash-join etc. are CRITIAL to database performance. Trust me when I say that this is a MAJOR priority - especially over SSH or Unicode since it impacts EACH AND EVERY statement which MySQL parses.
Small things which can really raise the respect of MySQL in the eyes of people who matter - the CTOs and CIOs of the world who might be considering something like Postgres or MySQL but opt for MS SQL, Sybase, or Oracle because of the more advanced (but less flashy) features.
Of course, integrated row (or in the least page) locking and full support of subqueries and the like are also VERY HIGH priorities.
I think addressing these issues will vastly improve MySQL -- they're not flames really, just gripes
Thanks,
--
Matt