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
