Slashdot Mirror
MySQL 4 - Is it Stable?
Shaklee3 asks: "I have been running version 3 of MySQL on the company's website for quite a while now. We recently ran into a problem where we needed the new features of version 4 that uses the UNION clause. We are running FreeBSD 4.6-STABLE and Apache 1.3.26. I know they reccomend not using it in a production environment yet, but from what I hear it is already being used on a few major websites. Does anyone have experience with version 4, and is it stable enough to run on a high traffic site?" If you feel MySQL isn't ready for prime-time, where specifically do you feel it needs improvement?
Is Sub-selects and foreign keys. These are probably the two biggest features I've constantly found myself needing / wanting.
Michael C. Hollinger
If you find yourself wanting more features, then ditch the toy DB and get something better like Postgresql.
I don't understand why people like MySQL so much when postgres is just as easy to set up and deal with, and has a much more complete feature set.
I've had enough abrasive sigs. Kittens are cute and fuzzy.
Running High traffic webboarding system, online stores, other various web apps running off of it and I have not had one problem related to mysql 4 not being stable.
I think it was good enough for production the day it came out, but they just wanted to keep it in Alpha mode for a while just in case...
No.
Its the endless battle..
Its worse then sitting in a room full of RedNecks fighting over which car is better.. Ford Or Chevy, with some silly idiot trying to throw in a Toyota once in a while..
Rephrase for slashdot
Its worse then sitting in a room full of SysAdmins fighting over which DB is better, Mysql Or Postgres, with some silly geek trying to throw in a Oracle once in a while..
Personal Website
I've been following the developments very closely and have had it running fine on my development box (P3, Win2K Pro) for over a month without a reboot.
I've also had a test site live to the world running off my broadband connection, so I could test stuff from work etc. and I haven't found any problems yet.
However I don't know how it would scale to a large site (the test site was 34 tables and only around 50k records in total).
I think a lot of the "don't use this on a live site" stuff is just to cover their own asses in case something goes wrong.
I am NaN
Mysql 3 supports foreign keys and transactions if you use innondb tables. i would love to see the next version of mysql have store procedures.
I have been using it for quite some time on a large active database. I have not had any problems with corruption, and it has been solid as a rock so far.
Why use mysql instead of Postgres ? Speed....
-- I doubt, therefore I might be.
If you feel MySQL isn't ready for prime-time, where specifically do you feel it needs improvement?
I'd respond, but Slashdot just keeps reloading the main page over and over again on me.
(/sarcasm)
--saint
I have reported this steganographic threat to the FBI. We must be VIGILANT.
If you are a MySQL user and don't understand why people who know databases don't like it, you simply have to consider MySQL's lack of common SQL DBMS features such as:
- Transactions
- Subselects
- Views
- Triggers
- Constraints
- Foreign Keys
- Etc, etc, etc.
You may not understand why such features are important, but that doesn't mean that having them available for use when you do finally learn about them isn't important. A DBMS without such common features is like driving a car without seat belts and air bags. You may get where you want to go, but woe to you if you run into any trouble along the way.BTW, yes, I know that Mysql supports foreign keys and transactions if you use innondb tables. But the point of using a SQL database is that you shouldn't have to worry about the underlying data representation.
Cheers,
Rob
Seriously, most SQL databases have moved on past the stage mySQL is at. The features you're asking for are standard in almost every other SQL-based product available.
I mean, it's like trying to use a calculator without an 'x^y' button. Sure, the calculator might be smaller and faster but do you really want to be hitting '*' hundreds of times when you could do '^' once?
You really are living in database technology yesterbidecade. Relational database software has moved on a lot since then - why not take advantage of it?
Does anyone know when it will become the official release? My Hosting company won't upgrade until it's marked stable, understandably, but I'd love to know when that will be.
THIS SPACE FOR RENT
This is just a rumor but I heard that this one guy, well he ran MYSQL4 and his arm blew off. I mean the whole thing, I am talking fingers and hands and everything.
new features of version 4 that uses the UNION clause.
As far as I know, IBM's System/R had UNION in 1974 and Oracle (back when the company was called Relational Software) had it in 1979...
Here is some info that might be relevant if you're running it on linux.
Don't know if this affects all versions of MySQL or only 4.0x
I am NaN
I promise not not to say I told you so.
You can likely change to a different database later as long as you don't use proprietary functions. Make sure you write standard SQL.
And before you say that what you write is SQL... You might not be. Check a few of your statements with an SQL Validator
Is it "Complete shit" week on Slashdot? I mean, the current bunch of shit is even worse than the usual shit!
I've been dying to dump our M$ SqlServer base for MySql for a long time now. Once MySql (finally) integrates stored procedures (which I think is now v4.2), that will happen, but not before.
I've heard the arguments for not needing stored procs before, but we have a application environment that is spread across multiple language and technology platforms and cannot afford the duplication in database logic.
Of course, if any one has some better suggestions, I'd be more than happy to hear them.
Ryosen
One man's "Troll, +1" is another man's "Insightful, +1".
I'd always thought MySQL was a fast, simple database until I built a Type-II JDBC driver for it.
Because the API does not allow more than one result (MYSQL_RES structure) per connection, and the client libraries are not thread safe by default, any Java classes must be synchronized on the connection. In addition, all rows in a result must be retrieved completely using mysql_store_result rather than the more network efficient mysql_use_result.
The JDBC specification insists (sensibly, in my opinion) that Statement objects be thread safe. The necessary synchronization and use of mysql_store_result severely limits the speed of any mutithreaded application sharing a connection, and probably discounts the speed benefits of MySQL over other free databases.
I would guess the same problems exist with other multithreaded languages using MySQL, and developers should consider these limitations before blindly agrreing with MySQL propaganda that the database if faster than its competitors for running web applications.
http://www.ejip.net/faq/postgresql-7.1.3.README
All you need to do is follow the instructions.
But I agree, a setup.exe would go a long way for increasing the use of Postgres on Windows.
MySQL's limitations are not a serious problem for me. Most database accesses in my applications are consolidated in a separate layer. It is very simple to duplicate features which may be missing from MySQL.
The support behind MySQL is outstanding, both from the mailing list and paid MySQL support. MySQL is extremely simple to set up and can handle quite impressive loads.
To keep score, MySQL + Innobase supports transactions (w/ row level locks and multi-versioning), foreign keys. 4.0 introduces UNION statements and a supposedly cool query cache. Pretty soon should come subqueries, stored procedures, etc.
That said, I've not used 4.0 in a production environment. What we have right now (3.23.51-max) seems to be doing the job just fine.
Uptime: 50139675 Threads: 9 Questions: 193188168 Slow queries: 508120 Opens: 2274831 Flush tables: 24 Open tables: 24 Queries per second avg: 3.853
Running 3.23.25-beta on Solaris. The db uses 45GB.
Having dealt with a lot of databases in a production environment here's my take:
You absolutely don't want to run any database which is designated "not stable" in a production environment.
Or put it another way: If I'm your boss I won't fire you for lacking features of the database. If we decided on this database engine we work around the shortcomings. But I'll have your ass sacked in no time if you install an unstable version of the product and corrupt the database in this process.
If that seems too harsh: You may explain to me the business reason and the risks associated and get it in writing that your management is aware of what's going on and knows of the risks.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
.. if you need features that your software doesn't have, get some other software.
Don't answer me. Moderate. Slashdot is about moderation, not discussion.
This is a summary of how many 'hits' I found for selected terms and pairs of term from 12 Dec 2001 to early July via Google's groups search when I was comparing Postgres and MySQL to see which is more trouble to maintain. You can draw your own conclusions as to their relative quality as based on their 'complaints' percentages.
postgres 17800 100 percent
MySQL 248000 100 percent
Postgres crash 358 2 percent
MySQL crash 1930 0.7 percent
Postgres corrupt 41 0.2 percent
MySQL corrupt 510 0.2 percent
Postgres slow 558 2.3 percent
MySQL slow 2830 1.1 percent
Postgres buggy 41 0.2 percent
MySQL buggy 297 0.1 percent
Postgres bugs 612 3 percent
MySQL bugs 7540 3 percent
Postgres problem 4520 25 percent
MySQL problem 42200 17 percent
Postgres hung 46 0.3 percent
MySQL hung 222 0.1 percent
Postgres happy 328 1.8 percent
MySQL happy 1810 0.7 percent
--
Benjamin Franz
Lameness filter encountered. Discussion aborted!
Reason: Please use less 'lame' filters.
In a recent database benchmark, the Alpha version of MySQL beat DB2, SQL Server and ASE. It kept up speed right along with Oracle (a expensive product to say the least). Check It Out
"Postgresql is slow" is a very popular myth and urban legend.
It even was a true myth - with older versions of Postgresql. Since 7.1.3 big improvements in the query-optimizer gave Postgresql big speed improvements - without stripping any of it's features.
7.2.1 is lightning fast.
Postgresql Tips for today: Do check out
Have they fixed the bug with the journal system yet?
Okay MySQL has its limitations but when it comes to doing simple web work and not so simple web work it does the trick.
For simple work I would rather use a free as in beer DB than pay thousands of dollars for features I just don't need.
http://www.mysql.com/documentation/lists.html
Here you will get few real answers to your question, and allot of chatter about which db you should be using.
Get a free ipod.
There are a number of nice and feature rich free databases available.
Postgresql is one option. You might want to look at SAPDB too. It is open source and runs as backend database for SAP-ERP in some big installations.
Hate to say it but VB and windows do have uses (insert snide comment here) ... just as mysql does. For certain things mysql is the right way to go. I'm sorry but I think postgress is slow ... for my simple selects mysql does it faster.
.... if it doesn't do what you need DON'T USE IT .... end of story, but for some of us we get along just fine.
It always amayzes me when people complain that mysql doesn't have this or that
And yes I do work (@ work) with "real" rdbms (oracle, DB2 , ingress). And yes I do like the features they offer and frequently use them.
Sticking feathers up your butt does not make you a chicken - Tyler Durden
1) If you need UNION capability, you are likely starting to get into the advanced SQL area where MySQL starts to break. I would suggest checking out PostgreSQL, which will have support for a lot of these advanced features.
2) The other alternative is to eat a little CPU and use a temporary table to get around the lack of UNION in v.3x. I've had to do this before when I was building an app using MySQL, got 90% done, and then realized I needed to do a UNION. To work around it, I did four separate queries into a temporary table, did my "union" query on the temp table, and then dropped the table. This creates the same result as UNION, but with a little more CPU overhead and a lot less elegance. But it beats either:
a) Rewriting your app to use PostgreSQL.
b) Taking a chance on a new and unproven version of MySQL.
Is anyone using SAP DB? http://www.sapdb.org ?
Most open source database debates revolve around MySQL vs. PostgreSQL. Why is SAP DB hardly ever mentioned?
I'm asking this because I'm evaluating SAP DB right now. So far it seems pretty robust. It also have an impressive list of features. So, Is there a catch?
I was at the Open Source Convention in San Diego last week.. I can't remember if it was Yahoo Finance, or Slashdot that uses 4.0 on all their slaves, but still has 3.23 as their master...
sapdb.org.
I have played with it a little and it seems very powerful, free, and supports multi processors.
I would be interested in any real world experiences.
Gizmos Gagets For Ninjas
Why bother with the endless MySQL/Postgres dispute when a completely redone Interbase has just gone gold with 1.0 final?
And it's GPL'd of course.
We suffer more in our imagination than in reality. - Seneca
If you ever had the pleasure of working for a little company in Burlington formally known as Epsilon then you would now the X System (not X11) is what you really want. Too bad the company pissed this software away. It could do queries on terabytes of information in realtime and this was back in the late 80's or early 90's.
Sad, very sad. I tell you no lies....
It is expected that any mssql stored procs would have to be converted. But that is the price that we are willing to pay in order to have a singular repository for the database logic.
Based on the number of people here who are saying "PostgreSQL", we're going to give some consideration to that alternative.
Can anyone relate their experience with PostgreSQL with respect to performance?
Ryosen
One man's "Troll, +1" is another man's "Insightful, +1".
PostgreSQL has been suprising the hell out of us. We moved a hosting crm/billing system from MSSQL to PostgreSQL and haven't looked back. The only thing that we ran into was the allowed laziness with @@IDENTITY after inserts. PostgreSQL's serials are just as nice, just a little extra work of naming them in your sql stmt.
ÕÕ
http://www.mlug.net/mlug-list/2002/msg00453.html
The discussion was about properly written software should be portable, and Ruben (who's been involved with MySQL for years) said:
MYSQL won't compile on BSD with the new tables.
(thus proving MySQL is poor non-portable code)
This conversation happend centuries ago and continues on today apparently...
Shrek: MySQL is like onions
Donkey: Ya mean it has layers?
Shrek: No Donkey! I mean it goes like stink! really fast but limited flavours
Donkey: Not everyone likes onions, I think MySQL is like cake, lots of flavours and everyone likes to eat cake!
Shrek: MySQL is not like cake, it is like an onion!
Donkey: Well what about Postgres? Postgres is like a cake...
Shrek: Yes Donkey, Postgres is like a cake, but MySQL is definitely an Onion...
From excellent karma to terible karma with a single +5 funny post...
I think I'm in a position a lot of people are in - I'm a back-end coder, who doesn't specialise in databases. Usually working in a team, there will be some database specialist, and I'll lead the team designing the engine that uses it, and someone else will lead the front-end. So I know some about databases, but I'm not an expert.
I also use databases for some of my own stuff. I've found mySQL to cover everything I need to do. On a rare occasion, I have to push the boundaries a little, but that's not impossible with a little design tweaking.
So my question is, I would like to learn more about these extra features - I know what most of them are and do, but I don't have the database structure knowledge to know what situations they should be used in, and more specifically, how to design databases to begin with to take advantage of them.
Online resources would be preferred, I don't want to spend $50 on a book I won't use THAT much, but they will certainly be considered.
Thanks,
Fross
... just use Excel. The development time is quicker and faster than building a database in Oracle or Foxpro or whatever. Just use a spreadsheet row for each row in your table and reimplement all the useful functions like SUM(), COUNT(), INSERT, WITH UPDLOCK etc in Visual Basic. If it gets corrupted just hire some cheap data entry people to type it all in again.
What could be easier?
Perhaps this is my ignorance, but I have not found a query that used the union statement that could not have been better written with a proper ANSI JOIN statement. ANSI JOINs are awesome, so awesome that I wish the UNION command would be written out of the standard.
"All great wisdom is contained in .signature files"
Just use Excel. The development time is quicker and faster than building a database in Oracle or Foxpro or whatever. Just use a spreadsheet row for each row in your table and reimplement all the useful functions like SUM(), COUNT(), INSERT, WITH UPDLOCK etc in Visual Basic. If it gets corrupted just hire some cheap data entry people to type it all in again.
What could be easier?
A new system I developed is using it in production without any problems whatsoever.
a MySQL 3.23 box is the database master though for safety, but it's being replicated to a bunch of MySQL 4.01a slaves. That gives the best of both worlds.
I chose to use it for it's result caching feature.
To tell the truth I'm actually experiencing a problem with the master crashing when I use the delay_key_write feature. All the slaves (which are processing exactly the same data - they're replicated remembers) have been rock solid.
Toyota is far better than any Chevy or Ford. That silly idiot is probably a geek among the idiot rednecks.
Understanding is a three-edged sword. -- Kosh Naranek
But features come at the expense of resources, so feature filled versions of MySQL are not especially fast.
Since most apps grow over time, why not use Postgres which comes with all the features you could want from the outset, and not have to hope that a stable version of MySql that meets you needs will be released?
Eat at Joe's.
MySQL has a speed advantage, but that is because they didn't choose for data integrity options, in favor of speed. (think about lack of roll back buffers).
Instead of using one complex query (subselects)mysql users have to write serialised less complex queries and pass data on the application level.
This drops the speed advantage on MySQL.
Laurence Olivier(nazi):Is it stable? .. Is it stable?
Dustin Hoffman(guy):You're talking to me?
L.O.: Is it stable?
D.H.: What stable?
L.O.: Is it stable?
D.H.: I don't know what you mean. I can't tell you something's stable or not, unless I know specifically what you're talking about.
L.O.: Is it stable?
D.H.: Tell me what "it" is first.
L.O.: Is it stable?
D.H.: Yes, it's stable, it's very stable, so stable you wouldn't believe it.
L.O.: Is it stable?
D.H.: No, it's not stable, it's very dangerous, be careful.
It's only fast in tests that don't reflect how databases are commonly used. If it's slow through a high-level driver (which is how most people access databases), then the mysql speed claims are bogus.
Firebird is sweet too.
If you're not restricted to using MySQL there are quite a few other options available, give it a try !
"Naughty, naughty, naughty, you filthy old soomka !"
This is retarded.
Don't use MySQL if you need a full featured DB, because it lacks many things you need for data integrity. Only use MySQL for small apps. These features were left out so MySQL would be a few nanoseconds faster when scaled to Slashdot sized apps.
But, as stated, people should be using MySQL only for small applications, so speed should not be an issue.
So, in conclusion, you are all retarded and it never makes any sense to use MySQL. End of story.
--
I've tried compiling MySQL version 4.0.2 on OpenBSD.
First of all, to even get this thing compiled, you'll probably need to apply patches from the ports. See http://www.openbsd.org/cgi-bin/cvsweb/ports/databa ses/mysql/patches/ for OpenBSD ports patches.
For example, I've needed patch-innobase_include_univ_i to compile any recent MySQL to compile on OpenBSD 3.1.
That patch is quite funny:
When installing MySQL 4.0.2 I've applied all of those patches, and then configured, compiled and made make install.
The database new MySQL daemon started up and operated fine (with previous version's data files), but mysql 4 client were unable to connect (I've got an "ERROR:" error message. Tells much, doesn't it?).
So, to summarize, wait some more time, at least until they release a beta.
I've been running a site on 4.0 since last December. The only issue that I've run across is occasional index corruption with a fulltext index. Other than that, I've enjoyed the new features.
What about Firebird (http://firebird.sourceforge.net) isn't it an OpenSource'd version of Interbase?
Has anyone used it? How is it compared to MySQL and PostgreSQL? I am migrating from M$ SQL Server and ASP to PHP; but I need the Stored Procedures and some other functionality that MySQL does not yet support; so MySQL is out.
I am currently toying around with both PostgreSQL and Firebird but haven't come to any conclusions on either of them yet. It seems that if the version of MySQL does not yet support what you need and the version that does has been listed as an Alpha Release, PostgreSQL and/or Firebird may be a viable solution.
we use MySQL 4 here at hotjobs to sync up our resume database with our search engine (altavista). works very well under high load. no problems thus far.
one thing i wish that the mysql team would add is error trapping for when the disk fills up. oracle handles this gracefully, mysql doesn't.
Oh well, that's strange because I just did it several months ago (and then just did ./configure, make, make install to get it working)
Sorry, I've posted this before but anyways...
l K ey s.html
Alternative lyrics to YMCA:
--
Verse:
Transactions, no they're not in the spec
Procedures, and let's not forget
Triggers, Monty says we must not let
Foreign keys into the feature set
Chorus:
This lightweight database...
MY - S - Q - L It has the tables locked
MY - S - Q - L
Well it's fast and it's free, but you surely must see
it doesn't pro-vide a-tom-icity
MY - S - Q - L It's not relational
MY - S - Q - L
You can do a SELECT but it's less than perfect
You can't gar-un-tee in-tegrity.
--
I think it would be great if you DB geeks would write some additional verses.
http://openacs.org/philosophy/why-not-mysql.htm
http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_
http://www.pgro.uk7.net/innodb1.htm
sysadmins rather than dba's choosing the db.
Why bother? Because numerous existing (== don't have to write/rewrite them yourself) packages can use MySQL or Postgres, but far less use Interbase.
Remember that what's inside of you doesn't matter because nobody can see it.
*shrug* The assclown Rubin was asked about it, and s/he said "go look it up", and never was able to back up the claim.
You can find some good documentation here
It is the documentation for a proprietary database called Mimer SQL Engine but it is very close to the SQL standard so it should work on many databases.
Now, admittedly, many databases are not adhering to the standard very well but that situation is actually improving.
You can also try the SQL validator they have here. It'll help you adhere to the standard. You might learn a thing or two too.
In many cases you can do the same thing both in a proprietary way and in the standard way for any given database. Given that choice I'd suggest that you use the standard.
The Internet is full. Go Away!!!
We (I) upgraded to MySQL-4.0.1 when it came out (after a little testing at home).
;)
... no problems for me.
I upgraded to MySQL-4.0.2 about 2 weeks ago.
We have about 5 databases with 10 tables in each which have between 1,000 and 100,000 records per table. We have about 30 users connected to the MySQL server from an Access 2002 front-end. I have been moving stuff from SQL Server 7 as it bogs down. MySQL-4 seems to handle multiple connections better than SQL Server - update queries that used to timeout (and crash Access) when in SQL Server now run effortlessly.
By the way, our little MySQL beast is an AMD K6-2 500 with 256MB, and is also running an IMAP server for about 50 mailboxes. Oh - and don't forget VNC
The ONLY problems I have had have been with the MyISAM table handler with large tables & multiple users. I was getting locks and time-outs, so I upgraded them to InnoDB, and have had no other problems since.
I have also started using transactions (which InnoDB supports). Seems to work perfectly for me. Admittedly, I'm not doing anything major, but any
Foreign Keys are also supported by InnoDB. Works well. MySQL-4.0.2 just made foreign key constraints survive an alter table command (4.0.1 used to dump the constraint).
What else can I say? I've been very happy with MySQL-4.0.x. Certainly no crashes or anything unexpected. And the --log-update startup option gives you a nice running backup anyway...
I would upgrade. I think the 'alpha' versioning is being too modest.
So, the original question was, is mySql stable enough to run in a live situation. Not which db is better.
Let me repeat the same things I say every time:
... 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."
Why MySQL is Not Suitable for Enterprise or High-Volume Use
or
MySQL.com misleads you about it's capabilities
Replication in MySQL is a joke for 'mission critical' use. As I understand it, the binary log records SQL modification statements which are executed on a master, not the data which was changed. This is involves significant assumptions beforehand, such that the master and slave(s) must be 100% identical. If I perform an UPDATE on the master, the changes are not replicated, but the query. This is what I would call the 'easy way out'. Who knows what happens to the query once it is replicated out - what if it hangs halfway through? I can't roll back and be in a consistent state, I have half-completed changes which makes my database inconsistent and now I'm forced to dump-and-load. Keep track of which rows are modified, to what from what, and ensure that those transactions are replicated to my slaves. Anything less is simply useless for high availability.
I would also be willing to bet that a significant number of installations that have transitioned to MySQL replication are doing so due to table-lock induced latency. A suitable system with a capable RDBMS could probably handle all of the load given to it and not need 'many slaves' to handle the extra traffic. They would have a single failover for high availability and that's it.
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
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. Postgres can do this with an add-on which is well worth the money. As far as I know MySQL can only do this with InnoDB tables and is a for-pay feature (since it has a MVC log to use in the meantime).
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 12GB Sybase DB in under 20 minutes. Loading it all (from scratch) and then bringing the DB online is about the same amount of time. MySQL stupidly logs the CREATE TABLE / INSERT statements. What does this mean? That I have to wait for 4 million INSERTs to be performed when loading my table, and FURTHER I have to wait for the INDEXES to be re-created on the new data. Dump the indexes, too! (Remember that full-text indexing is just another index, so if you use that and have to load from a dump be in store for SIGNIFICANT downtime).
Ability to specify the number of files to dump to. What happens if you have a dump which is larger than 2GB? Some linux distros cannot handle a single file of 2GB or more without recompiling the kernel. Give users a way to, within the dump statement, split the dump over two files. Not only will that help avoid the 2GB limit, but it can speed up dump/loads since I can dump to a bunch of different disks to improve throughput. 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. This also impacts the fact that MySQL tables and indexes are stored in filesystem files that are also subject to a 2GB limit.
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.
MySQL's query optimizer is PISS POOR. 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! Hands down the query optimizer 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.
Of course, the usuals: integrated row (or in the least page) locking, full support of subqueries, stored procedures, views, triggers, referential integrity, transactions, etc. etc. etc.'
PostGRES and virtually 100% of 'for pay' RDBMs have this. There simply is no reason to use MySQL for anything sufficiently non-trivial.
Thanks,
--
Matt
Why use mysql instead of Postgres ? Speed....
Ah, I see, you have the unusual requirement that your database must be slow...
INSERT INTO table SET column='value';
syntax error SET
MOTHER FUCKER!
DOH! ;-)
Sticking feathers up your butt does not make you a chicken - Tyler Durden
If I were you I would look into converting over to postgreSQL.
postgreSQL is kind of like the shy geeky kid in the family that no one ever heard about - seems like all people do is rave on and on about mySQL, but aren't aware of the limitataions.
unlike mySQL, postgreSQL will support subselects, row-level locking, union join, foreign keys, and the single most advantage over mySQL - it actually scales.
Its pretty fast too. for tiny databases mySQL is of course the king, because of its primitive structure, but once the db grows a little, postgreSQL is actually much faster than most dbs out there. odbc drivers are out there, and assuming you haven't been doing too many mySQL specific commands like "SELECT * FROM TABLES" the transition should be fairly easy.
Even if you define a subset of standard SQL that will run cross platform on a variety of target environments, it doesn't mean it will run well. For any serious (high volume, high performance) application , the queries you write end up being tweaked significantly to support the features/bugs/idiosyncracies of the query optimizer of a particular RDBMS. Move your standard queries to another vendor & watch your performance drop through the floor. Plus, limiting yourself to the subset of features that conform to standard SQL often prevents you from reaching that next level of performance (server-side procedures, bitmap indexes, anyone?).
The wonderful world of write-once SQL statements that will work well on all vendors' systems only exists if you don't have tight performance requirements (which, to be fair, does constitute a large number of applications out there).
-BbT
I had no problems using MySQL 4 for a while a month or so ago, for development purposes.
The UNION issue brings up yet another problem with SQL: it is too hard to self-extend. If something like this was used instead
http://www.geocities.com/tablizer/relat2.htm
then it would be relatively easy for somebody to add their own UNION equivalent, or whatever else is missing. It breaks queries into smaller, simpler functional-like statements instead of One-Big-Wad, like current SQL is. Although it looks like it would limit optimization because it seems sequential, the sequence is nominal only. The optimizer could ignore the sequence as long as the final result is the same.
Table-ized A.I.
Some years ago I used the Raima "network" database, which was non-sql (links between data represented by pointer-like links rather than shared keys), mapped very nicely to C structs, and later C++, and was blazingly fast.
Anyone out there still using it? I think they call themselves "birdstep" now.
Too bad not that many folks know about it!
MySQL needs several improvements before it can be trusted with data.
- Needs to handle memory limits better. 15 threads shouldn't be able to allocate more memory than the kernel allows (through ulimit-style limits). As it is now, MySQL hits that limit and then sits there and consumes 99.9% CPU forever. Won't die to kill 15, requires kill 9, which forces us to isamchk. Bad, bad, evil.
- Replication should not be query based, it should be data based. As it is now, replication is nothing more than a hack. Because it is a hack, it is very easy for the slave to differ from the master, which requires shutting down the master (effectively) while you restore the slave. This can take hours. Bad, not so evil. Just bad.
- MySQL can't tell if a database is corrupt, at least not in every case. Sometimes it'll sit there and chew CPU for hours trying to process some impossible data. This is a show-stopper, IMO. The first thing (the memory) is the typical reason for it to corrupt its databases.
- Documentation. MySQL's online documentation is horrible. For instance, they claim that SET SQL_LOG_BIN = 0 will stop replication. But in actuality, it does nothing (and they claim it works in the version I'm using, so don't bother). This wouldn't be such a problem if they didn't change syntax so drastically between minor versions. This is very bad, but not necessarily a show-stopper.
Some minor things:
- Explain doesn't work right. It doesn't return the right number of rows for a query. It's seemingly random. I don't know why.
- Insert delayed. What the hell is the point of this? It's about 10x slower when using mysqldump | mysql than straight inserts. The documentation indicates this should not be so.
- The MySQL team seems more interested in pushing out frequent updates and calling them stable than actually testing and making sure that their server is stable. Now I'm not suggesting a 5 year beta period, but something needs to be done about this. As it is now, I'm barely comfortable with 3.23 at all. 3.22.32 was probably the last great MySQL distribution.
I probably should have a lot more rants here, but I can't think of them now, I'm too busy trying to figure out why MySQL is so goddamned slow.
MySQL 4.x is not ready for prime time. You should trust the MySQL dev group on this. Keep using your 3.x version just code around your query problems. It's usually just as fast; but perhaps not as elegant. BTW, If MySQL sucked so bad then "Yahoo", "Google" and millions other users wouldn't be using it would they! There just jealous that MySQL is hands down the best. So don't let any of these whackers "whacked out hackers" try to fool you!
Sticking feathers up your butt does not make you a chicken - Tyler Durden
Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache
Don't blame the OS for this (assuming here we're talking about a POSIX-ish OS). It doesn't "tell MySQL it was written." What part of write() says that it returns only when data has been written to disk?
Buffering is desirable for most applications, and the OS places the decision whether to use synchronous or asynchronous IO in the hands of the programmer. Use fdatasync(), fsync(), or open with O_SYNC (in order of decreasing efficiency) to ensure your data is written to hardware. It's not the OS's fault if programmers determine that a transaction written with asynchronous IO is complete.
Let me guess, VB front end. . .
There is a VB object interface, but if you use that to open and close a lot of Caché records as objects in a loop, it is going to be impressively slow.
Also using dynamic SQL through VB is possible, but the back end has to compile each query individually. This also could make things slow if used in a loop.
Basic client/server design methodology, really. Probably not Caché's fault.
I rid myself of a great deal of funkiness by getting the latest version (4.1.5 for Linux IIRC), making sure the client Windows ODBC drivers match, and judiciously using %UnSwizzleAt() when working with Caché lists (one thing I don't like about Caché is that when you %Close() an object, it may not really close if it's being referenced in a list, you have to unswizzle it manually)
HTH
Sorry if this is a little OT
Speaking as the emergency backup holographic DBA who has experience with both MySQL and other Commercial Databases particularaly Oracle, I can give you the following info.
MySQL is small, fast and you can even use it with MS-Access with MyODBC
The drawbacks to MySQL are limited SQL support e.g. (no subselects, no inline views, no stored procedures, and just you TRY to figure out the outerjoin syntax (geez) ), however if you are doing simple queries it's fine. If you want to do more advanced stuff and say have multiple cursors open at the same time you have to use an additional language like Perl with DBD/DBI.
Also, MySQL does not have "read consistency", "row level locking", or the concept of a "transaction" (at least not last time I used it). If you do an insert/update it happens NOW, no need for that pesky SQL "commit".
Again, on the plus side, generally speaking MySQL is FAST for queries! However, when you do hit a snag, it is harder to tune performance and optimize the layout of the database on the physical disks e.g. (You can't partition a table across multiple disks/filesystems and have to rely on RAID0 striping). Also, I don't think there is anything as replication so keeping a hot standby database for failover or disaster recovery can be tricky.
The most important thing to keep in mind is this, "Use the right tool for the job". I still prefer any data I care about or, database that may affect my sleep be an Oracle database. However, replicating data from Oracle to a MySQL database, then using MySQL as the backend for query intensive web applications might make more sense e.g (Amazon-type, Slashdot-type). In this scenario, your data is tucked away securely in an Oracle database, but it feeds a bunch of lowcost, commodity beater boxes that can be quickly deployed to give lowcost scalability and more peace of mind against hacking.
Weigh the importance of your data and "use the right tool for the job". It could be argued that the most valuable asset of a modern company is it's data.
One of my favorite quotes which applies to this situation is: "When the only tool I have is a hammer, every job looks like a nail."
Good Luck!
getting back to the topic (4.x stable or not) ..
.. it scaled better, but the general performance was to low to even try it in production
.. but its still out of question because of the price tag .. i can pay a bunch of programmers a long time for the same money, and so we tend to throw some brain on the problem instead of money
.. so your mileage may vary, but for what we do here mysql has been proven to be the best solution
short answer is: if not used with replication, yes
bout the advocacy: shure mysql has its short comings, but it heavily depends on what you do and need
i have a mysql server that has a average of 3000-6000 queries a second, with peaks going up to bout 60000 q/s, 800-1600 connections, bout 30GB of data (which doesnt say much), lots of tables with bout 1.000.000 rows that are read only, some smaller that are frequently updated (200-300 updates/inserts/deletes per second on some tables)
server hardware is a SUN E6500 (18cpu 400MHz, 16GB RAM), table type used is myisam except some tables with sensitive data where i need transactions, there innodb is used)
wouldnt call it a simple at all, but its a web app
beside others(interbase and so on) i gave postgres several tries (latest version i tested was 7.1) because of having subselects and views
oracle might be a option (beside having a bunch of features more than mysql it suports clustering which i would like to have for scalability reasons)
with a different application the situation might change completely
sorry for the bad english
Wow someone is rather long winded. I just wanted to touch on the not allowed to have multiple indexes thing, I pretty sure that has been allowed in MySQL since about version 3.x maybe all the people who want to say it doesn't do this and doesn't do that should at least check to see if they are full of shit before posting. Also I don't think the guy who started this thread asked if some other database was better than MySQL, he simply asked is MySQL 4.x stable. I'm guessing you don't use MySQL from your wordy post so you should have just stf and moved on.
Postgres does not take advantage of indexes when using aggregate functions.
ie.
select sum(number) from table where type=15;
Will not use the index on the type column. This has got to be the stupidest thing I've ever heard of.
The reason? "Aggregate functions are blackboxed. We don't use them and neither should you. We're afraid of fixing the code."
Wow, a shining example of someone firmly planting their foot in their mouth on Slashdot. What a surprise. It's nice that the post was moderated up to 4 as ``Informative''... too bad most of the information was useless or wrong.
.
... Currently MySQL gives you meaningless info like 'slow queries'
The overarching problem in your comment was your opening statement:
'Let me repeat the same things I say every time'
The problem there, which is obvious to anyone remotely understanding of what active development means, is that MySQL is sort of a moving target. Your statements are erroneous is so many ways, but most of them can be boiled down to this: you are arguing against something that no longer exists. MySQL, as you attack it, is no more, and has been replaced by something far better. So let's just take some of your arguments (unlike you, I refuse to speak on those things that I *DON'T* know, so I'll skip a few with which I'm not familiar) and see how they stand up, shall we?
Argument: Replication in MySQL is a joke for 'mission critical' use.
Rebuttal: Somehow that fact doesn't impede Yahoo!'s extensive use of it. (See Jeremy Zawodny's presentations at the recently-held OSCON.) If the query hangs on the slave halfway through, it isn't marked on the slave as having completed. When the slave becomes available again, it notes its pointer in the transaction log and catches up automatically. Oh, and replication (at least in 4.x, possibly also in 3.23.x) is transaction-safe.
Argument: Filesystem buffered writes.
Rebuttal: As another poster wrote, leave the OS out of this. If you cannot properly configure your OS to not buffer writes, you probably shouldn't be running a 'mission critical' ANYTHING.
Argument: On-line backups [are not there]
Rebuttal: So set up a dedicated slave for backups. Turn off the slave while backups are running, it catches up when backups are done and it is brought back up. A *simple* solution to a *simple* problem. If you really feel the need to do a hot backup of your live server, you can check into using InnoDB's tool at http://www.innodb.com/hotbackup.html
Argument: Backups to something other than CSV files.
Rebuttal: You mean like backing up the raw MyISAM files? Of course, that doesn't work with InnoDB databases, so you can use their hot backup tool for that as well, if this is a REAL (rather than IMAGINED) problem.
Argument: I have to wait for 4 million INSERTs to be performed...
Rebuttal: RTFM. No you don't.
Argument: [No] Ability to specify the number of files to dump to
Rebuttal: Again, is this a real or imagined problem? It's likely that whatever you are trying to do, there's a better way. Unfamiliarity with a particular tool usually results in this type of problem.
Argument: Cleaner way to view query plans of statements
Rebuttal: So because YOU don't like the output of EXPLAIN you're saying MySQL isn't ready for production? WTF are you talking about?! As for a more *in-depth* EXPLAIN, I agree there, and I found PostgreSQL's mechanism kind of cool. Of course, in four years of running MySQL in a production environment, I'm not sure I would have used it more than once or twice; MySQL's EXPLAIN has always been sufficient, if you actually know what you're doing.
Argument: Ability to delve deeply into performance of the system
Rebuttal: RTFM. You are obviously unfamiliar with the slow queries log where MySQL gives you EXACTLY the information you are looking for. As for the data cache and whatnot, I don't know if that is actually available or not.
Argument: MySQL's query optimizer is PISS POOR
Rebuttal: And your evidence? Oh wait, you didn't actually provide any. You just brought up a tangential issue...
Argument: If I see another changelog entry...
Rebuttal: If you are that concerned, you have some good options here. (a) Pay the developers to hold your hand and explain to you what has happened. (b) Use the source and do your own friggin' diff. This is Unix; stop acting so helpless.
Argument: [a laundry list of disinformation]
Rebuttal: Dude, have you not even looked at MySQL since 3.21 or something? Row locking is available in InnoDB, as are transactions. Stored procedures and triggers are planned for 5.x IIRC, but so many applications DON'T need them that the MySQL folks simply haven't cared to add them. Ditto for views (which are also slated for 5.x).
I'm working on a BIG, very BIG project and started with postgresql, becouse of : transactions, subselects, forgein keys, stored procs, ... and becouse it should be stable.
:-)) and MySQL FOREVER.
... \d is NOT a sql command. Mysql has show tables . Works from everywhere.
Well. I came back from posgres to MySQL becouse of SPEED. I've made a test and optimized it for both Postgres and Mysql. And Posgres needed 20 (!!!!) secs, while mysql needed only 2. (I tought that was strange too.)
MySQL has the big power of not have to make all tables transactional. And the transactional tables are way faster than in postgres and it also does the job.
For subselects we will use temp tables.
My choise is MySQL (we will use 4 alpha
ALso the sql user system in MySQL is faaaar better than the strange user system of postgresql. Try to get a list of tables in postgresql
In my opinion Postgres sux and by the time it will not suck anymore, mysql will have everything everyone wanted WHEN he wants it.
Good boy. Now what does this have to do with 4.0 being a stable upgrade? You know, if you were posting about how WinXP was so much easier than Linux you'd be modded a troll.
and is it stable enough to run on a high traffic site
I count most of what I wrote as part of being 'stable' on a high traffic site.
Requiring your DB to be offline while you dump is not acceptable for a high traffic site.
Fail-safe replication is required for high stability on a high-trafficked site.
Backups which are flat-file based and require indexes to be created are not acceptable for a high-traffic site.
Having a poorly performing system and you not being able to diagnose why because the MySQL server provides inadequate tools is not acceptable.
I suppose it comes down to how you define 'stable'. Stable as in 'not crashing' may be true (although it still can and does crash). Stable as in 'highly-available' is not true.
You misread (or I mis-wrote) the multi-index statement. Multiple indexes on a SINGLE TABLE (which is clear from my SQL) is NOT implemented in MySQL. Multiple indexes on a single query works just fine if you have more than one table, but there still is the limit of one index per table. Either that, or it is so buggy it does not work when it is supposed to.
I don't use MySQL in production but I work with many people who do, and I also do significant consulting to clients who run MySQL. Perhaps you should know what *you're* talking about before posting -- I run several highly available, high trafficked sites (30+ million hits a week). But I guess you don't have the guts to stand behind your comments with your real moniker.
Thanks,
--
Matt
well, then it's only one thing to do; run the tpc test on each database and see whichone will perform best.
That's not exactly a ringing endorsement.
I'd prefer my OS not to do that.
As another poster has pointed out, moving from MSSQL to MySQL is a bad idea.
In addition to PostgreSQL, consider:
SAPDB ( http://sapdb.org )
Firebird ( http://firebirdsql.org )
I use Firebird because I need a database server that can "take care of itself" in an environment where there's no DBA. That is, I need to be able to deploy the server on some remote client's server, set up some automatic administration tasks, and let it do its thing for months and months without any intervention.
If I were operating in a centralized environment with a DBA (or even a database-savvy developer), I'd probably use SAPDB, since its feature set is impressive (subtransactions, etc.).
Another issue for *you* to consider might be Windows compatibility. MySQL, Firebird, and SAPDB all work well on Windows, whereas PostgreSQL currently requires Cygwin, and generally tries to treat the Windows server as though it were UNIX. I would never use such a taped-together setup in production.
In summary, it appears to me as though your best choice is SAPDB.
I am convinced that the only reason people like Mysql is it has better marketing. The perfect example is those benchmarks they distribute with the software. Those are single user transactionless benchmarks!!! Single user benchmarks are not realistic. Anyway Postgres is far stabler faster product in my opinion.
I've done a bit of research and the reason I've decided on Firebird over PostgreSQL is because:
1. PGSQL has bad Java/JDBC support
2. Firebird is easier and less work to maintain
3. The replies on this slashdot article seem to suggest some problems with PGSQL and corruption of the database.
I'm a lazy bastard and that's a good thing.
All I'm wondering about now is the speed difference between MySQL and Firebird on a simple high traffic site. Although, judging from another slashdot reply. MySQL and JDBC together will automatically be slower than normal MySQL.
I guess I just made up my mind.
It's worlds number 1 database system, and you want to get rid of it?
*WHY* ?
It has everything you want, is rocksolid and lightingfast. Ok it's expensive, but you already have it.
Never underestimate the relief of true separation of Religion and State.
You're just plain wrong about supporting only 1 index per table.
MySQL supports 32 indexes per table, and queries can use multiple indexes on the same table.
HI peyote, I'm glad you took the time to intelligently reply to my post. I think we're not quite on the same page in a couple places, certainly MySQL may be 'stable' (see my other post about what I consider to be 'stable') but I think the story starter was concerned with high trafficked sites which is a horse of a different color from, say, my personal site or a lightly-used corporate intranet which certainly doesn't require the types of things I illustrated in my post. I run several high-trafficked sites (upwards of 100 million imp/mo.) and those were 'gaps' in MySQL which our team isolated as ranging from 'really helpful' to 'required'.
.
Rebuttal: Somehow that fact doesn't impede Yahoo!'s extensive use of it. (See Jeremy Zawodny's presentations at the recently-held OSCON.) If the query hangs on the slave halfway through, it isn't marked on the slave as having completed. When the slave becomes available again, it notes its pointer in the transaction log and catches up automatically. Oh, and replication (at least in 4.x, possibly also in 3.23.x) is transaction-safe.
Right, and the slave goes down. So I have to manually intervene to fix it. That is unacceptable. It should do something better than simply quit and say 'oops'. Yahoo's extensive use is, as I see from MySQL.com, fairly weak:
Size of database: 25 GB
Average number of concurrent connections: 60
Max number of concurrent connections: 250
That is not worth writing home about.
Rebuttal: As another poster wrote, leave the OS out of this. If you cannot properly configure your OS to not buffer writes, you probably shouldn't be running a 'mission critical' ANYTHING.
Correct, knowing what your filesystem does is your problem. The filesystem buffer will say 'Ok I wrote that' and MySQL can move on, even if it is living in a buffer before being written. Not a big deal if you're aware of it, but MySQL doesn't give me an easy way to change it, or to specify WHICH tables/databases NEED to be O_SYNC'ed or not. That is what I wanted - recompiling MySQL and having EVERYTHING or NOTHING buffered is not acceptable. There are certain tables or databases I KNOW will benefit from buffering, but with MySQL it's all or nothing.
Rebuttal: So set up a dedicated slave for backups. Turn off the slave while backups are running, it catches up when backups are done and it is brought back up. A *simple* solution to a *simple* problem. If you really feel the need to do a hot backup of your live server, you can check into using InnoDB's tool at http://www.innodb.com/hotbackup.html
Ok, so I have to now get another boxen set up just to backup my database? How is getting another box, setting up replication, pausing replication, dumping, etc. etc. 'simpler' than saying 'dump database bob' and having MySQL do the rest for me? And I did point out InnoDB has a hot backup but that does work only with InnoDB.
Rebuttal: You mean like backing up the raw MyISAM files? Of course, that doesn't work with InnoDB databases, so you can use their hot backup tool for that as well, if this is a REAL (rather than IMAGINED) problem.
That only works if you offline your DB while your copy is being done. Again, not acceptable and what hot backups for ALL MySQL tables would fix.
Argument: I have to wait for 4 million INSERTs to be performed...
Rebuttal: RTFM. No you don't.
Well, certainly you can always use 'CONCURRENT' but in practice that is not terribly useful. Why? Well my 4 million load operation will take LONGER to perform, and sure people can query it now, but I have no indexes on the table so all queries will now table scan. So I suppose you're right, you don't have to lock the table for the whole operation, but in reality, your app won't run very well at all (are writes allowed to the table while you're loading?).
Rebuttal: Again, is this a real or imagined problem? It's likely that whatever you are trying to do, there's a better way. Unfamiliarity with a particular tool usually results in this type of problem.
It can be - depending on your OS and whether or not you have it configured for large file support. But again striping it can have significant performance advantages since I could dump AND load to multiple disks that leads to a significantly higher throughput rate.
Rebuttal: So because YOU don't like the output of EXPLAIN you're saying MySQL isn't ready for production? WTF are you talking about?! As for a more *in-depth* EXPLAIN, I agree there, and I found PostgreSQL's mechanism kind of cool. Of course, in four years of running MySQL in a production environment, I'm not sure I would have used it more than once or twice; MySQL's EXPLAIN has always been sufficient, if you actually know what you're doing.
Not necessarily 'production' use, but production use in a highly trafficked environment.
RTFM. You are obviously unfamiliar with the slow queries log where MySQL gives you EXACTLY the information you are looking for. As for the data cache and whatnot, I don't know if that is actually available or not.
Yes I've read the manual and seen the slow-query-log portion. However the log does not log the currently running SQL, only after it has completed. If my server is slow NOW I have to wait till whatever is blocking everything to complete BEFORE I knew what happened. I'd like a way to see RIGHT NOW what is going on, so if I see a rouge query performing a Cartesian product I can easily kill it and life can move on. The slow query log does not fit the bill.
No, there isn't a way to manage the data cache aside from setting config options on sizes of key caches and the like. I can't create dedicated caches to certain tables which is quite useful, nor can I change the I/O sizes of the caches if I know queries being performed on them would like 16K I/O vs 4K or whatnot.
Rebuttal: And your evidence? Oh wait, you didn't actually provide any. You just brought up a tangential issue...
Part of it comes into the fact that I can't manage index statistics (or even see them), can't tell what types of joins are being performed, etc. and the fact that the optimizer is somewhat 'young'. Now, simply because I can't see statistics doesn't mean the optimizer isn't using them, but I can't gauge how well they are being used, and I suspect the optimizer isn't as advanced as others. Optimizers are weird creatures, and with time it will certainly improve. However many-table joins are consistently slower in MySQL than many others - I'll see if I can't get test data up and run some quasi-useful benchmarks but I've seen this time and time again in properly normalized, indexed tables queries which run on Sybase ASE, Oracle, etc. with nontrivial amounts of data do not run as well on MySQL (which touts SELECT speed as their main advantage, I guess complicated SELECTs are not as nice).
Rebuttal: If you are that concerned, you have some good options here. (a) Pay the developers to hold your hand and explain to you what has happened. (b) Use the source and do your own friggin' diff. This is Unix; stop acting so helpless.
We don't use MySQL in a production environment, so I'm certainly not going to pay or waste my time coding. But MySQL.com seemingly touts that they are #1 in so many aspects and conveniently glosses over some issues which are important to those who write and manage large applications. I'm simply pointing out where they need work, and if they're serious about their claims then they should have no problem whatsoever implementing them.
Dude, have you not even looked at MySQL since 3.21 or something? Row locking is available in InnoDB, as are transactions. Stored procedures and triggers are planned for 5.x IIRC, but so many applications DON'T need them that the MySQL folks simply haven't cared to add them. Ditto for views (which are also slated for 5.x).
As I said in another post, I do lots of consulting and yes, I read the MySQL docs quite often. InnoDB does provide row locking, but at a cost of performance and memory. If you know much about how large-scale applications are built (which is what I think the comment author asked about) you know how well stored procedures and triggers are used. He asked if MySQL 4 (over something like PostGRES) was suitable in a high-trafficked environment. Those features are a serious part of any non-trivial application.
Thanks,
--
Matt
I would gladly try Postgres as it has some features that are currently missing from MySQL. But I don't feel like dealing with it.....
I've installed PostgreSQL five or six times. IIRC it involves seting a pair of environment variables, creating a user, and using configure and make. I was able to do it right after completing my first Linux instalation. I didn't find it difficult or even mildly challenging. The documentation is really good.
t'nera semordnilap
Really? Do you have an example?
You are not seeing my point -- obviously you either didn't read what I said or have no clue what I was talking about.
I never said you cannot create more than one index on a table. But for a single select, on a single table, you can only use a single index. I'm not making this up, read the docs:
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimiser tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.
http://www.mysql.com/doc/M/y/MySQL_indexes.html
Thanks,
--
Matt
Hey,
:)
:)
:)
:)
:)
:)
:) when running under Windows using a JDBC driver.
:) using us for critical, high-load applications.
:)
Thanks for the critical feedback - however, you have some information that is not accurate.
On the positive side, your criticism has provided some interesting ideas!
REPLICATION
You are correct that the binary log stores the SQL data modification statements that were applied to the master, rather than the actual
changes that were made to it. You are also correct that if something goes wrong, the DBA is the one who gets to fix it.
It does not assume that the master and slave are equal - it does assume that the slave contains an linear subset of the data on the
master.
We have users working with replication in high-demand situations and it is performing well. Could we improve it? Of course - I was just
browsing through the slides from the PostgreSQL Replication talk at OSCON and it looks like some very cool things are going on - we should
watch and learn.
TABLE LOCK LATENCY
It is true that table-level locking used by MyISAM performs poorly under heavy concurrent read/write.
If this is an issue switch to the InnoDB storage engine (which uses low-cost, non-escalating row level locking) or the Berkley DB storage
engine (which uses page-level locking).
InnoDB uses a single bit to indicate if a particular page stores rows that are locked. If any rows are locked in the page, then a few more bits are needed for the page to indicate what particular rows are locked.
FILE SYSTEM BUFFERING
File system buffering can be a thorny issue. The InnoDB storage engine forces a flush to disk upon the commit of every transaction and
then writes a checkpoint so that it knows when the last flush was made.
INDEXING
This is just plain wrong. Of course MySQL can use more than one index in a query!
CLUSTERED INDEXES
MyISAM tables can be optimized so that the order of the rows in the table matches the order of the indexes on the table.
InnoDB tables already use clustered primary key indexes. Secondary indexes refer to the primary key values.
ON-LINE BACKUPS
Replicate the database out to another server (even one on the same machine), then stop the slave to take a backup.
This has the added benefit of being able to ask the slave to take over from the master when you need to maintain the master.
BACKUP FORMAT
Use the binary log instead of the mysqldump tool - it uses a compressed binary format that is much more compact.
Also, if you have to recreate a table from a mysqldump, then disable indexes until you have recreated all the rows - will save a good deal
of time on bigger tables.
Dumping the indexes is probably not a bad idea though.
DUMPING TO MULTIPLE FILES
An option to allow dumping to multiple files would be convenient.
In the past, I just asked mysqldump to dump to stdout and have a perl script handle the segmenting.
The binary log does get segmented into multiple files automatically. I will ask the developers if it would make sense to make a new dump tool that works with the binary log format so as to get the benefits of that format.
BETTER TOOLS...
Duly noted - the output from EXPLAIN is cryptic.
DELVING DEEPLY...
Absolutely - we could use more tools to give detailed performance information. The current tools that report the number of active threads,
the number of questions run, the memory currently used, the max. memory consumed, etc. are not enough. We have some graphical tools like MySQL-Graph (A GPL'd app) to make review of the data easier.
Also, slow queries is more than a counter. The slow query log stores details on every query that ran over the value of the long_query_time
setting. Use the mysqldumpslow tool to give a summary of data in the log file. The log can also record queries that did not use an index.
QUERY OPTIMIZATION
On what knowledge do you base your assertion that the query optimizer is 'PISS POOR'? Do you understand the code behind it? Have you run
benchmarks to compare its performance to another optimizer?
Of course we keep optimizing for specific cases. We want to continue to improve performance whereever possible. We have spent a good deal
of effort doing broad optimizations - the optimizations that have the greatest benefit for the most queries. Now we are work more on
tweaking specific cases.
MISSING FEATURES
The Berkley DB storage engine uses page level locking.
The InnoDB storage engine uses row level locking (without resorting to lock escalation
We have excellent transaction support - likely the best of any available database today. InnoDB supports the repeatable read transaction
isolation level. However, due to how we implemented our multi-versioning support, we don't get phantom reads. This is a higher level of transactional isolation than MS SQL, Sybase, PostgreSQL, Interbase, Ingres, etc. IIRC, only FireBird and Oracle may be the same.
Sub-selects should be out very soon. We are still working on stored procedures, views, triggers and full support for referential
integrity. We know that these features are important. However, we are working on doing truly robust implementations - rushing them out
will not help anyone.
END NOTES
Why does no one mention the stuff that MySQL is good at?
We are fast - we have third party confirmation that we are faster than DB2, MS SQL and Sybase. The test even confirmed that we perform about as well as Oracle (a bit slower
We don't need to stop the database to vacuum or do many maintainance tasks.
We know that we can run in critical environments because we have users like Yahoo! Finance *and* Slashdot
We are a fully-threaded app and can take full advantage of SMP machines.
We can run natively under a bunch of OSs - including Windows.
Our ability to use different storage engines gives users great choice in how to manage their data. If someone needs a lightweight format in a non-transactional environment, use MyISAM - it has very little storage overhead and is speedy in situtations where you do not have many concurrent reads/writes.
If you need really robust transaction support that ensures the integrity of your data, use the InnoDB storage engine. Storage overhead is more than with MyISAM tables, but that is not generally an issue for enterprise level users.
I can hear people grumbling - well, the transaction support isn't integrated, so it isn't valid... That is complete junk - since when is choice a bug? That is like saying that the Linux is not a modern operating system because its default filesystem does not use journaling. Usually, is it only the proprietary and/or less advanced operating systems do not give you a choice of file system.
Also, we can easily add in new storage to support specific needs. Look at how quickly InnoDB was integrated - it suddenly took us from having no transaction support to having great transactional support. Without the storage engine concept, we would have had to do a lot more work to get it integrated.
blah blah blah...
Zak,
:)
:D With my DBA hat on, I have to deal with a lot of issues, and 'worrying' about replication (it's supposed to put my mind at ease, right? :D) failing shouldn't happen.
:D
Thanks for replying to my post. I'm only trying to help make MySQL better - I don't use it but I know many who do, and they have to struggle with issues which I think they shouldn't have to.
Re: replication
Correct, my gripe was that the DBA now has to do something to fix a problem which should've never occurred.
Re: File system buffering
Again, I am aware that is something you should 'know' when you're setting up your DB, but it would be nice to be able to control it on a table or at least a database level. Again I think system tables are really important and should always be non-buffered and so should several of my production DBs, but there could be a test DB that I don't care about and buffering will provide a performance advantage.
Does turning off buffering also kill the read cache, too?
Re: indexing
I think everyone is mis-reading, the docs here explain what I'm getting at:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimiser tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.
What I would like it to do would be to read BOTH indexes and then create some sort of a 'dynamic' index (really just the AND-ing of RowIDs after considering both indexes) to limit the results further.
Re: Optimizer
I will attempt to assemble some test data, but in the field I've seen many-table joins (inner, outer, etc.) being performed noticeably slower on MySQL than in other RDBMs. I haven't checked postgresql, though.
I actually browsed through the C code for the optimizer but couldn't exactly find what I was looking for - from what I saw it sets up a query class which does the optimization inside of it? I thought the optimizer would be a separate 'entity'. Maybe I was looking in the wrong place.
I read the InfoWorld benchmark thing, and also downloaded the code they used. MySQL's performance gains came from the query cache which is quite cool, although on systems in which you're updating/inserting you're going to hose the cache and lose the benefits (this was tested informally on a couple million row table, and performance with the cache when being updated frequently was roughly equal to without).
Again - I'm not saying there's no use for MySQL - but the original thread starter was looking at a high trafficked system. It's kind of like trying to figure out the Total Cost of Ownership of a piece of software. When you have a high trafficked site not only is performance important but reliability, maintainability, disaster preparation and recovery, etc. all need to be factored in, which I attempted to illustrate in the 'gaps'.
Thanks,
--
Matt
Zak,
Putting aside what MySQL is and isn't good at, I'd like to ask that when you guys implememt features, you do them according to SQL specs. As the author of DataDino, a database management tool, I've been bitten several times by MySQLs lack of SQL 92 adherance.
For example, the SQL specs state that identfier tokens (e.g. table names, column names, etc.) can be encaspulated in double quotes. The only problem is that MySQL uses the accent sign (`) instead of quotes. All the code that was ok for nearly every other database on the planet, is suddenly not ok for MySQL. Very bad.
Simple things like this can go a long way toward people taking MySQL more seriously. As it stands, you may manage to get all the standard RDBMS features in, but it won't be compatible with anyone. Thus MySQL will not be accepted until this changes.
Javascript + Nintendo DSi = DSiCade
Comment removed based on user account deletion
> What I would like it to do would be to read
> BOTH indexes and then create some sort of
> a 'dynamic' index (really just the AND-ing
> of RowIDs after considering both indexes)
> to limit the results further
This is exactly what MySQL does--it will use both indexes (or more than two) to narrow down the rows to fetch. I think it's the MySQL docs that may be unclear in this case.
If we're talking about high trafficked sites, I'm pretty sure our sites in my last job would qualify: around 80M-90M page views per month. Granted it isn't ``upwards of 100 million imp/mo.'' but I don't expect to get into a d*cksize war about that. :-) (Also, the great majority of these were PHP pages doing various things against our MySQL database.)
;-)
...pausing replication...
/path/to/mysqlhotcopy`.
:-)
Right, and the slave goes down. So I have to manually intervene to fix it. That is unacceptable.
I'm not really sure what you mean here. The only replication problem that would require manual intervention is in a multi-master setting where MySQL gets confused about which query comes first. (And they don't even officially support such a config.) In a typical failover setting, if a slave dies, goes down, whatever, it rebuilds itself when it comes back up, no intervention required.
As for just quitting and saying 'oops', I think that was a problem with MySQL's replication a number of versions ago, probably one of the first versions it was officially in the source (complete with dire warnings, of course).
Finally, as for Yahoo!'s total MySQL traffic, we actually outran that by quite a bit at my last job. We were seeing sustained averages (over a number of months) of nearly 180 queries per second, with a maximum of 1000 concurrent. Is that worth writing home about?
Correct, knowing what your filesystem does is your problem...
Maybe I'm misunderstanding, but this REALLY doesn't seem like it's MySQL's job. If you want a database to be O_SYNC'd, `chattr +S the_directory`. (Granted you cannot do that on a per-table level.) Want the whole thing sync? `mount -o sync the_filesystem/`.
Ok, so I have to now get another boxen set up just to backup my database?
Yep. Or you can get one box that acts as a slave for all five/ten/whatever of your MySQL pools and back them ALL up from one box, rather than five/ten/whatever.
No no no no no. You don't have to pause replication. Really. I promise.
etc. etc.
No, there is no etc. etc. You described it just fine in a few simple steps. At least in our application, this eliminates any need whatsoever for hot backups. YMMV, use the right tool if you determine that to be something else. But to say that is doesn't work is simply false. I can demonstrate that it does work, and it works well, even in a production environment with a healthy bit of traffic.
P.S. If you are still really bent on this hot backup business, check out `perldoc
Question: why are you doing lookups on a table that is in the process of being loaded? Do you do lookups on a table while you are restoring it from backup? Aren't you running the risk of getting bad data out in any case? Yes, you do have to wait for the operation. No, that does not mean that you have to do 4 million serialized INSERTs.
Yes I've read the manual and seen the slow-query-log portion. However the log does not log the currently running SQL...
Okay, if that's what you want, use SHOW FULL PROCESSLIST from the MySQL monitor. Done.
As for some of the data cache issues, I still don't purport to know much about that, but ISTR a lot of talk from Monty and the other MySQL team at OSCON about this sort of thing in 4.x and 5.x. We'll see over the coming months...
As for the optimizer, you're right, I can't see it either. Neither have I ever needed to, since it just doesn't come up. At least in our application, MySQL seems to get it right every time. (No, this isn't a proof. Yes, it's anecdotally helpful to the OP, I think.)
We don't use MySQL in a production environment, so I'm certainly not going to pay or waste my time coding. (regarding vague Changelog entries)
Okay, maybe the Changelog could be more verbose. (Personally, I'm happy with the amount of information in the Changelog.) But those who write and manage large applications, if they are truly concerned about this sort of thing, should have the expertise (either in-house or via a MySQL support contract) to squeeze every last ounce of performance out of the database. In the same way, they should be able to track changes in a more specific way. Maybe not you, since you don't run MySQL in production, but those who *do*, *can*.
InnoDB does provide row locking, but at a cost of performance and memory.
I'd be interested to see the application that provides row locking for free.
If you *NEED* those features (note that need does not just depend on the size of an application), use them, either in another package, or when MySQL gets them. I've seen plenty of non-trivial applications that run just fine on MySQL in production and high-trafficked environments. Size and volume do not constitute a feature need.
Currently, you can ask MySQL to become ANSI compatible by starting the server with the --ansi flag. When this is enabled, MySQL uses double quotes as the identifier delimiter.
The drawback with this is that it still has to be enabled on a per-server basis and many people do not run with it enabled (nor do they want to have it enabled).
This does make life difficult for tool developers and porters.
I will raise the issue with our developers and will reply to you as soon as I hear something.
Only 30 million hits a week? I was running a site, on MySQL that was pulling 15million a day. Now, if you mean 30mil _pageviews_ that's different. We did about 400k of those per day. Granted, we did have a slightly weird setup, but it did the job, and very well. Each front-end webserver had a MySQL slave instance with the necessary read tables. This helped keep the concurrency way down. The master was only written to upon user information adjustments, or hourly when banner data was replicated back to it from the slaves (no sense pushing all that data to the master on each hit when the front-side can be used as a temporary aggregation point).
Now as to the backup, mysqlhotcopy at about 3am. Drops a write lock on the tables. Maybe a couple minutes of write lock on a really gigantic table. As we didn't have a higly active forum, or other DB update intensive section of the site that required immediate public update, it didn't cause any real problems. In a year of this operation with 15 slaves, I think I had to hold the replication's have on one of them once, and that was my fault anyway. It helps to create the db before you start replicating to it.
Yes, I mis-spoke. 30 million pg view. Which many of them are writes, unfortunately. :D If they were reads we wouldn't have hit MySQL locking problems, and live would've been good.
Thanks,
--
Matt
Matt,
:)
I am glad that you are trying to help! The post seemed inflammatory due to certain phrases like, 'Why MySQL is Not Suitable for Enterprise or High-Volume Use', 'MySQL.com misleads you about it's capabilities' and 'PISS POOR'
I think that people misunderstood some of what you said because you painted broad generalizations over specific issues. Stating 'Inability to use more than one index on a table in a query' is very different than how you later phrased your concern.
I would certainly like to see some tests on the optimizer - I have been having some discussions regarding benchmarks with the PostgreSQL and Firebird developers and I am sure that we would all find the benchmarks interesting.
As for browsing the optimizer code, you are brave individual.
Part of our performance gains did come from the query cache. Windows does a poor job of caching disk I/O and the query cache helps make up for this. I believe that Oracle (and others) use a similar strategy as one of their optimizations. It is interesting with the benchmark to note how similar the performance curves were until each database hit some sort of internal limit. I expect that if Oracle and MySQL had been allowed to continue beyond the limits shown then we would have seen a similar type of drop-off for both at some later point.
Regarding high-use: We have users who are really putting MySQL through its paces under high load - it works quite well and has a low TCO. Low enough to displace Oracle, SQL Server and other databases from existing setups.
All I want out of MySQL at this point is subselects. This seems to be one of the most heavily requested features, yet there's no sign of it...
... with the promise of Subselects in Version 4. Well... version 4 is here, and no subselects... and my project can't move forward without them. Or at least, if the queries can be done another way, I haven't found it yet. The JOIN syntax in SQL is so horribly convoluted that I just simply have not been able to adequately wrap my brain around it, I suppose...
:)
Several of my projects (admittedly, they aren't as high profile or even as important as many things) are on indefnite hold because everything was built around MySQL
Subselects in and the world is all roses and honey... but at this point, I'm pretty pissed off at MySQL and am wishing I had chosen Postgres from the get go.
But hey... ya get what you pay for, so I guess I can't be too mad, eh? Which is probably why I don't rant over at mysql.com ever
Well, I feel that it is NOT suitable for enterprise use, and I thought I explained why. Perhaps I'm spoiled with Oracle, Sybase, etc. in that I don't have to code workarounds (which is what they are) to have hot backups, or spread over multiple dumps, etc.. At least to me that is what the Enterprise world knows, and loves, and expects. If you want MySQL to have a chance, you can't tell a company that they're going to have to write utilities (or custom code, or set up a dedicated replication backup server, etc.) to perform routine things that their RDBMS has been doing for them. They don't have to write custom application code to handle subselects, joined updates, hot backups, etc. and telling them they will have to simply 'deal with it' is not a good way to win the hearts and minds of developers, DBAs, etc. :D
:D
:D
:D).
;)) and having to 'put up' with a lot of deficiencies which certainly are 'slated' to be addressed but the only roadmap we have is 'next version' with no real timetable (and many of us recall many years ago the docs stating 'real soon' for things which are still not implemented). At our site we started on MySQL and encountered major problems and eventually purchased Sybase ASE for very cheap (under $15,000) which includes 24/7 support. And we have hot backups, subselects, triggers, stored procs, etc. and all sorts of other things which we didn't have to spend time developing in house.
:D
Not to really focus heavily on this but I believe MySQL.com does mislead:
designed for speed, power and precision in mission critical, heavy load use
Power, to me, implies much more SQL options (e.g. subselects, joined updates, etc.) which yes, IS BEING worked on, but is not there yet (nor stable from what the docs tell me). Power, to me as a DBA, means that I can tweak server settings without dropping and restarting the DB server. It means that I can see more than what I have now.
Stored procedures are typically faster than ad hoc SQL, so it would be faster to have precompiled SQL capabilities.
Mission critical implies that I can have fail-safe replication and failover when something goes wrong. It says that I don't need to have 'workarounds' to hot dump my database (say I can't afford to have a dedicated backup replication guy somewhere).
Is MySQL getting there? Of course - but as I said 'Not yet'.
The misunderstanding re: indexes came from SlashDot parsing out my less than in the query, so it cut it off. This is what I wrote (sometimes slashdot times out and I lose the post so I write them in textpad first):
Given an example query - 'SELECT bob FROM sometable WHERE somecol = 45 and somecol2 less than 44' - if I have two indexes on sometable (somecol and somecol2) it can join the two indexes together and use the 'virtual' index to find rows.
I don't think the generalizations were overly broad, certainly you can achive hot backups via a roundabout way, or that you can 'sort of' work with data as you're loading it via load but in PRACTICE I think it was accurate. We could split hairs over how to achive hot backups but in the end, the enterprise customers just want non-corrupt backups (is there a way to achive something like a rolling transaction dump to achive up-to-the-minue backups without backing up the entire database? I know we can't live with day old dumps if we have data loss, again replication can help but if I issue 'delete table;' then that will be replicated, too
As I said, it's sort of a trade-off between the costs of, say Oracle (which is typically obscenely high
I suppose it comes down to can you afford to not have the enterprise features? The problem is most people don't know until they encounter a situation in which they need the feature.
Thanks,
--
Matt
MySQL 4 - Is it Stable?
Better question is: "Is it ANSI SQL-92 compliant?"
No. It is not. MySQL does not conform to even a 10 year old SQL standard.
I'm a 2000 man.
Check em out :)
Ach! You're lucky to have artificial arm replacements...I had to use a wire coathanger after M$ Access blew off my arm!
It's great to use to roast marshmallows in the fire, but hell in the W.C.
4.0.2 has been rock-solid for us on Linux. We we're running 4.0.0 with 80 days of uptime before we upgraded to 4.0.2.
mysqladmin Ver 8.13 Distrib 3.23.31, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.2-alpha-Max
Protocol version 10
Connection db1 via TCP/IP
TCP port 3306
Uptime: 17 days 5 hours 2 min 37 sec
Threads: 127 Questions: 79924235 Slow queries: 66360 Opens: 52010 Flush tabl
es: 1340 Open tables: 222 Queries per second avg: 53.750
as a programmer, i like working with postgresql.
one, the postgresql protocol is extremely well documented, making it easy to implement a database handle, e.g., for a gsk style main loop or even in a language like haskell. the mysql protocol is completely undocumented.
second, the mysql client libraries are gpl licensed. postgresql is bsd.
these two points are related because 1) i can't really effective reimplement a mysql client library without good protocol documentation and 2) i can't distribute the mysql client library without releasing all my code, and sometimes from a commercial standpoint that's not an option. i could suck it up and get a mysql commercial license, but at that point, i'll also consider other commercial rdbms's.
i would also like to note that in the case the bsd license is actually indirectly incentivizing better documentation.
-- p
Postgres rocks!
Postgres rocks!
Postgres rocks!
(MySQL sux0rz!)
Postgres rocks!
That should keep the flaming morons away, so:
does the mm.mysql JDBC driver support the InnoDB features like foreign keys and row locking -- or is that a non-issue and something the driver doesn't have to deal with?
Many people are happy with MySQL despite it lacks some features. That's the point. :)
You can find many MS Access systems in corporate environment too.
It depends what you are working on.
For PostgreSQL and firebird, I think they really need some graphical administration tools. DBA may work on many diffenent databases in real life. Life is really hard if you depend 100% on command line.
I continually hear mysql folks repeat the dogma that it is faster than the commercial alternatives - but I've *never* seen a benchmark that backed this up - and wasn't trivial. Can anyone point to something beyond the home hobbiest level?
Since these days OLAP & warehousing are *core* functionality, any database that cannot provide at least nominal capabilities in this area isn't a serious contender beyond the web boutique/minor league applications.
Although some analytical functionality is needed today within most OLTP applications (summary tables, search tables, simplistic report tables, etc), a pure OLAP/Warehousing benchmark would be the easiet to compare to commercial alternatives.
So, how about something along the lines of TPCDs - representing, say, a 300 gbyte reporting application with dimensional tables.
Oracle, DB2, Informix, and Sybase easily delver solutions for this with support for partitioning, unions, parallelism, bitmap indexes, etc. SQL Server can as well, just to a lesser extent.
But, have any benchmarks - or success stories established that mysql is even a player in this space? Or is this just another popular delusion?
I'll use MySQL 4 when Slashdot upgrades to it. :-)
Daniel
The problem with putting 100% of the business logic in the middle tier dogma is that most folks saying this haven't sufficiently tested it.
.NET.
Aside from the valid and well-known disadvantages to implementation within the database, the following advantages are unfortunately not well known to having a logical application layer constructed from stored procedures & views:
#1: database technology typically outlasts application technology. So - business logic implemented in T/SQL or PL/SQL in 1994 still runs perfectly fine today. If it had been implemented in pro/c it would have them probably been re-implemented in VB, then C/CGI, then perhaps Perl, then Java, and now
#2: physical tuning. Databases physical schemas often have to be changed to deal with changing performance needs. If the interface from the application layer to the database is through a stored procedure the entire change can be implemented by a DBA. On the other hand, if the application is using selects/deletes/updates on the database a physical schema change will generally require a combined effort of programmers and DBAs - and suddenly become a significant project.
#3 Adaptability. Similar to the above issue, logic within the database allows the DBA to re-engineer the physical schema to one with greater flexibility without impacting the interface to existing applications. The alternative typically involves duct-tape-and-bailing wire to ensure minimal impact to existing application code.
#4 Capability. Some database modeling patterns involve the use of tree and network patterns that allow tons of flexibility but poor performance for scanning queries. The poor performance can be addressed with triggers that also insert to read-only tables. This capability provides extrordinarily flexible, adaptable, and reusable schemas, with no performance disadvantages. But - it is practically impossible if you are doing selects/deletes/inserts from the application layer.
So, yeah 'common knowledge' says that you need to use just a subset of database functionality, and move everything into a middle application layer. Then again 'common knowledge' in 1994 said that client-server was the future of database architectures. And quite a few projects suffered because of the parrots that endlessly repeated the dogma/mantra that client-server was best. Business logic in the middle-tier is the exact same thing.
After 20 years working with virtually every DBMS in the market I'd simply discarded MySQL in favor of Postgres.
MySQL is to Open Software what Access 1.0 was to closed Software. Sorry to say that.
Lame, lame "database" that ignores everything except CREATE, UPDATE, INSERT or DELETE. [where]...
No joins, no views, no foreign keys, no subqueries, no stored procedures, no transactions, no row locking, no dirty reads, no REAL gain in speed. I don't know what some MySQL supporters call an "application", but I'd really like to see MySQL trying to run a real production environment with a few hundred (oh, preferably around a thousand) concurrent users in a highly transactional environment, using a hundred-table database. Yeah, right. Go ahead. I know people that also tried with dbase III / Clipper on DOS. Some of them even survived.
You claim to know databases and yet you force your view that I am supposed to know the underlying data representation. The basic premise of a database is to shield you from the underlying complexity of handling data. If I am supposed to know the underlying data representation then what good is the database ? Sw. design is more about how components fit and db is just one component. Sure it would help me to enable data savings if I know I should put all the not null columns in front rather than mix them in the table definition. I'd rather pick someone who knows about cardinalities and how to setup proper indexes then someone who knows the innate underlying data representation.
Never ceases to amuse me: MySQL users clamouring for the basic features that have been in Interbase for a decade and now available to all opensource users.
Sure, MySQL is the popular choice (LAMP), but following that logic we never would have seen the growth of Linux against MS! C'mon, people, show some initiative! With PHP and Apache's inbuilt support for Interbase/Firebird, what are you waiting for? You want views? You want foreign keys? You want a true CS multigenerational RDBMS that has been around since 1984?
www.ibphoenix.com
-AF
ASsMastering.
Oh and by the way: VB sucks. If you'd tried Java, Delphi, hell, even C#, you'd realize.
We currently use MySQL 4 for www.workzoo.com and have been for the last 6 months since the first alpha release. We use it for the extended fulltext index support. We do up to 400 inserts and updates per search as we are a realtime meta-search engine and use MySQL to build a cache. We have had one or two minor problems which were solved by the Monty and his team (for free!) sometimes at 10pm on a friday night, simply by sending in a bug report to the list. We dont have a support contract.
We haven't had to reboot mysql for more than 3 months now - and it's version 4 Alpha!
BTW, MySQL currently supports transactions and foreign keys via InnoDB Tables - although we use MyISAM tables as InnoDB does not support the (ultra fast) full-text indexing of MyISAM.
~pHaze
Any help appreciated, treefrog