NTFS Filesystem Fragmentation issue occur after migrating mysql 5.6 database to mariadb 10.2

Question

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!

Answer 1

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)

Answer 2

Some questions to debug the problem... You did a dump from SQL Server, then a reload onto MariaDB? Please provide the commands involved. Is the problem with only one .ibd; that is, only one table? Also, explain what order the records dumped -- in particular whether they were in PRIMARY KEY order. Please provide SHOW CREATE TABLE and SHOW TABLE STATUS on MariaDB, plus the equivalent on SQL Server.

A possible fix... As soon as you get the data loaded in MariaDB, do OPTIMIZE TABLE. This will rebuild the .ibd for that table, hopefully eliminating most fragmentation. It is very likely to eliminate the fragmentation of the data, since (I think) it copies the data over in PK order. The off-record storage (for large TEXT and BLOB), plus secondary indexes -- these are another matter. (The SHOWs will give some clues of whether theses are issues.)

"Twice in 8 weeks" -- Are you implying that the fragmentation gets worse and worse, until the OS croaks? Please explain what the main write-type operations you have on the table. (I am suspecting UUID keys are heavily involved.)

Please provide the my.cnf, though I don't expect to see anything striking there. More telling would be SHOW GLOBAL STATUS and SHOW VARIABLES and RAM size. (Too big to paste here; use post.it or something.) Preferably after the server has been running a couple of days. Don't know what will show up in those outputs.

The real solution may be to switch from Windows to *nix.