Waɗannan su ne ainihin korafe-korafen da na ji daga masu haɓaka mu. Abu mafi ban sha'awa shi ne cewa wannan ya zama gaskiya, wanda ya haifar da dogon bincike. Za mu yi magana game da sabobin SQL waɗanda ke gudana akan VMware.
A zahiri, yana da sauƙi don tabbatar da cewa uwar garken samarwa ba ta da bege a bayan kwamfutar tafi-da-gidanka. Yi (ba a kan tempdb ba kuma ba akan bayanan bayanai tare da Jinkirin da aka kunna ba) lambar:
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
A kan tebur na yana ɗaukar daƙiƙa 5, kuma akan uwar garken yana ɗaukar daƙiƙa 28. Domin SQL dole ne ya jira ƙarshen zahiri na shigarwar log ɗin ciniki, kuma muna yin gajerun ma'amaloli anan. Kusan da magana, mun tuka babbar mota mai ƙarfi cikin zirga-zirgar birni, muna kallon yadda mutane masu isar pizza suka mamaye ta a kan babur - kayan aiki ba shi da mahimmanci a nan, latency kawai yana da mahimmanci. Kuma babu ajiyar hanyar sadarwa, komai yawan sifilin da ke cikin farashinsa, zai iya doke SSD na gida dangane da latency.
(a cikin sharhin ya nuna cewa na yi ƙarya - Na jinkirta karko a wurare biyu. Ba tare da jinkirin karko ba ya zama:
Desktop - 39 seconds, 15K tr/sec, 0.065ms / io zagaye tafiya
PROD - 360 seconds, 1600 tr/sec, 0.6ms
Ya kamata in lura cewa yana da sauri da yawa)
Koyaya, a cikin wannan yanayin muna hulɗa da sifilai marasa mahimmanci na aikin Riemann zeta tare da ƙaramin misali. A cikin misalin da masu haɓakawa suka kawo ni, ya bambanta. Na tabbata cewa sun yi gaskiya, kuma na fara cirewa daga misalin duk ƙayyadaddun su da suka shafi dabarun kasuwanci. A wani lokaci na gane cewa zan iya zubar da lambar su gaba daya kuma in rubuta kaina - wanda ke nuna matsala iri ɗaya - a cikin samarwa yana gudana sau 3-4 a hankali:
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
GOIdan komai yana da kyau, to duba primality na lamba zai ɗauki 6-7-8 seconds. Wannan ya faru a kan adadin sabobin. Amma akan wasu, cak ɗin ya ɗauki 25-40 seconds. Abin sha'awa, babu sabobin da za a yi kisa, a ce, 14 seconds - lambar ta yi aiki ko dai da sauri ko kuma a hankali, wato, matsalar ita ce, bari mu ce, baki da fari.
Me na yi? An yi amfani da ma'aunin VMware. Komai yana da kyau a can - akwai wadataccen albarkatu, Shirye-shiryen lokaci = 0, akwai isasshen komai, yayin gwaji akan sabobin sauri da jinkirin CPU = 100 akan vCPU ɗaya. Na yi gwaji don ƙididdige lambar Pi - gwajin ya nuna sakamako iri ɗaya akan kowace uwar garken. Kamshin baƙar sihiri ya ƙara ƙarfi da ƙarfi.
Da zarar na isa gonar DEV, na fara wasa tare da sabobin. Ya bayyana cewa vMotion daga mai masaukin baki zuwa mai masaukin baki na iya "warkar da" uwar garken, amma kuma yana iya juya sabar "mai sauri" zuwa "hankali". Da alama wannan shine - wasu runduna suna da matsala ... amma ... a'a. Wasu na'ura mai mahimmanci sun yi jinkiri a kan mai watsa shiri, in ji A, amma sunyi aiki da sauri a kan mai watsa shiri B. Kuma wani na'ura mai mahimmanci, akasin haka, yayi aiki da sauri akan A kuma ya rage a kan B! Dukansu injunan "sauri" da "jinkirin" sau da yawa suna yawo akan mai masaukin baki!
Tun daga wannan lokacin, akwai wani kamshin sulfur a cikin iska. Bayan haka, matsalar ba za a iya dangana ga kama-da-wane inji (Windows faci, misali) - bayan duk, shi ya juya zuwa cikin "sauri" tare da vMotion. Amma matsalar kuma ba za a iya dangana ga mai masaukin baki - bayan duk, yana iya samun duka biyu "sauri" da "slow" inji. Har ila yau, wannan ba shi da alaka da kaya - Na sami damar samun injin "jinkirin" a kan rundunar, inda babu wani abu banda shi.
Saboda rashin bege, na ƙaddamar da Process Explorer daga Sysinternals kuma na kalli tarin SQL. Akan injinan jinkirin layin layin ya kama idona:
ntoskrnl.exe!KeSynchronizeExecution+0x5bf6
ntoskrnl.exe!Ke Jira Abubuwa da yawa+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 Sauya+0x1a20
... tsallakewa
sqldk.dll!SystemThread::MakeMiniSOSThread+0xa54
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadFarawa+0x21
Wannan ya riga ya zama wani abu. An rubuta shirin:
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);
}
}
}
}Wannan shirin ya nuna jinkirin da ya fi dacewa - akan injunan "sauri" yana nuna hawan keke miliyan 16-18 a sakan daya, yayin da a jinkirin inji yana nuna miliyan daya da rabi, ko ma 700 dubu. Wato, bambancin shine sau 10-20 (!!!). Wannan riga ƙaramar nasara ce: a kowane hali, babu barazanar makale tsakanin Microsoft da tallafin VMware don su juya kibau akan juna.
Sa'an nan kuma ci gaba ya tsaya - hutu, muhimman al'amura, ciwon ƙwayar cuta da kuma karuwar yawan aiki. Sau da yawa nakan ambata matsalar sihirin ga abokan aiki na, amma a wasu lokuta kamar ba koyaushe suke yarda da ni ba - furucin cewa VMware yana rage lambar da sau 10-20 yana da ban tsoro.
Na yi kokarin tono kaina abin da ke rage min hankali. A wasu lokuta yakan zama kamar na sami mafita - kunna da kashe Hot matosai, canza adadin ƙwaƙwalwar ajiya ko adadin na'urori masu sarrafawa sau da yawa suna juya na'urar zuwa "mai sauri". Amma ba har abada ba. Amma abin da ya zama gaskiya shi ne cewa ya isa ya fita ya buga a kan dabaran - wato, canji wani siga na injin kama-da-wane
A ƙarshe, abokan aikina na Amurka ba zato ba tsammani sun gano tushen dalilin.
Runduna sun bambanta a mitar!
- A matsayinka na mai mulki, wannan ba babban abu ba ne. Amma: lokacin ƙaura daga mai masaukin 'yan ƙasa zuwa mai watsa shiri tare da mitar 'mabambanta', VMware dole ne ya daidaita sakamakon GetTimePrecise.
- A ka'ida, wannan ba matsala ba ne, sai dai idan akwai aikace-aikacen da ke buƙatar daidai lokacin miliyoyin sau a cikin dakika, kamar SQL uwar garken.
- Amma wannan ba abin tsoro bane, tunda SQL uwar garken ba koyaushe yake yin wannan ba (duba Kammalawa)
Amma akwai lokuta idan wannan rake ya yi tsanani. Kuma duk da haka, a, ta hanyar danna kan dabaran (ta canza wani abu a cikin saitunan VM) Na tilasta VMware don 'sake ƙididdige' tsarin, kuma mitar mai watsa shiri na yanzu ya zama mitar' 'yan ƙasa' na injin.
yanke shawara
Lokacin da ka kashe kamannin TSC, karanta TSC daga cikin na'ura mai kama-da-wane zai dawo da ƙimar TSC na na'ura, kuma rubuta TSC daga cikin na'urar ba ta da wani tasiri. Ƙaura na'ura mai kama da wuta zuwa wani mai masaukin baki, ci gaba da shi daga yanayin da aka dakatar, ko komawa zuwa hoto yana sa TSC ta yi tsalle ta daina tsayawa. Wasu tsarukan aiki na baƙo sun kasa yin boot, ko kuma nuna wasu matsalolin kiyaye lokaci, lokacin da aka kashe tsarin TSC. A baya, ana ba da shawarar wannan fasalin don inganta ayyukan aikace-aikacen da ke karanta TSC akai-akai, amma aikin TSC na kama-da-wane an inganta sosai a cikin samfuran yanzu. Hakanan an ba da shawarar fasalin don amfani yayin yin ma'auni waɗanda ke buƙatar ainihin tushen ainihin lokacin a cikin injin kama-da-wane.
A takaice, kuna buƙatar ƙara siga
Monitor_control.virtual_rdtsc = FALSE
ƙarshe
Wataƙila kuna da tambaya: me yasa SQL ke kiran GetTimePrecise sau da yawa?
Ba ni da lambar tushen SQL uwar garken, amma ma'anar ta faɗi wannan. SQL kusan tsarin aiki ne tare da haɗin gwiwar haɗin gwiwa, inda kowane zaren dole ne ya “ba da ciki” lokaci zuwa lokaci. Ina ne wuri mafi kyau don yin wannan? Inda akwai jira na halitta - kulle ko IO. To, amma idan muna juya madaukai na lissafi fa? Sa'an nan a bayyane kuma kusan kawai wuri yana cikin mai fassara (wannan ba ainihin fassarar ba ne), bayan aiwatar da magana ta gaba.
Gabaɗaya, ba a amfani da uwar garken SQL don tsantsar ƙusa ƙira kuma wannan ba matsala bane. Amma madaukai waɗanda ke aiki tare da kowane nau'in tebur na wucin gadi (wanda nan take aka adana) suna juya lambar zuwa jerin bayanan da aka aiwatar da sauri.
Af, idan kun nade aikin a cikin NATVELY COMPILED, to ya daina neman lokaci, kuma saurinsa yana ƙaruwa sau 10. Game da haɗin gwiwar multitasking fa? Amma don lambar da aka haɗa ta asali dole ne mu yi PREEMPTIVE MULTITASKING a cikin SQL.
source: www.habr.com