gysbert1
asked on
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.
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.
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
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
ASKER
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.
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.
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
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
ASKER
I am testing local on the box !
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.
Is it just one query you are runnning over and over? You haven't given any details on what the application/queries are.
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
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
ASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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
Just remind me in the email what you're looking for. He's the sharpest Sybase person I've ever met!
David
From the above that you posted, what indicates to you poor performance?