TimesTen

TimesTen: Parallel Thread Reads from Checkpoint Files – More is not always better

TT_imageI wanted to get around to to some performance improvements with Oracle TimesTen Database. As you know, the Oracle TimesTen database creates a checkpoint file for persistence of the In-Memory database. In the event you need to load the database into memory, depending upon the size of the checkpoint, this can take some time. In release 11.2.2.8.0, database restart time can be significantly improved by enabling parallel threads to read the TimesTen checkpoint files.

CheckPoint Read Parallelism (CkptReadThreads)

The setting only affects TimesTen startup times by reducing the amount if time required to read the database from disk. While this does not impact overall database performance, your load times and by default database restart time can be greatly reduced.

Here is this parameter in a partial sys.odbc.ini file:

TempSize=20000
Preallocate=1
Connections=500
MemoryLock=4
CkptFrequency=600
CkptLogVolume=10000
CkptRate=0
CkptReadThreads=6
PrivateCommands=1
RecoveryThreads=40
DatabaseCharacterSet=AL32UTF8
PLSQL_MEMORY_SIZE=64

The default value is 1 and was typically used when systems have spinning disk. With Flash and SSD this value can be increased anywhere from 2 to 8. As you can see, I have set the value at 6. Let’s take a look some load speeds with various values:

Using 4 threads and method 0 to read the checkpoint file
Checkpoint file read started; data segment = 200902.1 mb
200902.1 mb scanned in 161 sec (1247.8 mb/sec)

Using 6 threads and method 0 to read the checkpoint file
Checkpoint file read started; data segment = 200902.1 mb
200902.1 mb scanned in 81 sec (2480.3 mb/sec)

Using 8 threads and method 0 to read the checkpoint file
Checkpoint file read started; data segment = 200902.1 mb
200902.1 mb scanned in 126 sec (1594.5 mb/sec)

As you can see in this example, the best performance was realized using 6 threads. While this might not always be the case in every environment, before just picking the largest number, do some testing to make sure that you are getting the optimal performance.

As you explore way to improve your overall Oracle TimesTen experience consider using the CkptReadThread parameter and while you are at it, go ahead and put your checkpoint files on SSD/Flash disk.

Enjoy!dbaOnTap

Come see me present on some other cool topics at Kscope16 in Chicago.


Also published on Medium.