This discussion is archived
8 Replies Latest reply: Jan 14, 2013 11:39 PM by 983498 RSS

Unable to connect TT DSN "836: Cannot create data store shared-memory segm"

983498 Newbie
Currently Being Moderated
Hi all,

I'm loading tables from Oracle Database 11G(Source - Linux Machine - A), using ODI(Windows Machine - B) into TimesTen on Exalytics(Linux Machine - C).


We were loading a huge fact table of row count "176,041,880" (whose approx. size is 115GB on Oracle Database) into TimesTen and the session ended with following error in ODI:

---------------------------ODI Error----------------------------------------------

ODI-1217: Session INF_W_GL_OTHER_F (153003) fails with return code 778.
ODI-1226: Step INF_W_GL_OTHER_F fails after 1 attempt(s).
ODI-1240: Flow INF_W_GL_OTHER_F fails while performing a Loading operation. This flow loads target table W_GL_OTHER_F.
ODI-1228: Task SrcSet0 (Loading) fails on the target TIMESTEN connection tt_xyz.
Caused By: java.sql.BatchUpdateException: Batch command 189 failed: [TimesTen][TimesTen 11.2.2.2.0 ODBC Driver][TimesTen]TT0778: Log write failed because filesystem is full -- file "logmgr.c", lineno 6907, procedure "sbLogRecInsert"


--------------------------ttisql Error---------------------------------------------

At this moment when I checked the disk space on Exalytics(where our TimesTen is installed), it showed available space as "0".
I cleared some space on the disk, about 50GB+ then tried to connect the respective DSN, which triggered following Error in ttisql:

Command> connect "dsn=tt_xyz";
*836: Cannot create data store shared-memory segment, error 22*
*703: Subdaemon connect to data store failed with error TT836*
The command failed.
Command>

Command Run Time: Mon Jan 7, 22:01:20


--------------------------tterrors.log---------------------------------------------

Command Run Time: Mon Jan 7, 22:01:20

Error noted for same TimesStamp in tterrors.log:

22:01:23.47 Err : : 21748: TT14000: TimesTen daemon internal error: Error 22 creating shared segment, KEY 0x43100ed6
22:01:23.47 Err : : 21748: -- OS reports invalid shared segment size
22:01:23.47 Err : : 21748: -- Confirm that SHMMAX kernel parameter is set > datastore size
22:01:23.47 Err : : 14405: subd: Error identified in [sub.c: line 2375]
22:01:23.47 Err : : 14405: subd: (Error 836): TT0836: Cannot create data store shared-memory segment, error 22 -- file "db.c", lineno 9683, procedure "sbDbConnect"
22:01:23.47 Err : : 14405: -- file "db.c", lineno 9683, procedure "sbDbConnect"
22:01:23.47 Warn: : 14405: subd: connect trouble, rc 1, reason 836
22:01:23.47 Err : : 14405: Err 836: TT0836: Cannot create data store shared-memory segment, error 22 -- file "db.c", lineno 9683, procedure "sbDbConnect"
22:01:23.47 Err : : 21748: TT14000: TimesTen daemon internal error: Could not send 'manage' request to subdaemon rc 400 err1 703 err2 836
22:01:24.47 Warn: : 21748: 14405 ------------------: subdaemon process exited


-----------------------------sysctl.conf------------------------------------------

# Controls the maximum shared segment size, in bytes
############### Original
#kernel.shmmax = 68719476736
kernel.shmmax = 1099511627799

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 2048 64000 256 64

net.ipv4.tcp_rmem = 16777216 16777216 16777216
net.ipv4.tcp_wmem = 16777216 16777216 16777216
net.ipv4.tcp_mem = 16777216 16777216 16777216
net.core.optmem_max = 16777216
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.core.rmem_default=16777216
net.core.wmem_default=16777216
net.ipv4.ip_local_port_range = 9000 65500
# Changed from 10000 to 55000 by ME on Aug 6 2012
# Changed by ME to 150000 on Aug 24, 2012
vm.nr_hugepages = 410000
#
fs.file-max=262144
#Added by ME on Aug 6 2012
vm.hugetlb_shm_group = 500



---------------------------SYS.ODBC.INI----------------------------------------

[tt_xyz]
Driver=/u01/app/EXALYTICS_MWHOME/../TimesTen/tt1122/lib/libtten.so
DataStore=/u01/app/EXALYTICS_MWHOME/../aggregate_store/tt_xyz
LogDir=/u01/app/EXALYTICS_MWHOME/../aggregate_store/logs
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
LogFileSize=1024
LogBufMB=10240
LogBufParallelism=20
Preallocate=0
PermSize=600000
TempSize=200000
MemoryLock=4
CkptFrequency=30
CkptLogVolume=0
CkptRate=20
PrivateCommands=1
RangeIndexType=1
Connections=1024
RecoveryThreads=40

