Hastighed ved (sekventiel) skrivning til MySQL-database

Den væsentligste optimering får du ved at minimere antallet af queries til databasen.
dvs istedetfor 1000 kald til insert kunne du skrive din kommando som:

        String sqlKommando = "insert into test1 values ( \"+0+\", 'Hej' )";
        for (int i=1;i<antal;i++) {  // NB starter med 1
            sqlKommando += ",( \"+" +i +"+\", 'Hej' )";
        }
        stmt.executeUpdate( sqlKommando );

mvh JakobA

Ups. nu fatter jeg dit "+i+"

        String sqlKommando = "insert into test1 values ( 0, 'Hej' )";
        for (int i=1;i<antal;i++) {  // NB starter med 1
            sqlKommando += ",( " +i +", 'Hej' )";
        }
        stmt.executeUpdate( sqlKommando );

mange databaser vil give en stor gevindst ved at bruge PreparedStatement fremfor
Statement men ikke MySQL ifølge mine målinger

hvis du bruger MyISAM tabeller så er det alt for lidt (forudsat nyere hardware)

hvis du bruger InnoDB tabeller så lyder det meget sandsynligt

men du vil kunne speede det meget op ved at slå auto commit fra på connection
og så lave alle insert i en enkelt transaktion (kun en enkelt commit til sidst)

->arne_v:

Ja, PreparedStatement giver i første omgang ikke noget særligt:

import java.sql.*;

public class Testing2 {   
    public static void main(String[] args) throws Exception {
        int antal=1000;
        long starttid, sluttid;
        starttid = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test");
        Statement stmt=conn.createStatement();
        stmt.executeUpdate("drop table test1");
        stmt.executeUpdate("create table test1 (i int, v varchar(12))");
        PreparedStatement pstmt=conn.prepareStatement("insert into test1 values(?,?)");
        for (int i=0;i<antal;i++) {
            pstmt.setInt(1,i);
            pstmt.setString(2,"Hej");
            pstmt.executeUpdate();
        }
        stmt.close();
        conn.close();       
        sluttid = System.currentTimeMillis();
        System.out.println("Testing2: "+(sluttid-starttid)*0.001 +" sek.");
    }   
}


Men som du skriver, giver det en meget kraftig forbedring at slå autocommit fra:

import java.sql.*;

public class Testing3 {   
    public static void main(String[] args) throws Exception {
        int antal=100000;
        long starttid, sluttid;
        starttid = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test");
        conn.setAutoCommit(false);
        Statement stmt=conn.createStatement();
        stmt.executeUpdate("drop table test1");
        stmt.executeUpdate("create table test1 (i int, v varchar(12))");
        for (int i=0;i<antal;i++) {
            stmt.executeUpdate("insert into test1 values ("+i+",'Hej')");
        }
        conn.commit();
        conn.setAutoCommit(true);
        stmt.close();
        conn.close();       
        sluttid = System.currentTimeMillis();
        System.out.println("Testing3: "+(sluttid-starttid)*0.001 +" sek.");
    }   
}


Og endelig med både PreparedStatement og autocommit slået fra fås bedste performance:

import java.sql.*;

public class Testing4 {   
    public static void main(String[] args) throws Exception {
        int antal=100000;
        long starttid, sluttid;
        starttid = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test");
        conn.setAutoCommit(false);       
        Statement stmt=conn.createStatement();
        stmt.executeUpdate("drop table test1");
        stmt.executeUpdate("create table test1 (i int, v varchar(12))");
        PreparedStatement pstmt=conn.prepareStatement("insert into test1 values(?,?)");
        for (int i=0;i<antal;i++) {
            pstmt.setInt(1,i);
            pstmt.setString(2,"Hej");
            pstmt.executeUpdate();
        }
        conn.commit();
        conn.setAutoCommit(true);
        stmt.close();
        conn.close();       
        sluttid = System.currentTimeMillis();
        System.out.println("Testing4: "+(sluttid-starttid)*0.001 +" sek.");
    }   
}

Smid gerne et svar.

svar

Husk at gevindsten ved PreparedStatement er meget stor i andre databaaser.

Se http://www.eksperten.dk/artikler/830 nederst for nogle Oracle og MySQL tal

se Using Batches Instead of Prepared Statements
http://www.theserverside.com/articles/article.tss?l=JDBCPerformance_PartIII
som jakoba også er inde på. Jeg ville dog ikke bruge concatenation på String da det selv har et performance problem.. ihverfald i disse tests.

batch er meget effektivt hvis netværks hastighed er flaskehalsen

jeg tvivler på at det er tilfældet med localhost

der bør det være disk IO som er flaskehalsen

men det er da nemt at teste

jep det giver intet her i en lokal test.

Jeg undrer mig lidt over, at skrivning er væsentlig langsommere end læsning fra databasen, jf.:

import java.sql.*;

public class Testing4 {   
    public static void main(String[] args) throws Exception {
        int antal=10000;
        long starttid, sluttid;
        Class.forName("com.mysql.jdbc.Driver");
       
    // Skriver:
    Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test");
        conn.setAutoCommit(false);       
        Statement stmt=conn.createStatement();
        starttid = System.currentTimeMillis();
        stmt.executeUpdate("drop table test1");
        stmt.executeUpdate("create table test1 (i int, v varchar(12))");
        PreparedStatement pstmt=conn.prepareStatement("insert into test1 values(?,?)");
        for (int i=0;i<antal;i++) {
            pstmt.setInt(1,i);
            pstmt.setString(2,"Hej");
            pstmt.executeUpdate();
        }
        conn.commit();
        conn.setAutoCommit(true);
        stmt.close();
        conn.close();       
        sluttid = System.currentTimeMillis();
        System.out.println("Skrive: "+(sluttid-starttid)*0.001 +" sek.");
       
    // Læser:
        conn=DriverManager.getConnection("jdbc:mysql://localhost/test");
        stmt=conn.createStatement();
        starttid = System.currentTimeMillis();
        ResultSet rs=stmt.executeQuery("select * from test1");
        while (rs.next()) {
            int i=rs.getInt("i");
            String v=rs.getString("v");
        }
    stmt.close();
        conn.close();       
        sluttid = System.currentTimeMillis();
        System.out.println("Læse: "+(sluttid-starttid)*0.001 +" sek.");
    }   
}

I eksemplet tager det på min arbejds-PC 25 sek. at skrive 100.000 records, mens det kun tager 1,6 sek. at læse dem ind igen.

Hvorfor den store forskel? Efter min vurdering er 25 sek. for at skrive 100.000 records temmelig lang tid. Kan skrive-delen effektiviseres yderligere?

Der er ikke noget overraskende i at læsning specielt sekventiel læsning er
mange gange hurtigere end skrivning

databaser cacher data pages

en faktor 10-20-30 er helt normalt

100000 recs på 25 sekund er 1 på 0.25 millisekund

hvis du sammenligner med seek tider på din har disk, så er det ikke så ringe
endda !

->arne_v: Rart at få bekræftet.

Tænker på om der er metoder til at forbedre skrive-hastigheden, når der er tale om sekventiel skrivning, som sagtens kunne skrives ned i databasen i større klumper (?)

Hvis man f.eks. skriver data direkte til en Stream af en slags, er skrivehastigheden mange gange større.

Der er ingen super løsning.

Skrivning til database afhænger grundliggende af disk systemer. 15K SCSI diske i
RAID 1+0 er godt. Men også dyrt.

Hvis du kan undvære transaktioner så kan du skifte fra InnoDB tabeller til
MyISAM tabeller. Skrivning til dem er meget hurtigere.

Hvis du har brug for transaktioner og vil have højere skrive hastighed så er
SQLServer og Oracle noget hurtigere.

->arne_v: Skift fra InnoDB til MyISAM giver en besparelse på ca. 15%. Sætter jeg yderligere en

stmt.executeUpdate("lock tables test1 write");

efter

stmt.executeUpdate("create table test1 (i int, v varchar(12))");

og en

stmt.executeUpdate("unlock tables");

efter

conn.commit();

- opnår jeg en yderligere besparelse på ca. 8%.

Du kan nok også spare et par procent ved at bruge CHAR(12) istedet for VARCHAR(12). Så bliver alle records lige lange så DB-filen er nemmere at slå op i.

ah - sorry - den store forskel er mellem MyISAM og InnoDB med autocommit on

->arne_v: Det har du ganske ret i; det er virkelig stor forskel - man kan ligefrem høre harddisken arbejde med InnoDB og autocommit on, mens MyISAM kører glat igennem (også med autocommit on).

og det samme gælder forskellen på InndoDB og andre databaser - det er med autocommit
on (eller hyppige commit) at den store forskel er der

med 4000 INSERT/sekund er du nok stort set begrænset af disk systemet

rekorden i TPC-C er 3.2 millioner TPM hvilket er >53000 commits per sekund
(og noget mere komplekse transaktioner end din)

de brugte et disk system med 6400 diske for at gøre det