By Riedsio

2011-01-04 18:17:50 8 Comments

Assuming a production OLTP system with predominantly InnoDB tables

  • What are the common symptoms of a mistuned/misconfigured system?
  • What configuration parameters do you most commonly change from their defaults?
  • How do you spot potential bottlenecks before there is a problem?
  • How do you recognize and troubleshoot active problems?

Any anecdotes detailing specific status variables and diagnostics would be appreciated.


@RolandoMySQLDBA 2011-02-12 03:51:29

Interestingly, in MySQL 5.5, you can now have multiple innodb buffer pools.

The parameters you care about are

In about a month, I am slated to implement 112 innodb buffer pools for a client. I'll let you know how it went.

UPDATE 2011-02-27 21:57 EDT

I found out the max value for innodb_buffer_pool_instances is 64 I decided to configure 144 GB , so I set innodb_buffer_pool_instances to 18 and innodb_buffer_pool_size to 8. I am currently loading the server with 450GB

UPDATE 2011-04-28 13:44 EDT

I tried out multiple InnoDB Buffer Pools. There was too much threading locking and contention. I changed over to a single 162GB Buffer Pool + setting read_io_threads and write_io_threads to 64 (maximum value). This worked way better.

UPDATE 2012-07-03 17:27 EDT

I learned something amazing about MySQL. If you allocate a single monolithic InnoDB Buffer Pool that is bigger that Total Installed Divided By Number of Physical CPUs, your will incite the OS to regular intervals memory swapping due to a full InnoDB Buffer Pool. MySQL 5.5's option known as innodb_buffer_pool_instances can be used to split up the buffer pool. Yesterday, I properly implemented this for the client I mentioned in my answer last year. I still have 162GB for the client's Buffer Pool. I have set the server's innodb_buffer_pool_instances option to 2 because each DB Server is dual hexacore. I was thinking of setting it to 12 but then a colleague showed me a blog from Jeremy Cole on MySQL and Swappiness. After reading it, I put it into practice immediately for my client. I ran this command

numactl --hardware

I saw a mapping of 192GB of server RAM as 96GB to each physical core. Therefore, I set the innodb_buffer_pool_instances to 2. Things are looking good right now. I will update my answer to see how this affects memory swapping for the next 2 montns.

@Wen Ren 2014-08-15 09:19:22

hi @RolandoMySQLDBA, from jeremy's blogs I can't find any connection or conclusion that set innodb instance equals to number of physical CPUs will solve the swaping problem. Does multiple innodb instances will automatically balancing between memory nodes without setting numactl --interleave=all, flushing Linux’s buffer caches or forcing the OS to allocate InnoDB’s buffer pool?

@Pacerier 2015-04-09 12:20:22

@Rolando, Nice 2x 100 GB ram. Which client is it?

@Rick James 2017-06-30 21:47:28

I thought "instances" were implemented thus: (block number) MOD (num instances) --> (which instance to put the block in). That has nothing to do with NUMA.

@Gaius 2011-01-15 17:23:53

Here is a good article on InnoDB tuning from Sun's Jenny Chen - she blogs a lot about MySQL, some of it is Solaris-specific (e.g. using DTrace) but the entire blog is full of interesting tidbits.

@Pacerier 2015-04-09 12:15:53

Who is Jenny Chen?

@Total Pusher 2020-01-12 19:49:46

I am sad, article is 404

@Matt Healy 2011-01-11 05:00:05

Firstly increase the default InnoDB Buffer Pool Size in my.cnf (I believe it default to 8MB)

You should probably set this to 75% of your RAM size (in general)

@Brian Ballsun-Stanton 2011-01-10 00:29:40

You may want to explore the following resources:

@sbczk 2011-01-05 14:09:59

What configuration parameters do you most commonly change from their defaults?

memory configuration

@jcolebrand 2011-02-14 15:01:50

Can you provide a more explanatory answer? What about them do you change? Why do you make those changes? What are your expected observations after said changes? How do you know what to change them to?

@sbczk 2011-02-15 18:07:56

out of the box mysql configuration is prepared for quickly setup myisam tables (as most of the user use it that way), besides that to have well optimized server you should choose "primary" engine to use (innodb or mysql) as its really quite hard to configure database for excelent performance for booth engines, mysql have separate buffers for myisam and innodb engines

Related Questions

Sponsored Content

2 Answered Questions

Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables

  • 2018-12-31 13:50:49
  • udhayan dharmalingam
  • 439 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql performance

2 Answered Questions

[SOLVED] Simple query returning < 10000 rows taking 3-4 seconds

2 Answered Questions

4 Answered Questions

[SOLVED] How do you identify InnoDB table corruption?

1 Answered Questions

[SOLVED] How to use LOW_PRIORITY for heavy queries?

2 Answered Questions

[SOLVED] How to tune mysql to be inmemory-like

1 Answered Questions

[SOLVED] How do you read MySQL InnoDB Monitor output?

Sponsored Content