Xen & Databases

I’m running PostgreSQL and MySQL on my server that both serve different databases to WordPress, Drupal, Piwigo, Friendica, Mastodon, whatever…

In the past the databases where colocated in my mailserver VM whereas the webserver was running on a different VM. Somewhen I moved the databases from domU to dom0, maybe because I thought that the databases would be faster running on direct disk I/O in the dom0 environment, but can’t remember the exact rasons anymore.

However, in the meantime the size of the databases grew and the number of the VMs did, too. MySQL and PostgreSQL are both configured/optimized to run with 16 GB of memory in dom0, but in the last months I experienced high disk I/O especially for MySQL and slow I/O performance in all the domU VMs because of that.

Currently iotop shows something like this:

Total DISK READ :     131.92 K/s | Total DISK WRITE :    1546.42 K/s
Actual DISK READ:     131.92 K/s | Actual DISK WRITE:       2.40 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 6424 be/4 mysql       0.00 B/s    0.00 B/s  0.00 % 60.90 % mysqld
18536 be/4 mysql      43.97 K/s   80.62 K/s  0.00 % 35.59 % mysqld
 6499 be/4 mysql       0.00 B/s   29.32 K/s  0.00 % 13.18 % mysqld
20117 be/4 mysql       0.00 B/s    3.66 K/s  0.00 % 12.30 % mysqld
 6482 be/4 mysql       0.00 B/s    0.00 B/s  0.00 % 10.04 % mysqld
 6495 be/4 mysql       0.00 B/s    3.66 K/s  0.00 % 10.02 % mysqld
20144 be/4 postgres    0.00 B/s   73.29 K/s  0.00 %  4.87 % postgres: hubzilla hubzi~
 2920 be/4 postgres    0.00 B/s 1209.28 K/s  0.00 %  3.52 % postgres: wal writer process
11759 be/4 mysql       0.00 B/s   25.65 K/s  0.00 %  0.83 % mysqld
18736 be/4 mysql       0.00 B/s   14.66 K/s  0.00 %  0.17 % mysqld
21768 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.02 % [kworker/1:0]
 2922 be/4 postgres    0.00 B/s   69.63 K/s  0.00 %  0.00 % postgres: stats collector process

MySQL data site is below configured max memory size for MySQL, so everything should more or less fit into memory. Yet, there is still a large amount of disk I/O by MySQL, much more than by PostgreSQL. Of course there is much I/O done by writes to the database.

However, I’m thinking of changing my setup again back to domU based database setup again, maybe one dedicated VM for both DBMS’ or even two dedicated VMs for each of them? I’m not quite sure how Xen reacts to the current work load?

Back in the days when I did 3D computer graphic I did a lot of testing with different settings in regards of priorities and such. Basically one would think that giving the renderer more CPU time would speed of the rendering, but this turned out to be wrong: the higher the render tasks priority was, the slower the rendering got, because disk I/O (and other tasks that were necessary for the render task to work) got slowed down. When running the render task at lowest priority all the other necessary tasks could run on higher speed and return the CPU more quickly, which resulted in shorter render times.

So, maybe I experience something similar with the databases on dom0 here as well: dom0 is busy doing database work and this slows down all the other tasks (== domU VMs). When I would move databases back to domU this would enable dom0 again to better do its basic job of taking care of the domUs?

Of course, this is also a quite philosophical question, but what is the recommended setup? Is it better to separate the databases in two different VMs or just one? Or is running the databases on dom0 the best option?

I’m interested in your feedback, so please comment! 🙂

UPDATE: you can also contact me @ij@nerdculture.de on Mastodon or on Friendica at https://nerdica.net/profile/ij

Uncategorized