TRUNCATE TABLE is slow
201 views
Skip to first unread message
guil...@signal18.io
Jun 30, 2017, 10:14:56 AM6/30/17
to MyRocks - RocksDB storage engine for MySQL
TRUNCATE TABLE seems to be inefficient and slow with MyRocks. What's advised in this case? DROP and CREATE instead? Or should I avoid deleting data altogether?
-GL
MARK CALLAGHAN
Jun 30, 2017, 10:18:09 AM6/30/17
to Guillaume Lefranc, MyRocks - RocksDB storage engine for MySQL
Can you be more specific?
* how slow is too slow
* how big is the table
* are queries in progress during the TRUNCATE
* how slow is too slow
* how big is the table
* are queries in progress during the TRUNCATE
On Fri, Jun 30, 2017 at 7:14 AM, <guil...@signal18.io> wrote:
TRUNCATE TABLE seems to be inefficient and slow with MyRocks. What's advised in this case? DROP and CREATE instead? Or should I avoid deleting data altogether?-GL
--
You received this message because you are subscribed to the Google Groups "MyRocks - RocksDB storage engine for MySQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev+unsubscribe@googlegroups.com.
To post to this group, send email to myroc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/myrocks-dev/77669e14-17e2-49b4-b114-3d0703a19253%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Mark Callaghan
mdca...@gmail.com
mdca...@gmail.com
Mark Callaghan
Jun 30, 2017, 11:30:12 AM6/30/17
to MyRocks - RocksDB storage engine for MySQL
For history...
The code that determines whether truncate is fast or slow is in Sql_cmd_truncate_table::truncate_table. See the use of hton_can_recreate
for MariaDB - https://github.com/MariaDB/server/blob/10.2/sql/sql_truncate.cc#L421
and FB MySQL - https://github.com/facebook/mysql-5.6/blob/fb-mysql-5.6.35/sql/sql_truncate.cc#L473
And then the Sql_cmd_truncate_table::lock_table function that determines the value of hton_can_recreate:
MariaDB - https://github.com/MariaDB/server/blob/10.2/sql/sql_truncate.cc#L278
FB MySQL - https://github.com/facebook/mysql-5.6/blob/fb-mysql-5.6.35/sql/sql_truncate.cc#L332
The code in ::lock_table is different between MariaDB and FB MySQL and I will guess that is the problem and also the reason it doesn't reproduce for me. This is a good question for MariaDB.
The code that determines whether truncate is fast or slow is in Sql_cmd_truncate_table::truncate_table. See the use of hton_can_recreate
for MariaDB - https://github.com/MariaDB/server/blob/10.2/sql/sql_truncate.cc#L421
and FB MySQL - https://github.com/facebook/mysql-5.6/blob/fb-mysql-5.6.35/sql/sql_truncate.cc#L473
And then the Sql_cmd_truncate_table::lock_table function that determines the value of hton_can_recreate:
MariaDB - https://github.com/MariaDB/server/blob/10.2/sql/sql_truncate.cc#L278
FB MySQL - https://github.com/facebook/mysql-5.6/blob/fb-mysql-5.6.35/sql/sql_truncate.cc#L332
The code in ::lock_table is different between MariaDB and FB MySQL and I will guess that is the problem and also the reason it doesn't reproduce for me. This is a good question for MariaDB.
Guillaume Lefranc
Jun 30, 2017, 11:31:31 AM6/30/17
to MARK CALLAGHAN, MyRocks - RocksDB storage engine for MySQL, Sergey Petrunia
Interesting, hope that Sergey can shed some light. I just realized that I didn't reply to the list, adding back in case this can help other people.
-GL
The code in ::lock_table is different between MariaDB and FB MySQL and I will guess that is the problem and also the reason it doesn't reproduce for me. SergeyP might know more.On Fri, Jun 30, 2017 at 8:18 AM, MARK CALLAGHAN <mdca...@gmail.com> wrote:By "this" I mean https://groups.google.com/forum/#!topic/myrocks-dev/dhweTwN-9xgOn Fri, Jun 30, 2017 at 8:13 AM, MARK CALLAGHAN <mdca...@gmail.com> wrote:PMP output helps me. It looks like this is implemented by deleting a row at a time. And looking at source confirms that ha_rocksdb::truncate calls ha_rocksdb::remove_rows to delete rows one at a time.
https://github.com/MariaDB/server/blob/10.2/storage/rocksdb/ha_rocksdb.cc#L7990
https://github.com/facebook/mysql-5.6/blob/fb-mysql-5.6.35/storage/rocksdb/ha_rocksdb.cc#L8495
But then I read this that claims the SQL layer can avoid the call to ha_rocksdb::truncate, at least in FB MySQL.
So I need to speak to the experts.On Fri, Jun 30, 2017 at 7:56 AM, Guillaume Lefranc <guil...@signal18.io> wrote:SHOW PROCESSLIST shows the truncate.PMP isn't really helpful either:12 at,std::condition_variable::wait(std::unique_lock<std::mutex>&),rocksdb::ThreadPoolImpl::Impl::BGThread(unsigned,rocksdb::ThreadPoolImpl::Impl::BGThreadWrapper(void*),??,start_thread,clone10 ??,??,??,??,??,??,start_thread,clone7 at,??,??,start_thread,clone5 at,??,??,??,start_thread,clone3 at,??,start_thread,clone2 nanosleep,??,??,start_thread,clone2 epoll_wait,io_poll_wait(int,,get_event(worker_thread_t*,,??,start_thread,clone2 at,get_event(worker_thread_t*,,??,start_thread,clone1 set=<optimized,out>,,signal_hand,start_thread,clone1 poll,handle_connections_sockets(),mysqld_main(int,,__libc_start_main,_start1 malloc_consolidate,_int_malloc,__GI___libc_malloc,operator,rocksdb::WriteBatchWithIndex::WriteBatchWithIndex(rocksdb::Comparator,rocksdb::TransactionBaseImpl::TransactionBaseImpl(rocksdb::DB*,,rocksdb::TransactionImpl::TransactionImpl(rocksdb::TransactionDB*,,rocksdb::TransactionDBImpl::BeginInternalTransaction(rocksdb::WriteOptions,rocksdb::TransactionDBImpl::Delete(rocksdb::WriteOptions,myrocks::ha_rocksdb::remove_rows(myrocks::Rdb_tbl_def*),myrocks::ha_rocksdb::truncate(),??,Sql_cmd_truncate_table::handler_truncate(THD*,,Sql_cmd_truncate_table::truncate_table(THD*,,Sql_cmd_truncate_table::execute(THD*),mysql_execute_command(THD*),mysql_parse(THD*,,dispatch_command(enum_server_command,,do_command(THD*),tp_callback(TP_connection*),??,start_thread,cloneHowever my server is rather low spec (Google cloud n1-standard-2 instance) so that might also affect the speed. The data disk seems to be used to the maximum:Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %utilsda 0.00 0.98 2.82 0.81 0.07 0.02 52.24 0.02 5.40 5.00 6.79 1.24 0.45sdb 0.00 0.05 66.88 1258.23 1.01 5.87 10.63 1.17 0.89 1.57 0.85 0.65 85.50Does RocksDB need to write the deleted pages in some log?2017-06-30 16:34 GMT+02:00 MARK CALLAGHAN <mdca...@gmail.com>:What does SHOW PROCESSLIST show?
Also, output from PMP might help - http://poormansprofiler.org/
I assume you are using MyRocks in MariaDB. With MyRocks in FB MySQL TRUNCATE was instantaneous for a ~5gb table that fits in the RocksDB block cache. I am repeating that for a much larger table. I usually don't use it in my tests by I am pretty sure it needs to be performant in production. Although stalls in InnoDB TRUNCATE means that we encourage people to use DROP TABLE - https://bugs.mysql.com/bug.php?id=68184On Fri, Jun 30, 2017 at 7:18 AM, Guillaume Lefranc <guil...@signal18.io> wrote:50G of data, truncate has been running for a few hours now. No queries in progress.-GL
--Mark Callaghan
mdca...@gmail.com
Guillaume Lefranc
Mark Callaghan
Jun 30, 2017, 12:08:38 PM6/30/17
to MyRocks - RocksDB storage engine for MySQL, mdca...@gmail.com, ser...@mariadb.com
I shared this thread on maria-discuss at https://lists.launchpad.net/maria-discuss/msg04653.html
To unsubscribe from this group and stop receiving emails from it, send an email to myrocks-dev...@googlegroups.com.
----Mark Callaghan
mdca...@gmail.comMark Callaghan
mdca...@gmail.com--Guillaume Lefranc
Sergei Petrunia
Jul 9, 2017, 4:57:23 PM7/9/17
to MyRocks - RocksDB storage engine for MySQL, mdca...@gmail.com, ser...@mariadb.com
I'm sorry for the delay, here's my reply on that thread
I wasn't able to reproduce when debugging.
I would join the request for Guillaume to provide more info about how exactly he observes the slowdown (is it just being slow, or there are some counter values/etc that indicate that MariaDB takes a slow path?)
Reply all
Reply to author
Forward
0 new messages