Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no constraint of disk space. Which default settings should I change to optimize for better performance, reliability, and possible point-in-time backups [high availability].

Edited

At present, whenever I run "Optimize Tables" via Maintenance on MySQL Administrator, it shows:

Table does not support optimize, doing recreate + analyze instead

on all tables. All of my tables are InnoDB, but why they don't support Optimize?

share|improve this question
    
that edit warrants a separate question, imo. Take a look at the OPTIMIZE TABLE documentation: dev.mysql.com/doc/refman/5.5/en/optimize-table.html InnoDB supports the command, but the process is different than MyISAM –  Derek Downey Aug 4 '11 at 14:51
add comment

3 Answers

up vote 7 down vote accepted

The way to tune InnoDB is centered around

  • InnoDB Buffer Pool : It caches data pages and index pages. The amount of data and index you can cache is not a function of disk space constraints but a function of available memory and diskspace currently used by InnoDB.
  • InnoDB MetaData : By default, the file ibdata1 normally houses anything and everything InnoDB. That would include data pages, index pages, table metadata, MVCC data.

Here is a formula I have used for the past 5 years to compute the InnoDB Buffer Pool based on diskspace used by InnoDB data and index pages:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;
  • Use (SELECT 0 Power1024) for Bytes
  • Use (SELECT 1 Power1024) for KB
  • Use (SELECT 2 Power1024) for MB
  • Use (SELECT 3 Power1024) for GB
  • Use (SELECT 4 Power1024) for TB (Email Me if you have TerraBytes of RAM)

Of course, I said a function of available memory and diskspace currently used by InnoDB. From here, just use common sense. The recommended number from the above query SHOULD NOT EXCEED 75% OF INSTALLED RAM !!! That's the simplest rule-of-thumb for sizing the InnoDB Buffer Pool.

You should also set innodb_flush_method to O_DIRECT since it will provided stable synchronous writes of InnoDB. I have also written a post on how to optimize Disk Storage for InnoDB.

With regard to the message Table does not support optimize, doing recreate + analyze instead, the reason why you get that error message is the fact that the storage engine is InnoDB. Mechanically, OPTIMIZE TABLE just copies the table to a temp table and performs ANALYZE TABLE.

In reality, ANALYZE TABLE against InnoDB is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that same conclusion as well.

Here are other posts I have made over the year about InnoDB Tuning

share|improve this answer
    
My PC has 2GB RAM and InnoDB_Buffer_Pool_Size is by default set to 6M. I have increased it to 500M. Any suggestions? –  RPK Aug 5 '11 at 5:46
    
Set it 1536M which is 1.5G since that is 75% of installed RAM –  RolandoMySQLDBA Aug 5 '11 at 11:08
    
I ran your query, and it gives an output 45G. The RAM on my server is 8G.. Would I absolutely have to increase my RAM ? Or there are some conditions to the above query.. –  Stewie Mar 6 '12 at 18:56
    
@Stewie If you have 45G of InnoDB, 60GB of RAM is what you need if you have the budget. Since you only have 8G, 75% of installed RAM (6G) is adequate. –  RolandoMySQLDBA Mar 6 '12 at 18:59
add comment

Optimize comes in a lot of colors.

I think your first step should be to decide what "optimize" means for you. For some people, it means "fastest SELECT queries". For others it means "best balance between SELECT performance and INSERT performance". For still others, "fastest INSERT performance".

You need to decide what your criteria are and how you're going to tell whether your changes help before you start tuning.

Then put your configuration files and startup options under version control, and start experimenting. Document whose advice you follow, and where you found it. (Advice changes over time, as the codebase and hardware changes.) Put those documents under version control, too.

share|improve this answer
add comment

Here is an older article on the important variables to 'coarse tune'. The most important one probably being innodb_buffer_pool_size

I would highly recommend upgrading to MySQL 5.5 (if you aren't already running it). They made quite a few changes to InnoDB performance. They even provide a mainstream section on how to optimize for InnoDB now that it's the default engine: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html

As for point-in-time backups, you'll want to look at the binary log. The basics of enabling this is to set the log-bin variable in your my.cnf

share|improve this answer
    
I have upgraded to MySQL 5.5. –  RPK Aug 4 '11 at 14:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.