ʻO kēia nā hoʻopiʻi aʻu i lohe ai mai kā mākou mea hoʻomohala. ʻO ka mea hoihoi loa, ʻo ia ka mea i lilo i ʻoiaʻiʻo, e hāpai ana i kahi hoʻokolokolo lōʻihi. E kamaʻilio mākou e pili ana i nā kikowaena SQL e holo ana ma VMware.
ʻOiaʻiʻo, maʻalahi ke hōʻoia i ka manaʻo o ka server hana ma hope o ka pona. E hoʻokō (ʻaʻole ma ka tempdb a ʻaʻole ma kahi waihona me ka Delayed Durability i hiki ai) ke code:
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
Ma kaʻu pākaukau he 5 kekona, a ma ka kikowaena hana he 28 kekona. No ka mea pono e kali ʻo SQL i ka hopena kino o ke komo ʻana i ka log transaction, a ke hana nei mākou i nā kālepa pōkole loa ma aneʻi. ʻO ka ʻōlelo koʻikoʻi, ua hoʻokele mākou i kahi kaʻa nui ikaika i loko o ke kaʻa o ke kūlanakauhale, a nānā mākou i ka hopu ʻia ʻana e ka poʻe lawe pizza ma nā scooter - ʻaʻole mea nui ka throughput ma ʻaneʻi, ʻo ka latency wale nō ka mea nui. A ʻaʻohe mea mālama pūnaewele, ʻaʻohe mea i ka nui o nā zeros i kona kumukūʻai, hiki ke lanakila i ka SSD kūloko ma ke ʻano o ka latency.
(i loko o nā ʻōlelo ua hoʻopunipuni wau - ua hoʻopaneʻe au i ka lōʻihi ma nā wahi ʻelua.
Papakaukau - 39 kekona, 15K tr/sec, 0.065ms /io ka huakaʻi
PROD - 360 kekona, 1600 tr/sec, 0.6ms
Ua ʻike paha wau ua wikiwiki loa ia)
Eia naʻe, i kēia hihia ke hana nei mākou i nā zeros trivial o ka hana Riemann zeta me kahi hiʻohiʻona liʻiliʻi. I ka laʻana a nā mea hoʻomohala i lawe mai iaʻu, ʻokoʻa. Ua maopopo iaʻu ua pololei lākou, a hoʻomaka e wehe i kā lākou mau kikoʻī āpau e pili ana i ka loiloi ʻoihana. I kekahi manawa ua ʻike wau hiki iaʻu ke hoʻolei loa i kā lākou code a kākau i kaʻu iho - e hōʻike ana i ka pilikia like - i ka hana ʻana e holo 3-4 mau manawa lohi:
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
GOInā maikaʻi nā mea a pau, a laila e nānā i ka primality o kahi helu 6-7-8 kekona. Ua hana kēia ma kekahi mau kikowaena. Akā ma kekahi, ua lawe ka māka i 25-40 kekona. ʻO ka mea hoihoi, ʻaʻohe kikowaena kahi e lawe ai ka hoʻokō, ʻōlelo, 14 kekona - ua hana wikiwiki a lohi paha ke code, ʻo ia hoʻi, ʻo ka pilikia, e ʻōlelo kākou, ʻeleʻele a keʻokeʻo.
He aha kaʻu i hana ai? Hoʻohana ʻia nā metric VMware. Ua maikaʻi nā mea a pau ma laila - ua nui nā kumuwaiwai, Wā mākaukau = 0, ua lawa nā mea āpau, i ka wā o ka hoʻāʻo ʻana ma nā kikowaena wikiwiki a lohi CPU = 100 ma hoʻokahi vCPU. Ua ho'āʻo wau e helu i ka helu Pi - ua hōʻike ka hōʻike i nā hopena like ma kekahi kikowaena. Ua ʻoi aku ka ikaika o ka ʻala o ka ʻeleʻele.
I koʻu hiki ʻana i ka mahiʻai DEV, hoʻomaka wau e pāʻani me nā kikowaena. Ua ʻike ʻia e hiki i ka vMotion mai ka host a host ke "ho'ōla" i kahi kikowaena, akā hiki iā ia ke hoʻohuli i kahi kikowaena "wikiwiki" i kahi "lohi". Me he mea lā ʻo ia - he pilikia kekahi o nā mea hoʻokipa ... akā ... ʻaʻole. Ua lohi kekahi mīkini virtual ma luna o ka mea hoʻokipa, e ʻōlelo ʻo A, akā ua hana wikiwiki ma luna o ka mea hoʻokipa B. A ʻo kekahi mīkini virtual, ma ke ʻano ʻē aʻe, hana wikiwiki ma A a lohi ma B! ʻO nā mīkini "wikiwiki" a me nā mīkini "lohi" e wili pinepine i ka mea hoʻokipa!
Mai ia manawa mai, ua ʻike ʻia ka ʻala o ka sulfur i ka lewa. Ma hope o nā mea a pau, ʻaʻole hiki ke hoʻopili ʻia ka pilikia i ka mīkini virtual (Windows patches, no ka laʻana) - ma hope o nā mea a pau, ua lilo ia i "wikiwiki" me vMotion. Akā ʻaʻole hiki ke hoʻopili ʻia ka pilikia i ka mea hoʻokipa - ma hope o nā mea a pau, hiki iā ia ke loaʻa nā mīkini "wikiwiki" a "lohi". Eia kekahi, ʻaʻole pili kēia i ka ukana - hiki iaʻu ke kiʻi i kahi mīkini "lohi" ma ka host, kahi ʻaʻohe mea ʻē aʻe.
No ka hopohopo, ua hoʻokuʻu au i ka Process Explorer mai Sysinternals a nānā i ka waihona SQL. Ma nā mīkini lohi, hopu koke ka laina i koʻu maka:
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
... lele
sqldk.dll!SystemThread::MakeMiniSOSThread+0xa54
KERNEL32.DLL!BaseThreadInitThunk+0x14
ntdll.dll!RtlUserThreadStart+0x21
He mea kēia. Ua kākau ʻia ka papahana:
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);
}
}
}
}Ua hōʻike ʻia kēia papahana i kahi lohi nui loa - ma nā mīkini "wikiwiki" e hōʻike ana i 16-18 miliona mau pōʻai i kēlā me kēia kekona, ʻoiai ma nā mīkini lohi e hōʻike ana i hoʻokahi a me ka hapa miliona, a i ʻole 700 tausani. ʻO ia, ʻo ka ʻokoʻa he 10-20 mau manawa (!!!). He lanakila liʻiliʻi kēia: i kēlā me kēia hihia, ʻaʻohe mea hoʻoweliweli e pili ana ma waena o Microsoft a me VMware kākoʻo i mea e hoʻohuli ai lākou i nā pua ma luna o kekahi.
A laila ua kū ka holomua - nā hoʻomaha, nā mea nui, ka hysteria viral a me ka piʻi nui o ka hana. Ua ʻōlelo pinepine au i ka pilikia kilokilo i kaʻu mau hoa hana, akā i kekahi manawa me he mea lā ʻaʻole lākou i manaʻoʻiʻo mai iaʻu - ʻo ka ʻōlelo e hoʻolōʻihi nei ʻo VMware i ke code e 10-20 mau manawa.
Ua hoʻāʻo wau e ʻeli iho iaʻu iho i ka mea e lohi nei iaʻu. I kekahi manawa me he mea lā ua loaʻa iaʻu kahi hopena - hoʻohuli a hoʻopau i nā plug Hot, hoʻololi i ka nui o ka hoʻomanaʻo a i ʻole ka helu o nā mea hana i hoʻololi pinepine i ka mīkini i "wikiwiki". Akā ʻaʻole mau loa. Akā,ʻo ka mea iʻikeʻia heʻoiaʻiʻo, ua lawa ka heleʻana a kikeke i ka huila -ʻo ia hoʻi, hoʻololi kekahi hoʻohālikelike mīkini virtual
ʻO ka hope, ʻike koke koʻu mau hoa hana ʻAmelika i ke kumu kumu.
Ua ʻokoʻa nā mea hoʻokipa i ka pinepine!
- Ma keʻano he kūlana,ʻaʻole kēia he mea nui. Akā: i ka neʻe ʻana mai kahi pūʻali 'oiwi' i kahi hoʻokipa me kahi alapine 'ʻokoʻa', pono ʻo VMware e hoʻoponopono i ka hopena GetTimePrecise.
- Ma keʻano he kūlana,ʻaʻole kēia he pilikia, keʻole kahi noi e noi ana i ka manawa kiko'ī miliona miliona mau manawa i kēlā me kēia kekona, e like me SQL server.
- Akā ʻaʻole makaʻu kēia, no ka mea ʻaʻole hana mau ka server SQL i kēia (e ʻike i ka hopena)
Akā aia kekahi mau hihia ke paʻakikī kēia rake. Eia nō naʻe, ʻae, ma ke kaomi ʻana i ka huila (ma ka hoʻololi ʻana i kekahi mea i ka hoʻonohonoho VM) ua koi au iā VMware e 'helu hou' i ka hoʻonohonoho ʻana, a ua lilo ka alapine o ka mea hoʻokipa i kēia manawa i ka 'kumu' o ka mīkini.
olelo hooholo
Ke hoʻopau ʻoe i ka virtualization o ka TSC, heluhelu i ka TSC mai loko mai o ka mīkini virtual e hoʻihoʻi i ka waiwai TSC o ka mīkini kino, a ʻaʻohe hopena o ke kākau ʻana i ka TSC mai loko o ka mīkini virtual. ʻO ka neʻe ʻana i ka mīkini virtual i kahi hoʻokipa ʻē aʻe, hoʻomaka hou mai ka mokuʻāina i hoʻokuʻu ʻia, a i ʻole ka hoʻihoʻi ʻana i kahi kiʻi kiʻi e lele mau ai ka TSC. ʻAʻole hiki i kekahi mau ʻōnaehana lawelawe malihini ke kī, a hōʻike paha i nā pilikia mālama manawa ʻē aʻe, ke pio ka TSC virtualization. I ka wā ma mua, ua ʻōlelo ʻia kēia hiʻohiʻona e hoʻomaikaʻi i ka hana o nā noi e heluhelu pinepine i ka TSC, akā ua hoʻomaikaʻi nui ʻia ka hana o ka TSC virtual i nā huahana o kēia manawa. Ua ʻōlelo ʻia ka hiʻohiʻona no ka hoʻohana ʻana i ka wā e hana ana i nā ana e koi ai i kahi kumu kikoʻī o ka manawa maoli i ka mīkini virtual.
I ka pōkole, pono ʻoe e hoʻohui i ka ʻāpana
monitor_control.virtual_rdtsc = FALSE
hopena
He nīnau paha kāu: no ke aha e kāhea pinepine ai ʻo SQL iā GetTimePrecise?
ʻAʻole iaʻu ka code source SQL server, akā ke ʻōlelo nei ka loiloi i kēia. ʻO SQL kahi ʻōnaehana hana me ka hui like ʻana, kahi e "hāʻawi" ai kēlā me kēia pae i kēlā me kēia manawa. ʻAuhea kahi maikaʻi loa e hana ai i kēia? Aia kahi kali maoli - laka a IO paha. ʻAe, akā pehea inā ke wili nei mākou i nā puka helu helu? A laila ʻo ka mea maopopo a ʻaneʻane wale nō i loko o ka unuhi ʻōlelo (ʻaʻole kēia he unuhi ʻōlelo), ma hope o ka hoʻokō ʻana i ka ʻōlelo aʻe.
ʻO ka maʻamau, ʻaʻole hoʻohana ʻia ka server SQL no ka nailing computing maʻemaʻe a ʻaʻole pilikia kēia. Akā ʻo nā puka lou e hana pū ana me nā ʻano papa ʻaina āpau (i hūnā koke ʻia) e hoʻohuli i ke code i ke kaʻina o nā ʻōlelo i hoʻokō koke ʻia.
Ma ke ala, inā ʻoe e ʻōwili i ka hana ma NATIVELY COMPILED, a laila pau ka noi ʻana i ka manawa, a piʻi ka wikiwiki i nā manawa he 10. Pehea e pili ana i ka multitasking hui? Akā no ka code i hoʻopili ʻia e pono mākou e hana i ka PREEMPTIVE MULTITASKING ma SQL.
Source: www.habr.com