🥇Scientific poking method, los yog yuav ua li cas xaiv subdatabase configuration siv benchmarks thiab optimization algorithm

Nyob zoo

Kuv txiav txim siab los qhia kuv qhov kev pom - cov txiv hmab txiv ntoo ntawm kev xav, kev sim thiab kev ua yuam kev.
Los ntawm thiab loj: qhov no tsis yog ib qho kev pom, tau kawg - tag nrho cov no yuav tsum tau paub ntev, rau cov neeg uas koom nrog hauv kev siv cov ntaub ntawv txheeb xyuas thiab kev ua kom zoo ntawm txhua lub tshuab, tsis tas yuav tsum yog DBMS.
Thiab: yog, lawv paub, lawv sau cov lus nthuav qhia ntawm lawv cov kev tshawb fawb, Piv txwv (UPD.: hauv cov lus lawv tau taw qhia txog qhov project nthuav dav: ottertune )
Ntawm qhov tod tes: offhand Kuv tsis pom muaj kev nthuav dav lossis kev tshaj tawm ntawm txoj hauv kev no hauv Is Taws Nem ntawm cov kws tshaj lij IT, DBA.

Yog li, mus rau qhov chaw.

Cia peb xav tias peb muaj ib txoj hauj lwm: los teeb tsa ib qho kev pabcuam rau kev pabcuam qee yam haujlwm.

Nws paub txog txoj haujlwm no: nws yog dab tsi, qhov ua tau zoo ntawm qhov kev ua haujlwm no yog ntsuas, thiab qhov ntsuas qhov zoo li cas yog qhov ntsuas.

Cia peb xav tias nws paub ntau dua lossis tsawg dua thiab nkag siab: raws nraim li cas kev ua haujlwm tau ua hauv (lossis nrog) qhov kev pabcuam no.

"Ntau lossis tsawg" - qhov no txhais tau hais tias nws muaj peev xwm npaj tau (lossis tau txais los ntawm qhov chaw) ib qho cuab yeej, kev siv hluav taws xob, kev pabcuam uas tuaj yeem tsim kho thiab siv rau lub kaw lus nrog kev sim ntsuas kom txaus rau qhov yuav ua li cas, nyob rau hauv tej yam kev mob txaus txaus rau kev ua hauj lwm nyob rau hauv ntau lawm.

Zoo, cia peb xav tias cov txheej txheem hloov kho rau qhov kev pabcuam no tau paub, uas tuaj yeem siv los kho qhov system no raws li kev tsim khoom ntawm nws txoj haujlwm.

Thiab dab tsi yog qhov teeb meem - tsis muaj kev nkag siab txaus ntawm cov kev pabcuam no, ib qho uas tso cai rau koj los tsim kho cov teeb meem ntawm qhov system no rau yav tom ntej ntawm lub platform muab thiab tau txais cov khoom tsim nyog ntawm qhov system.

Zoo. Qhov no yuav luag txhua zaus.

Koj tuaj yeem ua dab tsi ntawm no?

Zoo, thawj yam uas los rau hauv siab yog saib cov ntaub ntawv rau qhov system no. Nkag siab tias qhov kev lees paub zoo li cas rau qhov tseem ceeb ntawm qhov kev hloov pauv tsis tau. Thiab, piv txwv li, siv txoj kev sib koom ua ke qhovntsej thiaj tsis mob, xaiv qhov tseem ceeb rau cov kab ke hauv kev sim.

Cov. muab lub kaw lus ib co configuration, nyob rau hauv daim ntawv ntawm ib tug tshwj xeeb txheej ntawm qhov tseem ceeb rau nws configuration tsis.

Siv ib qho kev sim load rau nws, siv cov cuab yeej siv hluav taws xob no, lub tshuab hluav taws xob thauj khoom.
Thiab saib tus nqi - cov lus teb, lossis ib qho kev ntsuas ntawm qhov zoo ntawm qhov system.

Qhov kev xav thib ob yuav yog qhov xaus tias qhov no yog lub sijhawm ntev heev.

Zoo, qhov ntawd yog: yog tias muaj ntau qhov kev teeb tsa, yog tias qhov ntau ntawm lawv cov txiaj ntsig tau khiav yog qhov loj, yog tias txhua tus neeg thauj khoom yuav siv sijhawm ntau los ua kom tiav, ces: yog, tag nrho cov no yuav siv sij hawm tsis txaus. ntev.

Zoo, ntawm no yog qhov koj tuaj yeem nkag siab thiab nco qab.

Koj tuaj yeem pom tias nyob rau hauv cov txheej txheem ntawm qhov tseem ceeb ntawm cov kev pabcuam kev teeb tsa tsis muaj qhov vector, raws li cov kab ke ntawm qee qhov tseem ceeb.

Txhua yam vector, lwm yam sib npaug (hauv qhov uas nws tsis cuam tshuam los ntawm vector), sib raug rau tus nqi tseeb ntawm qhov ntsuas - qhov taw qhia ntawm qhov zoo ntawm qhov kev ua haujlwm nyob rau hauv kev sim load.

I.e.

Cia peb qhia qhov system configuration vector li qhov twg ; Qhov twg - tus naj npawb ntawm qhov system configuration tsis, pes tsawg ntawm cov tsis muaj.

Thiab tus nqi ntawm cov metric sib raug rau qhov no cia peb txhais nws li
Tom qab ntawd peb tau txais ib txoj haujlwm:

Zoo, tom qab ntawd: txhua yam tam sim ntawd los rau, hauv kuv rooj plaub: yuav luag tsis nco qab los ntawm kuv cov tub ntxhais kawm hnub, algorithms rau kev tshawb nrhiav qhov kawg ntawm kev ua haujlwm.

Okay, tab sis ntawm no yog ib lub koom haum thiab siv lo lus nug tshwm sim: uas algorithm siv.

Hauv kev nkag siab - yog li ntawd koj tuaj yeem code tsawg dua los ntawm tes.
Thiab rau nws ua haujlwm, i.e. pom qhov extremum (yog tias muaj ib qho), zoo, tsawg kawg yog ceev tshaj kev sib koom tes qhovntsej thiaj tsis mob.

Thawj lub ntsiab lus qhia tias peb yuav tsum tau saib rau qee qhov chaw nyob rau hauv uas xws li algorithms twb tau ua tiav, thiab, nyob rau hauv ib co qauv, npaj rau siv nyob rau hauv code.
Zoo, kuv paub python и cran-r

Qhov thib ob txhais tau hais tias koj yuav tsum tau nyeem txog cov algorithms lawv tus kheej, lawv yog dab tsi, lawv cov kev xav tau yog dab tsi, thiab cov yam ntxwv ntawm lawv txoj haujlwm.

Thiab dab tsi lawv muab tuaj yeem ua rau muaj txiaj ntsig zoo - cov txiaj ntsig, lossis ncaj qha los ntawm algorithm nws tus kheej.

Los yog lawv tuaj yeem tau txais los ntawm cov txiaj ntsig ntawm algorithm.

Muaj ntau nyob ntawm qhov kev nkag siab.

Piv txwv li, yog tias, rau qee qhov laj thawj, koj yuav tsum tau txais cov txiaj ntsig sai dua, zoo, koj yuav tsum tau saib ntawm gradient qhovntsej thiaj tsis mob algorithms thiab xaiv ib qho ntawm lawv.

Los yog, yog tias lub sijhawm tsis tseem ceeb, koj tuaj yeem, piv txwv li, siv cov kev ua kom zoo tshaj plaws, xws li cov noob caj noob ces algorithm.

Kuv thov kom txiav txim siab txog kev ua haujlwm ntawm txoj hauv kev no, xaiv cov txheej txheem teeb tsa, siv cov txheej txheem genetic algorithm, hauv qab no, yog li hais: kev ua haujlwm hauv chav kuaj.

Original:

Cia muaj, raws li kev pabcuam system: oracle xe 18c
Cia nws ua haujlwm kev lag luam thiab lub hom phiaj: kom tau txais qhov ua tau zoo tshaj plaws ntawm cov subdatabase, hauv kev hloov pauv / sec.
Kev ua lag luam tuaj yeem sib txawv ntawm qhov ua haujlwm nrog cov ntaub ntawv thiab cov ntsiab lus ntawm kev ua haujlwm.
Cia peb pom zoo tias cov no yog cov kev lag luam uas tsis ua cov ntaub ntawv loj loj.
Hauv kev nkag siab tias lawv tsis tsim ntau cov ntaub ntawv undo dua li redo thiab tsis ua cov feem pua ntawm cov kab thiab cov ntxhuav loj.

Cov no yog cov kev hloov pauv uas hloov ib kab hauv ib lub rooj ntau dua lossis tsawg dua, nrog rau cov lej me me ntawm cov lus no.

Hauv qhov xwm txheej no: kev tsim khoom ntawm subdatabase rau kev ua lag luam yuav, nrog rau kev tshwj tseg, txiav txim siab los ntawm kev ua haujlwm zoo los ntawm redox database.

Kev tsis lees paub - yog tias peb tham tshwj xeeb txog kev teeb tsa subdb.

Vim hais tias, nyob rau hauv cov ntaub ntawv, tej zaum yuav muaj, piv txwv li, kev lag luam locks ntawm SQL zaug, vim tus tsim ntawm cov neeg siv ua hauj lwm nrog cov ntaub ntawv tabular thiab / los yog cov qauv tabular.

Qhov twg, tau kawg, yuav muaj kev nyuaj siab rau TPS metric thiab qhov no yuav yog ib qho exogenous, txheeb ze rau subdatabase: zoo, qhov no yog li cas tus qauv tabular tau tsim thiab ua hauj lwm nrog cov ntaub ntawv nyob rau hauv uas blockages tshwm sim.

Yog li ntawd, rau qhov purity ntawm qhov kev sim, peb yuav tshem tawm qhov teeb meem no, thiab hauv qab no kuv yuav qhia meej raws nraim li cas.

Cia peb xav tias, rau qhov tseeb, tias 100% ntawm SQL cov lus txib xa mus rau cov ntaub ntawv yog DML cov lus txib.
Cia tus yam ntxwv ntawm tus neeg siv ua haujlwm nrog subdatabase yog tib yam hauv kev sim.
Namely: tus naj npawb ntawm skl zaug, cov ntaub ntawv tabular, yuav ua li cas skl zaug ua haujlwm nrog lawv.
Subd ua haujlwm hauv FORCE LOGGING, ARCHIVELOG mods. Flashback-database hom raug muab tua, ntawm qib subd.
Rov ua dua cov cav: nyob rau hauv ib qho kev sib cais cov ntaub ntawv, ntawm ib qho "disk" cais;
Tus so ntawm lub cev tivthaiv ntawm lub database: nyob rau hauv lwm yam, cais cov ntaub ntawv system, ntawm ib tug cais "disk":

Cov lus qhia ntxiv txog lub cev lub cev. Laboratory database Cheebtsam

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 0

Thaum pib, nyob rau hauv cov xwm txheej no, kuv xav siv kev hloov pauv subd SLOB-utility
Nws muaj xws li ib tug zoo feature, kuv yuav hais tus sau:

Hauv plawv ntawm SLOB yog "SLOB txoj kev." SLOB Method aims mus sim platforms
tsis muaj kev sib cav sib ceg. Ib tug tsis tuaj yeem tsav qhov kev ua haujlwm siab tshaj plaws ntawm kev kho vajtse
siv daim ntawv thov code uas yog, piv txwv li, khi los ntawm daim ntawv thov xauv los yog txawm
sib koom Oracle Database blocks. Yog lawm - muaj nyiaj siv ua haujlwm thaum sib qhia cov ntaub ntawv
hauv cov ntaub ntawv thaiv! Tab sis SLOB-hauv nws qhov kev xa tawm ua ntej-yog kev tiv thaiv kev sib cav sib ceg.

Cov lus tshaj tawm no: sib raug, nws yog.
Nws yog qhov yooj yim los tswj cov qib ntawm parallelism ntawm cl zaug, qhov no yog qhov tseem ceeb -t tso lub tshuab hluav taws xob runit.sh los ntawm SLOB
Qhov feem pua ntawm DML cov lus txib yog tswj hwm, nyob rau hauv tus naj npawb ntawm cov ntawv nyeem uas xa mus rau subd, txhua qhov kev sib tham, cov ntawv UPDATE_PCT
Cais thiab yooj yim heev: SLOB nws tus kheej, ua ntej thiab tom qab lub sijhawm thauj khoom - npaj ib qho statspack, lossis awr-snapshots (dab tsi yog npaj los npaj).

Txawm li cas los xij, nws muab tawm tias SLOB tsis txhawb SQL ntu nrog lub sijhawm tsawg dua 30 vib nas this.
Yog li ntawd, kuv thawj zaug coded kuv tus kheej, tus neeg ua haujlwm-peasant version ntawm lub loader, thiab tom qab ntawd nws tseem ua haujlwm.

Cia kuv qhia meej tias lub loader ua li cas thiab nws ua li cas, kom paub meej.
Qhov tseem ceeb ntawm lub loader zoo li no:

Tus neeg ua haujlwm code

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 dotx

Cov neeg ua haujlwm tau pib ua li no:

Cov neeg ua haujlwm khiav

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"
wait

Thiab cov rooj rau cov neeg ua haujlwm tau npaj zoo li no:

Tsim cov rooj

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"

Cov. Rau txhua tus neeg ua haujlwm (xws li: sib cais SQL hauv DB) ib lub rooj sib cais yog tsim, uas tus neeg ua haujlwm ua haujlwm.

Qhov no ua kom tsis muaj kev sib pauv hloov pauv ntawm cov neeg ua haujlwm sib tham.
Txhua tus neeg ua haujlwm: ua ib yam, nrog nws tus kheej lub rooj, cov rooj yog tib yam.
Txhua tus neeg ua haujlwm ua haujlwm rau tib lub sijhawm.
Ntxiv mus, rau lub sijhawm ntev txaus yog li ntawd, piv txwv li, lub cav hloov yuav tshwm sim, thiab ntau dua ib zaug.
Zoo, raws li, cov nqi cuam tshuam thiab cov teebmeem tshwm sim.
Hauv kuv qhov xwm txheej, kuv tau teeb tsa lub sijhawm ntawm cov neeg ua haujlwm ua haujlwm ntawm 8 feeb.

Ib daim ntawv qhia statspack piav qhia txog kev ua haujlwm ntawm subd hauv qab load

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.1

Rov qab mus rau qhov chaw ua haujlwm.
Peb yuav, lwm yam uas yog sib npaug, sib txawv qhov tseem ceeb ntawm cov nram qab no tsis ntawm lub chaw soj nstuam subdatabase:

Qhov loj ntawm database log pawg. tus nqi ntau: [32, 1024] MB;
Tus naj npawb ntawm pawg ntawv xov xwm hauv cov ntaub ntawv. tus nqi ntau: [2,32];
log_archive_max_processes tus nqi ntau: [1,8];
commit_logging ob qhov txiaj ntsig tau tso cai: batch|immediate;
commit_wait ob qhov txiaj ntsig tau tso cai: wait|nowait;
log_buffer Tus nqi ntau: [2,128] MB.
log_checkpoint_timeout Tus nqi ntau: [60,1200] vib nas this
db_writer_processes Tus nqi ntau: [1,4]
undo_retention Tus nqi ntau: [30; 300] vib nas this
transactions_per_rollback_segment Tus nqi ntau: [1,8]
disk_asynch_io ob qhov txiaj ntsig tau tso cai: true|false;
filesystemio_options cov txiaj ntsig hauv qab no tau tso cai: none|setall|directIO|asynch;
db_block_checking cov txiaj ntsig hauv qab no tau tso cai: OFF|LOW|MEDIUM|FULL;
db_block_checksum cov txiaj ntsig hauv qab no tau tso cai: OFF|TYPICAL|FULL;

Ib tus neeg uas muaj kev paub txog kev tswj hwm Oracle databases yeej muaj peev xwm hais tau dab tsi thiab qhov tseem ceeb yuav tsum tau teeb tsa, los ntawm cov kev txwv tsis pub dhau thiab lawv cov txiaj ntsig tau txais txiaj ntsig, txhawm rau kom tau txais txiaj ntsig ntau dua ntawm cov ntaub ntawv rau kev ua haujlwm nrog cov ntaub ntawv uas tau qhia los ntawm daim ntawv thov code, ntawm no saum toj no.

Tab sis.

