| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sqlplus connect too slow under load both from DB & APP Servers
For some parameters, setting them to excessive values can cause performance problems . Consider how semaphores & shared memory parameters should be calculated per Sun:
Based on that, you might arrive at these values:
forceload: sys/shmsys
forceload: sys/semsys
set shmsys:shminfo_shmmax=4294967295 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=10 set semsys:seminfo_semmns=4000 set semsys:seminfo_semmni=40 set semsys:seminfo_semmsl=100 set semsys:seminfo_semmnu=40 set semsys:seminfo_semmap=4000 set semsys:seminfo_semopm=100
VIVEK_SHARMA wrote:
>
> -----Original Message-----
> Sent: Tuesday, April 02, 2002 9:15 AM
> To: 'Karl Vogel'; 'sunmanagers_at_sunmanagers.org'
> Servers
>
> Hi
>
> Because of Increase of SEMAPHORES ( semmns to 100,000 from 20,000 ) the Slowdown of "sqlplus" Connect to Database started Occuring after 1050 Process Connections Instead of 850 (Previously) .
>
> We are yet to try the Settings of Reducing swapfs_minfree , noatime Option with mount , inode cache Hit ratio as advised by you Below . I will Revert to you .
>
> Thanks Indeed
>
> Vivek
>
> -----Original Message-----
> Sent: Thursday, March 28, 2002 1:26 PM
> To: 'Karl Vogel'
> Servers
>
> On Adding the Following Parameters to the /etc/system the Slowdown of "sqlplus" Connect to the Database Started Occuring after 850 Process Connections Instead of 500 (Previously)
>
> set tcp:tcp_conn_hash_size=262144
>
> set rlim_fd_max=8092
> set rlim_fd_cur=8092
> set autoup=900
> set tune_t_fsflushr=1
> set bufhwm=8000
>
> We Are Considering INCREASING the Semaphores to the Following Values :-
>
> - /etc/system File on the DB & APP Servers :-
>
> set shmsys:share_page_table=1
> set maxuprc=20000
> set maxusers=1500
>
> set shmsys:shminfo_shmseg=200
> set shmsys:shminfo_shmmin=2
> set shmsys:shminfo_shmmni=5000
> set shmsys:shminfo_shmmax=25769803776
> forceload: sys/shmsys
> forceload: sys/shmsys
> set semsys:seminfo_semmsl=1000
> set semsys:seminfo_semaem=16384
> set semsys:seminfo_semmap=700000
> set semsys:seminfo_semmni=60000
> set semsys:seminfo_semmns=100000
> set semsys:seminfo_semmnu=10000
> set semsys:seminfo_semume=100
> set semsys:seminfo_semvmx=32767
>
> Thanks indeed for the Very Valuable Suggestions
>
> Regards
>
> Vivek
>
> -----Original Message-----
> Sent: Wednesday, March 27, 2002 12:11 AM
> To: VIVEK_SHARMA
> Servers
>
> >> On Tue, 26 Mar 2002 17:06:21 +0530,
> >> "VIVEK_SHARMA" <VIVEK_SHARMA_at_infy.com> said:
>
> V> Machine Model = E6500
> V> No. of CPUs = 20
> V> RAM = 40 GB
> V> SWAP = 4 GB
>
> You may need more swap. I've had the best luck with multiple 1-Gbyte
> swap partitions on different devices.
>
> V> After Migration from Oracle 7.3.4 to 8.1.7.2 on Solaris 2.8 sqlplus
> V> becomes SLOW as the Number of Unix Users Connecting to the Database
> V> Increases to about 400 whereafter even after all users are logged out
> V> the response continues to be Slow even after the machine is Booted
>
> Some suggestions are below. You may be spending too much time scanning
> memory for dirty pages.
>
> --
> Karl Vogel ASC/YCOA, Wright-Patterson AFB, OH 45433
> vogelke@dnaco.net http://www.dnaco.net/~vogelke
>
> I am not the reincarnation of Sammy Davis Jr.
> --written on blackboard by Bart Simpson
>
> -----------------------------------------------------------------------------
> http://docs.sun.com/ab2/coll.709.2/SOLTUNEPARAMREF/
>
> Overview of Solaris System Tuning
> Tuning a Solaris System
> Tuning the Solaris Kernel
> Special Structures
> Viewing System Configuration Information
> kstats
> Solaris Kernel Tunables
> NFS Tunable Parameters
>
> -------------------------------------------------------------------------
> If you have an application that's an incredible swap hog, or the system is
> really slowing down, try adding the lines below to /etc/system and
> rebooting. I run with these settings and they've never caused me trouble.
>
> *
> * Swap
> * System keeps 1/8th of all memory for swap, which is too much for
> * a 4GB system. Reduce that to 32 Mbytes (4096 8K pages).
> set swapfs_minfree=4096
> *
> * Memory management
> *
> * http://www.carumba.com/talk/random/tuning-solaris-checkpoint.txt
> * Tuning Solaris for FireWall-1
> * Rob Thomas robt_at_cymru.com
> * 14 Aug 2000
> *
> * On firewalls, it is not at all uncommon to have quite a bit of
> * physical memory. However, as the amount of physical memory is
> * increased, the amount of time the kernel spends managing that
> * memory also increases. During periods of high load, this may
> * decrease throughput.
> *
> * To decrease the amount of memory fsflush scans during any scan
> * interval, we must modify the kernel variable autoup. The default
> * is 30. For firewalls with 128MB of RAM or more, increase this
> * value. The end result is less time spent managing buffers,
> * and more time spent servicing packets.
> set autoup = 120
>
> -------------------------------------------------------------------------
>
> Run "mount" to see how your filesystems are set up. You can mount
> filesystems with "noatime" turned on, meaning don't bother updating the
> access time whenever a file is opened. We use this under Solaris-8,
> and it makes a *huge* difference if you're doing something to a large
> number of small or medium sized files.
>
> Check the size of your inode caches, which keep track of
> previously-accessed files. Run the DNLC script below as root to see your
> hit-rate percentage. If it's under 90-95%, you need to up the cache size.
> The easiest way to do that is setting maxusers in /etc/system to something
> like 2048.
>
> ---------------------------------------------------------------------------
> #!/bin/sh
> #
> # NAME:
> # dnlc
> #
> # SYNOPSIS:
> # dnlc
> #
> # DESCRIPTION:
> # "dnlc" reports on Directory name lookup cache statistics from
> # the kernel. This corrects a bug in vmstat.
> #
> # To change the kernel values, add something like this
> # to /etc/system and reboot. Both "nnn" numbers should be the
> # same.
> #
> # set ncsize = nnn
> # set ufs_ninode = nnn
> #
> # AUTHOR:
> # Kimberley Brown - UKAC Kernel Support
> # comp.unix.solaris
>
> PATH=/bin:/usr/bin:/usr/local/bin
> export PATH
>
> adb -k /dev/ksyms /dev/mem <<END
> ="** Directory/Inode Cache Statistics **"
> ="----------------------------------------"
> ufs_ninode/D"Inode cache size"
> ncsize/D"Directory name cache size"
> ncstats/D"# of cache hits that we used"
> +/D"# of misses"
> +/D"# of enters done"
> +/D"# of enters tried when already cached"
> +/D"# of long names tried to enter"
> +/D"# of long name tried to look up"
> +/D"# of times LRU list was empty"
> +/D"# of purges of cache"
> *ncstats%1000>a
> *(ncstats+4)%1000>b
> *(ncstats+14)%1000>c
> <a+<b+<c>n
> <a*0t100%<n=D"Hit rate percentage"
> ="(See /usr/include/sys/dnlc.h for more information)"
> END
>
> exit 0
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: VIVEK_SHARMA
> INET: VIVEK_SHARMA_at_infy.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suzy Vordos INET: lvordos_at_qwest.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Apr 02 2002 - 16:35:39 CST
 |
 |