Newsgroups: bit.listserv.sqlinfo
Date:         Mon, 28 Sep 1992 17:23:16 EDT
From:         Gene Kligerman <KLIGERMN@TOROLAB3.VNET.IBM.COM>
Subject:      re: SQL 3.3 Performance

Sorry about a delay, in answering, John, but I was away all last week.

You made some very good points, Carl, especially considering that you
did not have a first hand experience with VMDSS.

I will also add a few words.

Since VMDSS replaces *BLOCKIO with paging I/O, by turning on VMDSS you
can expect the disk I/O counters to go down, and the amount of paging to
go up.

It's not bad to page per se (in fact a paging operation is more efficient
then the corresponding I/O done via BLOCKIO).  The question is whether your
VM system has began to page so much that the performance has been affected.

I don't think that the paging rate of 60/sec is too big, but I am not an
expert in this area.

In short, a rise in paging activity does not demonstrate either improvement
or degradation in performance.

Let's talk about lack of apparent performance improvement.
May I ask how you measure performance improvement?  One way to do it is to
measure the transactions per second.

If you measure performance by saying 'my application doesn't seem to run
any faster with VMDSS than without', then that is something else.
I saw an installation testing VMDSS who said 'performance has not changed
with VMDSS'.  It turned out that out of every query, 90% of the time was
spent in application code, and 10% in the database.
Although database performance improved substantially, this improvement
was not apparent since the majority of the time was spent in application
code.

Now to answer a question that was asked implicitly in the original append:
Will VMDSS 'substantially improve database server performance in all cases'?

I would have to say that the answer is no for the following kind of workload:
A highly tuned database application that uses indexes to go after data
(no DBSPACE scans or sorting).  There is a large amount of data in
the database, but the access to it is very random so that there is
no benefit from caching data in main/expanded storage.

Remember, VMDSS gets the main performance benefit from the following:
- Blocking of I/Os during paging
- Prefetching of data based on reference pattern
- Caching of data in main and expanded storage

In all but one installations, VMDSS did result in significant
performance improvements.  In the remaining one, the workload is
of the type described above  (throughput increased slightly, paging
rate became very heavy, however).

I can't overemphasize the importance of tuning with VMDSS.
Playing with working set size and data caching priorities may yield
significant performance benefits.

Agreeing with Carl, if you have VMDSS, you are unlikely to have the
need for a 32Mb virtual machine.  Is the majority of this 32Mb
NCUSERs?  If so, check at periodic intervals to see how many connections
are actually active.
If you have very large data/directory buffers, then reduce them down,
since VMDSS will do the caching in main/expanded memory.

John, I am going to send you offline some information that may help
you in tuning your SQL/DS with VMDSS.

Gene Kligerman
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU