maria-discuss team mailing list archive

[Jure Sah <e@xxxxxxxxxx>]

Hi,

On 7. 09. 19 21:03, Jan Steinman wrote:
> I’m using an inexpensive Mac Mini, maxed out with RAM, and a 2GB SSD,
> running NOTHING but MariaDB. I even run it headless, which means all
> the UI processes stay in sleep(3). When I was having web server
> performance issues, that was the one thing that improved things the
> most. And that was after wasting a lot of time trying to tweak MariaDB
> variables.

I work for an ISP. The system they use is connected to a dedicated SSD
RAID array on SAN. On the particular virtual machine the MariaDB is on
the same server as the Apache webserver.  The main issue is that the
website gets a lot of traffic. It has some CMS-based website on it and
the session table is grinding away constantly.

Normally it's not a problem, but once when a malfunction on the SAN
network caused degraded performance (not downtime mind you!), with the
usual traffic the website was simply down with Gateway timeout from
memcached. The workaround was to temporarily move the database to a
ramdisk (tmpfs).

My employer considers stepping outside the recommendations of the open
source community to be not worth the risk, and the issue was resolved
since.  However I know that I shouldn't have had to make the workaround
with the ramdisk, because the kernel page cache was supposed to take
care of that by itself. The reason this doesn't work is the fsyncs used
by MariaDB, which effectively disables any advantage offered by the page
cache. I think that is a great shame so I am trying to see if there is
anything I can do about that.

Perhaps there is a better alternative out there but nobody has as of yet
tried to make it work. I'm also not entirely convinced there is a
reasonable argument for being so particular about fsyncing everything on
typical server hardware (servers are usually UPS backed).


The argument about battery-backed RAID controllers, while this is
something we have, is something I find particularly unreasonable, since
as far as I know fsync isn't even supported by most RAID controller
drivers in Linux. There's an email on the Kernel mailing list that it
does exist, but that Linux doesn't use it.

>
>> Disabling fsync boosts performance of a typical MySQL server
>> by something like a factor of 3.
>
> Are you really sure about that? I don’t know the MySQL implementation
> details, but typically, sync(2) is run from a dedicated thread per
> file, and so should not block anything, unless you’ve run out of
> threads or something, in which case, tuning may help.

I found the measurement by some blogger on the internet. I'm not sure
what hardware they tried this on, it seems to me it would depend.

In the aforementioned malfunction it was pretty clear that MariaDB's
performance is capped by storage. It makes sense too, if you are
fsyncing on write operations, it's going to block if the storage is slow.

I'm not excluding the possibility that there is a setting here that is
making my system behave the way that it is.

LP,
Jure