Liels array pret lielu DB tabulu

[Artenis]

Sveiki!

Doma ir tieši par performanci! Kā būu labāk veidot lielu array failu kurā būtu piemēŗam 100 000+ vērtības, no kuras tiktu nolasīta viena vērtība randomā vai arī datubāzes tabula ar 100 000+ ierakstu no kuras arī būtu jānolasa tikai viena vērtība randomā!?

Kāds būtu optimālākais un labākais variants kā nebremzēt lapas darbību uz lielu skaitu online lietotāju skaitu?

Paldies!

[briedis]

Kā tu iztēlojies array? Kā mainīgo ar struktūr - masīvs?

Ja tā, tad ņem vērā, ka lai kaut ko paņemtu no masīva, tas ir jāielasa atmiņā.

Bet ja ņem no db, tad var paņemt vienu vērtību pa taisno....

[Artenis]

skaidrs! Tad būs jāliek dati iekš DB.

Doma bija jā tāds paliels array, no kura tik vienu paķeram, bet nu okai!

Paldies par informāciju!

[2easy]

kamēr array vēl ir failā, vienu array item var nolasīt ar fseek(). vajag tikai lai katram itemam būtu fiksēts size (var izmantot paddingu ar space). db iekšpusē darītu to pašu, jo tabulas jau tāpat glabājas failos, tikai tur selecti ir optimizēti ar indexiem

Edited December 14, 2009 by 2easy

[2easy]

ok, lai nebūtu tikai tukša runāšana, uztaisīju simple test:

<?php
function utime() {  // izdod pašreizējo laiku: sekundes + mikrosekundes (aiz "komata")
list($sSecU, $sSec) = explode(' ', microtime());
return $sSec + $sSecU;
}

$gnTime = 0;
function timerSet() {  // no comment ;)
global $gnTime;
$gnTime = utime();
}
function timerGet() {  // same :D
global $gnTime;
return round(utime() - $gnTime, 6);
}
function timerEcho($sInfo) {  // parāda laiku ar precizitāti līdz 100 mikrosekundēm (ilgākām darbībām). lielākas precizitātes mērījumiem desmitos mikrosekunžu (vai vēl mazāk) ir jāņem vērā arī pašas funkcijas izsaukuma laiks (tb tad būtu jāaprēķina function call overhead)
printf('%s%.4f<br />', $sInfo, timerGet());
}

function dataRand() {  // izdod 10 simbolu garu stringu, kas sākas ar 1-10 vieniniekiem, atlikušo vietu aizpildot ar nullēm, piemēram, 1110000000, 1111100000
return str_pad(str_repeat(1, rand(1, 10)), 10, 0);
}
function dataLoad() {  // izveido/ielādē testa datus: 100 000 random vērtības failā un db tabulā
$hData = fopen('data.txt', 'w');
$hSql = fopen('data.sql', 'w');
fwrite($hSql, 'INSERT Data (s) VALUES (');
for ($i = 0; $i < 100000; $i++) {
	$s = dataRand();
	fwrite($hData, $s);
	fwrite($hSql, ($i ? '),(' : '') . "'" . $s . "'");
}
fwrite($hSql, ')');
fclose($hData);
fclose($hSql);

mysql_query('DROP TABLE IF EXISTS Data');
mysql_query('CREATE TABLE Data (s char(10) NOT NULL) ENGINE=MyISAM');
mysql_query(file_get_contents('data.sql'));
}
function dataLoadTimer() {  // ielādē datus un parāda, cik tas paņem laiku
timerSet();
dataLoad();
timerEcho('Datu ielāde: ');
}
function dataReadTimer() {  // file read vs db select!!! nju kurš tad ātrāks? ;)
// paņem random vērtību no faila
timerSet();
$h = fopen('data.txt', 'r');
fseek($h, 10 * rand(0, 99999));
$sValFromFile = fread($h, 10);
fclose($h);
timerEcho('File read: ');

// paņem random vērtību no db
timerSet();
$r = mysql_fetch_row(mysql_query('SELECT s FROM Data ORDER BY RAND() LIMIT 1'));
$sValFromDb = $r[0];
timerEcho('DB select: ');

// šīs vērtības atšķiras, jo katra read metode lieto savu random
echo '<br />';
echo 'Random vērtība no file: ' . $sValFromFile . '<br />';
echo 'Random vērtība no db: ' . $sValFromDb . '<br />';
}
dataLoadTimer();
dataReadTimer();

