Board index » delphi » ENABLE_HYPERTHREADING - no effect

2006-01-31 06:17:54 PM
delphi109
This week-end we finally upgraded one of our customers InterBase servers to
InterBase 7.5.1 - and things have gone to hell.
The old system was running InterBase 7.1SP2 on Windows 2000 Advanced Server on a
quad Xeon 2.8GHz with HyperThreading. HT was disabled in InterBase though.
The new system is running InterBase 7.5.1 on Windows Server 2003 on the same
hardware. Since Windows Server 2003 supposedly knows how to deal with
HyperThreading, I enabled HT in InterBase.
The upgrade went as planned with no problems, but Monday morning, as the users
started using the system, the performance declined steadily with CPU at 100% and
at noon the InterBase server "finally" crashed and left several hundred scary
error messages in the log. After a bit of research I concluded that the crash
was the result of memory corruption in InterBase.
Before InterBase crashed I had studied the CPU usage pattern and I noticed that
the load didn't seem to be distributed properly (properly meaning "as I
expected"). When one CPU peaked, all the other CPU's peaked too and when one
fell, the others fell with it. This to me looks like a completely random
scheduling of threads. I'd have expected to be able to identify some thread
affinity. Anyhow, this made me decide to disable HyperThreading in InterBase to
see if it made any difference.
According to gfix the database was still sane, but with the usual 6-800 index
errors. I decided to do a backup/restore to get rid of the index errors and
clean the database of any undetected latent errors we might have carried with us
from InterBase 7.1. Ten hours later the backup/restore was completed and the
system was back up again.
Today we have seen the same pattern of declining performance and 100% CPU, but
so far InterBase has stayed airborne (still one hour to noon though...). Now,
since I disabled HyperThreading in InterBase, I'd have expected InterBase to
stay on four of the processors and the CPU load to max out at 50%, but it
doesn't - It runs 100% on all eight processors.
Is it my assumptions that are flawed or doesn't the ENABLE_HYPERTHREADING
setting actually do anything? I have previously observed that CPU_AFFINITY had
absolutely no effect on process or thread affinity, so I'd be surprised.
--
DIXI
Anders Melander
Software Developer
Denmark
 

"Anders Melander" <XXXX@XXXXX.COM>escribi?en el mensaje
some circumstances.
 

J.L.Rocha writes:
right that 999999 is what you want for an SMP system.
Also, when running a four processor server I would suggest disabling
hyper-threading in BIOS. I don't think it does you much if any good on
a SMP machine used as a server. You're already getting reduced context
switching from having multiple CPUs, so there's little reason to accept
the negatives of hyper-threading.
IMHO hyper-threading is mostly useful for single processors.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues
 

Anders Melander writes:
performance switch. I *think* ENABLE_HYPERTHREADING tells the licensing
system to discriminate between logical and physical processors as best
it can (this is trickier on OSs which don't support hyper-threading
such as Win2000). But I am not positive about this, so I chose to stay
silent about something I don't really understand. :)
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: learningtowhistle.blogspot.com
 

"J.L.Rocha" <XXXX@XXXXX.COM>writes:
multiple threads.
I wasn't sure if the MAX_THREADS work around applied to our setup, both the
thread here and the official note from Borland are pretty vague, but I
implemented it nonetheless.
--
DIXI
Anders Melander
Software Developer
Denmark
 

"Craig Stuntz [TeamB]" <XXXX@XXXXX.COM [a.k.a. acm.org]>writes:
those, and most of the results showed that we got a small performance
improvement with HT enabled. I have previously posted my results, but here they
are again (remember these are "old" IB versions):
The test performed is an inhouse OLTP type benchmark measuring "sales processed
per second" with eight concurrent clients.
Interbase 6.5
# of physical CPUs 1 2 3 4
HT disabled 0,30 0,28 0,26 0,25
HT enabled (not tested)
Interbase 7.0
# of physical CPUs 1 2 3 4
HT disabled 0,30 0,49 0,67 0,81
HT enabled 0,34 0,63 0,82 0,93
HT benefit 13% 29% 22% 15%
Interbase 7.1
# of physical CPUs 1 2 3 4
HT disabled 0,27 0,46 0,63 0,76
HT enabled 0,29 0,56 0,76 0,85
HT benefit 13% 29% 22% 15%
Notice that IB 7.0 were faster than IB 7.1.
We have also performed similar tests with 50 and 100 clients, with similar
results, but I haven't got the statistics from those.
No comments on the ENABLE_HYPERTHREADING issue?
--
DIXI
Anders Melander
Software Developer
Denmark
 

