0

The SSD I/O capacity is around 450MB. When I copy files from one folder to another I can see in the Windows Resource Monitor that the sum of read/write speed gets around 450MB. Why the same wont happen with dbeng9 or dbsrv9, while I am executing a read, transform and insert procedure?

These are the parameters:

C:\Program Files\Sybase\SQL Anywhere 9\x64\dbsrv9.exe
C:\Tributos\Dados\Asa9\Almirante\Integra.db -xtcpip -gl all -c 5000M -cl 5000M -ch 5000M -gp 8K -gb high -gt 4 -cs -n cnv_tributos

The process is not using more than 25% from each one of the 4 cores, so I don't know where is the bottleneck.

The Sybase manual informs that the per-seat license(the one i am using), is suposed to use all cores for processing requests. In adiction the "-gt 4" purpose was to force the use of 4 cores. It looks like a default behavior as if the server process was expecting more conections or more querys to be executed, but that wont happen, only i am using the database during this process.

I need to use the maximum capacity from the machine, because I work with a ETL (Extract Transform and Load) scenario all day. These procedures runs for hours, if i could make then run 4 times faster it would make my day.

Copying Files -> http://s27.postimg.org/md3u2nlwj/Copying_Files.png Procedure Running -> http://s27.postimg.org/afbwzmdur/Sybase_Server.png

SSD -> Samsung SSD 840 EVO 500GB ATA Device

Sybase document reference.

Function (-gn) Set the maximum number of active requests (both user and system) that the database server can handle concurrently.

Syntax { dbsrv9 | dbeng9 } -gn integer ...

Applies to All operating systems and servers.

Description Set the number of active user and system requests that the database server can handle concurrently. If the maximum number of active requests are already in use when the database server receives an additional request, the new request must wait until another request is completed.

Each connection uses a thread for each request, and when the request is completed the thread is returned to the pool for use by other connections. As no connection can have more than one request in progress at one time, no connection uses more than one thread at a time. An exception to this rule is if a Java application uses threads. Each thread in the Java application is a database server execution thread.

The default is 20 threads for the network database server and for the personal database server, except on Windows CE where the default is 3 threads.

On Windows NT/2000/XP, you may want investigate the Performance Monitor readings for Requests: Active and Requests: Unscheduled. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase -gn. The Performance Monitor is not available for UNIX or Linux platforms.

Function (-gt) Set the maximum number of requests that can be run simultaneously by the database server. This option is only useful on multiprocessor systems.

Syntax { dbsrv9 | dbeng9 } -gt integer ...

Applies to All operating systems and servers except NetWare.

Description With per-seat licensing, the network database server uses all CPUs available on the machine (the default). With CPU-based licensing, the network database server uses only the number of processors you are licensed for. In addition, the personal database server and runtime database server are both limited to a single processor.

I tried to set both parameters to 4 and to 1 , the results had no change.

Improve this question
1
  • 1
    FYI that's not "transfering" the question, that's just creating another copy, and requires some work to clean up. In the future please flag it and then wait for the system to do its job.
    – Aaron Bertrand
    Mar 11, 2014 at 1:44

1 Answer 1

Reset to default
2

Two things sprint to mind:

  • The process could be partly CPU bound on a single thread. You don't state what you CPU resource is, but if you have a 4-core arrangement then it coudl be that the ~25% use you are seeing is a single thread bouncing between the cores. Similarly with 8 cores it could be using 2x parallelism so two cores worth of CPU resource which is 25% of the total available. Hyperthreading may confuse this further as the virtual cores exposed by HT do not perform completely independently.
    In this case your CPU is the bottleneck and trying to improve the concurrency of your operation may be the solution.
  • The process may not be streaming effectively so during the "read something, do something, write something" process there will be no disk activity during the "so something" phase. Often processes do not try to buffer I/O to improve this situation because adding the extra buffering layer is extra complication for (sometimes) minimal gain, it requires more memory, and it is less friendly to other concurrent processes. In comparison to your file copy test: there is almost nothing by way of "do something" stage there.
    Again increasing the concurrency of your process may help a lot here.

A lot of processes like this are still optimised for spinning-metal in mind, so they will avoid concurrency where it would create I/O contention because with disc+head drives this can harm performance a lot more than it helps - addding the extra processing threads woudl increase CPU utilisation but can make the discs much more of a bottleneck due to head thrashing. I/O contention is much less of a problem with solid state drives (particularly for read operations) with SSDs because you don't have the flapping heads problem so you can increase the concurrency of I/O operations for benefit rather than detriment.

If your ETL tool can't be told to increase concurrency, or the dependencies in your overall process mean further concurrency is not even possible, the if you have several such processes to run try running more than one instance of your ETL tool at once.

Improve this answer
4
  • Seems the case "~25% you are seeing is a single thread bouncing between the cores". F1 states that one connection cant have more than one thread unless you are using multiple threads resource from java. There is no concurrency i am using the database alone. The strange thing is that no matter if using a i5 or a QuadCore processor, both with 4 cores, but different processing capacity, it still uses 25% in both cases. I dont use java, just pure sybase stored procedure. In addition there are no commands inside the procedure that can be separated into different threads as they depend on each other
    – Fernandof
    Apr 9, 2014 at 17:34
  • That isn't "strange": both CPUs have four cores and you have one single-threaded CPU-bound operation. 1 is 25% of 4. If you were on a two core machine you would likely see ~50% instead.
    – David Spillett
    Apr 9, 2014 at 21:40
  • I know that, what i consider "strange" is that the process could be using more processing capacity. The point is, i want him to use every single possible source to the limit, so it can finish the task as faster as possible, as this is the only processes being executed on the machine. Hope i could made myself clear.
    – Fernandof
    Apr 12, 2014 at 19:27
  • Many things are not SSD aware and are optimised for spinning metal based drives only. With traditional drives often trying to perform parts of a single bulk operation like this will make it significantly slower than a single thread because of all the extra head movements involved. This isn't an issue with SSDs (well, much much less of one) but query/other planners don't optimise for this yet. They no doubt will soon, but for now if you need the extra parallelism you'll need to force it by breaking your process down manually and running several steps together.
    – David Spillett
    Apr 16, 2014 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.