/*
tipisks vidējais rezultāts uz mana ne pārāk jaunā kompja:
Datu ielāde: 2.2089
File read: 0.0003
DB select: 0.1272

uz dual corēm, gigiem rama un labiem sata diskiem noteikti būtu vēl ātrāk ;)
*/
?>

par usage: tā kā šeit prioritāte ir read timeris, tad pēc datu ielādes var aizkomentēt dataLoadTimer(); (jo load paņem ievērojami vairāk laika nekā read) un skatīties tikai dataReadTimer();

 

secinājums: file operācija fseek() kopā ar fread() random vērtības paņemšanai izpildās apmēram 400x ātrāk nekā ekvivalenta mysql darbība!!! ymmv

 

ja Artenim read performance tiešām ir mission critical, tad iesaku nosimulēt šīs read darbības uz datiem, kas ir tuvāki reālajai situācijai, un balstoties uz attiecīgā testa rezultātiem pieņemt lēmumu par labu vienai vai otrai metodei

[2easy]

btw, ja kāds grib atkārtot šo testu, bet pie datu ielādes sastopas ar erroru "MySQL server has gone away" :D:D:D, tad vnk vajag uzlikt lielāku atļauto packet size. by default ir tikai 1MB, bet data.sql ~1.4MB

1) my.ini

max_allowed_packet=100M

2) php/sql

mysql_query('SET GLOBAL max_allowed_packet = 104857600');

[codez]

pamēģini labāk

SELECT s FROM Data LIMIT $x,1;

kur $x randomā uzģenerē PHP pusē + protamas arī indeksu.

[tas_pats]

Tas ORDER BY RAND() smagi ietekmēs ātrdarbību.

[2easy]

oo, codez, tas tiešām ir ģeniāli. labais ;)

bezmaz iebliezu vēl vienu postu ar jautājumu, vai mysqlā ir iespēja selectēt konkrētu rindu pēc numura/kārtas: tipa WHERE ROW_NUMBER = x, kur ROW_NUMBER nav tabulas lauks/kolonna, bet kkāda mysql iebūvētā fīča. taču LIMIT to atrisina! :))

 

man jau bija aizdomas, ka tas ORDER BY RAND() varētu būt lēns, bet tad es to daudz nedomājot pagrābu ar googli no http://petefreitag.com/item/466.cfm

 

tagad ar

'SELECT s FROM Data LIMIT ' . rand(0, 99999) . ',1'

ir tikai kādas 20ms (iepriekšējo 120ms vietā). taču tas anyway nevar pārsist file seek,read, kam vajag mazāk par pusi no 1ms!!! grozies kā gribi, bet db serverim tomēr ir savs overhead salīdzinājumā ar tik primitīvām file operācijām :P

 

kā par brīnumu, uzliekot index, viss palika tikai lēnāk! vidēji kkur 50ms. es jau paredzēju, ka indexam šajā gadījumā nevajadzēja dot nekādu ieguvumu, jo nav jau WHERE vai ORDER, kur kkas ir jāmeklē/jākārto. bet es tiešām nebiju gaidījis, ka tas dos pretēju efektu! manuprāt, sliktākajā gadījumā indexam vajadzēja būt neitrālam...

ALTER TABLE Data ADD INDEX (s)

man liekas tas tikai pierāda to, ka performanci vajag mērīt/salīdzināt praktiski (uz konkrētiem datiem ar konkrētām metodēm), nevis paļauties uz to, kā teorētiski vajadzētu būt :D

Edited December 14, 2009 by 2easy

[briedis]

2easy, ja tu piemet klāt pirmāro atslēgu ar id un tad atlasīt pēc id?

