Size of a Field
- Thread starter Subhransu
- Start date
RealHeavyDude
Well-Known Member
Given that less information it is hard to say anything intelligent.
One would need much more information, for example:
Heavy Regards, RealHeavyDude.
One would need much more information, for example:
- How does the table look like?
- What are exactly your performance problems?
- How did you diagnose your performance problems?
- Furthermore the layout and setup of the database may be crucial
Heavy Regards, RealHeavyDude.
Hi,
It is a standard table which stores the user comments in form of free text. The size of the table has recently exceeded 100 GB.
Users reported slow response time while veiwing the comments in a browse ( not always but some times). There seems no issues from DB, Server as well as network side. People say it is because of the size of the field (free form user text) which is causing the issue. I am not buying this hypothesis. This table is stored in a different disk because of its size.
Please let me know if I can do something to establish that the above hypothesis is wrong. Also if I can detremine the total space taken by the field with respect to the size of the table?
Thanks.
Subhransu
It is a standard table which stores the user comments in form of free text. The size of the table has recently exceeded 100 GB.
Users reported slow response time while veiwing the comments in a browse ( not always but some times). There seems no issues from DB, Server as well as network side. People say it is because of the size of the field (free form user text) which is causing the issue. I am not buying this hypothesis. This table is stored in a different disk because of its size.
Please let me know if I can do something to establish that the above hypothesis is wrong. Also if I can detremine the total space taken by the field with respect to the size of the table?
Thanks.
Subhransu
RealHeavyDude
Well-Known Member
How big can the free text get? If it gets really big chances are the you record in the database is split across more than one block - which will have a performance impact.
Imagine that the max. record size can be 32000 bytes and your max block size can be 8K ...
But still this is just speculation. I have never seen any standard table in all of my life ...
Heavy Regards, RealHeavyDude.
Imagine that the max. record size can be 32000 bytes and your max block size can be 8K ...
But still this is just speculation. I have never seen any standard table in all of my life ...
Heavy Regards, RealHeavyDude.
TomBascom
Curmudgeon
Table and field size, per se, do not have a significant impact on performance.
Query structure is a much more likely candidate. Poor use of indexing or a lack of filtering are your most likely culprits. If this is a client/server environment (and it sounds like it is) then you should be using "field lists" to only return the fields needed for the browse - I doubt that you are displaying the notes in the browse so you should only fetch that field once you actually need it.
Things like record fragmentation and scatter could be having an impact but that would be unlikely to be noticeable to users unless you are returning an unfeasibly large set of records to the client.
Query structure is a much more likely candidate. Poor use of indexing or a lack of filtering are your most likely culprits. If this is a client/server environment (and it sounds like it is) then you should be using "field lists" to only return the fields needed for the browse - I doubt that you are displaying the notes in the browse so you should only fetch that field once you actually need it.
Things like record fragmentation and scatter could be having an impact but that would be unlikely to be noticeable to users unless you are returning an unfeasibly large set of records to the client.
TomBascom
Curmudgeon
Thirdly... The network and db guys may not see a problem in their areas but that doesn't means that there isn't one.
On the db front I would be concerned because you say this table has been put on it's own disk due to size. That suggests to me that the db administrator does not have a very sophisticated understanding of performance issues. It also suggests some unfavorable things about your system and storage administration functions.
I've no idea what the network people are basing their opinion on but, without data my confidence is low.
Are you using the -Mm parameter? (it must be set for both the server and all clients...)
On the db front I would be concerned because you say this table has been put on it's own disk due to size. That suggests to me that the db administrator does not have a very sophisticated understanding of performance issues. It also suggests some unfavorable things about your system and storage administration functions.
I've no idea what the network people are basing their opinion on but, without data my confidence is low.
Are you using the -Mm parameter? (it must be set for both the server and all clients...)
TomBascom
Curmudgeon
The simple test is to ask for numbers.
If your DBA can produce metrics related to database performance during problem periods then there is a chance that he is right.
If, on the other hand, he can only offer assertions that there are no database problems then he is almost certainly wrong.
If he does have numbers then you need to consider what those numbers actually are and whether or not they are relevant. To be relevant they need to be consistently measured and they need to be fine enough grained that they cover your trouble period. For instance, it is not sufficient to say "the hit ratio is 98%". You need to know over what period that statement is true -- are we talking about "since the database was started" (which is what PROMON will show by default) or over the last 24 hours (not a very useful period), or is that "yesterday between 10:50 and 10:55 when users were complaining about poor performance". This last statement is useful and relevant.
If metrics are available and relevant they still might not be the right metrics and it is still possible that they are being misinterpreted. For instance, the example above refers to "hit ratio". This is a very popular metric to watch but it isn't very helpful and there is a lot of even more unhelpful folklore surrounding it. You can, for instance, find references in the Progress documentation and in the knowledge center that would seem to state that so long as the value is higher than 95% it is "good". (Under most circumstances 95% is actually really quite bad. 98% is, IMHO, "barely acceptable".) You have to understand what a metric is and what it is telling you and then decide if it is useful -- "hit ratio" for example is telling you how often a database reference is satisfied from RAM vs having to read from disk. Disk IO is thousands of times slower than memory access so a very small number of disk IO ops will have a very perceptible impact on performance. Thus the desire for hit ratios with lots of 9s in them. But if your workload is very light (maybe you're only doing a few hundred db references per second) does it really matter? No it doesn't...
In your particular situation you probably want to start with a breakdown of what happens when your problem query is run. What tables are being accessed, using which indexes and how much IO does this generate? To do that you should start with table and index statistics. If you are on 10.1C or higher you can get those on a per user basis, otherwise you have to settle for aggregate data across all users (to get data for a specific user and a specific instance of a query you could arrange to be the only user on a test database...) In order to actually capture that data you have to have enabled its collection -- this means that the DBA needs to have the -tablerangesize and -indexrangesize startup parameters set large enough to include all of your tables and indexes. (If these parameters are not set they default to the first 50 tables and indexes. And if they are not set it also is a hint that your DBA isn't performance savvy.) You could then write queries against the VSTs to collect the data or you could use a tool like ProTop to help you analyze the situation.
Or you could save a lot of time and agony and hire a consultant to help you out. See signature line for details
If your DBA can produce metrics related to database performance during problem periods then there is a chance that he is right.
If, on the other hand, he can only offer assertions that there are no database problems then he is almost certainly wrong.
If he does have numbers then you need to consider what those numbers actually are and whether or not they are relevant. To be relevant they need to be consistently measured and they need to be fine enough grained that they cover your trouble period. For instance, it is not sufficient to say "the hit ratio is 98%". You need to know over what period that statement is true -- are we talking about "since the database was started" (which is what PROMON will show by default) or over the last 24 hours (not a very useful period), or is that "yesterday between 10:50 and 10:55 when users were complaining about poor performance". This last statement is useful and relevant.
If metrics are available and relevant they still might not be the right metrics and it is still possible that they are being misinterpreted. For instance, the example above refers to "hit ratio". This is a very popular metric to watch but it isn't very helpful and there is a lot of even more unhelpful folklore surrounding it. You can, for instance, find references in the Progress documentation and in the knowledge center that would seem to state that so long as the value is higher than 95% it is "good". (Under most circumstances 95% is actually really quite bad. 98% is, IMHO, "barely acceptable".) You have to understand what a metric is and what it is telling you and then decide if it is useful -- "hit ratio" for example is telling you how often a database reference is satisfied from RAM vs having to read from disk. Disk IO is thousands of times slower than memory access so a very small number of disk IO ops will have a very perceptible impact on performance. Thus the desire for hit ratios with lots of 9s in them. But if your workload is very light (maybe you're only doing a few hundred db references per second) does it really matter? No it doesn't...
In your particular situation you probably want to start with a breakdown of what happens when your problem query is run. What tables are being accessed, using which indexes and how much IO does this generate? To do that you should start with table and index statistics. If you are on 10.1C or higher you can get those on a per user basis, otherwise you have to settle for aggregate data across all users (to get data for a specific user and a specific instance of a query you could arrange to be the only user on a test database...) In order to actually capture that data you have to have enabled its collection -- this means that the DBA needs to have the -tablerangesize and -indexrangesize startup parameters set large enough to include all of your tables and indexes. (If these parameters are not set they default to the first 50 tables and indexes. And if they are not set it also is a hint that your DBA isn't performance savvy.) You could then write queries against the VSTs to collect the data or you could use a tool like ProTop to help you analyze the situation.
Or you could save a lot of time and agony and hire a consultant to help you out. See signature line for details
Similar threads
- Locked
- Locked