… TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 21559 temp tables, 44% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… of data (32 tables, 10M rows each) and a 4GiB buffer pool. The cache devices were flushed to disk immediately prior to…_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…’t make 5h run. After 3h the disk is full, and InnoDB data size is about 210GB with 234.238.440 inserted… = 4 #default_table_type = InnoDB thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
… configuration option “disk-tmp-table-size” introduced to set maximum expected on-disk temporary table size and avoid mix-up of tmp_table_size and max_heap_table_size ChangeSet@1.2358… tmp_table_size used to do. tmp_table_size retains to (give a hint about a) limit of the on-disk temporary table size. The limit imposed upon the disk…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the…> set global max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set global tmp_table_size=1000000000; Query OK, 0… have mysql> set max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set tmp_table_size=1000000000; Query OK, 0…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
…, Temperature 34C, isSOHGood= VirtualDev Size RAID Level Disks SpnDpth Stripe Status Cache ========== ========= ========== ===== ======= ====== …table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… a large tables, this can affect your server especially if the random index dives had to be read from disk. Here is an example of SHOW TABLE STATUS FROM schema taking away a couple…: 34187 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you can see db1 has about 294 tables and…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…: 68 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No… 0 # Tmp tables 10 149 1 1 1 1 0 1 # Tmp disk tbl 30 149 1 1 1 1 0 1 # Tmp tbl size 16… query. The tmp table size needed per query is 270.35k, which is not much considering the fact that tmp_tbl_size variable is set…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F14 SET timestamp=1347398285; select… Rows_affected: 0 Rows_read: 1 # Bytes_sent: 719 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F1D SET timestamp=1347398722; select…
Post: Filling the tmp partition with persistent connections
August 6, 2012 By 14 Comments
… as /tmp partition is a common trick to improve the performance of on-disk temporary tables. Servers usually have less RAM than disk space so those kind of partitions are very limited in size and…: Increase the size of the /tmp partition. Increase the size of the binary log cache. binlog_cache_size and binlog_stmt_cache_size. Before 5…
