Sybase 10.x / AIX 4.3 Performance (Caching ??, IO ?? Setup??)

We are running a 4 Processor SMP server - a IBM F50 (4x166MHz IBM RS6000 processors) with 1GB ram, SSA Drives, etc.

I am however amazed by the poor performance of the system in general and seriously suspect a setup problem somewhere. I am looking for any suggestions of what to do to improve our performance.

The database is fairly large (20 GB). Running iostat/monitor/etc indicates the following stats when a client application performs a number of single-row selects on multiple connections in parallel:

Average transactions/second   : ~100
CPU:  
    SYS 0.5%   USR 2.5% WAIT 89%  IDLE 8%
Disk IO:
    Total throughput from all disks 900 k/s

Mem:
REAL :    Free 0.5 Mb  Files 600MB Used 423.5MB
VIRTUAL: Free 0MB  Used 1024MB


I know this is very broad but any pointers will be usefull. I am more of a hardware expert than a database or AIX one and thus the problem is pretty complicated to me as I do not know the inner workings of Sybase.
LVL 2
gysbert1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

david_levineCommented:
How much memory is allocated to Sybase?

From the above that you posted, what indicates to you poor performance?
0
oavidovCommented:
gysbert1,
An obvious weakness point is spouting out from your details.
A virtual memory does not exist and sybase such as any relational data base needs for transactions.
I afford myself to presume that wide memory will solve your problem.
Yours
Ofer Avi-dov
0
gysbert1Author Commented:
david levine,

I expected the performance to be better than 100 transactions per second. The fact that the machine is 90% waiting for I/O seems to be part of the reason. I realize that increasing the memory would improve on the number of cache hits and improve performance but I assumed that 1GB of memory would be plenty (5% of the database size)


oavidov,

Could you explain a bit as I cannot full understand what you mean? Virtual memory does very much exist! It only lives on disk in stead of DRAM and is thus about 200 times slower than DRAM. What do you mean by wide memory?

I am not sure how the soft and hard limits to memory usage can be set in Sybase (I am no DBA) but as far as I can find out it seems that about 600MB is allocated to Sybase for caching data.

The operating system is however also doing some caching of its own and that is why it is using 600MB for file caching.

I suspect that reducing the paging file will increase performance as I suspect that the sybase cache is swapped out of memory by the operating system.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

david_levineCommented:
You have 1GB RAM on the machine. How much is allocated to Sybase? It's a database server configuration parameter.

Not sure what front-end you have to view the configuration of Sybase, so let me know what you have and I'll try to walk you through getting to "Total Memory" config value.

I'm also not sure if the wait time might include communications with the client. You might want to run some tests local on the box to determine if the bottleneck is network related.

David
0
gysbert1Author Commented:
I am testing local on the box !
0
david_levineCommented:
Depending on the query you are running, it might be doing a table scan. Is that possible? A table scan means it has to read all the rows in the table before return the results.

Is it just one query you are runnning over and over? You haven't given any details on what the application/queries are.
0
oavidovCommented:
gysbert1,
Mr David Levine ask you an obious question.and  cause you are not DBA
I will try to point at one more reason.
If our assumption is that you have enough memory for sybase, another reason could be that files  are not normalized as well.
For example
If your database contains a calculated fields instead of clumsy fields. You may pay in an I/O extraction time.
Calculated fields will calculate at the application not at the database.(one and basicly normalizing rule.
Please check these points with your DBA.
I wish you sccess solve it and glade to assist you shortly.
Yours
Ofer Avi-dov
0
gysbert1Author Commented:
oavidov,

I would still like an explanation of your answer above. What do you mean by wide memory and what is the obvious problem you refer to?

david,

The query is properly optimised. It is not doing any table scans (we did check out the query plan on all the queries). Proper indexes exist for all the keys I extract by. (I am hitting by primary key and the index was updated recently). The table I am hitting has aroud 20 Million rows and a table scan would take seconds and not milliseconds.

I still believe that the problem is not purely database related but a technicality in the setup. I think that sybase might be spreading the data over the disk expecting AIX to spread the data over multiple disks while AIX is not set up to do this (that we are changing by the way). Then I suspect that AIX is reading 64kb blocks into its own cache (which gets swapped out to disk due to the lack of memory) If the data is spread over the disk in say 5 stripes it would cause a 64kx5 = 320k read for a single row retrieval query!

As the disks only average 500kb/s (and they should be able to sustain around 2-3Mb/s) I assume that the heads are moving back and forth quite a bit.

If I knew more about the parameters you can tweak on Sybase it would give ma an idea of what optimization is possible to counter this specific problem.

According to me the machine should be around 60% in iowait and not 90%. If it is 90% either table scans are happening as you pointed out (which is not the case) or something else is wrong and in need of optimisation...
0
david_levineCommented:
I'm running ASE 11.9.2 on a S70. One of the recommendations from a super technical ex-Sybase employee was:

General recommendations:
tempdb on File System.
Use vmtune to set -p 5 and -P 10 to keep JFS from paging against ASE.
Use ASE 11.9.2 ESD #6 (install 11.9.2/P and then add ESD #6).  ESD #6 due out in
 a week or so.
Use RAID 0+1 for best disk I/O spread and fault tolerance.
Try to implement dbcc tune(doneinproc,0).  It will lower network writes from
stored procedures.
Try to implement cpu affinity with dbcc tune(cpuaffinity, 0, 'on') upon ASE
startup (optional, you're not even close to needing this, really).
sp_configure 'tcp no delay', 1


I'm not a Unix dude so this doesn't mean a lot to me. Maybe it means more to you.

David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gysbert1Author Commented:
David,

This is more in the line of what I was looking for.

We were just looking at vmtune. This paging conflict between the AIX file system (I think it is journalling?) and Sybase is my number 1 suspect!

You do not happen to have contact details for this guy do you? (hint-hint, nudge-nudge ...) He might just make a quick buck out of it and with the kickback ...

If you have any more info or recomentdations it will be greatly appreciated!
0
david_levineCommented:
I'll send you his email address if you send me yours. I don't want to post his address here. My email is david_levine@opcenter.net

Just remind me in the email what you're looking for. He's the sharpest Sybase person I've ever met!

David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.