Solved

Innodb Tuning Suggestions

Posted on 2013-05-18
7
514 Views
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.
0
Comment
Question by:jelinek
[X]
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
7 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 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.
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 334 total points
ID: 39178322
Hi!

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
https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
http://rtcamp.com/tutorials/using-mysqltuner-to-optimize-mysql-configuration/

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.


Regards,
    Tomas Helgi
0
 

Author Comment

by:jelinek
ID: 39178410
@Ray_Paseur:

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.

@TomasHelgi:

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?
0
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.

 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 334 total points
ID: 39178490
Hi!

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 :)
http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb.html
Here are some other links
http://www.kbedell.com/2011/09/30/basic-introduction-to-mysql-innodb-performance-tuning-what-are-the-best-resources/
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/


Regards,
    Tomas Helgi
0
 

Author Closing Comment

by:jelinek
ID: 39178575
Thanks for the tips on both the query and hardware side.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39178615
Please see the grading guidelines here:
http://support.experts-exchange.com/customer/portal/articles/481419
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?
0
 

Author Comment

by:jelinek
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.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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