…with highest memory available, and memory is what really really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of memory 26 EC2…the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB options innodb…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… techniques I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns…ENGINE INNODB STATUS and look for memory information block, which can use like this: ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What… other possibilities like InnoDB doing read_ahead by noticing the sequential access pattern. BKA is available in both MySQL 5.6 and… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to… MySQL 5.6, when the workload fits entirely in memory, because there is no extra cost for memory access at random locations versus memory… not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_fsyncs for your disk IO… of memory for example is unlikely to help. One helpful way to use this information is to compare systems with different memory amount having same workload. You will often see increasing amount of memory…
Post: Time for Zero Administration effort at MySQL ?
January 8, 2012 By 4 Comments
…line options (like much of Performance Schema configuration). Innodb settings alone push 100. True you do …or about other components in MySQL which makes it hard to manage memory, a lot of resource …account. Is not it silly for example MySQL would use the same sort_buffer_size whenever there …
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb… MySQL Server you may not have a good use for that memory anyway. Another thing you should keep into account is Innodb allocates more memory… want to do different VM memory adjustments. You may want to make MySQL to use Large Pages for allocating Innodb Buffer Pool and few…
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… moment the server has around 21 GB of memory used for applications (of which Mysql is the main one with “innodb_buffer…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Innodb kernel and still has 400 tickets to use. Innodb tries to limit thread concurrency allowing only innodb_thread_concurrency threads to run inside Innodb…
