More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Raw partitions or regular files?
As always, this answer depends on the vendor's implementation on a cooked file system for the ASE...
Performance Hit (synchronous vs asynchronous)
If on this platform, the ASE performs file system I/O synchronously then the ASE is blocked on the read/write and throughput is decreased tremendously.
The way the ASE typically works is that it will issue an I/O (read/write) and save the I/O control block and continue to do other work (on behalf of other connections). It'll periodically poll the workq's (network, I/O) and resume connections when their work has completed (I/O completed, network data xmit'd...).
Performance Hit (bcopy issue)
Assuming that the file system I/O is asynchronous (this can be done on SGI), a performance hit may be realized when bcopy'ing the data from kernel space to user space.
Cooked I/O typically (again, SGI has something called directed I/O which allows I/O to go directly to user space) has to go from disk, to kernel buffers and from kernel buffers to user space; on a read. The extra layer with the kernel buffers is inherently slow. The data is moved from kernel buffers to/from user space using bcopy(). On small operations this typically isn't that much of an issue but in a RDBMS scenario the bcopy() layer is a significant performance hit because it's done so often...
Performance Gain!
It's true, using file systems, at times you can get performance gains assuming that the ASE on your platform does the I/O asynchronously (although there's a caveat on this too... I'll cover that later on).
If your machine has sufficient memory and extra CPU capacity, you can realize some gains by having writes return immediately because they're posted to memory. Reads will gain from the anticipatory fetch algorithm employed by most O/S's.
You'll need extra memory to house the kernel buffered data and you'll need extra CPU capacity to allow bdflush() to write the dirty data out to disk... eventually... but with everything there's a cost: extra memory and free CPU cycles.
One argument is that instead of giving the O/S the extra memory (by leaving it free) to give it to the ASE and let it do its caching... but that's a different thread...
Data Integrity and Cooked File System
If the Sybase ASE is not certified to be used over a cooked file system, because of the nature of the kernel buffering (see the section above) you may face database corruption by using cooked file system anyway. The ASE thinks that it has posted its changes out to disk but in reality it has gone only to memory. If the machine halts without bdflush() having a chance to flush memory out to disk, your database may become corrupted.
Some O/S's allow cooked files to have a write through mode and it really depends if the ASE has been certified on cooked file systems. If it has, it means that when the ASE opens a device which is on a file system, it fcntl()'s the device to write-through.
When to use cooked file system?
I typically build my tempdb on cooked file system and I don't worry about data integrity because tempdb is rebuilt every time your ASE/SQL Server is rebooted.
(Continued on next question...)