Ja zināms, ka id ir 1..N, tad ar kvēriju

SELECT s FROM tabula WHERE id = ' . rand(0, 99999) . '

 

Manuprāt, likt pašam s (kas kā saprotu ir teksts) indeksu nu galīgi nav jēgas, jo tiešam - netiek meklēts pēc s vērtības, vai kārtots pēc s...

 

Paprovē ar id kolonnu kā indeksu, kādi tad laiki :) (es minu, neesmu pārliecināts, ka būs ātrāk)

Edited December 14, 2009 by briedis

[2easy]

yep briedi, man jau bija ideja pamēģināt arī tādu variantu līdz codez piespēlēja savu versiju

domāju, ka mysql prot pietiekami ātri atrast LIMIT offsetu (varētu būt kkas līdzīgs file seek)

tāpēc nemaz nemēģināju cackāties ap extra kolonnu, kad jau tāpat ir tik elegants risinājums

taču lai būtu... (zemāk ir 4x izmainītās koda rindiņas un rezultāts, kas kārtējo reizi pārsteidza. omg)

fwrite($hSql, 'INSERT Data (n, s) VALUES (');
fwrite($hSql, ($i ? '),(' : '') . ($i + 1) . ",'" . $s . "'");
mysql_query('CREATE TABLE Data (n int NOT NULL, s char(10) NOT NULL, PRIMARY KEY (n)) ENGINE=MyISAM');
$r = mysql_fetch_row(mysql_query('SELECT s FROM Data WHERE n = ' . rand(1, 100000)));

/*
tipisks vidējais rezultāts (uz mana pc) šai testa konfigurācijai:
File read: 0.0004
DB select: 0.0008
*/

njaa... vispār jau pamācoši (tādā ziņā, ka idejas vajag pārbaudīt) ;)

mana hipotēze bija, ka ar key/index performancei vajadzētu būt apmēram tādai pašai kā LIMIT offset,1, bet izrādās, ka tomēr nē

 

pamēģināju arī atkal uzlikt index uz s. šoreiz tas neko negatīvi neietekmēja! :))

vispār jau loģiski, jo šeit vienīgajā SELECTā atlase ar WHERE notiek pēc blakus esošā n, nevis s

 

secinājumi:

1) meklēšana pēc primārās atslēgas ir ĻOOOTI ātra

2) key/index jālieto "pareizajām" kolonnām :D

3) file read joprojām palika nepārspēts :)) taču tikai ar "minimālu" 2x pārsvaru (salīdzinot ar iepriekšējiem ~400x vai ~60x)

4) taču pie tik neliela pārsvara un vārda tiešā nozīmē mikroskopiskiem izpildes laikiem, es dotu priekšroku mysql, jo tādas datu manipulācijas kā update,delete (kas ļoti iespējams būs jāveic) tur noteikti izdarīt ir vieglāk nekā failā ar paštaisītu datu formātu. kaut arī tabulā ir jāmenedžē papildus kolonna ar savu numerāciju, anyway tas ir tā vērts :))

 

un vēl par naming (tādā testa tabulā viss ir max vnk):

n - num/number (numurs pēc kārtas)  // lai arī šīs vērtības ir unikālas, tas tomēr ir tikai tehniskais lauks (kas normāli nav primary key), kam tiek uzturēta vērtību nepārtrauktība (pēc delete ir UPDATE Data SET n = n - 1 WHERE n > n-of-deleted-row) atšķirībā no id, kur nodzēšot kādu rindu, id secībā rodas caurums
s - str/string (prosta any text)

anyway, man šķiet, ka topica autors ir saņēmis pietiekami pamatotu atbildi :D:D:D

[2easy]

hmm, šo tomēr vajadzētu:

tātad kopsavilkumu/secinājumu kopsavilkums no iepriekšējiem postiem:

