De grootste kennisbank van het HBO

Inspiratie op jouw vakgebied

Vrij toegankelijk

Terug naar zoekresultatenDeel deze publicatie

Optimizing performance on current hardware

An investigation how to improve the performance on a slow database without the need of buying extra servers.

Rechten: Alle rechten voorbehouden

Optimizing performance on current hardware

An investigation how to improve the performance on a slow database without the need of buying extra servers.

Rechten: Alle rechten voorbehouden

Samenvatting

From the thesis:
"Management Summary

Cause:
Easysize is a small technology company offering a size calculation tool for online shops. The
company is successful, but their servers are reaching the top of their capacity. The company wants
to overcome this problem before they are getting too big. While they were first looking into load
balancing, it was too expensive to do it. Instead, the focus was moved how to get more performance
from the current hardware.

Recommendations:
The recommendations made is mainly moving away from MySQL and choosing MariaDB or
PostgreSQL instead, where PostgreSQL is preferred.
The company is currently trying to solve the problem currently by switching from MySQL's
InnoDB storage engine to the older MyISAM. This seriously threatens data integrity, since
MyISAM is sensitive for bad things like a power outage, causing data loss and possible table
corruption.
The last serious recommendation is to move the database to a separate machine with no other
running things than the database, so it can take up as much RAM and CPU cycles it needs. In the
current situation, they have to share it with the nginx web server and the uWSGI application server,
as well as some other applications.

Motivation:
Initially MySQL was researched and there were no real further optimisations that could be made.
The configuration was correct, and indexes were made on places where it makes sense.
One of the possibilities tested was to move data once a day to a temporary table in the memory, but
that approach did not improve the performance, unfortunately. Therefore, a look at other systems
was worthwhile
MariaDB and PostgreSQL came up as potential replacements. MariaDB is a fork of MySQL, but
has many improvements on speed and the optimizer. It profits from its MySQL heritage, and is
therefore easy to replace. However, MySQL and MariaDB are moving away from each other, so
switching systems may become harder in the future.
PostgreSQL is a different system, with its own history and SQL dialect. The application needs to be
updated and the tables moved to the new database. For the database migration tools exists. Clear
migration instructions are provided in the report to make the update to go as smoothly as possible.
However, it is worth it considering the advantages.
To keep the performance up, the databases may need to be monitored for a while and tweaked a bit
further. The systems operate differently anyway (especially PostgreSQL) and the default
configuration is rarely the best.

Consequences:
However, there are a few drawbacks that need to be considered. A database change is of course a
major operation that has to be planned thoroughly. Backups must be taken, the new software
installed, moving the data to the new database, cleaning up and tweaking the configuration and
application. This makes a downtime unavoidable. The best moment to do this is at night to make
inconvenience as little as possible.
The employees of the company have to get familiar with the new SQL syntax, to avoid writing SQL
code in a way the database does not understand. The ORM used in the web application partially
solve this problem, since it can generate code for many databases, including MySQL/MariaDB and
PostgreSQL.
A difference approach of database management is needed, especially PostgreSQL, because its logs
sometimes offer performance improvements. They have to be read frequently to find anything
interesting and boost the performance."

Toon meer
OrganisatieHogeschool Utrecht
OpleidingICT
AfdelingICT
PartnerEasysize IVS
Datum2016-09-21
TypeBachelor
TaalEngels

Op de HBO Kennisbank vind je publicaties van 26 hogescholen

De grootste kennisbank van het HBO

Inspiratie op jouw vakgebied

Vrij toegankelijk