…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…
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_… will be. There is one MRR related variable introduced in MySQL 5.6 and MariaDB 5.5 Handler_… 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…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… in practice. mysql> show global status like “key%”; +————————+———+ | Variable_name | Value | +————————+———+ |…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: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… by the fact that variable-length records were not supported by the engine. This is true for all MySQL versions, including the… it was not included in MySQL in 2008): The key columns must come before any non-key variable-length columns. For performance… missing from this release however: MyISAM tables are still used for temporary tables where the MEMORY tables with dynamic row format could be used…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 16 Comments
… enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after…@ted.mysql.internal +7 -0 BUG #4291: max_heap_table_size affects creation of disk-based temporary table fix: the new system variable memory_tmp_table…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… variables are not important ? Not really. You still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables… Innodb. tmp_table_size This variable specifies maximum size implicit temporary table (created during query execution automatically) will grow as MEMORY table before it will…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… limit and MySQL will crash when one of internal memory allocations would unexpectedly fail. To give you couple of hints: Temporary Tables You… possibilities were validated. Session variables There is no restriction on how large you can set per connection variables for unprivileged user which…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… PHP config variable because that only controls memory which passes via PHP memory management which does not apply to MySQL result set… hint if you need to release table locks early. It comes at cost of creating temporary table though which can be quite high… do INSERT … SELECT to the temporary table with auto_increment column and fetch data from this table using auto increment column ranges instead…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table… temporary table will be converted to an on-disk table,…
