maria-developers team mailing list archive

[Jan Lindström <jan.lindstrom@xxxxxxxxxxx>]

Hi,

Firstly, thank you for detailed blogs that I have personally followed but
naturally with some delay. Personally, I have not yet tested latest MySQL
5.7 performance using punch hole. My testing have been done mainly using
MariaDB and NVMFS in FusionIO NVM devices. MariaDB 10.1 does not use by
default punch hole feature (you need to enable it by innodb-use-trim). I
have tested MariaDB punch hole feature on ext4 on both SSD and traditional
HD using linkbench and 10x database. However, testing was concentrated
mostly on loading database and measure phase. Similar testing was done
using NVMFS and FusionIO NVM device. I have done some testing on dropping
tables as there is problems already when normal InnoDB tables are used (see
e.g. https://mariadb.atlassian.net/browse/MDEV-8069), but these tables have
been clearly too small to see any problems. Performance of dropping very
big tables have not been really done on different file systems as above bug
report effects big tables. Similarly, I have not tested feature on zfs or
xfs. Last time I tested punch hole feature on NVMFS and FusionIO NVM
devices I did not see assertions or hangs you have reported (
https://bugs.mysql.com/bug.php?id=78277 observation #4 or
https://bugs.mysql.com/bug.php?id=78672). I will work closely with SanDisk
to evaluate again punch hole feature performance on different file system
on SSDs (and NVM), we try to repeat your observations on MariaDB 10.1 and
naturally try to fix them or improve the documentation of feature when
punch hole should be used. We have not yet introduced similar do unlink()
in a background thread solution as MySQL.

R: Jan Lindström

On Sat, Oct 17, 2015 at 4:54 PM, MARK CALLAGHAN <mdcallag@xxxxxxxxx> wrote:

> InnoDB punch-hole compression is very bad for a filesystem when done on
> every (or most) page writes. That feature was not designed to be used in
> that manner and generates far too much in-memory metadata in the
> filesystem. If nothing else it leads to multi-minute unlink() system calls.
> Yet both MariaDB and MySQL proudly exclaim this as a feature.
>
> Are you going to use the same kludge as MySQL (do unlink() in a background
> thread) to avoid locking up InnoDB for many minutes during drop table,
> maybe leading to InnoDB killing mysqld process?
>
> Why didn't you catch this problem via testing or reading my blog posts?
>
> https://mariadb.com/kb/en/mariadb/compression/
> https://blog.mariadb.org/mariadb-10-1-is-stable-ga/
>
> https://bugs.mysql.com/bug.php?id=78277
>
> http://smalldatum.blogspot.com/2015/08/first-day-with-innodb-transparent-page.html
>
> http://smalldatum.blogspot.com/2015/09/second-day-with-innodb-transparent-page.html
>
> http://smalldatum.blogspot.com/2015/09/third-day-with-innodb-transparent-page.html
>
> http://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.html
>
> --
> Mark Callaghan
> mdcallag@xxxxxxxxx
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>
>