Bug #81145 Sparse file and punch hole compression not working on Windows
Submitted: 19 Apr 2016 19:46 Modified: 6 May 2016 8:08
Reporter: alexandre Alex Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.11, 5.7.12 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[19 Apr 2016 19:46] alexandre Alex
Description:
Page compression on Windows is not working as expected - lz4 and zlib same result , even with high compression setting

Formatted drive with 4096 byte size cluster and set page to 64K and MySQL reports that it cant use page compression

It only allows with 2096/1024/512 bytes clusters, but there are no compression made at all

A similar issue happens with 
  32K page- 2K cluster (only works with 1024, 512) 
  16K page- 512B cluster (only works with  512) 

How to repeat:
Environment

MySQL 5.7.11 Windows 64 Bit

my.ini
[client]
no-beep

# pipe
socket=0.0
port=3306

[mysql]

default-character-set=utf8

[mysqld]

enable-named-pipe

shared-memory

shared-memory-base-name=MYSQL57

socket=MYSQL57

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="D:/MySQL57/"

# Path to the database root
datadir=D:/MySQL57\Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# Enable Windows Authentication
# plugin-load=authentication_windows.dll

# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="MOR-DWCPRD01.log"
slow-query-log=1
slow_query_log_file="MOR-DWCPRD01-slow.log"
long_query_time=10

# Binary Logging.
# log-bin

# Error Logging.
log-error="MOR-DWCPRD01.err"

# Server Id.
server-id=1

# Secure File Priv.
secure-file-priv="F:/MySQL57/Uploads"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=151

query_cache_size=0

table_open_cache=2000

tmp_table_size=756M

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=2G

key_buffer_size=8M

read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=256M

innodb_buffer_pool_size=4096M
innodb_log_file_size=250M

innodb_thread_concurrency=17

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=4

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.
query_cache_type=0

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

table_definition_cache=1400

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Keyring file data path.
loose_keyring_file_data="C:/ProgramData/MySQL/MySQL Server 5.7/keyring/keyring"

innodb_write_io_threads = 16
innodb_doublewrite = off
innodb_read_io_threads = 16
innodb_log_files_in_group = 4
innodb_flush_neighbors=0
innodb_flush_method=async_unbuffered
innodb_compression_level=7
innodb_page_size=64k

Hardware and OS

Storage 3PAR 8450

64 bit Virtual Machine under VMWare
Windows Server 2012 R2 

12 GB RAM
500 GB storage space allocated from VMFS5 

Filesystem NTFS using 4096 bytes cluster size in the virtual VMDK disk file 

D:\MySQL57\bin\my_print_defaults.exe --defaults-file=example.cnf client mysql
PS D:\MySQL57\bin> fsutil fsinfo ntfsInfo d:
NTFS Volume Serial Number :       0x241467c614679996
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x0000000004ffe7ff
Total Clusters :                  0x00000000009ffcff
Free Clusters  :                  0x00000000007d7943
Total Reserved :                  0x0000000000000180
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000100000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000000002
Mft Zone Start :                  0x00000000000c0000
Mft Zone End   :                  0x00000000000cc820
Resource Manager Identifier :     D00F99AF-01C6-11E6-80FA-005056A75413

when running 

CREATE TABLE `tb1` (
   `C1` integer DEFAULT NULL,
   `C2` decimal(2,0) DEFAULT NULL,
   `C3` decimal(9,0) DEFAULT NULL,
   `C4` decimal(7,0) DEFAULT NULL,
   `C5` decimal(2,0) DEFAULT NULL,
   `C6` varchar(15) DEFAULT NULL,
   `C7` date DEFAULT NULL,
   `C8` date DEFAULT NULL,
   `C9` date DEFAULT NULL,
   `C10` decimal(2,0) DEFAULT NULL,
   `C11` decimal(2,0) DEFAULT NULL,
   `C12` decimal(2,0) DEFAULT NULL,
   `C13` varchar(1) DEFAULT NULL,
   `C14` time DEFAULT NULL,
   `C15` decimal(5,0) DEFAULT NULL,
   `C16` varchar(1) DEFAULT NULL,
   `C17` decimal(5,0) DEFAULT NULL,
   `C18` char(1) DEFAULT NULL,
   `C19` double DEFAULT NULL
 ) ENGINE=InnoDB  COMPRESSION='lz4'

138 InnoDB: Punch hole not supported by the file system or the tablespace page size is not large enough. Compression disabled	0.094 sec

Formatting with 2096 clusters

PS D:\MySQL57\bin> fsutil fsinfo ntfsInfo d:
NTFS Volume Serial Number :       0x465aceb45ace9fd3
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x0000000004ffe7ff
Total Clusters :                  0x00000000013ff9ff
Free Clusters  :                  0x00000000013403d7
Total Reserved :                  0x0000000000000300
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               2048
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x00000000000c0000
Mft Start Lcn  :                  0x0000000000180000
Mft2 Start Lcn :                  0x0000000000000004
Mft Zone Start :                  0x0000000000180000
Mft Zone End   :                  0x0000000000199020
Resource Manager Identifier :     75B90067-05C4-11E6-80FA-005056A75413

create now works

