SQL zu langsam?

[Markus Ralf]

Hallo,

wir möchten über eine JDBC-Verbindung Daten in einer Tabelle auf einer iSeries (Modell 800 1 GB RAM) einfügen. Die Ausführungszeit beträgt ca. 400 ms pro Datensatz!!!.

Wo sind die entsprechenden Schrauben, an denen wir drehen müssen, damit es ein bissl schneller wird?

Danke für einen guten Hinweis.

Markus

[BenderD]

Hallo,

400 ms ist in der Tat schwach, das müsste eigentlich zigmal so schnell gehen, aber ohne jede Angabe kann man da nichtmal raten. Wenn es eine Schraube gäbe, an der man grundsätzlich auf schnell stellen könnte, dann stünde die bereits auf schnell.

mfg

Dieter Bender

Hallo,

wir möchten über eine JDBC-Verbindung Daten in einer Tabelle auf einer iSeries (Modell 800 1 GB RAM) einfügen. Die Ausführungszeit beträgt ca. 400 ms pro Datensatz!!!.

Wo sind die entsprechenden Schrauben, an denen wir drehen müssen, damit es ein bissl schneller wird?

Danke für einen guten Hinweis.

Markus

[holgerscherer]

wir möchten über eine JDBC-Verbindung Daten in einer Tabelle auf einer iSeries (Modell 800 1 GB RAM) einfügen. Die Ausführungszeit beträgt ca. 400 ms pro Datensatz!!!.

Auf den ersten Blick äh Riecher scheint da für jeden Satz eine gekapselte Transaktion gestartet zu werden, oder es hängen noch diverse viele Indexe hinter der Zieldatei. Oder ein mächtiges Exit-Programm, das auch noch ein Joblog produziert.
Und vielleicht noch eine kleine CPU (alles unter 1000CPW ist für SQL schon fast zu knapp).

Also: Her mit mehr Infos ;-)

-h

[BenderD]

@Holger: ich widerspreche dir ungern, aber auf meiner kleinen 170 geht das mit Transaktionssicherung um den Faktor zig mal schneller. Zeiten von 400 ms erreicht man nur mit kaputtem Treiber und der passenden Konstellation, bei vorhanden sein eines ernsthaften Ressourcen Engpasses, oder mit ernsthaften Fehlern im Programm oder Datenbankdesign.

Dieter Bender

Auf den ersten Blick äh Riecher scheint da für jeden Satz eine gekapselte Transaktion gestartet zu werden, oder es hängen noch diverse viele Indexe hinter der Zieldatei. Oder ein mächtiges Exit-Programm, das auch noch ein Joblog produziert.
Und vielleicht noch eine kleine CPU (alles unter 1000CPW ist für SQL schon fast zu knapp).

Also: Her mit mehr Infos ;-)

-h

[holgerscherer]

@Holger: ich widerspreche dir ungern, aber auf meiner kleinen 170 geht das mit Transaktionssicherung um den Faktor zig mal schneller. Zeiten von 400 ms erreicht man nur mit kaputtem Treiber und der passenden Konstellation, bei vorhanden sein eines ernsthaften Ressourcen Engpasses, oder mit ernsthaften Fehlern im Programm oder Datenbankdesign.

Hallo Dieter,
ich meinte ja auch eher, dass der Treiber für jeden Satz eine eigene Transaktion verwendet oder gar gleich eigene Connections dafür macht. Habe ich mal gesehen, mir fällt nur grade nicht ein, welcher Treiber das war.
Vielleicht nur eine unglückliche Konfigurationseinstellung?

-h

[BenderD]

Hallo,

ein Commit (transaction) liegt mit Ausnahme von Transaktionen mit zigtausend updates im untersten Bereich von milli sekunden eine Connection etwas darüber, selbst auf einer schwächlichen Maschine sind das im ungünstigsten Fall 10 ms, es sei denn die Maschine erstarrt völlig in der Furcht vor dem Web; im vorliegenden Fall müssen zuerst die anderen 390 ms gefunden werden, eh' man sich um diese Sachen kümmert; auch irgendwelche Indices tragen nur im Millisekunden Bereich bei und verschwinden noch mit in den 10 ms. Eh' man hier Feintuning macht, muss die Hauptursache weg. Unglückliche Konfigurationseinstellung? solange man nicht weiss welche Einstellungen denn vorgenommen wurden...m.E. reicht da auch Pech eher nicht aus...

mfg

Dieter Bender

Hallo Dieter,
ich meinte ja auch eher, dass der Treiber für jeden Satz eine eigene Transaktion verwendet oder gar gleich eigene Connections dafür macht. Habe ich mal gesehen, mir fällt nur grade nicht ein, welcher Treiber das war.
Vielleicht nur eine unglückliche Konfigurationseinstellung?

-h

[Markus Ralf]

Hallo Dieter,

nach diversen Tests haben wir mittlerweile folgendes rausgefunden:

- Wir hatten die Einstellung "extended metadata=true" im Connection-String angegeben. Wurde ursprünglich benötigt, um diverse Meta-Daten "genauer" ermitteln zu können. Nach dessen Abschaltung waren die Zeiten runter auf ca. 150 ms.

- setAutoCommit(false) hat dann eine weitere Reduzierung auf ca. 15 ms gebracht.

Das ist jetzt schonmal wesentlich besser Nur warum das bei aktivierten extended metadata so extrem langsamer wird, kann wohl nur IBM sagen...

Zur "Umgebung":
- iSeries 800, Prozessor 2463, 1 GB RAM, 2 x 35 GB Platte
- 200er Satzlänge, nur 1 LF für die PF vorhanden

System-Pools (2 - 4 stehen auf *CALC):

System    Pool-    Reserv.    Max.           
 Pool   Größe(M)  Größe (M)  Aktiv  Pool     
   1      115,36     60,49   +++++  *MACHINE 
   2      831,99      0,66     124  *BASE    
   3       55,85      <.01      26  *INTERACT
   4       10,13      0,00       8  *SPOOL

Nur so nebenbei erwähnt: Spaßeshalber mal 'ne MySQL auf einem stinknormalen Bürorechner installiert, der neben der iSeries steht und dort eine entspr. Tabelle erzeugt. Das Einfügen von Sätzen dauert dabei 'ne knappe Millisekunde... Auch wenn das jetzt kein echter Vergleich ist

Markus

Hallo,

ein Commit (transaction) liegt mit Ausnahme von Transaktionen mit zigtausend updates im untersten Bereich von milli sekunden eine Connection etwas darüber, selbst auf einer schwächlichen Maschine sind das im ungünstigsten Fall 10 ms, es sei denn die Maschine erstarrt völlig in der Furcht vor dem Web; im vorliegenden Fall müssen zuerst die anderen 390 ms gefunden werden, eh' man sich um diese Sachen kümmert; auch irgendwelche Indices tragen nur im Millisekunden Bereich bei und verschwinden noch mit in den 10 ms. Eh' man hier Feintuning macht, muss die Hauptursache weg. Unglückliche Konfigurationseinstellung? solange man nicht weiss welche Einstellungen denn vorgenommen wurden...m.E. reicht da auch Pech eher nicht aus...

mfg

Dieter Bender

[BenderD]

Hallo Markus,

