Door Redactie Tweakers

Database test: Sun UltraSparc T1 vs. AMD Opteron

26-10-2006 • 18:30

0

Singlepage-opmaak

MySQL optimizations

Testing the UltraSparc T1 was not a trivial task: we spent a good three months finding the optimal configuration for our tests, for which we worked together with people from Sun, who in turn worked with people from MySQL. In all, two billion queries were fired, spread out across 3,500 serial runs, which took more than nineteen days to complete. Our research showed that results can vary greatly, which taught us that this machine is not easy to tame. If you want to get it to achieve its maximum potential, you need the right combination of software and settings, which can demand a great deal of patience. We are reasonably convinced that we have done the best that we can do for now, but Sun is still researching our benchmark because the company believes that it can be improved. In response to the problems we found, the company has come up with an improvement for the Sun Studio compiler which may allow considerable gains at a later stage, but we did not want to wait for its release before publishing our results.

Compared to the previous article, a number of things have been changed in the test method. The most visible aspect is the change from 'queries per second' to 'requests per second'. This allows different databases to be compared in a more straightforward fashion, since one may need more queries to build a given page than another. An example is MySQL 4.x, which does not handle sub queries optimally and hence manages to perform faster on greater numbers of simple instructions. An alternative such as PostgreSQL on the other hand, is at its best when handling complex queries, of which fewer will be needed.

Additionally, we have switched to using two client machines to generate the database loads, since during the testing phase for the last review – unfortunately towards the end – this turned out to yield structurally better results. Also, we have added functionality that enables us to switch off cores and/or threads, in order to study the scaling behaviour of the servers.

To demonstrate the major effect that a few minor alterations can have, a graph is shown below with different MySQL 5.x results that we obtained during our research. The number of cores and the amount of memory is kept constant, but in spite of that the scores between the different MySQL versions vary wildly. Settings can also cater for huge differences: for example, a 5.0.18 version that was tweaked by Sun was a good deal faster than our original configuration, although only one parameter had been altered.

Sun T2000 - MySQL tuning