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.
jelinekAsked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
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
 
Ray PaseurConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Tomas Helgi JohannssonConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.