You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-dev@james.apache.org by Serge Knystautas <sk...@gmail.com> on 2005/06/25 19:23:44 UTC
Innodb better?
After reading some tips on mysql optimization, I altered my email
table for James from myisam to innodb. The rational was that there is
a lot of concurrent writing happening, so it would greatly benefit
from row-level locking.
It seems like things are faster, though I haven't done any real tests.
For others who know MySQL well, does this make sense, and should we
alter the create scripts to use this table type?
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Vincenzo Gianferrari Pini <vi...@praxis.it>.
Serge,
In my understanding, InnoDB offers two major functionalities: row-level
locking and ACID transactions.
1) Row-level locking
The only tables with lots of updates are inbox and spool, but the
inserts and deletes are always not conflicting, so using MyIsam tables
without table-level locking (as is by default in James) is safe (MyIsam
"atomic" updates are always guaranteed to be safe) and should be faster
than using InnoDB .
2) ACID transactions
Unless I'm completely wrong, in the worst case for each mail James reads
a row from the spool table, writes it to the inbox table and deletes it
from the spool table in the same transaction. Assuming that the order of
events is as just said, in case of a crash or JDBC abort, if using
MyIsam without ACID transactions and using autocommit there could be the
very unlike risk of ending up with the mail both still in the spool and
delivered to the inbox, but this would only imply the mail later on
being delivered again, which is not so terrible.
I personally feel nervous if I'm using a database without transaction
support even when not really needed, but others would not agree.
--------
Given that, and because InnoDB is not to be granted as available in a
new James installation, IMHO it is better to leave sqlResources.xml as
it is now. In any case, if the user wants to always use InnoDB like me
just as a matter of principle, it is enough for him to set
"default-table-type=innodb" in my.cnf.
Completely different is the case where a matcher or mailet issues
updates that *must* be under the umbrella of the same transaction, as is
for example the case of the BayesianAnalysisFeeder mailet. Because of
that, and because Bayesian Analysis is an option, I did put
"TYPE=InnoDB" in the sqlResources.xml CREATE TABLE statements for all
the relevant tables.
Moreover, as the updates can be thousands for the same transaction
during a feed, deadlocks could easily happen with row-level locking, so
I inserted a "synchronize" in the right place so only one message at a
time is processed. This single-threading is not a problem because the
rate of training feeds is in any case low. The real BayesianAnalysis
mailet instead is fully multi-threaded (synchronization occurs only when
reloading the corpus).
Vincenzo
Serge Knystautas wrote:
>After reading some tips on mysql optimization, I altered my email
>table for James from myisam to innodb. The rational was that there is
>a lot of concurrent writing happening, so it would greatly benefit
>from row-level locking.
>
>It seems like things are faster, though I haven't done any real tests.
> For others who know MySQL well, does this make sense, and should we
>alter the create scripts to use this table type?
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
RE: Innodb better?
Posted by "Noel J. Bergman" <no...@devtech.com>.
We could go down a whole can of worms here, but perhaps an answer is to
refactor sqlResources.xml into separate files for each database, where there
ARE special operations for that database, and use XML entities to include
them. That way, we can provide two different files for MySQL. Or for any
other DB people want to tune.
--- Noel
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Danny Angus <Da...@slc.co.uk>.
I've vacillated about this, and used both on and off since innodb was made
available in mysql.
My opinion is that it is a DBA concern, but we might like to offer some
documentation on the subject to allow DBA's to make informed choices if we
have knowledge worth sharing.
d.
|---------+---------------------------->
| | Serge Knystautas |
| | <sknystautas@gmai|
| | l.com> |
| | |
| | 25/06/2005 18:23 |
| | Please respond to|
| | "James Developers|
| | List" |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------|
| |
| To: server-dev@james.apache.org |
| cc: |
| Subject: Innodb better? |
>---------------------------------------------------------------------------------------------------------------------------------|
After reading some tips on mysql optimization, I altered my email
table for James from myisam to innodb. The rational was that there is
a lot of concurrent writing happening, so it would greatly benefit
from row-level locking.
It seems like things are faster, though I haven't done any real tests.
For others who know MySQL well, does this make sense, and should we
alter the create scripts to use this table type?
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
***************************************************************************
The information in this e-mail is confidential and for use by the addressee(s) only. If you are not the intended recipient (or responsible for delivery of the message to the intended recipient) please notify us immediately on 0141 306 2050 and delete the message from your computer. You may not copy or forward it or use or disclose its contents to any other person. As Internet communications are capable of data corruption Student Loans Company Limited does not accept any responsibility for changes made to this message after it was sent. For this reason it may be inappropriate to rely on advice or opinions contained in an e-mail without obtaining written confirmation of it. Neither Student Loans Company Limited or the sender accepts any liability or responsibility for viruses as it is your responsibility to scan attachments (if any). Opinions and views expressed in this e-mail are those of the sender and may not reflect the opinions and views of The Student Loans Company Limit
ed.
This footnote also confirms that this email message has been swept for the presence of computer viruses.
**************************************************************************
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Serge Knystautas <sk...@gmail.com>.
On 6/28/05, Stefano Bagnara <ap...@bago.org> wrote:
> I will try InnoDB because of the row level locking: I think this feature
> could improve james spooling performance but I'm not sure.
Thank you. I've only been using mysql for about 2 years but other
databases before that, and row-locking seems critical for high
performance write-heavy apps that James is doing.
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Simon Funnell <si...@atomic-operations.com>.
Hi Joe,
http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html
This is good read, my point is that the choice to use either MyISAM or
INNODB tables depends on how the james application code (of which I am not
aware in this case) is implemented.
> >If anyone asks you if you glass is half empty or half full, tell them you
> >design software so your glass is never empty and always filling.
What I mean by this is that there is no right or wrong in software design,
its all about what is more or less appropriate for a particular context.
Regards, Simon
----- Original Message -----
From: "Joe Cheng" <co...@joecheng.com>
To: "James Developers List" <se...@james.apache.org>
Sent: Tuesday, June 28, 2005 4:45 AM
Subject: Re: Innodb better?
> Hi Simon,
>
> Simon Funnell wrote:
>
> >In the case of hardware failure, no database on this planet can
'guarantee'
> >there will be no corruption, if you have a single point of failure, and
it
> >fails, everything fails, hence the term single as opposed to many points
of
> >failure.
> >
> >
> Of course, it's not reasonable to expect a database to guarantee data
> integrity if the hard drive fails, or if the filesystem itself is
> susceptible to corruption (like FAT, ext2).
>
> However, MyISAM tables can corrupt if the mysql process is merely killed
> during a write, or the power is cut off. Any decent
> logging/journaling[1] persistence strategy will protect you from
> these--there's no reason in the year 2005 for this to be a point of
> failure at all. InnoDB for example should not suffer from this problem,
> if it does then the MySQL folks *really* have no clue what they are doing.
>
> [1] If you're not familiar with the mechanics of journaling, see Chapter
> 7 of:
> http://www.nobius.org/~dbg/practical-file-system-design.pdf
>
> >One of the most important things about software design, in any area, is
the
> >concept of maximisation and minimisation.
> >
> >For example, in this case you can only minimise the chance of corruption,
or
> >maximise the chance the of recovery, or both.
> >
> >
> My understanding is that InnoDB is (far) less likely than MyISAM to
> corrupt and easier to recover if it does. So a vote for MyISAM is
> really a vote for speed at the expense of safety. My question is
> whether MyISAM is a reasonable default for an enterprise mail server--or
> any mail server at all.
>
> >Obviously, having both sides of the story present will reveal a fuller
> >picture, its called the art of abstraction.
> >
> >If anyone asks you if you glass is half empty or half full, tell them you
> >design software so your glass is never empty and always filling.
> >
> >
> Whoa, now you lost me... :)
>
> >Simon
> >
> >
> >----- Original Message -----
> >From: "Joe Cheng" <co...@joecheng.com>
> >To: "James Developers List" <se...@james.apache.org>
> >Sent: Tuesday, June 28, 2005 12:27 AM
> >Subject: Re: Innodb better?
> >
> >
> >
> >
> >>http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html
> >>
> >>According to this link, killing mysqld or turning off the computer in
> >>mid-write could result in table corruption with MyISAM. Not too
> >>surprising considering it doesn't support transactions. I personally
> >>would find this completely unacceptable for an "enterprise" e-mail
server.
> >>
> >>Doesn't this terrify everyone else, and if not, why not??
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> >>For additional commands, e-mail: server-dev-help@james.apache.org
> >>
> >>
> >>
> >>--
> >>No virus found in this incoming message.
> >>Checked by AVG Anti-Virus.
> >>Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
> >>
> >>
> >>
> >>
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> >For additional commands, e-mail: server-dev-help@james.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> For additional commands, e-mail: server-dev-help@james.apache.org
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Simon Funnell <si...@atomic-operations.com>.
> [1] If you're not familiar with the mechanics of journaling, see Chapter
> 7 of:
> http://www.nobius.org/~dbg/practical-file-system-design.pdf
Filesystem heaven.
http://www.namesys.com/
Simon
----- Original Message -----
From: "Joe Cheng" <co...@joecheng.com>
To: "James Developers List" <se...@james.apache.org>
Sent: Tuesday, June 28, 2005 4:45 AM
Subject: Re: Innodb better?
> Hi Simon,
>
> Simon Funnell wrote:
>
> >In the case of hardware failure, no database on this planet can
'guarantee'
> >there will be no corruption, if you have a single point of failure, and
it
> >fails, everything fails, hence the term single as opposed to many points
of
> >failure.
> >
> >
> Of course, it's not reasonable to expect a database to guarantee data
> integrity if the hard drive fails, or if the filesystem itself is
> susceptible to corruption (like FAT, ext2).
>
> However, MyISAM tables can corrupt if the mysql process is merely killed
> during a write, or the power is cut off. Any decent
> logging/journaling[1] persistence strategy will protect you from
> these--there's no reason in the year 2005 for this to be a point of
> failure at all. InnoDB for example should not suffer from this problem,
> if it does then the MySQL folks *really* have no clue what they are doing.
>
> [1] If you're not familiar with the mechanics of journaling, see Chapter
> 7 of:
> http://www.nobius.org/~dbg/practical-file-system-design.pdf
>
> >One of the most important things about software design, in any area, is
the
> >concept of maximisation and minimisation.
> >
> >For example, in this case you can only minimise the chance of corruption,
or
> >maximise the chance the of recovery, or both.
> >
> >
> My understanding is that InnoDB is (far) less likely than MyISAM to
> corrupt and easier to recover if it does. So a vote for MyISAM is
> really a vote for speed at the expense of safety. My question is
> whether MyISAM is a reasonable default for an enterprise mail server--or
> any mail server at all.
>
> >Obviously, having both sides of the story present will reveal a fuller
> >picture, its called the art of abstraction.
> >
> >If anyone asks you if you glass is half empty or half full, tell them you
> >design software so your glass is never empty and always filling.
> >
> >
> Whoa, now you lost me... :)
>
> >Simon
> >
> >
> >----- Original Message -----
> >From: "Joe Cheng" <co...@joecheng.com>
> >To: "James Developers List" <se...@james.apache.org>
> >Sent: Tuesday, June 28, 2005 12:27 AM
> >Subject: Re: Innodb better?
> >
> >
> >
> >
> >>http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html
> >>
> >>According to this link, killing mysqld or turning off the computer in
> >>mid-write could result in table corruption with MyISAM. Not too
> >>surprising considering it doesn't support transactions. I personally
> >>would find this completely unacceptable for an "enterprise" e-mail
server.
> >>
> >>Doesn't this terrify everyone else, and if not, why not??
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> >>For additional commands, e-mail: server-dev-help@james.apache.org
> >>
> >>
> >>
> >>--
> >>No virus found in this incoming message.
> >>Checked by AVG Anti-Virus.
> >>Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
> >>
> >>
> >>
> >>
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> >For additional commands, e-mail: server-dev-help@james.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> For additional commands, e-mail: server-dev-help@james.apache.org
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Joe Cheng <co...@joecheng.com>.
Stefano Bagnara wrote:
>>However, MyISAM tables can corrupt if the mysql process is
>>merely killed during a write, or the power is cut off. Any
>>decent logging/journaling[1] persistence strategy will
>>protect you from these--there's no reason in the year 2005
>>for this to be a point of failure at all. InnoDB for example
>>should not suffer from this problem, if it does then the
>>MySQL folks *really* have no clue what they are doing.
>>
>>
>I think here there are too many people talking about theory and interpreting
>sentences to easily.
>
>
That may be--database administration is neither my job nor hobby and I
don't claim to be an expert at anything.
The difference between MyISAM and InnoDB seems (in theory!) analogous to
the difference between FAT and NTFS (or ext2 and ext3), in that the
former is non-journalled and the latter is. In my personal experience I
have lost a lot of data to non-journalled filesystems (power outages,
faulty power supply, or simply frozen computer). That is why I was so
alarmed when it sounded like people were casually dismissing InnoDB as a
default choice.
However, last night I found out that MySQL offers a "binary log" that
you can use with MyISAM that, for data recovery purposes, should let you
(manually) recover from a crash as long as you have a backup from when
the log began. Although I couldn't figure out whether this
functionality is enabled by default.
There may also be reasons why MyISAM is inherently more robust than say
FAT--for example I found it surprising that myisamchk is so successful
most of the time (according to most articles about it I could find).
>I personally ran multiple MySQL, from 1GB to 80GB databases, for 5 years
>having millions of updates per day and never lost data. After few hardware
>crash mysql at run said "myisam table corrupted, run myisamchk for a manual
>repair" (or something similar) I simply ran myisamchk and repaired the table
>before restart: it worked fine.
>
>
While that's great to hear, I would be wary of relying too heavily on
anecdotal evidence when talking about data loss issues. That sounds to
me like saying "I've never worn seatbelts for the last 20 years and I've
always been fine."
>Have you ever restarted an Oracle machine after an hardware crash? It happen
>once to me and I had to call an Oracle DBA because the DB was not restarting
>automatically. For sure maybe I've had luck with MySQL and lesser with
>Oracle but here is my practice.
>
>IMHO No transaction support doesn't mean more probability of data
>corruption: When the application does not use transaction you don't know
>wether is safer a transactional db or a not transactional one.
>
>
To be precise, I'm not talking about transactional vs. not
transactional. I am talking about journaling vs. non-journaling.
You're right that the former does not mean increased crash safety, but
the latter certainly should. I agree with you that a non-transactional
application could end up in an inconsistent state in any case.
>And about your sentence about MySQL folks, I would consider that MySQL is
>the MOST installed DB around, it simply works and it is FAST.
>
>
I said *if* InnoDB is a journaling database that doesn't protect you
against crashes, then they don't know what they're doing. The very
point of journaling is to protect against this exact problem.
>James does not use transaction and if you look at source code you will find
>plenty of cases where after a SOFT restart it will resend the same message
>twice or after an HARD kill it will loose messages so I think that we should
>not give so much importance to the non transactionality of mysql.
>
>
Again, I'm not talking about the transactionality aspect and losing
in-flight messages (although perhaps now we should!). I am talking
about journaling and whether arbitrary amounts of data can be lost in a
database failure, and whether this problem can be avoided by simply
defaulting to InnoDB.
It may very well be that MyISAM is not actually susceptible to data loss
during a crash, but if so, I would feel a lot more comfortable knowing
the technical reason for that.
>I will try InnoDB because of the row level locking: I think this feature
>could improve james spooling performance but I'm not sure.
>
>
Cool... I would also be interesting in hearing how bad the real-world
performance impact on the rest of JAMES is.
>Stefano
>
>PS: it is REALLY easy to setup a slave mysql server that replicate your
>original server for better availability.
>
>
Yes, it's also very easy to tell James to use InnoDB. I'm just assuming
that most users will run James in whatever mode is the default.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Vincenzo Gianferrari Pini <vi...@praxis.it>.
Just to help make things clear, here are some snippets from the MySql
documentation:
[...]
MySQL Server (version 3.23-max and all versions 4.0 and above)
supports transactions with the |InnoDB| and |BDB| transactional
storage engines. |InnoDB| provides /full/ |ACID| compliance. [...]
The other non-transactional storage engines in MySQL Server (such as
|MyISAM|) follow a different paradigm for data integrity called
``atomic operations.'' In transactional terms, |MyISAM| tables
effectively always operate in |AUTOCOMMIT=1| mode. Atomic operations
often offer comparable integrity with higher performance.
[...]
``Atomic,'' in the sense that we mean it, is nothing magical. It
only means that you can be sure that while each specific update is
running, no other user can interfere with it, and there will never
be an automatic rollback (which can happen with transactional tables
if you are not very careful). MySQL Server also guarantees that
there will not be any dirty reads.
[...]
It means that MyISAM works like having transactions containing a single
update statement, offering row-level locking one at a time and being
able to recover from crashes (in the worst case using myisamchk).
James for normal operations *does use transactions* (see
org.apache.james.mailrepository.JDBCMailRepository#store(MailImpl mc)),
but does only one update at a time. So MyISAM is perfectly appropriate
and safe, and InnoDB row-level locking would bring no advantage. The
real disadvantage od MyISAM is the occasional need to run *manually* the
myisamchk utility, or setting up an appropriate startup script.
Because of such lack of automatic recovery in all situations, I feel
much more confortable using InnoDB, but if performance is an issue
MyISAM would perform better.
I did also setup a slave server, but just to have a second server always
ready to run if the primary server is unable to restart, not for having
better crash integrity. The synchronization is almost instantaneous.
Vincenzo
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Serge Knystautas <sk...@gmail.com>.
On 6/28/05, Vincenzo Gianferrari Pini
<vi...@praxis.it> wrote:
> It means that MyISAM works like having transactions containing a single
> update statement, offering row-level locking one at a time and being
> able to recover from crashes (in the worst case using myisamchk).
MyISAM does not support row-level locking and InnoDB does, so unless
I'm mistaken... With MyISAM while one thread is inserting or updating
a message, it has a table-level lock that prevents other threads from
reading messages.
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Serge Knystautas <sk...@gmail.com>.
On 6/28/05, Joe Cheng <co...@joecheng.com> wrote:
> I said *if* InnoDB is a journaling database that doesn't protect you
> against crashes, then they don't know what they're doing. The very
> point of journaling is to protect against this exact problem.
Agreed.
Incidentally, you can control the flush rules for InnoDB, which is
what I've done for my James install. I only found this from listening
to the Top 5 Performance Tips webinar, not from the docs. You can
either have it:
- flush from memory to the journal log at the completion of
transaction (true ACID)
- flush every second
- flush at some longer interval
I disabled the transaction sanctity, so I get the performance benefit
of row-level locking without any potential performance hit from
synchronous journaling. I wouldn't recommend this by default, but I
found it interesting.
> Again, I'm not talking about the transactionality aspect and losing
> in-flight messages (although perhaps now we should!). I am talking
> about journaling and whether arbitrary amounts of data can be lost in a
> database failure, and whether this problem can be avoided by simply
> defaulting to InnoDB.
Transactions and journaling are rather related, though I agree we're
not discussing whether James is transaction-based. James is not,
regardless of what we set the repository to, and it's an SMTP server
so it doesn't need to be... the protocol is supposed to support
retries and duplicate messages. The code throughout errs on the side
of duplicates instead of losing a message.
I just would prefer not have an InnoDB table come right back online
after a crash rather than run myismchk. It's an easy command, but
until you learn it is somewhat frustrating. Well, it was frustrating
to me :)
> >PS: it is REALLY easy to setup a slave mysql server that replicate your
> >original server for better availability.
> >
> >
> Yes, it's also very easy to tell James to use InnoDB. I'm just assuming
> that most users will run James in whatever mode is the default.
I would be curious about how having a slave would solve any of these
problems. Aside from the data being potentially lagged, how do you
cut over safely? The MySQL driver has failover support, but to
read-only connections. Yes, you can override that now, but then you
potentially lose messages because you are reading and writing against
a lagged database, and you would have to just dump the master rather
than figure out what messages had not yet been replicated.
Anyway, I'm dealing with this mysql failover for another project and
can't seem to avoid losing database. Maybe we should just all switch
to NDBCLUSTER table types (MySQL Cluster) and never have database
failures again!
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Serge Knystautas <sk...@gmail.com>.
On 6/28/05, Serge Knystautas <sk...@gmail.com> wrote:
> Incidentally, you can control the flush rules for InnoDB, which is
> what I've done for my James install. I only found this from listening
> to the Top 5 Performance Tips webinar, not from the docs. You can
> either have it:
> - flush from memory to the journal log at the completion of
> transaction (true ACID)
> - flush every second
> - flush at some longer interval
>
> I disabled the transaction sanctity, so I get the performance benefit
> of row-level locking without any potential performance hit from
> synchronous journaling. I wouldn't recommend this by default, but I
> found it interesting.
Some of these tips appear here:
http://dev.mysql.com/doc/mysql/en/innodb-tuning.html
--
Serge Knystautas
Lokitech >> software . strategy . design >> http://www.lokitech.com
p. 301.656.5501
e. sergek@lokitech.com
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Stefano Bagnara <ap...@bago.org>.
> However, MyISAM tables can corrupt if the mysql process is
> merely killed during a write, or the power is cut off. Any
> decent logging/journaling[1] persistence strategy will
> protect you from these--there's no reason in the year 2005
> for this to be a point of failure at all. InnoDB for example
> should not suffer from this problem, if it does then the
> MySQL folks *really* have no clue what they are doing.
I think here there are too many people talking about theory and interpreting
sentences to easily.
I personally ran multiple MySQL, from 1GB to 80GB databases, for 5 years
having millions of updates per day and never lost data. After few hardware
crash mysql at run said "myisam table corrupted, run myisamchk for a manual
repair" (or something similar) I simply ran myisamchk and repaired the table
before restart: it worked fine.
Have you ever restarted an Oracle machine after an hardware crash? It happen
once to me and I had to call an Oracle DBA because the DB was not restarting
automatically. For sure maybe I've had luck with MySQL and lesser with
Oracle but here is my practice.
IMHO No transaction support doesn't mean more probability of data
corruption: When the application does not use transaction you don't know
wether is safer a transactional db or a not transactional one.
And about your sentence about MySQL folks, I would consider that MySQL is
the MOST installed DB around, it simply works and it is FAST.
James does not use transaction and if you look at source code you will find
plenty of cases where after a SOFT restart it will resend the same message
twice or after an HARD kill it will loose messages so I think that we should
not give so much importance to the non transactionality of mysql.
I will try InnoDB because of the row level locking: I think this feature
could improve james spooling performance but I'm not sure.
Stefano
PS: it is REALLY easy to setup a slave mysql server that replicate your
original server for better availability.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Joe Cheng <co...@joecheng.com>.
Hi Simon,
Simon Funnell wrote:
>In the case of hardware failure, no database on this planet can 'guarantee'
>there will be no corruption, if you have a single point of failure, and it
>fails, everything fails, hence the term single as opposed to many points of
>failure.
>
>
Of course, it's not reasonable to expect a database to guarantee data
integrity if the hard drive fails, or if the filesystem itself is
susceptible to corruption (like FAT, ext2).
However, MyISAM tables can corrupt if the mysql process is merely killed
during a write, or the power is cut off. Any decent
logging/journaling[1] persistence strategy will protect you from
these--there's no reason in the year 2005 for this to be a point of
failure at all. InnoDB for example should not suffer from this problem,
if it does then the MySQL folks *really* have no clue what they are doing.
[1] If you're not familiar with the mechanics of journaling, see Chapter
7 of:
http://www.nobius.org/~dbg/practical-file-system-design.pdf
>One of the most important things about software design, in any area, is the
>concept of maximisation and minimisation.
>
>For example, in this case you can only minimise the chance of corruption, or
>maximise the chance the of recovery, or both.
>
>
My understanding is that InnoDB is (far) less likely than MyISAM to
corrupt and easier to recover if it does. So a vote for MyISAM is
really a vote for speed at the expense of safety. My question is
whether MyISAM is a reasonable default for an enterprise mail server--or
any mail server at all.
>Obviously, having both sides of the story present will reveal a fuller
>picture, its called the art of abstraction.
>
>If anyone asks you if you glass is half empty or half full, tell them you
>design software so your glass is never empty and always filling.
>
>
Whoa, now you lost me... :)
>Simon
>
>
>----- Original Message -----
>From: "Joe Cheng" <co...@joecheng.com>
>To: "James Developers List" <se...@james.apache.org>
>Sent: Tuesday, June 28, 2005 12:27 AM
>Subject: Re: Innodb better?
>
>
>
>
>>http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html
>>
>>According to this link, killing mysqld or turning off the computer in
>>mid-write could result in table corruption with MyISAM. Not too
>>surprising considering it doesn't support transactions. I personally
>>would find this completely unacceptable for an "enterprise" e-mail server.
>>
>>Doesn't this terrify everyone else, and if not, why not??
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
>>For additional commands, e-mail: server-dev-help@james.apache.org
>>
>>
>>
>>--
>>No virus found in this incoming message.
>>Checked by AVG Anti-Virus.
>>Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
>>
>>
>>
>>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
>For additional commands, e-mail: server-dev-help@james.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Simon Funnell <si...@atomic-operations.com>.
In the case of hardware failure, no database on this planet can 'guarantee'
there will be no corruption, if you have a single point of failure, and it
fails, everything fails, hence the term single as opposed to many points of
failure.
One of the most important things about software design, in any area, is the
concept of maximisation and minimisation.
For example, in this case you can only minimise the chance of corruption, or
maximise the chance the of recovery, or both.
Obviously, having both sides of the story present will reveal a fuller
picture, its called the art of abstraction.
If anyone asks you if you glass is half empty or half full, tell them you
design software so your glass is never empty and always filling.
Simon
----- Original Message -----
From: "Joe Cheng" <co...@joecheng.com>
To: "James Developers List" <se...@james.apache.org>
Sent: Tuesday, June 28, 2005 12:27 AM
Subject: Re: Innodb better?
> http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html
>
> According to this link, killing mysqld or turning off the computer in
> mid-write could result in table corruption with MyISAM. Not too
> surprising considering it doesn't support transactions. I personally
> would find this completely unacceptable for an "enterprise" e-mail server.
>
> Doesn't this terrify everyone else, and if not, why not??
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
> For additional commands, e-mail: server-dev-help@james.apache.org
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Joe Cheng <co...@joecheng.com>.
http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html
According to this link, killing mysqld or turning off the computer in
mid-write could result in table corruption with MyISAM. Not too
surprising considering it doesn't support transactions. I personally
would find this completely unacceptable for an "enterprise" e-mail server.
Doesn't this terrify everyone else, and if not, why not??
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
Re: Innodb better?
Posted by Daniel Rebolo <da...@adinet.com.uy>.
I read a lot of myisam or innodb databases.. but it is difficult to choose
one of them...
myisam, is much faster than innodb, but innodb has more security (transaccions
and row-level rocking).
is it possible to use james with myisam and dont less the security, i mean,
perhaps.. it is a way en mysql to configure myisam to use transaccions..
is it possible?
tell me.. what you suggest me .. innodb or myisam tables???
thanks people!!
>-- Mensaje original --
>Reply-To: "James Developers List" <se...@james.apache.org>
>Date: Sat, 25 Jun 2005 13:23:44 -0400
>From: Serge Knystautas <sk...@gmail.com>
>Reply-To: Serge Knystautas <se...@lokitech.com>
>To: server-dev@james.apache.org
>Subject: Innodb better?
>
>
>After reading some tips on mysql optimization, I altered my email
>table for James from myisam to innodb. The rational was that there is
>a lot of concurrent writing happening, so it would greatly benefit
>from row-level locking.
>
>It seems like things are faster, though I haven't done any real tests.
> For others who know MySQL well, does this make sense, and should we
>alter the create scripts to use this table type?
>
>--
>Serge Knystautas
>Lokitech >> software . strategy . design >> http://www.lokitech.com
>p. 301.656.5501
>e. sergek@lokitech.com
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
>For additional commands, e-mail: server-dev-help@james.apache.org
>
Daniel Rebolo
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org