Forum » Programiranje » [MariaDB] BTree index, ter različne oblike zapisa ID-ja z namenom pohitritev vnosa podatkov.
[MariaDB] BTree index, ter različne oblike zapisa ID-ja z namenom pohitritev vnosa podatkov.
HotBurek ::
Dobro jutro.
Evo, nov dan in seveda nov izziv.
Včeraj, ko sem opazoval hitrost vpisov novih record-ov v bazo, za posamičen process, sem opzail, da na samem začetku vpisi letijo v bazo, a se kasneje nekaj zabaše in upočasni. Kar mi je dalo misliti, da nekaj delam narobe.
Za ID uporabljam VARCHAR(18), BTree UNIQUE INDEX. Vrednosti, ki jih vpisujem, izgledajo takole:
000000-0-000000000
000070-2-000004566
000011-2-000001003
000170-3-000020603
Format je sledeč: shop_id-page_type-page_id
In mi je dalo misliti, da, če je BTree struktura na vrhu (root in navzdol) razdeljena glede na prve vrednosti iz leve proti desni, se s tem pri vnosu za posamično stran obremenjuje eno samo vejo (skoraj celotna pot od root do leaf elementa je ves čas ista).
Npr.: Za vnos 5.000 page-ov za en web site (z ID-jem 6) se generirajo sledeči ID-ji:
000006-2-000000000
000006-2-000000001
000006-2-000000002
itn. vse do
000006-2-000004999
Odebeljeni del je pot od root elementa skoraj vse do leaf-a, ki se ves čas vnosa ne spremeni. Vsi ID-ji so potem naloženi na koncu te veje.
Kot da bi, v naravi, 5.000 japk naložil na eno samo vejo. Japke bi bile na drevesu povsem neenakomerno razporejene, vsa obremenitev bi šla na eno samo vejo.
Vprašanje za prvi test je preprosto:
Ali bo nastala (časovna) razlika pri vnosi 5.000 vrstic, če:
A. Vnesem ID-je v rangu 000000000 do 000004999, fromat 000003-2-0000000001 do 000003-2-0000004999
B. Vnesem ID-je v rango 0 do 4999, format 1-3-2 do 4999-3-2
(V obeh zgornjih primerih formata je shop_id=3, url_type=2)
Za vrednost string sem v vseh primerih vnašal "test123".
Definiral sem tabelo:
V Pythonu sem definiral dva loop-a: loop i za shop_id, znotraj katerega je loop j za page_id.
ID-ja sta bila v Pythonu takole definirana:
Rezultat testa 5 x 5000:
Kot kaže, je pri malih vnosih (5 x 5.000) drugi primer počasnejši za 3 sekund.
Naslednji test je bil 3 x 30.000.
Rezultati:
Isto kot v prejšnem primeru. Drugi primer je počasnejši za 3 sekunde.
Ker je najboljši test delo z dejanskimi podatki, sem se odločil, da poženem test, kjer bom podatke zajel iz tabele pages (400.000 vnosov), in po enega vnašal v novo tabelo.
Zraven sem dodal še tretji test C. Namreč, premišljeval sem, kaj bi se zgodilo, če bi ID-je namesto v desetiški obliki vpisoval v šestnajstiški?
Prvo, ID string bi bil krajši (9 9999 9999 (DEC, dolžine 9) postane 3B9A C9FF (HEX, dolžine 8)), kar pomeni, da bi pot od root-a do leaf-a lahko bila krajša. . Ali je to res ali ne, ter, ali je to dobro ali ne za pihitritev INSERT-ov, pa ne vem.
A tu se ni ustavilo. HEX sem želel razširiti, da bi, namesto zgolj šestih črk, uporabil vseh 25 (angleška abeceda, ASCII). A kako spisat takšno funkcijo? Well, šel sem iskat na internete, kako je napisan DEC to HEX funkcija, da poizkusim naredi rewrite.
Pa ni bilo potrebno. Takšna funkcija že obstaja, imenuje se CONV.
Dokumentacija: https://mariadb.com/kb/en/conv/
Primer:
Se pravi, tak INDEX bi imel v root elementu (in elementih blizu root-a) več različnih vrednosti in posledično bi bil index že v samem začetku bolj razvejan:
Ugibam, da bo več različnih vrednosti pohitrilo INSERT-e.
Okolje za test sem pripravil tako, da sem zgeneriral DDL od izvorne tabele in insert procedure, spremenil imena, in kreiral tri tabele in tri procedure za teste A, B in C.
V vsaki od procedur sem popravil, kako se zgenerira ID:
Ostalo (razen imen tabel test1, test2 in test3) je vse isto.
Pri prvem testu sem naredil vnos 30.000 vrstic.
Rezultati:
Spet isto, kot pri ostalih dveh testih: A je najhitrejši, B in C pa sta za nekaj (tokrat 15) sekund počasnejša.
Sploh mi ni jasno, zakaj je B test počasnejši od testa A? V insert proceduri mora A dvakrat izvesti ukaz LPAD, B tega nima. Ostalo je isto.
Za naslednji test sem zgeneriral še eno tabelo, test4. Prav tako sem spremenil vse tri procedure, tako da sedaj vse vpisujejo v to tabelo (prej vsak v svojo).
Vse tri procese za uvoz sem pognal istočasno in kmalu ugotovil, da testa B in C ne bo možno poganjati istočasno, saj je prišlo do konflikta v PRIMARY KEY-u; B je za shop_id=10 vnesel 10, C pa za shop_id=36 prav tako 10.
Process C je krešnil. Razlog je v resnici bil v napaki v procedure, ker sem v tabeli povečal stolpec iz VARCHAR(18) na VARCHAR(20), v proceduri pa ne. A to, samo po sebi, ni vplivalo na končni rezultat.
Testa A in B sta šla pa do konca.
Rezultat:
Evo, to pa so že bolj pravi rezultati. A rabi 12 minut, B pa 8 minut. Opazil sem tudi, da sta oba processa do nekje 15.000 vnosov približno isto hitra, potem pa se A upočasni.
Ampak, je nekaj pomembnega. Da ne pride do prekrivanje (če bi, bi se izvedel samo UPDATE ne pa INSERT), sem vsakega od testov omejil na svoj subset glede na shop_id.
Eni shop-i imajo več page-ov, drugi manj, zato razlike v številu ID-jev, da se jih na koncu nabere vsaj 30.000.
Test je bilo potrebno pognat 3x, in vsakič rotirat WHERE pool, da se je izločilo še to.
Ter, da omogočim istočasno poganjanje testa B in C, sem v proceduri vsakemu od ID-jev (sedaj VARCHAR(20)) na koncu dodal; -a, -b oz. -c.
Rezultati testov, katere sem 3x pognal (ter vsakič zarotiral pool) so pokazali, da hitrost vnosa NI odvisna glede na obliko in dolžino ID-ja, ampak glede na podatke, ki jih dobi iz določenega pool-a.
Iz podatkov v bazi sem ugotovil, da imata pool 1 in 3 glede na shop_id nekaj primerov, kjer je page-ov po 10.000 ali več za isti shop_id, pool 2 pa nima takšne koncentacije.
Prav tako sem že opazil, da je začetni vnos za nov shop_id vedno hiter, potem pa se nad 10.000 upočasni.
Tu pa sem začel gledat v proceduro. Tam se page_id generira takole:
Na tabeli pages je za vsak stolpec (shop_id in url_type) po en INDEX:
In sedaj vprašanje: Kaj bi se zgodilo s hitrostjo vnosov, če bi dodal compound/composite INDEX, ki bi vključeval oba stolpca?
Takole sem dodal index:
Ter 2x pognal test A.
Rezultati testa A, enkat brez, drugič z INDEX-om:
Pohitritev z compund INDEX-om je x2. Gut.
V bazi sem našel shop_id, ki vsebuje preko 50.000 page-ov. In tega sem uporabil za naslednji test.
Tako sem v naslednjem testu vnesel 50.000 page-ov, kateri pripadajo samo enemu shop_id-ju.
Rezultat:
V tem primeru, ko je v tabeli samo en shop_id, se čas z/brez compund INDEX-a ne spremeni.
Za naslednji test sem izbral 9 shop_id-jev, ki imajo najmanj page-ov (skupno manj kot 1000), ter enega ki jih ima največ. Za vse skupaj sem ponovno dal limit na 50.000.
S tem bo več različnih vrednosti za shop_id (v primerjavi s prejšnim testom), hkrati pa bo test vnesel približno 49.000 page-ov za en sam shop_id.
Rezultat:
Ne glede na to, da je bil v drugem primeru dodan compund INDEX, je bil čas izvajanja v obeh primerih praktično isti.
Še enkrat sem preveril, da delam prav, ter ponovno pognal oba testa. Isti rezultat. What the heck?
Za naslednji test sem naredil preprosto LIMIT 100.000, kar pomeni različni shop_id-ji, eni z več, drugi z manj page-i.
Rezultati:
Spet, ne glede na to, ali je compund INDEX ali ga ni, je čas izvajanja isti.
Zaključek.
Well, enkrat je potrebno z zadevo zaključit.
Najprej je pri testu z 30.000 MIX in compund INDEX-om delalo 2x hitreje, kot takrat, ko tega INDEX-a tam ni bilo. A potem se pri testu z 50.00 MIX in 100.000 MIX ta razlika ni več pokazala.
Sedajle sem še enkrat pognal test 30.000 MIX in obakrat (z ali brez compound INDEX-om) dobil isti rezultat (7 mnitu). I am lost.
Ne glede na vse, bom na tabelo pages dodal compund INDEX, ter v prihodnje opazoval, če bo hitrost vpisovanja bolj konstantna tudi takrat, kadar je več kot 10.000 page-ov per shop_id.
Vsa ugibanja oz. predvidevanja o pohitritvi so se podrla. No, sem pa odkril novo funkcijo CONV.
Za danes bo dovolj. Rethink and move one.
Evo, nov dan in seveda nov izziv.
Včeraj, ko sem opazoval hitrost vpisov novih record-ov v bazo, za posamičen process, sem opzail, da na samem začetku vpisi letijo v bazo, a se kasneje nekaj zabaše in upočasni. Kar mi je dalo misliti, da nekaj delam narobe.
Za ID uporabljam VARCHAR(18), BTree UNIQUE INDEX. Vrednosti, ki jih vpisujem, izgledajo takole:
000000-0-000000000
000070-2-000004566
000011-2-000001003
000170-3-000020603
Format je sledeč: shop_id-page_type-page_id
In mi je dalo misliti, da, če je BTree struktura na vrhu (root in navzdol) razdeljena glede na prve vrednosti iz leve proti desni, se s tem pri vnosu za posamično stran obremenjuje eno samo vejo (skoraj celotna pot od root do leaf elementa je ves čas ista).
Npr.: Za vnos 5.000 page-ov za en web site (z ID-jem 6) se generirajo sledeči ID-ji:
000006-2-000000000
000006-2-000000001
000006-2-000000002
itn. vse do
000006-2-000004999
Odebeljeni del je pot od root elementa skoraj vse do leaf-a, ki se ves čas vnosa ne spremeni. Vsi ID-ji so potem naloženi na koncu te veje.
Kot da bi, v naravi, 5.000 japk naložil na eno samo vejo. Japke bi bile na drevesu povsem neenakomerno razporejene, vsa obremenitev bi šla na eno samo vejo.
Vprašanje za prvi test je preprosto:
Ali bo nastala (časovna) razlika pri vnosi 5.000 vrstic, če:
A. Vnesem ID-je v rangu 000000000 do 000004999, fromat 000003-2-0000000001 do 000003-2-0000004999
B. Vnesem ID-je v rango 0 do 4999, format 1-3-2 do 4999-3-2
(V obeh zgornjih primerih formata je shop_id=3, url_type=2)
Za vrednost string sem v vseh primerih vnašal "test123".
Definiral sem tabelo:
CREATE TABLE `test1` ( `id` VARCHAR(18) CHARACTER SET ASCII NOT NULL, `string` VARCHAR(7) NOT NULL, UNIQUE KEY `test1_id_IDX` (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
V Pythonu sem definiral dva loop-a: loop i za shop_id, znotraj katerega je loop j za page_id.
ID-ja sta bila v Pythonu takole definirana:
# A shop_id = str(i).zfill(6); page_id = str(j).zfill(9); id_string = shop_id + "-2-" + page_id; # B shop_id = str(i); page_id = str(j); id_string = page_id + "-" + shop_id + "-2";
Rezultat testa 5 x 5000:
# A 000003-2-000000001 start_time 2022-10-20 10:53:35.982772 end_time 2022-10-20 10:56:15.718701 diff_time 0:02:39.735929 # B 1-3-2 start_time 2022-10-20 11:08:57.527334 end_time 2022-10-20 11:11:39.562629 diff_time 0:02:42.035295
Kot kaže, je pri malih vnosih (5 x 5.000) drugi primer počasnejši za 3 sekund.
Naslednji test je bil 3 x 30.000.
Rezultati:
# A 000003-2-000000001 start_time 2022-10-20 11:14:43.218543 end_time 2022-10-20 11:24:14.381100 diff_time 0:09:31.162557 # B 1-3-2 start_time 2022-10-20 11:29:27.509630 end_time 2022-10-20 11:39:00.787253 diff_time 0:09:33.277623
Isto kot v prejšnem primeru. Drugi primer je počasnejši za 3 sekunde.
Ker je najboljši test delo z dejanskimi podatki, sem se odločil, da poženem test, kjer bom podatke zajel iz tabele pages (400.000 vnosov), in po enega vnašal v novo tabelo.
Zraven sem dodal še tretji test C. Namreč, premišljeval sem, kaj bi se zgodilo, če bi ID-je namesto v desetiški obliki vpisoval v šestnajstiški?
Prvo, ID string bi bil krajši (9 9999 9999 (DEC, dolžine 9) postane 3B9A C9FF (HEX, dolžine 8)), kar pomeni, da bi pot od root-a do leaf-a lahko bila krajša. . Ali je to res ali ne, ter, ali je to dobro ali ne za pihitritev INSERT-ov, pa ne vem.
A tu se ni ustavilo. HEX sem želel razširiti, da bi, namesto zgolj šestih črk, uporabil vseh 25 (angleška abeceda, ASCII). A kako spisat takšno funkcijo? Well, šel sem iskat na internete, kako je napisan DEC to HEX funkcija, da poizkusim naredi rewrite.
Pa ni bilo potrebno. Takšna funkcija že obstaja, imenuje se CONV.
Dokumentacija: https://mariadb.com/kb/en/conv/
Primer:
SELECT CONV('35', 10, 36); # Z
SELECT CONV('36', 10, 36); # 10Se pravi, tak INDEX bi imel v root elementu (in elementih blizu root-a) več različnih vrednosti in posledično bi bil index že v samem začetku bolj razvejan:
A = 0-9 = 10 B = 0-9a-f = 16 C = 0-9a-z = 36
Ugibam, da bo več različnih vrednosti pohitrilo INSERT-e.
Okolje za test sem pripravil tako, da sem zgeneriral DDL od izvorne tabele in insert procedure, spremenil imena, in kreiral tri tabele in tri procedure za teste A, B in C.
V vsaki od procedur sem popravil, kako se zgenerira ID:
# A SET @id = CONCAT(LPAD(p_shop_id, 6, '0'), '-2-', LPAD(@page_id, 9, '0')) # B SET @id = CONCAT(@page_id, '-', p_shop_id, '-2'); # C SET @id = CONCAT(CONV(@page_id, 10, 36), '-', CONV(p_shop_id, 10, 36), '-2');
Ostalo (razen imen tabel test1, test2 in test3) je vse isto.
Pri prvem testu sem naredil vnos 30.000 vrstic.
Rezultati:
# A 000003-2-000000001 start_time 2022-10-20 12:47:44.897252 end_time 2022-10-20 12:55:22.888233 diff_time 0:07:37.990981 # B 1-3-2 start_time 2022-10-20 13:04:05.781169 end_time 2022-10-20 13:11:59.860463 diff_time 0:07:54.079294 # C CONV start_time 2022-10-20 13:20:19.254594 end_time 2022-10-20 13:28:10.332940 diff_time 0:07:51.078346
Spet isto, kot pri ostalih dveh testih: A je najhitrejši, B in C pa sta za nekaj (tokrat 15) sekund počasnejša.
Sploh mi ni jasno, zakaj je B test počasnejši od testa A? V insert proceduri mora A dvakrat izvesti ukaz LPAD, B tega nima. Ostalo je isto.
Za naslednji test sem zgeneriral še eno tabelo, test4. Prav tako sem spremenil vse tri procedure, tako da sedaj vse vpisujejo v to tabelo (prej vsak v svojo).
Vse tri procese za uvoz sem pognal istočasno in kmalu ugotovil, da testa B in C ne bo možno poganjati istočasno, saj je prišlo do konflikta v PRIMARY KEY-u; B je za shop_id=10 vnesel 10, C pa za shop_id=36 prav tako 10.
Process C je krešnil. Razlog je v resnici bil v napaki v procedure, ker sem v tabeli povečal stolpec iz VARCHAR(18) na VARCHAR(20), v proceduri pa ne. A to, samo po sebi, ni vplivalo na končni rezultat.
Testa A in B sta šla pa do konca.
Rezultat:
# A 000003-2-000000001 start_time 2022-10-20 13:49:41.332314 end_time 2022-10-20 14:02:11.818795 diff_time 0:12:30.486481 # B 1-3-2 start_time 2022-10-20 13:49:41.813071 end_time 2022-10-20 13:58:15.226765 diff_time 0:08:33.413694
Evo, to pa so že bolj pravi rezultati. A rabi 12 minut, B pa 8 minut. Opazil sem tudi, da sta oba processa do nekje 15.000 vnosov približno isto hitra, potem pa se A upočasni.
Ampak, je nekaj pomembnega. Da ne pride do prekrivanje (če bi, bi se izvedel samo UPDATE ne pa INSERT), sem vsakega od testov omejil na svoj subset glede na shop_id.
# A sp_string = "SELECT `shop_id`, `url` FROM `pages` WHERE `shop_id` IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) LIMIT 30000;"; # B sp_string = "SELECT `shop_id`, `url` FROM `pages` WHERE `shop_id` IN (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33) LIMIT 30000;"; # C sp_string = "SELECT `shop_id`, `url` FROM `pages` WHERE `shop_id` IN (34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49) LIMIT 30000;";
Eni shop-i imajo več page-ov, drugi manj, zato razlike v številu ID-jev, da se jih na koncu nabere vsaj 30.000.
Test je bilo potrebno pognat 3x, in vsakič rotirat WHERE pool, da se je izločilo še to.
Ter, da omogočim istočasno poganjanje testa B in C, sem v proceduri vsakemu od ID-jev (sedaj VARCHAR(20)) na koncu dodal; -a, -b oz. -c.
Rezultati testov, katere sem 3x pognal (ter vsakič zarotiral pool) so pokazali, da hitrost vnosa NI odvisna glede na obliko in dolžino ID-ja, ampak glede na podatke, ki jih dobi iz določenega pool-a.
# ~15 minut pool 1 (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) # ~10 minut pool 2 (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33) # ~15 minut pool 3 (34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49)
Iz podatkov v bazi sem ugotovil, da imata pool 1 in 3 glede na shop_id nekaj primerov, kjer je page-ov po 10.000 ali več za isti shop_id, pool 2 pa nima takšne koncentacije.
Prav tako sem že opazil, da je začetni vnos za nov shop_id vedno hiter, potem pa se nad 10.000 upočasni.
Tu pa sem začel gledat v proceduro. Tam se page_id generira takole:
# get current max page id SELECT MAX(`page_id`) AS 'page_id' INTO @page_id FROM `pages` WHERE `shop_id` = p_shop_id AND `url_type` = 'page';
Na tabeli pages je za vsak stolpec (shop_id in url_type) po en INDEX:
KEY `pages_shop_id_IDX` (`shop_id`) USING BTREE KEY `pages_page_id_IDX` (`page_id`) USING BTREE
In sedaj vprašanje: Kaj bi se zgodilo s hitrostjo vnosov, če bi dodal compound/composite INDEX, ki bi vključeval oba stolpca?
Takole sem dodal index:
KEY `pages_shop_id_page_id_IDX` (`shop_id`,`page_id`) USING BTREE
Ter 2x pognal test A.
Rezultati testa A, enkat brez, drugič z INDEX-om:
30.000 MIX SHOP ID # A (NO INDEX) 000003-2-000000001 start_time 2022-10-20 14:48:43.928192 end_time 2022-10-20 15:03:31.518771 diff_time 0:14:47.590579 # A (WITH INDEX) 000003-2-000000001 start_time 2022-10-20 15:32:07.129313 end_time 2022-10-20 15:40:04.837671 diff_time 0:07:57.708358
Pohitritev z compund INDEX-om je x2. Gut.
V bazi sem našel shop_id, ki vsebuje preko 50.000 page-ov. In tega sem uporabil za naslednji test.
Tako sem v naslednjem testu vnesel 50.000 page-ov, kateri pripadajo samo enemu shop_id-ju.
Rezultat:
50.000 SINGLE SHOP ID # A (NO INDEX) 000003-2-000000001 start_time 2022-10-20 16:32:59.713368 stats 1 5000 2022-10-20 16:33:47.135496 stats 2 10000 2022-10-20 16:34:57.810180 stats 3 15000 2022-10-20 16:36:28.908567 stats 4 20000 2022-10-20 16:38:16.479172 stats 5 25000 2022-10-20 16:40:23.518959 stats 6 30000 2022-10-20 16:42:53.479731 stats 7 35000 2022-10-20 16:45:41.221264 stats 8 40000 2022-10-20 16:48:45.294047 stats 9 45000 2022-10-20 16:52:10.205119 end_time 2022-10-20 16:55:53.774944 diff_time 0:22:54.061576 # A (WITH INDEX) 000003-2-000000001 start_time 2022-10-20 16:08:05.209620 stats 1 5000 2022-10-20 16:08:51.460774 stats 2 10000 2022-10-20 16:10:03.766657 stats 3 15000 2022-10-20 16:11:37.521411 stats 4 20000 2022-10-20 16:13:27.454205 stats 5 25000 2022-10-20 16:15:38.733914 stats 6 30000 2022-10-20 16:18:05.172639 stats 7 35000 2022-10-20 16:20:48.365595 stats 8 40000 2022-10-20 16:23:56.805196 stats 9 45000 2022-10-20 16:27:22.675746 end_time 2022-10-20 16:31:03.160876 diff_time 0:22:57.951256
V tem primeru, ko je v tabeli samo en shop_id, se čas z/brez compund INDEX-a ne spremeni.
Za naslednji test sem izbral 9 shop_id-jev, ki imajo najmanj page-ov (skupno manj kot 1000), ter enega ki jih ima največ. Za vse skupaj sem ponovno dal limit na 50.000.
S tem bo več različnih vrednosti za shop_id (v primerjavi s prejšnim testom), hkrati pa bo test vnesel približno 49.000 page-ov za en sam shop_id.
Rezultat:
50.000 MIX (9 small, 1 large) SHOP ID # A (NO INDEX) 000003-2-000000001 start_time 2022-10-20 17:37:50.311451 stats 1 5000 2022-10-20 17:38:37.245751 stats 2 10000 2022-10-20 17:39:45.439133 stats 3 15000 2022-10-20 17:41:14.618235 stats 4 20000 2022-10-20 17:43:03.077541 stats 5 25000 2022-10-20 17:45:11.376023 stats 6 30000 2022-10-20 17:47:39.137252 stats 7 35000 2022-10-20 17:50:24.643375 stats 8 40000 2022-10-20 17:53:28.277259 stats 9 45000 2022-10-20 17:56:50.413050 end_time 2022-10-20 18:00:38.606855 diff_time 0:22:48.295404 # A (WITH INDEX) 000003-2-000000001 start_time 2022-10-20 17:13:33.891752 stats 1 5000 2022-10-20 17:14:22.052287 stats 2 10000 2022-10-20 17:15:32.151442 stats 3 15000 2022-10-20 17:16:59.554745 stats 4 20000 2022-10-20 17:18:46.625691 stats 5 25000 2022-10-20 17:20:56.710548 stats 6 30000 2022-10-20 17:23:22.627462 stats 7 35000 2022-10-20 17:26:09.701007 stats 8 40000 2022-10-20 17:29:13.321786 stats 9 45000 2022-10-20 17:32:38.872246 end_time 2022-10-20 17:36:23.495371 diff_time 0:22:49.603619
Ne glede na to, da je bil v drugem primeru dodan compund INDEX, je bil čas izvajanja v obeh primerih praktično isti.
Še enkrat sem preveril, da delam prav, ter ponovno pognal oba testa. Isti rezultat. What the heck?
Za naslednji test sem naredil preprosto LIMIT 100.000, kar pomeni različni shop_id-ji, eni z več, drugi z manj page-i.
Rezultati:
100.000 MIX SHOP ID # A (NO INDEX) 000003-2-000000001 start_time 2022-10-20 20:28:13.777029 stats 1 5000 2022-10-20 20:29:00.660356 stats 2 10000 2022-10-20 20:30:06.167323 stats 3 15000 2022-10-20 20:31:24.458921 stats 4 20000 2022-10-20 20:32:31.407283 stats 5 25000 2022-10-20 20:34:26.818388 stats 6 30000 2022-10-20 20:36:07.629854 stats 7 35000 2022-10-20 20:38:01.622203 stats 8 40000 2022-10-20 20:39:21.291319 stats 9 45000 2022-10-20 20:40:38.266345 stats 10 50000 2022-10-20 20:41:21.170020 stats 11 55000 2022-10-20 20:42:35.076880 stats 12 60000 2022-10-20 20:43:59.757656 stats 13 65000 2022-10-20 20:45:24.868018 stats 14 70000 2022-10-20 20:46:53.772028 stats 15 75000 2022-10-20 20:48:24.066853 stats 16 80000 2022-10-20 20:49:45.009390 stats 17 85000 2022-10-20 20:52:19.042243 stats 18 90000 2022-10-20 20:55:36.578944 stats 19 95000 2022-10-20 20:57:50.808827 end_time 2022-10-20 21:00:52.777662 diff_time 0:32:39.000633 # A (WITH INDEX) 000003-2-000000001 start_time 2022-10-20 19:47:42.827517 stats 1 5000 2022-10-20 19:48:29.344135 stats 2 10000 2022-10-20 19:49:34.141812 stats 3 15000 2022-10-20 19:50:53.764649 stats 4 20000 2022-10-20 19:52:00.527132 stats 5 25000 2022-10-20 19:53:56.138813 stats 6 30000 2022-10-20 19:55:37.078928 stats 7 35000 2022-10-20 19:57:31.881249 stats 8 40000 2022-10-20 19:58:53.608714 stats 9 45000 2022-10-20 20:00:16.746128 stats 10 50000 2022-10-20 20:01:04.258477 stats 11 55000 2022-10-20 20:02:17.313870 stats 12 60000 2022-10-20 20:03:41.537328 stats 13 65000 2022-10-20 20:05:04.546323 stats 14 70000 2022-10-20 20:06:32.302113 stats 15 75000 2022-10-20 20:08:00.917976 stats 16 80000 2022-10-20 20:09:19.465354 stats 17 85000 2022-10-20 20:11:53.773104 stats 18 90000 2022-10-20 20:15:14.657142 stats 19 95000 2022-10-20 20:17:29.245210 end_time 2022-10-20 20:20:32.349788 diff_time 0:32:49.522271
Spet, ne glede na to, ali je compund INDEX ali ga ni, je čas izvajanja isti.
Zaključek.
Well, enkrat je potrebno z zadevo zaključit.
Najprej je pri testu z 30.000 MIX in compund INDEX-om delalo 2x hitreje, kot takrat, ko tega INDEX-a tam ni bilo. A potem se pri testu z 50.00 MIX in 100.000 MIX ta razlika ni več pokazala.
Sedajle sem še enkrat pognal test 30.000 MIX in obakrat (z ali brez compound INDEX-om) dobil isti rezultat (7 mnitu). I am lost.
Ne glede na vse, bom na tabelo pages dodal compund INDEX, ter v prihodnje opazoval, če bo hitrost vpisovanja bolj konstantna tudi takrat, kadar je več kot 10.000 page-ov per shop_id.
Vsa ugibanja oz. predvidevanja o pohitritvi so se podrla. No, sem pa odkril novo funkcijo CONV.
Za danes bo dovolj. Rethink and move one.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
DamijanD ::
Zakaj imaš govoreči ID ?
Drugače, če imaš samo občasno ogromen job z inserti, lahko takrat indexe odstraniš in samo šopaš v bazo (torej posamezen insert ne bo rabil posodabljati indexov). Na koncu pa nazaj postaviš indexe.
Drugače, če imaš samo občasno ogromen job z inserti, lahko takrat indexe odstraniš in samo šopaš v bazo (torej posamezen insert ne bo rabil posodabljati indexov). Na koncu pa nazaj postaviš indexe.
Zgodovina sprememb…
- spremenilo: DamijanD ()
2g00d4u ::
Kot sem že napisal v drugi temi, lahko tudi uporabi sequence za generiranje id-jev. Pred vnosom je verjetno znano število zapisov, zato se lahko rezervira toliko id-jev in se jih samo vstavi.
HotBurek ::
Dobro jutro.
Nov dan, nova dobra novica.
Našel sem, kaj je bil problem. Mankajoč (ne optimalen) index. Od kar sem popravil, je vnos, ki je prej delal po polžje, začel delati z hitrostjo, kot se gre.
Spremembe, ki sem jih naredil na tabeli "pages":
1.: Kreiral sem PRIMARY KEY na "id" stolpcu.
Pred tem je bil zgolj "navade" unique index. Kolikor se spomnim iz MSSQL, je lahko po max eni clusterd index na tabelo. In ta definira fizično razporeditev podatkov. Leaf elementi v takem index-u so dejanski podatki. Ostali, non-clustered index-i, imajo na leaf poziciji pointer na podatke (in ne podatkov samih).
Ta sprememba ni pohitrila vnosa, ampak it feels right and solid, da se na stolpec "id" postavi tak index.
2.: UNIQUE index za stolpec "url" sem spremenil iz HASH v BTree.
Ta hash index se mi je zdel obupno počasen, select je rabil po več kot 2 sekunde, da je dobil response, če obstaja določen url ali ne.
3.: Tole je drugi select v proceduri za insert.
In za ta query nisem imel optimalnega index, ker sem vključil samo dva stolpca (shop_id in url_type).
Ko sem vključil še tretji stolpec (page_id), je zadeva poletela.
Sedaj leti hitreje, kot kadarkoli prej. CPU je iz skoraj konstantnih 100% padel na 60~80%.
This is good.
Solid.
Nov dan, nova dobra novica.
Našel sem, kaj je bil problem. Mankajoč (ne optimalen) index. Od kar sem popravil, je vnos, ki je prej delal po polžje, začel delati z hitrostjo, kot se gre.
Spremembe, ki sem jih naredil na tabeli "pages":
1.: Kreiral sem PRIMARY KEY na "id" stolpcu.
ALTER TABLE `pages` ADD PRIMARY KEY(`id`);
Pred tem je bil zgolj "navade" unique index. Kolikor se spomnim iz MSSQL, je lahko po max eni clusterd index na tabelo. In ta definira fizično razporeditev podatkov. Leaf elementi v takem index-u so dejanski podatki. Ostali, non-clustered index-i, imajo na leaf poziciji pointer na podatke (in ne podatkov samih).
Ta sprememba ni pohitrila vnosa, ampak it feels right and solid, da se na stolpec "id" postavi tak index.
2.: UNIQUE index za stolpec "url" sem spremenil iz HASH v BTree.
Ta hash index se mi je zdel obupno počasen, select je rabil po več kot 2 sekunde, da je dobil response, če obstaja določen url ali ne.
3.: Tole je drugi select v proceduri za insert.
SELECT MAX(`page_id`) AS 'page_id' INTO @page_id FROM `pages` WHERE `shop_id` = p_shop_id AND `url_type` = 'page';
In za ta query nisem imel optimalnega index, ker sem vključil samo dva stolpca (shop_id in url_type).
Ko sem vključil še tretji stolpec (page_id), je zadeva poletela.
KEY `pages_shop_id_url_type_page_id_IDX` (`shop_id`,`url_type`,`page_id`) USING BTREE
Sedaj leti hitreje, kot kadarkoli prej. CPU je iz skoraj konstantnih 100% padel na 60~80%.
This is good.
Solid.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | Mariadb (InnoDB) istočasni insert v tabelo iz različnih procesov (strani: 1 2 )Oddelek: Programiranje | 7474 (3832) | 2g00d4u |
| » | Telefonska številka 3399Oddelek: Loža | 3869 (1715) | V-i-p |
| » | Dela prosti dnevi do 2030Oddelek: Programiranje | 4438 (3292) | GupeM |
| » | java / mysql / počasne poizvedbeOddelek: Programiranje | 758 (687) | BRBR |
| » | domači benchmark programOddelek: Programiranje | 1115 (969) | ruph |