Hi Chris, > > > My statement was meant to say that the MyISAM table handler will just > > > disregard BEGIN and COMMIT statements, just as it parses but ignores > > > CHECK and FOREIGN KEY constraints in table creation, thus illustrating > > > that you wouldn't need to add a mysql-with-transactions directory to the > > > source tree. > > > > But don't we have to differentiate between a database with and a > > database without transactions? > > If we have transactions we can simply do all the database stuff, if > > anything fails we do the rollback and bail out. We still have a > > perfectly consistent database. > > Without transactions we have to remember every successful database > > modification we made and have to revert that. > > That would be very difficult in the case of UPDATEs though. > Additionally, with MyISAM tables there is a problem with race hazards. > For example: > > SELECT * FROM table WHERE id = 3; > UPDATE table SET attr = 'thing' WHERE id = 3; > // Error occurs > UPDATE table SET attr = $oldvalue WHERE id = 3; > > In the above statement, you have no way of ensuring that no one else has > buggered around with the row we're interested in. As DBMail's queries > seem to be primarily INSERTs and SELECTs though, this isn't as big a > problem as using LOCK TABLE statements would not result in a noticable > performance hit.
Yes adding a new message is the only critical part I think. If we have the message in the database there are only three possible updates: change read status, move to another mailbox, delete mail. Every single update can fail, no problem. But the insert consists of 35 SELECT, 4 INSERT and 5 UPDATE. So there is only one solution: transactions :-) > > > I was referring to someone who said in the body of their message posted > > > to another branch of this thread "the supposed database mysql". > > > Considering MySQL has proven itself time and time again in terms of > > > reliability, licencing flexibility and performance, such statements are > > > baseless regardless of what arguments you make for which features. > > > > That was me, I'm sorry! I didn't want to start a MySQL bashing thread. I > > had lot of problems with MySQL in the past, that's why I said that. In my > > eyes a database should support some basic things to have the right to > > carry the name SQL database. Among these are transactions, foreign key > > constraints, stored procedures, trigger, subselects. MySQL had not even > > one of these features (today they probably have most of these features) > > leading to poorly designed software, where the application had to do lot > > of work the database should do (a good example in dbmail are the quotas > > - the database could and should calculate that on its own). > > The case insensitiveness of MySQL is another point where I had lot of > > problems in the past. I only know two case insensitive databases: MySQL > > and MS Access. Every other database is case sensitive (Postgres, Oracle, > > MS SQL, what-ever). > > And one should never forget the dual license of MySQL. > > On the other hand people tell me that MySQL is faster than Postgres - > > that's simply wrong. It's only faster if you use the database type with > > less features. There are tons of threads on the postgresql list where > > these two databases (and others) are compared. > > That's why my heart beats faster when I hear the name MySQL - I'm sorry, > > I'll try to suppress these sidekicks. > > The reason I react to statements against my favourite database, MySQL, > is simply due to the things I have done with it and things I have seen > others do with it. There are lot servers in the world with critical data for companies on cheap x86 hardware with Windows <what ever>. So I can conclude that this combination is good for that usage? I don't think so. But this is really off topic at the moment :) > Today, MySQL does have transactions. foreign key constraints and > subselect and stored procedure functionality are in beta. What about trigger? > MySQL's case insensitivity is a result of having to compensate for > Windows' terrible deficencies in some areas and a design choice in > others (which is actually a pretty stupid design choice if you ask me - > case-sensitive string comparisons are always faster). Why the Windows part? MS SQL is case sensitive. > Regarding the comparisons you speak of, could you point me to some of > them? I'm yet to see a recent, decent comparison between the two. I'd > love to see PostgreSQL 7.4.1 vs MySQL 4.0.17 / 4.1.1 on Linux 2.6.x or > FreeBSD 5.2.x . If you point your newsreader to gmane.org and search in the group gmane.comp.db.postgresql.general and gmane.comp.db.postgresql.performance for topics with mysql you'll find lot of interesting threads. The problem is that you can't really compare Postgres with MySQL. You could compare Postgres and Oracle for example, but MySQL has lot of features missing so it has to be faster. Comparing Postgres with a current Beta (of MySQL 5?) would be on the one hand fair because they have similar features, on the other hand it's not fair because Postgres has these features for years, in MySQL they are brandnew and beta. So people tend to compare the same application with both Postgres and MySQL. Not fair again because quite a lot of intelligence is done in the application you would normally let the database do if you didn't have to take care of MySQL. I would recommend to use MySQL for applications where you have 100 read operations and one write, there MySQL is unbeaten fast. For critical data I would never recommend MySQL. At the moment, this might change in future. BTW: I wouldn't always recommend Postgres for that job too. Some commercial database can have several advantages - so always use the best database for the job to do :-) > I find your comment regarding the dual licence very interesting. If > you'd like to continue this (hopefully friendly) discussion, please > write to me off the list. :-) I always try to be friendly :-), but I don't see anything to discuss. The license says everything. > > > > BEGIN; > > > > .. whatever dbmail does... > > > > COMMIT; [..] > > As a quick speed up that's a good solution yes. But if it's the right > > permanent solution? > > Personally, I think it is. MyISAM in MySQL is a great table type for lots > and lots of INSERT statements OR lots and lots > of SELECT statements but not both (quoting Jeremy Zawdony from Yahoo!). > Anyone serious about performance in a case like DBMail would probably > use InnoDB anyway. The fact that it is now part of the standard MySQL > distro gives little reason to not use MyISAM tables except for their > ability to perform FULLTEXT searches. Sorry is this a typo or did you really mean MyISAM? So you wouldn't use transactions but let MySQL simply ignore the transaction commands? -- MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)
