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