Which is the bottleneck of database performance, CPU or memory?

Question

We all know that memory I/O is faster than disk I/O. Database use cache machanism to improve performance. It seems that there is no business of the CPU overhead.

I mean usually the CPU is fast enough not to be the performance bottleneck on a small local database using like Berkeley DB. Relatively faster CPU won't improve the performance of such database.

My question is that whether my upper assumption is right.

I did do some simple tests which turned out to be against to my assumption and made me confused.

For example, I made a test to do 100,000 writing/reading darabase operations, and I put a little FOR-loop before each writing operation to add some CPU overhead. What I expected was that the FOR-loop won't affect the program's total run time. But the result was on the contrary. The extra run time is exactly the same as that the FOR-loops take alone.

Does that mean when CPU writes data to like memory, it just suspends waiting for the operation to be done, no matter how fast the CPU is itself?

PS:Sorry for my inaccurate English expression. I've tried my best...

Answer 1

Yes, database operations usually have to be written to the disk before they return as successful. This is a very good thing, as a program shouldn't continue before knowing if the operation was done properly. If you would parallelize your test program, you would likely find that your loops have less impact on the runtime, as the loops could be run while other threads are waiting for their IO operations to finish.

Also, while certainly modern CPU's are the most unlike candidates for being the bottleneck in a database, this is not generally true: Depending on your application and it's DB queries, they can be extremely taxing on the CPU, leaving the disk subsystem idling while the CPU is running at 100%.