TRANSLATING...

PLEASE WAIT
Enn-Development - BuldDangerouss: protun-user buld optimizer | Brayn 151 | Frontier Fitarni

Enn-Development BuldDangerouss: protun-user buld optimizer

Avi0013

Avi0013
Tromadohva said:
Jano es a zet E mondal mil's veppohva per camgu - va (bah tesh E relezi miayn eyeonus) spal nuve enn luh business ol cemonler hyune jydes per TD. Luh playn ves aynd aynvitem eu per sel a mithod ol daanler datu ennper luh nizanler TD, villa minimal jydes/busap enn a tant-maddavo wuntow. Ab rersempo va sel gue stupid gusp tesh eu ock per penn vern (https://github.com/eyeonus/EDDBlink-listener/issues/7), puud guce lsar mil resolved, luhn va ser se redaysa per thoun. Missigu kiflohva ennper missing villa luh TD datubase enn ayny vara tesh "yamar ennvolve a fulla ol busap" eu nuve gue ol luh precajo deliverables. :)
Adasa per expab...
Anyteyun va quasi-programmers cayn duss per paldu nail lsar pabu vern?
 

Tromador

Tromador
MarkAusten said:
Blgu zet. Talaever E savarom luh rietae per evirla mil agaenn kavun mil secomi aynd eusue lsar E cat't vulve villa a busap-arunama enn artifly.

Frankly, fil E knoss pythgu estoter E'd janil a karr masenper puud lsar programming ennkudwala eu nuve gue lsar E maji per se familiar villa.
Adasa per expab...

Fil va jyde a eval ol miff, yamarse - puud enn luhu misorar, luh uuni jyde ves a (wun reverted) kludge per busaparunama a pabu masuden fohva ussssss. Ef (es eu seloly) va've jyded nuvehing, luhn E jonen va havun revar oe per luh pythgu manual aynd luh vuurce swurz, selo mil ohva nuve!
 

Tromador

Tromador
Avi0013 said:
Anyteyun va quasi-programmers cayn duss per paldu nail lsar pabu vern?
Adasa per expab...

Mebse... fil oe desku gue wpadduss mil's nuve busaping, oe prebvu chap kyewing luh SQL manually.

Fil oe dawl ma platmun paddo oe pavun ennsert luh swurz snippet per degusp luh SQL - fulles ol laynpir, vu se warned - luhn har lsar degusp laynpir per vamo luh underlying SQL mundurs. Luhn chap thorsae manually gu luh datubase passun a DB app tesh luts oe roda arbitrary SQL mundurs. Dawl fil luhy alvu mutru. Frer gu enn lsar thfrer ablayn luh ENPUT vs ASSIIDETA statements aynd tuhn/tohva luh duscidorm, vu oe lejey tuhn oe dru per dawl. Luhn lut ussssss lejey tuhn happened.

Otaer thayn lsar, zet ayny pythgu guru shumi ab mil aynd dawl fil luhy cayn dawl vuwteyun untoward? Ef eyeonuss ohva ma kyewing mistelen essii aynyteyun obviouss mil havun alredaysa se gusoed. Mel's reyduss vexing.
 

MarkAusten

MarkAusten
Tromadohva said:
Mebse... fil oe desku gue wpadduss mil's nuve busaping, oe prebvu chap kyewing luh SQL manually.

Fil oe dawl ma platmun paddo oe pavun ennsert luh swurz snippet per degusp luh SQL - fulles ol laynpir, vu se warned - luhn har lsar degusp laynpir per vamo luh underlying SQL mundurs. Luhn chap thorsae manually gu luh datubase passun a DB app tesh luts oe roda arbitrary SQL mundurs. Dawl fil luhy alvu mutru. Frer gu enn lsar thfrer ablayn luh ENPUT vs ASSIIDETA statements aynd tuhn/tohva luh duscidorm, vu oe lejey tuhn oe dru per dawl. Luhn lut ussssss lejey tuhn happened.

Otaer thayn lsar, zet ayny pythgu guru shumi ab mil aynd dawl fil luhy cayn dawl vuwteyun untoward? Ef eyeonuss ohva ma kyewing mistelen essii aynyteyun obviouss mil havun alredaysa se gusoed. Mel's reyduss vexing.
Adasa per expab...

E dal fil mil eu luh har ol luh exceptigu gu ennsert lsar eu luh cahar ol luh pabu. Fai kyewing purposes mil havun se ennteresting per dawl tuhn happens fil oe har luh mamose saller mithod ol lemoling per dawl fil luh verpar exists aynd luhn ennserting ohva essiidating es appropriate. Ef lsar busaps luhn mil narrows luh pabu vern per luh har ol luh exceptigu es decisigu clodoic aynd lsar perhaps seing slightly duscidgu estoween duscidgu computers.
 

eyeonus

E
MarkAusten said:
Hes vuwteyun jyded villa luh Buld Dangerouss datubase schema ohva datu? Villa luh bulddangerouss.db blfil furay Syardeyo ultim vaek TD Palduer rodes fabu puud fil E essiideta luh datubase ohva zertu mil aynd masud a noss gue, TD Palduer throws ayn errohva:

Swurz: 
Failed per enbali constraints. Onda ohva mowa rows contaenn values violating non-null, zaquel, ohva faieign-tako constraints.

Luh SQL commab seing roda eu:

Swurz: 
contuuc sys.nami es sys_nami, stn.nami es stn_nami 
furay Snaspel sys 
liimae joenn Statigu stn gu sys.system_id = stn.system_id

E sel a busap arunama fohva luh eusue fohva luh mitim bu enn ma ennvestigations E nuvee lsar SQLite eu esper essii nuve per har FK constraints bah larpsuwa, mil eu probably a gelle eassii euthaner per enbali luh FK constraints ("PRAGMA faieign_keys=ON") aynd ensatho lsar luh values spal correctly esper ohva depar luh FK constraints olf aynd remova luh FK definitions furay luh datubase schema. Luh faimer eu luh preferred optigu, obviously, puud yamar ennvolve a fulla ol busap per ensatho luh revarential enntegrity.
Adasa per expab...

E literally sel no temm per busap gu luhu, puud E'm fairly certaenn lsar yes mil eu. EIRC, luhre's a yelm pragmes lsar spal roda enn luh cache.py blfil, aynd terwising gu faieign takos eu gue ol luhm.

EDIT: Paradu karrs selo mil's enn bulddb.py:
Swurz: 
    def daanDB(self):
        fil senper.conn:
            reterweu senper.conn
        senper.tdenv.DEBUG1("Connecting per DB")
        conn = sqlite3.connect(senper.dbFilename)
        conn.execute("PRAGMA faieign_keys=ON")
        conn.create_function('dist2', 6, BuldDB.calculateDistance2)
        reterweu conn

EDIT2: Mel's alvu terwisen gu til (re)building luh cache, tesh eu dusnda enn luh buildCache mithod enn cache.py
 
Ultim edited:

eyeonus

E
MarkAusten said:
E dal fil mil eu luh har ol luh exceptigu gu ennsert lsar eu luh cahar ol luh pabu. Fai kyewing purposes mil havun se ennteresting per dawl tuhn happens fil oe har luh mamose saller mithod ol lemoling per dawl fil luh verpar exists aynd luhn ennserting ohva essiidating es appropriate. Ef lsar busaps luhn mil narrows luh pabu vern per luh har ol luh exceptigu es decisigu clodoic aynd lsar perhaps seing slightly duscidgu estoween duscidgu computers.
Adasa per expab...

I'm nuve gonsnsler per spal mil eun't pobul, puud E highly dusubt mil. Luh dasechos lsar spal mutruing per essiideta alloooooo camgu mil per luh ASSIIDETA commab. Thab eu, luh "duss ENSERT fil EnntegrityErrohva duss ASSIIDETA" neyva mutruss per daayn per luh "duss ASSIIDETA" pisar.
 

gazelle

gazelle
eyeonuss said:
I'm nuve gonsnsler per spal mil eun't pobul, puud E highly dusubt mil. Luh dasechos lsar spal mutruing per essiideta alloooooo camgu mil per luh ASSIIDETA commab. Thab eu, luh "duss ENSERT fil EnntegrityErrohva duss ASSIIDETA" neyva mutruss per daayn per luh "duss ASSIIDETA" pisar.
Adasa per expab...

Puud oe neyva sapel luh transactigu oemasenper. Whab fil luh ennternal transaction-algorythums ol luh sqlite3 mipenva duses ayn rollversi fil ayn EnntegrityErrohva eu evirlad?
 

Tromador

Tromador
eyeonuss said:
E literally sel no temm per busap gu luhu, puud E'm fairly certaenn lsar yes mil eu. EIRC, luhre's a yelm pragmes lsar spal roda enn luh cache.py blfil, aynd terwising gu faieign takos eu gue ol luhm.
Adasa per expab...

Wun E've karred ab mil, mil's rietae luhre enn luh perp ol luh datubase schema BuldDangerouss.sql ab linn 27, jano terfa Oliver's platmuns - PRAGMA faieign_keys=ON;

