…=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb…_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…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table…) group by ld.label_id, cai.inventory_id All 4 tables in the query are locked!
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-read-only=off run The base MySQL configuration (configuration …192K tmp_table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size…
Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
April 4, 2013 By 17 Comments
… converted databases from MyISAM to InnoDB, mysql tunning script gives below results for temp table , server has 4G ram, which…_table_size & tmp_table_size) , what values you experts are recomended ? TEMP TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 21559 temp tables, …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…–num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval=1 –rand-type…120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…sysbench –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on…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
… 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 as…@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_size…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…_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…_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… join 2 tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently…time_zoneblogpercona.commanual timed_mutexesblogpercona.commanual timestampblogpercona.commanual tmp_table_sizeblogpercona.commanual tmpdirblogpercona.commanual transaction_alloc_block_…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…
