Thomas Mueller wrote:
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 :-)
Right then, transactions are the solution!
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 :)
We could easily make it more off-topic, but how to do so would be
off-topic...
Today, MySQL does have transactions. foreign key constraints and
subselect and stored procedure functionality are in beta.
What about trigger?
If I recall correctly, triggers are in the 5.1 timeframe. Makes sense,
considering you want solid stored procedures to use in your triggers.
The possibilty of having pluggable stored procedure modules is a nice
though though - the number of times I have had clients look at me in a
state of shock when I tell them how much a Windows 2003 Server + SQL
Server setup is going to cost them for whatever application they need it
for.
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.
MySQL stores table definitions as seperate files. Due to the Win32 API
not being fully case-aware, table names on Windows are always
case-insensitive. I believe there is a MySQL option to alter case
sensitivity though.
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.
That's also a bit of a generalisation. For instance, MS SQL Server has a
lot of features missing compared to DB2 and Oracle. Yet, there are
plenty of benchmarks showing DB2 and Oracle dominating SQL Server and
qutie a few that go the other way. The correlation between feature set
and level of performance is erratic at best (where Pearson's r is about 0).
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.
True, but MySQL has always put performance first. It is rare that a
production release is significantly faster than the first alpha release
in that series (for instance, 4.0.1 vs 4.0.18).
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.
This can be quite fair, as many applications are built to the
lowest-common-denominator, which would be FoxPro. :-)
In all seriousness though, if you have an application that does things
in the application regardless of database used, the comparison is fair.
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.
MySQL + InnoDB is one blazing combination. InnoDB is multiversioned,
just like PostgreSQL. InnoDB also has the advantage of being one of the
youngest mutli-versioning implementations around. The fact that phantom
rows can not appear at any isolation level allows for higher performance
in some applications through not needing to go to SERIALIZABLE isolation.
Additionally, InnoDB Hot Backup is an excellent backup solution for
maintaining performance during a hot backup, not placing any additional
strain on the database than that caused by the users logged in. pg_dump
acts as a normal client app.
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 :-)
Indeed! The only thing you need to remember is that the best database is
rarely a Microsoft product - Access is slow, FoxPro's data storage is a
sick joke and MS SQL Server does some very disturbing things to maintain
the level of performance advertised.
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.
The way I see it, it makes sense. Either you give back to the open
source community through software or you ensure that open source
software continues to develop through funding some of that development.
Call it karma if you will.
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?
Yes! If someone has an attachment to the MyISAM table type (for the
HANDLER interface, FULLTEXT indexes, OpenGIS-types in 4.1.0 and higher)
then they are fully aware of things that can happen in applications that
rely on the ROLLBACK statement.
Regards,
Chris
