InnoDB Performance Tuning Basics

The original address of this article is here: http://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics/, here is the translation.

-----------------------------------------------------------This is a split line------------------------------------------------------- ----

November1, 2007 by Peter Zaitsev

Note: The advanced knowledge of this article is here: http://www.mysqlperformanceblog.com/2013/09/20/innodb-performance-optimization-basics-updated/ (Translation of this text: http://blog.csdn.net/zhu19774279/article/details/38645663)

I'm going to be the ones who come to our interview, always like to ask some basic questions, such as: if you have a 16G memory database server, used to deal with the regular Web application requests, the database is MySQL, the table engine is InnoDB, and the amount of data is very large, how do you configure. Interestingly, most people are not giving any useful advice. So I decided to announce the answers I wanted to hear, including how to adjust the hardware, OS, and apps. I call this article the title "InnoDB Performance Tuning Base" because these configurations in this article are universal and, in most cases, have good results. Of course, if you want the best performance, you have to "specific situation analysis."

1. Hardware

If your InnoDB database is facing a very large amount of data, then memory size is a very important factor. Today it seems that 16g-32g is the most effective value. With 2 dual-core CPUs enough, 2 quad-core CPUs can see larger loads even on scalable issues, although this depends more on the program. Io is the third element of hardware: A high speed direct storage and a RAID card with a battery backup cache. Usually 6-8 hard drives are enough, of course, sometimes they need more. Note the new 2.5″sas hard drive, they are small but faster. RAID10 suitable for data storage and read-oriented situation, if you want some redundancy, RAID5 can also work well, but pay attention to random write RAID5. I do not understand the basic hardware, so this paragraph of the translation I have no grasp, I hope the passing of the expert can help modify. Here is a list of the original: If you have large Innodb the database size Memory is paramount. The 16g-32g is the cost efficient value. From CPU standpoint2*dual core CPUs seems to do very as is, while with even just two Quad Core cpusscalability issues can B E observed on many workloads. Though This depends onthe application a lot. The third is IO subsystem–directly attached storagewith plenty to spindles and RAID with battery ' up cache are a go OD bet. Typically can get 6-8 hard drives in the standard case and often it isenough, while sometimes to more. Also note new 2.5″sas hard drives. They are tiny but often fasterthan. RAID10 works, for the data storage and for read-mostlyCaseswhen you still would like some redundancy RAID5 can work pretty-as wellbut beware of Random writes to RAID5.).

2. Operating system

First: Use a 64-bit operating system. We can still see a 32-bit operating system running on hardware that supports 64-bit and has large memory. Don't do that. If you are using Linux, install LVM for your database directory for more efficient backups. In most cases EXT3 is a good choice, of course, if you run in particular roadblocks, you can try XFS. If you use MySQL's innodb_file_per_table parameter and have a lot of tables, then setting up Linux noatime and nodiratime can improve performance slightly. Also make sure that your operating system will not have MySQL swap out in the event of low memory.

3. MySQL InnoDB Configuration

innodb_buffer_pool_size: The most important item in a parameter. The 70-80% of memory is a security value. I'll set this value to 12G at 16G. Attached: If you want to know more details, you can read this article: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

innodb_log_file_size: It depends on how fast you need to recover the data, but in general, 256M is a good balance between recovery time and high performance.

innodb_log_buffer_size=4m: In most cases, 4M is a good choice, and if you want to process large binary data (large blobs), you can turn this value up a bit.

innodb_flush_log_at_trx_commit=2: If you're not so focused on acid, you're also allowed to lose the last two seconds of the entire operating system crash, you can set it to 2. The change in this value has a very noticeable effect, especially for some short write transactions.

innodb_thread_concurrency=8: Even if the current innodbscalability fixes, the restrictions on concurrency are limited. The real situation will be slightly higher or slightly lower than this, depending on your application, of course, starting with 8 is a good choice.

innodb_flush_method=o_direct: Avoid double buffering and reduce the pressure of swap (swap), in most cases, this configuration can improve performance. Note that if you do not have a RAID card cache with a battery backup, write IO pressure may increase.

innodb_file_per_table: If you don't have a lot of tables, consider this option so you don't have to worry about having a primary table space that can't be recycled and uncontrolled growth. This option is MySQL 4.1-plus and is now stable enough.

Also check to see if your program supports running in read-commited isolation mode-if you can-set it to the default value of transaction-isolation=read-committed. This option will improve performance, especially in the 5.0 lock and the upcoming 5.1, as well as row-level replication.

There are many other configurations that can be adjusted, but today we are only concerned with InnoDB. You can also read these two places yourself: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/, http://www.mysqlperformanceblog.com/mysql-performance-presentations/.

4. Application Tuning

For applications, especially those previously using the MyISAM engine, the following adjustments are required.

First, ensure that transactions are used when updating operations (update), so that both consistency and better performance can be achieved. Second, if your program has any write operations, you should consider the possible deadlock. Again, check your table structure to make sure they can get performance improvements from the InnoDB property, for example: aggregate data with primary key, all indexes have primary key (so to ensure primary key is short), use primary key for quick lookup (try to use join), large uncompressed index (make primary key as simple as possible). (This passage is not quite understood: clustering by primary key, has primary key in all indexes (Sokeep primary key short), fast lookups by primary Keys (try to use the it in joins), large unpacked indexes (try to is easy on indexes). Through the above configuration, you can ensure that your database performance than most use the MySQL default configuration, no battery preparation Cache, do not optimize OS, applications, and use the MyISAM of the person's better.

-----------------------------------------------------------This is a split line------------------------------------------------------- ----

The original text is written in 2007.11.1, so some of the content will look strange, such as the original text in the "forthcoming 5.1" and other statements.

My English level is general, to Linux, hardware, MySQL is very limited to understand, errors are unavoidable, but also hope that passers-by passing by can correct me.