[es] - Brzina INSERT komande

Igor Gajic

Član broj: 93194
Poruke: 747
*.neobee.net.

+987 Profil

^{20.02.2009. u 13:09 - pre 184 meseci}

E ovako,
koristim

MySql Server v5.0
.NET Connector 5.2.2
VS 2008 Professional
C#

Imam na jednom mestu u programu da upisem ~100.000 redova u tabelu. Podaci se nalaze u DataTable, a insert se vrsi preko MySqlCommand .

Kod je:

[code]

MySqlCommand cmdUbaciPromene = new MySqlCommand(@"INSERT INTO Promene (idPromene,
Konto,
BrojNaloga,
DatumKnjizenja,
DatumValute,
DatumDPO,
DatumDokumenta,
Duguje,
Potrazuje,
Opis,
Rasknjizen,
BrojPovezivanja)
VALUES (?id,
?Konto,
?BrojNaloga,
?DatumKnjizenja,
?DatumValute,
?DatumDPO,
?DatumDokumenta,
?Duguje,
?Potrazuje,
?Opis,
?Rasknjizen,
?BrojPovezivanja);", conn);

[code]

i za svaki red u tabeli postavim vrednosti parametara i pozovem INSERT komandu.

Problem je u trajanju ove operacije. Potraje dobrih 45 minuta na Core2Duo procesoru.
Probao sam isto da uradim i u MySQL Query Browser-u, tj. napisem ~100.000 INSERT komandi (copy-paste),
i ista brzina.

Da li postoji neka MySQL komanda za brze ubacivanje redova, a da nije LOAD DATA INFILE... ?

Pokusavam da uradim insert bez nekih temp fajlova...

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.mysql.com.

Sajt: mysql.rs

+2377 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 10:09 - pre 184 meseci}

moracu da ti se izvinim ali zbog mog odnosa prema .net tehnologiji (gadjenje je blaga rec) ne znam kako to tacno da uradis koristeci te kontrole, ali teoretski treba da koristis BULK INSERT.

Sta to znaci, znaci da umesto:

Code:

insert into t1 values (1,2,3);
insert into t1 values (2,2,3);
insert into t1 values (3,2,3);
insert into t1 values (4,2,3);
...

uradis mnoooooooogo puta brzu stvar:

Code:

insert into t1 values (1,2,3), (2,2,3), (3, 2, 3)...;

Ako se niko ne javi sa idejom, pitacu u ponedeljak kolegu koji pravi .net konektor da li ima neka fora da mu se kaze da to uradi sam...

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

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.mysql.com.

Sajt: mysql.rs

+2377 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 10:21 - pre 184 meseci}

upravo sam napravio test, ucitavam 100K redova "jedan po jedan" pa onda "bulk" razlika u brzini je ocigledna

Code:

[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../100K.sh )

real    0m2.223s
user    0m0.286s
sys    0m0.250s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../100Kx.sh )

real    0m0.072s
user    0m0.007s
sys    0m0.004s

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

Odgovor na temu

Igor Gajic

Član broj: 93194
Poruke: 747
79.101.168.*

+987 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 12:13 - pre 184 meseci}

Hvala na odgovoru.

Sada sam isprobao brzine i razlika je drasticna

Code:

Insert 10000 redova u MySql bazu.

Obican insert vreme : 00:05:36.3437500

BULK insert vreme : 00:00:00.1716750

na procesoru Q6600 sa 2GB ram.

Samo sada postoji problem oko max_packet_length jer duzina BULK INSERT komande moze biti >1MB koliko je default vrednost velicine network paketa. Ali mislim da se i to moze zaobici parcelisanjem INSERT komande, tj umesto jedne koristim 5-6 BULK INSERT komandi.

Inace ako nekog zanima test:

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.mysql.com.

Sajt: mysql.rs

+2377 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 13:01 - pre 184 meseci}

Citat:

Igor Gajic
Samo sada postoji problem oko max_packet_length jer duzina BULK INSERT komande moze biti >1MB koliko je default vrednost velicine network paketa. Ali mislim da se i to moze zaobici parcelisanjem INSERT komande, tj umesto jedne koristim 5-6 BULK INSERT komandi.

