Shulou(Shulou.com)06/01 Report--

What is innodb in mysql? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Introduction to innodb

InnoDB, one of the database engines of MySQL, is now the default storage engine of MySQL and one of the standards for publishing binary for MySQL AB. InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the most important feature of InnoDB is that it supports ACID-compatible transaction (Transaction) function, similar to PostgreSQL.

Detailed introduction

The preferred engine for transactional databases that supports ACID transactions and row-level locking. InnoDB is designed for maximum performance when dealing with large amounts of data. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its tables & indexes in one tablespace, which can contain several files (or raw disk partitions). This is different from the MyISAM table, such as where each table in the MyISAM table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB. InnoDB is included by default in the MySQL binary distribution. Windows Essentials installer makes InnoDB the default table for MySQL on Windows.

InnoDB stores its tables & indexes in one tablespace, which can contain several files (or raw disk partitions). This is different from the MyISAM table, such as where each table in the MyISAM table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB.

Performance skills

1. If Unixtop or Windows Task Manager (Task Manager) shows that the CPU occupancy rate of the service is less than 70%, (shows that the CPU usage percentage with your workload is less than 70%,) your system bottleneck may be on disk read and write. Maybe you commit a lot of transactions, or the buffer pool (buffer pool) is too small. It helps to make the buffer pool larger, but be careful not to be more than 80% of physical memory.

two。 Several modifications are included in a transaction. If a transaction makes changes to the database, InnoDB must flush the log to disk when the transaction commits. Because the rotation speed of the hard disk is usually at most 167 rpm, the limit of transactions committed is also 167 per second users as long as the disk does not deceive the operating system.

3. If it doesn't matter if you lose the most recent transactions, you can set the parameter innodb_flush_log_at_trx_commit to 0 in the my.cnf file. InnoDB always tries to flush the log once a second anyway, although the refresh is not guaranteed.

4. Make the log files (log files) larger so that the sum of the log files is exactly as large as the buffer pool (buffer pool). When InnoDB runs out of space in the log file, it has to write the modified contents of the buffer pool to disk at a point in time. Small log files may cause unnecessary disk writes. But the disadvantage of large log files is that it takes a long time to recover the data.

5. Also, try to make the log buffer as large as possible, say 8 MB.

6. If you want to store a longer string or field that may contain a large number of NULLs, use a Char field instead of VARCHAR. A CHAR (n) field always uses n bytes to store data, even if the string is short or a NULL value. Smaller tables are more suitable for buffer pools and can reduce disk Imax O.

7. (suitable from 3.23.41 or later) in some versions of Linux and Unixes, using Unixfsync or other similar methods to flush files to disk is unusually slow. The default method for InnoDB is fsync. If you are not satisfied with the disk write performance of your database system, you can try setting innodb_flush_method to O_DSYNC in my.cnf, although the O_DSYNC option looks slow on most systems.

8. When importing data into InnoDB, make sure that MySQL does not have autocommit=1 open. Otherwise, each insert statement flushes the log to disk. In the first line of your SQL import file, add

Set autocommit=0; and add commit on the last line

If you use the mysqldump option-- opt, you will get a dump file that quickly imports the InnoDB table, and you can even stop using the set autocommit=0; mentioned above. Commit; .

9. Beware of insert-complete big rollback (roolback): InnoDB uses insert buffering to reduce disk Icano when inserting, but does not use such a mechanism in the corresponding rollback. A disk-bound rollback may take 30 times the corresponding insertion time. If an out-of-control rollback occurs, you can check the techniques in Chapter 6.1 to stop it.

10. Also be careful of the operation of a large disk-bound. Use DROP TABLE or TRUNCATE (above MySQL-4.0) to empty a table instead of using DELETE FROM yourtable.

This is the answer to the question about what innodb is in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.