Anders Melander <XXXX@XXXXX.COM>writes:
out to be our dear old enemy: The InterBase "optimizer".
It seems InterBase 7.5.1 mangles some queries slightly different than 7.1 did.
The work arounds we had implemented for the 7.1 optimizer didn't fool the 7.5.1
optimizer.
The good news is that after fixing the worst offenders our application
screeeeeams! The customer is happy again. InterBase 7.5.1 finally seems to have
delivered the performance improvement we had hoped (prayed and begged actually)
for. Now I only hope it doesn't eat our database.
Apart from altering a few queries, I also changed USER_QUANTUM to 10000 in the
hope that it would cause the long running queries, which seemed to block short
running queries, to complete faster. I don't know if this change has made a
difference. I wish InterBase tuning wasn't such a trial and error process.
--
DIXI
Anders Melander
Software Developer
Denmark
 

Anders Melander writes:
would also expect it to make the short queries take much longer. My
understanding is that the IB thread scheduling mechanism is
cooperative, not preemptive. If you set user quantum to a high number
you allow one thread to use the CPU for a long time causing all other
threads to wait. I will interested to hear what your results are.
--
Bill Todd (TeamB)
 

"Bill Todd" <XXXX@XXXXX.COM>writes:
the USER_QUANTUM setting at all.
We've had severe problems with this scenario in all versions since 7.1 (and
probably earlier, but we didn't have proper tools to look for it back then).
Sorting by "Elapsed time" in Performance Monitor, we can see a few long running
queries being executed; Their quantum, read and fetch counters increase with
every refresh. After these there are a lot of queries making no or very little
progress. Once the long running queries have completed, the rest completes in
"no time". By "long running" I mean 10-15 minutes, up to 30 if gbak is running.
If I am able to determine if the setting has made a difference, I will let you
know.
--
DIXI
Anders Melander
Software Developer
Denmark
 

Wayne Niddery [TeamB] writes:
blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
...suggests that IB 7.5.1 and 7.1 may be different, then, in this
respect, since 7.5.1 uses spinlocks and 7.1 did not.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
blogs.teamb.com/craigstuntz/category/21.aspx
 

Anders Melander writes:
preemtive there is no need for USER_QUANTUM. Preemtive multitasking, as
I understand it, occurs when the scheduler determines when to swap a
thread out and move on to the next. It does not depend on the thread to
relinquish control before it can move to the next thread.
--
Bill Todd (TeamB)
 

Anders Melander writes:
circumstances here, however, disable HT *in the BIOS*. I have discovered the
past few days, finding several articles indicating that HT *degrades*
performance for various types of applications that definitely include
database servers. This is not an Interbase issue, e.g. MS SQL server also
degrades with HT.
Also make sure MAX_THREADS is set to 999999 instead of 1000000.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"Light is faster than sound, which is why some folks appear bright
before they speak."
 

Anders Melander writes:
queries when, etc). Evidence suggests that HT actually hurts database
servers due to contention over the L1 and L2 caches.
news.zdnet.co.uk/0,39020330,39237341,00.htm
blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Hi Anders,
I believe that your doubts are very interesting and the results of
these tests could explain better some points that, at least to me,
aren't so clear yet. If you could send details for all tests will be
fine.
Some aditional points:
"Quantum is a measure of how much work the server has to do to complete
a request.",
I would expect for decreasing values for quantum from time to time.
I have never seen increasing values.
Am I wrong ?
problems I get the same kind of problems: Bad queries and/or
"optimizer".
In the past I have suggested to include in Performance Monitor the
query plan, in order to help us and avoid a lot of work to find them.
I know that the query plan isn't in the system tables, but IMHO it
could be one of the new feature for InterBase.
--
Paulo S Palmerio
Presence Tecnologia e Aplicativos
www.presence.com.br
InterBase Distributor - Brazil
 

Paulo S Palmerio writes:
steps to walk across a field -- you count how many steps you've taken
thus far, not how many are remaining.
Anyway, if you want to see increasing quantum just run this:
SELECT
COUNT(*)
FROM
RDB$RELATION_FIELDS, RDB$RELATION_FIELDS, RDB$RELATION_FIELDS
RDB$RELATION_FIELDS
...and watch it in Performance Monitor.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
blogs.teamb.com/craigstuntz/category/21.aspx