makadii
Ndakafunga kugovera zvandakawana - chibereko chepfungwa, kuedza uye kukanganisa.
Kakawanda: izvi hazvisi kuwanikwa, hongu - izvi zvese zvinofanirwa kunge zvichizivikanwa kwenguva yakareba, kune avo vanobatanidzwa mukushandiswa kwenhamba yedata data uye optimization yechero system, kwete kunyanya iyo DBMS.
Uye: hongu, ivo vanoziva, vanonyora zvinonakidza zvinyorwa patsvagiridzo yavo, (UPD.: mune zvakataurwa vakaratidza chirongwa chinonakidza kwazvo: )
Kune rimwe divi: kure ini handisi kuona kutaurwa kwakapararira kana kuparadzira nzira iyi paInternet pakati penyanzvi dzeIT, DBA.
Saka, kune iyo pfungwa.
Ngatifungei kuti tine basa: kumisa imwe sevhisi sisitimu yekushandira imwe mhando yebasa.
Zvinozivikanwa nezvebasa iri: kuti chii, kuti kunaka kwebasa iri kunoyerwa sei, uye ndeipi chiyero chekuyera hunhu uhu.
Ngatifungei zvakare kuti inonyanya kuzivikanwa uye inonzwisiswa: chaizvo maitirwo anoitwa basa mu (kana ne) iyi sevhisi system.
"Zvizhinji kana zvishoma" - zvinoreva kuti zvinokwanisika kugadzirira (kana kuitora kubva kune imwe nzvimbo) chimwe chishandiso, chekushandisa, sevhisi inogona kugadzirwa uye kuiswa kune sisitimu ine muyedzo wemuyedzo wakakwana kune izvo zvichave mukugadzirwa, mumamiriro ezvinhu akakwana zvakakwana kushanda mukugadzirwa.
Zvakanaka, ngatifungei kuti seti yezvigadziro zvekugadzirisa kwegadziriro iyi yebasa inozivikanwa, iyo inogona kushandiswa kugadzirisa iyi hurongwa maererano nekugadzirwa kwebasa rayo.
Uye chii chiri dambudziko - hapana kukwana kwakakwana kunzwisiswa kweiyi sisitimu yebasa, iyo inobvumidza iwe kuti ugadzirise nehunyanzvi marongero ehurongwa uhu hwemutoro weramangwana pane yakapihwa chikuva uye kuwana inodiwa chibereko chehurongwa.
Zvakanaka. Izvi zvinenge zvagara zvakadaro.
Ungaitei pano?
Zvakanaka, chinhu chekutanga chinouya mupfungwa kutarisa zvinyorwa zveiyi system. Nzwisisa kuti ndeapi marenji anogamuchirika eiyo kukosha kweiyo gadziriso paramita. Uye, semuenzaniso, uchishandisa iyo yekubatanidza nzira yekudzika, sarudza kukosha kweiyo system paramita mubvunzo.
Avo. ipa iyo sisitimu imwe mhando yekumisikidzwa, muchimiro cheiyo chaiyo seti yezvimiro zveiyo magadzirirwo maparamita.
Isa muyedzo wemuyedzo kwairi, uchishandisa ichi chishandiso-chishandiso, mutoro jenareta.
Uye tarisa kukosha - mhinduro, kana metric yemhando yehurongwa.
Pfungwa yechipiri inogona kunge iri mhedziso yekuti iyi inguva refu kwazvo.
Zvakanaka, ndiko kuti: kana paine akawanda ekuseta paramita, kana huwandu hwehutsika hwavo huri kumhanyisa hwakakura, kana bvunzo yega yega inotora nguva yakawanda kuti ipedze, zvino: hongu, zvese izvi zvinogona kutora zvisingagamuchirwi. nguva refu.
Zvakanaka, hezvino zvaunogona kunzwisisa nekurangarira.
Iwe unogona kuona kuti mune seti yehutsika hwesevhisi masisitimu magadzirirwo paramita pane vector, sekutevedzana kwemamwe maitiro.
Imwe neimwe vheti yakadaro, zvimwe zvinhu zvakaenzana (mukuti hazvikanganisike nevheta iyi), inoenderana nekukosha kwakazara kwemetric - chiratidzo chemhando yekushanda kwehurongwa pasi pekuyedzwa mutoro.
Avo.
Ngatiratidzei sisitimu yekumisikidza vector se 
kupi ; Kupi - nhamba yemagadzirirwo ehurongwa, mangani emaparamita aya aripo.
Uye kukosha kwemetric inoenderana neizvi ngatizviratidze se
, tobva tawana basa:
Zvakanaka, zvino: zvese zvinouya pasi, mune yangu nyaya: yakapotsa yakanganwika kubva kumazuva angu emudzidzi, algorithms yekutsvaga iyo yakanyanyisa yebasa.
Zvakanaka, asi pano mubvunzo wesangano uye wakashandiswa unomuka: ndeipi algorithm yekushandisa.
- Mupfungwa - kuitira kuti iwe unogona kukodha zvishoma neruoko.
- Uye kuti ishande, i.e. yakawana iyo yakanyanyisa (kana paine imwe), zvakanaka, nekukurumidza kupfuura kurongeka kudzika.
Pfungwa yekutanga inoratidza kuti isu tinofanirwa kutarisa kune dzimwe nharaunda umo maalgorithms akadaro akatoitwa, uye ari, mune imwe nzira, akagadzirira kushandiswa mukodhi.
Zvakanaka, ndinoziva python и cran-r
Pfungwa yechipiri inoreva kuti iwe unofanirwa kuverenga nezve algorithms pachawo, zvaari, izvo zvavanoda, uye maitiro ebasa ravo.
Uye izvo zvavanopa zvinogona kubatsira mhedzisiro - mhedzisiro, kana zvakananga kubva kune algorithm pachayo.
Kana ivo vanogona kuwanikwa kubva kune zvakabuda zvealgorithm.
Zvakawanda zvinoenderana nemamiriro ekuisa.
Semuenzaniso, kana, nekuda kwechimwe chikonzero, iwe unofanirwa kuwana mhedzisiro nekukurumidza, zvakanaka, iwe unofanirwa kutarisa kune gradient descent algorithms uye sarudza imwe yacho.
Kana, kana nguva isina kunyanya kukosha, unogona, semuenzaniso, kushandisa stochastic optimization nzira, senge genetic algorithm.
Ini ndinokurudzira kufunga nezvebasa reiyi nzira, kusarudza iyo gadziriro yekugadzirisa, uchishandisa genetic algorithm, mune inotevera, sekutaura: basa re laboratori.
Chekutanga:
- Ngakuve, sesevhisi system:
oracle xe 18c - Rega ishandire kutengeserana chiitiko uye chinangwa: kuwana iyo yepamusoro inogoneka kuburikidza neiyo subdatabase, mukutengeserana/sec.
- Kutengeserana kunogona kunge kwakasiyana zvakanyanya muchimiro chekushanda nedata uye mamiriro ebasa.
Ngatibvumei kuti aya matransaction asingagadzirise huwandu hukuru hwetabular data.
Mupfungwa yekuti ivo havaburitse data rekugadzirisa pane kuitazve uye havagadzirise mapeji makuru emitsara nematafura makuru.
Aya matranseksheni anoshandura mutsara mumwe mune yakakura kana shoma tafura yakakura, ine nhamba shoma yema indexes patafura iyi.
Mumamiriro ezvinhu aya: kugadzirwa kweiyo subdatabase yekugadzirisa kutengeserana kuchave, nekuchengetera, kunotemwa nemhando yekugadziriswa neiyo redox dhatabhesi.
Disclaimer - kana tikataura zvakananga nezve subdb marongero.
Nekuti, mune zvakajairika, panogona kunge paine, semuenzaniso, makiyi ekutengeserana pakati pemasesheni eSQL, nekuda kwekugadzirwa kwebasa remushandisi netabular data uye / kana tabular modhi.
Izvo, zvechokwadi, zvichave nezvinoodza mwoyo pametric yeTPS uye ichi chichava chinhu chekunze, chinoenderana neiyo subdatabase: zvakanaka, iyi ndiyo nzira yakagadzirirwa tabular modhi uye basa rine data mairi kuti blockages anoitika.
Naizvozvo, nekuda kwekuchena kwekuyedza, isu tichasiya chinhu ichi, uye pazasi ini ndichanyatso kujekesa sei.
- Ngatitorei, kune chokwadi, kuti 100% yemirairo yeSQL yakatumirwa kune database ndeye DML mirairo.
Rega hunhu hwemushandisi ushande neiyo subdatabase ive yakafanana mubvunzo.
Zvinonzi: nhamba yezvikamu zve skl, tabular data, kuti skl zvikamu zvinoshanda sei navo. - Subd inoshanda mukati
FORCE LOGGING,ARCHIVELOGmods. Flashback-database mode yakadzimwa, padanho re subd. - Dzokorora matanda: iri mune yakaparadzana faira system, pane yakaparadzana "disk";
Iyo yakasara yechikamu chemuviri chedhatabhesi: mune imwe, yakaparadzana faira system, pane yakaparadzana "disk":
Mamwe mashoko pamusoro pechigadzirwa chemuviri. laboratori Database zvikamu
SQL> select status||' '||name from v$controlfile;
/db/u14/oradata/XE/control01.ctl
SQL> select GROUP#||' '||MEMBER from v$logfile;
1 /db/u02/oradata/XE/redo01_01.log
2 /db/u02/oradata/XE/redo02_01.log
SQL> select FILE_ID||' '||TABLESPACE_NAME||' '||round(BYTES/1024/1024,2)||' '||FILE_NAME as col from dba_data_files;
4 UNDOTBS1 2208 /db/u14/oradata/XE/undotbs1_01.dbf
2 SLOB 128 /db/u14/oradata/XE/slob01.dbf
7 USERS 5 /db/u14/oradata/XE/users01.dbf
1 SYSTEM 860 /db/u14/oradata/XE/system01.dbf
3 SYSAUX 550 /db/u14/oradata/XE/sysaux01.dbf
5 MONITOR 128 /db/u14/oradata/XE/monitor.dbf
SQL> !cat /proc/mounts | egrep "/db/u[0-2]"
/dev/vda1 /db/u14 ext4 rw,noatime,nodiratime,data=ordered 0 0
/dev/mapper/vgsys-ora_redo /db/u02 xfs rw,noatime,nodiratime,attr2,nobarrier,inode64,logbsize=256k,noquota 0 0Pakutanga, pasi pemamiriro ezvinhu aya, ndaida kushandisa transaction subd
Iine chinhu chakanaka kudaro, ndichatora munyori:
Pamwoyo weSLOB ndiyo "SLOB nzira." Iyo SLOB Method ine chinangwa chekuyedza mapuratifomu
pasina kukakavara kwekushandisa. Mumwe haakwanise kutyaira yakanyanya hardware performance
uchishandisa kodhi yekushandisa iyo, semuenzaniso, yakasungwa nekuvhara application kana kunyange
kugovera Oracle Database blocks. Ndizvozvo-pane pamusoro pekugovana data
mune data block! Asi SLOB - mukumisikidzwa kwayo - haina kudzivirirwa pamakakatanwa akadai.
Ichi chiziviso: zvinoenderana, ndizvo.
Izvo zvakaringana kudzora dhigirii rekufanana kwezvikamu zvecl, ichi ndicho kiyi -t vhura utility runit.sh kubva kuSLOB
Iyo muzana yemirairo yeDML inodzorwa, muhuwandu hwemashoko anotumirwa kune subd, yega yega chinyorwa, parameter. UPDATE_PCT
Zvakasiyana uye zviri nyore kwazvo: SLOB pachayo, pamberi uye mushure mechikamu chemutoro - inogadzirira statspack, kana awr-snapshots (izvo zvakagadzirirwa kugadzirirwa).
Zvisinei, zvakazoitika SLOB haitsigire SQL sesheni ine nguva isingasviki 30 masekonzi.
Naizvozvo, ini ndakatanga kukodha yangu, yevashandi-varimi vhezheni yemutoro, uyezve yakaramba ichishanda.
Rega ndijekese izvo zvinorodha uye kuti zvinozviita sei, kuti zvijeke.
Chaizvoizvo iyo loader inoita seizvi:
Kodhi yevashandi
function dotx()
{
local v_period="$2"
[ -z "v_period" ] && v_period="0"
source "/home/oracle/testingredotracе/config.conf"
$ORACLE_HOME/bin/sqlplus -S system/${v_system_pwd} << __EOF__
whenever sqlerror exit failure
set verify off
set echo off
set feedback off
define wnum="$1"
define period="$v_period"
set appinfo worker_&&wnum
declare
v_upto number;
v_key number;
v_tots number;
v_cts number;
begin
select max(col1) into v_upto from system.testtab_&&wnum;
SELECT (( SYSDATE - DATE '1970-01-01' ) * 86400 ) into v_cts FROM DUAL;
v_tots := &&period + v_cts;
while v_cts <= v_tots
loop
v_key:=abs(mod(dbms_random.random,v_upto));
if v_key=0 then
v_key:=1;
end if;
update system.testtab_&&wnum t
set t.object_name=translate(dbms_random.string('a', 120), 'abcXYZ', '158249')
where t.col1=v_key
;
commit;
SELECT (( SYSDATE - DATE '1970-01-01' ) * 86400 ) into v_cts FROM DUAL;
end loop;
end;
/
exit
__EOF__
}
export -f dotxVashandi vanogadzirwa nenzira iyi:
Vashandi vanomhanya
echo "starting test, duration: ${TEST_DURATION}" >> "$v_logfile"
for((i=1;i<="$SQLSESS_COUNT";i++))
do
echo "sql-session: ${i}" >> "$v_logfile"
dotx "$i" "${TEST_DURATION}" &
done
echo "waiting..." >> "$v_logfile"
waitUye matafura evashandi anogadzirwa seizvi:
Kugadzira matafura
function createtable() {
source "/home/oracle/testingredotracе/config.conf"
$ORACLE_HOME/bin/sqlplus -S system/${v_system_pwd} << __EOF__
whenever sqlerror continue
set verify off
set echo off
set feedback off
define wnum="$1"
define ts_name="slob"
begin
execute immediate 'drop table system.testtab_&&wnum';
exception when others then null;
end;
/
create table system.testtab_&&wnum tablespace &&ts_name as
select rownum as col1, t.*
from sys.dba_objects t
where rownum<1000
;
create index testtab_&&wnum._idx on system.testtab_&&wnum (col1);
--alter table system.testtab_&&wnum nologging;
--alter index system.testtab_&&wnum._idx nologging;
exit
__EOF__
}
export -f createtable
seq 1 1 "$SQLSESS_COUNT" | xargs -n 1 -P 4 -I {} -t bash -c "createtable "{}"" | tee -a "$v_logfile"
echo "createtable done" >> "$v_logfile"Avo. Kumushandi wega wega (zvichida: chikamu cheSQL chakasiyana muDB) tafura yakaparadzana inogadzirwa, iyo mushandi anoshanda nayo.
Izvi zvinovimbisa kusavapo kwekukiya kwekutengeserana pakati pezvikamu zvevashandi.
Mushandi mumwe nomumwe: anoita chinhu chimwe, netafura yake, matafura akafanana.
Vashandi vose vanoita basa panguva imwe chete.
Uyezve, kwenguva yakareba yakakwana kuitira kuti, semuenzaniso, shanduko yelogi yaizoitika, uye kanopfuura kamwe.
Zvakanaka, saizvozvo, mari yakabatana nemigumisiro yakasimuka.
Mune mhaka yangu, ndakagadzirisa nguva yebasa revashandi pamaminitsi e8.
Chidimbu chemushumo westatspack inotsanangura kushanda kweiyo subd iri pasi pemutoro
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2929910313 XE 1 07-Sep-20 23:12 18.0.0.0.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
billing.izhevsk1 Linux x86 64-bit 2 2 1 15.6
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1630 07-Sep-20 23:12:27 55 .7
End Snap: 1631 07-Sep-20 23:20:29 62 .6
Elapsed: 8.03 (mins) Av Act Sess: 8.4
DB time: 67.31 (mins) DB CPU: 15.01 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,392M Std Block Size: 8K
Shared Pool: 288M Log Buffer: 103,424K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 8.4 0.0 0.00 0.20
DB CPU(s): 1.9 0.0 0.00 0.04
Redo size: 7,685,765.6 978.4
Logical reads: 60,447.0 7.7
Block changes: 47,167.3 6.0
Physical reads: 8.3 0.0
Physical writes: 253.4 0.0
User calls: 42.6 0.0
Parses: 23.2 0.0
Hard parses: 1.2 0.0
W/A MB processed: 1.0 0.0
Logons: 0.5 0.0
Executes: 15,756.5 2.0
Rollbacks: 0.0 0.0
Transactions: 7,855.1Kudzokera kubasa re laboratory.
Isu, zvimwe zvinhu zvakaenzana, zvinosiyanisa kukosha kweanotevera paramita yerabhoritari subdatabase:
- Saizi yedatabase log mapoka. kukosha kwemhando: [32, 1024] MB;
- Nhamba yemapoka ejenari ari mudhatabhesi. kukosha kwemhando: [2,32];
log_archive_max_processeskukosha kwemhando: [1,8];commit_loggingmaitiro maviri anobvumidzwa:batch|immediate;commit_waitmaitiro maviri anobvumidzwa:wait|nowait;log_bufferkukosha kwemhando: [2,128] MB.log_checkpoint_timeoutkukosha siyana: [60,1200] masekonzidb_writer_processeskukosha kwemhando: [1,4]undo_retentionkukosha siyana: [30;300] masekonzitransactions_per_rollback_segmentkukosha kwemhando: [1,8]disk_asynch_iomaitiro maviri anobvumidzwa:true|false;filesystemio_optionszvinotevera zvakakosha zvinotenderwa:none|setall|directIO|asynch;db_block_checkingzvinotevera zvakakosha zvinotenderwa:OFF|LOW|MEDIUM|FULL;db_block_checksumzvinotevera zvakakosha zvinotenderwa:OFF|TYPICAL|FULL;
Munhu ane ruzivo rwekuchengetedza Oracle dhatabhesi anogona kunyatsotaura kuti chii uye kune zvipi zvakakosha zvinofanirwa kuisirwa, kubva pane zvakatarwa paramita uye hunhu hwavo hunogamuchirika, kuitira kuti vawane hukuru hukuru hwe database yebasa rine data rinoratidzwa ne iyo kodhi yekushandisa, pano pamusoro.
Asi.
Pfungwa yebasa rerabhoritari ndeyekuratidza kuti iyo optimization algorithm pachayo ichajekesa izvi kwatiri nekukurumidza.
Kwatiri, chasara kutarisa mugwaro, kuburikidza neiyo customizable system, zvakangokwana kuti tizive kuti ndeapi maparamendi ekuchinja uye mune api masanji.
Uye zvakare: kodhi iyo kodhi iyo ichashandiswa kushanda neyakajairwa sisitimu yeiyo yakasarudzwa optimization algorithm.
Saka, ikozvino nezve kodhi.
Ndataura pamusoro cran-r, kureva: ese manipulations ane customized system akarongwa nenzira yeR script.
Iro chairo basa, ongororo, kusarudzwa nemetric kukosha, system state vectors: iyi pasuru GA ()
Iyo pasuru, mune iyi kesi, haina kunyatsokodzera, mupfungwa yekuti inotarisira vectors (chromosomes, kana maererano nepakakeji) inotsanangurwa muchimiro chetambo dzenhamba dzine chikamu chechikamu.
Uye vector yangu, kubva kuhunhu hwekumisikidza paramita: aya ndiwo gumi nemana uwandu - integers uye tambo tsika.
Dambudziko, hongu, rinodzivirirwa zviri nyore nekupa dzimwe nhamba dzakananga kune tambo tsika.
Saka, pakupedzisira, chikamu chikuru cheR script chinotaridzika seizvi:
Dana GA::ga
cat( "", file=v_logfile, sep="n", append=F)
pSize = 10
elitism_value=1
pmutation_coef=0.8
pcrossover_coef=0.1
iterations=50
gam=GA::ga(type="real-valued", fitness=evaluate,
lower=c(32,2, 1,1,1,2,60,1,30,1,0,0, 0,0), upper=c(1024,32, 8,10,10,128,800,4,300,8,10,40, 40,30),
popSize=pSize,
pcrossover = pcrossover_coef,
pmutation = pmutation_coef,
maxiter=iterations,
run=4,
keepBest=T)
cat( "GA-session is done" , file=v_logfile, sep="n", append=T)
gam@solutionPano, nerubatsiro lower и upper subroutine attributes ga chaizvo, nzvimbo yenzvimbo yekutsvaga inotsanangurwa, mukati umo kutsvaga kuchaitwa kune yakadaro vector (kana mavheji) iyo iyo yakanyanya kukosha yefitness basa ichawanikwa.
Iyo ga subroutine inoita tsvakiridzo inokudza iyo kusimba basa.
Zvakanaka, zvino, zvinozoitika kuti, mune iyi kesi, zvinodikanwa kuti kusimba basa, kunzwisisa iyo vector seti yemitengo yemamwe ma parameter eiyo subd, inogamuchira metric kubva kune subd.
Ndiko kuti: vangani, ine yakapihwa subd setup uye yakapihwa mutoro pane iyo subd: iyo subd maitiro ekutengesa pasekondi.
Ndokunge, kana kuvhurwa, inotevera nhanho yakawanda inofanirwa kuitwa mukati mekusimba basa:
- Kugadzirisa iyo yekuisa vector yenhamba - kuishandura kuita kukosha kune subdata paramita.
- Kuedza kugadzira nhamba yakapihwa yemapoka ezve saizi yakapihwa. Uyezve, kuedza kunogona kusabudirira.
Mapoka emagazini aitovepo musubd, mune humwe huwandu uye hwehumwe hukuru, kuitira kuchena kwekuyedza - d.b. deleted. - Kana iyo yapfuura poindi yakabudirira: kutsanangura kukosha kwekumisikidza paramita kune dhatabhesi (zvakare: panogona kunge paine kutadza)
- Kana iyo yapfuura nhanho yakabudirira: kumisa iyo subd, kutanga iyo subd kuitira kuti ichangobva kutsanangurwa parameter kukosha iite. (zvakare: panogona kunge paine glitch)
- Kana danho rekare rakabudirira: ita bvunzo yemutoro. tora metrics kubva ku subd.
- Dzorera iyo subd kune yayo yekutanga, i.e. bvisa mamwe mapoka egirogi, dzosera iyo yekutanga subdatabase kumisikidzwa kuti ishande.
Fitness function code
evaluate=function(p_par) {
v_module="evaluate"
v_metric=0
opn=NULL
opn$rg_size=round(p_par[1],digit=0)
opn$rg_count=round(p_par[2],digit=0)
opn$log_archive_max_processes=round(p_par[3],digit=0)
opn$commit_logging="BATCH"
if ( round(p_par[4],digit=0) > 5 ) {
opn$commit_logging="IMMEDIATE"
}
opn$commit_logging=paste("'", opn$commit_logging, "'",sep="")
opn$commit_wait="WAIT"
if ( round(p_par[5],digit=0) > 5 ) {
opn$commit_wait="NOWAIT"
}
opn$commit_wait=paste("'", opn$commit_wait, "'",sep="")
opn$log_buffer=paste(round(p_par[6],digit=0),"m",sep="")
opn$log_checkpoint_timeout=round(p_par[7],digit=0)
opn$db_writer_processes=round(p_par[8],digit=0)
opn$undo_retention=round(p_par[9],digit=0)
opn$transactions_per_rollback_segment=round(p_par[10],digit=0)
opn$disk_asynch_io="true"
if ( round(p_par[11],digit=0) > 5 ) {
opn$disk_asynch_io="false"
}
opn$filesystemio_options="none"
if ( round(p_par[12],digit=0) > 10 && round(p_par[12],digit=0) <= 20 ) {
opn$filesystemio_options="setall"
}
if ( round(p_par[12],digit=0) > 20 && round(p_par[12],digit=0) <= 30 ) {
opn$filesystemio_options="directIO"
}
if ( round(p_par[12],digit=0) > 30 ) {
opn$filesystemio_options="asynch"
}
opn$db_block_checking="OFF"
if ( round(p_par[13],digit=0) > 10 && round(p_par[13],digit=0) <= 20 ) {
opn$db_block_checking="LOW"
}
if ( round(p_par[13],digit=0) > 20 && round(p_par[13],digit=0) <= 30 ) {
opn$db_block_checking="MEDIUM"
}
if ( round(p_par[13],digit=0) > 30 ) {
opn$db_block_checking="FULL"
}
opn$db_block_checksum="OFF"
if ( round(p_par[14],digit=0) > 10 && round(p_par[14],digit=0) <= 20 ) {
opn$db_block_checksum="TYPICAL"
}
if ( round(p_par[14],digit=0) > 20 ) {
opn$db_block_checksum="FULL"
}
v_vector=paste(round(p_par[1],digit=0),round(p_par[2],digit=0),round(p_par[3],digit=0),round(p_par[4],digit=0),round(p_par[5],digit=0),round(p_par[6],digit=0),round(p_par[7],digit=0),round(p_par[8],digit=0),round(p_par[9],digit=0),round(p_par[10],digit=0),round(p_par[11],digit=0),round(p_par[12],digit=0),round(p_par[13],digit=0),round(p_par[14],digit=0),sep=";")
cat( paste(v_module," try to evaluate vector: ", v_vector,sep="") , file=v_logfile, sep="n", append=T)
rc=make_additional_rgroups(opn)
if ( rc!=0 ) {
cat( paste(v_module,"make_additional_rgroups failed",sep="") , file=v_logfile, sep="n", append=T)
return (0)
}
v_rc=0
rc=set_db_parameter("log_archive_max_processes", opn$log_archive_max_processes)
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("commit_logging", opn$commit_logging )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("commit_wait", opn$commit_wait )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("log_buffer", opn$log_buffer )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("log_checkpoint_timeout", opn$log_checkpoint_timeout )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("db_writer_processes", opn$db_writer_processes )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("undo_retention", opn$undo_retention )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("transactions_per_rollback_segment", opn$transactions_per_rollback_segment )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("disk_asynch_io", opn$disk_asynch_io )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("filesystemio_options", opn$filesystemio_options )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("db_block_checking", opn$db_block_checking )
if ( rc != 0 ) { v_rc=1 }
rc=set_db_parameter("db_block_checksum", opn$db_block_checksum )
if ( rc != 0 ) { v_rc=1 }
if ( rc!=0 ) {
cat( paste(v_module," can not startup db with that vector of settings",sep="") , file=v_logfile, sep="n", append=T)
rc=stop_db("immediate")
rc=create_spfile()
rc=start_db("")
rc=remove_additional_rgroups(opn)
return (0)
}
rc=stop_db("immediate")
rc=start_db("")
if ( rc!=0 ) {
cat( paste(v_module," can not startup db with that vector of settings",sep="") , file=v_logfile, sep="n", append=T)
rc=stop_db("abort")
rc=create_spfile()
rc=start_db("")
rc=remove_additional_rgroups(opn)
return (0)
}
rc=run_test()
v_metric=getmetric()
rc=stop_db("immediate")
rc=create_spfile()
rc=start_db("")
rc=remove_additional_rgroups(opn)
cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="n", append=T)
return (v_metric)
}Izvozvo. basa rose: rinoitwa mukusimba.
Iyo ga-subroutine inogadzira mavector, kana, zvakanyanya, machromosomes.
Mune izvo, chinonyanya kukosha kwatiri kusarudzwa kwemakromosomes ane majini ayo basa rekusimba rinoburitsa hukuru hukuru.
Izvi, muchidimbu, maitiro ekutsvaga iyo yakakwana seti yemakromosomes uchishandisa vheji mune N-dimensional yekutsvaga nzvimbo.
Zvakajeka, zvakanyatsojeka , ine mienzaniso yeR-code, basa re genetic algorithm.
Ndinoda kucherechedza zvakasiyana mapoinzi maviri ehunyanzvi.
Mafoni ekubatsira kubva kubasa evaluate, semuenzaniso, kumira-kutanga, kuisa kukosha kweiyo subd parameter, inoitwa zvichienderana cran-r mabasa system2
Nerubatsiro rweiyo: imwe bash script kana kuraira kunodanwa.
Somuenzaniso:
set_db_parameter
set_db_parameter=function(p1, p2) {
v_module="set_db_parameter"
v_cmd="/home/oracle/testingredotracе/set_db_parameter.sh"
v_args=paste(p1," ",p2,sep="")
x=system2(v_cmd, args=v_args, stdout=T, stderr=T, wait=T)
if ( length(attributes(x)) > 0 ) {
cat(paste(v_module," failed with: ",attributes(x)$status," ",v_cmd," ",v_args,sep=""), file=v_logfile, sep="n", append=T)
return (attributes(x)$status)
}
else {
cat(paste(v_module," ok: ",v_cmd," ",v_args,sep=""), file=v_logfile, sep="n", append=T)
return (0)
}
}Pfungwa yechipiri ndeye mutsara, evaluate mabasa, nekuchengetedza chaiyo metric kukosha uye inowirirana tuning vector kune regi faira:
cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="n", append=T)Izvi zvakakosha, nekuti kubva pane iyi data array, zvinogoneka kuwana rumwe ruzivo rwekuti ndechipi chezvikamu zvetuning vector ine hukuru kana kushoma mhedzisiro pane metric kukosha.
Ndiko kuti: zvinogoneka kuita hunhu-kukosha ongororo.
Saka chii chingaitika?
Mune girafu fomu, kana iwe ukaraira bvunzo mukukwira metric kurongeka, mufananidzo unotevera:
Imwe data inoenderana neakanyanya kukosha eiyo metric:
Pano, mune iyo skrini ine mhedzisiro, ini ndichajekesa: kukosha kweiyo tuning vector inopihwa maererano neiyo fitness function kodhi, kwete maererano nenhamba rondedzero yemaparamita / marenji eiyo parameter values, iyo yakagadzirwa. pamusoro muchinyorwa.
Zvakanaka. Zvakawanda here kana zvishoma, ~ 8 zviuru tps: mubvunzo wakasiyana.
Mukati megadziriro yebasa re laboratori, nhamba iyi haina kukosha, izvo zvakakosha ndezvemasimba, kuti kukosha uku kunoshanduka sei.
Masimba epano akanaka.
Zviripachena kuti chimwe chinhu chinonyanya kukanganisa kukosha kwemetric, iyo ga-algorithm, kuronga kuburikidza nemakromosome vectors: akafukidzwa.
Tichitarisa nemasimba akasimba eiyo curve values, pane chimwezve chinhu icho, kunyangwe chiri chidiki zvakanyanya, chine pesvedzero.
Apa ndipo paunoda attribute-importance ongororo kuti unzwisise kuti ndeapi hunhu (zvakanaka, mune ino kesi, zvikamu zveiyo tuning vector) uye kuti yakawanda sei inopesvedzera kukosha kwemetric.
Uye kubva paruzivo urwu: nzwisisa kuti ndezvipi zvinhu zvakakanganiswa nekuchinja kwehunhu hwakakosha.
Kuuraya attribute-importance zvinogoneka nenzira dzakasiyana.
Nezvinangwa izvi, ini ndinoda iyo algorithm randomForest R pasuru yezita rimwechete ()
randomForest, sezvo ini ndinonzwisisa basa rake mune zvese uye maitiro ake ekuongorora kukosha kwehunhu kunyanya, inovaka imwe modhi yekutsamira kwemhinduro yekuchinja pane hunhu.
Mune yedu kesi, mhinduro inosiyana ndeye metric inowanikwa kubva kudhatabhesi mukuyedzwa kwemutoro: tps;
Uye hunhu zvikamu zve tuning vector.
Saka pano randomForest inoongorora kukosha kwemuenzaniso wega wega hunhu nenhamba mbiri: %IncMSE - kuti kuvapo / kusavapo kwechimiro ichi mumuenzaniso kunoshandura sei MSE mhando yeiyi modhi (Mean Squared Error);
Uye IncNodePurity inhamba inoratidza kuti zvakanaka sei, zvichibva pahunhu hwehunhu uhu, dhatabheti rine zvinoonekwa rinogona kupatsanurwa, zvekuti mune chimwe chikamu pane data ine kukosha kumwe kweiyo metric inotsanangurwa, uye mune imwe ine. imwe kukosha kwemetric.
Zvakanaka, ndiko kuti: kusvika papi iyi hunhu hwekuisa (ndakaona yakanyatsojeka, tsananguro yemutauro wechiRussia paRandomForest ).
Worker-peasant R-code yekugadzirisa dhatabheti nemhedzisiro yekuyedzwa kwemutoro:
x=NULL
v_data_file=paste('/tmp/data1.dat',sep="")
x=read.table(v_data_file, header = TRUE, sep = ";", dec=",", quote = ""'", stringsAsFactors=FALSE)
colnames(x)=c('metric','rgsize','rgcount','lamp','cmtl','cmtw','lgbffr','lct','dbwrp','undo_retention','tprs','disk_async_io','filesystemio_options','db_block_checking','db_block_checksum')
idxTrain=sample(nrow(x),as.integer(nrow(x)*0.7))
idxNotTrain=which(! 1:nrow(x) %in% idxTrain )
TrainDS=x[idxTrain,]
ValidateDS=x[idxNotTrain,]
library(randomForest)
#mtry=as.integer( sqrt(dim(x)[2]-1) )
rf=randomForest(metric ~ ., data=TrainDS, ntree=40, mtry=3, replace=T, nodesize=2, importance=T, do.trace=10, localImp=F)
ValidateDS$predicted=predict(rf, newdata=ValidateDS[,colnames(ValidateDS)!="metric"], type="response")
sum((ValidateDS$metric-ValidateDS$predicted)^2)
rf$importanceIwe unogona kusarudza zvakananga ma hyperparameters egorgorithm nemaoko ako uye, uchitarisa pamhando yemuenzaniso, sarudza muenzaniso unonyatsozadzisa zvakafanotaurwa pane dheta rekusimbisa.
Iwe unogona kunyora imwe mhando yebasa rebasa iri (nenzira, zvakare, uchishandisa imwe mhando ye optimization algorithm).
Unogona kushandisa R package caret, kwete pfungwa inokosha.
Nekuda kweizvozvo, mune iyi kesi, inotevera mhedzisiro inowanikwa kuti iongorore dhigirii rekukosha kwehunhu:
Zvakanaka. Nokudaro, tinogona kutanga kufungisisa kwenyika yose:
- Zvinoitika kuti zvakanyanya kukosha, pasi pemamiriro aya ekuyedzwa, yaive parameter
commit_wait
Nehunyanzvi, inotsanangura maitiro ekuita kweiyo io mashandiro ekunyora redo data kubva kune subdb log buffer kune yazvino log group: synchronous kana asynchronous.
ukoshinowaitizvo zvinoguma neinenge yakamira, yakawedzera kuwedzera kukosha kwetps metric: uku ndiko kuiswa kweasynchronous io mode mumapoka ezve.
Mubvunzo wakasiyana ndewekuti iwe unofanirwa kuita izvi here mudura rekudya. Pano ndinogumira pakutaura chete: ichi chinhu chakakosha. - Zvine musoro kuti saizi yerogi buffer ye subd: inoshanduka kuve yakakosha chinhu.
Iyo diki saizi yerogi buffer, kushoma kwayo kubhafa, iyo inowanzofashukira uye / kana kusakwanisa kugovera yakasununguka nzvimbo mairi kune chikamu chetsva redox data.
Izvi zvinoreva: kunonoka kwakabatana nekugovera nzvimbo mulog buffer uye/kana kurasa redo data kubva mairi kuita mapoka ezve.
Kunonoka uku, hongu, kunofanirwa uye kunokanganisa kubuda kwedhatabhesi yekutengeserana. - Parameter
db_block_checksum: zvakanaka, zvakare, zviri pachena - transaction process inotungamira mukugadzirwa kwedarty blocks mune subdatabase's buffer cache.
Izvo, kana uchitarisa cheki yedatablocks inogoneswa, dhatabhesi inofanirwa kugadzirisa - kuverenga aya macheki kubva kumutumbi wedatablock, tarisa iwo nezvakanyorwa mumusoro wedatablock: machisi/haaenderane.
Basa rakadaro, zvakare, harigone asi kunonoka kugadzirisa data, uye nekudaro, paramende uye meshini inoisa iyi parameter inoshanduka kuve yakakosha.
Ndosaka mutengesi achipa, mune zvinyorwa zveiyi parameter, akasiyana siyana ayo (iyo parameter) uye anonyora kuti hongu, pachave nekukanganisa, asi, zvakanaka, unogona kusarudza akasiyana maitiro, kusvika "kudzima" uye zvakasiyana-siyana.
Eya, mhedziso yenyika yose.
Iyo nzira, kazhinji, inoshanduka kuva inoshanda chaizvo.
Anonyatso azvibvumira, mumatanho ekutanga ekuyedzwa kwemutoro weimwe sevhisi sisitimu, kuitira kuti asarudze ayo (system) yakakwana kumisikidzwa yemutoro, kwete kunyura zvakanyanya mune chaiyo yekumisikidza sisitimu yemutoro.
Asi hazvizviisire zvachose - zvirinani pamwero wekunzwisisa: sisitimu inofanirwa kuzivikanwa nezve "makonzo ekugadzirisa" uye miganhu inotenderwa yekutenderera kweaya mapfundo.
Iyo nzira inogona ipapo nekukurumidza kuwana iyo yakakwana sisitimu yekumisikidza.
Uye zvichibva pamhedzisiro yekuyedzwa, zvinokwanisika kuwana ruzivo nezve chimiro chehukama pakati peiyo system performance metrics uye kukosha kweiyo system masisitimu ma paramita.
Izvo, zvechokwadi, zvinofanirwa kubatsira pakubuda kwekunzwisisa kwakadzika kwehurongwa, kushanda kwayo, zvishoma pasi pemutoro wakapihwa.
Mukuita, uku ndiko kuchinjana kwemitengo yekunzwisisa iyo yakagadziridzwa sisitimu yemitengo yekugadzirira kuedzwa kwakadaro kwehurongwa.
Ndinoda kucherechedza zvakasiyana: mune iyi nzira, chiyero chekukwana kwekuongorora kwehurongwa kune mamiriro ekushanda ayo achange aine mukushanda kwekutengeserana kwakakosha zvikuru.
Ndinokutendai nekuteerera kwenyu nenguva.
Source: www.habr.com