Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
This question is awfully broad. Can you split it into 4 questions? (And even your bulleted questions are incredibly broad. Can you instantiate them with your specific problems?) –  Brian Ballsun-Stanton Jan 10 '11 at 0:30

5 Answers 5

up vote 8 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
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? –  Wen Ren Aug 15 at 9:19

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)

share|improve this answer

You may want to explore the following resources:

share|improve this answer

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

memory configuration

share|improve this answer
    
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? –  jcolebrand Feb 14 '11 at 15:01
    
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 –  tomaszsobczak Feb 15 '11 at 18:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.