Hi,
We are using Sybase ASE 15.0.3.
I need clarity and recommendations for setting up cache configuration for a consolidation activity.We are talking about 300 GB of data pumped from one db to another using slow bcp.
The test environment has the below settings:
sp_helpcache
Cache Name Config Size Run Size Overhead
default data cache 2000.00 Mb 2000.00 Mb 226.97 Mb
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
2000.01 Mb 2000.00 Mb
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
sp_cacheconfig
A B C D E
Cache Name Status Type Config Value Run Value
default data cache Active Default 2000.00 Mb 2000.00 Mb
------------ ------------
Total 2000.00 Mb 2000.00 Mb
==========================
Cache: default data cache, Status: Active, Type: Default
Config Size: 2000.00 Mb, Run Size: 2000.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 1, Run Partition: 1
IO Size Wash Size Config Size Run Size APF Percent
2 Kb 61440 Kb 1600.00 Mb 1600.00 Mb 10
4 Kb 40960 Kb 200.00 Mb 200.00 Mb 10
8 Kb 40960 Kb 200.00 Mb 200.00 Mb 10
(return status = 0)
Configuring a 16 kb pool would give beeter results?
Having a log cache is better?(since we r using slow bcp and it would be logged?
Would be grateful if experts can give me a detailed explanation as I am a beginner with DBA aspects.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks for the inputs Joe..
Yes you are right,this is a one time activity and whatever configuration changes we make can be reverted to the default values once the consolidation is complete.
The source and destination databases are in two different data servers.
Can you think of any other configuration changes which might be needed?
Bcp (both directions) usually also benefits from picking a larger network packet size. You need to request this on the bcp command line (the "-A" option), and enable it at the server side (tune "additional network memory" and "max network packet size" - well documented in the System Administration Guide, or you could just set them to -A16384, 53248 and 16384 respectively, which sets a 16Kb network packet size for one connection).
When you are creating indexes, create the clustered index first as it will cause any non-clustered indexes to be rebuilt.
For bcp in, picking a sensible transaction batch size helps throughput a lot, but you need to monitor the output to confirm all batches successfully committed. A batch size of 5000, 10000 or 20000 is usually a decent first guess.
Beyond that, difficult to suggest much more that would suit every bcp job... but all that should help, some.
Parameter Name Default Memory Used Config Value Run Value Unit Type
additional network memory 0 500 512000 512000 bytes dynamic
default network packet size 512 #18712 2048 2048 bytes static
max network packet size 512 0 8192 8192 bytes static
max number network listeners 5 2209 5 5 number dynamic
number of remote connections 20 156 25 25 number static
number of remote logins 20 83 20 20 number static
number of remote sites 10 8845 20 20 number static
remote server pre-read packets 3 #154 3 3 number static
send doneinproc tokens 1 0 1 1 switch dynamic
tcp no delay 1 0 1 1 switch static
This is the current network packet size confihured.We already have plans of using the -A option.
I guess if we retain the max network packet size as 8192 and not change the adiitional network memory we should be fine.I figured this out based on the below calculation:
<<To determine the value for additional network memory when your applications use larger packet sizes:
Estimate the number of simultaneous users who will request the large packet sizes, and the sizes their applications will request,
Multiply this sum by three, since each connection needs three buffers,
Add two percent for overhead, and
Round the value to the next highest multiple of 2048.>>
Also you have mentioned teh memory seems to be low.By this you mean we need to increase the size of data cache right?
Is there a benchmark to decide on this?
Re. network memory - you look fine there, 8k is good too.
Re. memory - no benchmark needed. More is always better. There are no points of diminishing returns - if you can give ASE even 1Mb more of memory without causing paging, do so.
Almost always, that is best spent on data cache(s) but sometimes other areas need it more too. We're probably now getting beyond the scope of what will be useful for a single event... if we were tuning for an ongoing workload we could monitor and adjust based on that. I think you've probably now hit most of the major points needed for your exercise.
Business Accounts
Answer for Membership
by: Joe_WoodhousePosted on 2009-10-08 at 14:34:53ID: 25530361
Is this something you're running just once? I'm asking because in this scenario you won't be re-reading data, which is basically the main benefit of caching.
You can still benefit from sequential large I/Os (ie large buffer pools) and you can still benefit from speculative prefetching (you asked to read these pages, so we guess that you'll want those pages next and try to prefetch them before you even ask - ASE calls this asynchronous prefetch or APF)... IF your data is relatively unfragmented.
Adding a 16Kb pool
This might be helpful, we won't know since we have no idea of how fragmented your underlying data is. Fragmentation makes sequential reads less useful. If you create a 16Kb pool (worth trying!), remove all of your 8Kb pool as ASE will only ever use the largest and the smallest pools for data without trying anything in between. (It can use other sizes for logging.) Size it to be one third of the cache in total (as a starting place) ie make it about 650Mb.
Adding a log cache
I'm not as convinced this will be helpful - you don't seem to have much memory there. Only 2Gb for default data cache seems awfully low these days - I can do more than that on my laptop. :) Named caches reduce the amount of cache any one object can use and when you don't have much to go around that may be a bad thing. That said... a log cache can usually be pretty small... in your scenario even 25Mb would be large enough.
I don't think you'll likely see much gain from this though. There are two motivations for wanting named caches - to reduce spinlock contention in accessing the caches in the first place, and to reduce contention within the caches (to stop one object flushing another out of cache). In your scenario I can't imagine you'll have many concurrent users so we won't see spinlock contention, and you won't be re-reading data pages or rewriting log pages so as soon as you've finished with any page, you won't reuse it, so who cares if it gets thrown out of cache?
I think you might gain more from setting the log I/O size high. A mass move of data like that will generate a lot of transaction logs, so using a larger I/O to write them to data will help. Look into the procedure "sp_logiosize". I suggest (once you create a 16Kb pool) you set it to "16" (note it must be a string and hence be in quotes). Don't forget to set it back to the default "4" when you're done!
Other thoughts
Are your source and data in the same ASE? If so then unless you're doing some ETL along the way, you gain nothing and lose much by doing a BCP out and then in again. It would be faster to just do an insert... select.