CREATE TABLE `tb1` (
   `C1` integer DEFAULT NULL,
   `C2` decimal(2,0) DEFAULT NULL,
   `C3` decimal(9,0) DEFAULT NULL,
   `C4` decimal(7,0) DEFAULT NULL,
   `C5` decimal(2,0) DEFAULT NULL,
   `C6` varchar(15) DEFAULT NULL,
   `C7` date DEFAULT NULL,
   `C8` date DEFAULT NULL,
   `C9` date DEFAULT NULL,
   `C10` decimal(2,0) DEFAULT NULL,
   `C11` decimal(2,0) DEFAULT NULL,
   `C12` decimal(2,0) DEFAULT NULL,
   `C13` varchar(1) DEFAULT NULL,
   `C14` time DEFAULT NULL,
   `C15` decimal(5,0) DEFAULT NULL,
   `C16` varchar(1) DEFAULT NULL,
   `C17` decimal(5,0) DEFAULT NULL,
   `C18` char(1) DEFAULT NULL,
   `C19` double DEFAULT NULL
 ) ENGINE=InnoDB  COMPRESSION='lz4'

0 row(s) affected	0.062 sec

but the final size for the tabel is the same

load file 361 mb data file  4.000.000 rows

final table size (ibd file)

327 Mb

I thought that this could be a result of the additional cluster index created by default by innodb, but creating additional indexes made it much bigger that i thought . Also, i wasnt able to see any additional cluster index created by default at all for the table

show status output

Innodb_buffer_pool_dump_status	Dumping of buffer pool not started
Innodb_buffer_pool_load_status	Loading of buffer pool not started
Innodb_buffer_pool_resize_status	
Innodb_buffer_pool_pages_data	5668
Innodb_buffer_pool_bytes_data	371458048
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_bytes_dirty	0
Innodb_buffer_pool_pages_flushed	5929
Innodb_buffer_pool_pages_free	10715
Innodb_buffer_pool_pages_misc	1
Innodb_buffer_pool_pages_total	16384
Innodb_buffer_pool_read_ahead_rnd	0
Innodb_buffer_pool_read_ahead	0
Innodb_buffer_pool_read_ahead_evicted	0
Innodb_buffer_pool_read_requests	12083141
Innodb_buffer_pool_reads	13
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	8061732
Innodb_data_fsyncs	414
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	917504
Innodb_data_reads	14
Innodb_data_writes	6859
Innodb_data_written	918775296
Innodb_dblwr_pages_written	0
Innodb_dblwr_writes	0
Innodb_log_waits	0
Innodb_log_write_requests	1035833
Innodb_log_writes	105
Innodb_os_log_fsyncs	124
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	530397696
Innodb_page_size	65536
Innodb_pages_created	5655
Innodb_pages_read	13
Innodb_pages_written	5751
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	0
Innodb_row_lock_time_avg	0
Innodb_row_lock_time_max	0
Innodb_row_lock_waits	0
Innodb_rows_deleted	0
Innodb_rows_inserted	4000085
Innodb_rows_read	85
Innodb_rows_updated	0
Innodb_num_open_files	7
Innodb_truncated_status_writes	0
Innodb_available_undo_logs	128
[19 Apr 2016 19:57] alexandre Alex
No additional message is reported in the error log file.
[27 Apr 2016 3:48] alexandre Alex
Tested on another machine. In this other one the warning for compression disabled shows up when trying to create a compressed table with 64k pages and 4k NTFS clusters . Installed and Enabled debug, but no trace of which method is called or any error or internal warning message is displayed

Windows 10 64bits 

fsutil fsinfo ntfsInfo c:
NTFS Volume Serial Number :        0x24fe01b0fe017b76
NTFS Version   :                   3.1
LFS Version    :                   2.0
Number Sectors :                   0x000000001d0ca7ff
Total Clusters :                   0x0000000003a194ff
Free Clusters  :                   0x000000000282fc41
Total Reserved :                   0x00000000000024f0
Bytes Per Sector  :                512
Bytes Per Physical Sector :        512
Bytes Per Cluster :                4096
Bytes Per FileRecord Segment    :  1024
Clusters Per FileRecord Segment :  0
Mft Valid Data Length :            0x000000000e4c0000
Mft Start Lcn  :                   0x00000000000c0000
Mft2 Start Lcn :                   0x0000000000000002
Mft Zone Start :                   0x0000000000c69980
Mft Zone End   :                   0x0000000000c75c00
Max Device Trim Extent Count :     512
Max Device Trim Byte Count :       0xffffffff
Max Volume Trim Extent Count :     62
Max Volume Trim Byte Count :       0x40000000
Resource Manager Identifier :     BF7EF2FE-5F3D-11E5-847A-E4361AEC2B38

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
 basedir = C:/Users/xxx/Documents/MySQL57
 datadir = C:/Users/xxx/Documents/MySQL57/data
 port = 3306
 server_id = 1

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

innodb_flush_log_at_trx_commit = 1
innodb_file_format = Barracuda
default-storage-engine = InnoDB
innodb_page_size = 65536

innodb_log_buffer_size = 16777216
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_write_ahead_size = 8192
innodb_log_file_size = 50331648
innodb_buffer_pool_size = 2048M

mysqld-debug --defaults-file=C:/Users/xxx/Documents/MySQL57/my.ini --shared-memory --console   --debug=d,info,error,query,general,where:O,C:/Users/xxx/Documents/MySQL57/mysqld.trace

debug output is in a attached file

Is there any option i could use to log the punch hole feature test?
[27 Apr 2016 3:52] alexandre Alex
trace while trying to create table and getting no hole punch support

Attachment: mysqld.trace (application/octet-stream, text), 349.68 KiB.

[6 May 2016 8:08] Umesh Shastry
Hello Alex,

Thank you for the report and test case.
Observed this with 5.7.12 build.

Thanks,
Umesh