[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

database is slow

Posted on 2004-11-30
29
Medium Priority
?
6,106 Views
Last Modified: 2012-05-05
Hello ,

I a have a sybase database where the trade browser runs ad hoc queries.
It is slow at time, it has been going steady fro 2 weeks now but it becomes slow and one user or the user has to stop or kill their process.
I need help in trouble shooting  the entire process as what steps I need to take and what to run ;
This is whay my configuration looks like:
**************************************************************
Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
allow remote access                      1           0           1            1
print recovery information               0           0           0            0
recovery interval in minutes             5           0           5            5
tape retention in days                   0           0           0            0

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
memory alignment boundary             2048           0        2048         2048
number of index trips                    0           0           0            0
number of oam trips                      0           0           0            0
procedure cache percent                 20      209390          20           20
total data cache size                    0      807368           0       807368
total memory                          7500     1048576      524288       524288

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
allow sql server async i/o               1           0           1            1
disk i/o structures                    256          19         256          256
number of devices                       10          #4          10           10
page utilization percent                95           0          95           95

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
configuration file                       0           0           0  /home/sybas

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
default character set id                 1           0           1            1
default language id                      0           0           0            0
default sortorder id                    50           0          50           50
disable character set conversi           0           0           0            0
number of languages in cache             3           4           3            3

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
address lock spinlock ratio            100           0         100          100
deadlock checking period               500           0         500          500
freelock transfer block size            30           0          30           30
max engine freelocks                    10           0          10           10
number of locks                       5000        4688       50000        50000
page lock spinlock ratio               100           0         100          100
table lock spinlock ratio               20           0          20           20

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
additional network memory                0           0           0            0
audit queue size                       100          42         100          100
default network packet size            512        #473         512          512
disk i/o structures                    256          19         256          256
event buffers per engine               100         #30         100          100
executable codesize + overhead           0        5152           0         5152
max number network listeners            15        1126          15           15
max online engines                       1         438           3            3
number of alarms                        40           1          40           40
number of devices                       10          #4          10           10
number of extent i/o buffers             0           0           0            0
number of languages in cache             3           4           3            3
number of locks                       5000        4688       50000        50000
number of mailboxes                     30           1          30           30
number of messages                      64           1          64           64
number of open databases                12         396          12           12
number of open objects                 500         489         500          500
number of remote connections            20          33          20           20
number of remote logins                 20          22          20           20
number of remote sites                  10         750          10           10
number of user connections              25       18704         250          250
partition groups                      1024          21        1024         1024
permission cache entries                15        #134          15           15
procedure cache percent                 20      209390          20           20
remote server pre-read packets           3         #32           3            3
stack guard size                      4096       #1148        4096         4096
stack size                           34816       #9759       34816        34816
total data cache size                    0      807368           0       807368
total memory                          7500     1048576      524288       524288

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
additional network memory                0           0           0            0
allow remote access                      1           0           1            1
allow sendmsg                            0           0           0            0
default network packet size            512        #473         512          512
max network packet size                512           0         512          512
max number network listeners            15        1126          15           15
number of remote connections            20          33          20           20
number of remote logins                 20          22          20           20
number of remote sites                  10         750          10           10
remote server pre-read packets           3         #32           3            3
syb_sendmsg port number                  0           0           0            0
tcp no delay                             0           0           0            0

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
max async i/os per engine       2147483647           0  2147483647   2147483647
max async i/os per server       2147483647           0  2147483647   2147483647
o/s asynch i/o enabled                   0           0           0            0
o/s file descriptors                     0           0           0         4096
tcp no delay                             0           0           0            0

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
additional network memory                0           0           0            0
lock shared memory                       0           0           0            0
shared memory starting address           0           0           0            0
total memory                          7500     1048576      524288       524288

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
max online engines                       1         438           3            3
min online engines                       1           0           1            1

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
allow nested triggers                    1           0           1            1
allow updates to system tables           0           0           0            0
audit queue size                       100          42         100          100
cpu accounting flush interval          200           0         200          200
cpu grace time                         500           0         500          500
deadlock retries                         5           0           5            5
default database size                    2           0           2            2
default fill factor percent              0           0           0            0
event buffers per engine               100         #30         100          100
housekeeper free write percent           1           0           1            1
i/o accounting flush interval         1000           0        1000         1000
i/o polling process count               10           0          10           10
identity burning set factor           5000           0        5000         5000
identity grab size                       1           0           1            1
lock promotion HWM                     200           0         200          200
lock promotion LWM                     200           0         200          200
lock promotion PCT                     100           0         100          100
number of alarms                        40           1          40           40
number of extent i/o buffers             0           0           0            0
number of mailboxes                     30           1          30           30
number of messages                      64           1          64           64
number of open databases                12         396          12           12
number of open objects                 500         489         500          500
number of pre-allocated extent           2           0           2            2
number of sort buffers                   0           0           0            0
partition groups                      1024          21        1024         1024
partition spinlock ratio                10           0          10           10
print deadlock information               0           0           0            0
runnable process search count         2000           0        2000         2000
size of auto identity column            10           0          10           10
sort page count                          0           0           0            0
sql server clock tick length        100000           0      100000       100000
time slice                             100           0         100          100
upgrade version                       1100           0        1102         1102

Parameter Name                 Default     Memory Used Config Value Run Value  
--------------                 -------     ----------- ------------ ---------  
default network packet size            512        #473         512          512
number of pre-allocated extent           2           0           2            2
number of user connections              25       18704         250          250
permission cache entries                15        #134          15           15
stack guard size                      4096       #1148        4096         4096
stack size                           34816       #9759       34816        34816
systemwide password expiration           0           0           0            0
user log cache size                   2048           0        2048         2048
user log cache spinlock ratio           20           0          20           20

*******************************************************

thanks
Vinai
0
Comment
Question by:vijadon
  • 12
  • 12
  • 5
29 Comments
 
LVL 14

Accepted Solution

by:
Jan Franek earned 450 total points
ID: 12708489
I would run sp_sysmon and try to look for something suspicios in the output - high CPU usage, data cache misses, I/O device contention etc.
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12713750
I usually look at following sections of sp_sysmon output:
Kernel Utilization
Task Management
Transaction Profile
Transaction Management
Metadata Cache Management
Lock Management
Data Cache Management
Disk I/O Management

But as I see, you are on quite old version of ASE, so I'm not sure, if your sp_sysmon output has all these sections.
0
 
LVL 6

Assisted Solution

by:ChrisKing
ChrisKing earned 1050 total points
ID: 12714389
vijadon

first I would like you to answer (as many as you can):
- what O/S and version are you running ?
- what version of ASE are you running ?
- how much memory does the server have ?
- how much memory is the O/S set for (max memory) ?
- how much memory is allowed to be used by ASE ?
- how many CPU's does the server have ?
- what type of disks are you running local (PATA,SATA,SCSI) or disk array ?
- are you using RAID and if yes, then what type ?
- is the database created on RAW or file system devices ? (if file system, then which type)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 6

Expert Comment

by:ChrisKing
ID: 12714437
sample commands required:
===================

- what O/S and version are you running ?
-------------------------------------------------
-bash-2.05b$ uname -a
Linux jackhammer 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux

- what version of ASE are you running ?
-------------------------------------------------
-bash-2.05b$ isql -Sjackhammer -Usa
Password:
1> select @@version
2> go
 -----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
 Adaptive Server Enterprise/12.5.2/EBF 11799/P/Linux Intel/Enterprise Linux/ase1
         252/1831/32-bit/OPT/Fri Apr  9 02:53:50 2004
(1 row affected)

- how much memory is the O/S set for (max memory) ?
-------------------------------------------------
-bash-2.05b$ grep kernel.shmmax /etc/sysctl.conf
kernel.shmmax = 2000000000
0
 
LVL 1

Author Comment

by:vijadon
ID: 12716084
Working on these , will get you to soon.
The database is not slow all the time. It has been fine for 2 weeks now, but  it can get slow any time now. I am thinking it amy be the ad hoc queries ,but I need to get them caught and resolved.
If for instance the database is slow now how can I capture the sql and what are the other queries I can run to see and resolve it.

thank you
Vinai
0
 
LVL 1

Author Comment

by:vijadon
ID: 12716449
Version:
SQL Server/11.0.2/P/Sun_svr4/OS 5.4/1/OPT/Tue Sep 10 14:03:23 PDT 1996

DATABASE:
SunOS sybserv-drf2 5.6 Generic_105181-26 sun4u sparc SUNW,Ultra-80

Server Memory: 4GB

Memory in the O/S set for (max memory) -- VM Subsystem has 4GB RAM + 4GB Swap

Memory allowed to be used by ASE: 1.3GB per shared mem seg

Number of CPU: 4 450 Mhz

Type os disk using: JBOD

RAID: N/A, volumes are concat or on single spindles

The database is not slow all the time. It has been fine for 2 weeks now, but  it can get slow any time now. I am thinking it amy be the ad hoc queries ,but I need to get them caught and resolved.
If for instance the database is slow now how can I capture the sql and what are the other queries I can run to see and resolve it.

thank you
Vinai
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12716498
On newer versions of ASE you can capture SQL of currently running queries, but it seems to me, that you are on quite ancient version 11.0, so I'm afraid, that you will not be able to get what you want (unless you have installed auditing, in that case you should find all the SQL in your audit log).
0
 
LVL 1

Author Comment

by:vijadon
ID: 12720020
I know it is a older version but still there has to be some way to capture the sql.
I already put the information for chrisking , waiting for his reply.

thank you
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12720865
some quick things for you to consider:

> Memory allowed to be used by ASE: 1.3GB per shared mem seg
> total memory                          7500     1048576      524288       524288
you are only configured for 1GB
and then you may want to reduce the % to keep the memory used the same
> procedure cache percent                 20      209390          20           20

> Number of CPU: 4 450 Mhz
> max online engines                       1         438           3            3
> min online engines                       1           0           1            1
you are configured to start with 1 cpu, that will use up to 3. Consider setting both to 4.

have you evaluated the show plan of the queries which are needing to be killed ?

if you killing this users process when it happens, you can run:
select * from master..sysprocesses where spid = <spid>    -- to see the physical i/o
exec sp_showplan <spid>, null, null, null                            -- to see thei query plan
before you kill his connection, and ensure that the query is not a "server killer"

0
 
LVL 1

Author Comment

by:vijadon
ID: 12728524
Hello,

How would I change the number of cpu used from 1 to 3.
Also when I run the exec sp_showplan it gives me the message:
Server Message:  Number  2812, Severity  16
Line 1:
Stored procedure 'sp_showplan' not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

It does not produce anything.

How would I reduce the memory to be the same.

thank you for your help.

Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12733688
> How would I change the number of cpu used from 1 to 3.
exec sp_configure 'min online engines', 3
and then restart the server

> Stored procedure 'sp_showplan' not found.
sorry, looks like version 11.0 is too old for that command, I am pretty sure is was added in 11.9.x (but might have been 11.5)

> How would I reduce the memory to be the same.
On the assumption you increased the "total memory" from 1GB to 1.3GB ...
You had "procedure cache percent" set to 20% (being 200MB), the 15.38% of 1.3GB would also be 200MB
so you would need to set it to 16.

Of course you should be following Jan_Franek's advise and review the sp_sysmon output. It **MIGHT** tell you that you only are using 40MB of procedure cache and that you should be reducing it.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12733695
... and the are lots of other googies in sp_sysmon too (see Jan_Franek's comments above)
0
 
LVL 1

Author Comment

by:vijadon
ID: 12735583
Currently there have been no delays for the past 2 weeks.
Also ,the hanging is caused by either inserts or selects , is it possible.

I will also try to run sp_sysmon , but i went to read the document it is huge. It has lot os stuff and I did not know what to run.

thank you for your help

vinai
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12736316
Just run sp_sysmon "00:10:00" - this will collect data for 10 minutes and then you get output.

I recommend running it now, when everything works fine and then again, when the server is slow. You can probably find some hints about your problem by comparing these two outputs/
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12736640
> Also ,the hanging is caused by either inserts or selects , is it possible.
it might just be blocked at the time, try and get an sp_who output next time it happens
0
 
LVL 1

Author Comment

by:vijadon
ID: 12738968
It shows a bunch of users running select statements like:
SELECT  DISTINCT( T.TXT_VAL_02 ) AS ISOCONTRACTID FROM TRADE T,
         XP X WHERE T.TRADE_TYPE_NUM = 40070  AND T.TXT_VAL_03 = 'RT' AND T.TXT_VAL_01 = 'PUR' AND T.START_DT_GMT <= '10-Nov-04' AND T.END_DT_GMT > '10-Nov-04' AND T.INACTIVE_DT_SYS > '
10-Nov-04' AND X.TXT_VAL_01 = 'AEP-DAYTON HUB' AND X.INACTIVE_DT_SYS > '10-Nov-04' AND T.NUM_VAL_01 = X.XP_ID AND T.TXT_VAL_06 = 'EXGNCE' AND T.TXT_VAL_07 = 'CPSI'


and


SELECT DISTINCT index_map..match_info.match_id
FROM index_map..match_info, index_map..match_detail_info
WHERE index_map..match_info.match_id = index_map..match_detail_info.match_id
  AND index_map..match_info.active   = 1
  AND index_map..match_detail_info.end_date >= convert( datetime, '20050101')
  AND index_map..match_detail_info.start_date  < convert( datetime, '20050102')
  AND index_map..match_detail_info.asset = 'COMMOD PWR PJM PHYSICAL'
  AND index_map..match_detail_info.delivery_point = 'NIHUB'

I really need help,

thanks
Vinai
0
 
LVL 1

Author Comment

by:vijadon
ID: 12745124
Should the 'min online engine ' be set to 1 or 4, and changing  the max online engine to 4 pose any problem ,I hope it does not blow away my system.
Currentlu the min is set to 1 and max online engine is set to 3 ,so it should be using the 3 cpu. Correct me if i AM WRONG.

Also how would I change the configuration of Sql server so it uses memory of 1.3 gb.

thank you
Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12763525
> Should the 'min online engine ' be set to 1 or 4,
that is really a matter for you to decide, what is the minimum number of CPU's you want to Sybase to use.

> and changing  the max online engine to 4 pose any problem
only if the server is predominately used for another purpose

> I hope it does not blow away my system.
very unlikely  :)

> Currentlu the min is set to 1 and max online engine is set to 3
correct

> so it should be using the 3 cpu. Correct me if i AM WRONG.
it will only use 1 CPU, and then after the server reaches a certain point of load (not too sure here) then it brings the extra CPU's online

note if your server is mostly use as a Sybase server, and the server has 4 CPU's, then most people would configure both min and max engines to 4 (but this is really up to you). The OS will still respond nicely will one engine per CPU.
0
 
LVL 1

Author Comment

by:vijadon
ID: 12765663
I want to be sure, I had called Sybase ,their intervention is to use 1 as min online engine and 3 as max online online engine.
They did not recommend changing that parameter , because we also have other databases on the server and their performance is not slow. The other database is 2 gigs and this one is 10 gigs ,though they are both small to us byt very critical ones.
I can safely change both the min and max online engine to 4 .
My server is not crashed. RIGHT.

Also how would I change the configuration of Sql server so it uses memory of 1.3 gb.


thank you for your help.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12769583
> I can safely change both the min and max online engine to 4 .
yes, but if Sybase told you not to, there will be a good reason, make sure you understand why

> Also how would I change the configuration of Sql server so it uses memory of 1.3 gb.
- 1.3GB = 1.3 * 1024 * 1024 * 1024 = 1395864371 bytes
- number of pages = 1395864371 / 2048 (you use a 2K pages size) = 681574 pages
so execute the command:
    exec sp_configure 'total memory', 681574
0
 
LVL 1

Author Comment

by:vijadon
ID: 12770292

Sybase said that always leave 1 cpu for OS, AND 3 FOR Sybase.
Did not give more reasoning, said for performance and tuning Sybase professionals can come to the site to look at our servers and there is a fee for it.

That is why if I change it, I just wanted to be sure for it.
Thank you for helping at this time.
The database was very slow today, the queries were running very slowly.
We had to kill the one process that was blocking others.
thank you ,will try now.

thanks
VSJ
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12770338
> Sybase said that always leave 1 cpu for OS, AND 3 FOR Sybase.
I am shocked by thate statement

> performance and tuning Sybase professionals can come to the site to look at our servers and there is a fee for it.
that is worthwhile, but only if they discuss the reasoning (with you) for every change they want make before they do it.
0
 
LVL 1

Author Comment

by:vijadon
ID: 12770540
I will schedule the change next week and see how it goes.
I do have a question .
I have another user database on the server , though it is small ,it does not show any performance problems or any hanging.
i really do not care what they say ,just want my system fixed.

thanks
Vsj
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12770616
until then, run sone sp_sysmon's (as per Jan_Franek suggested)
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12783463
any progress ?
0
 
LVL 1

Author Comment

by:vijadon
ID: 12886380
I did change the min online engine to 4 ,and increased total memory used to be 12.3 gbs.
Still i see no difference ,the database is still slow.


thanks
Vinai
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12888051
well since you have persued this performance in another thread and seem to have abandoned this one. Do you want to close it
0
 
LVL 1

Author Comment

by:vijadon
ID: 12888464
I have already given the point, I guess I will close it.
But sadly.

thanks
Vinai
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12890696
Did you run that sp_sysmon that I suggested in the very first post ?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question