Ehe, laptop yangu yekare ine simba rakawanda kupfuura yako yekugadzira server.

Izvi ndizvo chaizvo zvichemo zvandakanzwa kubva kuvagadziri vedu. Chinhu chinonyanya kufadza ndechekuti izvi zvakazove zvechokwadi, zvichikonzera kuferefetwa kwenguva refu. Tichataura nezve SQL maseva anomhanya paVMware.

Chaizvoizvo, zviri nyore kuve nechokwadi chekuti sevha yekugadzira haina tariro kuseri kwelaptop. Ita (kwete pa tempdb uye kwete pane dhatabhesi ine Delayed Durability inogoneswa) iyo kodhi:

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

Padesktop yangu zvinotora 5 masekonzi, uye pane yekugadzira server inotora 28 masekondi. Nekuti SQL inofanirwa kumirira kupera kwemuviri kweiyo transaction log yekupinda, uye isu tiri kuita pfupi pfupi kutengeserana pano. Kutaura zvazviri, takatyaira rori hombe, ine simba mutraffic yemuguta, uye takaona sezvayakanga yabatwa nevanhu vanotakura pizza pama scooters - kubuditsa hakuna kukosha pano, chete latency yakakosha. Uye hapana chengetedzo yetiweki, zvisinei kuti mazero mangani mumutengo wayo, anogona kurova iyo SSD yemuno maererano ne latency.

(mumashoko acho zvakaratidza kuti ndainyepa - I had delayed durability in both places. Pasina kunonoka durability zvinobuda:
Desktop - 39 masekondi, 15K tr/sec, 0.065ms / io kutenderera
PROD - 360 masekondi, 1600 tr/sec, 0.6ms
Ndaifanira kunge ndaona kuti kwaikurumidza)

Nekudaro, mune ino kesi tiri kubata neasina mazero eRiemann zeta basa nemuenzaniso mudiki. Mumuenzaniso wandakaunzwa nevagadziri, zvaive zvakasiyana. Ndakanga ndine chokwadi chekuti vaive vakarurama, uye vakatanga kubvisa kubva pamuenzaniso zvese zvavo zvine chekuita nebhizinesi logic. Pane imwe nguva ndakaona kuti ndaigona kurasa kodhi yavo zvachose ndonyora yangu - iyo inoratidza dambudziko rimwechete - mukugadzira inomhanya 3-4 nguva inononoka:

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
GO

Kana zvinhu zvose zvakanaka, ipapo kuongorora primality nhamba kuchatora 6-7-8 masekondi. Izvi zvakaitika pane akati wandei maseva. Asi pane vamwe, cheki yakatora 25-40 masekondi. Sezvineiwo, pakanga pasina maseva uko kuurayiwa kwaizotora, toti, masekonzi gumi nemana - kodhi yakashanda nekukurumidza kana zvishoma nezvishoma, ndiko kuti, dambudziko raive, ngatiti, dema nechena.

Zvandaita? Yakashandisa VMware metrics. Zvese zvaive zvakanaka ipapo - pakanga paine zviwanikwa zvakawanda, Yakagadzirira nguva = 0, pakanga paine zvakakwana zvezvese, panguva yekuedzwa pamaseva anokurumidza uye anononoka CPU = 100 pane imwe vCPU. Ndakatora bvunzo kuti ndiverenge nhamba Pi - bvunzo yakaratidza zvakafanana mhedzisiro pane chero server. Kunhuwa kwemashiripiti kwakawedzera kusimba.

Pandakangosvika papurazi reDEV, ndakatanga kutamba nemaseva. Zvakazoitika kuti vMotion kubva kumugadziri kuenda kune imwe inogona "kurapa" sevha, asi inogona zvakare kushandura "inokurumidza" sevha kuita "inononoka" imwe. Zvinoita senge izvi ndizvo - mamwe mauto ane dambudziko ... asi ... kwete. Mumwe muchina chaiwo wainonoka pamugamuchiri, toti A, asi wakashanda nekukurumidza pamubati B. Uye mumwe muchina chaiwo, pane kudaro, wakashanda nekukurumidza paA uye wakadzikira paB! Michina "yekukurumidza" uye "inononoka" yaiwanzotenderera pamugamuchiri!

Kubva panguva iyoyo zvichienda mberi, makanzwa kunhuwa kwesarufa mumhepo. Mushure mezvose, dambudziko harina kuverengerwa kune chaiwo muchina (Windows zvigamba, semuenzaniso) - mushure mezvose, yakashanduka kuita "nekukurumidza" nevMotion. Asi dambudziko zvakare harina kuverengerwa kune muenzi - mushure mezvose, rinogona kunge riine ese ari maviri "anokurumidza" uye "anononoka" michina. Zvakare, izvi zvanga zvisingaenderane nemutoro - ndakakwanisa kuwana muchina "unononoka" pamugamuchiri, pakanga pasina chinhu zvachose kunze kwayo.

Nekuda kwekupererwa, ndakatangisa Process Explorer kubva kuSysinternals uye ndakatarisa iyo SQL stack. Pamichina inononoka mutsara wakabva wabata ziso rangu:

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::PxlvlReplace+0x1a20
... akasvetuka
sqldk.dll!SystemThread::MakeMiniSOSTthread+0xa54
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21

Ichi chakanga chatova chimwe chinhu. Chirongwa chakanyorwa kuti:

    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);
                }
            }
        }
    }

Ichi chirongwa chakaratidza kuderera kwakanyanya - pamichina "yekukurumidza" inoratidza 16-18 miriyoni kutenderera pasekondi, nepo pamichina inononoka inoratidza miriyoni imwe nehafu, kana 700 zviuru. Ndiko kuti, musiyano uri 10-20 nguva (!!!). Uku kwaive kwatove kukunda kudiki: chero zvakadaro, pakanga pasina kutyisidzira kwekunamatira pakati peMicrosoft neVMware rutsigiro kuitira kuti vatendeuke miseve kune mumwe nemumwe.

Ipapo kufambira mberi kwakamira - mazororo, zvinhu zvakakosha, hutachiona hwehutachiona uye kuwedzera kwakanyanya kwebasa. Ini ndaiwanzotaura nezvedambudziko remashiripiti kune vandinoshanda navo, asi dzimwe nguva zvaiita sekunge vaisatombonditenda nguva dzose - chirevo chekuti VMware inodzikisira kodhi kakapetwa ka10-20 chaive chinotyisa.

Ndakaedza kuzvichera kuti chii chaindinonotsa. Dzimwe nguva zvaiita kwandiri senge ndawana mhinduro - kubatidza nekudzima maHot plugs, kushandura huwandu hwendangariro kana huwandu hwema processor aiwanzo shandura muchina kuita "unokurumidza". Asi kwete nokusingaperi. Asi izvo zvakazoitika ndezvekuti zvakakwana kubuda uye kugogodza pavhiri - ndiko kuti, shanduko chero virtual muchina parameter

Pakupedzisira, vandaishanda navo vekuAmerica vakangoerekana vawana chikonzero.

Vagamuchiri vakasiyana mukuwanda!

• Sezvo mutemo, ichi hachisi chinhu chikuru. Asi: kana uchifamba kubva kune 'wekuzvarwa' muenzi kuenda kune ane 'yakasiyana' frequency, VMware inofanirwa kugadzirisa iyo GetTimePrecise mhedzisiro.
• Sezvo mutemo, iyi haisi dambudziko, kunze kwekunge paine chikumbiro chinokumbira nguva chaiyo mamiriyoni enguva pasekondi, seSQL server.
• Asi izvi hazvityisi, sezvo SQL server isingaite izvi nguva dzose (ona Mhedziso)

Asi pane zviitiko apo reki iyi inorova zvakanyanya. Uye zvakadaro, hongu, nekudzvanya pavhiri (nekuchinja chimwe chinhu muVM marongero) ndakamanikidza VMware kuti 'iverengezve' iyo yekumisikidza, uye kuwanda kweiyo iyezvino muiti yakave iyo 'yekuzvarwa' frequency yemuchina.

chisarudzo

www.vmware.com/files/pdf/techpaper/Timekeeping-In-VirtualMachines.pdf

Paunodzima virtualization yeTSC, kuverenga TSC kubva mukati memuchina chaiwo kunodzosa kukosha kwemuchina weTSC, uye kunyora TSC kubva mukati memuchina chaiwo hakuna mhedzisiro. Kutamisa muchina chaiwo kuenda kune mumwe muenzi, kuutangazve kubva payakamiswa, kana kudzoka kune snapshot kunoita kuti TSC isvetuke isingarambe. Mamwe masisitimu anoshanda evaenzi anotadza kutanga, kana kuratidza mamwe matambudziko ekuchengetedza nguva, kana TSC virtualization yakadzimwa. Munguva yakapfuura, iyi ficha yakambokurudzirwa kuvandudza mashandiro ezvishandiso zvinoverenga TSC kazhinji, asi kuita kweiyo TSC chaiyo yakagadziridzwa zvakanyanya muzvigadzirwa zvazvino. Iyo ficha yakakurudzirwa zvakare kuti ishandiswe kana uchiita zviyero zvinoda chaiyo sosi yenguva chaiyo mumuchina chaiwo.

Muchidimbu, unofanira kuwedzera parameter

monitor_control.virtual_rdtsc = FALSE

mhedziso

Iwe unogona kunge uine mubvunzo: nei SQL ichifonera GetTimePrecise kazhinji?

Ini handina iyo SQL server sosi kodhi, asi pfungwa inotaura izvi. SQL ingangoita sisitimu yekushandisa ine cooperative concurrency, apo shinda yega yega inofanirwa "kupa" nguva nenguva. Ndeipi nzvimbo yakanaka yekuita izvi? Iko kune kumirira kwechisikigo - kukiya kana IO. Zvakanaka, asi ko kana isu tiri kutenderera computational loops? Zvino zviri pachena uye inenge nzvimbo chete iri mumupirikiri (uyu haasi mududziri chaiye), mushure mekuita chirevo chinotevera.

Kazhinji, SQL server haishandiswe kune yakachena komputa nailing uye iri harisi dambudziko. Asi zvishwe zvinoshanda nemarudzi ese ematafura enguva pfupi (ayo anobva avharirwa) shandura kodhi yacho kuita nhevedzano yezvitaurwa zvinokurumidza kuitwa.

Nenzira, kana iwe ukaputira basa muNATIVELY COMPILED, inobva yamira kubvunza nguva, uye kukurumidza kwayo kunowedzera neka 10. Zvakadini nekubatana kwekuita zvakawanda? Asi kune natively yakaunganidzwa kodhi taifanira kuita PREEMPTIVE MULTITASKING muSQL.

Source: www.habr.com