… performs better when a larger number of rows must be joined together (Q4.1, Q4.2… be good for raw performance, but there are limitations which MyISAM imposes that are difficult to work …usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size=…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…, 16 tables with 5M rows each (about 23GB). So it makes sense to check if the dataset size makes any difference. We… optimization in MySQL 5.6 virtually eliminates contention on the mutex protecting the list of transactions, its applicability is quite limited. Namely… –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: Innodb row size limitation
April 7, 2011 By 12 Comments
…) ) Engine=InnoDB; Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL, ->Â Â ‘First… ~8000 bytes limit on row size (8126 as reported by my test installation). This limit is a consequence of InnoDB storing two rows on a… store a single row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… to the amazing MySQL manual, especially the option and variable reference table…row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows_readblogpercona.commanual Innodb_rows…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql… 56 rows…limit was also raised. Unlikely to cause significant gains unless opening and closing files is expensive operation (such as using MySQL…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… a max_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies both to implicit and … (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows…LIMIT 10; +————+——–+———+———+——–+————+———+ | engine | tables | rows | data | idx | total_size | idxfrac | …
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
…size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit…. The size limit is determined by the value of the tmp_table_size system …size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… see my indexes: mysql> select * from all_indexes limit 1 \G *************************** 1. row *************************** TABLE_SCHEMA: sakila… index sizing information in the all_indexes view: mysql> select * from all_indexes\G … *************************** 33. row *************************** …
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
… engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by the fact that variable-length… to remove this limitation. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although… dynamic row format is chosen over the fixed one, how to request it explicitly and how to set the data block size…
