Kuwaani waa dhab ahaan cabashooyinka aan ka maqlay horumarintayada. Arrinta ugu xiisaha badan ayaa ah in tani ay noqotay run, taasoo keentay baaritaan dheer. Waxaan ka hadli doonaa server-yada SQL ee ku shaqeeya VMware.
Dhab ahaantii, way fududahay in la hubiyo in server-ka wax soo saarku uu rajo la'aan ka dambeeya laptop-ka. Fulin (ha ku jirin tempdb oo aan ku jirin kaydka xogta daahsan ee daahsan) koodka:
set nocount on
create table _t (v varchar(100))
declare @n int=300000
while @n>0 begin
insert into _t select 'What a slowpoke!'
delete from _t
set @n=@n-1
end
GO
drop table _t
On desktop-ka wuxuu qaadanayaa 5 ilbiriqsi, server-ka wax soo saarkana wuxuu qaadanayaa 28 ilbiriqsi. Sababtoo ah SQL waa inay sugto dhammaadka jireed ee gelitaanka log-ga wax kala iibsiga, waxaanan ku sameyneynaa macaamil aad u gaaban halkan. Qiyaastii, waxaan u kaxaysanay baabuur weyn oo xoog badan taraafikada magaalada, waxaanan daawannay iyadoo ay si lama filaan ah u dulmareen dadka pizza geynaya ee mootooyinka - wax soo saarka halkan muhiim maaha, daahitaanka kaliya ayaa muhiim ah. Oo ma jiraan kaydinta shabakad, iyadoon loo eegayn inta eber ee ku jirta qiimihiisu, way garaaci kartaa SSD-ga maxalliga ah marka la eego daahitaanka.
(Faallooyinka waxay ii soo baxday inaan been sheegay - waxaan dib u dhigay cimri dhererka labada meeloodba.
Desktop - 39 ilbiriqsi, 15K tr/sek, 0.065ms/io wareeg ah
PROD - 360 ilbiriqsi, 1600 tr/sek, 0.6ms
Waxay ahayd inaan ogaado inay aad u dhaqso badan tahay)
Si kastaba ha ahaatee, kiiskan waxaanu la macaamilaynaa eber yar oo ka mid ah shaqada Riemann zeta oo leh tusaale yar. Tusaalaha ay horumariyayaashu ii keeneen, way ka duwanayd. Waxaan ku qanacsanahay inay saxan yihiin, oo waxaan bilaabay inaan ka saaro tusaalaha dhammaan waxyaabaha gaarka ah ee la xidhiidha macquulnimada ganacsiga. Mararka qaarkood waxaan ogaaday inaan gabi ahaanba iska tuuri karo koodka oo aan qori karo aniga - taas oo muujinaysa isla dhibaatada - wax soo saarka waxay ku socotaa 3-4 jeer tartiib tartiib ah:
create function dbo.isPrime (@n bigint)
returns int
as
begin
if @n = 1 return 0
if @n = 2 return 1
if @n = 3 return 1
if @n % 2 = 0 return 0
declare @sq int
set @sq = sqrt(@n)+1 -- check odds up to sqrt
declare @dv int = 1
while @dv < @sq
begin
set @dv=@dv+2
if @n % @dv = 0 return 0
end
return 1
end
GO
declare @dt datetime set @dt=getdate()
select dbo.isPrime(1000000000000037)
select datediff(ms,@dt,getdate()) as ms
GOHaddii wax waliba ay fiican yihiin, markaa hubinta mudnaanta lambarka waxay qaadan doontaa 6-7-8 ilbiriqsi. Tani waxay ku dhacday dhowr server. Laakiin qaarkood, jeeggu wuxuu qaatay 25-40 ilbiriqsi. Waxa xiisaha lihi leh, ma jirin wax adeegayaal ah oo fulintu qaadanayso, dheh, 14 ilbiriqsi - koodhka ayaa si degdeg ah u shaqeeyay ama si tartiib tartiib ah u shaqaynayay, taas oo ah, dhibaatadu waxay ahayd, aynu nidhaahno, madow iyo caddaan.
Maxaan sameeyay? Qiyaasta VMware ee la adeegsaday. Wax kastaaba meeshaas way fiicnaayeen - waxaa jiray khayraad badan, Wakhti diyaarsan = 0, wax walba ayaa ku filnaa, inta lagu gudajiray imtixaanka labadaba xawaaraha iyo xawaaraha labadaba CPU = 100 hal vCPU. Waxaan qaaday imtixaan si aan u xisaabiyo lambarka Pi - imtixaanku wuxuu muujiyay natiijooyin isku mid ah server kasta. Udgoonkii sixirka madow ayaa sii xoogaystay oo sii xoogaystay.
Markii aan tagay beerta DEV, waxaan bilaabay inaan la ciyaaro server-yada. Waxaa soo baxday in vMotion laga bilaabo martigeliyaha ilaa martigeliyaha uu "daaweyn karo" server-ka, laakiin sidoo kale waxay u rogi kartaa server-ka "degdega ah" mid "gaabinaya". Waxay u egtahay sidan oo kale - martigeliyayaasha qaar ayaa dhibaato haysta... laakiin... maya. Qaar ka mid ah mishiinada farsamada ayaa si gaabis ah u ahaa martigeliyaha, dheh A, laakiin waxay si dhakhso ah uga shaqeeyeen martigeliyaha B. Iyo mashiinka kale ee farsamada, liddi ku ah, si dhakhso ah ayuu u shaqeeyay A oo hoos u dhigay B! Labada mashiin ee "dhakhso leh" iyo "gaabis" ayaa badanaa ku wareegaya martida loo yahay!
Laga bilaabo wakhtigaas, waxaa hawada ka baxay ur kala duwan oo baaruud ah. Ka dib oo dhan, dhibaatada looma aanayn karin mashiinka farsamada (Windows patches, tusaale ahaan) - ka dib oo dhan, waxay isu rogtay "dhakhso" leh vMotion. Laakiin dhibaatada sidoo kale looma nisbayn karo martida loo yahay - ka dib oo dhan, waxay yeelan kartaa labadaba mashiinnada "dhakhso leh" iyo " gaabis ah ". Sidoo kale, tani lama xiriirin culeyska - waxaan ku guuleystey inaan helo mashiinka "gaabis ah" ee martida loo yahay, halkaas oo aysan jirin wax aan ahayn.
Niyad-xumo darteed, waxaan ka bilaabay Process Explorer Sysinternals oo waxaan eegay kaydka SQL. Mashiinnada qunyar socodka ah ayaa isla markiiba xariiqdu indhahayga qabatay:
ntoskrnl.exe!KeSynchronizeExecution+0x5bf6
ntoskrnl.exe!KeWaitForMultipleObjects+0x109d
ntoskrnl.exe!KeWaitForMultipleObjects+0xb3f
ntoskrnl.exe!KeWaitForSingleObject+0x377
ntoskrnl.exe!KeQuerySystemTimePrecise+0x881 <- !!!
ntoskrnl.exe!ObDereferenceObjectDeferDelete+0x28a
ntoskrnl.exe!KeSynchronizeExecution+0x2de2
sqllang.dll!CDiagThreadSafe::Pxlvl Beddel+0x1a20
... booday
sqldk.dll!SystemThread::SameeMiniSOSThread+0xa54
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21
Tani waxay hore u ahayd wax. Barnaamijka ayaa u qornaa:
class Program
{
[DllImport("kernel32.dll")]
static extern void GetSystemTimePreciseAsFileTime(out FILE_TIME lpSystemTimeAsFileTime);
[StructLayout(LayoutKind.Sequential)]
struct FILE_TIME
{
public int ftTimeLow;
public int ftTimeHigh;
}
static void Main(string[] args)
{
for (int i = 0; i < 16; i++)
{
int counter = 0;
var stopwatch = Stopwatch.StartNew();
while (stopwatch.ElapsedMilliseconds < 1000)
{
GetSystemTimePreciseAsFileTime(out var fileTime);
counter++;
}
if (i > 0)
{
Console.WriteLine("{0}", counter);
}
}
}
}Barnaamijkani wuxuu muujiyay hoos u dhac xitaa ka sii badan - mashiinnada "dhakhso leh" waxay muujinayaan 16-18 milyan wareeg ah ilbiriqsi kasta, halka mishiinnada gaabis ah ay muujinayaan hal milyan iyo badh, ama xitaa 700 kun. Taasi waa, farqigu waa 10-20 jeer (!!!). Tani waxay horeyba u ahayd guul yar: si kastaba ha noqotee, ma jirin khatar ah in ay ku xayirmaan Microsoft iyo taageerada VMware si ay midba midka kale u rogaan.
Markaa horumarku wuu joogsaday - fasaxyada, arrimaha muhiimka ah, xanuunka fayraska iyo korodhka shaqada oo aad u kordhay. Waxaan marar badan u sheegay dhibaatada sixirka asxaabtayda, laakiin mararka qaarkood waxay u muuqatay inaysan xitaa had iyo jeer i rumaysnayn - hadalka ah in VMware ay hoos u dhigto koodka 10-20 jeer aad ayuu u bahaloobay.
Waxa aan isku dayay in aan is-xoqo waxa iga gaabiyay. Mararka qaarkood waxa ay ii muuqatay in aan xal helay - shidista iyo daminta furayaasha Kulul, beddelka xaddiga xusuusta ama tirada processor-yada inta badan waxay u beddelaan mashiinka mid "dhakhso leh". Laakiin weligeed ma aha. Laakiin waxa run noqday waa ku filan tahay in la baxo oo la garaaco giraangiraha - yacni, isbeddel mid kasta qiyaasta mashiinka dalwaddii
Ugu dambeyntii, asxaabteyda Mareykanka ayaa si lama filaan ah u helay sababta asalka ah.
Martigaliyayaasha ayaa ku kala duwanaa inta jeer!
- Sida caadiga ah, tani maahan wax weyn. Laakin: marka laga guurayo martigaliyaha 'hooyo' una guurayo martigeliyaha leh 'kala duwan', VMware waa inuu hagaajiyaa natiijada GetTimePrecise.
- Sida caadiga ah, tani dhib ma aha, ilaa uu jiro codsi codsanaya wakhtiga saxda ah malaayiin jeer ilbiriqsi kasta, sida SQL server.
- Laakiin tani maahan cabsi, maadaama SQL server uusan had iyo jeer samayn tan (eeg gabagabada)
Laakin waxaa jira kiisas marka uu raafkani si adag u dhaco. Hase yeeshee, haa, adoo ku dhejinaya giraangiraha (adiga oo beddelaya wax ku jira goobaha VM) Waxaan ku qasbay VMware inay 'dib u xisaabiyaan' qaabeynta, iyo inta jeer ee martida hadda jirta waxay noqotay 'dhalad' inta jeer ee mashiinka.
go'aanka
Marka aad damiso hab-dhaqanka TSC, akhrinta TSC ee mashiinka farsamada waxay soo celinaysaa qiimihii mashiinka jireed, iyo qorista TSC ee mashiinka farsamada wax saameyn ah kuma yeelanayso. U haajiridda mashiinka farsamada gacanta martigeliyaha kale, ka soo celinta xaaladdii la hakiyay, ama ku noqoshada sawir qaade waxay sababta TSC inuu si joogto ah u boodo. Qaar ka mid ah nidaamyada hawlgalka martida ayaa ku guul daraystay inay bootiyaan, ama muujiyaan dhibaatooyin kale oo waqti-hayn ah, marka TSC-ga ay naafo noqoto. Waagii hore, sifadan ayaa mararka qaarkood lagula taliyay in lagu wanaajiyo waxqabadka codsiyada soo akhriya TSC si joogto ah, laakiin waxqabadka TSC-ga casriga ah ayaa si aad ah loo hagaajiyay marka la eego alaabta hadda jirta. Muuqaalka ayaa sidoo kale lagu taliyay in la isticmaalo marka la samaynayo cabbirro u baahan il sax ah oo wakhtiga dhabta ah ee mashiinka farsamada.
Marka la soo koobo, waxaad u baahan tahay inaad ku darto cabbirka
Monitor_control.virtual_rdtsc = BEEN
gunaanad
Waxaa laga yaabaa inaad qabto su'aal: muxuu SQL ugu wacaa GetTimePrecise marar badan?
Ma haysto koodhka isha SQL ee server-ka, laakiin caqligu wuxuu leeyahay tan. SQL waxay ku dhowdahay nidaamka hawlgalka oo leh lacag la wadaago, halkaas oo dun kasta ay tahay inay "bixiso" waqti ka waqti. Aaway meesha ugu fiican ee tan lagu sameeyo? Halka ay jirto sugitaan dabiici ah - quful ama IO. Hagaag, laakiin maxaa dhacaya haddii aan wareegeyno wareegyada xisaabinta? Kadibna meesha muuqata oo ku dhow kaliya ayaa ku taal turjumaanka (tani runtii ma aha turjumaan), ka dib marka la fuliyo bayaan soo socda.
Guud ahaan, server-ka SQL looma isticmaalo ciddida xisaabinta saafiga ah tanina dhib maaha. Laakiin wareegyada ku shaqeeya dhammaan noocyada miisaska ku-meel-gaarka ah (kuwaas oo isla markiiba la kaydiyay) waxay u rogaan koodka taxane ah weedho si degdeg ah loo fuliyay.
Dhanka kale, haddii aad shaqada ku duubto NATVELY COMPILED, markaas way joojinaysaa inaad weydiisato wakhti, xawaaraheedana wuxuu kordhiyaa 10 jeer. Ka waran iskaashiga badan ee iskaashiga? Laakin koodka asal ahaan la soo ururiyey waxa aanu ku qasbanahay in aanu ku samayno SQL.
Source: www.habr.com