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?
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.
- raw devices deprecated in DB2, but it can use Linux block devices instead which is still quite low-level
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
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.