Procedure cache

[grega]

Need some ideas here. Sun Ultra 10, 256Mb memory, Solaris 2.6, ASE 11.9.2. I'm getting the "not enough procedure cache" error, particularly when running queries on large tables (500,000 - 1,000,000 rows) with a "group by" clause.

From sp_configure ...

Parameter Name                 Default     Memory Used Config Value Run Value   
--------------                 -------     ----------- ------------ ---------   
total memory                         14336       30000       15000        15000 
total data cache size                    0        6838           0         6838 
procedure cache percent                 20        4726          40           40

The top command shows sybase as using 33M of memory (26M) resident. As you can see, I already have the cache configure to 40% and am loathe to raise it any more - it probably shouldn't be that high anyway.

I could increase total memory, but Sybase is already grabbing 30Mb and there really isn't any free memory anyway on the server as it is (top reports between 3 and 4Mb free).

Do I have any configuration options, or should I just stick more memory into the server and give it to Sybase?

Greg.

 

[JMCraig]

The bad news is, to handle GROUP BY's on tables of that size, you need more RAM. I don't think there's really any other way around it. I'm guessing there must be some other apps running on the server since Solaris wouldn't normally use up that much RAM. Any possibility of moving some of them (probably less costly to buy more RAM).

BOL,

J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[grega]

Here's the output from top -s size 100. Looks like most RAM is being used, but only a small percentage is of the processes are resident .. they're all sleeping. This is a development only box. Could I just tell Sybase to grab more memory, say 50Mb, and see what happens?

Greg.

43 processes:  42 sleeping, 1 on cpu
CPU states: 99.6% idle,  0.0% user,  0.4% kernel,  0.0% iowait,  0.0% swap
Memory: 256M real, 3944K free, 80M swap in use, 420M swap free

  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
  224 root       1  33    0   58M  792K sleep   9:48  0.02% lp
  392 sybase     2  33    0   33M   27M sleep  22.8H  0.05% dataserver
 5839 root       1  34    0   10M 2344K sleep   0:01  0.00% Xsun
  397 sybase     1  33    0 8248K  480K sleep   1:15  0.00% backupserver
 5854 root       4  34    0 6400K 1432K sleep   0:01  0.00% dtgreet
 5840 root       5  15    0 6080K  896K sleep   0:00  0.00% dtlogin
  316 root       5  29    0 6016K 1008K sleep   0:00  0.00% dtlogin
  186 root       5  33    0 3992K 1256K sleep   0:01  0.00% automountd
  324 root       5  33    0 3312K  960K sleep   0:00  0.00% snmpXdmid
  190 root      11  34    0 3136K 1304K sleep   2:02  0.00% syslogd
21425 root       1  29    0 2872K  840K sleep   0:01  0.00% rpc.ttdbserverd
  215 root       1  34    0 2720K  224K sleep   0:02  0.00% lpsched
  323 root       6  33    0 2680K  888K sleep   0:00  0.00% dmispd
 1756 root       9  30    0 2416K 1520K sleep   0:05  0.00% nscd
  303 root       4  24    0 2408K  968K sleep   0:00  0.00% mountd
  269 root       6  33    0 2352K  968K sleep   0:01  0.00% vold
  169 daemon     4   7    0 2184K  976K sleep   0:00  0.00% statd
  261 root       1  23    0 2104K  392K sleep   0:06  0.00% sendmail
  137 root       1  33    0 2048K  800K sleep   0:00  0.00% rpcbind
  139 root       4  15    0 2016K  880K sleep   0:00  0.00% keyserv
  164 root       1  23    0 2008K  368K sleep   0:01  0.00% inetd
 5842 root       1  34    0 2000K  904K sleep   0:00  0.00% fbconsole
  311 root       1  33    0 1880K  288K sleep   0:00  0.00% snmpdx
  239 root       1  34    0 1768K  472K sleep   0:00  0.00% suntechd
  306 root       1  18    0 1736K    0K sleep   0:00  0.00% nfsd
  171 root       1  28    0 1736K    0K sleep   0:00  0.00% lockd
  238 root       1  34    0 1680K  568K sleep   0:06  0.00% lmgrd.ste
12226 garchiba   1  33    0 1656K 1216K sleep   0:00  0.03% ksh
12140 garchiba   1  33    0 1656K 1168K sleep   0:00  0.00% ksh
  335 root       1  33    0 1640K  848K sleep   0:00  0.00% mibiisa
  199 root       1  23    0 1632K  440K sleep   0:33  0.00% cron
12224 root       1  33    0 1584K 1280K sleep   0:00  0.00% in.telnetd
12138 root       1  23    0 1584K 1192K sleep   0:00  0.00% in.telnetd
  334 root       1  33    0 1584K  264K sleep   0:00  0.00% ttymon
27105 root       1  13    0 1576K    0K sleep   0:00  0.00% ttymon
  330 root       1  34    0 1520K  176K sleep   0:00  0.00% sac
  333 root       1  33    0 1448K  136K sleep   0:00  0.00% listen
    1 root       1  34    0 1352K  160K sleep   0:14  0.00% init
12284 garchiba   1  33    0 1224K 1024K cpu     0:00  0.18% top
  242 root       4  33    0 1208K  664K sleep   0:00  0.00% powerd
  396 sybase     1  23    0  928K    0K sleep   0:00  0.00% RUN_charcomm1_b
  391 sybase     1  23    0  928K    0K sleep   0:00  0.00% RUN_charcomm1
  252 root       1  34    0  920K  688K sleep   0:00  0.00% utmpd

 

[JMCraig]

Sure, tell Sybase to grab a bunch more RAM and see if things start swapping badly--that's the sign you've really hit the limit.

HTH, J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net