<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<b>Synopsis:</b> One speed-up already in place, a couple others to
follow before May1, a request for feedback.<br>
<br>
<hr size="2" width="100%"><br>
Those of you who attend SSS and/or EVLA Project Coordination meetings
know that the SSS staff has finally begun looking at performance issues
for the OPT. The problems we experience with speed in the OPT are not
actually specific to the OPT but to the software that materializes our
objects from the database. This same software is used by the scheduler
(OST) and model-to-script. The SSS group has stated that version 1.09,
the one currently in development, will improve the situation. This
email announces some improvements that will precede 1.09.<br>
<br>
<i><b><u>Background</u><br>
<br>
</b></i>The SSS staff has been trying to understand the previously
reported disparity between the speed of our production environment
versus that of our test environment. We had previously reported that
timing tests showed it took 7-8x as long to load the same scheduling
blocks (SBs) from production as it did from test. We then found that
the production database server was older and less capable than the test
server. We ran some inconclusive tests and then a decision was made to
acquire a newer machine since the current one was fairly old. The new
machine has arrived and we have been performing various tests.<br>
<br>
<u><i><b>Improvements Made on 2011-03-23</b></i></u><br>
<br>
The project database has ~65 tables. Many of these are linked to one
another by foreign keys (FKs). In fact, if one were to request every
data item for a particular project, almost every one of these tables
would come into play. Daniel Lyons & Brian Truitt discovered, much
to our surprise, that the FKs used to link the tables were not
indexed. This has extreme consequences for doing look-ups. It also
leads to worse and worse performance as the number of rows in the
tables grows. Factoring out the different hardware, this problem alone
accounted for the relative slowness of the production database compared
to the test database.<br>
<br>
Daniel & Brian created the needed indices and we took new timings.
The results for 10 SBs are presented in the attached pdf. The
performance increase was pretty remarkable: SBs are now loading in
about 3% of the time they had taken before the indices were introduced.<br>
<br>
We would appreciate it if OPT users (other than Rick Perley) would open
some of their more troublesome SBs and report back whether they felt
there was a noticeable improvement. You might not feel like you got a
30-fold improvement, but we sincerely hope you can tell the difference.<br>
<br>
<u><i><b>New Machine Coming Soon</b></i></u><br>
<br>
We ran many of our timing tests on the new database server, as well as
on our existing servers. After our FK change the impact of the faster
hardware has been lessened from its previously reported speed-up factor
of 3.5. At the moment -- before we attempt to tune the new machine --
we see load times that are about 80% of what they now are on
production. We expect to see this number get better after we make some
configuration changes. We hope that ultimately the new machine will
cut load times in half. Its impact is probably non-linear and may help
large SBs more than small.<br>
<br>
Many thanks to Stephan Witz for constantly swapping databases in and
out over the last few days.<br>
<br>
<u><b>Version 1.09</b></u><br>
<br>
We are quite optimistic about the changes we're making to our database
mappings and our source code. We have been able to find places where
many hundreds of SQL statements are being run where only a handful need
be run. At this time we do not have a good estimate for the kind of
performance improvements we can expect, but we certainly expect them to
be easily noticed by users (maybe even Rick -- we'll see).<br>
<br>
Version 1.09 should be in production by May 1.<br>
<br>
<br>
-- The SSS Staff<br>
</body>
</html>