Hoping someone has some experience with this. I am in the middle of
attempting to migrate over to a 64bit SQL cluster, however have found a
major problem with i/o requests being painfully slow. Slower in fact than
on a 32bit system with less than half the power of the Itanium.
Below is the output from the sp_configure, I'm not seeing anything.
Server info: HP rx7620, 4x1.6GHZ Itanium2 CPU, 32GB RAM, 120GB local
disk, 500GB (multiple luns) XP1024 SAN, running secure path (autopath set
to SQR).
Any thoughts?
Thanks,
Nic
affinity mask-2147483648214748364700
affinity64 mask-2147483648214748364700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03211
max server memory (MB)4214748364721474836472147483647
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0100
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options0327676464
Nicholas,
We run systems of the this size and larger. Mainly HP as well. When we've
moved from 32 bit to 64bit we've seen large increases in IO throughput.
Average Queue Lengths drop and throughput on the SAN increases.
Do you have a base line of IO on the 32bit system that you can compare to
the 64bit?
The first thing to look at would be are there any hot spots on your disk?
Check for a high average queue length.
What SQL build are you running? The same build numbers in 32bit and 64bit
can have different issues.
It's difficult without more information about your processes but here are a
few suggestions for you to try on your config.
Set the min a min and max memory to about 28GB. More or less depending on
what else you have running.
max DOP is 1? If you don't get blocked schedulers up this or set it to 0.
max worker threads may need to be higher but that depends on the number and
size of the processes.
"Nicholas Cain" <nicholas.cain@.nospam.t-mobile.com> wrote in message
news:Xns96A646766DD6Cnicholascainnospamtm@.207.46.2 48.16...
> Hoping someone has some experience with this. I am in the middle of
> attempting to migrate over to a 64bit SQL cluster, however have found a
> major problem with i/o requests being painfully slow. Slower in fact than
> on a 32bit system with less than half the power of the Itanium.
> Below is the output from the sp_configure, I'm not seeing anything.
> Server info: HP rx7620, 4x1.6GHZ Itanium2 CPU, 32GB RAM, 120GB local
> disk, 500GB (multiple luns) XP1024 SAN, running secure path (autopath set
> to SQR).
>
> Any thoughts?
> Thanks,
> Nic
>
> affinity mask -2147483648 2147483647 0 0
> affinity64 mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 1 1
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 32767 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 0 0
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 64 64
|||I actually ran some i/o stats outside of SQL and got great performance
(over 6300 io/sec), however when running through SQL this drops off
massively to something around 600 io/sec.
I changed the DOP to 1 to see if that would improve things any (testing a
bulk insert), however it didn't do anything and I was going to change it
back.
As a comparison from the SQL standpoint, I have a 32bit system with 4GB
RAM connected to the same SAN. I'm running a bulk insert with exactly the
same parameters into a database, logically and physically layed out the
same way and it's taking 3.5 minutes to complete the insert on the 32bit
system and well over 5 minutes on the 64bit.
Just doesn't seem right to me, especially when the raw i/o figures (using
iometer) show that we have at least twice the i/o throughput on the 64bit
system.
Nic
"Danny" <someone@.nowhere.com> wrote in
news:c0KHe.9971$Bx5.7552@.trnddc09:
> Nicholas,
> We run systems of the this size and larger. Mainly HP as well. When
> we've moved from 32 bit to 64bit we've seen large increases in IO
> throughput. Average Queue Lengths drop and throughput on the SAN
> increases.
> Do you have a base line of IO on the 32bit system that you can compare
> to the 64bit?
> The first thing to look at would be are there any hot spots on your
> disk? Check for a high average queue length.
> What SQL build are you running? The same build numbers in 32bit and
> 64bit can have different issues.
> It's difficult without more information about your processes but here
> are a few suggestions for you to try on your config.
> Set the min a min and max memory to about 28GB. More or less
> depending on what else you have running.
> max DOP is 1? If you don't get blocked schedulers up this or set it
> to 0. max worker threads may need to be higher but that depends on the
> number and size of the processes.
>
Saturday, February 25, 2012
64bit i/o Performance
Labels:
64bit,
amajor,
cluster,
database,
experience,
microsoft,
migrate,
mysql,
ofattempting,
oracle,
performance,
server,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment