How-to for MySQL InnoDB (insertion) performance optimization?

Question

I am still struggling with the performance of my MySQL database using the InnoDB engine. Especially data insertion performance, poor query performance.

I searched for information, instructions, etc., but I found most of the information quite deep. Can I find somewhere in the network some basic information for newbies, a starting point for optimizing performance? The first, most imported step to optimize InnoDB is explained in a less complicated way.

I am using the windows platform

Answer 1

I used to manage several very large MySQL databases (e.g. 1TB +). They were huge, implacable animals with endless appetite to cause me stomach problems.

I read everything I could find in MySQL Performance Tuning and innodb. Here is a summary of what helped me:

• The book High Performance MySQL is good, but still available to you.
• Blog The MySQL performance blog (this link refers to their posts tagged with "innodb" tags) is the most useful shared resource I have found on the net. They are well versed in many issues with setting up innodb. From time to time he gets "ranty", but overall it's great. There is still a link to InnoDB Performance Optimization Basics , which are good.
• The last thing I did to find out was just to read the MySQL docs themselves. I read how each last parameter works, changed them on my server, and then did basic profiling. After a while, you will find out what works by executing large queries and seeing what happens. Here's a good place to start: Configuring and troubleshooting InnoDB

In the end, it just experiments and works through things until you gain enough knowledge to know what works.

Answer 2

For beginners: innodb_flush_log_at_trx_commit = 0, if you can afford to lose up to 1 second of your work if the server crashes. This is a switch between performance and reliability, but it will significantly improve recording performance. If you can afford to use the cache for backup, use it.

In particular, on Windows and for write performance, MariaDB 5.3 might be a better idea than having MySQL from Oracle, since MariaDB can make better use of asynchronous I / O on Windows. I wrote a note about this some time ago here , on a standard synthetic benchmark it performs up to 500% better than in a MySQL 5.5 warehouse (see the pictures at the end of the note).

However, the first and most important thing that kills performance is flushing the disk. This is solvable if you relax longevity with the * innodb_flush_log_at_trx_commit * parameter, with a backup write cache with battery. You can also use larger transactions, they reduce the number of disk flushes.

Answer 3

Try the MySQL Primer script: http://day32.com/MySQL/

Answer 4

I did not use the "network", I used books. :)

The book I used to learn MySQL is The Beginning of MySQL from Wrox Press, Robert Sheldon and Jeff Moz. Chapter 15 covers some of the basics of optimization. I really liked this book and think it will be a good read, and I was a reference to # 1. But this is not a very specific storage mechanism.

I have another book, Pro MySQL from apress, which contains much more detailed information about specific storage mechanisms, but it is also much more difficult to read. However, a good recommendation.