Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Innodb Tuning Suggestions

Posted on 2013-05-18
Medium Priority
Last Modified: 2013-05-19
I'm going to be working with a MySQL DB that is pretty large in size.  I've never used Innodb before as the storage engine and was looking for some tips on performance tuning.  I understand the concepts of Innodb and have read a lot about it but have no practical experience with it.

Ultimately I'm looking to maximize read/write performance, allocate as much memory to Innodb/MySQL without causing the OS to stall, and looking for best practices on file sizes/etc.

Thanks in advance.
Question by:jelinek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 498 total points
ID: 39177986
Ensure that you have an index on every column used in WHERE, ORDER, GROUP, JOIN.

Use LIMIT on all queries that do not absolutely require a complete table scan.

Never use SELECT * -- instead SELECT the columns you need and no others.

Use EXPLAIN SELECT on all complex queries (queries that get data from more than one table).

There are other suggestions, but start with these and see how things are going.  Then post a new question here at EE if these ideas do not give you satisfactory results.
LVL 25

Accepted Solution

Tomas Helgi Johannsson earned 1002 total points
ID: 39178322

If your MySQL database is running on a Linux system I recommend using the mysqltuner tool which should be available in your linux repository
or here

That little script is very useful and gives you recommended values based on the data and available memory. Sometimes though you will need to be careful as those values can cause the linux system to stall  which is not what you want. If you see recommended values go beyond available memory.
Let's say you have a 500GB database but only 16GB memory and your InnoDB bufferpool is currently 2GB  and the InnoDB bufferpool suggestion says
         innodb_buffer_pool_size (>=500GB)
you obviously won't set it to to that size but you will probably want to double or triple the bufferpool size.

    Tomas Helgi

Author Comment

ID: 39178410

I'm more on the system admin side of things.  The dev team has a large rails app which queries MySQL for requests.  Besides having indexes set on columns to speed things up the queries that are written are based upon their code which I have little control over, but can provide suggestions.  Thank you for the tips.


Thanks for the tip on the innodb_buffer_pool_size.  I'm starting  a new position so I'm not sure what our total memory is on the virtual servers but once I get into the environment I can evaluate total memory, how it's allocated across the system, and adjust the buffer_pool_size from there.  Are there any other tips you can suggest for tuning an existing Innodb deployment for maximum performance?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 1002 total points
ID: 39178490

It all depends on the workload that running on your InnoDB database. If your virtual server that hosts the mysql database are running on a Linux system then mysqltuner should give you an idea on what mysql parameters should be tuned and to what size.  

And it's always good idea to read the manual :)
Here are some other links

    Tomas Helgi

Author Closing Comment

ID: 39178575
Thanks for the tips on both the query and hardware side.
LVL 111

Expert Comment

by:Ray Paseur
ID: 39178615
Please see the grading guidelines here:
A should be the default grade awarded unless the answer is deficient.
When closing the question, the asker should explain why a B grade was awarded.
So what was wrong with the answers?

Author Comment

ID: 39178618
Sorry about that, I'm new here and didn't read the grading guidelines.  Both answers provided useful information and were helpful.  How can I change the grading to an A?

Again, apologies for that.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question