da, bas tako, podelis ga na nekoliko bulk inserta ...

mada lako je povecati max_allowed_packet http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html ako je u pitanju tvoj mysql server. ako je neki hosting u pitanju onda :( ... mada, nisam video nijedan hosting da im max_allowed_packet nije bar 8M sto nije malo :)

dodatno mozes da ubrzas insert na myisam tabeli ako uradis:

Code:

ALTER TABLE t1 DISABLE KEYS;

INSERT INTO ...

INSERT INTO ...

ALTER TABLE t1 ENABLE KEYS;

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Citat:

This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11.

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

Odgovor na temu

Igor Gajic

Član broj: 93194
Poruke: 747
79.101.168.*

+987 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 13:12 - pre 184 meseci}

U pitanju su baze koje se vrte kod korisnika, pa nam je daleko lakse napraviti 5-6 BULK insert komandi dinamicki nego im objasnjavati gde se i kako menja velicina paketa :(

Pretpostavljam da DISABLE KEY daje najvece prednosti ukoliko su obe tabele povece. Isprobacu da napravim par tabela sa FK relacijom cisto da vidim kolika je prednost u %.

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.mysql.com.

Sajt: mysql.rs

+2377 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 13:19 - pre 184 meseci}

Citat:

. Isprobacu da napravim par tabela sa FK relacijom cisto da vidim kolika je prednost u %.

rekoh da to enable/disable key radi za myisam ... za innodb mislim da nece biti nikakve razlike u brzini - no, probaj ako te ne mrzi pa javi rezultate :)

generalno sve zavisi od izgleda tabele, ako imas tabelu sa dosta non unique kljuceva (npr datumi, full text polje...) onda je ubrzanej veliko, ako imas samo primarnu kljuc - nema razlike

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

Odgovor na temu

Igor Gajic

Član broj: 93194
Poruke: 747
79.101.168.*

+987 Profil

Re: Brzina INSERT komande

^{21.02.2009. u 14:10 - pre 184 meseci}

Uradio sam merenja i rezultati su me malo iznenadili.

Skript za dve koriscene tabele:

Code:

CREATE TABLE masterT
(
idmaster INTEGER(10) PRIMARY KEY AUTO_INCREMENT,
vrednost CHAR(30) UNIQUE COLLATE latin1_general_ci NOT NULL ,
val3 INTEGER(10) NOT NULL
) engine=MyISAM;

CREATE TABLE detailT
(
iddetail INTEGER(10) PRIMARY KEY AUTO_INCREMENT,
vrednost INTEGER(10) NOT NULL,
mastervrednost CHAR(30)COLLATE latin1_general_ci NOT NULL,
CONSTRAINT `FK_myisam_2` FOREIGN KEY (`mastervrednost`) REFERENCES `masterT`(`vrednost`)
) engine=MyISAM;

Rezultati:

MyISAM engine:

Code:

Master tabela 4000 redova
Detail tabela 50000 redova

vreme BEZ disable:   0.3281250
vreme SA disable:     0.437500

Master tabela 4000 redova
Detail tabela 300000 redova

vreme BEZ disable:   2.1875000
vreme SA disable:     3.046875

InnoDB engine:

Code:

Master tabela 4000 redova
Detail tabela 50000 redova

vreme BEZ disable:   0.75125
vreme SA disable:     0.71875

Master tabela 4000 redova
Detail tabela 300000 redova

vreme BEZ disable:   5.8718750
vreme SA disable:   14.546875

Program za testiranje:

Code:

    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder mscsb=new MySqlConnectionStringBuilder();
            mscsb.Server="localhost";
            mscsb.UserID="root";
            mscsb.Database="testBULK";

            MySqlConnection conn=new MySqlConnection(mscsb.ToString());

            DataTable TabelaMaster = new DataTable();
            TabelaMaster.Columns.Add("idMaster", typeof(int));
            TabelaMaster.Columns.Add("vrednost", typeof(string));
            TabelaMaster.Columns.Add("val3", typeof(int));

            DataTable TabelaDetail = new DataTable();
            TabelaDetail.Columns.Add("idDetail", typeof(int));
            TabelaDetail.Columns.Add("vrednost", typeof(int));
            TabelaDetail.Columns.Add("mastervrednost", typeof(string));

            for (int i = 0; i < 4000; i++)
            {
                TabelaMaster.Rows.Add(new object[] {null,i.ToString(),i});
            }

            Random rnd = new Random((int)DateTime.Now.Ticks);
            for (int i = 0; i < 300000; i++)
            {
                TabelaDetail.Rows.Add(new object[] { null, i, (rnd.Next()%4000).ToString() });
            }

            Console.WriteLine("Velicina master tabele: " + TabelaMaster.Rows.Count + " redova");
            Console.WriteLine("Velicina detail tabele: " + TabelaDetail.Rows.Count + " redova");

            try
            {
                conn.Open();
                MySqlCommand cmdInsertMaster = new MySqlCommand(NapraviBULKINSERTMaster(TabelaMaster), conn);
                DateTime pocetak = DateTime.Now;
                cmdInsertMaster.ExecuteNonQuery();
                DateTime kraj = DateTime.Now;
                Console.WriteLine("Master insert vreme : " + kraj.Subtract(pocetak).ToString() + Environment.NewLine);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
            }

            try
            {
                conn.Open();
                MySqlCommand cmdInsertDetail = new MySqlCommand(NapraviBULKINSERTDetail(TabelaDetail), conn);
                DateTime pocetak = DateTime.Now;
                cmdInsertDetail.ExecuteNonQuery();
                DateTime kraj = DateTime.Now;
                Console.WriteLine("Detail insert vreme (bez DISABLE KEYS): " + kraj.Subtract(pocetak).ToString() + Environment.NewLine);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
            }

            try
            {
                conn.Open();
                MySqlCommand cmdDisableKeys = new MySqlCommand("ALTER TABLE detailT DISABLE KEYS;", conn);
                MySqlCommand cmdEnableKeys = new MySqlCommand("ALTER TABLE detailT ENABLE KEYS;", conn);

                MySqlCommand cmdInsertDetail = new MySqlCommand(NapraviBULKINSERTDetail(TabelaDetail), conn);
                DateTime pocetak = DateTime.Now;

                cmdDisableKeys.ExecuteNonQuery();           //Ponistim FK
                cmdInsertDetail.ExecuteNonQuery();          //ubacim podatke
                cmdEnableKeys.ExecuteNonQuery();            //vratim FK

                DateTime kraj = DateTime.Now;
                Console.WriteLine("Detail insert vreme (sa DISABLE KEYS): " + kraj.Subtract(pocetak).ToString() + Environment.NewLine);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
            }

            Console.ReadKey();
        }

        public static string NapraviBULKINSERTMaster(DataTable dt)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO masterT (idMaster,vrednost,val3) VALUES ", dt.Rows.Count * 100);
            foreach (DataRow dr in dt.Rows)
                sb.Append(string.Format("(null,'{0}',{1}),", dr[1], dr[2]));

            return (sb.Remove(sb.Length - 1, 1)).ToString(); //Ukloni poslednji zarez i vrati string
        }

        public static string NapraviBULKINSERTDetail(DataTable dt)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO detailT (idDetail,vrednost,mastervrednost) VALUES ", dt.Rows.Count * 100);
            foreach (DataRow dr in dt.Rows)
                sb.Append(string.Format("(null,{0},'{1}'),", dr[1], dr[2]));

            return (sb.Remove(sb.Length - 1, 1)).ToString(); //Ukloni poslednji zarez i vrati string
        }
    }

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.mysql.com.

Sajt: mysql.rs

+2377 Profil

Re: Brzina INSERT komande

^{22.02.2009. u 06:21 - pre 184 meseci}

zanimljivi rezultati, doduse ne preterano validni posto je test neprecizan :(

generalno, mnogo sve zavisi od konfiguracije, sto masine, sto mysql-a, koliki je innodb buffer pool na primer za innodb, posto ti je mozda ceo prvi test iskesiran a dok izvrsavas drugi test tu ti ulazi vreme snimanja prvog testa na disk + izvrsavanje drugog testa .. i slicno .. za myisam opet .. system file cache .. koliki je i slicno ...

najbolje bi bilo kada bi mogao da, resetujes mysql pre svakog testa i odradis "sync" pre svakog i posle svakog testa :)

btw, vezano za disable keys ... mysql mora da napravi kljuceve pa mora, da bi tabela bila validna, fora kod disable keys je da myisam ima foru da brze izgenerise kljuceve odjednom nad tabelom nego insert po insert ... kod innodb-a to nije slucaj i trebalo bi da je "tu negde" po brzini sa i bez disable keys ...

ono sto je bitno je da tvoja primer tabela nije "idealna" cak - obrnuto je od idealne posto ima samo unique kljuceve (koji nisu afektirani ovim pristupom i potvrdno ovaj pristup usporava sistem umesto da ubrzava)... dakle, samo non unique kljucevi su impaktirani ovim pristupom, za unique kljuceve "disable key" ne radi nista, a enable key mora da prodje kroz sve da proveri el sve ok (sto dodatno uspori proces) ...

probaj sa:

Code:

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL,
  KEY `x` (`x`),
  KEY `y` (`y`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

evo primer kod mene

Code:

[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../nobulk.sh )

real    0m3.155s
user    0m0.292s
sys    0m0.288s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../bulk.sh )

real    0m0.127s
user    0m0.010s
sys    0m0.002s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../endis.sh )

real    0m2.217s
user    0m0.303s
sys    0m0.317s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../endisbulk.sh )

real    0m0.132s
user    0m0.010s
sys    0m0.001s

dakle ..
1 po 1 .. 100K komada 3.155
1 po 1 .. 100K komada sa disable/enable key 2.217sec

bulk 100K komada 0.127
bulk 100K komada sa disable/enable0.132sec

sta mozemo da zakljucimo ... da kod bulk-a i nije neko poboljsanje .. sto je normalno posto bulk svejedno (sam za sebe jedan jedini) doda pa odradi key sto znaci da smo samo ima 2 statementa vise nego bez disable/enable key ... ali da je tu bilo 6 bulkova umesto samo jedan bilo bi neke znacajnije razlike (btw ova razlika od 0.01sec vise ima veze sa tim da li ja mrdam misa tokom testa nego sa realnim rezultatima a mrzi me sad da pravim test sa 500K redova ili vise posto bi to mozda napravilo neku znacajniju razliku)

kod normalnog inserta vidimo vec ozbiljnu razliku (1sec ili 30% ubrzanje) koje donosi disable/enable key ...

obrati paznju na izgled tabele .. dakle ovo je "idealna" tabela nad kojom ovakav pristup donosi ubrzanje, nema unioque indexa (na koje se to inace ne odnosi) i sva polja (oba) su kljucevi (non unique)....

e sad .. ista stvar za innodb ... (identicna tabela samo innnodb)

Code:

[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../nobulk.sh )

real    0m34.017s
user    0m0.343s
sys    0m0.281s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../bulk.sh )

real    0m0.712s
user    0m0.007s
sys    0m0.006s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../endis.sh )

real    0m33.850s
user    0m0.300s
sys    0m0.294s
[arhimed@gedora10 bin]$ time (./mysql -uroot test < ../../endisbulk.sh )

real    0m0.727s
user    0m0.009s
sys    0m0.001s

sta reci .. osim da je inndb u "standardnom insertu" mnogo sporiji od myisam-a .. disable/enable key nije napravio skoro nikakvu razliku .. (0.2sec za obican i 0.01sec za bulk - sto je kao sto napomenuh razlika da li mi je stigla poruka na skype dok je isao test ili sam mrdno misa) ....

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

Odgovor na temu