srv_use_posix_fallocate should be on by default

[Raghavendra D Prabhu]

Currently, srv_use_posix_fallocate is turned on (and is internal) only when InnoDB
atomic writes are on. However, fallocate has many benefits over
pwrite/write-ing of zeroes that is currently used in extend path
(and some other probably): it works independent of file size (so
works like O(1) rougly),
does lazy allocation (XFS, ext4 etc. do that), and thus provides
much better usage around fil_system->mutex by decreasing the time
it is held. So, it should be ON by default.

There may be rare
instances where people may want to turn it off (really old
kernels/distros), so having it as a global varaible (defaulting
to ON) should be nice.

[Laurynas Biveinis (laurynas-biveinis)]

See the thread at http://lists.mysql.com/internals/38679.

[Valerii Kravchuk (valerii-kravchuk)]

Code (storage/innobase/handler/ha_innodb.cc) is like this:

...
        if (innobase_use_atomic_writes) {
                ib_logf(IB_LOG_LEVEL_INFO, "using atomic writes.");

                /* Force doublewrite buffer off, atomic writes replace it. */
                if (srv_use_doublewrite_buf) {
                        ib_logf(IB_LOG_LEVEL_INFO, "switching off doublewrite "
                                "buffer because of atomic writes.");
                        innobase_use_doublewrite = FALSE;
                        srv_use_doublewrite_buf = FALSE;
                }

                /* Force O_DIRECT on Unixes (on Windows writes are always
                unbuffered)*/
#ifndef _WIN32
                if(!innobase_file_flush_method ||
                   !strstr(innobase_file_flush_method, "O_DIRECT")) {
                        innobase_file_flush_method =
                                srv_file_flush_method_str = (char*)"O_DIRECT";
                        ib_logf(IB_LOG_LEVEL_INFO,
                                "using O_DIRECT due to atomic writes.");
                }
#endif
#ifdef HAVE_POSIX_FALLOCATE
                /* Due to a bug in directFS, using atomics needs
                posix_fallocate() to extend the file, because pwrite() past the
                end of the file won't work */
                srv_use_posix_fallocate = TRUE;
#endif
        }

[Raghavendra D Prabhu (raghavendra-prabhu)]

Regarding http://lists.mysql.com/internals/38679, yes, the
benchmarks in the last mail of thread look good (though the
overhead of number of tables may have added to delay there as
well). Also, regarding "cases which this patch does not cover", I
think this may have been an allusion to fallocate not covering
all filesystems, which doesn't apply to posix_fallocate.

One more thing is, currently, it seems to be enabled for directfs but not
for others while the support for fallocate in in-tree filesystems
is much better.

There is one more place where fallocate is used, but this is for
an entirely different purpose - hole punching while keeping the
size:

#if 0 //def FALLOC_FL_PUNCH_HOLE
  /* Try to deallocate the space for the file on disk.
  This should work on ext4 on Linux 2.6.39 and later,
  and be ignored when the operation is unsupported. */
  fallocate(index->online_log->fd,
     FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE,
     ofs, srv_buf_size);
#endif /* FALLOC_FL_PUNCH_HOLE */

It is currently disabled due to

revno: 5400
revision-id: <email address hidden>
parent: <email address hidden>
committer: Satya Bodapati <email address hidden>
branch nick: mysql-5.6
timestamp: Fri 2013-08-23 18:03:27 +0530
message:
BUG#17316314 - SRV_BUF_SIZE NOT DECLARED

Temporary fix. Disabling FALLOC_FL_PUNCH_HOLE for now

------------------------------------------------------------

http://bugs.mysql.com/bug.php?id=70047

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2443