7

I'm still struggling with the performance of my MySQL database using the InnoDB engine. Especially the performance of data insertion, minor the performance of running queries.

I've been googling for information, how-to's and so on, but I found most information rather profound matter. Can I find somewhere on the net some basic information for "newbies", a starting point for performance optimization? The first, most import steps for InnoDB optimization, explained in a less complicated way.

I'm using the Windows platform

waanders
  • 8,907
  • 22
  • 70
  • 102

4 Answers4

15

I used to manage a couple very large MySQL Databases (like, 1TB+). They were huge, unforgiving beasts with an endless appetite to cause me stomach problems.

I read everything I could find on MySQL Performance Tuning and innodb. Here's a summary of what helped me:

  1. The book High Performance MySQL is good, but only gets you so far.
  2. The blog MySQL Performance Blog (this link is to their posts tagged 'innodb') was the most useful overall resource I found on the net. They go into detail on a lot of innodb tuning issues. It gets 'ranty' at times, but overall it's great. Here's another link there on InnoDB Performance Optimization Basics that's good.
  3. The last main thing I did to learn it was to simply read the MySQL Docs themselves. I read how every last parameter works, changed them on my server and then did some basic profiling. After a while you figure out what works by running big queries and seeing what happens. Here's a good place to start: InnoDB Performance Tuning and Troubleshooting

In the end, it's just experimenting and working through things until you gain enough knowledge to know what works.

Kevin Bedell
  • 13,254
  • 10
  • 78
  • 114
  • Thanks for your detailed answer. I'm not going to work very extensively with MySQL and unfortunately I don't have so much time to study all this matters thoroughly, but I've some good starting points now – waanders Oct 03 '11 at 07:59
  • 1
    @Kevin, You read how every setting works? How's that possible when there's as much as [622 server variables](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html)? And that list only includes some of the variables that are [specifiic to InnoDB](http://stackoverflow.com/a/29539807/632951). – Pacerier Apr 09 '15 at 13:34
  • I read through all the docs for the version I was using. It was a while ago now, but yeah there were a lot of them. We built this over the course of a couple years. – Kevin Bedell Apr 10 '15 at 15:16
4

For newbies: innodb_flush_log_at_trx_commit=0, if you can afford to lose up to 1 second of your work if server crashes. This is the performace vs reliability tradeoff, but it will improve your write performance hugely. If you can afford battery backed write cache, use it.

Specifically on Windows, and for write performance, MariaDB 5.3 might be a better idea than stock MySQL from Oracle, since MariaDB is able to better utilize asynchronous IO on Windows. I wrote a note about it some time ago here, on standard synthetic benchmark it performs up to 500% better than stock MySQL 5.5 (see pictures at the end of the note).

However, the first and foremost thing that kills performance is the disk flushing. This is solvable if you relax durability with *innodb_flush_log_at_trx_commit* parameter, of with battery backed write cache. Also you might consider using larger transactions, they reduce the amount of disk flushes.

Vladislav Vaintroub
  • 5,308
  • 25
  • 31
1

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

Andreas
  • 2,211
  • 1
  • 18
  • 36
-2

I didn't use the 'net, I used books. :)

The book I used to learn MySQL is "Beginning MySQL" from Wrox Press, by Robert Sheldon and Geoff Moes. Chapter 15 goes into some basics of optimization. I liked this book a lot and think it would be good reading and has been my #1 reference. But it isn't very storage engine specific.

I have another book, Pro MySQL from apress that goes into a lot more detail about particular storage engines, but it is also much harder to read. Still a good reference though.

Poodlehat
  • 360
  • 1
  • 2
  • 9
  • Thanks, maybe I've to buy this book(s). But for most programming issues I can use the net, there should be some information somewhere ... – waanders Sep 30 '11 at 12:38