|
Home > Archive > SQL Anywhere database > May 2005 > Database Performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Database Performance
|
|
| massimomm 2005-05-24, 7:23 am |
| Hi !!
From some days, my network database have lower performance
and the system processors works around the 80-90 %, the main
database file is upper 3Gb, is there any storage limit ? I
use Sql anyware studio 7 Ebf 3532
Thanks in advance
Massimo
| |
| Greg Fenton 2005-05-24, 9:23 am |
| massimomm wrote:
> From some days, my network database have lower performance
> and the system processors works around the 80-90 %, the main
> database file is upper 3Gb, is there any storage limit ?
Which operating system and version/service pack are you running at?
The limitations are described in the ASA 7.x online docs:
ASA Adaptive Server Anywhere Reference Manual
CHAPTER 13. Physical Limitations
- Size and number limitations
However, the size of the file will not have a direct impact on
performance. What will have is the size of the working set of data
(i.e. how much data is being processed/queried at any one time) as well
as the particular queries themselves (how complex?).
You say that the CPU is sitting at 80-90%. Are the hard drives spinning
wildly as well? Is the OS swapping out virtual memory? Is the physical
memory on that server maxed out?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| massimomm 2005-05-25, 7:23 am |
| Hi Greg,
thanks for your answer,
My network database server run on Win2003 Server, the
version of Sql anywhere studio is 7 patched to EBF 3532.
The Os don't use swapping memory, the strange thing is that,
i see that the database file .db and .log are written each
minute, but the size not always change !
Do you thing is a symptom of Database malfunctioning, and i
've to really worry up ?
> massimomm wrote:
> any storage limit ?
>
> Which operating system and version/service pack are you
> running at?
>
> The limitations are described in the ASA 7.x online docs:
>
> ASA Adaptive Server Anywhere Reference Manual
> CHAPTER 13. Physical Limitations
> - Size and number limitations
>
> However, the size of the file will not have a direct
> impact on performance. What will have is the size of the
> working set of data (i.e. how much data is being
> processed/queried at any one time) as well as the
> particular queries themselves (how complex?).
>
> You say that the CPU is sitting at 80-90%. Are the hard
> drives spinning wildly as well? Is the OS swapping out
> virtual memory? Is the physical memory on that server
> maxed out?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Greg Fenton 2005-05-26, 8:23 pm |
| massimomm wrote:
> The Os don't use swapping memory,
How do you mean? That the swap is not being used or that you have
somehow disabled it?
> the strange thing is that,
> i see that the database file .db and .log are written each
> minute, but the size not always change !
>
> Do you thing is a symptom of Database malfunctioning, and i
> 've to really worry up ?
No, the file timestamp updating frequently is an indication that there
are frequent checkpoints going on. This is likely NOT a symptom of
database malfunction, though it *might* indicate application problems.
What you need to do is determine what it is the database itself is
actually doing (i.e. what the application(s) are asking it to do).
You didn't tell me if the harddisk(s) are spinning wildly. One possible
problem is that your database file is fragmented or that there is
internal database fragmentation.
Are there any warnings or other types of information in the database
console log and/or the OS's Event Log?
Have you done a database validation recently?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| massimomm 2005-05-27, 7:23 am |
| Hi Greg, thanks for your clear answers !
Greg wrote:
Are there any warnings or other types of information in the
database
console log and/or the OS's Event Log?
Have you done a database validation recently?
- No warnings and other type of information are present in
the OS's Event Log !
And each 2 week i perfom a database validation but no
problems are found in the DB structure.
In any case i've noticed this :
If i run the database network service as an user created in
my OS, the file timestamp updating are updating NOT
frequently
, but If i run the database network service Local system
account the file timestamp updating NOT frequently but every
two-three hours or every day ! (it's depends of when the db
system perform a checkpoints, i think !), i don't understand
this behavior !!
My last question is :
in the ASA 7.0.4.3526 are the DB size capacity limitations ?
Greg i thanks you in Advance, for your answer !
Massimo
> massimomm wrote:
>
> How do you mean? That the swap is not being used or that
> you have somehow disabled it?
>
>
>
> No, the file timestamp updating frequently is an
> indication that there are frequent checkpoints going on.
> This is likely NOT a symptom of database malfunction,
> though it *might* indicate application problems.
>
> What you need to do is determine what it is the database
> itself is actually doing (i.e. what the application(s)
> are asking it to do).
>
> You didn't tell me if the harddisk(s) are spinning wildly.
> One possible problem is that your database file is
> fragmented or that there is internal database
> fragmentation.
>
> Are there any warnings or other types of information in
> the database console log and/or the OS's Event Log?
>
> Have you done a database validation recently?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Greg Fenton 2005-05-27, 9:23 am |
| massimomm wrote:
>
> - No warnings and other type of information are present in
> the OS's Event Log !
What about the database console? If you aren't already, consider adding
"-o c:\some\path\asa_con
sole.txt -os 10MB" to your database engine start
parameters so that the engine console is getting logged to a file. Then
you have a history (for the above example, you have a 10MB history) of
the engine's console which is where any Info, Warning or Error messages
will certainly be logged.
> And each 2 week i perfom a database validation but no
> problems are found in the DB structure.
That's great news.
> , but If i run the database network service Local system
> account the file timestamp updating NOT frequently but every
> two-three hours or every day ! (it's depends of when the db
> system perform a checkpoints, i think !), i don't understand
> this behavior !!
I may have been mistaken in my previous reply. Unfortunately I don't
remember all the ins-and-outs of the 7.x file format (it has changed
significantly in 8.x and 9.x). I'm not sure that a checkpoint always
updated the file timestamp in 7.x. In 8.x and 9.x, the timestamp only
changes when the .db file grows or when the engine shuts down.
Typically the .log file will be updated more frequently and so its
timestamp will be modified more frequently.
However, I don't think that any of this indicates a problem. The 80-90%
CPU that you were talking about likely has nothing to do with how
frequently the .db and .log file timestamps are changing. As I
mentioned earlier, you need to determine what it is your application(s)
are asking the engine to *do*.
> My last question is :
> in the ASA 7.0.4.3526 are the DB size capacity limitations ?
>
As the documentation I posted earlier in this thread indicates, the
maximum size of a single dbspace (a .db file) depends on the filesystem
it is stored on. For FAT32, the max is 4GB. For NTFS, the max is 512GB
for a default 2k page sized database, or 1TB for the recommended 4k page
size.
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| massimomm 2005-05-30, 1:23 pm |
| Greg thanks for you answers !
My (.db) space is stored on NTFS file system on Win2003
server and
the page size database is set to 4096 bytes so is it the max
size really 1TB ??
Thanks Massimo?
> massimomm wrote:
>
> What about the database console? If you aren't already,
> consider adding "-o c:\some\path\asa_con
sole.txt -os
> 10MB" to your database engine start parameters so that
> the engine console is getting logged to a file. Then you
> have a history (for the above example, you have a 10MB
> history) of the engine's console which is where any Info,
> Warning or Error messages will certainly be logged.
>
>
>
> That's great news.
>
>
> I may have been mistaken in my previous reply.
> Unfortunately I don't remember all the ins-and-outs of
> the 7.x file format (it has changed significantly in 8.x
> and 9.x). I'm not sure that a checkpoint always updated
> the file timestamp in 7.x. In 8.x and 9.x, the timestamp
> only changes when the .db file grows or when the engine
> shuts down.
>
> Typically the .log file will be updated more frequently
> and so its timestamp will be modified more frequently.
>
> However, I don't think that any of this indicates a
> problem. The 80-90% CPU that you were talking about
> likely has nothing to do with how frequently the .db and
> log file timestamps are changing. As I mentioned earlier
> , you need to determine what it is your application(s)
> are asking the engine to *do*.
>
>
> As the documentation I posted earlier in this thread
> indicates, the maximum size of a single dbspace (a .db
> file) depends on the filesystem it is stored on. For
> FAT32, the max is 4GB. For NTFS, the max is 512GB for a
> default 2k page sized database, or 1TB for the recommended
> 4k page size.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Greg Fenton 2005-05-30, 8:23 pm |
| massimomm wrote:
> My (.db) space is stored on NTFS file system on Win2003
> server and
> the page size database is set to 4096 bytes so is it the max
> size really 1TB ??
>
Yes.
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| massimomm 2005-05-31, 11:23 am |
| Thanks very muche fopr your support Greg !!
> massimomm wrote:
>
> Yes.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
|
|
|
|
|