Hello
Ua hoʻoholo wau e kaʻana like i kaʻu ʻike - ka hua o ka noʻonoʻo, ka hoʻāʻo a me ka hewa.
Ma a nui: ʻaʻole kēia he loaʻa, ʻoiaʻiʻo - ua ʻike ʻia kēia mau mea no ka manawa lōʻihi, i ka poʻe e pili ana i ka hoʻopili ʻana i ka ʻikepili helu a me ka optimization o nā ʻōnaehana, ʻaʻole pono i ka DBMS.
A: ʻae, ʻike lākou, kākau lākou i nā ʻatikala hoihoi i kā lākou noiʻi, (UPD.: ma nā ʻōlelo a lākou i kuhikuhi ai i kahi papahana hoihoi loa: )
Ma ka ʻaoʻao ʻē aʻe: ʻaʻole au i ʻike i kahi ʻōlelo ākea a hoʻolaha paha i kēia ala ma ka Pūnaewele ma waena o nā loea IT, DBA.
No laila, i ke kumu.
E manaʻo mākou he hana kā mākou: e hoʻonohonoho i kahi ʻōnaehana lawelawe e lawelawe i kekahi ʻano hana.
Ua ʻike ʻia e pili ana i kēia hana: he aha ia, pehea e ana ʻia ai ka maikaʻi o kēia hana, a he aha ke kumu no ke ana ʻana i kēia ʻano.
E noʻonoʻo kākou he ʻike a ʻike ʻole ʻia: pehea e hana ʻia ai ka hana ma (a i ʻole) i kēia ʻōnaehana lawelawe.
"ʻoi aʻe a i ʻole ka liʻiliʻi" - ʻo ia ka mea hiki ke hoʻomākaukau (a kiʻi paha mai kahi wahi) i kahi mea hana, pono, lawelawe i hiki ke hoʻohui ʻia a hoʻopili ʻia i ka ʻōnaehana me kahi haʻahaʻa hoʻāʻo i lawa i ka mea e hana ʻia. i nā kūlana kūpono no ka hana ʻana.
ʻAe, e manaʻo mākou ua ʻike ʻia kahi hoʻonohonoho hoʻonohonoho hoʻoponopono no kēia ʻōnaehana lawelawe, hiki ke hoʻohana ʻia e hoʻonohonoho i kēia ʻōnaehana e pili ana i ka huahana o kāna hana.
A he aha ka pilikia - ʻaʻole lawa ka hoʻomaopopo piha ʻana i kēia ʻōnaehana lawelawe, kahi e hiki ai iā ʻoe ke hoʻonohonoho akamai i nā hoʻonohonoho o kēia ʻōnaehana no ka hoʻouka ʻana i ka wā e hiki mai ana ma kahi kahua i hāʻawi ʻia a loaʻa i ka huahana i koi ʻia o ka ʻōnaehana.
Pono. ʻO kēia ka mea kokoke i nā manawa a pau.
He aha kāu e hana ai ma ʻaneʻi?
ʻAe, ʻo ka mea mua i hiki i ka naʻau ke nānā i nā palapala no kēia ʻōnaehana. E hoʻomaopopo i nā pae i ʻae ʻia no nā waiwai o nā ʻāpana hoʻoponopono. A, no ka laʻana, me ka hoʻohana ʻana i ke ʻano o ka hoʻokaʻawale ʻana, koho i nā waiwai no nā ʻōnaehana ʻōnaehana i nā hoʻokolohua.
ʻO kēlā mau. hāʻawi i ka ʻōnaehana i kekahi ʻano hoʻonohonoho, ma ke ʻano o kahi hoʻonohonoho kikoʻī o nā waiwai no kāna mau ʻāpana hoʻonohonoho.
E noi i ka ukana ho'āʻo iā ia, me ka hoʻohana ʻana i kēia mea hana pono, hoʻoili hoʻoili.
A e nānā i ka waiwai - ka pane, a i ʻole kahi metric o ka maikaʻi o ka ʻōnaehana.
ʻO ka manaʻo ʻelua paha ka hopena he manawa lōʻihi loa kēia.
ʻAe, ʻo ia hoʻi: inā he nui nā ʻāpana hoʻoponopono, inā he nui nā pae o kā lākou mau waiwai i uhi ʻia, inā he nui ka manawa o kēlā me kēia hoʻouka hoʻouka ʻana, a laila: ʻae, hiki i kēia mau mea ke lawe i kahi helu ʻae ʻole. o ka manawa.
ʻAe, eia ka mea hiki iā ʻoe ke hoʻomaopopo a hoʻomanaʻo.
Hiki iā ʻoe ke ʻike i loko o ka hoʻonohonoho o nā waiwai o nā ʻōnaehana hoʻonohonoho lawelawe he vector, e like me ke ʻano o kekahi mau waiwai.
ʻO kēlā me kēia vector, ua like nā mea ʻē aʻe (no ka mea ʻaʻole i hoʻopili ʻia e kēia vector), pili i kahi waiwai paʻa loa o ka metric - kahi hōʻailona o ka maikaʻi o ka hana o ka ʻōnaehana ma lalo o kahi haʻahaʻa hoʻāʻo.
Ie
E hōʻike mākou i ka vector hoʻonohonoho pūnaewele e like me 
kahi ; Mahea — ka helu o nā ʻāpana hoʻonohonoho ʻōnaehana, ʻehia ka nui o kēia mau ʻāpana.
A ʻo ka waiwai o ka metric e pili ana i kēia e hoailona kakou
, a laila loaʻa iā mākou kahi hana:
ʻAe, a laila: hiki koke mai nā mea a pau, i koʻu hihia: aneane poina mai koʻu mau lā haumāna, algorithms no ka ʻimi ʻana i ka extremum o kahi hana.
ʻAe, akā eia kahi nīnau hoʻonohonoho a hoʻopili ʻia: ʻo ia ka algorithm e hoʻohana ai.
- Ma ke ʻano - i hiki iā ʻoe ke helu liʻiliʻi ma ka lima.
- A no ka hana ana, i.e. loaʻa ka extremum (inā loaʻa kekahi), maikaʻi, ʻoi aku ka wikiwiki ma mua o ka hoʻohui ʻana.
Hōʻike ka helu mua e pono mākou e nānā i kekahi mau kaiapuni kahi i hoʻokō mua ʻia ai kēlā mau algorithm, a ma kekahi ʻano, mākaukau no ka hoʻohana ʻana i ke code.
ʻAe, ʻike wau python и cran-r
ʻO ka helu ʻelua, pono ʻoe e heluhelu e pili ana i nā algorithm iā lākou iho, he aha lākou, he aha kā lākou koi, a me nā hiʻohiʻona o kā lākou hana.
A ʻo nā mea a lākou e hāʻawi ai hiki ke lilo i nā hopena ʻaoʻao kūpono - nā hopena, a i ʻole pololei mai ka algorithm ponoʻī.
A i ʻole hiki ke loaʻa iā lākou mai nā hopena o ka algorithm.
Pili ka nui i nā kūlana hoʻokomo.
No ka laʻana, inā, no kekahi kumu, pono ʻoe e kiʻi i kahi hopena wikiwiki, pono, pono ʻoe e nānā i nā algorithm gradient descent a koho i kekahi o lākou.
A i ʻole, inā ʻaʻole koʻikoʻi ka manawa, hiki iā ʻoe, no ka laʻana, hoʻohana i nā ʻano hana stochastic optimization, e like me ka genetic algorithm.
Ke manaʻo nei au e noʻonoʻo i ka hana o kēia ala, ke koho ʻana i ka hoʻonohonoho ʻōnaehana, me ka hoʻohana ʻana i kahi genetic algorithm, i ka wā e hiki mai ana, no laila e ʻōlelo ai: ka hana lab.
Kumu:
- E ʻae, ma ke ʻano he ʻōnaehana lawelawe:
oracle xe 18c - E lawelawe i ka hana transactional a me ka pahuhopu: e kiʻi i ka throughput kiʻekiʻe loa o ka subdatabase, i nā kālepa / sec.
- Hiki ke ʻokoʻa loa nā hana i ke ʻano o ka hana ʻana me ka ʻikepili a me ke ʻano o ka hana.
E ʻae kākou ʻo kēia nā hana ʻaʻole e hana i ka nui o ka ʻikepili papa.
I ka manaʻo ʻaʻole lākou e hoʻohua i ka ʻikepili wehe hou aʻe ma mua o ka hana hou ʻana a ʻaʻole hana i nā pākēneka nui o nā lālani a me nā papa nui.
ʻO kēia nā hana e hoʻololi i hoʻokahi lālani ma kahi pākaukau nui a liʻiliʻi paha, me kahi helu liʻiliʻi o nā kuhikuhi ma kēia papa.
Ma kēia kūlana: e hoʻoholo ʻia ka huahana o ka subdatabase no ka hoʻoili ʻana i nā hana, me ka mālama ʻana, e ka maikaʻi o ka hana ʻana e ka redox database.
Hōʻole - inā mākou e kamaʻilio kikoʻī e pili ana i nā hoʻonohonoho subdb.
No ka mea, ma ka hihia maʻamau, aia paha, no ka laʻana, nā laka transactional ma waena o nā kau SQL, ma muli o ka hoʻolālā ʻana o ka hana o ka mea hoʻohana me ka ʻikepili tabular a / a i ʻole ka papa kuhikuhi.
ʻO ia, ʻoiaʻiʻo, e loaʻa i kahi hopena hoʻohaʻahaʻa i ka metric TPS a he mea exogenous kēia, e pili ana i ka subdatabase: ʻo ia ke ʻano o ka hoʻolālā ʻana o ka papa kuhikuhi a me ka hana me ka ʻikepili i loko o ia mea e hana ai nā blockages.
No laila, no ka maʻemaʻe o ka hoʻokolohua, e kāpae mākou i kēia kumu, a ma lalo wau e wehewehe pololei pehea.
- E noʻonoʻo kākou, no ka maopopo ʻana, ʻo 100% o nā kauoha SQL i waiho ʻia i ka waihona he mau kauoha DML.
E hana like nā ʻano o ka mea hoʻohana me ka subdatabase i nā hoʻokolohua.
ʻO ia hoʻi: ka helu o nā kau skl, ka ʻikepili tabular, pehea e hana ai nā hui skl me lākou. - Ke hana nei ʻo Subd
FORCE LOGGING,ARCHIVELOGmods. Ua pio ke ano Flashback-database, ma ka pae subd. - Hana hou i nā lāʻau: aia ma kahi ʻōnaehana faila ʻokoʻa, ma kahi "disk" ʻokoʻa;
ʻO ke koena o ka ʻāpana kino o ka waihona: ma kahi ʻē aʻe, ʻōnaehana faila kaʻawale, ma kahi "disk" ʻokoʻa:
Nā kikoʻī hou aku e pili ana i ka mea kino kino. nā ʻāpana waihona ʻikepili
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 0I ka wā mua, ma lalo o kēia mau ukana, makemake wau e hoʻohana i ka subd transaction
He hiʻohiʻona maikaʻi loa ia, e haʻi wau i ka mea kākau:
Aia ma ka puʻuwai o SLOB ke ʻano "SLOB". Ke manaʻo nei ka SLOB Method e hoʻāʻo i nā paepae
me ka hoopaapaa noi. ʻAʻole hiki i kekahi ke hoʻokele i ka hana ʻoi loa
me ka hoʻohana ʻana i ke code application, no ka laʻana, i hoʻopaʻa ʻia e ka laka noi a i ʻole
kaʻana like ʻana i nā poloka ʻo Oracle Database. Pololei - aia ke poʻo i ke kaʻana like ʻana i ka ʻikepili
i nā poloka ʻikepili! Akā ʻo SLOB-i kāna hoʻonohonoho paʻa ʻana-ʻaʻole i pale i kēlā paio.
ʻO kēia ʻōlelo: pili, ʻo ia.
He mea maʻalahi ka hoʻoponopono ʻana i ka pae o ka parallelism o nā kau cl, ʻo ia ke kī -t hoʻolana i ka pono runit.sh mai SLOB
Hoʻoponopono ʻia ka pākēneka o nā kauoha DML, i ka helu o nā leka i hoʻouna ʻia i ka subd, kēlā me kēia kau kikokikona, ʻāpana. UPDATE_PCT
Hoʻokaʻawale a maʻalahi loa: SLOB pono'ī, ma mua a ma hope o ke kau hoʻouka - hoʻomākaukau i kahi statspack, a i ʻole awr-snapshots (ka mea i hoʻonohonoho ʻia e hoʻomākaukau).
Eia naʻe, ua ʻike ʻia kēlā SLOB ʻaʻole kākoʻo i nā kau SQL me ka lōʻihi o lalo o 30 kekona.
No laila, ua hoʻopaʻa mua wau i kaʻu iho, ka mea hana-mahina o ka loader, a laila hoʻomau i ka hana.
E wehewehe wau i ka hana a ka mea hoʻoili a pehea e hana ai, no ka maopopo.
ʻO ka mea nui ke ʻano o ka loader penei:
Code limahana
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 dotxHoʻokumu ʻia nā mea hana ma kēia ala:
Nā limahana holo
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"
waitA ua hoomakaukauia na papaaina e like me keia.
Ke hana nei i nā papa
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"ʻO kēlā mau. No kēlā me kēia limahana (maʻamau: kahi hui SQL ʻokoʻa i ka DB) hana ʻia kahi papa ʻokoʻa, kahi e hana ai ka mea hana.
Mālama kēia i ka nele o nā laka transactional ma waena o nā kau limahana.
ʻO kēlā me kēia mea hana: hana i ka mea like, me kāna papaʻaina pono'ī, ua like nā papaʻaina.
Hana nā limahana a pau i ka hana no ka manawa like.
Eia kekahi, no ka manawa lōʻihi no laila, no ka laʻana, e kū maoli ana kahi hoʻololi log, a ʻoi aku ma mua o hoʻokahi.
ʻAe, no laila, ua ala mai nā kumukūʻai pili a me nā hopena.
I koʻu hihia, ua hoʻonohonoho au i ka lōʻihi o ka hana a nā limahana ma 8 mau minuke.
He ʻāpana o kahi hōʻike statspack e wehewehe ana i ka hana o ka subd ma lalo o ka ukana
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.1Ke hoʻi nei i ka hana hoʻokolohua.
E like mākou, nā mea ʻē aʻe, e hoʻololi i nā waiwai o nā ʻāpana o ka subdatabase lab.
- Ka nui o nā pūʻulu waihona waihona. pae waiwai: [32, 1024] MB;
- Ka helu o nā hui puke pai ma ka waihona. pae waiwai: [2,32];
log_archive_max_processespae waiwai: [1,8];commit_loggingʻAe ʻia nā waiwai ʻelua:batch|immediate;commit_waitʻAe ʻia nā waiwai ʻelua:wait|nowait;log_bufferlaula waiwai: [2,128] MB.log_checkpoint_timeoutpae waiwai: [60,1200] kekonadb_writer_processeslaula waiwai: [1,4]undo_retentionpae waiwai: [30;300] kekonatransactions_per_rollback_segmentlaula waiwai: [1,8]disk_asynch_ioʻAe ʻia nā waiwai ʻelua:true|false;filesystemio_optionsua ʻae ʻia kēia mau waiwai:none|setall|directIO|asynch;db_block_checkingua ʻae ʻia kēia mau waiwai:OFF|LOW|MEDIUM|FULL;db_block_checksumua ʻae ʻia kēia mau waiwai:OFF|TYPICAL|FULL;
Hiki i ke kanaka me ka ʻike i ka mālama ʻana i nā ʻikepili Oracle ke ʻōlelo i ka mea a me nā waiwai e hoʻonohonoho ʻia, mai nā ʻāpana i ʻōlelo ʻia a me kā lākou mau waiwai i ʻae ʻia, i mea e loaʻa ai ka huahana nui o ka waihona no ka hana me ka ʻikepili i hōʻike ʻia e ka palapala noi, ma ʻaneʻi ma luna.
Akā.
ʻO ke kumu o ka hana hoʻokolohua ʻo ia ka hōʻike ʻana i ka algorithm optimization ponoʻī e wehewehe i kēia no mākou me ka wikiwiki.
No mākou, ʻo ka nānā wale ʻana i ka palapala, ma o ka ʻōnaehana maʻamau, lawa wale nō e ʻike i nā ʻāpana e hoʻololi ai a me nā pae.
A eia pū kekahi: code i ke code e hoʻohana ʻia e hana me ka ʻōnaehana maʻamau o ka algorithm optimization i koho ʻia.
No laila, i kēia manawa e pili ana i ke code.
Ua kamaʻilio wau ma luna cran-r, ʻo ia hoʻi: ua hoʻonohonoho ʻia nā manipulations āpau me ka ʻōnaehana maʻamau i ke ʻano o kahi palapala R.
ʻO ka hana maoli, ka nānā ʻana, ke koho ʻana e ka waiwai metric, nā vectors state state: he pūʻolo kēia GA ()
ʻO ka pūʻolo, i kēia hihia, ʻaʻole kūpono loa, ma ke ʻano he manaʻo ia i nā vectors (chromosomes, inā ma ke ʻano o ka pūʻolo) e kuhikuhi ʻia ma ke ʻano o nā kaula helu me kahi hapa hapa.
A ʻo kaʻu vector, mai nā waiwai o nā ʻāpana hoʻonohonoho: he 14 nui kēia - integers a me nā waiwai string.
ʻO ka pilikia, ʻoiaʻiʻo, hiki ke pale ʻia ma ka hāʻawi ʻana i kekahi mau helu kikoʻī i nā waiwai string.
No laila, i ka hopena, ʻo ka ʻāpana nui o ka palapala R e like me kēia:
Kāhea iā 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@solutionEia, me ke kōkua lower и upper nā hiʻohiʻona subroutine ga ʻO ka mea nui, ua kuhikuhi ʻia kahi wahi o ka wahi hulina, kahi e hana ʻia ai kahi hulina no kēlā vector (a i ʻole vectors) kahi e loaʻa ai ka waiwai kiʻekiʻe o ka hana hoʻoikaika kino.
Hana ka subroutine i kahi hulina e hoʻonui i ka hana hoʻoikaika kino.
ʻAe, a laila, ʻike ʻia, i kēia hihia, pono ka hana hoʻoikaika kino, ka hoʻomaopopo ʻana i ka vector ma ke ʻano he hoʻonohonoho waiwai no kekahi mau ʻāpana o ka subd, e loaʻa i kahi metric mai ka subd.
ʻO ia: ʻehia, me kahi hoʻonohonoho subd i hāʻawi ʻia a me kahi haʻawina i hāʻawi ʻia ma ka subd: nā subd kaʻina hana i kēlā me kēia kekona.
ʻO ia hoʻi, i ka wā e wehe ai, pono e hana ʻia kēia mau ʻanuʻu he nui i loko o ka hana hoʻoikaika kino:
- Ke kaʻina ʻana i ka vector input o nā helu - hoʻololi iā ia i nā waiwai no nā ʻāpana subdata.
- He ho'āʻo e hana i kekahi helu o nā pūʻulu hana hou o ka nui i hāʻawi ʻia. Eia kekahi, ʻaʻole i kūleʻa ka hoʻāʻo ʻana.
Nā pūʻulu Magazine i noho mua ma ka subd, ma kekahi nui a me kekahi nui, no ka maʻemaʻe o ka hoʻokolohua - d.b. holoi ʻia. - Inā kūleʻa ka helu mua: e hōʻike ana i nā waiwai o nā ʻāpana hoʻonohonoho i ka waihona (eia hou: aia paha ka hemahema)
- Inā kūleʻa ka hana mua: hoʻōki i ka subd, hoʻomaka i ka subd i mea e hoʻokō ai nā koina koho hou. (eia hou: aia paha ka hewa)
- Inā kūleʻa ka hana mua: e hana i kahi hoʻāʻo hoʻouka. kiʻi i nā metric mai ka subd.
- E hoʻihoʻi i ka subd i kona kūlana mua, ʻo ia hoʻi. holoi i nā pūʻulu lāʻau hou, hoʻihoʻi i ka hoʻonohonoho subdatabase kumu e hana.
Code hana kino
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)
}ʻO kēlā. nā hana a pau: hana ʻia ma ka hana hoʻoikaika kino.
ʻO nā kaʻina hana ga-subroutine nā vectors, a i ʻole, ʻoi aku ka pololei, nā chromosomes.
I loko o ia mea, ʻo ka mea nui loa iā mākou ke koho ʻana i nā chromosomes me nā genes kahi e hana ai ka hana hoʻoikaika kino i nā waiwai nui.
ʻO kēia, ma ke ʻano, ʻo ia ke kaʻina o ka ʻimi ʻana i ka hoʻonohonoho maikaʻi o nā chromosomes me ka hoʻohana ʻana i kahi vector i kahi wahi hulina N-dimensional.
Akaka loa, kiko'ī , me nā hiʻohiʻona o R-code, ka hana o kahi genetic algorithm.
Makemake au e hoʻokaʻawale i ʻelua mau mea ʻenehana.
Kāhea kōkua mai ka hana evaluate, no ka laʻana, stop-start, hoʻonohonoho i ka waiwai o ka subd parameter, hana ʻia ma muli o cran-r nā hana system2
Me ke kōkua o ia: ua kapa ʻia kekahi bash script a kauoha paha.
Eia kekahi laʻana:
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)
}
}ʻO ka helu ʻelua ka laina, evaluate nā hana, me ka mālama ʻana i kahi waiwai metric kikoʻī a me kāna vector hoʻoponopono e pili ana i kahi faila log:
cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="n", append=T)He mea koʻikoʻi kēia, no ka mea, mai kēia pūʻulu ʻikepili, hiki ke loaʻa ka ʻike hou aku e pili ana i nā ʻāpana o ka vector tuning i ʻoi aku ka nui a i ʻole ka hopena liʻiliʻi i ka waiwai metric.
ʻO ia hoʻi: hiki ke hana i ka nānā ʻana i ka attribute-importamce.
No laila he aha ka mea hiki ke hana?
Ma ka palapala kiʻi, inā ʻoe e kauoha i nā hoʻāʻo ma ka papa metric piʻi, penei ke kiʻi:
ʻO kekahi mau ʻikepili e pili ana i nā waiwai koʻikoʻi o ka metric:
Ma ʻaneʻi, i ka kiʻi kiʻi me nā hopena, e wehewehe wau: ua hāʻawi ʻia nā waiwai o ka vector tuning e pili ana i ke code function fitness, ʻaʻole i ka helu o ka helu helu o nā ʻāpana / pae o nā koina parameter, i kūkulu ʻia. ma luna o ka kikokikona.
Pono. He nui a he uuku paha, ~ 8 tausani tps: he ninau okoa.
I loko o ka hoʻolālā o ka hana lab, ʻaʻole koʻikoʻi kēia kiʻi, ʻo ka mea nui ka dynamics, pehea e loli ai kēia waiwai.
Maikaʻi nā dynamics ma aneʻi.
ʻIke ʻia ʻo ka liʻiliʻi o hoʻokahi kumu e hoʻololi nui i ka waiwai o ka metric, ka ga-algorithm, e hoʻokaʻawale i nā vectors chromosome: uhi ʻia.
Ma ka hoʻoholo ʻana i ka hoʻoikaika ikaika o nā waiwai curve, aia ma kahi liʻiliʻi hoʻokahi kumu ʻē aʻe, ʻoiai ʻoi aku ka liʻiliʻi, he mana.
ʻO kēia kahi āu e pono ai attribute-importance ka nānā ʻana e hoʻomaopopo i nā ʻano (maikaʻi, i kēia hihia, nā ʻāpana o ka vector tuning) a me ka nui o kā lākou hoʻololi ʻana i ka waiwai metric.
A mai kēia ʻike: e hoʻomaopopo i nā mea i hoʻopili ʻia e nā loli i nā ʻano koʻikoʻi.
E holo attribute-importance hiki i nā ʻano like ʻole.
No kēia mau kumu, makemake wau i ka algorithm randomForest R pūʻolo o ka inoa like ()
randomForest, e like me ko'u hoomaopopo ana i kana hana ma ke ano nui a me kona manao ana i ka noonoo ana i ke ko'iko'i o na ano, ke kukulu nei i kekahi kumu hoohalike o ka hilinai ana o ka pane pane ma na ano.
I kā mākou hihia, ʻo ka pane pane he metric i loaʻa mai ka waihona i nā hoʻāʻo hoʻouka: tps;
A ʻo nā hiʻohiʻona nā ʻāpana o ka vector tuning.
No laila maanei randomForest loiloi i ke koʻikoʻi o kēlā me kēia ʻano hoʻohālike me nā helu ʻelua: %IncMSE — pehea ka hoʻololi ʻana o kēia ʻano i loko o kahi kumu hoʻohālike i ka maikaʻi o MSE o kēia kumu hoʻohālike (Mean Squared Error);
A ʻo IncNodePurity kahi helu e hōʻike ana i ka maikaʻi, e pili ana i nā waiwai o kēia ʻano, hiki ke hoʻokaʻawale ʻia kahi ʻikepili me nā ʻike, no laila ma kahi ʻāpana he ʻikepili me kahi waiwai o ka metric i wehewehe ʻia, a ma kekahi me ka kekahi waiwai o ke ana.
ʻAe, ʻo ia hoʻi: pehea ka nui o kēia ʻano hoʻohālikelike (ʻike wau i ka wehewehe ʻana i ka ʻōlelo Lūkini ma RandomForest ).
R-code no ka hana ʻana i kahi ʻikepili me nā hopena o nā hoʻokolohua hoʻouka:
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$importanceHiki iā ʻoe ke koho pololei i nā hyperparameters o ka algorithm me kou mau lima a, me ka nānā ʻana i ka maikaʻi o ke kumu hoʻohālike, koho i kahi hoʻohālike e hoʻokō pono ai i nā wānana ma ka ʻikepili hōʻoia.
Hiki iā ʻoe ke kākau i kekahi ʻano hana no kēia hana (ma ke ala, e hoʻohana hou i kekahi ʻano algorithm optimization).
Hiki iā ʻoe ke hoʻohana i ka pūʻolo R caret, ʻaʻole ka manaʻo nui.
ʻO ka hopena, i kēia hihia, loaʻa kēia hopena e loiloi i ke kiʻekiʻe o ke koʻikoʻi o nā ʻano:
Pono. No laila, hiki iā mākou ke hoʻomaka i ka noʻonoʻo honua:
- ʻIke ʻia ʻo ka mea nui loa, ma lalo o kēia mau kūlana hoʻāʻo, ʻo ia ka ʻāpana
commit_wait
Ma keʻano loea, kuhikuhi ia i ke ʻano hoʻokō o ka hana io o ke kākau ʻana i ka ʻikepili hou mai ka subdb log buffer i ka pūʻulu log o kēia manawa: synchronous a i ʻole asynchronous.
waiwainowaitʻo ia ka hopena i kahi kokoke kū pololei, hoʻonui nui i ka waiwai o ka metric tps: ʻo kēia ka hoʻokomo ʻana o ke ʻano io asynchronous i nā hui redo.
ʻO kahi nīnau kaʻawale inā pono ʻoe e hana i kēia ma kahi waihona meaʻai. Eia wau e kaupalena iaʻu iho i ka ʻōlelo ʻana: he mea nui kēia. - He mea kūpono ka nui o ka log buffer o ka subd: huli i ka mea nui.
ʻO ka liʻiliʻi o ka nui o ka lāʻau paʻa, ʻo ka liʻiliʻi o kona hiki ke hoʻopaʻa ʻia, ʻoi aku ka nui o ke kahe ʻana a/a i ʻole ka hiki ʻole ke hoʻokaʻawale i kahi wahi manuahi i loko ona no kahi hapa o ka ʻikepili redox hou.
ʻO ia hoʻi: nā lohi e pili ana i ka hoʻokaʻawale ʻana i ka manawa ma ka pahu pahu a/a i ʻole ka hoʻolei ʻana i ka ʻikepili hana hou mai ia mea i loko o nā pūʻulu hana hou.
ʻO kēia mau lohi, ʻoiaʻiʻo, pono a pili i ka throughput o ka waihona no nā kālepa. - ʻO ka pākuhi
db_block_checksum: maikaʻi hoʻi, ma ka laulā he maopopo - ke alakaʻi nei ka hoʻoili ʻana i ka hana i ka hoʻokumu ʻana i nā poloka darty i loko o ka cache buffer o ka subdatabase.
ʻO ia, i ka wā e hiki ai ke nānā i nā checksums o nā datablocks, pono e hana ka waihona - e helu i kēia mau checksums mai ke kino o ka datablock, e nānā iā lākou me nā mea i kākau ʻia ma ke poʻomanaʻo datablock: pili / ʻaʻole kūlike.
ʻO ia mau hana, ʻaʻole hiki ke hoʻopaneʻe i ka hoʻoili ʻana i ka ʻikepili, a no laila, ʻo ke ʻano a me ke ʻano hana e hoʻonohonoho ai i kēia ʻāpana he mea nui.
ʻO ia ke kumu e hāʻawi ai ka mea kūʻai aku, i loko o ka palapala no kēia ʻāpana, nā waiwai like ʻole no ia (ka parameter) a me nā memo e ʻae, aia ka hopena, akā, maikaʻi, hiki iā ʻoe ke koho i nā waiwai like ʻole, a hiki i ka "off" a nā hopena like ʻole.
ʻAe, he hopena honua.
ʻO ke ala, ma keʻano holoʻokoʻa, ua lilo i mea hana.
ʻAe ʻo ia iā ia iho, i ka wā mua o ka hoʻāʻo ʻana i kahi ʻōnaehana lawelawe, i mea e koho ai i kāna (pūnaewele) hoʻonohonoho maikaʻi loa no ka ukana, ʻaʻole e ʻimi nui i nā kikoʻī o ka hoʻonohonoho ʻana i ka ʻōnaehana no ka ukana.
Akā ʻaʻole ia e hoʻokaʻawale loa - ma ka liʻiliʻi ma ka pae o ka ʻike: pono e ʻike ʻia ka ʻōnaehana e pili ana i nā "hoʻoponopono knobs" a me nā pae i ʻae ʻia o ka hoʻololi ʻana o kēia mau kī.
Hiki i ka hoʻokokoke ke loaʻa koke i ka hoʻonohonoho ʻōnaehana maikaʻi loa.
A ma muli o nā hopena o ka hoʻāʻo ʻana, hiki ke loaʻa ka ʻike e pili ana i ke ʻano o ka pilina ma waena o nā metric hana ʻōnaehana a me nā waiwai o nā ʻāpana hoʻonohonoho ʻōnaehana.
ʻO ia, ʻoiaʻiʻo, e kōkua i ka puka ʻana o kēia ʻike hohonu loa o ka ʻōnaehana, kāna hana, ma lalo o kahi haʻahaʻa i hāʻawi ʻia.
Ma ka hoʻomaʻamaʻa, he hoʻololi kēia o nā kumukūʻai no ka hoʻomaopopo ʻana i ka ʻōnaehana maʻamau no nā kumukūʻai o ka hoʻomākaukau ʻana i ia hoʻokolohua o ka ʻōnaehana.
Makemake wau e hoʻomaopopo kaʻawale: ma kēia ala, he mea nui ke kiʻekiʻe o ka lawa o ka hoʻāʻo ʻana o ka ʻōnaehana i nā kūlana hana e loaʻa iā ia i ka hana ʻoihana.
Mahalo iā ʻoe no kou nānā ʻana a me kou manawa.
Source: www.habr.com