Re: performance question: protocol v2 vs v3 - Mailing list pgsql-jdbc

Hello,

I have conducted tests that seem to indicate that using protocol
v2 gives slightly better insert performance as using protocol v3.

- Database product name: PostgreSQL
- Database product version: 9.3.4
- Database driver name: PostgreSQL Native Driver
- Database driver version: PostgreSQL 9.3 JDBC4 (build 1101)
- JDBC major version: 4
- JDBC minor version: 0
- java.runtime.version: 1.8.0_05-b13
- os.name: Linux
- os.arch: amd64
- os.version: 2.6.38.7-desktop-1mnb2
- tomcat 8.0.8

Most PG config default except:

    shared_buffers = 512MB
    vacuum_cost_delay = 50
    vacuum_cost_page_hit = 1
    vacuum_cost_page_miss = 10
    vacuum_cost_page_dirty = 20
    vacuum_cost_limit = 1000
    track_counts = on
    autovacuum = on
    autovacuum_vacuum_cost_delay = 100
    synchronous_commit = off
    random_page_cost = 2
    effective_cache_size = 1536MB

Hardware is a random desktop computer of 2011.

Test is a mono threaded insertion of 10,000 entries into each of
two tables (insert #1 into table a, insert #1 into table b,
insert #2 into table a, etc):

                                        Table "public.a"
       Column       |           Type           |                          Modifiers
--------------------+--------------------------+-------------------------------------------------------------
 uid                | integer                  | not null default nextval('a_uid_seq'::regclass)
 cola               | character varying(15)    |
 colb               | character varying(25)    |
 colc               | text                     |
 cold               | character varying(25)    |
 cole               | text                     |
 colf               | character varying(25)    |
 colg               | character varying(128)   |
 colh               | integer                  |
 coli               | timestamp with time zone | not null
 colj               | timestamp with time zone |
Indexes:
    "a_pkey1" PRIMARY KEY, btree (uid)
    "idx_a_coli" btree (coli)
    "idx_a_coli_colc" btree (coli, colc)
    "idx_a_coli_cold_colg" btree (coli, cold, colg)
Referenced by:
    TABLE "b" CONSTRAINT "fk_a_uid" FOREIGN KEY (a_uid) REFERENCES a(uid) ON DELETE CASCADE

             Table "public.b"
         Column         |         Type         | Modifiers
------------------------+----------------------+-----------
 a_uid                  | integer              | not null
 cola                   | text                 |
 colb                   | text                 |
 colc                   | integer              |
 cold                   | text                 | not null
 cole                   | character varying(3) |
 colf                   | integer              |
 colg                   | integer              |
 colh                   | integer              |
 coli                   | integer              |
 colj                   | text                 |
Indexes:
    "b_pkey2" PRIMARY KEY, btree (a_uid)
Foreign-key constraints:
    "fk_a_uid" FOREIGN KEY (a_uid) REFERENCES a(uid) ON DELETE CASCADE

Using a prepared statement on an autocommit=true connection:

    ps = conn.prepareStatement( "INSERT INTO a( ... ) VALUES ( ?, ... )", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
    ps.setString( 1, ... )
    ...
    synchronized( ps.getConnection() ) {
        ps.executeUpdate()
    }

Using jdbc:postgresql:dbname?charSet=UTF8&protocolVersion=2,
clock time is:

#1: 00:58.519
#2: 00:56.678
#3: 00:56.222

Using jdbc:postgresql:dbname?charSet=UTF8&protocolVersion=3,
clock time is:

#1: 01:01.404
#2: 00:59.331
#3: 01:00.091

I know this is not massive. However, I was about to switch from
protocol v2 to protocol v3 wholly, but now, I'm wondering if
anyone can give any insight on this. Also, is there any known
downsides in sticking to protocol v2 - since it's very old now.

Thanks

--
Guillaume Cottenceau


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc