|
Home > Archive > SQL Anywhere database > October 2005 > Performance Tuning Sybase 7
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 |
Performance Tuning Sybase 7
|
|
| James Girvan 2005-10-27, 7:39 am |
| Hi there,
we have quite a large (3.5GB) database running on Sybase 7 that has died
with assertion errors a couple of times over the past few weeks. We're in
the process of rebuilding the database and I wondered if anyone knew how
best to set up the database startup parameters so the database is allocated
the right amount of shared memory, etc to work efficiently.
The only performance tuning we have done so far is alter to -c parameter to
1000, but this doesn't seem to have made much of a difference. The database
is used as part of a call centre operation and is also having data inputted
into it all day long aside from the operators updating client records, etc.
In addition to this there are people running management reports against the
very same database!
If anyone knows how to set the database for optimum performance, please let
me know!
Thanks,
James
| |
| Greg Fenton 2005-10-27, 7:39 am |
| James Girvan wrote:
>
> If anyone knows how to set the database for optimum performance, please let
> me know!
Please always post the version and (more importantly) build number of
ASA that you are using (e.g. use the command "dbeng7 -v").
-c 1000 sets the *initial* cache size. If the database becomes idle or
extremely busy, that cache size will grow or shrink due to dynamic cache
sizing. If this is a production server dedicated to running just the
database engine, I typically suggest specifically setting the lower
(-cl) and upper (-ch) cache sizes or even disabling cache sizing
altogether (-ca 0).
However, tuning parameters will likely not stop the assertions from
occuring. What steps are you taking when you get an assertion?
Have you read:
http://www.ianywhere.com/developer/.../assertion.html
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Breck Carter [TeamSybase] 2005-10-27, 7:40 am |
| You can never have too much RAM; try -c 1024M (one gigabyte) Or
more... depending on what kind of queries you do, it is possible to
benefit from having *more* RAM than the file size, but that requires
some fine dancing with the O/S, and (I think) a new version of ASA.
FWIW -c 1000 in version 7 is *strangling* the engine... it means less
than one megabyte. See this section of the Help...
=====
ASA Adaptive Server Anywhere Reference Manual
CHAPTER 2. The Database Server
The database server
Database server switches
-c command-line option
Function
Set the initial memory reserved for caching database pages and other
server information.
Syntax
[ dbsrv7 | dbeng7 ] -c [integer | integerK | integerM | integerP ] ...
Applies to
All operating systems and servers.
Description
The amount of memory available for use as a database server cache is
one of the key factors controlling performance. You can set the
initial amount of cache memory using the -c command-line option
The more cache memory that can be given the server, the better will be
its performance.
The units K and M can be either lower case or upper case. If K or M is
not supplied, any integer less than 10000 is assumed to be in
kilobytes, and any integer 10000 or greater is assumed to be in bytes.
For example, -c 4096 means 4096KB or 4 194 304 bytes. Whereas, -c 200
000 means (an unreasonably small) cache of 200 000 bytes.
=====
Consider running the database on its own box, with a 100Mbit network.
Then, defrag your hard drive. Diskeeper 9 is highly recommended, much
better than the builtin defrag (I am assuming Windoze here).
Next, upgrade to 9.0.2, it runs wayyyyyyy faster than 7.
And finally, buy my book, read Chapter 10 :)
Breck
On 7 Oct 2005 06:41:25 -0700, "James Girvan" <j_girvan@tunstall.co.uk>
wrote:
>Hi there,
>
>we have quite a large (3.5GB) database running on Sybase 7 that has died
>with assertion errors a couple of times over the past few weeks. We're in
>the process of rebuilding the database and I wondered if anyone knew how
>best to set up the database startup parameters so the database is allocated
>the right amount of shared memory, etc to work efficiently.
>The only performance tuning we have done so far is alter to -c parameter to
>1000, but this doesn't seem to have made much of a difference. The database
>is used as part of a call centre operation and is also having data inputted
>into it all day long aside from the operators updating client records, etc.
>In addition to this there are people running management reports against the
>very same database!
>
>If anyone knows how to set the database for optimum performance, please let
>me know!
>
>Thanks,
>
>James
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| James Girvan 2005-10-27, 7:40 am |
| Hi Greg/Breck,
thanks for your help. We are running ASA 7.0.3.2107 with an upgrade to v9
planned in for the next month or so. I forgot to mention our -c parameter is
set to -c 1000M. And yes, you are right we're running on Windows! We did an
unload and reload last week and touch wood there haven't been any more
assertion errors. Does an unload/reload get rid of redundant space within
the database from deleted records, etc?
Cheers,
James
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:h06dk15tp6ttgks
mcb59d1002nfougsach@
4ax.com...
> You can never have too much RAM; try -c 1024M (one gigabyte) Or
> more... depending on what kind of queries you do, it is possible to
> benefit from having *more* RAM than the file size, but that requires
> some fine dancing with the O/S, and (I think) a new version of ASA.
>
> FWIW -c 1000 in version 7 is *strangling* the engine... it means less
> than one megabyte. See this section of the Help...
>
> =====
> ASA Adaptive Server Anywhere Reference Manual
> CHAPTER 2. The Database Server
> The database server
> Database server switches
>
> -c command-line option
>
> Function
> Set the initial memory reserved for caching database pages and other
> server information.
>
> Syntax
> [ dbsrv7 | dbeng7 ] -c [integer | integerK | integerM | integerP ] ...
>
> Applies to
> All operating systems and servers.
>
> Description
> The amount of memory available for use as a database server cache is
> one of the key factors controlling performance. You can set the
> initial amount of cache memory using the -c command-line option
>
> The more cache memory that can be given the server, the better will be
> its performance.
>
> The units K and M can be either lower case or upper case. If K or M is
> not supplied, any integer less than 10000 is assumed to be in
> kilobytes, and any integer 10000 or greater is assumed to be in bytes.
> For example, -c 4096 means 4096KB or 4 194 304 bytes. Whereas, -c 200
> 000 means (an unreasonably small) cache of 200 000 bytes.
> =====
>
> Consider running the database on its own box, with a 100Mbit network.
>
> Then, defrag your hard drive. Diskeeper 9 is highly recommended, much
> better than the builtin defrag (I am assuming Windoze here).
>
> Next, upgrade to 9.0.2, it runs wayyyyyyy faster than 7.
>
> And finally, buy my book, read Chapter 10 :)
>
> Breck
>
> On 7 Oct 2005 06:41:25 -0700, "James Girvan" <j_girvan@tunstall.co.uk>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Greg Fenton 2005-10-27, 7:40 am |
| James Girvan wrote:
> Does an unload/reload get rid of redundant space within
> the database from deleted records, etc?
>
Yes. The philosophy that ASA takes is that if a db grows to a
particular size, then a bunch of data is deleted, the odds are that over
time the db will grow to (and likely beyond) that initial size. So the
database file never shrinks. The way to recover free pages is to do an
unload/reload.
Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Breck Carter [TeamSybase] 2005-10-27, 7:40 am |
| On 17 Oct 2005 02:46:24 -0700, "James Girvan"
<j_girvan@tunstall.co.uk> wrote:
>Does an unload/reload get rid of redundant space within
>the database from deleted records, etc?
The "etc" is far more important than recovering space. Free space is
reusable so "shrinking" a database file is not often a goal worth
pursuing; in fact, folks often use ALTER DBSPACE to *expand* a .DB
file beyond current requirements, then run Diskeeper to defragment the
big file so it will be efficient disk-wise.
What the unload/reload does that is really good, is internally
reorganize the tables and indexes. Split rows are gathered, and
indexes are rebalanced. Version 9 has the REORGANIZE TABLE command so
you can do it without the pain of unload reload.
FWIW my book has a chapter on Tuning, lots of material on data
organization, for Version 9.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| James Girvan 2005-10-27, 7:40 am |
| Thanks for your help. We've unloaded and reloaded and upgraded to 7.04 but
we're still getting assertion errors (Assertion Failed: 200302)
Cheers,
James
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:28b7l1l3usbfm28
k3fq29nook04mi3uv8q@
4ax.com...
> On 17 Oct 2005 02:46:24 -0700, "James Girvan"
> <j_girvan@tunstall.co.uk> wrote:
>
>
> The "etc" is far more important than recovering space. Free space is
> reusable so "shrinking" a database file is not often a goal worth
> pursuing; in fact, folks often use ALTER DBSPACE to *expand* a .DB
> file beyond current requirements, then run Diskeeper to defragment the
> big file so it will be efficient disk-wise.
>
> What the unload/reload does that is really good, is internally
> reorganize the tables and indexes. Split rows are gathered, and
> indexes are rebalanced. Version 9 has the REORGANIZE TABLE command so
> you can do it without the pain of unload reload.
>
> FWIW my book has a chapter on Tuning, lots of material on data
> organization, for Version 9.
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
|
|
|
|
|