tipisks vidējais rezultāts (uz mana pc):  // metodes ir sakārtotas pēc performances. laiks ir noapaļots biežākais mērījums (laiki bieži mainās, veicot atkārtotus pieprasījumus. tāpēc šīs vērtības ir izvēlētas pēc vairākiem desmitiem F5, ievērojot pāris sekunžu intervālus, lai serveris "atpūšas" :D)
1) File read: 0.0004  // fseek($h, 10 * rand(0, 99999)); fread($h, 10);
2) DB select: 0.0008  // mysql_query('SELECT s FROM Data WHERE n = ' . rand(1, 100000))
3) DB select: 0.0200  // mysql_query('SELECT s FROM Data LIMIT ' . rand(0, 99999) . ',1')
4) DB select: 0.1200  // mysql_query('SELECT s FROM Data ORDER BY RAND() LIMIT 1')

ieteicamā metode, pēc tādiem kritērijiem kā performance un izmantošanas ērtums/vieglums: 2)

[Gints Plivna]

Ņemot vērā, ka datus anyway laikam jāglabā tabulā (nu lai formāts būtu kaut cik portējams), tad protams, ka taisīt kaut kādu lielu megastruktūru pa virsu ir laika patēriņš kodējot, potenciālas iespējas ielaist bugus, nemaz nerunājot par problēmu, kas notiek ja jāsinhronizē dati - t.i. daudzi lietotāji labo, daudzi lasa. Nu labi nezinu kādas šeit īsti bija prasības, varbūt tas nav aktuāli.

BTW, ja no tabulas vajag tikai vienu lauku pēc id, un to vajag bieži un tā performance ir kritiska :), tad var uzlikt arī kombinētu indeksu uz id, lauks, jo tādā gadījumā MySQLam pat nebūs jāiet pēc indeksa meklēt kaut ko tabulā, viss jau būs indeksā. Mazāk lasīšanas operāciju, mazāks laiks. Tā vismaz būtu jābūt teorētiski :)

 

Gints Plivna

http://datubazes.wordpress.com/

[2easy]

taisīt kaut kādu lielu megastruktūru pa virsu ir laika patēriņš kodējot, potenciālas iespējas ielaist bugus, nemaz nerunājot par problēmu, kas notiek ja jāsinhronizē dati - t.i. daudzi lietotāji labo, daudzi lasa

protams, taisīt pašam savu dbms (un vēl uz php :D) laikam nevajadzētu, kaut arī pamata darbības aizņem tikai pāris rindiņas:

select: $h = fopen('data.txt', 'r'); fseek($h, $iRowOffset); fread($h, $iRowSize); fclose($h);
insert: $h = fopen('data.txt', 'a'); fwrite($h, $sRowData); fclose($h);
update: $h = fopen('data.txt', 'r+'); fseek($h, $iRowOffset); fwrite($h, $sRowData); fclose($h);
delete: $h = fopen('data.txt', 'r+'); fseek($h, $iDelRowOffset + $iRowSize); $s = fread($h, filesize('data.txt')); fseek($h, $iDelRowOffset); fwrite($h, $s); ftruncate($h, filesize('data.txt') - $iRowSize); fclose($h);

manuprāt, diezgan vienkārši (vismaz pats vienkāršākais gadījums ir vienkāršs :D)

 

index realizācija varētu būt šāda:

index vērtības sakārto un glabā atsevišķā failā index.txt, un meklē ar binary search. turklāt meklēšanu veic neveidojot masīvu (lai viss faila saturs nebūtu lieki jāpārdzen uz atmiņu), bet uzreiz ar fseek() pārvietojas pa failu un lasa & salīdzina tikai atsevišķas vērtības. hdd galviņu dzenāt pa failu ar fseek() ir ok, jo tāpat vajag relatīvi maz darbību, lai kko atrastu: tā kā binary search algoritma sarežģītība ir log2N, tas nozīmē, ka vajag tikai max 20 pārbaudes, lai atrastu vajadzīgo vērtību starp 1 000 000 citām vērtībām (code: log(1000000, 2)), un max 17 pārbaudes priekš 100 000 vērtībām (code: log(100000, 2)). pie katras index vērtības atrodas attiecīgās rindas offsets datu failā, lai atrodot meklēto vērtību, pāris mikrosekundēs ar fseek() nokļūtu arī tur un nolasītu pašas rindas datus

 

