Database Software

We are currently doing performance testing for timesten for 2000 users. The application is a java project and is deployed on weblogic server. We are facing very poor performance with timesten. The response time for the same code with Oracle db is 0.116 second , however with timesten it is coming about 9 seconds.

We have tried both the client-server connection as well as direct connection .
The sql query is just a select statement which gets the count of records from the database . Our requirement is read only and we are not writing anything in timesten . We are caching data from oracle db in timesten tables and running our query on it .

The details of the environment and the timesten database are as follows.

1.)Timesten is intalled on RHEL 5 64 bit machine . The output of ttversion of the same is

TimesTen Release 11.2.1.9.0 (64 bit Linux/x86_64) (TTEAG:23388) 2012-03-19T21:35:54Z
Instance admin: tteag
Instance home directory: /timestendb/TimesTen/TTEAG
Group owner: ttadmin
Daemon home directory: /timestendb/TimesTen/TTEAG/info
PL/SQL enabled.

2.)This machine has currently 10 cpu . The cpu details is

processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5675 @ 3.07GHz
stepping : 2
cpu MHz : 3066.886
cache size : 12288 KB
physical id : 1
siblings : 5
core id : 0
cpu cores : 5
apicid : 32
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx pdpe1gb rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips : 6133.77
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: [8]





3.)The memory details for the machine are :-

MemTotal: 148449320 kB
MemFree: 45912888 kB
Buffers: 941548 kB
Cached: 94945804 kB
SwapCached: 48 kB
Active: 93980700 kB
Inactive: 5289636 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 148449320 kB
LowFree: 45912888 kB
SwapTotal: 147455984 kB
SwapFree: 147455732 kB
Dirty: 616 kB
Writeback: 412 kB
AnonPages: 3383108 kB
Mapped: 298540 kB
Slab: 2848180 kB
PageTables: 19772 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 221549572 kB
Committed_AS: 102509964 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 340988 kB
VmallocChunk: 34359395635 kB
HugePages_Total: 128
HugePages_Free: 96
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

4.)We have permanent and temporary both databases.

4 a )The dsn entry for permanent timesten db is :-

[TTEAG]
Driver=/timestendb/TimesTen/TTEAG/lib/libtten.so
DataStore=/timesten03/TimesTen/database/TTEAG/TT_1121
LogDir=/timesten04/TimesTen/logs/TTEAG
PermSize=50000
TempSize=2000
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=EAG
Connections=2047
#MemoryLock=4
PLSQL=1



Output of monitor command for it
Command> monitor;

TIME_OF_1ST_CONNECT: Wed Sep 12 14:39:40 2012
DS_CONNECTS: 1574
DS_DISCONNECTS: 799
DS_CHECKPOINTS: 746
DS_CHECKPOINTS_FUZZY: 746
DS_COMPACTS: 0
PERM_ALLOCATED_SIZE: 51200000
PERM_IN_USE_SIZE: 70188
PERM_IN_USE_HIGH_WATER: 70188
TEMP_ALLOCATED_SIZE: 2048000
TEMP_IN_USE_SIZE: 26561
TEMP_IN_USE_HIGH_WATER: 869386
SYS18: 0
TPL_FETCHES: 0
TPL_EXECS: 0
CACHE_HITS: 0
PASSTHROUGH_COUNT: 0
XACT_BEGINS: 738998
XACT_COMMITS: 739114
XACT_D_COMMITS: 0
XACT_ROLLBACKS: 0
LOG_FORCES: 746
DEADLOCKS: 0
LOCK_TIMEOUTS: 0
LOCK_GRANTS_IMMED: 765835
LOCK_GRANTS_WAIT: 0
SYS19: 0
CMD_PREPARES: 13
CMD_REPREPARES: 0
CMD_TEMP_INDEXES: 0
LAST_LOG_FILE: 1
REPHOLD_LOG_FILE: -1
REPHOLD_LOG_OFF: -1
REP_XACT_COUNT: 0
REP_CONFLICT_COUNT: 0
REP_PEER_CONNECTIONS: 0
REP_PEER_RETRIES: 0
FIRST_LOG_FILE: 1
LOG_BYTES_TO_LOG_BUFFER: 305952
LOG_FS_READS: 0
LOG_FS_WRITES: 747
LOG_BUFFER_WAITS: 0
CHECKPOINT_BYTES_WRITTEN: 955832
CURSOR_OPENS: 727934
CURSOR_CLOSES: 728366
SYS3: 0
SYS4: 0
SYS5: 0
SYS6: 0
CHECKPOINT_BLOCKS_WRITTEN: 6739
CHECKPOINT_WRITES: 9711
REQUIRED_RECOVERY: 0
SYS11: 0
SYS12: 1
TYPE_MODE: 0
SYS13: 0
SYS14: 0
SYS15: 0
SYS16: 0
SYS17: 0
SYS9:

4 b.) The dsn of temporary db is :-

[TTEAGTMP]
Driver=/timestendb/TimesTen/TTEAG/lib/libtten.so
DataStore=/timesten03/TimesTen/database/TTEAGTMP/TT_1121
LogDir=/timesten04/TimesTen/logs/TTEAGTMP
Temporary=1
AutoCreate=1
PermSize=20000
TempSize=20000
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=EAG
Connections=2047
#MemoryLock=4
PLSQL=2
PLSQL_MEMORY_ADDRESS=20000000

The output of monitor command is :-

Command> monitor;

TIME_OF_1ST_CONNECT: Tue Sep 11 14:00:34 2012
DS_CONNECTS: 4609
DS_DISCONNECTS: 4249
DS_CHECKPOINTS: 894
DS_CHECKPOINTS_FUZZY: 893
DS_COMPACTS: 0
PERM_ALLOCATED_SIZE: 20480000
PERM_IN_USE_SIZE: 70198
PERM_IN_USE_HIGH_WATER: 70560
TEMP_ALLOCATED_SIZE: 20480000
TEMP_IN_USE_SIZE: 15856
TEMP_IN_USE_HIGH_WATER: 326869
SYS18: 0
TPL_FETCHES: 0
TPL_EXECS: 0
CACHE_HITS: 0
PASSTHROUGH_COUNT: 0
XACT_BEGINS: 1005281
XACT_COMMITS: 1005661
XACT_D_COMMITS: 0
XACT_ROLLBACKS: 6
LOG_FORCES: 8
DEADLOCKS: 0
LOCK_TIMEOUTS: 8
LOCK_GRANTS_IMMED: 2031645
LOCK_GRANTS_WAIT: 2
SYS19: 0
CMD_PREPARES: 149
CMD_REPREPARES: 0
CMD_TEMP_INDEXES: 0
LAST_LOG_FILE: 0
REPHOLD_LOG_FILE: -1
REPHOLD_LOG_OFF: -1
REP_XACT_COUNT: 0
REP_CONFLICT_COUNT: 0
REP_PEER_CONNECTIONS: 0
REP_PEER_RETRIES: 0
FIRST_LOG_FILE: 0
LOG_BYTES_TO_LOG_BUFFER: 12515480
LOG_FS_READS: 0
LOG_FS_WRITES: 36
LOG_BUFFER_WAITS: 0
CHECKPOINT_BYTES_WRITTEN: 0
CURSOR_OPENS: 928766
CURSOR_CLOSES: 929343
SYS3: 0
SYS4: 0
SYS5: 0
SYS6: 0
CHECKPOINT_BLOCKS_WRITTEN: 0
CHECKPOINT_WRITES: 0
REQUIRED_RECOVERY: 0
SYS11: 0
SYS12: 1
TYPE_MODE: 0
SYS13: 0
SYS14: 0
SYS15: 0
SYS16: 0
SYS17: 0
SYS9:

5.)The weblogic installed is version 10.3.4 . There are three managed servers in it and they are in a cluster . The timesten data source is created in the weblogic . The application uses the jndi name to connect to the data source using jdbc driver .

6 .) Cache groups

We have 7 cachegroups created for the 7 tables. Indexes have also been created on the tables which are same as the source oracle db from where data is cached .

The cachegroups details is :-

Cache Group CACHEADM.PROCESS_INSTANCE_B_T_UG:

Cache Group Type: User Managed
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 1 Minute
Autorefresh Status: ok
Aging: No aging defined

Root Table: EAGBE00.PROCESS_INSTANCE_B_T
Table Type: Propagate


7.)For client server connection. On the client machine weblogic 10.3.4 is installed with two managed servers .

The ttversion for timesten client is :-

TimesTen Release 11.2.1.9.0 (64 bit HPUX/IPF) (TTEAG) 2012-03-20T00:45:11Z
Instance home directory: /globalapp/app/TimesTen/TTEAG
Group owner: oinstall


8.) The oracle database is 10.2.0.4.0



Please let us know if you see anything wrong with this setup or if we are doing something wrong.