![](bg3.png)
Table 1: TPC-H full test results for increasing memory size. In SQL Server, we varied the total server memory; in MySQL,
the buffer and sort memories, with a 3:1 ratio as recommended by MySQL developers. Fill factor is kept at 90% for SQL
Server and 15/16 (default) for MySQL. Page size is kept at 8KB (which is the default for SQL Server) for both systems.
Memory Size Test
total server memory 16 MB 64 MB 128 MB 256 MB 512 MB 768 MB 1024 MB
load test
SQL Server 46min 20min 19min 17min 16min 16min 36min
MySQL 48min 23min 20min 16min 16min 14min 57min
performance test
SQL Server 4h54min 1h13min 1h 52min 41min 40min 1h9min
MySQL 5h32min 1h28min 1h13min 1h2min 56min 54min 1h44min
QphH@1GB
SQL Server 19.13qph 78.55qph 90.20qph 102.30qph 130.76qph 131.80qph 86.03qph
MySQL 17.41qph 75.70qph 79.84qph 89.77qph 103.67qph 105.10qph 70.63qph
Price-per-QphH@1GB
SQL Server 73.08$ 17.80$ 15.49$ 13.67$ 10.69$ 10.61$ 16.25$
MySQL 28.72$ 6.60$ 6.26$ 5.57$ 4.82$ 4.76$ 7.80$
3 EXPERIMENTS
The goal of our experiments was to showcase a set
of useful TPC-H tests that any small enterprise could
perform in order to choose the database system and
tuning configurations that offer optimal ad-hoc DSS
performance in their system. In addition, we ran these
tests ourselves on off-the-shelf hardware, aiming at
some take-away rules-of-thumb for choosing between
a commercial (SQL Server 2008) and an open-source
(MySQL 5.1) database system and optimizing tuning
for DSS queries at this scale.
We are interested in the characteristics of ad-hoc
DSS workloads and the tuning parameters that affect
their performance, for a given database. Since DSS
queries deal with large amounts of data within scans,
sorts and joins, the size of the buffer pool and the sort
buffer play an important role. Following the same
logic, the fill factor and the page size can also in-
fluence performance, as they can contribute to more
rows per page thus keeping more sequential data in
the data cache.
However, not all these parameters can be set by the
user in each of the database systems at hand. In SQL
Server, it is not possible to set the size of the buffer
pool or the sort buffer; only the total size of mem-
ory that the system can use can be set, by determining
its minimum and maximum values. MySQL, on the
other hand, allows to set a specific size for the buffer
pool and the sort buffer. Also, while SQL Server op-
erates with a fixed page size of 8 KB, in MySQL the
user can set the page size to 8, 16, 32 or 64 KB. Fi-
nally, in SQL Server it is possible to specify the fill
factor for each page, while MySQL manages the free
space automatically, with tables populated in sequen-
tial order having a fill factor of
15
/16.
In light of these differences, we decided to run two
general types of tests: the memory size test and the
number of rows per page test. Tables 1, 2 and 3
Table 2: TPC-H full test results for increasing fill factor
in SQL Server. Page size is kept at default value of 8KB.
Memory size is set at a medium value of 128KB.
MS SQL Server- Number of Rows per Page Test
fill factor 40% 60% 80% 100%
load test 27min 22min 20min 19min
perf. test 2h2min 1h9min 1h3min 59min
QphH@1GB 34.59qph 80.10qph 89.34qph 91.58qph
PPQphH@1GB 40.42$ 17.45$ 15.65$ 15.23$
Table 3: TPC-H full test results for increasing page size
in MySQL. Fill factor is kept at default value of 15/16.
Total memory size is set at a medium value of 128KB,
with a buffer/sort memory ratio of 3:1 as recommended by
MySQL developers.
MySQL- Number of Rows per Page Test
page size 8 KB 16 KB 32 KB 64 KB
load test 20min 18min 17min 17min
perf. test 1h13min 59min 52min 50min
QphH@1GB 79.84qph 92.41qph 106.20qph 109.38qph
PPQphH@1GB 6.26$ 5.41$ 4.71$ 4.57$
show the test results. For the number of rows per page
test, note that the resulting range of number of rows
per page is different for the two database systems, but
that serves exactly the purpose of verifying whether
allowing the user to specify much larger page sizes
gives MySQL an advantage.
In the interest of simulating the environment of a
smaller enterprise, we chose inexpensive off-the-shelf
hardware (an AMD Athlon processor with 1GB of
RAM and a SATA 80 GB hard disk) and the lowest
possible scale factor (yielding a 1 GB database). We
find it interesting to provide some results with a lower
scale factor, as the only available ones to date are the
official TPC-H results starting at 100 GB. Finally, for
the price/performance metric calculations, we consid-
ered the hardware cost to be approximately 500$ and
the software cost to be the current price of 898$ for
SQL Server 2008 (circa 2010) and 0$ for MySQL 5.1.
BenchmarkingwithTPC-HonOff-the-ShelfHardware-AnExperimentsReport
207