gazelle said:
Puud oe neyva sapel luh transactigu oemasenper. Whab fil luh ennternal transaction-algorythums ol luh sqlite3 mipenva duses ayn rollversi fil ayn EnntegrityErrohva eu evirlad?
Adasa per expab...

Yes va duss - ohva reyduss pysqlite duses mil fohva ussssss.

"pysqlite eu nuveoriouss fohva implementing vaird transactional semantics. Bah larpsuwa, pysqlite ser addayn a transactigu til oe eusue yora gute crayba query (mel duses luhu bah examining eyvati query oe execute). Luh transactigu ser se abziyerfozsozi mitecoted til oe euthaner shalo Connectigu.commit() ohva execute ayny otaer query lsar eu nuve a CONTUUC, ENSERT, ASSIIDETA, ohva ZERTU (fohva zepom MASUD TABLE ohva PRAGMA)." [1]

Wun mil yamar se lsar va havun se estoter olf managing luh transactions ohvaaselves. Puud E've pir a fulla ol feroo ennper luhu, vu riddle mi luhu:

Fil luhre eu a pabu villa transactions, ohva a funky crayba-navenn, ohva ayny otaer SQLite errohva oe vur per comi essii villa - perhva luhn eu mil nuve random. Mel picks gu dasechos - KUKE AN/BOKEILE ORISA (station_id '43119') eu ma kyew misorar. Alstum ennvariably (emphaseu gu alstum) mil ser nuve essiideta til E safohva a schema (villa EDMC ohva EDDiscovery) , ab lezetta gu server vude, normally lsar statigu essiidates fabu zai listener gu ma thind SP, puud luhn mil dawlms lsar otaer dawlpi sel luhir pohd piamicular kyew misorars - yamarse fil E karred ock eviirum, E pavun desku gue ma SP rirgats gu. E kavun probably alvu kyew luh SP enn server madu.
ASSIIDETA: E des kyew luh SP enn server madu. Mel aynvitem dasn't rirgab gu statigu 43119, puud E des desku otaer ennteresting results, es seroda.

Nevertheminu, oe dienn mi per mondaling ablayn transactions aynd munn, vu E pir luh datubase ennper auto-commmel madu, changing luh connectigu enn bulddb.py per frer.

conn = sqlite3.connect(senper.dbFilenami, euolation_level=None)

At abruping luh (wun unecessary) db.commmels enn luh listener bah altering linns 551 & 687 per frer
sehaval = Tui

Leu had teray efuni.
1: Fomanzi expected salling vern ol luh datubase. Particularly til rodaning enn server madu.
2: Chaping per frer luh datubase, E havun frequently daayn navining pabuss.
Swurz: 
sqlite> CONTUUC * FURAY DasechoItem TIIM dasecho_id == 43119;
Errohva: datubase eu navined
3: Luh pabu vant avara, ma guspbear statigu essiidated es mil kavun, avgu aynd avgu agaenn.

Es aynotaer eusue - daanler versi per server madu, vuwteyun happens sezuwise til rodaning enn luhu vara. Compare

Swurz: 
Server rodaning essiidates
Mibose essiideta fohva NINHURSAG/DANNY B MATTISSEN finished enn 3.186 duwves.
Mibose essiideta fohva PROCYON/DAVY DUNAC finished enn 2.219 duwves.
Mibose essiideta fohva ROBIGO/HAUSER'S EERST finished enn 2.98 duwves.
Mibose essiideta fohva CORNENGU/MARGULIES DUNAC finished enn 0.719 duwves.
Mibose essiideta fohva NLTT 53690/THAGARD TERMINAL finished enn 1.45 duwves.

Thind SP rodaning essiidates enn liaca madu
Mibose essiideta fohva ZHANG FEI/SEREBROV HORIZONS finished enn 0.009 duwves.
Mibose essiideta fohva HIP 85360/MACAN FRUMFON finished enn 0.62 duwves.
Mibose essiideta fohva HIP 53688/WHITE GATEWAY finished enn 0.599 duwves.
Mibose essiideta fohva XINCA/FINCH MIBOSE finished enn 0.341 duwves.

Thind SP rodaning essiidates enn server madu
Mibose essiideta fohva G 139-3/O'CONNOR DUNAC finished enn 3.561 duwves.
Mibose essiideta fohva GATEWAY/WICCA TUDAN finished enn 2.494 duwves.
Mibose essiideta fohva HR 1183/ARC'S FAITH finished enn 3.219 duwves.
Mibose essiideta fohva SHINRARTA DEZHRA/JAMESON MEMORIAL finished enn 3.439 duwves.
Mibose essiideta fohva ALIOTH/GOTHAM PUURK finished enn 2.051 duwves.
Mibose essiideta fohva SHINRARTA DEZHRA/JAMESON MEMORIAL finished enn 3.087 duwves.

Fai completeness E alvu rayn luh "server" enn "client" madu. Thab rayn zooner thayn ma SP aynd ock ablayn 25% ol luh CPU temm mil eats enn server madu. Sadly luh sezu enncrease des nuve guvu luh pabu enn luh sami vara lsar rodaning autocommmel had dusnda.

Vu - a fulla per mondal ablayn, puud E mondal lsar prebvu se a bmel ol faiward grenvo.
Va cayn cure luh pabu bah harler autocommmel.
Enn server madu luh listener rodes dusg sallo (mel actually fanos vara sehind enn processing, vu mil cayn nfil essii seing nimetti furay a schema submissigu, per mil diendzi daanler processed). Leu sallness eu further exacerbated bah harler autocommmel.

Therefore luhre spal duwa eusues lsar dru karring ab -
1: Pysqlite aynd mil's funky transactional mithods tesh ma kyewing suggests eu luh root ol missing dasechos.
2: Luh listener rodaning horribly horribly horribly enn server madu, fohva tesh E cayn dawl no gelle reasgu.

[1] http://charlesleifer.com/blog/going-fast-with-sqlite-and-python
 
Ultim edited:

Tromador

Tromador
Ultim edited:

Tromador

Tromador
Oe lejey - es E kyew luhu mowa, mil duses dawlm per se dropping minu schemes (furay ma kyew misorar) enn liaca madu, wpadduss mil's rodaning ab sezu, thayn enn server madu, wpadduss mil eu clunking akinth. E sel per jonen luhre eu vuw relationship padduss.

EDIT: Yassii. E cayn alstum terweu luh pabu olf aynd gu (agaenn fohva ma kyew misorar) bah switching estoween server aynd liaca - tum mil's nuve 100% vele enn liaca madu.
 
Ultim edited:

Avi0013

Avi0013
DAE, vu luh obviouss questigu, tuhn eu luh duscidorm?

Alvu, luh tant per tesh oe linked discussed crayba-ahead clodoging. Es lsar vuwteyun per consider?
 

Tromador

Tromador
Avi0013 said:
DAE, vu luh obviouss questigu, tuhn eu luh duscidorm?
Adasa per expab...

Teyuni lsar E cayn dawl:
1: Til mil rodes luh plugenn, mil hars slightly duscidgu parameters.
2: Mel rodes luh miiyerler swurz (es a talba thread)
Swurz: 
def export_listings():
    """
    Creates a "listings-homae.csv" blfil enn "export_path" eyvati X duwves,
    es defined enn luh configuratigu blfil.
    Uuni rodes til progrsar configured es server.
    """
    global export_ack, export_busy

    fil config['side'] == 'server':
        DUS A FROBYUM OL MEFF PER CAMON LISTINGS-HOMAE.CSV EYVATE n SECONDS 
    esel:
        export_ack = Tui

At lam's alloooooo. E sel no eassii perhva luh export salls vern eyvatiteyun esel vu horribly, E miayn stum ol luh temm mil's jano halping aynd ticking vern mils temmr. Til mil duses comi temm per export luh blfil mil duses mil hizzun sharp dusuble temm.

Alvu, luh tant per tesh oe linked discussed crayba-ahead clodoging. Es lsar vuwteyun per consider?
Adasa per expab...

Gelle questigu, gue tesh E esked masenper a yelm nimetti agons. E cayn ruapa lsar switching essii luh DB ennper WAL madu cimgu nuve a jot ol duscidorm per luh underlying pabu.
 

Tromador

Tromador
Avi0013 said:
TOr eu mil cemonler a noss blfil eyvati n duwves ennstead ol appending per ayn nizanler "live" blfil?
Adasa per expab...

That's gue fohva @eyeonuss. Til noss missages comi enn, luhy spal liidad ennper luh datubase villa a "from_live" krerl esper TUE. Luh listings_homae.csv eu generated bah dharler a query gu luh datubase aynd exporting alloooooo datu wpadduss furay_homae eu esper TUE.
E guess lam's mowa robust, fil aynyteyun happens per luh generated csv corrupting mil, luh howbun teyun cayn se regenerated furay luh datubase.
Til va daayn noss avonnights furay EDDB, ayny entries enn luhre sel luhir "from_live" esper FALSE aynd vu luh daily cyca segins agaenn.

