1 2 3 4 Previous Next 46 Replies Latest reply: Apr 19, 2012 6:27 AM by Chrisjenkins-Oracle RSS

    Perfomance Tunning required in Timesten

    user10366531
      I want to i monitor Timesten perfomance in TT DB .
      I am checking through oracle sites and need to know that Timesten plug in is avaiable to see the statistics of timesten DB.
      How can I install this Plug in. or is there any other alternative to get this perfomace statistcs report.
        • 1. Re: Perfomance Tunning required in Timesten
          Tim Vincent
          Take a look here in the "TimesTen EM-Plugin Demos" section

          [http://www.oracle.com/technetwork/products/timesten/overview/timesten-tutorials-demos-1443972.html#tutorials ]

          And in the TimesTen docs

          [http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21649/toc.htm]

          The EM plugin is available from the OTN downloads page

          [http://www.oracle.com/technetwork/products/timesten/downloads/index.html]
          • 2. Re: Perfomance Tunning required in Timesten
            Gennady Sigalaev
            Dear user10366531

            I just would like to add something to Tim's message.
            You can also monitor performance by using SYS.MONITOR http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21644/systemtables.htm#autoId43)
            and SYS.SYSTEMSTATS (http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21644/systemtables.htm#autoId49) system tables.

            Best regards,
            Gennady
            • 3. Re: Perfomance Tunning required in Timesten
              user10366531
              can anyone suggest me how can Times Ten plug in Useful for report.
              being a DBA what are parameters are required to tune in Times ten DB.
              • 4. Re: Perfomance Tunning required in Timesten
                Tim Vincent
                For Performance tuning take a look at this chapter in the docs here http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21633/perform.htm#CACFHAAD
                • 5. Re: Perfomance Tunning required in Timesten
                  user10366531
                  thanks for your reply. but i want to know more in details. I am facing below problem.
                  1) I installed timesten DB. i have put all the basic configuration as it is. only i changed its permsize=2gb and tempsize=1G.
                  rest of the parameters are same.
                  2) I have put load on TT for 4,00,000 customer for 2000 TPS.things were working smoothly.we have achived 99%request are passed smoothly <2 second.
                  3) Now While i am putting load for 10,00,000 customers, My load average goes around 100 + .and response times also goes Down.

                  Now i wnt to tune Times Ten DB.I need to know now which parameters i need to change to get the max response time.
                  • 6. Re: Perfomance Tunning required in Timesten
                    Tim Vincent
                    Hi,

                    All the main best practices for performance tuning are listed in the URL above. If you have just gone with the defaults then these are the areas I would look at:

                    Increase LogBufMB (in your ODBCINI) if needed
                    In ttIsql run the monitor command:
                    Command>monitor;
                    Look for LOG_BUFFER_WAITS if this value is increasing then you need to increase the size of your transaction log buffer LogBufMB and resize the LogFileSize(in your ODBCINI) value to match.

                    Also displayed in monitor is LOCK_TIMEOUTS and LOCK_GRANTS_IMMED if these are increasing then you have possible lock contention in your application.

                    What sort of hardware are you running TT on and what version of TT are you using?
                    You may want to increase LogBufParallelism(in your ODBCINI) to be equal to the number of CPU cores you have on the machine.

                    If there is any possibility that your machine is under memory pressure, be sure to use MemoryLock=4 if your platform supports it.

                    Separate the LogDir(in your ODBCINI) directory filesystem from the DataStore(in your ODBCINI) checkpoint files filesystem, by default they are located in the same filesytem and this can lead to disk contention.

                    Also how is the application connecting to TT? Is it in direct mode or Client/Server?
                    What language is the application written in? Is it a mulithreaded application?
                    What sort of load is running on TT? Is it read-only? Is it 80% read 20% update? Or?
                    What does the SQL look like? Are you using prepared statements?
                    Are you using replication or caching?

                    Let us know how you get on with the changes above?

                    Tim
                    • 7. Re: Perfomance Tunning required in Timesten
                      user10366531
                      Thanks for your reply.

                      Please check the below value while load was running.
                      -------------------------------------------------------------------------
                      LOCK_GRANTS_IMMED: 3246464
                      LOCK_TIMEOUTS: 0
                      LOG_BUFFER_WAITS: 0
                      ----------------------------------------------------------------------

                      version : TimesTen Release 11.2.2.2.0 (64 bit Linux/x86_64) (tt11:65527) 2011-12-23T09:26:28Z
                      ------------------------------------------------------------------------
                      DSN
                      -----------------
                      [My_ttdb]
                      Driver=/backup/Timesten/TimesTen/tt11/lib/libtten.so
                      DataStore=/backup/Timesten/TimesTen/tt11/Datastore/my_ttdb
                      logdir=/backup/Timesten/TimesTen/Log
                      PermSize=4098
                      tempSize=1024
                      LogFileSize=1024
                      LogBufMB=1024
                      PLSQL=1
                      DatabaseCharacterSet=AL32UTF8
                      OracleNetServiceName=MTLOAD
                      UID=CRESTELOCSSQA
                      PWD=CRESTELOCSSQA
                      OracleId=CRESTELOCSSQA
                      OraclePwd=CRESTELOCSSQA
                      passThrough=1
                      Connections=1500
                      ---------------------------------------------

                      log file and data store are on seperate location.


                      ------------------------------------------

                      Also how is the application connecting to TT? Is it in direct mode or Client/Server?
                      My comment: Direct Mode

                      What language is the application written in? Is it a mulithreaded application?
                      java-

                      What sort of load is running on TT? Is it read-only? Is it 80% read 20% update? Or?
                      there is maximum insert and update.

                      What does the SQL look like? Are you using prepared statements?- we are using insert and update + select and these single query having index and primary key as well.

                      Are you using replication or caching? - caching
                      • 8. Re: Perfomance Tunning required in Timesten
                        user10366531
                        while i am processing request for 2000 Tps , we are having 8 CPU.
                        all CPUs are 100% occupied.
                        • 9. Re: Perfomance Tunning required in Timesten
                          Tim Vincent
                          ok thanks so add the following to your DSN:
                          LogBufParallelism=8

                          I see you have 'passThrough=1' this means that inserts/updates that reference one or more tables not in TimesTen, are passed through to the Oracle database. Does this happen in your application? If so this will cause slower response times. Are you able to test with 'PassThrough=0'?

                          Your DataStore and LogDir need to be in separate filesystems or disks not just a separate location. If you are doing a lot of updates/inserts you will experience disk contention.

                          I see you have Connections=1500. How many concurrent connections do you have connected to TT? If you have substantially more connections than CPU/cores on the machine you may experience lower throughput. Are you using a connection pool at all? Try the application with the same amount of connections as CPUs/cores then add connections until to reach the optimal number.

                          On the caching side I assume you're using AWT (asynchronous writethrough) cache groups? In which case try with
                          CacheAwtParallelism=4

                          Another thing as with any database make sure you have run update statistics.

                          After you have made the changes post your DSN and the output of monitor.
                          • 10. Re: Perfomance Tunning required in Timesten
                            user10366531
                            6226: Ignoring value requested for first connection attribute 'LogBufParallelism' -- value currently in use: 4, requested value: 8
                            6228: Invalid value (8) for CacheAwtParallelism connection attribute -- value must be the same as the current data store value (1)
                            The command failed.

                            I may not able to Up my DSN by setting LogBufParallelism=8

                            Regards,
                            • 11. Re: Perfomance Tunning required in Timesten
                              Chrisjenkins-Oracle
                              These attributes are 'first connection' attributes. They take effect at the tiem the datastore is loaded into memory and cannot be changed while the datastore is in memory. To change them you need to:

                              1. Stop all applications that are connected to the datastore and make sure they are disconnected (check with ttStatus)
                              2. Stop the replication and/.or cache agents for the datastore (if running).
                              3. If datastore has non-default ramPolicy, then explicitly unload it using ttAdmin -ramUnload (may have to change ramPolicy first)
                              4. Verify that datastore is not still in memory by using ttStatus
                              5. Change values for LogBufParallelism and CacheAwtParallelism in DSN settings in sys.odbc.ini
                              6. Connect to the datastore as the instance administrator user to action the changes.
                              7. Re-start repagent/cache agent and applications.

                              Chris
                              • 12. Re: Perfomance Tunning required in Timesten
                                user10366531
                                Done with LogBufParallelism=8. and below is output of Monitor;

                                still CPU gets 100% occupied.


                                TIME_OF_1ST_CONNECT: Mon Mar 26 15:17:59 2012
                                DS_CONNECTS: 512
                                DS_DISCONNECTS: 500
                                DS_CHECKPOINTS: 0
                                DS_CHECKPOINTS_FUZZY: 0
                                DS_COMPACTS: 0
                                PERM_ALLOCATED_SIZE: 4196352
                                PERM_IN_USE_SIZE: 1064455
                                PERM_IN_USE_HIGH_WATER: 1064528
                                TEMP_ALLOCATED_SIZE: 1048576
                                TEMP_IN_USE_SIZE: 48788
                                TEMP_IN_USE_HIGH_WATER: 48852
                                SYS18: 0
                                TPL_FETCHES: 0
                                TPL_EXECS: 0
                                CACHE_HITS: 0
                                PASSTHROUGH_COUNT: 338
                                XACT_BEGINS: 3897
                                XACT_COMMITS: 3888
                                XACT_D_COMMITS: 0
                                XACT_ROLLBACKS: 0
                                LOG_FORCES: 0
                                DEADLOCKS: 0
                                LOCK_TIMEOUTS: 0
                                LOCK_GRANTS_IMMED: 40983
                                LOCK_GRANTS_WAIT: 9
                                SYS19: 50
                                CMD_PREPARES: 61
                                CMD_REPREPARES: 0
                                CMD_TEMP_INDEXES: 0
                                LAST_LOG_FILE: 433
                                REPHOLD_LOG_FILE: 433
                                REPHOLD_LOG_OFF: 626745608
                                REP_XACT_COUNT: 3060
                                REP_CONFLICT_COUNT: 0
                                REP_PEER_CONNECTIONS: 0
                                REP_PEER_RETRIES: 0
                                FIRST_LOG_FILE: 433
                                LOG_BYTES_TO_LOG_BUFFER: 5502728
                                LOG_FS_READS: 0
                                LOG_FS_WRITES: 8
                                LOG_BUFFER_WAITS: 0
                                CHECKPOINT_BYTES_WRITTEN: 0
                                CURSOR_OPENS: 10473
                                CURSOR_CLOSES: 10443
                                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
                                • 13. Re: Perfomance Tunning required in Timesten
                                  Chrisjenkins-Oracle
                                  It's hard to give tuning advice with only limited information but here are some things to think about:

                                  1. Full CPU utiliisation is not necessarily an issue. With a traditional client/server RDBMS such as oracle where the application and DB are on different machines the application is often waiting for a response from the databse. Hence (a) the CPU utilisation onb the application machine tends to be lower (lots of waiting which does not use CPU) and (b) a relatively large number of concurrent connections (app -> DB) are needed to get good overall throughput. If you are using direct mode, neither of these apply to TimesTen; database and application processing occur on the same machien resulkting in higher CPu consumption, there is no 'waiting' so again CPU usage is higher and lastly you do not want to jhave lots of concurrent connections as this just intorduces contention both within the database (locks and latches) and also at the OS level (scheduling).

                                  2. Have you verified the query plans for all SQL statements being executed by the application? A bad query plan can result in low performance and high CPU usage.

                                  3. I see PASSTHROUGH_COUNT: 338. PassThrough is a great convenience feature but it is not intended for high performance and is quite 'expensive'. If you're application makes a lot of queries that need to run against Oracle it is better (i.e. cheaper and faster) to have a separate connection direct to Oracle for those.

                                  4. These stats show LOG_BYTES_TO_LOG_BUFFER: 5502728. Either this workload is 99.9% read or these stats represent a very short amount of time. For stats to be useful you need to run a realistic workload and then sample the 'monitor' stats a few times at 60 second intervals while the workload is running. Any kinf of meaningful performance test needs to run for many minutes (really 10s of minutes). You can't really say anythign useful from a test that runs for just a minute or two (or less).

                                  5. How many cores does this machine have? ANy hyper-threading? How many concurrent connections are there to TimesTen and how many of those are actually active (i.e. doing database work)?

                                  Chris
                                  • 14. Re: Perfomance Tunning required in Timesten
                                    user10366531
                                    can you please share how can we monitor this perfomance in timesten.
                                    In oracle we are having AWR report. but how can i Use same in Timesten.
                                    1 2 3 4 Previous Next