MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mark Date:May 12 2002 9:40pm
Subject:Re: AUTOCOMMIT and FLUSH?
View as plain text  
----- Original Message -----
From: "Paul DuBois" <paul@stripped>
To: "Mark" <admin@stripped>; <mysql@stripped>
Sent: Sunday, May 12, 2002 2:39 AM
Subject: Re: AUTOCOMMIT and FLUSH?

> At 2:16 +0200 5/12/02, Mark wrote:
>
> > Hello folks,
> >
> > Could somebody please tell me what the effectual difference is between
> > AUTOCOMMIT and FLUSH? The manual says, "By default, MySQL runs in
> > autocommit mode. This means that as soon as you execute an update, MySQL
> > will store the update on disk." Good. But what is left to flush then?
> > INSERT DELAYED queries and the like? Or any other outstanding queries
> > for that matter?
>
> It depends on what you're flushing, but if you read the manual section on
> the FLUSH statement, you'll see that it's unrelated to committing queries.
>
> http://www.mysql.com/doc/F/L/FLUSH.html
>
> Nor is FLUSH one of the statements that is listed as causing an implicit
> COMMIT:


Dear Paul,

Of course I read the manual -- even quoted from it. :)

You say FLUSH is "unrelated to committing queries;" yet about "FLUSH TABLES"
the manual says, "Closes all open tables and force all tables in use to be
closed. This also flushes the query cache." Committing a query and flushing
it may well be two different things, but can you see my confusion?

I honestly find the manual a bit vague in this regard. "You should use the
FLUSH command if you want to clear some of the internal caches MySQL uses,"
is not too descriptive to me.

The manual also says, "This means that the data is safe even if mysqld
crashes, as the OS will ensure that the not flushed data is written to disk.
You can force MySQL to sync everything to disk after every SQL command by
starting mysqld with --flush."

That statement I can only read to mean that mysqld not started with --flush
leaves data unwritten while the daemon is runnig, right? And that,
therefore, autocommit definitely leaves things uncommitted. Or, I should
say, leaves "some of the internal caches MySQL uses" unwritten; things, if I
interpret this correctly, without which your tables wind up corrupted in
case of a drastic event like a system reset, but which will survive a
"regular" crash of the mysql daemon, as the OS will ensure that the not
flushed data is written to disk, right?

I could run mysqld with --flush, but that feels like an unwelcome I/O
overhead to me. Plus, come to think of it, I could make a good case out of
arguing that running without --flush makes you safer against a system reset,
as the many intermediate flushes of running mysqld with --flush enlarges the
chance of getting caught "mid-write" so to speak.

I am sorry if I sound nit-picky about this whole stuff. I just do not
understand what it is that corrupts my tables when I have not FLUSH-ed them
and a system reset should occur.

- Mark

Thread
AUTOCOMMIT and FLUSH?Mark12 May
  • Re: AUTOCOMMIT and FLUSH?Paul DuBois12 May
  • Re: AUTOCOMMIT and FLUSH?Mark12 May