Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

Innodb Tuning Suggestions

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
jelinek
Asked:
jelinek
  • 3
  • 2
  • 2
3 Solutions
 
Ray PaseurCommented:
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
 
Tomas Helgi JohannssonCommented:
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
 
jelinekAuthor Commented:
@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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Tomas Helgi JohannssonCommented:
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
 
jelinekAuthor Commented:
Thanks for the tips on both the query and hardware side.
0
 
Ray PaseurCommented:
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
 
jelinekAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now