…: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive… = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
… ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock… no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock_mode X insert…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is…. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was… values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x…-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G…. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 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…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock… 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD… need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32… innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
… MySQL RDS, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE select my_id+1,.,.,.,.,.,.,. from SAME_TABLE where myid = input… and Writing in same table. For 22,518,912 rows it is taking 14min ( avg_row_size is 136 around 2.85…
Post: Percona welcomes Drizzle 7.1
April 10, 2012 By Leave a Comment
… API (experimental) Some of the Percona InnoDB patches have been merged Since Drizzle … changed, 473733 insertions(+), 210435 deletions(-) The size of the Drizzle kernel (drizzled directory …UNIX socket protocol, javascript plugin, authentication via tables (similar to MySQL), RabbitMQ, regex policy, …
