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?

3 Answers
3

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.

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.

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.

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.