Mysql – NTFS Filesystem Fragmentation issue occur after migrating thesql 5.6 database to mariadb 10.2

configurationmariadbMySQLvirtualisation

I am currently experience a novel issue with a previously fine running database server. For a second time within 8 weeks, the current running MariaDB 10.2.7 has been corrupted due to Operating System Error 665 with underlying cause being the NTFS Filesystem.The Sysinternals Utility contig should a heavily fragmented .ibd file (>1Million fragments) which leads me to believe the above is the cause of the problem.

3 months ago I switched from MySQL Server 5.6 to MariaDB 10.2.6 due to better backup features. The database runs on top of a virtual Windows 2012 R2 Machine. Previously, this setup was able to handle a much larger database without so much as a sweat and was running 24/7. The underyling hardware does not post any errors, nor do the physical harddrives show any errors.

As this has happened twice in the past 8 weeks, I am wondering if something is going wrong in MariaDB? Has anybody else experienced such heavy fragmentation of the database file? And does it have to do with the SQL Server switch? Are there any settings which I have overlooked in the MariaDB my.cnf which make it play nice with NTFS?

Thank you very much for your answers!

Edit:

The exact chronology is as follows

31st of July Write the existing 500GB Database into empty MariaDB. This was done via Parsing the old database and writing into the new via INSERT Statement. This was done as a new column for legacy "flagging" was added.
During the writing process of a minor table( one .ibd file), the error occurred for the first time. This resulted in a corrupt new database. Unfortunately, at the time I thought the operating system error 665 was due to a faulty MariaDB install.

To test this hypothesis, reinstalled MariaDB on 4th of August, recreated an empty database to write in TestData and see if the error occurred again. The database ran since then till the 22nd of September. Then the operating system error 665 was logged again and the database was corrupt.

This prompted me to look more seriously at error 665 and see if the database files had the fragmentation issue. This was the case with 1M+ fragments for now only 200GB. Unfortunately, I never checked the fragmentation issue before as it had been a non-issue. The write operations involve uuid of incoming packages from multiple sources to verify origin. Additionally, the data is mainly Blob and timestamp information.

Now I am sitting on two corrupted MariaDBs. In recovery mode, the SHOW TABLE Status shows errors whereas SHOW CREATE TABLE gives an error on execution.

The my.cnf is below

[mysqld]
datadir=D:\Database\Data\
port=3306
max_allowed_packet=64M
max_connections=251
innodb_buffer_pool_size=6G

[client]
port=3306
plugin-dir=C:/Program Files/MAriaDB 10.2/lib/plugin

The machine has 8 Vcores and rund on 16GB RAM. I hope this provides you with the wanted information.

The main issue is there anything that I can set in mariadb or on the NTFS Filepartition to stop this error from occurring?

Thanks for your time!

Best Answer

This is a bug, which I filed and fixed, based on this question. BTW, we do have a bug system too, do not hesitate to use it directly.

Here is the bug report. https://jira.mariadb.org/browse/MDEV-13941

Basically, fragments are created because sparse file is being extended. The fix is not to create sparse files, unless user wants a table with page compression (which is an exotic feature that not many people would use). The fix will appear in the next 10.2 (i.e 10.2.10), mid-end October.

However, just the upgrade to 10.2.10 won't automatically fix the problem for existing tables. There is something else you need to do prior to installation of 10.2.10

  • Stop the server
  • Unset the "sparse flag" on .idb files. in elevated command prompt, type

    fsutil sparse setflag C:\path\to\table.ibd 0
    
  • Defragment file with contig (or other tools)