7 Replies Latest reply on Sep 11, 2012 9:59 AM by ChrisJenkins-Oracle

    Questions after first TimesTen test: Query peformance and memory footprint


      I am testing TimesTen In-Memory Database cache to see if it could help with some ad-hoc reporting queries that need too long to execute in our Oracle database.

      Here's the setup:

      1.) TimesTen on a 2 CPU Quadcore server with 32GB of RAM running Windows 2003 x64.

      2.) Set up two read-only cachegroups: A small one for a quick test and the real thing that maps to a large database table as such:

      Database table looks like:
         "EVENT_ID" NUMBER, 
         "ID_ALARM" NUMBER, 
      Oracle database table has 1.367.336.329 rows and the table segments are around 61 GB, so an average row takes about 46 bytes.

      Since I only have 32GB in the TimesTen machine, I created the cache group with a where predicate on the ID column so that only the 98.191.284 newest rows get into the cache group. In the Oracle database this is around 4,2 GB of data.

      After loading the cache group dssize returns:
      Command> dssize
        PERM_ALLOCATED_SIZE:      26624000
        PERM_IN_USE_SIZE:         19772852
        PERM_IN_USE_HIGH_WATER:   26622892
        TEMP_ALLOCATED_SIZE:      32768
        TEMP_IN_USE_SIZE:         10570
        TEMP_IN_USE_HIGH_WATER:   14192
        (Note: the high PERM_IN_USE_HIGH_WATER comes from a first test where I tried to cache too many rows)
      I then executed on the TimesTen machine:
      tisql> select avg(value) from tb_bd;
      It is still running after 10 hours, so I can already say that the query execution time doesn't really meet my expectations. :-)

      In Windows Task Manager I see that tisql is constantly using 13% of CPU (=100% / 8 cores), so it is only using one core, but even it was using all cores and the execution time would be 1/8th, it wouldn't meet my expectation. :-)

      I also see in Windows Tasking Manager that the "MemUsage" of my tisq processl is slowly getting higher and higher, currently 14GB. My understanding is that it is mapping the shared memory that already is mapped by the TimesTen process which has around 24GB mapped. So the query is probably 53% through and the total query time might be around 20 hours.

      My questions:

      1.) For what I tested, 1 GB of Oracle table data needs around 4-5 GB of memory in the TimesTen database. I read a forum post that explained it with "data is optimized for performance, not for space in TT", but I don't quite buy it. A factor of 4-5 means that the CPU has to go through 4-5 times the amount of data. The data is not compressed in the Oracle database, but it is there in its natural binary form. I would like to understand why data is taking so much more space in TT - like when you have a NUMBER value in Oracle, what does TT do with it to make it 4-5 times bigger and why does it do that?

      2.) Regarding the query performance: How long can it take even for one core to scan through around 20GB of in-memory data, count the rows, sum up the NUMBER from one column and do a division to get the avg(<column>)? Is there something flawed with my setup?

      Thanks for any insights!

      Kind regards,

      Edited by: user11973438 on 06.09.2012 23:27
        • 1. Re: Questions after first TimesTen test: Query peformance and memory footprint
          Update: The query just finished after around 12 hours, with only 14GB mapped in the tisql process:
          Command> select avg(wert) from tb_bd;
          < 51.36905989353454114659037503359641405236 >
          1 row found.
          • 2. Re: Questions after first TimesTen test: Query peformance and memory footprint
            Gennady Sigalaev
            Hi user11973438,

            I am sure that something wrong with your configuration.

            I've done a couple of tests.

            There is my configuration (very old desktop):

            CPU: Intel Core 2 Duo CPU 2.33 ГГц
            RAM: 4 GB DDR2
            OS - Fedora 8 x64 (
            Oracle TimesTen (64 bit Linux)
            Command> select count(*) from accounts;
            Query Optimizer Plan:
              STEP:                1
              LEVEL:               1
              OPERATION:           TblLkSerialScan
              TBLNAME:             ACCOUNTS
              IXNAME:              <NULL>
              INDEXED CONDITION:   <NULL>
              NOT INDEXED:         <NULL>
            < 30000000 >
            1 row found.
            AVG Results (direct connection): *1,920321 sec*
            AVG Result (client/server connection): *1,921004 sec*

            Could you please post the steps you've done for making the test.

            • 3. Re: Questions after first TimesTen test: Query peformance and memory footprint
              I agree that using 4-5 times more memory than Oracle is far from optimal. Your schema is unfortunately slightly pathological; normally we see more like 2-3 times (which is still too much really0. There are many internal differences between Oracle and TimesTen in how data is stored internally. Some are historical and some are due to optimisation for performance rather than storage efficiency.

              For example:

              1. Oracle rows are always variable length in storage whereas TimesTen rows are always fixed length in storage.

              2. In Oracle, a column defined as NUMBER only occupies the space actually needed based on the value stored. In TimesTen a NUMBER column always occupies space to store the maximum possible precision and so occupies 22 bytes. You can reduce this by explicitly limiting it using NUMBER(n) or NUMBER(n,p).

              3. TimesTen does not support any kind of parallel query within a single datastore. All queries will execute using at maximum single CPU core. Oracle DB supports parallel query and so thsi can make a big difference for some types of query.

              4. NUMBER is implemented in software and is relatively inefficient. Computing the average of almost 100M rows will take some time... You could try changing this to a native binary type (TT_INTEGER, TT_BIGINT, BINARY_DOUBLE depending on your data); this will probably give a useful improvement (but see point 5 below).

              5. With a database this size it is possible that Windows is doing a lot of paging while the query is running. I have myself also observed on Windows that there seems to be a penalty when a process touches/maps a page for the first time. You should monitor paging activity via Task Manager as the query runs. Any significant paging will really hurt performance. Also, try running the query a second time without disconnecting ttIsql as this may also show a benefit. On Unix/Linux platforms we provide an option (MemoryLock==4) to lock the whoel database into physical memory to prevent any paging but this is not available on Windows.

              • 4. Re: Questions after first TimesTen test: Query peformance and memory footprint
                Thanks Chris for the helpfull and honest answer!

                Regarding the execution speed: I rebooted the server and the query now runs in 15 seconds:
                Command> select sysdate from dual;
                < 2012-09-07 11:55:00 >
                1 row found.
                Command> select avg(value) from tb_bd;
                < 51.36905989353454114659037503359641405236 >
                1 row found.
                Command> select sysdate from dual;
                < 2012-09-07 11:55:15 >
                1 row found.
                That is more like what I was expecting. Running the query against the production DB with similiar hardware and no parallism (Oracle SE), takes around 3 minutes:
                select avg(value) from tb_bd where id>=1511225399;
                --> 182 seconds
                | Id  | Pid | Ord | Operation          | Name             | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
                |   0 |     |   3 | SELECT STATEMENT   |                  |      1 |  1391K(100)|      1 |00:03:01.76 |    8072K|   8071K|
                |   1 |   0 |   2 |  SORT AGGREGATE    |                  |      1 |            |      1 |00:03:01.76 |    8072K|   8071K|
                |*  2 |   1 |   1 |   TABLE ACCESS FULL| TB_BD            |      1 |  1391K (11)|    108M|00:03:11.48 |    8072K|   8071K|
                This scanned 8.072.000 Blocks * 8 KB / Blocks = 61GB of data (and actually loaded most of that from disk, though likely all cached in the SAN level) in 3 minutes.
                So TT is around 12 times faster here (with a grain of salt, since TT only has a subset of the data and Oracle does a FTS through the whole table).

                Is this in the ballpark what I can expect from TT?

                Btw, ttstatus says that the shared memory is locked. Unfortunetely, I don't know what it said before the reboot:
                Shared Memory KEY Global\tt_dbat112.k||oracle|timesten|TT_DBA~1.SHM.37 HANDLE 0x2ec (LARGE PAGES, LOCKED)
                Kind regards,

                Edited by: user11973438 on 07.09.2012 03:40

                Edited by: user11973438 on 07.09.2012 03:40
                • 5. Re: Questions after first TimesTen test: Query peformance and memory footprint
                  Seems the cure for everything on Windows is to reboot :-) Seriously though, I suspect there was some memory related issue before and probably a lot of heavy paging going on but ubnless it occurs again and you can take a look there is no proof. Certainly this performance level is much more what I would expect!

                  The issue of locking the memory is interesting. We never used to have this functionality on Windows and indeed we do not expose any setting for it. Maybe we have added functionality to the product recently to lock the memory if possible (I need to check on that) or maybe it is something to do with your Windows config. I see that it says 'LARGE PAGES' so you must have some different config (maybe because it is a server). I do not see the same thing on my Windows 7 x64 machine (in fact I see 'Lock Status Unknown'). I will check on this.

                  • 6. Re: Questions after first TimesTen test: Query peformance and memory footprint
                    I checked with the vmmap tool from the SysInternals suite and it also shows the 26GB of locked, shared memory, so it's not a display quirk with ttstatus.
                    • 7. Re: Questions after first TimesTen test: Query peformance and memory footprint
                      I checked on this and on Windows we will always try and lock the memory (and use large pages if available). Whether either of these work depends on the privileges assigned to the TimesTen instance administrator user. If the TT instance admin user has SeLockMemoryPrivilege then we should be able to use large pages (which will lock the datastore in memory).