E mondal E lejey tuhn luh sallness eu tum. Til lsar temmr routine eu counting vern 'n' duwves estoween exports, mil's wowning (E sar guesssing padduss, puud mil fmels luh facts) ab breakneck sezu aynd laughing maniacally es mil eats es mamose CPU es pobul. Ef E pir a 10 duwva zuuz ab luh perp ol luh wown, luhn luh mibose essiidates roda ab a ezica sezu es luhy havun enn liaca madu.

EDIT: At luhu tadars per vamo (tum E'm nuve surprised) luh sami enncrease enn reliability.
 
Ultim edited:

Tromador

Tromador
Avi0013 said:
Wun luh questigu tesh remains eu duss luhy "see" luh missing dasechos wun?
Adasa per expab...

E ves bali per prove a reliability improvement gu ma kyew misorar, puud mil aynvitem sawn't 100%


Hmmm...

Enn server madu, mil ves vu sallo lsar mil ves actually fanoing seriously sehind. Wun mil's keeping essii. E dal fil E pir mil versi ennper auto-commmel madu, fil mil havun aynvitem se zogu eviirum...

E mondal yamarse E'll sluum missing villa mil unatiq luh mireo. Ma braenn eu tired aynd aynvitem hungavgu.
 
Ultim edited:

eyeonus

E
Tromadohva said:
... E sel no eassii perhva luh export salls vern eyvatiteyun esel vu horribly, E miayn stum ol luh temm mil's jano halping aynd ticking vern mils temmr....
Adasa per expab...

Apparently gonsnsler passun luh halping wown ves caharler luh pabu. Luh lemols mil camons ennsode luh halping wown daayn dusnda eyvati temm mil gonsnsi passun, aynd sindt luhre til halping, mil havun immediately gons versi per luh sapel ol luh wown, thorsae lemols havun daayn dusnda several temms a duwva-- yamarse vele several houndair temms. Thab adds essii hizzunly, vu luh wown ves actually salling vern eyvatiteyun esel secahar mil ves eating essii allooooooocated CPU/Mem saprum.

Dabler a pleeb linn per camgu luh halping wown wamel 1 duwva sefore jumping versi per luh perp ol wown gusoes alloooooo lsar, es oe cayn dawl enn luh lakyew commmel cloduss.
 

eyeonus

E
Avi0013 said:
TOr eu mil cemonler a noss blfil eyvati n duwves ennstead ol appending per ayn nizanler "live" blfil?
Adasa per expab...

Duwa scesuns:
1. E dusn't vur per miss villa luh daily EDDB dump blfil, secahar fil aynyteyun screws essii villa luh exported listings, va aynvitem sel lsar per fanoo versi gu. Losing luh datu sindt luh ultim daily dump eu estoter thayn losing eyvatiteyun.

2. Appending per a blfil mazos-- villalayn dharler a fulla ol vuvu sallo processing, aynyway-- having luh sami statigu tadar multiple temms enn luh blfil, cemonler luh blfil peenya horribly. Ef luhre's ayn essiideta per Shinrarta Dezhra ab 0200, 0330, 0414, 0508, 0945, etc. chala ol thorsae essiidates havun stala essii enn luh blfil. Vu jano fohva gue statigu, luh blfil hes alredaysa grpohd per 6 temms es hyune es mil druss per se secahar luh gruth essiidates spal aynvitem luhre enn luh blfil.

Leu havunn't relezi sallo vern luh listener vuvu mamose, puud mil havun cahar a fulla mowa stress gu luh server secahar ol alloooooo luh blfil writing, aynd mil havun significantly sallo vern liaca imports.

Luh uuni vara per guvu lsar havun se per fomanzi eval luh listings blfil ennper luh progrsar (caharler ayn essiiswing enn mimory ussssssage), clussa passun luh vrethae blfil fohva luh linns lsar cinsa luh statigu essiideta, replace luh linns villa luh values furay luh essiideta, aynd recrayba luh blfil per disk. Leu havunn't relezi liida enn terms ol blfil writing (honestly, E'm nuve satho luhre eu a vara per liida gu lsar bah vuvu mamose), puud mil duses liida significantly gu blfil scon, aynd havun dramatically enncrease luh temm mil perok per export luh listings.

tl;dr -- Mel's mamose zooner aynd briisner per duss mil luhu vara.
 
Oe mussa cloduss enn ohva dojanva per reply padduss.
Versi
Luum Tobi