Solved

Innodb Tuning Suggestions

Posted on 2013-05-18
7
496 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 108

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 24

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

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 108

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now