db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Armbrust <daniel.armbrust.l...@gmail.com>
Subject Derby performance issues
Date Wed, 22 Jul 2009 19:52:07 GMT
I have a fairly complex application which I currently support on
PostgreSQL and SQL Server.  Most of the SQL that is run is created by
Hibernate.

I'm trying out Derby as a backend for the application - and well, I'm
hoping I have something configured wrong - because the performance of
Derby is dismal in my use case.

Maybe someone can give me a pointer.

Typically, my application is driving 40 connections to the DB - all
simultaneously.  With Derby, I backed that down to 8 parallel
connections, which seemed to help a bit... but it is still far to slow
-

Derby is at least 30 times slower than PostgreSQL on the same
hardware, and completely pegs both CPU usage and disk IO.

I've bumped the page size to 8192, and pushed the pageCacheSize to 10000.

One problem I suspect I have is tuning how Derby handles fsync.  Are
there settings for this?  I don't need commits to be written to disk
at commit time - buffered is ok, so long as the database can recover
from an unexpected shutdown.

Another problem appears in this output I gathered - this query appears
to be running a table scan (which I wouldn't expect) and has some
pretty ridiculously large optimizer estimates.  Postgres does this
without a table scan, anyway.  I realise the query is kind of odd, it
is something that hibernate is burping out.

DDL of tables involved:

CREATE TABLE "PSLOGIN"."IPHOST" ("ETHERNETMACADDR" CHAR(17) NOT NULL,
"CPEMAC" CHAR(11) NOT NULL, "IPADDR" VARCHAR(15) NOT NULL, "REGTIME"
TIMESTAMP NOT NULL, "LEASETIME" TIMESTAMP, "LAST_UPDATED" TIMESTAMP
NOT NULL DEFAULT current_timestamp);

CREATE TABLE "PSLOGIN"."CPE" ("CPEMAC" CHAR(11) NOT NULL,
"SUBSCRIBERID" INTEGER NOT NULL, "SLAID" INTEGER NOT NULL, "VLANID"
INTEGER NOT NULL, "ENABLE" CHAR(1) NOT NULL DEFAULT 'F', "ROAMALLOWED"
CHAR(1) NOT NULL DEFAULT 'F', "NUMBEROFHOSTS" INTEGER NOT NULL DEFAULT
0, "CHANGETIME" TIMESTAMP, "REGLOCATIONID" INTEGER, "REGTIME"
TIMESTAMP, "REGBASEID" CHAR(11), "SWVERSION" VARCHAR(20), "STATICIP"
CHAR(1) NOT NULL DEFAULT 'F', "SERIALNUMBER" VARCHAR(20) NOT NULL,
"ENDTIME" TIMESTAMP, "VOIPID" INTEGER, "CONFIGURATION" INTEGER NOT
NULL DEFAULT 1, "COMMENT" VARCHAR(50), "MODELID" VARCHAR(20), "ISPID"
INTEGER NOT NULL DEFAULT -1, "LAST_UPDATED" TIMESTAMP NOT NULL DEFAULT
current_timestamp, "CPECONFIGID" INTEGER, "CPEFILTERID" INTEGER);


CREATE INDEX "PSLOGIN"."IX_CPE_ENABLE" ON "PSLOGIN"."CPE" ("ENABLE");
CREATE INDEX "PSLOGIN"."IX_CPE_ISPID" ON "PSLOGIN"."CPE" ("ISPID");
CREATE INDEX "PSLOGIN"."IX_IPHOST_CPEMAC" ON "PSLOGIN"."IPHOST" ("CPEMAC");
CREATE INDEX "PSLOGIN"."IX_IPHOST_IPADDR" ON "PSLOGIN"."IPHOST" ("IPADDR");

ALTER TABLE "PSLOGIN"."CPE" ADD CONSTRAINT "PK_CPE" PRIMARY KEY ("CPEMAC");
ALTER TABLE "PSLOGIN"."IPHOST" ADD CONSTRAINT "PK_IPHOST" PRIMARY KEY
("ETHERNETMACADDR", "IPADDR");
ALTER TABLE "PSLOGIN"."IPHOST" ADD CONSTRAINT "FK_IPHOST_CPE" FOREIGN
KEY ("CPEMAC") REFERENCES "PSLOGIN"."CPE" ("CPEMAC") ON DELETE CASCADE
ON UPDATE NO ACTION;

(Also - a couple more foreign keys from the CPE table out to other
tables not listed here)

QueryPlan:

2009-07-22 17:31:20.924 GMT Thread[APMessageManager-LP-1,5,main] (XID
= 109026), (SESSIONID = 11), select dynamichos0_.ethernetmacaddr as
ethernet1_0_, dyn
amichos0_.ipaddr as ipaddr0_, dynamichos0_.cpemac as cpemac0_,
dynamichos0_.last_updated as last3_0_, dynamichos0_.leasetime as
leasetime0_, dynamichos0_.r
egtime as regtime0_ from iphost dynamichos0_, cpe cpe1_ where
dynamichos0_.cpemac=cpe1_.cpemac and (dynamichos0_.ethernetmacaddr=?
and dynamichos0_.ipaddr<
>? or dynamichos0_.ipaddr=? and dynamichos0_.ethernetmacaddr<>? and cpe1_.vlanid=(select
cpe2_.vlanid from cpe cpe2_ where cpe2_.cpemac=?)) ******* Materia
lized subqueries:
        Begin Subquery Number 0
        Once ResultSet:
        Number of opens = 1
        Rows seen = 1
        Source result set:
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count:         2504.00
                optimizer estimated cost:        14360.44

                Project-Restrict ResultSet (7):
                Number of opens = 1
                Rows seen = 1
                Rows filtered = 0
                restriction = false
                projection = true
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count:         2504.00
                        optimizer estimated cost:        14360.44

                Source result set:
                        Index Row to Base Row ResultSet for CPE:
                        Number of opens = 1
                        Rows seen = 1
                        Columns accessed from heap = {3}
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                optimizer estimated row count:         2504.00
                                optimizer estimated cost:        14360.44

                                Index Scan ResultSet for CPE using
constraint PK_CPE at read committed isolation level using share row
locking chosen by th
e optimizer
                                Number of opens = 1
                                Rows seen = 1
                                Rows filtered = 0
                                Fetch Size = 1
                                        constructor time (milliseconds) = 0
                                        open time (milliseconds) = 0
                                        next time (milliseconds) = 0
                                        close time (milliseconds) = 0
                                        next time in milliseconds/row = 0

                                scan information:
                                        Bit set of columns fetched=All
                                        Number of columns fetched=2
                                        Number of deleted rows visited=0
                                        Number of pages visited=2
                                        Number of rows qualified=1
                                        Number of rows visited=1
                                        Scan type=btree
                                        Tree height=2
                                        start position:
        >= on first 1 column(s).
        Ordered null semantics on the following columns:

                                        stop position:
        > on first 1 column(s).
        Ordered null semantics on the following columns:

                                        qualifiers:
None
                                        optimizer estimated row count:
        2504.00
                                        optimizer estimated cost:
  14360.44


        End Subquery Number 0
Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
       restriction time (milliseconds) = 0
        projection time (milliseconds) = 0
        optimizer estimated row count:         2504.00
        optimizer estimated cost:         5796.72

Source result set:
        Hash Exists Join ResultSet:
        Number of opens = 1
        Rows seen from the left = 2500
        Rows seen from the right = 1
        Rows filtered = 0
        Rows returned = 1
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count:         2504.00
                optimizer estimated cost:         5796.72

        Left result set:
                Table Scan ResultSet for IPHOST at read committed
isolation level using instantaneous share row locking chosen by the
optimizer
                Number of opens = 1
                Rows seen = 2500
                Rows filtered = 0
                Fetch Size = 16
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        next time in milliseconds/row = 0

                scan information:
                        Bit set of columns fetched=All
                        Number of columns fetched=6
                        Number of pages visited=130
                        Number of rows qualified=2500
                        Number of rows visited=3271
                        Scan type=heap
                        start position:
null                    stop position:
null                    qualifiers:
None
                        optimizer estimated row count:         2504.00
                        optimizer estimated cost:         2483.44

        Right result set:
                Project-Restrict ResultSet (4):
                Number of opens = 2500
                Rows seen = 2500
                Rows filtered = 2499
                restriction = true
                projection = false
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count:         2504.00
                        optimizer estimated cost:         3313.28

                Source result set:
                        Hash Scan ResultSet for CPE at read committed
isolation level using instantaneous share row locking:
                        Number of opens = 2500
                        Hash table size = 2500
                        Hash key is column number 0
                        Rows seen = 2500
                        Rows filtered = 0
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 0

                        scan information:
                                Bit set of columns fetched={0, 3}
                                Number of columns fetched=2
                                Number of pages visited=167
                                Number of rows qualified=2500
                                Number of rows visited=2500
                                Scan type=heap
                                start position:
null                            stop position:
null                            scan qualifiers:
None
                                next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                optimizer estimated row count:         2504.00
                                optimizer estimated cost:         3313.28


Thanks,

Dan

Mime
View raw message