0

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?

3

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
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.

1

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.

  • Does it true for every databases system in general? For low level disk operations (i.e. reading, writing), do every DBMS rely on local file system? – user3606704 Nov 26 '15 at 15:44
  • That's my best guess, as I don't know all the databases for sure... But if database is lying over the OS then it has to use the low level system calls ultimately!! – mysql_user Nov 26 '15 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.