Lub ntsiab lus ntawm kev sim ua haujlwm yog qhia tias qhov kev ua kom zoo tshaj plaws ntawm nws tus kheej yuav qhia meej qhov no rau peb sai sai.

Rau peb, txhua yam uas tseem tshuav yog saib mus rau hauv cov ntaub ntawv, los ntawm cov txheej txheem customizable, tsuas yog txaus kom paub seb qhov kev hloov pauv dab tsi thiab nyob rau qhov twg.
Thiab tseem: cov lej code uas yuav siv los ua haujlwm nrog cov txheej txheem kev cai ntawm cov kev xaiv optimization algorithm.

Yog li, tam sim no hais txog cov cai.
Kuv tham saum toj no txog cran-r, i.e.: tag nrho cov manipulations nrog lub customized system yog orchestrated nyob rau hauv daim ntawv ntawm ib tug R tsab ntawv.

Lub luag haujlwm tiag tiag, kev tsom xam, xaiv los ntawm tus nqi metric, lub xeev vectors: qhov no yog pob GA (cov ntaub ntawv)
Lub pob, nyob rau hauv cov ntaub ntawv no, tsis yog heev haum, nyob rau hauv lub siab hais tias nws xav kom vectors (chromosomes, yog hais tias nyob rau hauv cov nqe lus ntawm lub pob) yuav tsum tau teev nyob rau hauv daim ntawv ntawm cov hlua ntawm cov zauv nrog ib tug feem.

Thiab kuv vector, los ntawm qhov tseem ceeb ntawm qhov kev teeb tsa tsis: cov no yog 14 qhov ntau - cov lej thiab cov nqi hlua.

Qhov teeb meem, tau kawg, yog zam tau yooj yim los ntawm kev muab qee tus lej tshwj xeeb rau txoj hlua tus nqi.

Yog li, thaum kawg, daim ntawv tseem ceeb ntawm R tsab ntawv zoo li no:

npe 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@solution

Ntawm no, nrog kev pab lower и upper subroutine cwj pwm ga qhov tseem ceeb, ib cheeb tsam ntawm qhov chaw tshawb nrhiav tau teev tseg, nyob rau hauv qhov kev tshawb nrhiav yuav ua rau xws li vectors (lossis vectors) uas tus nqi siab tshaj plaws ntawm kev ua haujlwm qoj yuav tau txais.

Lub ga subroutine ua qhov kev tshawb fawb ua kom lub zog muaj zog.

Zoo, ces, nws hloov tawm hais tias, nyob rau hauv cov ntaub ntawv no, nws yog tsim nyog hais tias lub zog muaj nuj nqi, nkag siab lub vector raws li ib tug txheej ntawm qhov tseem ceeb rau tej yam tsis muaj subd, tau txais ib tug metric los ntawm subd.

Ntawd yog: ntau npaum li cas, nrog rau kev teeb tsa subd thiab ib qho kev thauj khoom ntawm subd: lub subd txheej txheem kev lag luam ib ob.

Ntawd yog, thaum nthuav tawm, ntau cov kauj ruam hauv qab no yuav tsum tau ua hauv kev ua haujlwm qoj ib ce:

Ua cov input vector ntawm cov lej - hloov nws mus rau hauv qhov tseem ceeb rau cov subdata tsis.
Ib qho kev sim los tsim ib tus naj npawb ntawm redo pawg ntawm ib qho loj. Ntxiv mus, qhov kev sim yuav ua tsis tiav.
Cov pab pawg magazine uas twb muaj nyob rau hauv subd, hauv qee qhov ntau thiab ntawm qee qhov loj, rau qhov purity ntawm kev sim - d.b. tshem tawm.
Yog tias cov ntsiab lus yav dhau los ua tiav: qhia qhov tseem ceeb ntawm kev teeb tsa tsis raug rau cov ntaub ntawv (dua: tej zaum yuav muaj qhov tsis ua haujlwm)
Yog tias cov kauj ruam dhau los ua tiav: nres lub subd, pib lub subd kom cov txheej txheem tshiab tau teev tseg yuav siv tau. (dua: tej zaum yuav muaj glitch)
Yog tias cov kauj ruam dhau los ua tiav: ua qhov kev xeem thauj khoom. tau metrics los ntawm subd.
Rov qab subd rau nws lub xeev qub, i.e. tshem tawm cov pab pawg ntxiv log, rov qab qhov qub subdatabase configuration mus ua hauj lwm.

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)
}

