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@gmail.com> 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-pag...
http://smalldatum.blogspot.com/2015/09/second-day-with-innodb-transparent-pa...
http://smalldatum.blogspot.com/2015/09/third-day-with-innodb-transparent-pag...
http://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.ht...
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp