… MEMORY tables you can create both as permanent and temporary. There is a max_heap_table_size …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: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /…BYTES_WRITTEN: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT… 0x concurrency ________________ # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= …
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… 20M rows table: sysbench –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –mysql-socket=/tmp/mysql_sandbox5524.sock –mysql-db=test –oltp-table-name=sbtest_20m –max-time=900 –num-threads=8 –max-requests…
Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
January 19, 2007 By 17 Comments
… the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option….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: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…: mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id(‘demo’,'dashboard_customer_sales’)\G *************************** 1. row *************************** mview_table_id: …_sales’)); Query OK, 0 rows affected (41 min 52.04 sec) *If you use MIN/MAX/COUNT_DISTINCT, a secondary …
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
… –oltp-table-size=25000000 run Results: Load time for regular tables: 19693 sec, for compressed tables: 38278 sec. Compressed tables are create as: ROW_FORMAT…-table-size=5000000 –oltp-read-only=on –rand-init=on –num-threads=16 –max-requests=0 –rand-type=uniform –max-time=1800 –mysql-user…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…mysql> show table status like “post_channel” \G *************************** 1. row *************************** Name: post_channel Engine: InnoDB Version: 10 Row_format: Compact Rows: 278987416 Avg_row…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000060 Avg_row…
Post: Is MySQL 5.6 slower than MySQL 5.5?
February 18, 2013 By 23 Comments
… the overhead, especially for simple queries. I ran small (1M rows) benchmark so data was well fitting in memory and Read…/mysql_sandbox5610.sock –mysql-user=msandbox_rw –mysql-password=msandbox –oltp-table-size=1000000 –num-threads=1 –oltp-read-only=on –init-rng=on –max-requests=0 –max…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…tables: mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 2097152 Avg_row…
