I have a third-party, object-oriented database server running on a Windows Server 2003 x64 machine with 24 GB of RAM. Except for some system processes and monitoring tools, the database server is the only thing running on this machine. I has open ten ~130GB and one ~5GB database file, the latter of which is accessed most often and is the one that is giving me problems.
Whenever the database server flushes this database file, it seems to take forever to complete. Because the DBMS will discard its transaction log once the data has been written to disk, it has to wait for the flush to complete.
I have looked at the file IO in Process Monitor and saw that during normal operation prior to flushing, there are a lot of buffered writes (usually 128 Bytes, sometimes a little more) at random a offsets in the file. This is to be expected, as small pieces of data are added to and updated in the database in no predictable pattern.
When the transaction log is full and a flush occurs, there were some 8000 writes in multiples of 4K (the page size on the machine in question), totalling about 35 MB in the one instance I analyzed. The flush took about 12 seconds to complete, during which the database server hangs. Our storage people tell me that 1.5ms per write (12 seconds divided by 8000 writes) repesents the SAN's normal performance and there is nothing they can do to speed this up.
I have read up on the way file caching works in Windows Internals 4, and it mentions that the cache manager will try to write dirty pages to disk in regular intervals on a background thread even when the application does not request a flush (lazy writer). Is there any way to make it write pages to disk faster, so when the application calls
FlushFileBuffers, there are only a few dirty pages left that the application would have to wait for to be written to disk?
The book also mentions that there is just one copy of any given file in the cache manager. Does that mean I could trigger a flush by opening the same file in a separate process and regularly calling
FlushFileBuffers on it, so again there would be less to flush when the database server requests a flush?
Are there any other operating or file system parameters that I should look into?
One recommendation I heard was to increase the page size in the file system, as that would improve throughput by increasing the number of bytes written to the SAN per IO. Since the pages saved during a flush are non-consecutive and far between, I doubt this will do anything, right?