High CPU and Low Database Throughput

This post examines the reason why many relational DBMS servers show very high CPU utilization without completing much work.  The discussion applies to transactional database workloads running on Oracle, SQL Server, MySQL, and PostgreSQL.

A friend asked why his database server's CPUs were constantly maxed out while not really getting much work done.  In fact, users were complaining about the time it takes to complete simple transactions.  My friend was worried the only solution was to move the database to a bigger and more expensive server with more CPUs. 

He were particularly worried about escalating DBMS licensing fees.  Most DBMS are licensed by CPU count.  Moving to a bigger server means buying more licenses.  And once you go up, DBMS vendors are very reluctant to let you reduce licenses.  This is a common concern for commercial DBMS customers.  For open source customers it is a different problem: there is no software license to buy, but these customers don't really have a lot of money for buying newer and bigger servers.

I hear this complaint so frequently that I immediately asked, "Have you heard about spin locks?"

Recall each database process is assigned to a CPU execution thread.  Also recall that whenever a process issues an I/O request (to read from or write to storage) the process has to hold the processor and wait for a reply.  To hold it the process uses something called a spin lock.  If the process didn't do this, then it might lose its processor to the next process wanting to do work. 

Several thousand times per second the process asks the operating system "is my work done?" and the processor replies, "no, your work is not done yet."  This chatter spins the CPU thread so it appears to be 100% utilized but it isn't do any work.  It's just chatter spinning the CPU.  Imagine that: every CPU that appears to be fully utilized might not be doing anything other than saying "I am not done yet".

Now you know.  Spin locks hold onto a resource and make it spin.  You can find a much fancier definition in your favorite C programming guide.

Here's a fun example: assume a thousand database sessions on a server with dual 12-core processors with hyperthreading enabled so there are 48 total CPU threads.  Subtracting a few threads for key operating system and DBMS processes, let's say you have 44 threads to support all 1,000 sessions.  This means 23 sessions are mapped to every thread.  (Recall each database session has its own process mapped to a CPU execution thread).  At any given time this server supports 23 sessions doing work and 977 waiting for their turn.  Processes take turns locking their respective thread to request I/O and spinning it with the "are you done yet?" and "no I'm not done yet" chatter I mentioned earlier.

After explaining all of this to my friend he asks, "How do we stop using spin locks? They're killing us!"  I had to explain the locks are built into the kernel and cannot be eliminated, but there's no need to.

The solution is to help the system answer the I/O calls more quickly.  If the CPU is spinning because the processes are constantly asking if their I/O is done yet, then help the I/O finish faster.  The sooner the I/O completes, the sooner the process will stop spinning the CPU asking for status and release the CPU to the next user.

My friend's reply hinted of frustration, "The SQL is already tuned and cannot be made any faster."

The good news is the solution requires absolutely zero re-programming or application code changes.  You don't need to tune SQL.

The bad news is, it's a hardware problem and fixing it will cost money.  It won't cost you as much money as a new server with inflated licensing fees, or as much time as migrating your database to a new server or tuning SQL statements.  Still, every solution has a cost.

Spinning CPUs on transactional databases are most often caused by high-latency hard disk drive storage.  Moving OLTP database files to faster storage almost always solves the problem of spinning CPUs.

To implement the solution you first need to identify database files with high wait times.  Those are the files we want to move to storage with better latency.  For example: if you are using Oracle then generate an AWR report and look for any datafiles where metric db file sequential read is more than 1ms.  My friend's latency was around 10ms.

He tested my proposed solution on a test database identical to production which was attached to three storage arrays each providing two dozen hard disk drives (72 total disks).  We noticed that under a typical workload the latency on single block reads from storage were taking 10ms on average.  We then installed one flash storage device and moved their hottest database files to it.  Latency dropped to around 0.4 ms and the CPU spins disappeared.  In other words, the I/O were completing in just 1/25^th of the usual time so the processes didn't have to spin their CPUs asking "are you done yet".

With latency all-but eliminated the transactions were completing much faster and the CPUs were no longer spinning.  CPU utilization dropped from 98% to 70%.  (It's not a direct correlation between latency improvements and overall CPU utilization because a DBMS server does many things).

"That gives us something to think about," the friend said, "This has negated our need for a new server and we have enough free CPU cycles to meet our growth rate for years to come.  Now the problem is, we don't have flash storage for our production server."  Doh!