System Data Recorder

We would like to introduce a real test example where we are using a relational database system, Sybase ASE 15.0 on a T5220 server from Sun Microsystems. We are running a basic OLTP application developed inhouse and we try to test this application and analyse the results.For the first time we will record the CPU statistics data from operating system via System Data Recorder or mpstat and analyse the results using cpuplayer.

The server uses 1 physical CPU, 8 cores per chip making 32 virtual CPUs. 32GB RAM is used for this server and Sybase configured for 3GB shared memory segment. Default configuration used for this test was 8 engines for ASE. Sybase ASE uses its own private mechanism for threading, meaning the database server deploys a number of database engines, one multithreading process as each db engine. Interesting is that you can easily see that only one thread is active per database engine at all times, visible using prstat. This is important to note when deploying Sybase on server architectures as Niagara, T5220.

A series of testes were executed on this hardware configuration and we tried to select the best setup for ASE by looking to the overall utilisation and workload: we try to maximize the utilisation of all CPUs or some part of them for our application. To maximize the utilisation and the throughput we tried to use processor sets, part of Solaris 10 operating system.

We used 8 database engines and run a test against the database server. We been able to collect data from mpstat. This raw data we used for cpuplayer and we tried to see how effective the CPUs, in our case the virtual CPUs were used for our workload. We were able to see that majority of the CPUs were not highly used in the blue area, meaning the current default setup might not have been enough good for our workload.

Results: check carefully each sphere, representing a virtual cpu and the area where it is present. We try to stay as uch as possible on the blue area. The results showed us that we were using as much as possible the green-blue area, somewhere in between idle - user space. Not very effective.

As previously noted, Sybase uses its own private threading mechanism therefore we noticed that only 1 thread was active per database engine. From scenario 1 we were able to clear see that CPUs were in majority of time between green-blue zone, not effectivly used. We tried to partition the CPUs and dedicate to ASE a portion of the entire CPU power and measure again. We defined a processor set cpuids: (0 4 8 12 16 20 24 28) and start ASE on this config.

Results: This immediatelly shows a different thing. Out processor set is highly used on the blue area, making more use of the processors than the previous example. As one might note the cpuplayer was capable of showing this within seconds, playing our CPU data and showing us the correct direction.

The last test case, 8 engines, processor set, shows us that using processor sets we can maximize the throughput and utilisation of the machine for this workload, even if we didnt have a best processor set configuration. A clear direction is that using processor sets is the recommended way and makes no sense to spend more time testing other scenarios without processor sets.

Graphics and Problem solving can really work together if we have proper tools and methods. Cpuplayer is one them.