This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Feb 26, 2013 9:12 AM by jgarry RSS

Multi-threaded or Dedicated TNS

yxes2013 Newbie
Currently Being Moderated
Hi all,

SLES 11
Oracle 10.2.0.4

We have 1000+ POS machines connecting to a central database from 8am-6pm. All the machines is just doing "insert" 1 row.

What is the advantage of making its tns multi-threaded against dedicated? Assuming that I got lots of memory like 12Gb.


Thanks a lot,

zxy

Edited by: yxes2013 on 24.2.2013 19:40 **** addling OS or DB version :)
  • 1. Re: Multi-threaded or Dedicated TNS
    rp0428 Guru
    Currently Being Moderated
    If you only have 2 machines connecting each minute why is there even anything to worry about?
  • 2. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    dear...it is 1000 POS machines againts 1 database server wtih 12Gb RAM :).
    I just want to know if I can full blast allocate my memory.
  • 3. Re: Multi-threaded or Dedicated TNS
    asahide Expert
    Currently Being Moderated
    Hi,

    It's a small issue..
    If 1000 connection will be established, then you consider about this issue..

    Anyway, what is your appl server ? Do you have plan to use connection pool ?

    Regards,
  • 4. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    It is a "cashering" machine :( How can I best setup it for performance? Is it by connection pooling?
  • 5. Re: Multi-threaded or Dedicated TNS
    rp0428 Guru
    Currently Being Moderated
    >
    it is 1000 POS machines againts 1 database server wtih 12Gb RAM .
    I just want to know if I can full blast allocate my memory.
    >
    You said that over a 10 hour window each machine will only insert ONE ROW. That is a 600 minute window.

    I'm trying to understand why you think that is anything to worry about? Are you suggesting that all 1000 connections could possibly be at exactly the same time during that 600 minute window? That seems pretty unlikely to me.
  • 6. Re: Multi-threaded or Dedicated TNS
    sb92075 Guru
    Currently Being Moderated
    asahideO wrote:
    Hi,

    It's a small issue..
    If 1000 connection will be established, then you consider about this issue..

    Anyway, what is your appl server ? Do you have plan to use connection pool ?

    Regards,
    I've supported 1500 concurrent OLTP user on a system with under 1GB RAM painlessly.
    Where they were in the application "continuously" without bottlenecks.
  • 7. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    Sorry for not being clear :(

    The POS machines will post transactions continuously for the 10 hr window. The average total posted rows per day is 12million rows.

    How do I setup my database server for performance consideration? Do I need multi-threaded or dedicated service.

    Gracias...
  • 8. Re: Multi-threaded or Dedicated TNS
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    A thousand dedicated server processes will take up significant RAM. Your page table can also be very large (this is a concern on Linux)).

    How frequently do these POS terminals execute the INSERTs ?

    What version of Oracle are you running ? Do the POS terminals have the Oracle client of the same version ?

    If the POS terminals are making direct connections to the database and not connecting via an Application Server :
    I would consider Multi-Threaded (known as Shared) server architecture. In 11g, you also have the option of a Database Resident Connection Pool.

    If the POS terminals are connecting via an Application Server :
    I would consider Connection Pooling on the Application Server.


    Hemant K Chitale
  • 9. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    Hi hem,
    How frequently do these POS terminals execute the INSERTs ? (once every minute)
    What version of Oracle are you running ? Do the POS terminals have the Oracle client of the same version ? (10.2.0.4 , yes same version)
    If the POS terminals are making direct connections to the database and not connecting via an Application Server : ( I think so)
    I would consider Multi-Threaded (known as Shared) server architecture. In 11g, you also have the option of a Database Resident Connection Pool. ( 10g only)
    If the POS terminals are connecting via an Application Server : ( I will check first, not sure yet)
    I would consider Connection Pooling on the Application Server. ( Got it :) thanks)
  • 10. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    Hi Hem,Sb,Asahi,

    We are using failover,load-balancing, and standby too :(.

    With tns similar to this:
    PROD2=
     (DESCRIPTION= 
       (LOAD_BALANCE=on)                                    # 1
       (FAILOVER=on)
       (ADDRESS_LIST= 
         (SOURCE_ROUTE=yes) 
         (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))    # 2
         (ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521)))
      (ADDRESS_LIST= 
         (SOURCE_ROUTE=yes) 
         (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(port=1630)) 
         (ADDRESS=(PROTOCOL=tcp)(HOST=host4)(port=1521)))
       (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))  # 3
    How do the tns know which server is busy and which server has less load? To promote load balancing?
    Are there other parameters to be added here?



    Thanks,
  • 11. Re: Multi-threaded or Dedicated TNS
    Girish Sharma Guru
    Currently Being Moderated
    yxes2013 wrote:
    Hi Hem,Sb,Asahi,

    We are using failover,load-balancing, and standby too :(.

    With tns similar to this:
    PROD2=
    (DESCRIPTION= 
    (LOAD_BALANCE=on)                                    # 1
    (FAILOVER=on)
    (ADDRESS_LIST= 
    (SOURCE_ROUTE=yes) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))    # 2
    (ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521)))
    (ADDRESS_LIST= 
    (SOURCE_ROUTE=yes) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(port=1630)) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=host4)(port=1521)))
    (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))  # 3
    How do the tns know which server is busy and which server has less load? To promote load balancing?
    Are there other parameters to be added here?
    No, you don't need to know which server is busy or idle. When you said LOAD_BALANCE=on, it means Oracle net service will balance the load to all hosts equally internally. In your case listener will start to hand over client connect request by host1 randomly.

    The listener on host1 compares the load of the instances host1 and host2. The comparison takes into account the load on nodes hos1 and host2, respectively. Since host2 is less loaded than listener will auto hand over the client connect request to host2, you don't need to know/doing anything else.

    Regards
    Girish Sharma
  • 12. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    Thanks Gir :)
    No, you don't need to know which server is busy or idle. When you said LOAD_BALANCE=on, it means Oracle net service will balance the load to all hosts equally internally. In your case listener will start to hand over client connect request by host1 randomly.
    
    The listener on host1 compares the load of the instances host1 and host2. The comparison takes into account the load on nodes hos1 and host2, respectively. Since host2 is less loaded than listener will auto hand over the client connect request to host2, you don't need to know/doing anything else.
    Will these activity cause performance degradation due to constant checking of the background process which server to connect?

    I can not understand why the sample tns used different lisneter ports? when 1521 can be used in all aspects :)
    Does this mean that host1,host2,host3,host4 can failover to anyone?

    Edited by: yxes2013 on 24.2.2013 21:26
  • 13. Re: Multi-threaded or Dedicated TNS
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    (LOAD_BALANCE=on)
    This is client-side load balancing. The client decides which server to connect to. In theory, it randomly selects an address. (In practice, you might see the client sending connections more frequently to one server than another if the application frequently does a connect-insert-disconnect).

    Are you running RAC ? You would then be configuring server-side load balancing whereby PMON communicates with the registered listeners and updates the listeners about "load" so that a listener may decide which server should an incoming connection request be directed to.

    Does your application maintain persistent connections from the POS terminals or does it do a "connect-insert-disconnect" cycle every minute ?
    (In the latter case, the connect and disconnect overheads may a significant fraction of the total "transaction time" -- which can be reduced by using Shared Servers)

    Hemant K Chitale

    Edited by: Hemant K Chitale on Feb 25, 2013 1:37 PM
    Added "-- which can be reduced by using Shared Servers"
  • 14. Re: Multi-threaded or Dedicated TNS
    yxes2013 Newbie
    Currently Being Moderated
    Are you running RAC ? You would then be configuring server-side load balancing whereby PMON communicates with the registered listeners and updates the listeners about "load" so that a listener may decide which server should an incoming connection request be directed to.
    No dear...we are using dbvisit replication + standby...because it is cheaper than rac.

    We have active & active replication + standby. We just use simple failover setup, since RAC can be substitution by powerful cpus and big memory servers. ;)
1 2 Previous Next

Legend

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