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...
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
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 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
InnoDB tables offer nice capabilites but are horrible to maintain compared to MyISAM tables.
For example, with InnoDB you have to statically allocate the table space and there's no tools to profile for how much space you'll need, meaning you have to keep on checking the available space constantly.
"Although it is not true that all conservatives are stupid, it is true that most stupid people are conservative."
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...
Mysql needs to be careful with the features it adds. It holds a niche market on web sites that need a fast primarily read only db right now. When they start adding features like stored procs, views, etc... they WILL take a performance hit. Then people will be asking themselves why not just use postgres or informix or any of the pay to play dbs since these dbs already implement all these wanted features and have been around and tested for awhile.
I really don't understand why so many people get caught in this stupid discussion about what is better, MySQL or PostgreSQL.
It seems as though people live in a 2 dimensional world, a black and white world, where you only have one answer to a question.
MySQL has its advantages and disadvantages, just like any other RDBMS on the planet. It doesn't mean its CRAP or SUPER. It ALWAYS depends on what you are trying to do with it, the time you have on your hands to develop software that will use it, the number of records you will be holding, if you need transactions or not, if you need replication or not, if you need just SELECT speed on simple tables, or if you are developping a transaction-based e-commerce app.
There is a reason why Oracle is so popular
There is a reason why MySQL is so popular
There is a reason why PostgreSQL is so popular
My motto is: "Don't be a bigoted nerd! Always use the best tool for your specific project/job"
In some cases MySQL will be the best, in others it will be the worst.
In most cases people talk too much and read too little about what they are talking about.
(p.s. this is NOT a flaimbait... just a call for an end to all the nonsense!)
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
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.
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.
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
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.
"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
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.
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.
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
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.
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.
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.
Maybe I'm ignorant, but I can't see how to use a JOIN to do what I use UNION for. Maybe you can enlighten me. Here's an example:
SELECT DISTINCT 'foo' AS src, id,JOIN combines data from multiple tables horizontally (into the same row/tuple) while UNION combines data from multiple tables horizontally (into separate rows/tuples). I need and use both, although JOIN far more frequently than UNION. In fact, I use UNION much more often when merging external data for import than I do within my main databases/applications.
(If it makes any difference, I work primarily in Ingres and PostgreSQL)
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.
Good job refuting my point:
Statement A: MySQL should not be used for large apps because of its lack of serious features.
Statement B: MySQL should only be used for apps which require speedy query execution with lots of data.
Statement C: The speedy execution benefit seen in MySQL is only really seen if you have lots of data or lots of queries. If you have lots of data or lots of queries, you're not implementing a small app.
B + C = MySQL should only be used for large apps.
which is in conflict with A, so we conclude that it's a contradiction and you should never use MySQL.
Now, instead of calling me a troll, explain why I'm wrong and why MySQL fanboys on Slashdot have any argument whatsoever for people to use software which causes bugs and bloated software and fails to prevent data corruption.
--
So you should be able to build a installation package for PostgreSQL that is on par with say ActivePython or ActivePerl for Windows, and sell it (you don't have to release your code modifications) for a reasonable amount.
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...
Well one thing you forget in all your arguments in money. Mysql is more or less free. Oracle is very expensive(well, if you are the person wo has to pay for it).
And beside the "best tool" is not always what the pointy haired boss has to say about it.
The asker "is MYSQL 4.@ stable" askes for this. since there are alternatives that already have the features he askes for. He will be lucky if there are 1 or 2 comments (deeply burries under postgres and sapdb is better) about people that actualy use MYSQL 4.
By the way why not program arround UNION? surey there is something in your programming language of choice to merge a couple of tables. if you need it only once or twice it might be better than a DB upgrade.
--- IANA mysql expert
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
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.
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.
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, 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 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.
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.
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
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...
Any time...
FWIW, I rarely have need for UNION, but when I do it's a lot better than multiple INSERTs into a temporary table followed by a SELECT and a DROP.
It's always fun to dive into the relational algebra behind it to get a better idea what's going on. If you're interested, this page has a good summary of relational operations, although it doesn't give direct mappings between the examples and standard SQL. Interestingly enough, 6 of the 10 examples would be done with a JOIN in SQL, and only one would need a UNION...
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
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.
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
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.
I'll use MySQL 4 when Slashdot upgrades to it. :-)
Daniel
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.
As aminorex said, large dataset != large app. I was working on a MySQL to go through gigabytes of server logs. It was a very small app, just lots of data for it to go through very fast.
As for corruption, etc., I haven't heard our slashdot admins complaining about it -- even kept up through the 9/11 traffic.