… the database optimizer. The SSB benchmark consists of four sets of queries. Each set is known as a “flight”. I have labeled…_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage_engine=myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… deployment has a massive data set size in relation to both the working set and the cache device size, the numbers are encouraging. Conclusion…_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
… 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…=root skip-grant-tables TokuDB-related options are all defaults, as I understand from documentation TokuDB comes with good settings out-of…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
… which force table creation on the disk because MEMORY storage engine does not support them Right ? Wrong. In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value… 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…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
….03 sec) mysql> set global max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set global tmp_table_size=1000000000; Query OK… rows we 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: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…: 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… feature). When set to 0, automatic updates to the table statistics (items 1 and 2 above) is disabled unless ANALYZE TABLE is ran…
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… # 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…. The tmp table size needed per query is 270.35k, which is not much considering the fact that tmp_tbl_size variable is set to 32M…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… 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 * from sbtest where… Rows_read: 2 # Bytes_sent: 75 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…set_connectionblogpercona.commanual character_set_databaseblogpercona.commanual character_set_filesystemblogpercona.commanual character_set_resultsblogpercona.commanual character_set_serverblogpercona.commanual character_set_systemblogpercona.commanual character_sets…commanual tmp_table_…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…table_cache = 3072 open_files_limit = 9216 tmp_table_size=1000M sort_buffer_size = 100M read_buffer_size = 100M read_rnd_buffer_size = 100M myisam_sort_buffer_size…
