CODEWITHSUNDEEP
mysqlperformance
waanders
waanders

Reputation: 9083

How-to's for MySQL InnoDB (insert) performance optimization?

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

Upvotes: 7

Views: 13969

Answers (4)

Vladislav Vaintroub
Vladislav Vaintroub

Reputation: 5663

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.

Upvotes: 4

Kevin Bedell
Kevin Bedell

Reputation: 13404

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.

Upvotes: 15

Andreas
Andreas

Reputation: 2301

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

Upvotes: 1

Poodlehat
Poodlehat

Reputation: 360

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.

Upvotes: -2

Related Questions