… that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will… gracefully while interupted? The tool doesn’t clean up the temporary table or triggers. It is designed to be resumable, although we… breaks reliably on the __tmp_x table why ? The first question sounds like a potential MySQL bug. Are you sure this is…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum… which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… how MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period… no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: How to diagnose errors in the MySQL error log
March 26, 2012 By 4 Comments
… questions about how to understand the nature of errors in MySQL‘s error log. Now, there is a lot of complexity… all of that mess is this bit: (Errcode: 28). Now, MySQL includes a little perror utility to translate this into something… That’s the key to understanding what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key… bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_… 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
… tables. Thus, I claimed, there’s no alternative but to eliminate temporary tables. This problem may not exist for row-based replication in MySQL…_log_pos: 288 Info: use `test`; drop temporary table test.t *************************** 7. row *************************** Log_name: mysql-bin.000011 Pos: 567 Event_type: Xid…, I still don’t see any way to use temporary tables with MySQL statement-based replication without some risk of breaking slaves. At…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… table size, even when all data fits in memory MEMORY temporary tables can be 10-100 times faster than disk based MyISAM tables Hopefully MySQL will implement support of dynamic rows for MEMORY tables…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
… with the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film… query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032… over 3/4ths of the time was spent working with temporary tables. But there’s something still missing: it doesn’t show…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… in the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by… Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did not add the… missing from this release however: MyISAM tables are still used for temporary tables where the MEMORY tables with dynamic row format could be used…
