…with highest memory available, and memory is what really really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of memory 26 EC2…I used: ## 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…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query… in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how much memory Innodb has allocated. In fact this…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What… have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key…
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 23, 2012 By 10 Comments
… simple way to do it would be to use the same random dives trick Innodb uses – You can do 100 partial random dives… short of reading it. This would get you approximate in memory fit for each index and it can be refreshed every… cases as frequently you would have only some portion in memory, but it is the portion which is queried the most…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool… not have a good use for that memory anyway. Another thing you should keep into account is Innodb allocates more memory in structures related… they usually play on the safe side, having enough “free” memory used as Cache and Buffers. Kevin Burton wrote a good post…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of your Innodb log files to be able to use a lot of memory for write buffering efficiently. Q30: Question about using solid state disks for storing ib_log* files; I know that innodb writes…:-). At the moment the server has around 21 GB of memory used for applications (of which Mysql is the main one with…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… to test different memory/dataset size ratios. But why is it important, and how does it affect how InnoDB works internally? Let me show some details. Internally, InnoDB uses two lists for flushing (writing pages from the Innodb buffer pool memory to… mostly defined by available memory and the I/O subsystem. Also, it seems MySQL 5.1 with the InnoDB-plugin, MySQL 5…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… buffer pool. In high load situations where INNODB is using most of the system’s memory the kernel can decide (incorrectly) to swap… process. Q20: We’ve migrated to using INNODB so that it operates 100% out of memory. This way every write is serial so… we could use an 8G write ahead log and tell innodb to do a full write of the database (basically dump memory to…
