Re: Increasing row retrieving speed via net8

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 16 Apr 2012 16:21:19 +0300
Message-ID: <CAMHX9JJMoJOo75iknUyoStYNdy5iqrvgXbskJBO0LsA5uFQYxw_at_mail.gmail.com>

Application think time...
Note that sometimes such locks & mutexes are used for putting threads to sleep by application, by design. So this might not be contention due to some scalability problem, but rather just how the application works (the main thread waits for the socket and all the other threads wait to be posted by a mutex release). This is the case on solaris for example, when you're using user-mode asynch IO via libaio ... the worker threads all wait for a lock, which the main thread then releases, once there's IO to do.

So, I'm still not fully convinced that this is an application side contention issue - it might well be, but some apps just work that way. As I wrote previously, increasing the arraysize would probably be the best way to get extra throughput out of a single connection (or just use multiple connections as that gave you the aggregate throughput you needed).

-- 
Tanel Poder
Enkitec Europe
http://www.enkitec.com/
Advanced Oracle Troubleshooting v2.0 Seminars in May/June 2012!
http://blog.tanelpoder.com/seminar/



On Fri, Apr 13, 2012 at 7:02 PM, GG <grzegorzof_at_interia.pl> wrote:



> Making long story short .

> After quick talk with OS Admin I got root access :) and first try was

> (well second because first I was typing rm -rf / too scary him :) :

>

> strace -p _pid_of_pmdtm_binary -f -c

>

> -f is a must because pmdtm makes like 19 threads and they do actual work I

> saw something like this

>

> time spent 80% -> futex!!

>                    10% poll

>                        5 % read

>                        5% other stuff .

>

>

> so only 5% of work time socket was read and most of the time threads was

> fighting each other in

> somekind of concurency issue :) or feature .

>

> So that explains why messing with sdu , socket buffers, tcp stack buffers

> and so on gave only minor changes in rows fetched per sec

> from that tool .

>

> Comparing that to sqlplus , strace gives something like that:

>

> time spent syscall

> 99% read

> 1 % write

>

> Funny thing is You have to find 'sweet spot' when setting above net stack

> / net8 related parameters,

> its not about simply increasing them :).

> You can observe how things are going with strace on sqlplus and checking

> read syscall arguments , and

> how they are changing when You messing for example with sdu size .

> Tcpdump is quite useful and snapper of coz as we are DBAs .

> Just remember with 1500 MTU You cant do much , I think jumbo frames and/or

> GBit interface is the way to go

> if cant afford You can do parallelizm via opening many connections and

> aggregate them in 'smart' way .

> Thanks for all comments, now its time for Informatica vendor response :).

>

> Regards

> GregG

>

>

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 16 2012 - 08:21:19 CDT