Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    1

    Unhappy Unanswered: SYbase performance problem!

    Hi all
    I am running Sybase ASE 12.5 (Trial) on Windows 2000 Professional. I am using a native sybase ODBC driver to connect and run simple insert and select queries through a Visual Basic 6 application.

    I am having performance issues in importing the records into my Sybase database. I had run the same application on the same environment earlier and had run the application in 45 mins. It has been running for 2 days and is still running.

    I have tried to increase network memory, and the sie of the network packets, the number of concurrent connections, I have the same size devices (100 MB for logs and 500 MB for data), and have still not been able improve the performance.

    Is there some setting that I am missing on the database end? I have appended to the bottom, the memory usage configurations.

    Thanks all in advance.


    KS
    1> sp_configure "Memory Use"
    2> go

    Group: Memory Use

    Parameter Name Default Memory Used Config Value Run Value Unit Type
    ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
    additional network memory 0 4 4096 4096 bytes dynamic
    allocate max shared memory 0 0 1 1 switch dynamic
    allow resource limits 0 #4 0 0 switch static
    audit queue size 100 2 100 100 number dynamic
    average cap size 200 0 200 200 bytes static
    caps per ccb 50 0 50 50 number static
    deadlock pipe max messages 0 0 0 0 number dynamic
    default network packet size 512 #1655 5120 5120 bytes static
    disk i/o structures 256 38 256 256 number dynamic
    dynamic allocation on demand 1 0 1 1 switch dynamic
    enable rep agent threads 0 0 0 0 switch dynamic
    errorlog pipe max messages 0 0 0 0 number dynamic
    event buffers per engine 100 #20 100 100 number static
    executable codesize + overhead 0 677767 0 677767 kilobytes NULL
    heap memory per user 4096 0 4096 4096 bytes dynamic
    lock hashtable size 2048 51 2048 2048 bytes static
    lock spinlock ratio 85 0 85 85 ratio static
    max SQL text monitored 0 4 0 0 bytes static
    max memory 23552 204800 102400 102400 memory pages(2k) dynamic
    max number network listeners 5 4485 5 5 number static
    max online engines 1 112658 2 2 number static
    memory per worker process 1024 14 1024 1024 bytes dynamic
    number of alarms 40 4 40 40 number dynamic
    number of aux scan descriptors 200 #206 200 200 number dynamic
    number of ccbs 0 0 0 0 number static
    number of devices 10 #10 10 10 number dynamic
    number of dtx participants 500 146 500 500 number dynamic
    number of java sockets 0 0 0 0 number dynamic
    number of large i/o buffers 6 126 6 6 number dynamic
    number of locks 5000 752 5000 5000 number dynamic
    number of mailboxes 30 3 30 30 number dynamic
    number of messages 64 3 64 64 number dynamic
    number of open databases 12 1370 12 12 number dynamic
    number of open indexes 500 582 500 500 number dynamic
    number of open objects 500 663 500 500 number dynamic
    number of remote connections 20 303 20 20 number static
    number of remote logins 20 504 50 50 number static
    number of remote sites 10 1073 10 10 number static
    number of user connections 25 2141 25 20 number dynamic
    number of worker processes 0 1031 10 10 number dynamic
    open index hash spinlock ratio 100 0 100 100 ratio dynamic
    open index spinlock ratio 100 0 100 100 ratio dynamic
    partition groups 1024 860 1024 1024 number dynamic
    partition spinlock ratio 10 0 10 10 ratio dynamic
    permission cache entries 15 #172 15 15 number dynamic
    plan text pipe max messages 0 0 0 0 number dynamic
    procedure cache size 3271 6902 3271 3271 memory pages(2k) dynamic
    process wait events 0 0 0 0 switch dynamic
    remote server pre-read packets 3 #302 3 3 number static
    size of global fixed heap 150 0 150 150 memory pages(2k) dynamic
    size of process object heap 1500 76 1500 1500 memory pages(2k) dynamic
    size of shared class heap 1536 0 1536 1536 memory pages(2k) dynamic
    size of unilib cache 0 120 0 0 bytes dynamic
    sql text pipe max messages 0 0 0 0 number dynamic
    stack guard size 16384 #1248 16384 16384 bytes static
    stack size 41984 #3198 41984 41984 bytes static
    statement pipe max messages 0 0 0 0 number dynamic
    total data cache size 0 8722 0 8722 kilobytes NULL
    total logical memory 23552 35740 17870 17870 memory pages(2k) read-only
    total physical memory 0 203776 0 101888 memory pages(2k) read-only
    txn to pss ratio 16 726 16 16 number dynamic
    user log cache size 2048 0 2048 2048 bytes static
    user log cache spinlock ratio 20 0 20 20 ratio dynamic
    wait event timing 0 0 0 0 switch dynamic
    (1 row affected)
    (return status = 0)

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: SYbase performance problem!

    Hi,

    ASE 12.5 has what some people may consider a bug. The default size for the default data cache is very small. It's platform independent, but on NT/W2K I think it's 8MB.

    Increase the amount of memory available to ASE

    sp_configure 'max memory', <number of 2K pages>
    go

    The following configuration changes, tell ASE to ask the OS for all the memory it requires when it starts. The second change tells ASE to allocate memory at boot up not as it's required. Both these reduce the processing overhead of allocating memory within ASE and the OS.

    sp_configure 'allocate max shared memory', 1
    go
    sp_configure 'dynamic allocation on demand' 0
    go

    Recycle the ASE.

    sp_cacheconfig 'default data cache', '100M'
    go

    100MB for the data cache ought to be considered the minimum, if you have it set it to approx 90% of the system memory (if a server and ASE is the only major task). 75%is probably closer to the limit if the client and server reside on the same machine.

    Are you running the ASE as the same machine as the client application? If so you ought to reduce the "runnable process search count". The controls the number of searches for processes on the run queue before yielding the CPU back to the operating system

    Once the default data cache has been changed, recycle the ASE to take account of the change.

    Create a 16 I/O pool in the default data cache. If ASE decides to use a large IO, it will use the largest available, so there is no point in creating 8K pools if you have a 16K pool. If you find 8K is better than 16K go with that.

    sp_poolconfig 'default data cache', '75M', '16K'
    go

    The 75M should be approx 75% of the cache size. Fine tuning of this figure may be required.

    How are you importing the data, inserts or bcp? If you're using BCP try increasing the value of "number of pre-allocated extents". This maxes out at 31, but controls how space is allocated by the storage manager when the session asks for more space.

    On Windows NT, you may need to change a registry key. Search the registry for "LargeSystemCache". Set this to 0. If the ASE is running on a server, set the foreground application performance boost to none, otherwise the screen saver runs with a higher priority than the ASE.

    That covers most of the "out of box" stuff I do for ASE on Windows. Plenty to play with, have fun.

    Richard.

  3. #3
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Oh yeah, something I forgot to add.

    Keep an eye on the amount of physical memory available. If this falls below 5MB, Windows creates loads of page faults - These are very expensive and may hit disk I/O bandwidth.

    Richard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •