…buffer_size, while MariaDB introduces a different variable to control the MRR buffer size mrr_buffer_size. Both buffer sizes default to 256K in MySQL… execution plan, is not sufficiently tuned and it is recommended to …this works only with joins and specifically with Block Access Join Algorithms. So …
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX… large sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query. I… executed, so I could do something like: SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC LIMIT…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema, t.name, i.name) as index_name, sum(data_size…size_mb from innodb_sys_tables as t inner join innodb_sys_indexes as i using(table_id) inner join innodb_buffer…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… recovery time and good performance innodb_log_buffer_size=4M 4M is good for most cases… check about tuning other options here or read one of our MySQL Presentations. Application tuning for Innodb…joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL…in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is quite…_buffer_size is optimal – if you see more than 30% of log buffer size…active database size is smaller than allocated buffer pool size so you can tune it …
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
…by other queries. MySQL Server does not…size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes ON id = index_id JOIN…this feature to tune buffer pool invalidation …
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… larger than 16K – is exact size allocated or also segment based allocation… out not buffer pool but some other important parts of MySQL process. Q20…tune the checkpointing values. HT: Hmm… we could tune the way InnoDB does the buffer… if ORDER and ORDERLINE tables are joined to form a single table),…