Qhov ntawd. tag nrho cov hauj lwm: ua nyob rau hauv lub cev muaj nuj nqi.

Lub ga-subroutine txheej txheem vectors, lossis, zoo dua, chromosomes.
Nyob rau hauv uas, qhov tseem ceeb tshaj plaws rau peb yog kev xaiv cov chromosomes nrog cov noob uas lub cev muaj zog ua rau muaj txiaj ntsig loj.

Qhov no, hauv qhov tseem ceeb, yog cov txheej txheem ntawm kev tshawb nrhiav qhov zoo tshaj plaws ntawm chromosomes siv vector hauv qhov chaw tshawb N-dimensional.

Paub meej heev, nthuav dav kev piav qhia, nrog piv txwv ntawm R-code, kev ua haujlwm ntawm cov noob caj noob ces algorithm.

Kuv xav cais cais ob lub ntsiab lus technical.

Pab hu xov tooj los ntawm kev ua haujlwm evaluate, piv txwv li, nres-pib, teeb tsa tus nqi ntawm subd parameter, yog ua raws li cran-r muaj nuj nqi system2

Nrog kev pab los ntawm uas: ib co bash tsab ntawv los yog hais kom ua hu ua.

Piv txwv li:

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)
}
}

Qhov thib ob yog kab, evaluate kev ua haujlwm, nrog rau kev txuag tus nqi metric tshwj xeeb thiab nws qhov sib thooj tuning vector rau cov ntaub ntawv teev cia:

cat( paste("result: ",v_metric," ",v_vector,sep="") , file=v_logfile, sep="n", append=T)

Qhov no yog qhov tseem ceeb, vim hais tias los ntawm cov ntaub ntawv array no, nws yuav muaj peev xwm tau txais cov ntaub ntawv ntxiv txog qhov twg ntawm cov ntsiab lus ntawm kev hloov kho vector muaj qhov cuam tshuam ntau dua lossis tsawg dua ntawm tus nqi metric.

Ntawd yog: nws yuav muaj peev xwm ua qhov kev txheeb xyuas attribute-importamce.

Yog li cas yuav tshwm sim?

Hauv daim duab, yog tias koj xaj cov ntawv xeem hauv ascending metric order, daim duab yog raws li nram no:

Qee cov ntaub ntawv sib raug rau qhov tseem ceeb ntawm cov metric:

Ntawm no, nyob rau hauv lub screenshot nrog cov txiaj ntsig, Kuv yuav qhia meej: qhov tseem ceeb ntawm kev hloov kho vector yog muab nyob rau hauv cov nqe lus ntawm lub zog muaj nuj nqi code, tsis yog nyob rau hauv cov nqe lus ntawm tus naj npawb ntawm daim ntawv teev cov tsis / ntau ntawm parameter qhov tseem ceeb, uas yog formulated. saum toj no hauv cov ntawv nyeem.

Zoo. Puas yog ntau lossis me me, ~ 8 txhiab tps: cov lus nug cais.
Nyob rau hauv lub moj khaum ntawm kev soj ntsuam ua hauj lwm, daim duab no tsis yog ib qho tseem ceeb, dab tsi yog qhov tseem ceeb ntawm cov dynamics, yuav ua li cas tus nqi no hloov.

Cov dynamics ntawm no yog qhov zoo.
Nws yog pom tseeb tias tsawg kawg yog ib qho tseem ceeb cuam tshuam rau tus nqi ntawm cov metric, ga-algorithm, txheeb los ntawm chromosome vectors: npog.
Kev txiav txim los ntawm qhov muaj zog zoo ntawm qhov nkhaus qhov tseem ceeb, muaj tsawg kawg yog ib qho ntxiv uas, txawm tias me me, muaj kev cuam tshuam.

Nov yog qhov koj xav tau attribute-importance tsom xam kom nkag siab txog dab tsi tus cwj pwm (zoo, qhov no, cov khoom ntawm qhov kev hloov kho vector) thiab ntau npaum li cas lawv cuam tshuam tus nqi metric.
Thiab los ntawm cov ntaub ntawv no: nkag siab txog dab tsi cuam tshuam los ntawm kev hloov pauv ntawm cov yam ntxwv tseem ceeb.

khiav attribute-importance ua tau nyob rau hauv ntau txoj kev.

Rau cov hom phiaj no, kuv nyiam cov algorithm randomForest R pob ntawm tib lub npe (cov ntaub ntawv)
randomForest, raws li kuv nkag siab txog nws txoj haujlwm feem ntau thiab nws txoj hauv kev los ntsuas qhov tseem ceeb ntawm cov cwj pwm tshwj xeeb, tsim ib qho qauv ntawm kev vam khom ntawm cov lus teb sib txawv ntawm cov cwj pwm.

Hauv peb cov ntaub ntawv, cov lus teb sib txawv yog ib qho kev ntsuas tau los ntawm cov ntaub ntawv hauv cov kev xeem thauj khoom: tps;
Thiab cov cwj pwm yog cov khoom siv ntawm kev hloov kho vector.

Yog li no randomForest ntsuas qhov tseem ceeb ntawm txhua tus qauv cwj pwm nrog ob tus lej: %IncMSE - yuav ua li cas lub xub ntiag/tsis muaj tus cwj pwm no hauv tus qauv hloov MSE zoo ntawm tus qauv no (Lub ntsiab lus Squared yuam kev);

Thiab IncNodePurity yog tus lej uas qhia txog qhov zoo npaum li cas, raws li qhov tseem ceeb ntawm tus cwj pwm no, cov ntaub ntawv nrog cov kev soj ntsuam tuaj yeem muab faib, yog li hauv ib feem muaj cov ntaub ntawv nrog ib tus nqi ntawm qhov ntsuas tau piav qhia, thiab lwm yam nrog lwm tus nqi ntawm metric.
Zoo, qhov ntawd yog: qhov twg yog qhov kev faib tawm ntawm tus cwj pwm (Kuv pom qhov tseeb tshaj plaws, lus piav qhia Lavxias ntawm RandomForest S, SѓS,).

Cov neeg ua haujlwm-peasant R-code rau kev ua cov ntaub ntawv nrog cov txiaj ntsig ntawm kev kuaj xyuas:

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$importance

Koj tuaj yeem ncaj qha xaiv cov hyperparameters ntawm lub algorithm nrog koj txhais tes thiab, tsom mus rau qhov zoo ntawm tus qauv, xaiv tus qauv uas ua tiav qhov kev kwv yees ntawm cov ntaub ntawv siv tau.
Koj tuaj yeem sau qee yam haujlwm rau txoj haujlwm no (los ntawm txoj kev, dua, siv qee yam kev ua kom zoo dua qub).

Koj tuaj yeem siv R pob caret, tsis yog lub ntsiab lus tseem ceeb.

Raws li qhov tshwm sim, nyob rau hauv cov ntaub ntawv no, cov txiaj ntsig hauv qab no tau txais los ntsuas qhov ntsuas qhov tseem ceeb ntawm cov cwj pwm:

Zoo. Yog li, peb tuaj yeem pib xav txog thoob ntiaj teb:

Nws hloov tawm tias qhov tseem ceeb tshaj plaws, nyob rau hauv cov kev sim no, yog qhov parameter commit_wait
Technically, nws qhia meej txog hom kev ua haujlwm ntawm io ua haujlwm ntawm kev sau cov ntaub ntawv redo los ntawm subdb log tsis mus rau pawg log tam sim no: synchronous lossis asynchronous.
nqi nowait uas ua rau yuav luag ntsug, ntau qhov nce ntawm tus nqi ntawm tps metric: qhov no yog qhov suav nrog asynchronous io hom hauv redo pawg.
Ib lo lus nug txawv yog seb koj puas yuav tsum ua qhov no hauv cov khoom noj khoom haus. Ntawm no kuv txwv kuv tus kheej kom tsuas yog hais: qhov no yog qhov tseem ceeb.
Nws yog qhov xav tau tias qhov loj ntawm lub log tsis ntawm lub subd: hloov tawm los ua qhov tseem ceeb.
Qhov me me ntawm lub log tsis, qhov tsawg dua nws lub peev xwm buffering, ntau zaus nws dhau mus thiab / lossis tsis muaj peev xwm faib thaj chaw dawb hauv nws rau ib feem ntawm cov ntaub ntawv redox tshiab.
Qhov no txhais tau tias: ncua sij hawm cuam tshuam nrog kev faib qhov chaw hauv lub log tsis thiab / lossis pov tseg cov ntaub ntawv rov qab los ntawm nws mus rau hauv pawg redo.
Cov kev ncua sij hawm no, tau kawg, yuav tsum thiab ua rau muaj kev cuam tshuam rau kev nkag mus ntawm cov ntaub ntawv rau kev lag luam.
Parameter db_block_checksum: Zoo, kuj, feem ntau nws yog qhov tseeb - kev ua lag luam ua rau tsim cov darty blocks hauv subdatabase's buffer cache.
Uas, thaum xyuas checksums ntawm datablocks yog enabled, lub database yuav tsum tau ua - xam cov checksums los ntawm lub cev ntawm datablock, xyuas lawv nrog dab tsi yog sau nyob rau hauv lub datablock header: ntais ntawv / tsis phim.
Cov hauj lwm zoo li no, dua, tsis tuaj yeem ncua sijhawm ua cov ntaub ntawv, thiab raws li qhov ntsuas thiab cov txheej txheem uas teeb tsa qhov ntsuas no dhau los ua qhov tseem ceeb.
Tias yog vim li cas tus neeg muag khoom muab, nyob rau hauv cov ntaub ntawv rau qhov parameter no, txawv qhov tseem ceeb rau nws (qhov parameter) thiab sau tseg tias yog, yuav muaj kev cuam tshuam, tab sis, zoo, koj tuaj yeem xaiv cov nqi sib txawv, mus txog "tawm" thiab cuam tshuam sib txawv.

Zoo, lub ntsiab lus thoob ntiaj teb.

Txoj hauv kev, feem ntau, hloov mus ua haujlwm heev.

Nws kuj tso cai rau nws tus kheej, nyob rau hauv cov theem thaum ntxov ntawm kev kuaj load ntawm ib tug tej yam kev pab cuam system, nyob rau hauv thiaj li yuav xaiv nws (system) zoo configuration rau lub load, tsis mus delve ntau dhau mus rau qhov tshwj xeeb ntawm kev teeb tsa lub system rau lub load.

Tab sis nws tsis suav nrog nws tag nrho - tsawg kawg ntawm qib kev nkag siab: lub kaw lus yuav tsum paub txog "kho qhov hloov kho" thiab qhov kev tso cai ntawm kev sib hloov ntawm cov pob qhov rooj.

Qhov kev mus kom ze tuaj yeem pom sai sai nrhiav qhov kev pom zoo ntawm qhov system.
Thiab raws li cov txiaj ntsig ntawm kev sim, nws muaj peev xwm tau txais cov ntaub ntawv hais txog qhov xwm txheej ntawm kev sib raug zoo ntawm lub kaw lus qhov kev ntsuas zoo thiab qhov tseem ceeb ntawm qhov kev teeb tsa ntawm lub system.

Uas, ntawm chav kawm, yuav tsum tau pab txhawb rau qhov tshwm sim ntawm no sib sib zog nqus nkag siab ntawm lub system, nws cov hauj lwm, yam tsawg kawg nkaus nyob rau hauv ib tug muab load.

Nyob rau hauv kev xyaum, qhov no yog ib qho kev sib pauv ntawm cov nqi ntawm kev nkag siab txog cov txheej txheem customized rau cov nqi ntawm kev npaj xws li kev xeem ntawm lub system.

Kuv xav kom nco ntsoov cais: nyob rau hauv txoj kev no, qib ntawm kev txaus siab ntawm kev sim rau cov kev ua haujlwm uas nws yuav muaj nyob rau hauv kev ua lag luam yog qhov tseem ceeb heev.

Ua tsaug rau koj qhov kev mloog thiab sijhawm.

Tau qhov twg los: www.hab.com

Txoj kev tshawb fawb poke, los yog yuav ua li cas xaiv ib lub database configuration siv benchmarks thiab optimization algorithm

Ntxiv ib saib Отменить ответ