Performance og forbedringsforslag til DB klasser ifm multiuser

Istedet for at lave dine preparedStatements hver gang, kunne du vælge at genbruge dem.
Dvs. oprette dem i open() og så bare genbruge dem i alle de andre kald.

En connection pool er en meget god ting !

Det er en ret dyr operation at lave en fysisk connection til en database.

Men du åbner og lukker jo ikke connection hele tiden. Du gemmer en connection
i dit Database object.

Der giver (formentligt - afhænger lidt af hvordan du bruger dit Database objekt,
men lad os antage at du f.eks. gemmer det i session) fremragende performance. Men
det skalerer dårligt. Med rigtigt mange brugere så løber du tør for database connections og får fejl.

Hvis du skal bruge connection pool så skal du enten
  begynde at åbne og close en logiske database connection fra en connection pool i hver metode
eller
  putte dine Database objekter i en pool

Hvis load er lav kan man godt nogen gange funktions opdele sin
database adgang og give hver funktion en connection og så lade alle
brugere der skal bruge en funktion bruge samme connection.

Det kan laves ved hjælp af static fields/metoder eller ved hjælp af
en singleton.

Og ja - det er meget godt at genbruge prepared statements.

Er du iøvrigt ikke ved at være på et niveau hvor:

      e.printStackTrace();
      System.out.println("bla bla"+e);

bør erstattes af nogle kald til et logging framework ?

:-)

Hej igen, tak for kommentarerne :)

Jo, det er vist på tide jeg få arbejdet lidt med logging framework! Jeg har nu opdateret min klasse så jeg benytter log4j(har kigget på din artikkel), men når jeg nu ikke selv hoster, hvordan gør jeg så lige når "jeg" skal starte programmet? det er lidt svært at starte det med: -Dlog4j.configuration=file:///C:/log4j.properties eller -Dlog4j.configuration=file:///C:/log4j.xml

jeg mener at log4j leder efter de 2 filer i classpath d.v.s. at hvis du ligger en af dem
i WEB-INF/classes så burde det virke

jep den søger selv efter en fil der hedder log4j.properties så medmindre du omdøber den eller ligger den uden for classpath så behøver du ikke det parameter.

Så spiller logging frameworket, og diverse resulsets og prepared statements bliver nu genbrugt :)
Nu må det være tid til at kigge lidt på connection pool ud fra dette:
http://jakarta.apache.org/commons/pool/
http://jakarta.apache.org/commons/dbcp/

Så læg bare et svar :)
Tak for hjælpen

ok

hvis du vil putte din klasse i pool skal du kigge på dem

hvis du vil bruge en standard connection pool er den indbygget i Tomcat

Jeg bliver nød til at putte mine klasser i en pool, da jeg ikke selv hoster, og da min udbyder ikke har sat en op man kan bruge :(

>> trp79

Umiddelbart lyder det somom du fokuserer på det forkerte sted at optimere. I forhold til noget nær enhver relationsdatabase er Java lynenede hurtig, dvs: 5% af tiden går med at eksekvere Javakode og 95% med at vente på svar fra databasen.

Så der der virkelig kan hentes noget optimering hjem er i at gøre databasen hurtigere, At sørge for korrekt normaliserede tabeller og gode indexer. Hvis grunden til dit spørgsmål er at det går for langsomt vil jeg 99% gætte på det er der den er gal.

mvh JakobA

Performance forbedringer ved brug af connection pool er altså noget man
helt konkret oplever ude i den virkelige verden og som kan betyde rigtigt
meget.

Selv det at fjerne System.out er set have signifikant betydning for performance
på high volume sites.

Men du selvfølgelig stadig ret i at hvis man lave queries med join er det
også vigtigt at man har index på plads.

arne og jakoba oplever i som programmører tit at få lov til at røre ved databasen? Det indtryk jeg har af branchen er at en DBA ofte står for det, ihvertfald større steder og at man får adgang til f.eks stored procedures eller skal have sine querys godkendt af dem..osv. og så er der jo ikke meget man selv kan optimere på den side. Eller læser jeg bare udtalelser for for mange medarbejdere i fortune 500 virksomheder og er virkligheden noget helt andet?

trp79 du kunne også tage og kigge på caching når du er kommet lidt videre med systemet.

det hænder at jeg får lov til at lave database

(jeg kan faktisk også lidt database, jeg har undervist 4 år i databaser på
Handelshøjskole Syd, 1997-1998 var jeg faktisk DBA på en Oracle RDB
database - Oracle RDB = den gamle DEC RDB ikke Oracle 7/8/9/10, og jeg
er faktisk #3 i Databaser her på E hvilket man kunne se hvis Top-10
virkede)

der er flere faktorer som har betydning for om udviklere for lov at lave database

1)  virksomhedens størrelse (som du selv nævner)

en lille virksomhed har typisk N "udviklere" som skal kunne lidt af hvert

en stor virksomed har typisk arkitekter, backend udviklere, frontend
udviklere, web designere, DBA'er, system administratorer etc.

2)  applikations type

OLTP systemer er primært INSERT og UPDATE og så er der ikke så store
optimerings muligheder

informations systemer er primært queries og her er der rigtigt store
optimerings muligheder

3)  arbejds type

et produkt vil man typisk gerne gøre database uafhængig og så er der igen
store begrænsninger på optimerings mulighederne

et project (one off) kan udnytte det valgte database fuldtud inklusive en
masse databas especifik optimering

[og grunden til at jeg sommetider for lov at lave datbase behøver ikke
nødvendigvis at være fordi jeg er god, men kunne også være fordi at jeg arbejder
med et OLTP produkt]

4)  teknologi

Visse teknologier ligger mere op til database optimeringer end andre. Med
mange moderne teknologier (EJB CMP, Hibernate, JDO etc.) skriver man ikke
SQL'en, men dne bliver genereret for den. Og det mindsker selvfølgeligt
optimerings mulighederne.

5)  niveau

Det er ret almindligt at programmører er med i det logiske database design
tabeller, felter, indexes, views, SP's etc. (nogle steder laver de det selv, andre steder
arbejder de sammen med en database mand om at lave det).

Men det fysiske database design devices/tablespaces/log-filer, placeringen
af tabeller og indexes på samme og den slags får databasen normalt lov til
at lave selv (hvis altså en sådan eksisterer)

ok tak arne

Her er resultatet så, hvor der er en connectionpool. Bemærk at udover:
http://jakarta.apache.org/commons/pool/
http://jakarta.apache.org/commons/dbcp/
så skal denne også bruges:
http://jakarta.apache.org/commons/collections/

Klassen bør nok deles op i to klasser, således at forespørgsler trækker på en connection/pool klasse istedet.

Bør man iøvrigt klargør alle de preparedstatement? indtil videre optager det jo 6 connections i poolen, og applikationen er jo langt fra færdig endnu.

Men som i skriver så bør et næste skridt i forbindelse med performance nok omhandle optimering af databasen.

Nu er spørgsmålet godt nok lukket, men hvis i har lyst må i meget gerne komme med forbedrings forslag :)
***********Database.java************
import java.sql.*;
import javax.servlet.*;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

public class Database
{
    private static boolean poolReady = false;
    private final static Logger dbLogger = Logger.getLogger("DatabaseLogger");
    private ServletContext application = null;
    private Connection conn=null;    //our connnection to the db - presist for life of program
    private PreparedStatement pstmLoginOk;
    private PreparedStatement pstmFindesBruger;
    private PreparedStatement pstmOpretBruger;
    private PreparedStatement pstmRegLogin;
    private PreparedStatement pstmRegLogin2;
    private PreparedStatement pstmRegLogin3;
    private ResultSet rsFindesBruger;
    private ResultSet rsLoginOk;
    private ResultSet rsRegLogin;
   
  public Database(ServletContext application) throws Exception    // note more general exception
  {
    this.application = application;
  }

  public boolean open()
  {
    if(poolReady==true) return true;
    else
    {
      try
        {
          String drv=null, url=null, bru=null, adg=null;
          if(application!=null){
            drv = application.getInitParameter("dbDriver");
            dbLogger.info("Database.open() - dbDriver indlæst fra web.xlm");
            url = application.getInitParameter("dbUrl");
            dbLogger.info("Database.open() - dbUrl indlæst fra web.xlm");
            bru = application.getInitParameter("dbBruger");
            dbLogger.info("Database.open() - dbBruger indlæst fra web.xlm");
            adg = application.getInitParameter("dbAdgangskode");
            dbLogger.info("Database.open() - dbAdgangskode indlæst fra web.xlm");
          }
          if(drv==null)
          {
            drv = "com.mysql.jdbc.Driver";
            dbLogger.info("Database.open() - dbDriver indlæst fra metoden");
          }
          if(url==null)
          {
            url = "jdbc:mysql://localhost/Banankasse";
            dbLogger.info("Database.open() - dbUrl indlæst fra metoden");
          }
          if(bru==null)
          {
            bru = "root";
            dbLogger.info("Database.open() - dbBruger indlæst fra metoden");
          }
          if(adg==null)
          {
            adg = "";
            dbLogger.info("Database.open() - dbAdgangskode indlæst fra metoden");
          } 
         
          dbLogger.info("Database.open() - Loading underliggende JDBC driver");
          Class.forName(drv);
          dbLogger.info("Database.open() - Loading underliggende JDBC driver - Done");
 
          dbLogger.info("Database.open() - Driver sættes op");
          poolReady = setupDriver(url+"?"+"user="+bru+"&password="+adg);
          dbLogger.info("Database.open() - Driver sat op: "+poolReady);
           
           
          dbLogger.info("Database.open() - Creating connection.");
         
          pstmLoginOk = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("SELECT brugernavn FROM BKBruger WHERE brugernavn=? AND password=?");
          pstmFindesBruger = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("SELECT email FROM BKBruger WHERE brugernavn=?");
          pstmOpretBruger = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("INSERT INTO BKBruger(brugernavn,password,fornavn,efternavn,email,foedselsdato,mobil,fastnet,mailBanan,mailForum,regDato,sidsteLogin,billede, antalLogin) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
          pstmRegLogin = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("UPDATE BKBruger SET sidsteLogin= ? WHERE brugernavn = ?");
          pstmRegLogin2 = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("SELECT antalLogin FROM BKBruger WHERE brugernavn = ?");
          pstmRegLogin3 = DriverManager.getConnection("jdbc:apache:commons:dbcp:banankasse").prepareStatement("UPDATE BKBruger SET antalLogin = ? WHERE brugernavn = ?");
          dbLogger.info("Database.open() - prepared statements indlæst");
          printDriverStats();
        }
        catch(ClassNotFoundException e){
          dbLogger.error("Database.open() - Problemer med databasen - ClassNotFoundException: "+e);
          poolReady=false; //der er ikke forbindelse til poolen, prøv at oprette den senere
          return false;
        }
        catch(SQLException e){
          dbLogger.error("Database.open() - Problemer med databasen - SQLException: "+e);
          poolReady=false; //der er ikke forbindelse til poolen, prøv at oprette den senere
          return false;
        }
        catch(Exception e){
          dbLogger.error("Database.open() - Problemer med databasen - Exception: "+e);
          poolReady=false; //der er ikke forbindelse til poolen, prøv at oprette den senere
          return false;
        }
        return true;
    }
  }

  /**
  * Luk forbindelse til db
  * @throws java.sql.SQLException
  */
  public void shutdown() throws SQLException
  {
      if(poolReady!=false)
      {
        try
        {
            pstmLoginOk.close();
            pstmFindesBruger.close();
            pstmOpretBruger.close();
            pstmRegLogin.close();
            pstmRegLogin2.close();
            pstmRegLogin3.close();
            shutdownDriver();
            dbLogger.info("Database.shutdown() - forbindelse til database lukket");
        }
        catch (Exception e)
        {
            dbLogger.error("Database.shutdown() - Problemer med at lukke forbindelse til database: "+e);
        }       
      }
  }

  /**
  * Ud fra det indtastede brugernavn og password logges brugeren ind, hvis der
  * findes en bruger med det pågældende brugernavn og password
  * @return
  * @param password
  * @param brugernavn
  */
  public synchronized boolean loginOk(String brugernavn, String password)
  {
    boolean loggetInd = false;
    try{
        if (open())
        {
          pstmLoginOk.setString(1, brugernavn);
          pstmLoginOk.setString(2, password);
          rsLoginOk = pstmLoginOk.executeQuery();
          if (rsLoginOk.next())
          {
            loggetInd = true;  //korrekt! brugeren er logget ind.
            dbLogger.info("Database.loginOk() - Bruger logget ind: "+brugernavn);
           
          }
          else
          {
            loggetInd = false;
            dbLogger.info("Database.loginOk() - Forkert brugernavn eller password - brugernavn: "+brugernavn);
          }
        }
      }
      catch(SQLException e){
        dbLogger.error("Database.loginOk() - Problemer med at tjekke login: "+e);
      }
    finally
    {
      try
      {
      rsLoginOk.close();
      pstmLoginOk.clearParameters();;
      }
      catch(SQLException e)
      {
        dbLogger.error("Database.loginOk() - Problemer i finally med rsLoginOk.close() og/eller pstmLoginOk.clearParameters(): "+e);
      }
    }
    return loggetInd;
  }
 
  /**
  * Ud fra det indtastede brugernavn undersøges det om et tilsvarende findes
  * i forvejen.
  * @return
  * @param brugernavn
  */
  public synchronized boolean findesBruger(String brugernavn)
  {
    boolean findesBruger = true;
    try
    {
      if(open())
      {
        pstmFindesBruger.setString(1, brugernavn);
        rsFindesBruger = pstmFindesBruger.executeQuery();
        if(rsFindesBruger.next())
          findesBruger = true;
        else
          findesBruger = false;
      }
    }
    catch(SQLException e)
    {
      dbLogger.error("Database.findesBruger() - SQLException: "+e);
    }
    finally
    {
      try
      {
        rsFindesBruger.close();
        pstmFindesBruger.clearParameters();
      }
      catch(SQLException e)
      {
        dbLogger.error("Database.findesBruger() - Problemer i finally med rsFindesBruger.close() og/eller pstmFindesBruger.clearParameters(): "+e);
      }     
    }
    return findesBruger;
  }
 
  /**
  * Ud fra bønnen Login oprettes brugeren i BKBruger.
  * @return
  * @param nytPassword
  * @param login
  */
  public synchronized boolean opretBruger(Login login, String nytPassword)
  {
    boolean opretBrugerOk=false;
    if (open())
    {
      try
      {       
        pstmOpretBruger.setString(1, login.getBrugernavn());
        pstmOpretBruger.setString(2, nytPassword);
        pstmOpretBruger.setString(3, login.getFornavn());
        pstmOpretBruger.setString(4, login.getEfternavn());
        pstmOpretBruger.setString(5, login.getEmail());
        pstmOpretBruger.setDate(6, login.konverterTilDate(login.getFoedselsdato()));
        pstmOpretBruger.setString(7, login.getMobil());
        pstmOpretBruger.setString(8, login.getFastnet());
        pstmOpretBruger.setInt(9, (login.getMailBanan() ? 1 : 0));
        pstmOpretBruger.setInt(10, (login.getMailForum() ? 1 : 0));
        pstmOpretBruger.setTimestamp(11, new Timestamp(new java.util.Date().getTime()));
        pstmOpretBruger.setDate(12, null);
        pstmOpretBruger.setString(13, "billedesti");
        pstmOpretBruger.setInt(14, 0);
        pstmOpretBruger.executeUpdate();
        pstmOpretBruger.clearParameters();
        dbLogger.info("Database.opretBruger() - Bruger oprettet korrekt - brugernavn: "+login.getBrugernavn());
        opretBrugerOk=true;    //oprettelse lykkedes!
      }
      catch(SQLException e){
          dbLogger.error("Database.opretBruger() - Problemer med opretBruger - SQLException: "+e);
          opretBrugerOk=false;
      }
      catch(Exception e){
          dbLogger.error("Database.opretBruger() - Problemer med opretBruger - SQLException: "+e);
          opretBrugerOk=false;
      }
      finally
      {
        try
        {
          pstmOpretBruger.clearParameters();
        }
        catch(SQLException e)
        {
          dbLogger.error("Database.opretBruger() - Problemer i finally med pstmOpretBruger.clearParameters(): "+e);
        }     
       
      }     
    }
    else
    {
      opretBrugerOk=false;
    }
    return opretBrugerOk;
  } 

  /**
  * I BKBruger opdateres felterne sidsteLogin og antalLogin
  * sidsteLogin sættes ud fra tidspunktet på det aktuelle login
  * antalLogin tælles een op.
  * @param brugernavn
  */
  public synchronized void regLogin(String brugernavn)
  {
    if(open())
    {
      try
        {
          pstmRegLogin.setTimestamp(1, new Timestamp(new java.util.Date().getTime()));
          pstmRegLogin.setString(2,brugernavn);
          pstmRegLogin.executeUpdate();
          pstmRegLogin.clearParameters();
          dbLogger.info("Database.regLogin() - sidsteLogin opdateret");
         
          pstmRegLogin2.setString(1, brugernavn);
          rsRegLogin = pstmRegLogin2.executeQuery();
          if(rsRegLogin.next())
          {
            int antalLogin = rsRegLogin.getInt("antalLogin");
            antalLogin++;
           
            pstmRegLogin3.setInt(1, antalLogin);
            pstmRegLogin3.setString(2, brugernavn);
            pstmRegLogin3.executeUpdate();
            pstmRegLogin3.clearParameters();
            dbLogger.info("Database.regLogin() - antalLogin opdateret");
          }
        }
      catch(SQLException e)
      {
        dbLogger.error("Database.regLogin() - Problemer med regLogin - SQLException: "+e);
      }
      finally
      {
        try
        {
          rsRegLogin.close();
          pstmRegLogin3.clearParameters();
          pstmRegLogin2.clearParameters();
          pstmRegLogin.clearParameters();
        }
        catch(SQLException e)
        {
          dbLogger.error("Database.regLogin() - Problemer i finally med rsRegLogin.close(), pstmRegLogin3.clearParameters(), pstmRegLogin2.clearParameters() og/eller pstmRegLogin3.clearParameters(): "+e);
        }     
       
      }     

    }
  }
 
  public static boolean setupDriver(String connectURI) throws Exception {
        //
        //Objektpool, der tjener som den aktuelle pool af connections
        //
        // GenericObjectPool instans bruges, selvom enhver ObjectPool
        // implementation ville have være tilstrækkelig
        //
        ObjectPool connectionPool = new GenericObjectPool(null);
        //
        // ConnectionFactory som poolen vil bruge til at lave connections.
        // 
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);
        //
        // PoolableConnectionFactory, indpakker(wraps) de "rigtige" connections,
        // der blev lavet af ConnectionFactory sammen med klasser, der
        // implementerer pooling funktionalitet
        //
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
        //
        // PoolingDriveren oprettes...
        //
        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        //
        // ...og registreres sammen med poolen.
        //
        driver.registerPool("banankasse",connectionPool);
        //
        // nu kan denne streng bruges til at tilgå poolen med connections "jdbc:apache:commons:dbcp:example"
        //
        if(connectionPool!=null && connectionFactory!=null && poolableConnectionFactory!=null && driver!= null)
          return true;
        else
          return false;
  }

  /**
  * Udskriver antal active og idle connection i poolen
  * @throws java.lang.Exception
  */
  public static void printDriverStats() throws Exception {
      PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
      ObjectPool connectionPool = driver.getConnectionPool("banankasse");
     
      System.out.println("NumActive: " + connectionPool.getNumActive());
      System.out.println("NumIdle: " + connectionPool.getNumIdle());
  }
 
  /**
  * Lukker pooldriveren
  * @throws java.lang.Exception
  */
  public static void shutdownDriver() throws Exception {
      PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
      driver.closePool("banankasse");
  } 
}

Når ikke du åbner og lukker database connections hele tiden så har du ikke
rigtigt nytte af connection poolen.

Så derfor vil det være en god ide at oprette forbindelsen nede i metoderne i stedet for?
Hvis jeg ikke gør det vil jeg jo også få et hav af connections tilknyttet de prepared statements, der nu forberedes i open() efterhånden som jeg kommer videre med applikation og altså få brug for langt flere forespørgsler.