Brzina inserta u MySQL-u

Interesuje me šta sve (konfiguracioni parametri, podešavanja...) mogu uticati na brzinu inserta u mysql-u.
Naime, radim iz Jave import nekih podataka u bazu. Sve ide preko Hibernate, ali mislim da to nije bitno za ovu priču. U pitanju su čisti inserti u praznu, MyISAM tabelu. E sad, na mom laptopu, gde imam Windows 7, i MySQL 5.5 import traje par minuta, ukupan broj rekorda koji se insertuje je oko milion ipo, dakle par hiljada u sekundi.


Kada isti taj kod pokrenem na produkcionom serveru (Linux), koji je po svim parametrima daleko moćniji od mog laptopa import traje neuporedivo sporije - mislim da uspeva manje od 20 rekorda da insertuje u sekundi i ceo proces traje satima. U oba slučaja aplikacija radi sa "lokalnim" MySQL-om i tabela je MyISAM. Na produkcionom serveru, aplikacija radi - u produkciji (gle čuda!), pa se osim ovog importa dešavaju i mnoge druge stvari, ali mislim da nije preterano opterećena ni aplikacija ni baza. Recimo da je u proseku na njemu 10-tak upita u sekundi, ali ništa od toga ne dira ovu tabelu (i schemu) u koju se insertuje. Ajd, u toku dana je load dosta veći, ali sada u večernjim satima aplikacija i baza su vrlo malo opterećeni pa ne verujem da to može toliko da uspori import podataka.

Ima li neko ideju šta bi mogao da bude problem?

Zaboravih jednu stvar, mada nisam siguran da li to može da ima veze - na produkcionom serveru je podešeno da se radi replikacija.

1. koja je verzija na produkciji?
2. uporedi config na produkciji i kod tebe u lokalu
3. da li radis bulk insert ili jedan po jedan? bulk je mnoooooooooooooogo brzi
4. da li radis disable/enable keys za veliki import podataka?
5. query cache?
6. table cache?
7. max open files?

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

replikacija (kapiram da je master u pitanju) malo usporava stvar posto svaki taj insert mora da se upise na dva mesta, ali ne toliko da razlika bude 1000 vs 10 /sec

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Na produkciji je 5.5.9. Mislim da mi je na laptopu nešto niža (možda 5.5.7). Proveriću sutra, nije mi ovde laptop.
Ne radim bulk insert. Znam da je brži, ali pošto idem preko Hibernate, nemam kompletnu kontrolu nad upitima koji se šalju. Ja sam bio zadovoljan performansama na mom laptopu pa nisam hteo dalje da optimizujem. Inače, imao sam par indexa nad tabelom, pa sam i njih pobrisao na produkciji ali i dalje mi radi sporo.

Proveriću sutra za konfiguracione parametre. Znam jedino da sam primetio da je na produkciji nekoliko puta veći bulk_insert_buffer_size, ali nisam siguran koliko on utiče na ove inserte ako se ne koristi bulk sintaksa.

Ja sam nekada davno imao problema sa ovim http://dev.mysql.com/doc/refman/5.0/en/dns.html. Ne mora da znači da je i kod tebe ista stvar ali lako se provjeri.

Koji je fajl sistem na tom Linux i koji je kernel u pitanju?

Potpis

Beneath civilization's fragile crust, cold chaos churns...

Hm, nisam siguran da to može da bude problem pošto konekciju uzimam jednom i sve radim na njoj.

Kernel je 2.6.18-164.2.1.el5 a fajl sistem ext3.

Privremeno sam obustavio rešavanje ovog problema pošto sam dumpovao tabelu sa laptopa i importovao je na produkciji.
Inače, primetio sam da se i u lokalu posle 800-900 hiljada rekorda import uspori, doduše ne kao na produkciji, ali bude dosta sporije (fajl sa 6-7000 rekorda koji bi na početku importovao za sekund-dva, posle nekog vremena traje 10-20 sekundi).
Pogledaću kad budem stigao konfiguracione parametre, pošto su po svemu sudeći, ostali default parametri za MyISAM (na ovom serveru inače koristimo InnoDB, ovo je jedina MyISAM tabela). Na mom laptopu su sigurno svi parametri default...

prvo uporedi parametre (konfiguraciju) ... pa onda gledaj dalje sta ...

generalno myisam je full table lock storage engine tako da ako imas jos neki proces koji pristupa toj tabeli bice znacajno sporije ... drugo, normalno je da se brzina inserta isporava kako tabela raste .. trece, razmisli o bulk insertu .. sve te orm kala*urcije su mnogo veci problem nego pomoc .. i na kraju - particionisi tu tabelu

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Skoro sam se opet bavio ovim problemom pošto sam ponovo imao potrebu da popunim pomenutu tabelu sa par stotina hiljada rekorda i naišao na iste (u stvari još gore) probleme kao i prvi put. Import je na serveru sada trajao besmisleno dugo i postalo mi je očigledno da je poblem najverovatnije do koda pa sam seo da ga rešim.

Mislim da je osnovni problem to što Hibernate kešira objekte kod inserta iako je u ovom slučaju to nepotrebno pošto se radi samo o insertima velike količine podataka (nema čitanja, a samim tim ni potrebe za keširanjem). Iako je moguće brisati keš iz sesije i na taj način verovatno dosta poboljšati performance, odlučio sam da ipak u ovom slučaju totalno zaobiđem Hibernate i pucam direktno preko SQL-a. Štaviše, poslušao sam Bogdana i koristio MySQL "bulk insert" sintaksu i ceo proces toliko ubrzao da ono što se ranije izvršavalo satima sada se završi za par sekundi. Eto, iako inače rado koristim Hibernate, u ovom konkretnom slučaju Bogdan je u pravu da on više smeta nego što pomaže!

ja sam ubedio do sada dve firme da ga izbace kompletno (hibernate) i mrzeli su me mesecima a sada me kuju u zvezde kako sam ih spasao bede ...

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Ako nije tajna šta su koristili kao zamenu?

I mene jako interesuje, šta su nastavili da koriste, samo JDBC, ili neki drugi ORM?

Daleko sam ja od ideje da se odreknem Hibernate. Isuviše mi vremena uštedi u najvećem broju situacija. Ipak, nije dobar za sve poslove, kao što se može videti u pomenutom slučaju.

izbacili su ORM kompletno. zato su me i mrzeli posto su morali da redizajniraju aplikaciju i programiraju "kako valja" .. ono sto je zastrasujuce u celoj prici je kako su prilicno velike aplikacije (moze se reci i ogromne) lisene orm-a za manje od 4 meseca rada i pritom su resili skoro sve probleme sa skaliranjem koje su imali pre toga ... dobili nekoliko servera viska i slicno ..

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Meni licno se dopada nacin na koji delphi prilazi radu sa bazom, tako da mi dodje (posto trebam da oradim jednu manju aplikaciju) da je uradim u njemu. Vidim u novoj veziji XE studio ima mogućnost kompajliranja za iOS i XOS tako da nije ni to lose pored toga što je aplkacija native. Izvinite za off.

Ako ti se svidja delfi - overi obavezno Lazarus delimicno (meni skroz ali kazu ljudi da neke stvari ne rade) kompatibilan sa delfijem ali je free + kompajlira na i windozi i na linuxu native aplikaciju (vrlo moguce i da vec ima varijanta za mobilce ali nisam trazio - ovo moje go*no od blackberija svakako ne radi sa njim). Ja nisam probao najnoviji delfi (presao na lazarus za te pascalolike stvari) ali onaj zadnji sto sam probao je pravio .not kod a ne nativni kod sto je veliki nono za mene tako da je odleteo sa masine pod odma

ORM je ok koncept za "brzo programiranje i lako odrzavanje koda ako nemate kvalitetne developere" posebno kada se koristi "univerzalno resenje" ali ako hocete kvalitetan izlaz koji je optimalan, skalabilan ... kvalitetniji developeri i malo vise rada su neophodni.. obzirom da sada i manji enterprise operise sa terabajtima podataka "hardware nije toliko jeftin" kao sto se misli!

No da ne idemo u detalje oko ORM-a, ako "morate" da koristite ORM nekoliko saveta

1. koristite innodb

2. obavezno predjite na 5.5 (i na 5.6 cim bude GA) posto odradjuje te "usr*ne" upite koje ORM generise mnogo bolje od 5.1 ili nedaj boze 5.0

3. razmislite o pgsql-u!!! mysql je mnooooogo dobar rdbms ali je njegova osnovna snaga u dobrim optimizovanim upitima, ako ga tako koristite ne postoji rdbms koji moze da se poredi sa mysql-om, ali ako generisete ogavne gubave upite (kao sto to radi skoro svaki ORM) to je ono gde je mysql losiji od konkurencije... i to *mnogo losiji* te ako ne mozete da promenite tehnologiju na kojoj radi app, onda turite pgsql koji je isto open source, isto vrlo dobro dokumentovan, odlicno podrzan etc etc a koji odradjuje te ogavne upite mnogo bolje od mysql-a. (naravno uvek je tu oracle :D no pricamo o open source resenjima koja ne kostaju bubreg i levo plucno ...) ... elem da budem precizan - kada kazem konkurencija mislim na pgsql, NE mislim na m$sql koji ni jednu ni drugu vrstu upita ne odradjuje kako treba tako da on nije uopste "igrac" u ovoj prici...

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Juče sam se dogovarao sa starijim kolegom i on insistira da to bude Java. Barem sam uspeo da ga odgovorim od hibernatea i da odaberemo mybatis mali wrapper oko JDBC u kome sami definišete svoje upite, a kao zamenu za SWING da iskoristimo JAVAFX 2. Hvala Bogdane na savetima.

nema razloga za "odgovaranje" od jave ..

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Ne skontah je li ovo bio sarkazam ili stvarno misljenje?