[Maria-discuss] question about fast path for TRUNCATE TABLE
This started as a question on a MyRocks list. MyRocks comes from MariaDB in this case and the code that determines whether the truncate table fast path is used looks different in MariaDB than in FB MySQL. By "fast path" for truncate I mean the use of drop/recreate for truncate. The slow path is delete one row at a time. In this case, truncate is doing delete one row at a time for MyRocks in MariaDB but drop/recreate in FB MySQL. https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xg -- Mark Callaghan mdcallag@gmail.com
Hello, On Fri, Jun 30, 2017 at 08:58:08AM -0700, MARK CALLAGHAN wrote:
This started as a question on a MyRocks list. MyRocks comes from MariaDB in this case and the code that determines whether the truncate table fast path is used looks different in MariaDB than in FB MySQL. By "fast path" for truncate I mean the use of drop/recreate for truncate. The slow path is delete one row at a time. In this case, truncate is doing delete one row at a time for MyRocks in MariaDB but drop/recreate in FB MySQL.
https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xg
I didn't try to measure speed, but when I debugged TRUNCATE statements in MariaDB and MySQL, I see they both call ha_rocksdb::create(), which re-creates the table. MariaDB 10.2: https://gist.github.com/spetrunia/35738308469e27381056c6e90a5e2ce6 FB/MySQL-5.6: https://gist.github.com/spetrunia/18096aa8ec965dc37a502496217c0bb0 MariaDB also prints these statements into stderr 2017-07-08 2:53:10 140737294370560 [Note] RocksDB: Begin filtering dropped index (0,261) 2017-07-08 2:53:10 140736961083136 [Note] RocksDB: Finished filtering dropped index (0,261) which is another indication that there is no row-by-row deletion. I was using current github trees of both FB/mysql-5.6 and MariaDB-10.2. Maybe that is the reason... I'll do more checks. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Hi Sergey, I might be able to try again, though I moved to a different database at this point. But the phenomenon I observed was very high IO for several hours, and the table was still not dropped and recreated. if TRUNCATE TABLE just unlinks the data files we shouldn't observe that behavior. Thanks Guillaume Le dim. 9 juil. 2017 à 22:34, Sergey Petrunia <sergey@mariadb.com> a écrit :
Hello,
This started as a question on a MyRocks list. MyRocks comes from MariaDB in this case and the code that determines whether the truncate table fast
On Fri, Jun 30, 2017 at 08:58:08AM -0700, MARK CALLAGHAN wrote: path
is used looks different in MariaDB than in FB MySQL. By "fast path" for truncate I mean the use of drop/recreate for truncate. The slow path is delete one row at a time. In this case, truncate is doing delete one row at a time for MyRocks in MariaDB but drop/recreate in FB MySQL.
https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xg
I didn't try to measure speed, but when I debugged TRUNCATE statements in MariaDB and MySQL, I see they both call ha_rocksdb::create(), which re-creates the table.
MariaDB 10.2: https://gist.github.com/spetrunia/35738308469e27381056c6e90a5e2ce6 FB/MySQL-5.6 <https://gist.github.com/spetrunia/35738308469e27381056c6e90a5e2ce6FB/MySQL-5.6>: https://gist.github.com/spetrunia/18096aa8ec965dc37a502496217c0bb0
MariaDB also prints these statements into stderr
2017-07-08 2:53:10 140737294370560 [Note] RocksDB: Begin filtering dropped index (0,261) 2017-07-08 2:53:10 140736961083136 [Note] RocksDB: Finished filtering dropped index (0,261)
which is another indication that there is no row-by-row deletion.
I was using current github trees of both FB/mysql-5.6 and MariaDB-10.2. Maybe that is the reason... I'll do more checks.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
MyRocks stores data in RocksDB column families and they can be shared by indexes/tables. So TRUNCATE TABLE, DROP TABLE, DROP DATABASE don't unlink files. The benefit is that they are very fast. The cost is that it can take time to reclaim space as that is done in the background when compaction runs. And there are ways to get compaction running earlier than normal to get space reclaimed. But the problem in this case is that slowness appeared to be from using "delete 1 row at a time" algorithm for TRUNCATE. I can't reproduce that in FB MySQL, but some of the code in MariaDB that determines wether to use "delete 1 row at a time" differs from what we have in FB MySQL and I have more details on that at https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xg On Fri, Aug 4, 2017 at 6:41 AM, Guillaume Lefranc <guillaume@adishatz.net> wrote:
Hi Sergey,
I might be able to try again, though I moved to a different database at this point. But the phenomenon I observed was very high IO for several hours, and the table was still not dropped and recreated. if TRUNCATE TABLE just unlinks the data files we shouldn't observe that behavior.
Thanks Guillaume
Le dim. 9 juil. 2017 à 22:34, Sergey Petrunia <sergey@mariadb.com> a écrit :
Hello,
This started as a question on a MyRocks list. MyRocks comes from MariaDB in this case and the code that determines whether the truncate table fast
On Fri, Jun 30, 2017 at 08:58:08AM -0700, MARK CALLAGHAN wrote: path
is used looks different in MariaDB than in FB MySQL. By "fast path" for truncate I mean the use of drop/recreate for truncate. The slow path is delete one row at a time. In this case, truncate is doing delete one row at a time for MyRocks in MariaDB but drop/recreate in FB MySQL.
https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xg
I didn't try to measure speed, but when I debugged TRUNCATE statements in MariaDB and MySQL, I see they both call ha_rocksdb::create(), which re-creates the table.
MariaDB 10.2: https://gist.github.com/spetrunia/ 35738308469e27381056c6e90a5e2ce6 FB/MySQL-5.6 <https://gist.github.com/spetrunia/35738308469e27381056c6e90a5e2ce6FB/MySQL-5.6>: https://gist.github.com/spetrunia/18096aa8ec965dc37a502496217c0bb0
MariaDB also prints these statements into stderr
2017-07-08 2:53:10 140737294370560 [Note] RocksDB: Begin filtering dropped index (0,261) 2017-07-08 2:53:10 140736961083136 [Note] RocksDB: Finished filtering dropped index (0,261)
which is another indication that there is no row-by-row deletion.
I was using current github trees of both FB/mysql-5.6 and MariaDB-10.2. Maybe that is the reason... I'll do more checks.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Mark Callaghan mdcallag@gmail.com
On Fri, Aug 04, 2017 at 01:41:42PM +0000, Guillaume Lefranc wrote:
I might be able to try again, though I moved to a different database at this point. But the phenomenon I observed was very high IO for several hours, and the table was still not dropped and recreated. if TRUNCATE TABLE just unlinks the data files we shouldn't observe that behavior.
I've tried again and I observe the following: - TRUNCATE TABLE non_partitioned_table works instantly - TRUNCATE TABLE partitioned_table is slow. This is in both MariaDB and facebook/mysql-5.6. I'll collect the details and report a bug. Guillaume, could it be that you saw TRUNCATE TABLE being slow on a partitioned table? (or it was non-partitioned?) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Hi Sergey, Yes, it was partitioned as mentioned in the other issue (Actually same structure as the one described for the LOAD DATA) -GL Le dim. 6 août 2017 à 20:39, Sergey Petrunia <sergey@mariadb.com> a écrit :
On Fri, Aug 04, 2017 at 01:41:42PM +0000, Guillaume Lefranc wrote:
I might be able to try again, though I moved to a different database at this point. But the phenomenon I observed was very high IO for several hours, and the table was still not dropped and recreated. if TRUNCATE TABLE just unlinks the data files we shouldn't observe that behavior.
I've tried again and I observe the following:
- TRUNCATE TABLE non_partitioned_table works instantly - TRUNCATE TABLE partitioned_table is slow.
This is in both MariaDB and facebook/mysql-5.6. I'll collect the details and report a bug.
Guillaume, could it be that you saw TRUNCATE TABLE being slow on a partitioned table? (or it was non-partitioned?)
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Filed as https://github.com/facebook/mysql-5.6/issues/676 On Sun, Aug 06, 2017 at 06:40:41PM +0000, Guillaume Lefranc wrote:
Yes, it was partitioned as mentioned in the other issue (Actually same structure as the one described for the LOAD DATA)
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
participants (3)
-
Guillaume Lefranc -
MARK CALLAGHAN -
Sergey Petrunia