MySQL Bugs: #77974: 'PUNCH HOLE support not available' while the kernel actually supports

Bug #77974	'PUNCH HOLE support not available' while the kernel actually supports
Submitted:	7 Aug 2015 8:34	Modified:	19 Aug 2015 1:04
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am testing the new innodb compression of mysql 5.7.8.  but always print the bellow  message in error log:
  [Note] InnoDB: PUNCH HOLE support not available

From innodb.cmake  file ,  HAVE_FALLOC_PUNCH_HOLE_AND_KEEP_SIZE is not set if the flag of FALLOC_FL_PUNCH_HOLE doesn't exists. 

I am using redhat 6 and actually punch hole is supported. then：
1. i removed the check in innodb.cmake, and always execute: ADD_DEFINITIONS(-DHAVE_FALLOC_PUNCH_HOLE_AND_KEEP_SIZE=1)
2. explicitly define the flags in os0file.cc:
#define FALLOC_FL_PUNCH_HOLE    0x02 /* de-allocates range */
#define FALLOC_FL_KEEP_SIZE     0x01 /* default is extend size */ 

then everything works well.....

I am not sure if this is a common case, because i am not familiar with Linux kernel. So i filed this bug... :(

kernel version : 2.6.32-358 el6.x86_64
 

How to repeat:
described above

Suggested fix:
use the return value of fallocate to check if PUNCH HOLE is supported

correct the category

Hello Zhai,

Thank you for the report.
Observed this with 5.7.8 builds(using binary tarball) on RHEL6(also, OL6).
But works as documented with OL7 and also couldn't locate anything about RHEL6 on https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html  

###

## rhel 6

[root@rhel6 mysql-5.7.8]# uname -r
2.6.32-504.el6.x86_64

[ushastry@rhel6 Downloads]$ md5sum mysql-5.7.8-rc-linux-glibc2.5-x86_64.tar.gz 
187d117cdbdb47d103f003444faf706d  mysql-5.7.8-rc-linux-glibc2.5-x86_64.tar.gz
[ushastry@rhel6 Downloads]$ rpm -qa|grep kernel
..
kernel-firmware-2.6.32-504.el6.noarch
kernel-devel-2.6.32-504.el6.x86_64
kernel-2.6.32-504.el6.x86_64
kernel-headers-2.6.32-504.el6.x86_64
.
[ushastry@rhel6 Downloads]$ 

[root@rhel6 mysql-5.7.8]# cat /etc/*release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Red Hat Enterprise Linux Server release 6.6 (Santiago)
Red Hat Enterprise Linux Server release 6.6 (Santiago)

[root@rhel6 mysql-5.7.8]# bin/mysqld --basedir=/home/ushastry/Downloads/mysql-5.7.8 --datadir=/tmp/data --socket=/tmp/mysql_ushastry.sock
.
2015-08-11T05:07:04.131848Z 0 [Note] bin/mysqld (mysqld 5.7.8-rc) starting as process 2710 ...
2015-08-11T05:07:04.152656Z 0 [Note] InnoDB: PUNCH HOLE support not available
.
.
2015-08-11T05:07:04.560454Z 0 [Note] bin/mysqld: ready for connections.
Version: '5.7.8-rc'  socket: '/tmp/mysql_ushastry.sock'  port: 3306  MySQL Community Server (GPL)

// OL6

[root@cluster-repo mysql-5.7.8-rc-linux-glibc2.5-x86_64]# uname -r
2.6.39-400.250.2.el6uek.x86_64
[root@cluster-repo mysql-5.7.8-rc-linux-glibc2.5-x86_64]# cat /etc/*release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Oracle Linux Server release 6.7
NAME="Oracle Linux Server"
VERSION="6.7"
ID="ol"
VERSION_ID="6.7"
PRETTY_NAME="Oracle Linux Server 6.7"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:6:7"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 6"
ORACLE_BUGZILLA_PRODUCT_VERSION=6.7
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=6.7
Red Hat Enterprise Linux Server release 6.7 (Santiago)
Oracle Linux Server release 6.7

bin/mysql_install_db --insecure --basedir=/data/ushastry/source/mysql-5.7.8-rc-linux-glibc2.5-x86_64 --datadir=/data/ushastry/source/mysql-5.7.8-rc-linux-glibc2.5-x86_64/77972 -v
bin/mysqld --no-defaults --basedir=/data/ushastry/source/mysql-5.7.8-rc-linux-glibc2.5-x86_64 --datadir=/data/ushastry/source/mysql-5.7.8-rc-linux-glibc2.5-x86_64/77972 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/data/ushastry/source/mysql-5.7.8-rc-linux-glibc2.5-x86_64/77972/log.err 2>&1 &

.
[root@cluster-repo mysql-5.7.8-rc-linux-glibc2.5-x86_64]# cat 77972/log.err
2015-08-09T19:16:36.633199Z 0 [Note] InnoDB: PUNCH HOLE support not available
.
.
Version: '5.7.8-rc'  socket: '/tmp/mysql_ushastry.sock'  port: 15000  MySQL Community Server (GPL)

Thanks,
Umesh

// OL7 - Not affected

[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc: uname -r
3.8.13-44.1.1.el7uek.x86_64
[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc: cat /proc/version
Linux version 3.8.13-44.1.1.el7uek.x86_64 (mockbuild@ca-build56.us.oracle.com) (gcc version 4.8.2 20140120 (Red Hat 4.8.2-16) (GCC) ) #2 SMP Tue Sep 9 22:50:46 PDT 2014

// 5.7.9

bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/77972 -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/77972 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/77972/log.err 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: cat 77972/log.err
2015-08-07T11:22:21.788779Z 0 [Note] InnoDB: PUNCH HOLE support available
.
2015-08-07T11:22:21.936032Z 0 [Note] bin/mysqld: ready for connections.
Version: '5.7.9-enterprise-commercial-advanced'  socket: '/tmp/mysql_ushastry.sock'  port: 15000  MySQL Enterprise Server - Advanced Edition (Commercial)
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9:

// 5.7.8

[umshastr@hod03]/export/umesh/server/source/77974: md5sum mysql-5.7.8-rc.tar.gz
f6f1c39050f2428add2b0771c2766f82  mysql-5.7.8-rc.tar.gz

[umshastr@hod03]/export/umesh/server/source/77974: cd mysql-5.7.8-rc
[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc:

[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc:  cmake . -DCMAKE_INSTALL_PREFIX=/export/umesh/server/source/77974/mysql-5.7.8-rc -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/tmp/
[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc: make -j16
[umshastr@hod03]/export/umesh/server/source/77974/mysql-5.7.8-rc: make install

bin/mysql_install_db --insecure --basedir=/export/umesh/server/source/77974/mysql-5.7.8-rc --datadir=/export/umesh/server/source/77974/mysql-5.7.8-rc/77972 -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/source/77974/mysql-5.7.8-rc --datadir=/export/umesh/server/source/77974/mysql-5.7.8-rc/77972 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/source/77974/mysql-5.7.8-rc/77972/log.err 2>&1 &

.
2015-08-07T10:57:21.444231Z 0 [Note] InnoDB: PUNCH HOLE support available

1. This is a build issue. Most likely the binary is built on a host that doesn't support punch hole.

2. We can't make this a runtime check. The flags FALLOC_FL_PUNCH_HOLE  and FALLOC_FL_KEEP_SIZE need to be defined in order to call fallocate() with the relevant args.

3. I'm open to other suggestions.

Hi, sunny, I think the flags can be defined explicitly if they are not defined, such as:
--- a/storage/innobase/os/os0file.cc
+++ b/storage/innobase/os/os0file.cc
@@ -72,6 +72,13 @@ Created 10/21/1995 Heikki Tuuri
 #include <lz4.h>
 #include <zlib.h>
 
+#ifndef FALLOC_FL_PUNCH_HOLE
+#define FALLOC_FL_PUNCH_HOLE    0x02 /* de-allocates range */
+#endif
+#ifndef FALLOC_FL_KEEP_SIZE
+#define FALLOC_FL_KEEP_SIZE     0x01 /* default is extend size */ 
+#endif

And check if punch hole is available during initiation of innodb (for example, in function innobase_start_or_create_for_mysql, create/open a tmp file,  test if punch hole supported , and then delete the tmp file)

Posted by developer:
 
There is no guarantee that the flags will have the same values on all supported platforms. I think this is a build issue.

Yes, it's not  guaranteed. But we can create a temporary file and check if punch hole is working, If not, then disable this feature.
I do really hope this feature can be checked during run time , because we have more than tens of  thousands of mysql instances and varies of Linux kernel version. So typically we build the mysql on a machine with a lower version of kernel for compatibility.

i found the generic .tar.gz linux package won't support punchhole. building from source does though.  not sure about rpm's as i dont use them.

According to our platform roadmap, the generic Linux binaries are for glibc 2.5 and libstdc++ 4.1.2, which were originally released about 10 years ago. If we added support for PUNCH HOLE to the generic binaries, then we should test them with some old environments or provide some way to disable the feature in case there are problems.