Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Question Unanswered: More CPUs = Less Throughput???

    Hi All,

    I'm looking for some help with a scalability problem we're having. We have a large database (1800+ tables) and some very complex batch processes. These batch processes are broken up into 1000s of jobs and run in separate threads by a multithreaded open client app we run alongside ASE.

    We are finding that when we add CPUs to the box, the scalability simply isn't there. Throughput scalability appears linear at 1, 2 & 3 CPUs, but starts falling off after 3 CPUs, and starts deteriorating after 6 CPUs. We see similar results whether running 10, 32, 64 or 128 threads (open client app connections running the batch jobs).

    We typically see 85% of max throughput at 3 CPUs, max throughput at 6 CPUs, and back down to 82% of max throughput with 8 and 10 CPUs.

    We have basically eliminated application level blocking. So the multiple threads are very rarely blocking each other and we don't see any deadlock situations.

    We have 8 hdisks in a RAID10 configuration for the log and 24 hdisks for the data, also in RAID10. From extensive testing we believe it maybe a log IO issue, and we're having trouble seeking guidance from IBM on how to tell if we're IO bound on the log array.

    If anyone has any thoughts they would be most appreciated. Please email me if you would like to see the full sp_sysmon report. Please see details of box spec, mda wait info and abbreviated sp_sysmon output below.

    Thanks very much in advance,
    Carl Manson.

    Server information:
    System Model: IBM,7040-681
    Processor Type: PowerPC_POWER4
    Number Of Processors: 11
    Processor Clock Speed: 1904 MHz
    CPU Type: 64-bit
    Kernel Type: 64-bit
    LPAR Info: 1 DOMAIN A
    Memory Size: 7168 MB
    Good Memory Size: 7168 MB
    Firmware Version: IBM,RG040719_regatta

    O/S Version
    AIX 5.2.0.0

    ASE Version:
    Adaptive Server Enterprise/12.5.3/EBF 12146/P/RS6000/AIX 5.1/ase125x/1883/64-bit/FBO/Thu Nov 11 22:31:37 2004

    Disk array:
    IBM ESS 800 -128 Disks

    mda Wait Output...
    ASE server: XXXXXXXX (12.5.3/EBF 12146)
    Sampling period: 25-Jan-2005 11:40:10 - 11:42:10 (120 seconds)
    Wait event times for: entire ASE server

    WaitSecs NrWaits WaitEvent WtEvtID
    ----------- ----------- -------------------------------------------------- --------
    3955 1404053 waiting for disk write to complete 52
    1192 147340 waiting while no network read or write is required 179
    1118 2256657 waiting on run queue after sleep 215
    959 228421 waiting for semaphore 150
    495 5620 waiting for incoming network data 250
    480 8 xact coord: pause during idle loop 19
    298 100170 waiting for disk write to complete 51
    279 151559 waiting for network send to complete 251
    152 48647 waiting for lock on PLC 272
    143 80819 wait to acquire latch 41
    131 5 hk: pause for some time 61
    127 31542 wait for buffer read to complete 29
    120 4 wait until an engine has been offlined 104
    120 1 waiting for date or time in waitfor command 260
    117 32 waiting while allocating new client socket 178
    117 4 checkpoint process idle loop 57

    Total #spids in ASE server: 87 (system: 12; user: 75)


    Abbreviated sp_sysmon Output...
    Kernel Utilization
    ------------------

    Your Runnable Process Search Count is set to 2000
    and I/O Polling Process Count is set to 20

    Engine Busy Utilization CPU Busy I/O Busy Idle
    ------------------------ -------- -------- --------
    Engine 0 55.8 % 7.1 % 37.1 %
    Engine 1 57.8 % 7.6 % 34.6 %
    Engine 2 51.9 % 8.2 % 39.9 %
    Engine 3 53.5 % 8.1 % 38.4 %
    Engine 4 56.9 % 7.2 % 36.0 %
    Engine 5 51.7 % 9.2 % 39.0 %
    Engine 6 54.9 % 7.3 % 37.8 %
    Engine 7 51.2 % 8.0 % 40.7 %
    Engine 8 50.7 % 8.7 % 40.6 %
    Engine 9 53.7 % 8.0 % 38.3 %
    ------------------------ -------- -------- --------
    Summary Total 538.2 % 79.3 % 382.5 %
    Average 53.8 % 7.9 % 38.3 %

    Task Context Switches Due To:
    Voluntary Yields 259.1 0.0 77717 1.3 %
    Cache Search Misses 178.9 0.0 53678 0.9 %
    System Disk Writes 4.3 0.0 1301 0.0 %
    I/O Pacing 882.5 0.2 264744 4.5 %
    Logical Lock Contention 3.0 0.0 900 0.0 %
    Address Lock Contention 5.0 0.0 1498 0.0 %
    Latch Contention 714.1 0.1 214225 3.6 %
    Log Semaphore Contention 1925.2 0.3 577559 9.7 %
    PLC Lock Contention 415.7 0.1 124697 2.1 %
    Group Commit Sleeps 12470.3 2.1 3741084 63.1 %
    Last Log Page Writes 110.0 0.0 32997 0.6 %
    Modify Conflicts 157.7 0.0 47323 0.8 %
    I/O Device Contention 0.0 0.0 0 0.0 %
    Network Packet Received 40.7 0.0 12211 0.2 %
    Network Packet Sent 1325.8 0.2 397735 6.7 %
    Other Causes 1276.7 0.2 383010 6.5 %

    ================================================== =============================

    Disk I/O Management
    -------------------

    Max Outstanding I/Os per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Server n/a n/a 0 n/a
    Engine 0 n/a n/a 60 n/a
    Engine 1 n/a n/a 69 n/a
    Engine 2 n/a n/a 70 n/a
    Engine 3 n/a n/a 77 n/a
    Engine 4 n/a n/a 49 n/a
    Engine 5 n/a n/a 70 n/a
    Engine 6 n/a n/a 53 n/a
    Engine 7 n/a n/a 72 n/a
    Engine 8 n/a n/a 64 n/a
    Engine 9 n/a n/a 93 n/a


    I/Os Delayed by
    Disk I/O Structures n/a n/a 0 n/a
    Server Config Limit n/a n/a 0 n/a
    Engine Config Limit n/a n/a 0 n/a
    Operating System Limit n/a n/a 0 n/a

    Device:
    /dev/rCONVTLSMYDAT1
    CONVTLSMYDAT1 per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Reads
    APF 5.8 0.0 1730 0.4 %
    Non-APF 174.6 0.0 52383 11.9 %
    Writes 1282.8 0.2 384849 87.7 %
    ------------------------- ------------ ------------ ---------- ----------
    Total I/Os 1463.2 0.3 438962 77.0 %

    Device:
    /dev/rCONVTLSMYLOG1
    CONVTLSMYLOG1 per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Reads
    APF 0.0 0.0 0 0.0 %
    Non-APF 3.8 0.0 1140 0.9 %
    Writes 425.2 0.1 127571 99.1 %
    ------------------------- ------------ ------------ ---------- ----------
    Total I/Os 429.0 0.1 128711 22.6 %

  2. #2
    Join Date
    Dec 2004
    Posts
    25
    Increasing the number of CPU's/engines does not necessarily guarantee a performance increase.

    From the sysmon output, you can see that the engines are less than 50% busy. As you increase the number of engines, the degree of parallelism increases, but so does the the overhead on ASE to manage all the engines and each process queue running on each engine.

    You could try binding process to engines to increase throughput.

  3. #3
    Join Date
    Aug 2004
    Posts
    42
    What is the nature of the multi-threaded client app?

    We had a somewhat similar situation with a multi-threaded OpenServer application.

    It turned out to be an issue with Sybase's internal communications module whereby it was getting itself into prolonged wait states as the number of CPUs and activity increased.

    This was on HPUX 11 and ASE 12.5.

    It took avery good HP tech analyst to discover what was going on and point it out to Sybase.

    Could have been specific to HP -- I really don't know.

    I would try to get IBM to take a hard look at what is going on at the operating system level -- no just at the LOG I/O.

    Best of luck,

    Tad Hawkins

  4. #4
    Join Date
    Jan 2005
    Posts
    6
    Hi there Tad, the nature of our server side multithreaded app is a parent child process, whereby the parent is OpenServer (basically a controller process), and the child is multithreaded OpenClient. The OpenClient process has one thread that polls the DB for jobs, and dispatches them to a newly spawned thread, or queues the job on an existing thread if the thread pool is maxed out. Each thread has it's own connection to the database and executes it's jobs.

    Thanks very much for your input so far. With more understanding of our Open Client/Server app, if you have any more ideas, we'd love to hear them.

    Best regards, Carl.

Posting Permissions

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