{"id":11476,"date":"2018-07-24T12:55:26","date_gmt":"2018-07-24T10:55:26","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-on-linux-io-internal-thoughts\/"},"modified":"2023-07-17T11:09:32","modified_gmt":"2023-07-17T09:09:32","slug":"sql-server-on-linux-io-internal-thoughts","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-on-linux-io-internal-thoughts\/","title":{"rendered":"SQL Server on Linux – I\/O internal thoughts"},"content":{"rendered":"

Let\u2019s start the story from the beginning with some funny testing I tried to perform with SQL Server on Linux a couple of months ago. At that time, I wanted to get some pictures of syscalls from SQL Server as I already did in a past on Windows side with sysinternal tools<\/a> as procmon and stack traces. On Linux strace<\/a> is probably one of the best counterparts.<\/p>\n

\"blog<\/p>\n

Firstly, please note this blog is just from my own researches and it doesn\u2019t constitute in any cases an official documentation from Microsoft and may lead likely to some inaccuracies. This kind of write up is definitely not easy especially when you’re not the direct developer of the product and because things change quickly nowadays making\u00a0at the same time\u00a0your blog post biased <\/strong>\ud83d\ude42 Anyway, I was just curious to figure out how SQL Server deals with I\/O on Linux side and the main safety point here is certainly to show how you may achieve it on Linux. So let\u2019s start from the beginning with already what we know on the Windows operating system: The SQL Server engine goes through Win32 API and functions like CreateFile()<\/em>, ReadFile()<\/em>, WriteFile()<\/em> to deal with I\/O but let\u2019s focus specifically on the CreateFile()<\/em> function here. CreateFile()<\/a> is used to create or to open an existing file or an I\/O device with some specific flags. Some of them as FILE_FLAG_WRITE_THROUGH are used to meet the Write-Ahead Logging (WAL) Protocol by bypassing all system \/ disk caches (cf. Microsoft article<\/a>).<\/p>\n

On April 10 2018 I did my first tests on the Linux side with SQL Server 2017 CU5 and here was my first strace output after creating dbi_db database:<\/p>\n

\"blog<\/p>\n

\"blog<\/p>\n

It was an expected output for me because on Linux SQL Server uses an low-level open()<\/a> system call \u2013 that is the counterpart of createfile() on Windows \u2013 but the surprising thing was with O_DIRECT flag only. I\u2019m not a system developer and from my position, I may understand benefits from using O_DIRECT with database systems because it is driving by AIO<\/a> (asynchronous I\/O) and by the fact we may completely bypass any kernel space buffers (by default on Linux). I get the opportunity to thanks @dbaffaleuf<\/a> with our interesting discussions on this topic<\/p>\n

But referring to the documentation we may read the following sample as well about O_DIRECT:<\/p>\n

File I\/O is done directly to\/from user-space buffers. The O_DIRECT flag on its own makes an effort to transfer data synchronously, but does not give the guarantees of the O_SYNC flag that data and necessary metadata are transferred<\/em><\/strong><\/p><\/blockquote>\n

From my understanding using O_DIRECT that implies durable writes on block devices are not guaranteed and from my trace I noticed the transaction log seemed to be open with O_DIRECT in this case \u2026<\/p>\n

2837  20:44:32 open(\"\/u02\/sqlserverlog\/mssqlserver\/dbi_test_log.ldf\", O_RDWR|O_CREAT|O_EXCL|O_DIRECT, 0660) = 187\n2837  10:13:09 fstat(187, {st_mode=S_IFREG|0660, st_size=0, ...}) = 0<\/pre>\n
\u2026 From a WAL protocol perspective\u00a0using\u00a0such flag\u00a0might\u00a0lead to\u00a0not meet the requirements\u00a0because we might experience data loss in case of a system \/ storage outage unless either implementing another low-level mechanism like fsync()<\/a> for transaction log and checkpoints or to be sure the storage guarantees writes are O_DIRECT safe. At this stage I expected to find out more fsync() related entries in my trace\u00a0but no chance as shown below (I put only a sample but in fact no other relevant syscalls in the entire trace that might indicate forcing synchronization stuff)<\/p>\n
% time     seconds  usecs\/call     calls    errors syscall\n------ ----------- ----------- --------- --------- ----------------\n 72.59   12.993418        2089      6220      2503 futex\n 10.78    1.929811        7509       257           epoll_wait\n  9.08    1.625657       47813        34        28 restart_syscall\n  3.28    0.587733        8643        68           io_getevents\n  1.97    0.351806       70361         5           nanosleep\n  1.92    0.344254       34425        10        10 rt_sigtimedwait\n  0.32    0.056943           3     22116           clock_gettime\n  0.02    0.003530         101        35           munmap\n  0.01    0.002149          32        67           io_submit\n  0.01    0.001706           6       273           epoll_ctl\n  0.01    0.000897           6       154           read\n  0.00    0.000765          11        68           writev\n  0.00    0.000605           4       136        68 readv\n  0.00    0.000591           4       137           write\n  0.00    0.000381          10        40        23 open\n  \u2026\n------ ----------- ----------- --------- --------- ----------------\n100.00   17.900633                 29827      2638 total<\/pre>\n
A couple of weeks ago, I wanted to update my test environment with SQL Server 2017 CU8 (on Linux) I noticed the following messages (in the red rectangle):<\/p>\n
\"blog<\/p>\n
Hmm .. that\u00a0was pretty new and interesting and in fact, the aforementioned messages\u00a0were related to this Microsoft article<\/a> (Thanks @Bobwardms<\/a> to pointed me out). This new behavior is available since SQL Server 2017 CU6 and the article\u00a0describes that how\u00a0Microsoft has introduced a change with a new “forced flush” mechanism for SQL Server on Linux system.<\/p>\n
In a nutshell for all scenarios, a new flush mechanism guarantees data is safely written to a stable media for transaction logs and during checkpoints. Let\u2019s dig further into the both methods.<\/p>\n
Let\u2019s say first\u00a0I applied the same pattern for all the tests that follow. The test\u2019s protocol included one dbo.t1<\/em> table with only one column (id int). I inserted for each test a bunch of data rows (67 rows to be more precise related to 67 distinct implicit transactions) without any other user concurrent activities. It remains some internal stuff from SQL Server but I guess we may consider them as negligible compared to my tests.<\/p>\n
insert dbo.t1 values (1)\ngo 67<\/pre>\n