Re: Results of benchmarking with non-trivial "select"s
Avery Pennarun
> Again, for reference, we're now talking ~10k, and this is a 'tiny' select :)
>
> MSSQL: 0.0017240 s
> Perl-DBus: 0.0235199 s (factor of 13.6 slowdown)
> VxODBC: 0.0751301 (not even in this universe; using gzip)
>
> When I disabled the gzip (ie. used raw tcp), I got:
> VxODBC: 0.0319029 s.
>
> I changed "TOP 1" to "TOP 5", ie. about 50k.
>
> MSSQL: 0.0056315 s
> Perl-DBus: 0.0331586 s (5.89 x slowdown)
> VxODBC: 0.0652423 s (smoking some crack, as usual).
Okay, so this is pretty interesting. The most important parts that I
can see from the above are:
- VxODBC tests get *faster* with more data, so your test is
statistically invalid.
- returning 5x as many rows is *way* less than 5x slower for
perl-dbus. More like 1.5x.
- MSSQL is roughly 3.2x slower for 5x as many rows.
I'm still praying, here, that the above timings are taken by running a
query 1000x and dividing by 1000, or something equally statistically
significant, and not just trying to time a single query to the nearest
microsecond, which would be worthless.
So we can model the time to run a query like this:
T = Q + (A + nB)
Where T is the total time, Q is the time to actually run the query in
the database, A is the per-request time for a given transport, n is
the number of bytes in the response, and B is the time per byte for a
given transport.
We can assume Q is constant across all transports, and we know n in
all cases (around 10000 or 50000; the overhead from our verbose dbus
headers is negligible compared to that). Thus, because we have
results at two sizes, we can solve for A and B for all transports by
using a system of parallel equations.
Doing it roughly in my head, the conclusion is: for dbus-perl, A is
huge while B is comfortably small (seemingly around 200% of B for
MSSQL, which is acceptable). We have a huge per-request overhead for
some reason.
Now, I don't know *why* we have a huge per-request overhead, but it
sounds like the sort of thing that's much easier to fix than a
transport-level overhead. That's good news.
Moreover, the per request overhead, while embarrassing, is pretty
acceptable. We can still do about 33 requests per second. Compared
to the 500 we could do with straight MSSQL, that's kind of
embarrassing, but it'll work for my purposes, where Delphi and network
latency constrain the number of requests we can do anyway.
The last part is VxODBC, which as I pointed out above, is necessarily
wrong; there's no way it's consistently faster with more data than
with less. That said, it's also super-slow for no reason. It's
probably mostly just excessive log messages that are the problem, so
I'd recommend cutting them to a minimum to see what happens. If we
can get it close to the perl-dbus speed, which ought to be achievable,
I think we're okay.
So I think that's good enough for now. Just to finish this up, it
would be nice if you could adjust the benchmark code to do the
following:
- Make sure it does a lot of repetitions (~1000) and averages them,
rather than just one request, in case it doesn't already.
- Print all three results on a single line, aligned nicely
- Print results for all the request sizes we've tested so far, one
line each: "select 1", 10k request, 50k request, and "huge" request (>
1000 rows).
That should give us everything we need to do further optimizations in
the future, and to compare before-and-after results in case we make a
change.
Thanks,
Avery
Avery Pennarun
(in versaplexd/benchmarks, in case anyone else is interested). That
answers some of my questions:
> I'm still praying, here, that the above timings are taken by running a
> query 1000x and dividing by 1000, or something equally statistically
> significant
Yes, this is all good.
> The last part is VxODBC [...] It's
> probably mostly just excessive log messages that are the problem, so
> I'd recommend cutting them to a minimum to see what happens.
I haven't tried this yet, but it spews *lots* of debug messages during
the test, so that's surely killing performance, especially on Windows,
where writing to a log file is extra slow.
> - Make sure it does a lot of repetitions (~1000) and averages them,
> rather than just one request, in case it doesn't already.
>
> - Print all three results on a single line, aligned nicely
>
> - Print results for all the request sizes we've tested so far, one
> line each: "select 1", 10k request, 50k request, and "huge" request (>
> 1000 rows).
Is looks like this is done too, more or less.
So we're in pretty good shape :)
Have fun,
Avery