Slashdot Mirror
Major Changes To MySQL Coming Soon
Meltr writes: "This ZDNET article details some of the coming changes to the MySQL database server. In 4.0, to be released in mid-October: 'support for the Unicode character set, the SSL (Secure Sockets Layer) protocol, embedded database links and multitable updates' and in 4.1, to be released in December: 'nested queries and stored procedures'."
Hmm I guess this means its a good thing that I didn't buy the MySQL book because its all going to change. I hope that the differnces aren't very complicated to interchange between so that the people who are familar with MySQL now aren't going crazy tring to retrain their brain for the new versions. But its only a hope.
Unicode support will come in very nicely for
database driven, foriegn language websites.
Hope the Java JDBC drivers for MySQL are updated
to handle unicode as well. (Should be easy, as
Java got all the Unicode stuff as it is).
I'm just looking forward to being able to tune the FULLTEXT search function without recompiling the database server!
Universal Objects has also announced that their Unicode report server has been ported to Linux and is available now. Cheap, multilingual solutions might be what Linux needs for acceptance.. I belive we could beat Microsoft at the whole "total cost of ownership" game here.. What Unicode compliant database software is available free or cheap to use this with?
I just started teaching myself a little bit about mySQL this evening. I'm already impressed by this example of open-source might...
I don't see these as major changes, rather than just adding some features...
Slashdot strikes again. If this was Microsoft, you'd be criticising them for releasing vapouware. But it's MySQL/linux so it's cool huh?
Get PostgresQL, a real DB. That already has all of these features, and rock solid too, not beta patches etc.
"Making linux GPL was the best thing I ever did" - Torvalds. I'd hate to see the worst thing...
Real men also post under while logged in!
Did you just grab my ass?
Did you just grab my ass?
It sounds great, MySQL it is trying to implement many features that were missed without loosing speed.
You can have a look to these comparison between Mysql-PostgreSQL and other open source databases.
Excellent. One of the reasons my company decided to hold off on porting to mysql was the lack of stored procedures. A pity; now they've moved to DB2 and the moment is lost.
"Einstein argued that [...] God is not capricious or arbitrary. No such faith comforts the software engineer." ~ Brooks
What I'd like to see is a profound comparison of mysql and postgresql. I'm a happy user of both, and I currently have pgsql serving a 8 million pageviews/month site, and handling load gracefully. AFAICare, pgsql is at least fast enough. I also never had any reliability/data loss problems with mysql, despite heavy concurrent access. AFAICare, mysql is robust enough. I'd really like to find out what are the core differences in both designs to get a grasp of how fast they may evolve.
If at first you don't succeed, skydiving is not for you
Another Open Source debacle. They add gobs of new features while leaving the most fundamental and important feature untouched (again). Feh.
Go ahead, flame away, I can take it.
The only certainty is entropy.
Yes, why ? Mysql is a very popular free sql database system , and I still have problems figuring out why.
It supports almost none of the sql features I need. The solution to my needs(which includes free/opensource) is PostgreSQL,
which do supports the features i require(subqueries, locking, stored procedures,views,triggers,other _real_ nice features..).
One point that often comes up is that mysql is very fast, and it is fast, but atleast for my projects only silghtly faster than postgresql(2-4%),
and in many special cases posgresql is way faster.
Also the point of mysql beeing very fast disappears if you use the locking features of mysql, BDB/Innodb tables.
For me, its postgres over mysql anytime.
1. Microsoft Access is more functional, easier to use and deploy than MySQL.
More functional? I could explain theoretically why that statement is utterly ludicrous, but I'll merley give a practical example:
A big British financial organisation maintained internally, its publicly available financial data in Access. I wrote a system for them, using GNU/Linux, MySQL and mod_perl, which allowed web visitors do perform complex searches on this data. The internally-maintained big Access dataset was imported into the MySQL datbase via an import system I wrote for them. Now here's the interesting thing - they began to realise that the web-based system, even over an ISDN line, returned data substantially more quickly and reliably than did Access, even after they upgraded to the (ACID compliant) SQL server! Indeed, the latter constantly corrupted their data, would get into all sorts of unremovable row-locks and god knows what else in its attempt to remain beautifully ACIDic. Eventually, they ditched it and now use my system (with a content management client they've had written which accesses MySQL via myODBC). They've been using this for just under three years now, and there has not been, cumulatively, even one minute's downtime.
This parent is not a troll. Our company's software has a DB abstraction layer allowing us to write database drivers for any relational database.
Except MySQL of course, because of its lack of nested queries. Perhaps we will be able to support MySQL when this newer version comes out - this will be a good thing, as it still holds the performance crown over Postgres (our current DB of choice).
I can tell already that this is going to be a flame war, but here are my two cents: PostgreSQL has stored procedures, unicode support, transactions, triggers, rules, and all sorts of other goodies! In fact, there is precious little that PostgreSQL doesn't have. I am using it for several projects at work, and I love it. It's great that MySQL is adding features, but it has a lot of catching up to do.
I can't understand why anyone would use MySQL when PostgreSQL is more free and without doubt far technically superior, even speed-wise PostgreSQL is faster; as of the 7.1 release.
I am quite frightened when i see people still using MySQL ...
... (AFAIR there is no views)
...)
...)
...)
.gdb files)
... go for a powerfull database ;-)
Ok, it's a nice database but it lacks from major steps :
- fast and decent transactions
- procedures
- triggers
- views
Why do not people user alternative database such as PostgreSQL or Interbase ?
For instance insterbase and its sister projects (IB Phoenix : http://www.ibphoenix.org/ , FireBird: http://firebird.sourceforge.net ,
The basic specs of interbase are :
- full SQL92 compliant (entry level)
- not fully SQL99 compliant
For instance you have :
- fast transactions
- super fast blob/clob feature
- procedure (full SQL92 here!!!)
- trigger
- strucutred data types
- JDBC2.0 driver (type 4 JDBC3.0 is underway
- cool tools (admin, major crash fix and recovery stuffs
- easy data deployment (thru
Under linux there are 2 architecture, the classical server and the super server (cf the docs).
There are also cool and nice free GUI admin tools such as IBAccess:
http://sourceforge.net/projects/ibaccess/
All these stuffs are opensourced and free (as in beer) !
No more hesitation
Foreign keys are supported since yesterday with the InnoDB table handler (check www.innodb.com for more infos).
But does it support foreign key constraints yet? This oversight on the part of the developers has caused me no end of headaches. I'm tired of having to write three thousand extra lines of Java code to do something that should be automatic. I'm tired of the MySQL developers playing fast and loose with the SQL standard. It's time to fall into line.
Denial isn't just a river in Italy
All very nice, but will it build properly on a MIPS system?
I've had no end of trouble trying to get 3.23.xx to build on our Cobalt RaQ2's, just refuses to play nice...
Listening for the sound of the coming rain...
Which front-end are you using for your MySQL-PgSQL Databases? :)
I am interesting from hearing about your experience.
I have tested:
- MS Access through MyODBC
- StarOfficce through MyODBC or UnixODBC (it is missing native support connection to MySQL but it is in StarOffice TODO list, maybe in forthcoming StarOffice 6.0?
- Rekall: it is still in Beta but seems really awesome
Do you know any other alternative which one it is your prefered? i would like hearing about you
Any serious work would be done in Oracle. Yeah, I know, I know (db2? Sybase?)... But seriously, I've got it up and running under Linux and it has everything. Connection times are no problem with connection pooling. Java Servlets make it very nice.
I tried to do all of this with mySQL wholeheartedly, and couldn't get anywhere. Once you've had full SQL support, you can just never go back.
phppgadmin
- daniel
Turn off your computer and go outside
I really enjoy using MySQL-Front. It's free, has lots of great import/export features, and is getting quite stable. There is another good tool called Mascon, which is available in a free and non-free version.
Have to say I 100% agree with this guy. PostgreSQL is light years ahread of MySQL, and is 100% open source. The GPL'ed version of MySQL isn't even free software! (The GPL version i always a subverions or two behind, I believe.) Why people continue to use it when PostgeSQL is out there defies all logic IMHO.
no, real men use MUMPS.
I can tell you. That shit is ugly!
Dave
In that past I've used Access to prototype, then exportsql (in the Mysql contrib page - it's too slow get grab a link right now) to create the script for mysql.
Now I generally use PHPMyAdmin to do it through the web.
"don't fall into the fallacy of believing that Perl can solve social problems. Maybe Perl 6 can, but that's a ways off"
This is great! When I forayed into the SQL land 3 years ago I dried postgreSQL first because it was a "pure" SQL that was a true GPL and free.. but honestly, MySQL is tons easier to set up and use and program for. So that is what I settled on, it just added a step for me, I had to show a client how to download and install it to meet the license requirement at that time.. (Shoulder driving, click there, click ok, now type rpm -i *.rpm.. cool I can take over now!) I use it extensively today, and the documentation seems to be greated for MySQL than postgreSQL. Is that a fact though? is there sources for postgreSQL for dummies? or nice comprehensive manuals? or 3rd party books?
Do not look at laser with remaining good eye.
Well, but MySQL is fast. It's the same reason why Sybase became popular. It was missing some important features (like row-level locks), but boy it was fast.
This also shows you that most applications using dbs are not that complex - just updating one row at a time is fine.
...richie - It is a good day to code.
But what's the best MySQL type of (transaction-enabled) tables :
Or something else, new to MySQL 4?
Anyone has experience or benchmarks with these different types of tables?
{{.sig}}
It goes a little like this:
1. Someone needs a small easy to install database quick.
2. Sysadmin knows PostGres is superior but also knows that MySQL is dead easy to set-up quickly. He has set up MySQL before since someone told him how easy it was. He uses that.
3. People are so impressed in the organization that he got the thing up quickly they start suggesting MySQL for larger projects where it falls flat.
4. The organization gets turned off to Open-Source databases and chooses Oracle or DB2 instead totally bypassing PostGres which is sad.
In the end PostGres gets completely bypassed. Lots of people cut their teeth on MySQL so when someone needs a small database set up really quick they choose it. If more people used PostGres initially I think they would never look back. However, I understand immediately why MySQL is used so often.
ACK
"Hatred is the coward's revenge for being intimidated"
DBTools Is a pretty nice Windows-only front end (Though it will easily admin a server on a linux box.) - It looks a lot like Oracle's tool did 5 years ago, but it really works pretty well.
This is the tool I recommend to people coming from MSAccess. I often use it when creating tables because I can never remember the syntax for doing auto-increment fields...
It will take you about 5-10 minutes to figure out how it all works and it doesn't insult the intelligence of the command-line crowd.
Hope this is of some use to somebody...
Cheers,
Jim in Tokyo
-- My Weblog.
At work we use oracle to run a system I'm supposed to operate. As the actual application is, to say the least, weak my major interface to the system is TOAD [from quest software]. Its similiar to DB artisan etc.
Do either Pogres or MySQL have similiar, free tools? if so any recomendations / problems. I'd like to play about with one of the 2, but really want a reasonable FE to do it in.
Thanks
According to the article:
"The GPL allows open-source programs to be changed by users, but those changes aren't official and can't be sold commercially unless they're given back to and accepted by the owner."
That's not exactly how I understood it. My impression was that the GPL was a recursive license allowing the free use and modification of code, but requiring that said modifications also be made freely available under the GPL. I didn't think the person whose code I was modifying had to accept my modifications in any way. Nor did I think it was directly incumbent upon me to send them to him.
I wonder what it is about journalists that makes them so capable of half-understanding so many things?
This use case is most certainly not fine in mysql, though, due to its refusal to support anything more granular than full table locks.
It's difficult to take a platform seriously when the documentation makes silly rationalizations like "For large tables, table locking is MUCH better than row locking for most applications" which is about the silliest load of crap I've read since they pulled the paragraph from the mysql documentation that explained why you'd never want to use foreign keys (it was removed once mysql began supporting foreign keys).
MySQL's main feature seems to be its immense popular support among people who haven't used any of the alternatives. Not a very compelling endoresement, if you ask me.
The point of these tools is to manage more advanced stuff like Sequences, constraints (including foreign keys), rollback segments, parallel queries, transactions, etc. etc. MySQL doesn't have anything much more advanced than very basic inserts and updates. What's the point of a tool?
My sincerest condolences to anyone trying to use this in a production environment.
Easy does it!
This comment has been submitted already, 276865 hours , 59 minutes ago. No need to try again.
Yep, phpMyAdmin is great; really everything you'll ever need to administer your MySQL database. Once a month or so I drop into command-line access via telnet to do a big, tricky query, but that's more a "because I can" kind of thing -- phpMyAdmin always allows you to execute queries directly if you want.
As far as user front ends go -- just write everything in PHP. Look at the phpMyAdmin code if you want some ideas; they don't mind! Anyone with a decent amount of Web development and general programming knowledge can whip up a good-looking, functional PHP-based Web interface for a MySQL database in a very short time.
The correlation between ignorance of statistics and using "correlation is not causation" as an argument is close to 1.
It's about fucking time!
MySQL has been mired in the stone ages of Dbase4 for years; these will be welcom additions and will definitely help MySQL overtake some of its lofty closed source competitors.
Me, I'm sticking with PostGreSQL. But I applaud the effort of the developers to make their application into something that DBAs worldwide can feel proud to add to their resumes.
Hey freaks: now you're ju
To all those who are comparing MySQL to PostgreSQL, I'd like to comment on why this is good.
First, competition will make them both better. The whole point of open source is *more* choice, not less. Stop complaining that MySQL is finally catching up to PostgreSQL and use which one you want. They both have their strengths and weaknesses. It's not like they both cannot coexist.
Second, I use MySQL occasionally now because most ISPs seem to support it. PostgreSQL is great, but I cannot use it because my clients have long since chosen their ISPs and it is a major PITA to change for many reasons.
Nobody stores critical data in Access.
Ahh, the naivete of the young...
People frequently do insane things like storing business-breaking data in access. And they absolutely refuse to listen to the $200 per hour consultant that tells them they're doing a very risky thing.
Note to ACs: I usually delete AC replies without reading them. If you want to talk to me, log in.
I'm not ragging on PostgreSQL. PostgreSQL is the most feature-rich, open-source implementation of a SQL92 database out there.
But the fact is, MySQL simply kills it in performance. Which is usually the number one issue with any kind of database. That isn't to say that PostgreSQL is bad. It just isn't as fast. That's all. Here's a benchmark of MySQL versus other databases... including PostgreSQL.
All of this being said... if MySQL gets the following features, I think PostgreSQL will lose ground on MySQL again:
1) Nested sub-queries/sub-selects *coming in 4.x*
2) Update a table with joins to other tables. *coming in 4.x*
3) Delete from a table with joins to other tables. *coming in 4.x*
4) Stored procedures *coming in 4.x*
5) Triggers *who knows*
With 4.1, they will be most of the way there. With triggers, they will be pretty much feature complete.
Personally, I'd like to see materialized views implemented once all of this is done. I think that having materialized views will position MySQL as a serious consideration in the IT industry an an 'Oracle killer'. Lots of companies have already trashed Oracle in favor of MySQL. Those that have not... usually haven't done it because of lack of features in MySQL.
Hopefully that changes.
You shouldn't be administrating databases at all if you don't have a good understanding of the theoretical context and these, eh "advanced" features. Shit, I've seen so many web-monkeys-turned-dbas fuck up their systems because they lack the proper education of basic rdbms functionality.
______________
OTTERS RULE.
Vintage computer games and RPG books available. Email me if you're interested.
I never hear much about "alternative" commercial databases like Frontbase or SOLID Server... what good are they ? Did anyone tried them in a web environement ?
But really... there are 'database people' and poeple that need to store data for a simple website that don't give a damn about acid stuff if it costs them anything more then mysql takes in resources.
And that someone that pointed out that the speed of grep is nigh infinite.... he should try 4Gb textfiles. When you next see him, be sure to give him the troll treatment - for that's what he is.
+++ATH0
No. Actually, speed (which is what I assume you mean by performance) is the last thing a database engineer is worried about; otherwise, you're just increasing the speed at which you fuck your dataset.
No. ACIDity means nothing if your SQL takes ten years to come back. As far as fucking your dataset because of high performance... I'm afraid I haven't seen that.
Now, agreed, lots of DBAs don't care about performance. Apparently, you are one of those. But from a usability perspective, you have to have good performance.
Enhanced MySQL (especially with Gemini tables) has excellent ACIDity. Automatic crash recoverty, ACID transactions. Row-level locks. SQL-standard statement atomicity. Replication. Index based queries. Table cardinality and referential integrity. Blocked I/O for good performance. Optimization statistics.
What I am saying is that MySQL is a fantastic database which not only has good features as far as ACIDity goes, but is also faster than everyone in the bunch -- although I'm not sure about fucking of datasets -- I've never had MySQL fuck up my datasets.
That was all I was saying.
I have used MySQL for web-based applications only until now. And while I like it because it is fast, easy to install and administer, as others have alread pointed out, I find it still lacks some rather important "features" (like views, nested queries and stored procedures), a fact that makes MySQL a non-choice for most of my customers at the moment.
How do they plan to do SSL? OpenSSL isn't licensed under a GPL-compatible license.
If they've built a whole new SSL library, I'm impressed.
Become a FSF associate member before the low #s are used
The GPL'ed version of MySQL isn't even free software! (The GPL version i always a subverions or two behind, I believe.)
You're wrong - the newest version of MySQL is GPLed (you can buy another kind of license from the company behind it if you need to). They changed their policy mid-2000, if memory serves.
When using databases as backends, I'd much rather use PostgreSQL myself (transactions without table trickery, foreign keys, views and subselects being the things I miss most in MySQL), but MySQL certainly is free - and the subset of SQL they do support is very useful, and sufficient for many purposes
Well 1 year ago no one in his right mind would touch PostgreSQL as it was buggy and slow as hell. Now it has improved (but could improve some more...)
At the same time MySQL was always rock solid and fast as hell but missing lots of features. They are improving on that too.
In the end we only have two databases with radical different philosophies - one rich but bloated and one fast but poor. Both are overcoming their weak points. In the end they'll be pretty close in spead and functionnality...
With MySQL its:
I spent a few hours one weekend trying to perform these equivalent operations with pgsql, etc. Granted I work with MySQL every day, but I wanted to try out PostgreSQL and see how it compares. It took me forever to find out that I needed to 'su' to user postgres in order to connect to the freaking database! Grr.
I'm willing to try, but somebody needs to point me to the right parts of some manual, FAQ, or HOWTO ... because dammit if I didn't actually go and do just what I said I would do above to make sure that it worked, and now I have an installation of MySQL running in less than 5 minutes.
--Robert
Enough of the MySQL bashing already. PostgreSQL is great ok? That doesn't mean there's not a place for MySQL.
The place I work for is probably going to go with MySQL, and I'll tell you why.
Currently, all our databases are in MS Access. The largest of which has about 12,000 rows, which could grow up to about 65,000 rows in my lifetime. In our couple of "big" datasets, updates are done annually.
99.999% of everything we do will be database reads, with the occasional conference registration type deal.
We require win32 odbc access, as we're still a primarily wintel shop (I don't like it, but I live with it for now). We need people to be able to connect, retrieve, and edit their data with Access, as it's what they know. MyODBC is far ahead of the PostgreSQL ODBC driver.
It wouldn't make much sense for us to buy Oracle, or run PostgreSQL. It adds uneeded complexity where just using MySQL provides us with the needed speed improvement (900 ms for a query against access down to 30 ms for the same query against the same data in MySQL).
Use the tool that fits the job. If we were doing GIS, storing gb's or tb's of data, needed massive fault tolerance, etc., we'd look towards Postgres or a commercial solution. But the truth is, we just don't need it... we need something inbetween Access and Postgres... something with the performance but relative ease of administration. MySQL fits that bill quite nicely.
One word: Windows.
MySQL is available for Windows with a simple, quick install. PostgreSQL can be compiled on Windows, but I think you need Cygwin to do so, and the installation routine is a readme, not an executable wizard.
Most small and mid-size ISPs offer MySQL on their servers, not PostgreSQL. Why? I'd guess it's because it's used more widely (yes, that's a catch-22), and because clients can easily install it on Windows to develop their applications.
For simple applications like a web site, you usually don't need the features of PostgreSQL, MySQL will do the job. If you need the features that PostgreSQL can offer, you usually develop with a database server which you can run Linux on anyway. But for applications devloped in small networks or even on a single computer, availability for Windows is a huge selling point because it's the number one desktop system.
Sig (appended to the end of comments I post, 54 chars)
Anyone know a good way to address a SQL Server database from an application using the mySQL API?
That is, is there any type of library available that would let my Unix C program talk ODBC to a SQL server system?
I know about ODBCSOCKETSERVER, and it's what I use now, but it's not robust enough for some of the things I want to start doing, and I'd really like to have a system that directly talks ODBC under Unix instead of having to go through a Windows box with ODBC installed. (I don't want to run the bridge on the computer that actually contains the SQL server interface).
Thanks for any thoughts.
D
come on, moderators, this is NOT informative.
Row-level locking is available in mysql by using a third-party table type...As are transactions, and page-level locking.
you use what you need, and for a lot of people, they really don't need to use correlated subqueries, they don't mind replicating the functionality of a stored procedure in perl or php, and they really don't need a 5-digit price tag.
MySQL's main feature seems to be its immense popular support among people who haven't used any of the alternatives.
really? so yahoo and NASA don't know about alternatives to mysql?
this is such total FUD...i really don't understand the motivation for everyone to tear down mysql based on these same objections and that damn phillip greenspun article.
can't everyone just let people use the solution that fits their requirements?
InterBase also has a very interesting transaction mechanism based on a multiversioning engine. One key benefit is that readers never block writers. You can process a long report of consistent (commited) data, without blocking data-entry applications.
Here is a description of this versioning system.
I must admit that I have no "real life" experience with this database. I don't know how fast or robust it is. Has anyone used it ?
MOD THE CHILD UP!
Flamebait, yeah you're right. I stand corrected - I'd hardly call it a good troll though, no mention of O(n)NP complete VM algorithms, or kernel programming in VB.
But maybe it is a good troll, after all since my reply to it (and someone else saying the same thing) it's been modded about 7 times, and its a vacuous little sentence...
The MySQL/mSQL book is the worst Oreilly book I've read. The online manual is far better, which is sad.
/. is irrelevant.
"MySQL: When your data doesn't matter that much"
Ehhh... needs some work.
"Do you expect me to talk?" "No, Mr. Bond. I expect you to die!"
>But the fact is, MySQL simply kills it in
>performance. Which is usually the number one
>issue with any kind of database. That isn't to
>say that PostgreSQL is bad. It just isn't as
>fast. That's all. Here's a benchmark [mysql.com]
>of MySQL versus other databases... including
>PostgreSQL.
Their posted benchmarks are for SINGLE threaded access. Unless you're using it for one of the very few niche applications that require this, the benchmarks are *useless*.
MySQL has a major problem with heavily concurrent access, particularly in instances where you are doing a lot of updates.
At work I'm migrating from MySQL to Postgres for precisely this reason. Performance has been going majorly downhill as the utilization has grown. And now its not uncommon to see as many as 50-60 threads stalled waiting for a table lock .
Yes, I'm aware that alternate table types exist for MySQL that implement finer grained locking. The problem is they are fairly new, and are in use on only a small portion of the MySQL installations out there; hence, they are nowhere near as well tested as Postgres.
Also, the benchmarks published on InnoDB are extremely poor. The comparison to Postgres suffers from the same single thread test only problem I mentioned and is far from comprehensive. The rest of it is even worse - the test for concurrency impact only shows results up to 50 threads for inserts, and shows a major dropoff in performance of selects between 50 and 100 rows. And he actually admits to poor methodology on the test against a commercial database.
Matt
MyISAM tables are not good in an environment where you are doing a lot of long running updates and selects on the same tables.
InnoDB tables doesn't however have this problem.
The benchmarks on the www.mysql.com page are still single users, but we are working on an open source multi user tests that will show how the different table handlers MySQL provides works under heavy multi-user load.
The single user benchmarks does however show the top speed for a database and the strength and weakness for each database, so they are still very useful on their own.
Regards,
Monty
PS: I agree that the benchmarks on the InnoDB web page are far from perfect.
And everyone knows that the guys at mysql.com are gonna be able to install, configure, and tune postgresql to be an optimal dbms just like they did with mysql.
These guys couldn't even get vacuum to run, a command I've never had fail...
They probably have no idea how to optimize the query planner, change the buffer memory blocks, or create the right types of indexes to accelerate the database. And that's ok, but you should realize these things before you go by a benchmark made by one company against a competitive product.
--- It is not the things we do which we regret the most, but the things which we don't do.
That is a function of how your tables / database are setup, rather than the database system it's running under.
Right?
The copper bosses killed you, Joe. 'I never died', said he.
The point is that any organization that promotes ideas such as "table level locking is superior to row level locking because it's faster" obviously has little understanding of what kind of features are needed for a robust database. That kind of attitude raises all kinds of questions about everything from their high level design down to the code. Data integrity isn't something that you tack-on to a database engine as an afterthought, it's something that should be designed in from the start. Have they ever thought about things such as data integrity problems caused by re-ordered writes by the filesystem? Probably not because it's not much of an issue if you don't provide transactions. (Yes, I know there are 3rd party add-ons that provide transactions)
It's encouraging to see 3rd parties adding sorely missing features to MySQL, but the point is that MySQL's developers should have at least realized what kind of things MySQL needs to be more than a glorified flat file handler. If they don't want to add these features that's their call, but it's a real disservice when they bash features they don't want to impliment because they see no use for them. If there was no use for them, then Oracle, IBM, Sybase, Microsoft, and countless other companies wouldn't have spent a lot of money developing them.
>MyISAM tables are not good in an environment
>where you are doing a lot of long running
>updates and selects on the same tables.
Unfortunately MyISAM is still the best backend that has a widely deployed long-standing backend.
>The benchmarks on the www.mysql.com page are
>still single users, but we are working on an
>open source multi user tests that will show how
>the different table handlers MySQL provides
>works under heavy multi-user load.
I applaud the effort. That doesn't change the fact that the current benchmarks have major deficiencies.
>The single user benchmarks does however show the
>top speed for a database and the strength and
>weakness for each database, so they are still
>very useful on their own.
It shows the top speed for a database under conditions almost never found in production environments and hides the weaknesses of MySQL by doing so. They are very much NOT useful to most people.
Matt
man -k postgres might do wonders for your clue deficiency, or try "apropos postgres", if yer "old school."
--
You sure got a purty mouth...
I don't think it's fair to characterize a third-party developer's efforts to compensate for a noticeably lacking feature in mysql as "Row-level locking is available in mysql", especially when the mysql documentation still goes out of its way to justify the reliance on table locks in the platform.
The bottom line is, mysql lacks row-level locking. It's not in the product. Moreover, the mysql developers defend this omission and claim it was a conscious decision which they attempt to justify in the documentation.
I'm pleased that the innodb folks have seen fit to try to overcome this liability in mysql, but let's not get carried away by describing it as a native feature.
I see a alot of the postgreSQL vs mySQL but not much on MS SQL vs MY SQL. I think 99% of us would agree that Access doesn't complete w/ MY SQL. But how many of you would push mySQL over MS SQL on a MS platform? I've had a chance to use both and actuall run both mySQL and MS SQL on the same DB server. For set up and configuration, MS SQL wins hands down.
As to speed tests, I would like to check some out (both straight SELECTs and queires where row locking is required).
As to the features, it's hard for me to imagine useing a DB w/o Stored Procedures, Views, and SubQueries.
Have you ever tried to do a Full Text Search through the 50million records? A reasonably high volume site I know (www.TribalWar.com) that runs vBulletin 2.0 had to remove the Search function do to mySQL crashing the server because it couldn't go through all the messages.
Might I suggest some additions:
These are all really great tools; why leave them out? I like Oracle too, but $10k per processor is pretty steep. Hey, it's big and slow, but at least it's expensive!
Wearing my programming hat, I have to use the right tool for the job. If I don't need lots of nifty DB features, I'll use my native filesystem structure to store data, or even a flat file. Remember those? If I need bells & whistles, I'll choose a DB that has just what I need, and no more. Why waste cycles (or licensing fees) on worthless features? If I need transactions (for example), and I don't want to spend $$ on proprietary DB licenses or on man-hours trying to figure out a complicated open-source DB (remember TCO?), I'll code it myself with something simple like MySQL on the back end. If I do need scalability/lots more features/whatever, I'll pony up the time/money for the big boys.
Right tool, right job.
This post expresses my opinion, not that of my employer. And yes, IAAL.
Try throwing 20 million rows in and out of the server everyday. Big RDBMSes still have a place. Write portable SQL as much as you can.
Nobody, but NOBODY, will challenge Oracle with a database unless SAP and PeopleSoft port to it.
I wish RedHat had understood this. RedHat might have been better off bundling SAP-DB and selling an SAP-optimized version.
I will agree that for 99.9% of MySQL users MySQL runs just fine. They don't need, nor care about, sub-selects, row-locking, triggers, etc.
:)
We used to run MySQL but found that it died *horribly* on heavy multiuser loads. (e.g. 500 concurrent users, all updating/insert/etc.)
I investigated the problem and found out that table locking really, really sucks. Last summer when we had this problem we didn't have the luxury to mess around with pre-alpha table structures and spend countless hours poking around with settings.
I carefully explored the other RDBMS's out there and eventually picked Sybase ASE 11.9.2 for Linux as the best choice. I can say it was hands-down the best choice we made. Now we're at 12.5 which supports SSL, XML, etc. and a host of other features MySQL hasn't even thought of.
Instead of 'dealing' with MySQL we're making money with something else.
So here it is:
If you are having problems with MySQL - DON'T PUT UP WITH IT. There are many other fish in the sea that will better fit your application. Simply because it is 'free' or 'popular' does not make it better for your application.
As someone else said, I always follow the 'pick the best tool for the job' test. If it is out of our price range, we either find a way to buy it or move on to the next item on our list.
I think far too often people perform the 'open source' or 'free' litmus test first -- leading to major headaches down the road.
If we were in this situation today I think we'd rather have picked Postgres, simply because it was a lot cheaper and offers many of the performance-enhancing features as the 'big three'!
Thanks,
--
Matt
No, referential integrity is provided by foreign keys, which MySQL doesn't always have IIRC. Triggers and rules can be used as well, but MySQL doesn't have those either.
If you fall off a building, go real limp, because maybe you'll look like a dummy and people will be like hey, free dummy
I keep on seeing the same statement, over and over, saying:
"I don't understand why MySQL is so popular, the only thing it has going for it is tha it's easier to install!"
Answer...staring...right...at...you.
Democrats or Republicans. They are both taking us to the same place and they are not afraid of us anymore.
But they are
then you are saying that the gap between the two products isn't that big.
I'm not saying that. It's a big gap, and not likely to close anytime soon.
That those features of PostgreSQL that have been around for years aren't anything to shout about.
That means that they are tested and proven. And work reliably now. That's worth shouting about.
Is it that hard for you to imagine that the everyone else is getting along fine with what mySQL has to offer?
If they understand the alternatives, and really only need a fast, small but junior leauge DB like MySQL, then no problem. If they are just ignorant, then I suggest trying harder.
My Karma: ran over your Dogma
StrawberryFrog
Also, my guess is that they didn't bother turning off all kinds of features in the other databases in order to put them on equal ground with MySQL. Features such as transactions, row-level locking, etc.
It's amazing how much faster a database runs when it doesn't have to worry about such 'niceties'.
Go MySQL!!!
:)
:))
Todat i was making a query which as quite hard, but eventually i succeded. But when nested queries are possible... Oh, this would be heaven!!
Now wait for the transactions
There was just one thing. I've reloaded the zdnet page many many times and every time a f***g Oracle banner poped up. Yeah, we know you're bigger. we also know MySQL is free and will run on my slowestt 486 with a maximum of 32Mb RAM
Privacy is terrorism.
Well 1 year ago no one in his right mind would touch PostgreSQL as it was buggy and slow as hell.
<cough>bullshit. I've never had stability problems on Postgres, and if you turned off fsync-after-every-write (default on) your performance was actually pretty decent. Postgres was (is) always faster than MySQL for any type of query other than straight SELECT x from y where [simple clause].
At the same time MySQL was always rock solid and fast as hell but missing lots of features.
<cough>more bullshit. We ran MySQL as the backend for a RADIUS server for our growing ISP about 12-18 months ago; it crashed regularly (MySQL, which took down RADIUS). We're now using Postgres for the backend and moved to GNU-RADIUSd and it's yet to die (the backend; the RADIUS server has an "every x months" bug that's hard to duplicate)... This is with 8x the load now, too.
You're right though, two radically different philosphies. Postgres is an RDBMS; MySQL was an SQL interface to a flatfile/hashed system. I'm watching MySQL to see how these new changes (transactions and ACID-compliance in general) are going to affect it, but it's got a ways to go before trumping Postgres, or any other RDBMS for that matter.
I've been using MySQL for a couple months, nothing too serious, just storing some content for my website. After reading the posts concerning MySQL vs. PostgreSQL, I was convinced that I should switch to PostgreSQL....In the end, I want a complete JDBC driver and SQL92 DB.
So, I went to the website and decided to order a CD. I'm on a broadband connection and it would download in a couple minutes, but I wanted to support the development.
I attempted, but their 'purchase' page had no submit button for the form. I let them know, and got the response that there was a submit button. I checked under IE on my roommates' win me machine (and in the html) and there is one.
So, I've heard all these great things about PostgreSQL? Anyone know when, or if, they will support Linux? Until then, I guess the MySQL vs. PostgreSQL argument is kinda moot...
take your sig and shove it
Also, it is much easier to pronounce. I never ran into an MP3 that was put online to teach me how to say "MySQL". :-)
Ditto!!! MySQL provides WAY more horsepower and features for 99% of web applications out there.
In the case of huge searches through gobs of text... Well, guess what... Any database is going to huff and puff on that kind of a query. We had the same problems with Oracle.
Does any search engine use such a simple query technique? NO! You have to do some (gasp) programming and (gasp) planning in order to create a (double-gasp) algorithm that works. Check out what the areyouhotornot.com guys did.
KDB is a very fast and efficient. It also has the best stored procedure language around (it may look like Perl, but it is no where close to it in philosophy).
.1 second.
----
on thursday jan 4, 2001 steve miano, ed bierly, keith mason and i
loaded 2.5 billion trades and quotes on a 50cpu linux cluster.
simple table scans on one billion trades, e.g.
select distinct sym from trade
select max price from trade
take 1 second
multi-dimensional aggregations, e.g.
/ 100 top traded stocks
100 first desc select sum size*price by sym from trade
/ daily high and close
select high:max price, close:last price by sym, date from trade
take 10 to 20 seconds
translating the data from TAQ to kdb took about 5 hours.
(steve had loaded the 200 TAQ cd's onto several disk drives.)
distributing the 100gigabytes over the 100Mbit ethernet took 3 hours.
(this cluster should probably have Gbit ethernet)
loading the database (k db taq.m -P 2080), starting 50 slaves,
connecting, mapping shared indicative tables over nfs, building
parallel partitions, etc. took
----
1. What is Kdb ?
Kdb is an extremely fast RDBMS extended for time-series analysis.
2. Does Kdb support SQL92, ODBC and JDBC ?
Yes.
3. Is Kdb a read-only RDBMS ?
No. Kdb is very fast for OLTP (online transaction processing).
For example, it runs over 50,000 ATM-style transactions per second logged
to disk with full recovery on a single cpu. This was against a database of
over 100,000,000 accounts, tellers and branches. Kdb can do batch updates at
several hundred thousand records per second per cpu.
4. Is Kdb a memory resident RDBMS ?
No. Kdb has minimal memory requirements and is very fast from disk.
For example, it ran the gigabyte TPC-D (an industry standard decision support benchmark)
queries and updates on a 200MHZ PC with 64 megabytes of memory, an ultrawide SCSI
controller and four disk drives many times faster than the best published results
at a fraction the cost.
5. What about time series ?
Kdb handles much more than just SQL92 tables. Online analytical
processing (OLAP) on multi-dimensional arrays is done with our
extended SQL language, KSQL. For example, on the 35 megabyte OLAP APB-1
benchmark queries, Kdb ran 12,000 queries per minute with no precalculation.
6. Since Kdb is so fast, does it require more storage ?
No. Kdb is simple and will often store just the raw data.
For example, in TPC-D, the published results required storage
between 3 and 10 times the raw data. The Kdb factor is a little over one.
Some OLAP tools require (for fast queries) massive precalculations. For example,
in APB-1 some expanded the 35 megabytes of input data to many gigabytes. Kdb
aggregates relations (extended with time series fields) so fast that precalculation
is often obviated. Certainly when the raw data is less than a few gigabytes.
7. Is there a parallel version ?
Yes. Although Kdb can handle much larger databases than other database
products without requiring parallel processing, there is a parallel
version for the largest applications. Kdb scales
----
KDB is the classiest database on the internet.
See http://kx.com
-j
I'm just going to have to keep hitting "refresh" until v .44 comes up ;-).