I'm unable to connect to this DSN anymore.
Does increasing LogFileSize will help? or is the problem related with shmmax and shmni in sysctl.conf?
Please help me with this issue, as we are in the middle of a Demo.

Thanks.
  • 1. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    ChrisJenkins Guru
    Currently Being Moderated
    Can you please confirm if you are using huge pages (post output of cat /proc/meminfo, output of cat /etc/security/limits.conf, output of ttVersion and output of cat /u01/app/TimesTen/tt1122/info/ttendaemon.options). I suspect that you may have an old shared memory segment 'left over' which is consuming your memory. You may be able to verify this using ttStatus and ipcs.

    Could you also confirm how you 'cleared some space on the disk'? Hopefully you did not delete any TimesTen related files...

    Thanks,

    Chris
  • 2. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    983498 Newbie
    Currently Being Moderated
    Hi Chris,

    Thanks for the reply. As you have asked:

    ------------------------------meminfo------------------------------

    MemTotal: 1058964444 kB
    MemFree: 690256 kB
    Buffers: 5168 kB
    Cached: 131236028 kB
    SwapCached: 16784 kB
    Active: 148184872 kB
    Inactive: 50435664 kB
    Active(anon): 66044216 kB
    Inactive(anon): 1353192 kB
    Active(file): 82140656 kB
    Inactive(file): 49082472 kB
    Unevictable: 0 kB
    Mlocked: 0 kB
    SwapTotal: 101023736 kB
    SwapFree: 38175028 kB
    Dirty: 936 kB
    Writeback: 0 kB
    AnonPages: 67396040 kB
    Mapped: 74248 kB
    Shmem: 136 kB
    Slab: 17530904 kB
    SReclaimable: 564676 kB
    SUnreclaim: 16966228 kB
    KernelStack: 72840 kB
    PageTables: 420584 kB
    NFS_Unstable: 0 kB
    Bounce: 0 kB
    WritebackTmp: 0 kB
    CommitLimit: 210665956 kB
    Committed_AS: 345615976 kB
    VmallocTotal: 34359738367 kB
    VmallocUsed: 1924132 kB
    VmallocChunk: 33553179080 kB
    HugePages_Total: 410000
    HugePages_Free: 405706
    HugePages_Rsvd: 156146
    HugePages_Surp: 0
    Hugepagesize: 2048 kB
    DirectMap4k: 5376 kB
    DirectMap2M: 2082816 kB
    DirectMap1G: 1071644672 kB



    ------------------------------limits.conf------------------------------

    ############################ Original Parameters ##########################
    #* hard nofile 65536
    #* soft nofile XS65536
    #* hard memlock unlimited
    #* soft memlock unlimited
    #* hard core unlimited
    #* soft core unlimited

    * hard nofile 131072
    * soft nofile 131072
    * hard memlock unlimited
    * soft memlock unlimited
    * hard core unlimited
    * soft core unlimited
    * hard nproc 131072
    * soft nproc 131072



    ------------------------------ttendaemon.options------------------------------

    # Commented values are default values
    #-supportlog /u01/app/EXALYTICS_MWHOME/../TimesTen/tt1122/info/ttmesg.log
    #-maxsupportlogfiles 10
    #-maxsupportlogsize 10485760
    #-userlog /u01/app/EXALYTICS_MWHOME/../TimesTen/tt1122/info/tterrors.log
    #-maxuserlogfiles 10
    #-maxuserlogsize 0x100000
    #
    -verbose
    #-tns_admin
    -server *****
    -serverpool 5
    -linuxLargePageAlignment 2


    ------------------------------ttversion------------------------------

    TimesTen Release 11.2.2.2.0 (64 bit Linux/x86_64) (tt1122:53396) 2011-12-23T09:26:28Z
    Instance admin: oracle
    Instance home directory: /u01/app/EXALYTICS_MWHOME/../TimesTen/tt1122
    Group owner: oracle
    Daemon home directory: /u01/app/EXALYTICS_MWHOME/../TimesTen/tt1122/info
    PL/SQL enabled.


    I did not delete any TimesTen related files. I removed old backup/dmp files from the machine.

    Thanks,
    Srikanth

    Edited by: 980495 on Jan 8, 2013 5:11 AM
  • 3. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    ChrisJenkins Guru
    Currently Being Moderated
    Thanks, that all looks in order. Can you please check what the output of the ttStatus and ipcs commands show? Is there still some large shared memory segment allocated?

    Also, how much swap space is configured on the machine (/sbin/swapon -s)?

    And lastly, what is the (numeric) primary group id of the 'oracle' user (output of 'id' command)?

    errno 22 on Linux is EINVAL. The shmget() call is failing with this error. According to the man page there are only a few possibilities:

    EINVAL A new segment was to be created and size < SHMMIN or size >
    SHMMAX, or no new segment was to be created, a segment with
    given key existed, but size is greater than the size of
    that segment.

    Chris
  • 4. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    983498 Newbie
    Currently Being Moderated
    Hi Chris,

    I really appreciate your help in this. But unfortunately we had to destroy the DSN, as it has been using up memory at peaks.
    We have decided to re-create the DSN and reload the tables, using Columnar Compression.
    Would this be any helpful? Are there any other alternatives to reduce amount of space taken by tables in TimesTen.


    I have another question.

    After destroying the Datastore successfully, the entries for it in sys.odbc.ini were retained.
    Can I simply edit the file and remove it?

    Thanks,
    Srikanth
  • 5. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    ChrisJenkins Guru
    Currently Being Moderated
    Hi Srikanth,

    If you are on an Exalytics system then Columnar Compression is a very good way to save memory (and potentially also boost performance in some cases). Also, data type mapping can yield useful savings too. We have a tool that can help with type mapping and deciding what to compress and this will likely be included (initially as a non-production utility) in an upcoming TimesTen release.

    With regard to destroying the datastore; ttDestroy simply removes the physical database files from disk and updates the main daemon catalog. It does not remove the entry in sys.odbc.ini (no TimesTen tool ever modifies that file). Once the database is destroyed you can simply edit the sys.odbc.ini file to remove the DSN or, if you actually want to re-create the database, you can just edit the parameters and then connect to the DSN as the instance administrator to re-create the database with the new settings.

    Chris

    Edited by: ChrisJenkins on Jan 9, 2013 3:17 PM
  • 6. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    983498 Newbie
    Currently Being Moderated
    Hi Chris,

    Yes, we are working on Exalytics environment.

    It would be grateful if such tool comes included with TimesTen.

    Thanks for the info on ttDestroy, I'm going to recreate the same Datastore by changing parameters.

    Meanwhile can you point me to any document that has data types for TT, equivalent to Oracle Datatypes. So that I can manually change the Data types of TT while creating the tables.

    Thanks,
    Srikanth
  • 7. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    ChrisJenkins Guru
    Currently Being Moderated
    We don't (yet) have any formal documentation on this but there are three kinds of type mapping that you might consider.

    'Standard' numeric type mapping. This consists of mapping certain types of NUMBER column to a compatible TimesTen native integer type. The native integer types are (a) smaller (in terms of memory requirements) and (b) faster (as they are hardware implemented native types as opposed to the software implemented NUMBER type). Types defined in Oracle as NUMBER(p,s) can be mapped as follows for the case where s=0.

    p < 5 -> TT_SMALLINT

    p >= 5 and p < 10 -> TT_INTEGER

    p >= 10 and p < 19 -> TT_BIGINT

    This mapping is safe and depending on the schema may result in useful savings.

    The other two mappings are more aggressive and should be used with caution.

    1. Analyse the data that you want tyo move to TimesTen in the source database and for NUMBER columns check (a) if they really have any fractional values, (b) if they have any negative values and (c) the maximum stored value and then apply the mapping I mentioned previously based on this analysis. You can also then consider additional mappings:

    Value >= 0 and <= 255 -> TT_TINyINT

    p >= 19 but < 22 -> NUMBER(p,0)

    2. Analyse the data in VARCHAR2 and/or NVARCHAR2 columns to determine the length of the maximum stored value. If it is < the defined maximum length of the column reduce the size of the column in TimesTen.

    These mappings are less safe in that if the data is subsequently modified in Oracle it may no longer meet the criteria used for the mapping and a subsequent load of the data into TimesTen may give errors. In that case you would have to regenerate the tables in TimesTen.

    As I mentioned, we have a tool which can do all this for you and in addition recommend/generate compressed TimesTen table definitions (if the source data is in an Oracle database of version 10gR2 or newer). If you contact your local Oracle office and ask them to get in touch with me we may be able to let you have this tool in advance of it's official release. We are looking for real world feedback on it to help improve it for future iterations.

    Chris
  • 8. Re: Unable to connect TT DSN "836: Cannot create data store shared-memory segm"
    983498 Newbie
    Currently Being Moderated
    Thanks Chris for sharing this valuable information.

    Currently I'm using Columnar Compression along with the DataTypes in reference to what you have mentioned.

    Regarding the tool, I'll take this to my superiors and see how it goes.

    Thank you very much for the help,
    Srikanth

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points