THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Amdahl’s Law

For the past few weeks, I’ve been working on a diverse array of issues ranging from studying SQL Server performance on various multi-core processors, pondering the implications of many-core processors, troubleshooting SQL Server performance problems, looking at the scalability of Oracle RAC and Sybase shared-disk clusters, and so on.

 

Everywhere I turned, I bumped into the signs of Amdahl’s law, in particular Amdahl’s parallelism law. If you studied the computer architecture in school, you almost certainly have come across Amdahl’s various laws including his parallelism law below:

 

If a computation has a serial component S, a parallel component P, and there are N processors, then the maximum speedup is as follows

Speedup =

S + P

S + P / N

This law is often stated in various alternative but equivalent forms. The following is an alternative expression of Amdahl’s law in terms of the fraction of the computation that must be done serially (F):

Speedup =

1

F + (1 – F)  /  N

One of many important implications of this law is that even if the number of processors goes to infinity, the maximum speedup you may get is:

Max Speedup =

S + P

S

Therefore, if the serial component is a large fraction of the whole computation, you won’t get much speedup no matter how many processors you may have. On the other hand, if the parallel component is large, the speedup can be significant.

 

So, if your system has a shared global state, for consistency it must be accessed (updated) in a serialized fashion. This law suggests that if your workload is seriously bottlenecked on this global state, you won’t get much performance improvement by focusing your efforts on getting better processors, more processors, or improving any other hardware or software parallel components.

 

In the case of using the Intel multi-core processors, if your workload happens to bottleneck on its front-side bus, it most likely would not help to switch to faster processors. For an interesting discussion of the implications of Amdahl's law on multicore computing, I highly recommend reading this article: "Amdahl's Law in the Multicore Era".

 

And if you are designing a database application, but the design relies heavily on the shared tempdb, thus causing significant serialization on the tempdb system data structures, the scalability of your app will suffer.

 

In the case of RAC and Sybase shared-disk clusters, if your workload is such that it cause a huge amount of inter-node traffic to maintain cache coherency, your app will not scale well with more nodes in the cluster.

 

These observations are intuitive, and are easy to understand without you ever being exposed to Amdahl’s law. However, Amdahl’s law elegantly highlights the common thread to you, and is worth revisiting from time to time.

Published Tuesday, February 12, 2008 12:43 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chuck Boyce said:

smarty pants.

;-)

February 12, 2008 2:46 AM
 

jchang said:

For those trying to forecast ultimate limits to database performance, rather trying to make very difficult technical arguments, I will provide some simple advice for the majority of people who are on 4-socket systems or lower. Even for people of 4-socket quad core systems, i.e., a total of 16 cores, there is still significant headroom with existing SW. This is because whatever the big iron systems (64 sockets, 128 cores, 256 logical proc) can do today, 4 socket systems with 8-16 cores (plus core level improvements) will be able to do in 3-4 years. Of course for those of us on SQL Server, this depends on MS increasing max supported cores from 64 to 128 or better yet 256, and working out high scheduler issues that Oracle and IBM has had more experience (read pain) with. At least 8X headroom is available in OLTP type apps, and comparable in DW, based on TPC-C and TPC-H benchmarks.

It is clear we are well into the era where one cannot sit still on antiquated application architectures, relying completely on faster hardware for performance improvements. During the period when processor frequency shot up from 500MHz to 3GHz+, memory latency did not improve much, except on single socket AMD Opteron. Now we are in a period where single core performance is improving slowly, but the number of cores per socket will grow, probably doubling every 2 years, possible more if we step back to simpler in-order cores. This is where Linchi summary of Amdahl touches on.

The call to action is for MS to make parallel execution improvements in the SQL Server engine, and for ISVs to improve applications, getting rid of ancient serialized code to fully set oriented SQL. SQL Server 2000 had serious problems in parallel execution plans (PEP). Only really simple operations benefited from PEP. PEP was just as likely to cause degradation as improvements. Vast improvements with PEP were made in SQL 2005 mostly when all data is in memory. There are still a number of PEP issues when data must be fetched from disk. MS talked about alternative strategies for multi-threading disk IO at the last SQL Connections. Even MS does not have enough expertise in high performance storage to iron out these issues.

I would like to see SQL Server get a proper PEP for merge joins. In theory this should be possible, and even simple, but last I saw, SQL Server cannot do this.

A really big item would be if MS could figure out how to do PEP for writes. In complicated SQL, if triggers or constraints are involved, this might not be possible. But in some simpler situations, i.e., a data load or a full table column update with no triggers, this should be possible, considering writes are so CPU intensive.

February 12, 2008 11:56 AM
 

colin leversuch-roberts said:

very interesting post, your blog is always giving me things to think about !

February 18, 2008 5:49 AM
 

jonoo said:

Jchang....... u r a champ

you have given new dimention to my thoughts.....

March 19, 2008 2:28 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement