java - Testing Throughput of postgres database using thread pool and connection pool. But why do I only have 300 inserts per second when it should be 6000? -

i want test throughput of system has connection postgresql database. system consists of 2 main components: threadpoolexecutor newfixedthreadpool maximum of 10 threads , pgpoolingdatasource called connectionpool has maximum of 10 connections database. call stored procedures in postgres database, stored procedure simple insert , returns error message if insert failed. executing single call of stored procedure takes 20-30 ms.

the system works this: main thread creates message tasks , passes them thread pool. message task following: gets connection connection pool , calls stored procedure on postgres server. waits response , task finished. thread in thread pool can work on new message task.

now, think should work fine , extent. slow , have absolutely no idea why. using following code record 300-500 inserts second when should 6000 inserts per second. have no idea why. when using systems monitor, see cpus @ 20% load. when uncomment section indicated (1), 1 cpu @ 100% load while others around 0%, mystery me.

if can share light on i'm doing wrong, great. postgres server not configured correctly? when use top command, shows java uses 20% cpu , there 8 postgres processes each use 3%. (i'm on ubuntu 14.04 using eclipse).

here maintester code, containing main function. creates thread pool , database connection pool.

public class maintester { public static threadpoolexecutor threadpoolexecutor;     public static pgpoolingdatasource connectionpool;  public static void main(string[] args) {      establishconnectionpool(10);     threadpoolexecutor = (threadpoolexecutor)      executors.newfixedthreadpool(10);      operator operator = new operator(1, 2, 30);         operator.run(); // created other thread here before. //now use main thread run operator }   private static void establishconnectionpool(int nrofconnections)     {         connectionpool = new pgpoolingdatasource();         connectionpool.setdatasourcename("connectionpool");         connectionpool.setservername(dbservername);         connectionpool.setdatabasename(dbname);         connectionpool.setuser(dbuser);         connectionpool.setpassword(dbpassword);         connectionpool.setmaxconnections(nrofconnections);     } 

this operator code. spawns message tasks , hands them on thread pool. want let run 2 minutes , check amount of messages has inserted. want keep queue of thread pool full @ times, why check if queue of thread pool has less 1000 tasks. if has less, spawn new tasks thread pool chew on.

public class operator implements runnable{  private int minutestorun = 2;  private void run () {      long starttime = system.currenttimemillis();      while (system.currenttimemillis() - starttime < minutestorun * 60 * 1000 + 10) {              while(maintester.threadpoolexecutor.getqueue().size() < 1000) {                 messagetask messagetask = new messagetask(queueoperation.send, 1, 1, 1, "abc");                 maintester.threadpoolexecutor.execute(messagetask);             }              try { // (1)                 thread.sleep(100);             } catch (interruptedexception e) {                 e.printstacktrace();             }     } } 

}

(1) when don't sleep here, system monitor shows 1 cpu @ 100%, others @ 0%. makes no sense me. sure, method occupy 1 cpu threads thread pool should work on other cpus no?

here code message task:

public class messagetask implements runnable {  private queueoperation operation; private int senderid; private int receiverid; private int queueid; private string message;    public messagetask (queueoperation op, int senderid, int receiverid, int queueid, string message) {     operation = op;     this.senderid = senderid;     this.receiverid = receiverid;     this.queueid = queueid;     this.message = message; }  @override public void run() {      connection connection = null;     try {         connection = maintester.connectionpool.getconnection();     } catch (sqlexception e) {         e.printstacktrace();     }      try{          statement statement = connection.createstatement();          string dbstoredprocedure = "select send(" + senderid + "," + receiverid + "," + queueid + "," + "'"+message+"'"+ ");";;          resultset resultset = statement.executequery(dbstoredprocedure);         resultset.next();         string dbresponse = resultset.getstring(1);     }      catch (sqlexception e) {     }      {         try {             connection.close();         } catch (sqlexception e) {             e.printstacktrace();         }     }  } 

so questions is: why slow? why 8 of cpus @ 20% capacity? perhaps i've configured postgresql server wrong? i've not changed in default configuration. have misunderstood how thread pool works? or connection pool not work intended?

when measure stored proc execution time, you're not factoring in how long commit takes. appear focusing on cpu , ignoring disk i/o , cost of disk flushes.

300 transactions per second pretty reasonable number typical system basic ssd. i'd you're committing after each insert.

to faster results you'll need to:

• batch work transactions multiple inserts;
• enable commit_delay , set synchronous_commit = off (has data loss risk); or
• get faster disk

for more information see how speed insertion performance in postgresql

if don't mind losing entire dataset after crash use unlogged tables. but, really, after unclean shutdown data erased. gone. unrecoverable. sure mean it.