The MariaDB implementation of page_compressed tables for InnoDB uses sparse files. In the worst case, in the data file, every data page will consist of some data followed by a hole. This may be extremely inefficient in some file systems.
If the underlying storage device is thinly provisioned (can compress data on the fly), it would be good to write regular files (with sequences of NUL bytes at the end of each page_compressed block) and let the storage device take care of compressing the data.
Attachments
Issue Links
blocks
MDEV-11068Review which innodb_compression_algorithm to support in binary packages
Closed
is blocked by
MDEV-26042Atomic write capability is not detected correctly
Closed
relates to
MDEV-26192Sparse files are being created on thinly provisioned storage
The detection of sparse files currently requires read access to the block device. It would be nice to have it exposed via the Linux /sys file system, similar to the SSD detection (MDEV-17380 reads files /sys/block/*/queue/rotational). If read access is not granted, one will see the following kind of a message on startup:
mariadb-10.6.3
Unable to determine if thin provisioning is used: open("/dev/sfdv0n1"): Permission denied
In such case, thin provisioning will be assumed to be disabled, that is, sparse files will be written for page_compressed tables, as was the case before this improvement.
Marko Mäkelä
added a comment - The detection of sparse files currently requires read access to the block device. It would be nice to have it exposed via the Linux /sys file system, similar to the SSD detection ( MDEV-17380 reads files /sys/block/*/queue/rotational ). If read access is not granted, one will see the following kind of a message on startup:
mariadb-10.6.3
Unable to determine if thin provisioning is used: open("/dev/sfdv0n1"): Permission denied
In such case, thin provisioning will be assumed to be disabled, that is, sparse files will be written for page_compressed tables, as was the case before this improvement.
People
Marko Mäkelä
Marko Mäkelä
Votes:
0Vote for this issue
Watchers:
1Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":970.7999999523163,"ttfb":157.39999997615814,"pageVisibility":"visible","entityId":100846,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"5d5d597d-9a2e-4bfe-abf4-29bd4049e60c","navigationType":0,"readyForUser":1080,"redirectCount":0,"resourceLoadedEnd":1192.8999999761581,"resourceLoadedStart":163,"resourceTiming":[{"duration":391,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":554,"responseStart":0,"secureConnectionStart":0},{"duration":390.89999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":163.20000004768372,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":554.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":400.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":163.29999995231628,"connectEnd":163.29999995231628,"connectStart":163.29999995231628,"domainLookupEnd":163.29999995231628,"domainLookupStart":163.29999995231628,"fetchStart":163.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":163.29999995231628,"responseEnd":563.7000000476837,"responseStart":563.7000000476837,"secureConnectionStart":163.29999995231628},{"duration":482.8000000715256,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":163.39999997615814,"connectEnd":163.39999997615814,"connectStart":163.39999997615814,"domainLookupEnd":163.39999997615814,"domainLookupStart":163.39999997615814,"fetchStart":163.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":163.39999997615814,"responseEnd":646.2000000476837,"responseStart":646.2000000476837,"secureConnectionStart":163.39999997615814},{"duration":486.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":163.60000002384186,"connectEnd":163.60000002384186,"connectStart":163.60000002384186,"domainLookupEnd":163.60000002384186,"domainLookupStart":163.60000002384186,"fetchStart":163.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":163.60000002384186,"responseEnd":650,"responseStart":650,"secureConnectionStart":163.60000002384186},{"duration":487,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":163.70000004768372,"connectEnd":163.70000004768372,"connectStart":163.70000004768372,"domainLookupEnd":163.70000004768372,"domainLookupStart":163.70000004768372,"fetchStart":163.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":163.70000004768372,"responseEnd":650.7000000476837,"responseStart":650.7000000476837,"secureConnectionStart":163.70000004768372},{"duration":487.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":163.70000004768372,"connectEnd":163.70000004768372,"connectStart":163.70000004768372,"domainLookupEnd":163.70000004768372,"domainLookupStart":163.70000004768372,"fetchStart":163.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":163.70000004768372,"responseEnd":651.5,"responseStart":651.5,"secureConnectionStart":163.70000004768372},{"duration":488.40000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":163.79999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":652.2000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":488.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":163.89999997615814,"connectEnd":163.89999997615814,"connectStart":163.89999997615814,"domainLookupEnd":163.89999997615814,"domainLookupStart":163.89999997615814,"fetchStart":163.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":163.89999997615814,"responseEnd":652.2999999523163,"responseStart":652.2999999523163,"secureConnectionStart":163.89999997615814},{"duration":488.89999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":164.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":164.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":653,"responseStart":0,"secureConnectionStart":0},{"duration":489,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":164.20000004768372,"connectEnd":164.20000004768372,"connectStart":164.20000004768372,"domainLookupEnd":164.20000004768372,"domainLookupStart":164.20000004768372,"fetchStart":164.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":164.20000004768372,"responseEnd":653.2000000476837,"responseStart":653.2000000476837,"secureConnectionStart":164.20000004768372},{"duration":596.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":165,"connectEnd":165,"connectStart":165,"domainLookupEnd":165,"domainLookupStart":165,"fetchStart":165,"redirectEnd":0,"redirectStart":0,"requestStart":165,"responseEnd":761.2000000476837,"responseStart":761.2000000476837,"secureConnectionStart":165},{"duration":1027.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":165.10000002384186,"connectEnd":165.10000002384186,"connectStart":165.10000002384186,"domainLookupEnd":165.10000002384186,"domainLookupStart":165.10000002384186,"fetchStart":165.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":165.10000002384186,"responseEnd":1192.8999999761581,"responseStart":1192.8999999761581,"secureConnectionStart":165.10000002384186},{"duration":52.09999990463257,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":732.2000000476837,"connectEnd":732.2000000476837,"connectStart":732.2000000476837,"domainLookupEnd":732.2000000476837,"domainLookupStart":732.2000000476837,"fetchStart":732.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":732.2000000476837,"responseEnd":784.2999999523163,"responseStart":784.2999999523163,"secureConnectionStart":732.2000000476837}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":7,"responseStart":157,"responseEnd":161,"domLoading":160,"domInteractive":1214,"domContentLoadedEventStart":1214,"domContentLoadedEventEnd":1251,"domComplete":1882,"loadEventStart":1882,"loadEventEnd":1883,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1194.8999999761581},{"name":"bigPipe.sidebar-id.end","time":1195.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1195.7999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":1197},{"name":"activityTabFullyLoaded","time":1262.5}],"measures":[],"correlationId":"9f333f98ca4f13","effectiveType":"4g","downlink":9.1,"rtt":0,"serverDuration":93,"dbReadsTimeInMs":16,"dbConnsTimeInMs":24,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
MDEV-11068 Review which innodb_compression_algorithm to support in binary packages
The detection of sparse files currently requires read access to the block device. It would be nice to have it exposed via the Linux /sys file system, similar to the SSD detection (
MDEV-17380reads files /sys/block/*/queue/rotational). If read access is not granted, one will see the following kind of a message on startup:mariadb-10.6.3
Unable to determine if thin provisioning is used: open("/dev/sfdv0n1"): Permission deniedIn such case, thin provisioning will be assumed to be disabled, that is, sparse files will be written for page_compressed tables, as was the case before this improvement.