transakciju emulēšana:

// failiem
flock($h, LOCK_SH);  // shared lock (reader)
flock($h, LOCK_EX);  // exclusive lock (writer)
flock($h, LOCK_UN);  // release lock (unlock)

// tabulām
mysql_query('LOCK TABLES Data READ');
mysql_query('LOCK TABLES Data WRITE');
mysql_query('UNLOCK TABLES');

aizdomīgi līdzīgi, ne? :D:D:D

hehe, aiz tabulām tie paši faili vien ir. tabula ir tikai abstrakcija

 

kr4 es tikai gribēju nodemonstrēt, ka ļoti vienkāršām file operācijām performance ir labāka, un kā redzams sākotnējā piemērā, nav jau nekas tur sarežģīts to uzkodēt

 

btw, ja performance ir ūber svarīga, tad jau labāk kodēt c/asm un kompilēt/asemblēt, lai serveris uzreiz izpilda machine kodu, nevis ķepājas ap kkādu php parsēšanu. taču tas jau sāk iet ārpus šī foruma tematikas... ;)

 

tad var uzlikt arī kombinētu indeksu uz id, lauks, jo tādā gadījumā MySQLam pat nebūs jāiet pēc indeksa meklēt kaut ko tabulā, viss jau būs indeksā.

pamēģināju, un šoreiz expectation (ka tas neko neietekmēs) sakrita ar rezultātu: PRIMARY KEY (n, s) neko nepaātrināja. viss tāpat kā ar PRIMARY KEY (n). anyway "pēc indeksa iet meklēt kko tabulā" manuprāt vajadzētu būt pāris mikrosekunžu jautājumam, jo tajā brīdī rindas atrašanās vietai (pozīcija, file offset) jau ir jābūt zināmai. savādāk jau no indeksa nebūtu jēgas, ja atkal kkas ir jāmeklē!!! (var jau būt, ka es kļūdos. varbūt tas ir atkarīgs no dbms realizācijas)

 

droši vien, ka no kombinētā indeksa šeit kkāds uzlabojums arī bija, taču lai to detectētu, būtu jāmodificē pašreizējais timer kods, lai varētu mērīt darbības, kas aizņem mazāk par 100 mikrosekundēm. bet tad rezultātiem īpaši nevar uzticēties, jo tos pārāk ietekmē visādi blakus faktori, turklāt rezultāti ļoti variē katrā testā reizē. tā ka tev Gint iespējams ir taisnība, tikai ar šīm timer funkcijām to nevarēja pamanīt

 

taču es kombinēto indexu (n,s) parasti izveidotu tikai tad, ja man vajadzētu izpildīt kverijus, kur abi lauki ir izmantoti meklēšanā vai kātošanā: WHERE n = 123 AND s = 'asdf' vai ORDER BY n,s

 

oo, visbeidzot ENGINE=MyISAM vietā pamēģināju arī ENGINE=MEMORY, kas izrādījās bik ātrāks

mysql_query('CREATE TABLE Data (n int NOT NULL, s char(10) NOT NULL, PRIMARY KEY (n)) ENGINE=MEMORY');

jaunais kopvērtējums:

tipisks vidējais rezultāts (uz mana pc):
1) File read: 0.0004  // fseek($h, 10 * rand(0, 99999)); fread($h, 10);
2) DB select: 0.0006  // mysql_query('SELECT s FROM Data WHERE n = ' . rand(1, 100000)) ar ENGINE=MEMORY
3) DB select: 0.0008  // mysql_query('SELECT s FROM Data WHERE n = ' . rand(1, 100000)) ar ENGINE=MyISAM
4) DB select: 0.0200  // mysql_query('SELECT s FROM Data LIMIT ' . rand(0, 99999) . ',1')
5) DB select: 0.1200  // mysql_query('SELECT s FROM Data ORDER BY RAND() LIMIT 1')