Does database systems use basic system calls of local file system?

Question

Suppose database system inserts a row in table, will it use write() system call of host file system to write that particular row to table?

Answer 1

What mysql_user wrote is probably true for all systems using files to store data as those just have to communicate with the filesystem used and it does not make sense to rewrite the "drivers" for all known filesystems just to manage them directly (and would collide with the OS drivers anyway).

But some DBMS allow the use of disks/partitions directly. It allows to skip one layer of indirection and buffering, to manage caches directly.., but loses the benefits of using files (simpler backup etc.).

It is often called raw partition

• MySQL InnoDB
• SQL Server
• Oracle used to support it but seems to be desupported - probably getting replaces by Oracle ASM which looks more like a dedicated filesystem, but in the end has similar benefits - being optimized for databases instead of universal files.
• Firebird
• raw devices deprecated in DB2, but it can use Linux block devices instead which is still quite low-level

Answer 2

I agree with the other comments. But maybe the question is "Does MySQL always issue a write() when storing a row?" The answer is no. This is because of a lot of buffering and caching. And also because the MEMORY Engine never touches disk.

InnoDB goes to a lot of pains to avoiding touching the disk. Things are held in the buffer_pool; blocks are written 'eventually'. To avoid loss of data, the 'log' is written after ever one (or a few) transaction. See the setting of innodb_flush_log_at_trx_commit.

If you have replication turned on, the writing to the binlog may or may not happen immediately. See "group commit" and other things.

"Writing" and "Flushing" -- In some situations, MySQL will write() many times without worrying about whether the OS actually hits the disk, but then uses fsync() at critical times to make sure that things are actually flushed and/or are flushed in a specific order.

Etc.

Answer 3

That is correct. You can read it here or choose to search that in code ;).

MySQL updates the files on disk with the write() system call after every SQL statement and before the client is notified about the result.

In addition to this, there are few variables that controls log flushing to disk.