Can I/O completion port help with database instead of File writes?

Question

I am reading up on IOCP, and from what I understand so far, the asynchronous writes only apply in the context of writing to Files. By "Files", I don't mean just disk file, but "File" type output devices on Windows.

I am planning to somehow use IOCP in implementing a server that takes messages from clients and then writes those messages asynchronously to database (either MySQL or SQLite). But, from what I understand, async writes in IOCP involves passing the data to be written to a device driver - and the very mention of "device driver" seems to rule out the possibility of using IOCP and async writes on databases, because there is no "device driver" involved in writing to databases from the application writer's point of view.

So, can IOCP actually help in implementing servers that write to database? I have a nagging feeling that I am misunderstanding something.

If IOCP can't help in this case, are there any recommendations on what I should look into for implementing a server that does async writes to database on Windows?

Answer 1

They can help anywhere when you write to a file and don't want to block while any of it happens. It's hard to imagine a database wanting that, except maybe in writing text log files whose integrity doesn't really matter.

Answer 2

IOCP can help a great deal when implementing a database.

IOCP combined with FILE_FLAG_NO_BUFFERING and FILE_FLAG_WRITE_THROUGH and correctly aligned blocks lets the database engine control cache behaviour, avoid unnecessary duplicate caching and block copies, get writes in the correct order, control which writes can be in-flight at any the same time, etc.

Of course the database needs to be implemented to use these features, with SQLite and Mysql you get something else.

For more details on how this would help with implementing a database, "Transaction Processing: Concepts and Techniques" by Gray and Reuter is an excellent reference.

Answer 3

Normally a database will give you an API that you use to write to it. This usually hides all the complex things it does. Sometimes that API might be generic and work with lots of databases, like OLE-DB and ODBC and sometimes the API might be database specific.

It's unlikely that the database you use will expose an API which is low level enough to use IOCP though it may use IOCP internally.

What you probably want to be asking is, can I use asynchronous writes to write to my database, that is, can you fire off a db write without blocking the thread that's doing the 'firing off'.

Whilst an IOCP 'friendly' database API would be nice when the rest of your server is using IOCP to communicate with its own log files and socket read/writes often the best you can get is to use a thread pool to allow you to issue the blocking calls that the database API requires without blocking the rest of the work that your server does. I tend to call this a 'business logic thread pool' and it's separate to the IOCP thread pool that I use for all my non blocking I/O.

I wrote some articles for building servers that used such a design for writing to databases and the code and links to the articles can be found here.

Answer 4

Io Completion Ports are a versatile mechanism that can be used in a number of ways to achieve scalability.

In the "best" case the Io Completion Port is associated with the OS handles that are being used with overlapped Io. But this is actually not a requirement at all: the Io Completion Port mechanism is general enough to provide scalability even if all the APIs used are blocking and/or do not expose the handles required.

In a very simple model, one can use CreateIoCompletionPort - the call PostQueuedCompletionStatus to create user defined "jobs". Create a pool of worker threads all looping on GetQueuedCompletionStatus - and simply call your blocking Io routines inside the worker threads when processing a queued job. Each time one of the worker threads blocks on any kernel function, the Io Completion Port mechanism will see that the concurrency count is low, and release another worker thread.

Obviously, used this way, the number of active worker threads can exceed the concurrency count, but, assuming the jobs are symetrical, that should resolve itself quite quickly when the thread returns to its `GetQueuedCompletionStatus' call.