um mit dem letzten anzufangen: doch, doch das ist ein Vergleich, das muss man von einer AS400 auch erwarten, wenn sie Konkurrenz fähig sein soll. Die 15 ms sind jedenfalls immer noch schlapp. Eine simple Datenbankoperation muss immer im Bereich von max. 2-3 ms liegen, gemessen in der Datenbank - bei dieser Gelegenheit, wie messt ihr eure Zeiten und was liegt alles im Messintervakll drin?.
Das mit den extended Metadata deutet irgendwo auf einen Treiber Bug, welchen verwendet ihr denn (Toolbox oder native, welches Jar File)? ich würde mal eine neuere Version/anderen Treiber ausprobieren.
Die Poolgrößen sagen nix aus, wichtig sind die Paging Raten und da insbesondere dy synchronous für Database und non Database und die CPU Auslastung; die große Differenz für Commit versus ohne Commit könnte auf Plattenengpässe hindeuten, was habt ihr für Platten an der Büchse (Typ und Anzahl)?
Und es fehlt noch gänzlich eine Angabe dazu um was für eine Anwendung sich das handelt? Servlet/JSP/EJB, welcher AppServer? Mix RPG Java, Batch oder interaktiv?

mfg

Dieter Bender

Hallo Dieter,

nach diversen Tests haben wir mittlerweile folgendes rausgefunden:

- Wir hatten die Einstellung "extended metadata=true" im Connection-String angegeben. Wurde ursprünglich benötigt, um diverse Meta-Daten "genauer" ermitteln zu können. Nach dessen Abschaltung waren die Zeiten runter auf ca. 150 ms.

- setAutoCommit(false) hat dann eine weitere Reduzierung auf ca. 15 ms gebracht.

Das ist jetzt schonmal wesentlich besser Nur warum das bei aktivierten extended metadata so extrem langsamer wird, kann wohl nur IBM sagen...

Zur "Umgebung":
- iSeries 800, Prozessor 2463, 1 GB RAM, 2 x 35 GB Platte
- 200er Satzlänge, nur 1 LF für die PF vorhanden

System-Pools (2 - 4 stehen auf *CALC):

System    Pool-    Reserv.    Max.           
 Pool   Größe(M)  Größe (M)  Aktiv  Pool     
   1      115,36     60,49   +++++  *MACHINE 
   2      831,99      0,66     124  *BASE    
   3       55,85      <.01      26  *INTERACT
   4       10,13      0,00       8  *SPOOL

Nur so nebenbei erwähnt: Spaßeshalber mal 'ne MySQL auf einem stinknormalen Bürorechner installiert, der neben der iSeries steht und dort eine entspr. Tabelle erzeugt. Das Einfügen von Sätzen dauert dabei 'ne knappe Millisekunde... Auch wenn das jetzt kein echter Vergleich ist

Markus

[Markus Ralf]

Hallo Dieter,

um mit dem letzten anzufangen: doch, doch das ist ein Vergleich, das muss man von einer AS400 auch erwarten, wenn sie Konkurrenz fähig sein soll. Die 15 ms sind jedenfalls immer noch schlapp. Eine simple Datenbankoperation muss immer im Bereich von max. 2-3 ms liegen, gemessen in der Datenbank - bei dieser Gelegenheit, wie messt ihr eure Zeiten und was liegt alles im Messintervakll drin?.

Ich erwarte von der iSeries nicht unbedingt die 0 ms von MySQL. Das Teil hat sonst überhaupt nichts zu tun, läuft allerdings aber auch auf 'nem stinknormalen Büro-PC. Aber 2-3 ms für die iSeries wären schon wirklich gut :-)

Die Zeit-Messung mache ich auf dem PC und zwar nur für die tatschliche Dauer des insertRow(). Source-Code siehe weiter unten. Die aktuellen Zeiten liegen zwischen 0 und 15 ms, mit vereinzelten Ausreißern von etwa 250 ms.

Das mit den extended Metadata deutet irgendwo auf einen Treiber Bug, welchen verwendet ihr denn (Toolbox oder native, welches Jar File)? ich würde mal eine neuere Version/anderen Treiber ausprobieren.

JTOpen 4.5, mit dem "originalen" aus CA V5R2 ist's aber auch nicht wirklich anders.

Die Poolgrößen sagen nix aus, wichtig sind die Paging Raten und da insbesondere dy synchronous für Database und non Database und die CPU Auslastung; die große Differenz für Commit versus ohne Commit könnte auf Plattenengpässe hindeuten, was habt ihr für Platten an der Büchse (Typ und Anzahl)?

Ich hoffe, dies sind die gewünschten Werte:

% CPU benutzt  . . . . . :       32,4
% DB-Kapazität . . . . . :       20,6

System    Pool-    Reserv.    Max.  -DB-Seiten--  --Nicht-DB--
 Pool   Größe(M)  Größe (M)  Aktiv  fehl.  geles  fehl.  geles
   1      115,81     60,55   +++++    0,0    0,0    3,2    3,2
   2      834,62      0,66     124    0,7    5,2    2,0    3,8
   3       52,77      <.01      26    0,0    0,0    0,1    0,1
   4       10,13      0,00       8    0,0    0,0    0,0    0,0

System    Pool-    Reserv.    Max.  Aktiv->  Warten->  Aktiv-> 
 Pool   Größe(M)  Größe (M)  Aktiv  Warten   n.wählb.  n.wählb.
   1      115,81     60,55   +++++    21,4      0,0       0,0  
   2      834,62      0,66     124   527,1      0,0       0,0  
   3       52,77      <.01      26     3,9      0,0       0,0  
   4       10,13      0,00       8     0,0      0,0       0,0  

Ein-        Größe  %be-    E/A   Anford.  Lese   Schrb.  Lesen  Schrb  %ver-
heit  Typ    (M)   legt   Anf.  Größe(K)   Anf.   Anf.    (K)    (K)   wend.
   1  6719  35165  40,9    8,2      9,2     1,9     6,3   17,6    6,7     2 
   2  6719  35165  41,1   12,9      6,8     1,8    11,0    6,8    6,8    10

Und es fehlt noch gänzlich eine Angabe dazu um was für eine Anwendung sich das handelt? Servlet/JSP/EJB, welcher AppServer? Mix RPG Java, Batch oder interaktiv?

Direkte JDBC-Anwendung zum Server, ohne zwischengeschalteten AppServer, kein RPG im Spiel, Programm läuft auf einem PC ab - und letzterer kann die Bremse nicht sein: AMD Athlon XP 3000+ mit 1 GB RAM, hat während der Testzeit genau wie die iSeries sonst nix zu tun. Hier mal mein Testprogramm:

package Test;

import com.ibm.as400.access.*;
import java.sql.*;

public class TestSqlInsert {
    
    public TestSqlInsert() {
        try {
            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
            Connection conn = DriverManager.getConnection("jdbc:as400://meinServer/$$FILE;extended metadata=false");
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            conn.setAutoCommit(false);
            for (int x=1; x<=10; x++) {
                ResultSet rs = stmt.executeQuery("Select * From LABDTA00");
                for (int i=1; i<=100; i++) {
                    rs.moveToInsertRow();
                    rs.updateString("LDSA", "LD");
                    rs.updateInt("LDAUFN", 123);
                    rs.updateDate("LDDATUM", new java.sql.Date(2004, 11, 25));
                    rs.updateInt("LDLFDNR", i);
                    rs.updateString("LDUSER", "User");
                    rs.updateString("LDMENUEID", "xyz");
                    rs.updateString("LDBLOCKID", "abc");
                    rs.updateString("LDFELDID", "qqq");
                    rs.updateString("LDDATEN", "Daten");
                    long start = System.currentTimeMillis();
                    rs.insertRow();
                    System.out.println("time " + (System.currentTimeMillis() - start));
                }
                conn.commit();
                stmt.execute("Delete From LABDTA00 Where LDAUFN = 123");
                conn.commit();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
    }
    
    public static void main(String[] args) {
        new TestSqlInsert();
        System.exit(0);
    }
    
}

Markus

[BenderD]

Hallo Markus,

fangen wir wieder mit dem einfachen an:
- CPU sollte nach deiner Beschreibung kein Problem sein, auch ersichtlich an den %Werten
- Hauptspeicher sollte auch okay sein, die 1 GB wären allerdings knapp gewesen, wenn das komplett auf der AS gelaufen wäre, die Pagingraten sehen für den Zeitraum gesund aus (Summe der beiden geles. Spalten pro Pool).
- der PC sollte auch kein Bottleneck darstellen, dann wäre auch der Vergleichswert mit MySQL anders ausgefallen
- deine as400 ist deutlich unbalanciert, die zwei langsamen Riesenhühner von Platte sind für die CPU eindeutig zu schlapp; diese Maschine ist für Produktionszwecke nicht geeignet - was immer du damit machst, die macht schlapp und verhält sich ruppig eventuell könnte hier auch der Grund für die Ausreißer liegen (lässt sich nur mit entsprechendem Monitoring erfassen)
- zum messen des Lastverhaltens wäre es sinnvoll den Database Monitor einzusetzen, der einem die Zeiten für jeden einzelnen Datenbankzugriff liefert (Connect, Cursor erstellen, jeden einzelnen fetch etc); diese Messung müsste einem auch die eigentliche Datenbakoperation von dem Geschraube an dem Resultset trennen.
Interessant wäre auch der Vergleich zur traditionellen Variante mit preparedStatement Object und executeUpdate, die JDBC 2.0 Sachen sind teilweise lausig implementiert im Treiber (z.B.: Batch update ist reiner Fake in der Version, bei der ich es probiert habe).

mfg

Dieter Bender

Hallo Dieter,



Ich erwarte von der iSeries nicht unbedingt die 0 ms von MySQL. Das Teil hat sonst überhaupt nichts zu tun, läuft allerdings aber auch auf 'nem stinknormalen Büro-PC. Aber 2-3 ms für die iSeries wären schon wirklich gut :-)

Die Zeit-Messung mache ich auf dem PC und zwar nur für die tatschliche Dauer des insertRow(). Source-Code siehe weiter unten. Die aktuellen Zeiten liegen zwischen 0 und 15 ms, mit vereinzelten Ausreißern von etwa 250 ms.



JTOpen 4.5, mit dem "originalen" aus CA V5R2 ist's aber auch nicht wirklich anders.



Ich hoffe, dies sind die gewünschten Werte:

% CPU benutzt  . . . . . :       32,4
% DB-Kapazität . . . . . :       20,6

System    Pool-    Reserv.    Max.  -DB-Seiten--  --Nicht-DB--
 Pool   Größe(M)  Größe (M)  Aktiv  fehl.  geles  fehl.  geles
   1      115,81     60,55   +++++    0,0    0,0    3,2    3,2
   2      834,62      0,66     124    0,7    5,2    2,0    3,8
   3       52,77      <.01      26    0,0    0,0    0,1    0,1
   4       10,13      0,00       8    0,0    0,0    0,0    0,0

System    Pool-    Reserv.    Max.  Aktiv->  Warten->  Aktiv-> 
 Pool   Größe(M)  Größe (M)  Aktiv  Warten   n.wählb.  n.wählb.
   1      115,81     60,55   +++++    21,4      0,0       0,0  
   2      834,62      0,66     124   527,1      0,0       0,0  
   3       52,77      <.01      26     3,9      0,0       0,0  
   4       10,13      0,00       8     0,0      0,0       0,0  

Ein-        Größe  %be-    E/A   Anford.  Lese   Schrb.  Lesen  Schrb  %ver-
heit  Typ    (M)   legt   Anf.  Größe(K)   Anf.   Anf.    (K)    (K)   wend.
   1  6719  35165  40,9    8,2      9,2     1,9     6,3   17,6    6,7     2 
   2  6719  35165  41,1   12,9      6,8     1,8    11,0    6,8    6,8    10

Direkte JDBC-Anwendung zum Server, ohne zwischengeschalteten AppServer, kein RPG im Spiel, Programm läuft auf einem PC ab - und letzterer kann die Bremse nicht sein: AMD Athlon XP 3000+ mit 1 GB RAM, hat während der Testzeit genau wie die iSeries sonst nix zu tun. Hier mal mein Testprogramm:

package Test;

import com.ibm.as400.access.*;
import java.sql.*;

public class TestSqlInsert {
    
    public TestSqlInsert() {
        try {
            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
            Connection conn = DriverManager.getConnection("jdbc:as400://meinServer/$$FILE;extended metadata=false");
            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            conn.setAutoCommit(false);
            for (int x=1; x<=10; x++) {
                ResultSet rs = stmt.executeQuery("Select * From LABDTA00");
                for (int i=1; i<=100; i++) {
                    rs.moveToInsertRow();
                    rs.updateString("LDSA", "LD");
                    rs.updateInt("LDAUFN", 123);
                    rs.updateDate("LDDATUM", new java.sql.Date(2004, 11, 25));
                    rs.updateInt("LDLFDNR", i);
                    rs.updateString("LDUSER", "User");
                    rs.updateString("LDMENUEID", "xyz");
                    rs.updateString("LDBLOCKID", "abc");
                    rs.updateString("LDFELDID", "qqq");
                    rs.updateString("LDDATEN", "Daten");
                    long start = System.currentTimeMillis();
                    rs.insertRow();
                    System.out.println("time " + (System.currentTimeMillis() - start));
                }
                conn.commit();
                stmt.execute("Delete From LABDTA00 Where LDAUFN = 123");
                conn.commit();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
    }
    
    public static void main(String[] args) {
        new TestSqlInsert();
        System.exit(0);
    }
    
}

Markus

[Fuerchau]

Es ist ja immer so schön einfach, die verfügbaren Funktionen zu nutzen ohne sich Gedanken über die Wirkung zu machen.
Insert/Updates auf Recordset-Ebene sind immer die langsamsten die es gibt.
Man beachte, was der Treiber bzw. die SQL-Schicht leisten muss, bevor es überhaupt zur Aktion kommt.

Ein Insert/Update wird vom Treiber intern erst zusammengebaut, prepared, ausgeführt und das Statement wieder freigegeben.
Wenn ich einen Recordset.Update() bzw. Recordset.Insert() kodiere ist also einiges zu tun.
Wobei ich mich beim Update frage, wo der Schlüsselbezug herkommt oder der korrekte Satz automatisch ermittelt wird.

Im Gegenzug ist ein selbstgestricktes Prepared-Statement um Faktoren schneller.
Also ein "insert into myfile (f1, f2, ...) values(?, ?, ...)" bzw. ein "update myfile set f1=?, f2=?,... where Key=?" mit der entsprechenden Execute-Methode.

[holgerscherer]

Ein Insert/Update wird vom Treiber intern erst zusammengebaut, prepared, ausgeführt und das Statement wieder freigegeben.

Wenn ich mir so die CPU-Auslastung anschaue (20% DB-Kapazität), dann befürchte ich dass die AS400 eigentlich nur mit dem auseinanderfriemeln der SQL-Statements beschäftigt ist. (Aus dem Grund geht IBM beim POWER6 berechtigterweise Richtung Takt, nicht Richtung I/O).

Die Platten und das RAM sinds in dem Falle nicht, die Werte sind hier banal (@Dieter: das kriegen selbst zwei 35er Festscheiben noch geregelt). Da ich mich nicht JAVA-Äggsberde schimpfe: Wie siehts denn mit der Zusammenfassung der Insert-Statements aus? Für jeden Satz ein eigenes INSERT INTO ist ja auch nicht wirklich optimal.

Habe neulich ähnliches mit einer (zufällig in der Ecke rumstehenden) 150er gemacht. Mit (per Hand) vorbereiteten INSERTS mit mehreren Sätzen geht die erstaunlich gut ab, dafür, dass ein schwerer Ficus drauf steht...

-h
(jaja, eine 150er als Blumenvasenständer ist zumindest aus physikalischer Betrachtung gewagt)