MySQL myisam/innodb High concurrency optimization experience

Recently done an application, functional requirements is very simple, is the Key/value form of storage, simple insert/select, without any complex query, the only problem is the amount is very large, if currently put into use, the initial single-table INSERT frequency of about 20Hz (Times/sec, I like this unit, reminds me of the domestic AC power is 50Hz), but I estimate there will be 500hz+ peak. Current work results, rated power 200Hz (CPU occupied 10–20,load avg = 2), Maximum power 500Hz (at this time load avg > 20, it is obvious that only temporarily, should be in the presence of such a load prior to dismantling the table)

INSERT DELAYED into

Start with the insertion of the data. If the results can be tolerated after a few seconds, you can INSERT DELAYED into, because in my structure there is no need to insert/select the same key frequently, because SELECT I was blocked with Memcached, unless Memcached Hang up, or the data is too old to expire, will go to SELECT. Also note that if PHP does not need to care about the return results of the MySQL operation, it should use unbuffered query, simply put, after you submit the query, you do not have to wait for MySQL to return information to continue after the execution of the PHP instructions, the use of Mysql_unbu Ffered_query instead of mysql_query, if used Mysqli class, should use Mysqli->query ($sQuery, Mysqli_use_result);

If SHOW processlist, you can see the process with the user name DELAYED, the number of processes equals the number of INSERT DELAYED tables, because table-level locks exist, and more than one DELAYED process per table is meaningless

There are three MY.CNF configurations for this feature, and I'll set the following values

Delayed_insert_limit = 1000
Delayed_insert_timeout = 300
Delayed_queue_size = 5000

Connection

Some people say, if the error connection number is too large, you turn the max_connections big OK, if only say so without speaking the reason, is completely nonsense, you tune into 1M certainly will not report Too many connections (but should be reported memory overflow and so on), but if so Why does MySQL give this parameter?

A very useful formula that I have seen

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

Previously only very vague concept, should be set up very big, but not too big, specific how much suitable, know this is clear. InnoDB's formula is more complicated than this.

Innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ max_connections * 2MB

There is also a seemingly useful parameter back_log that gives me a sense of connection pooling, and it does work, and I don't know how much memory will be consumed if it is set up, but not much.

Key_buffer_size

Many articles tell you that the bigger the better, to allocate about half of the physical memory, so do not usually have problems, but certainly not optimal, and even can be said to be unreasonable head-how much memory should be determined according to demand, and not no matter what machine, cut off half of the memory used as key_buffer_size-- Sometimes it may not be enough, and sometimes it can be wasted.

In fact, the most critical indicators, or see show GLOBAL STATUS when the key_blocks_unused, as long as there are remaining, it means key_buffer_size useless. Some people say to see the ratio of key_reads and key_read_requests, at least to reach 1:100. This can be measured as a result, but not accurate, because most requests are new to the cache when the server is just booting up, and the cache hit is not high enough to run steady (hours later) to observe. My personal advice is to see key_blocks_unused.

If it does not take a long time to debug in the run, give a simple calculation method, the database fills up, to achieve the maximum design time, to see how much space index, and then the index size of all the tables added up, is the maximum value that key_buffer_size may reach, of course, but also to leave some room, Take a 2 or 3 or something like that.

This is when I do the test phpMyAdmin, can see Key_buffer_size was wasted too much

OPTIMIZE TABLE

It's good to optimize, but it will lock the table and it's worth making a tradeoff. Take me now as an example of this table, there is a text field, 7 million records, 1.5G size, optimization time of about two minutes, optimized performance increased by 50%, and the size of the table to 1.4G, but as the table is frequently rewritten, about a day later back to the previous speed, so it seems to me is not worth it.

Query Cache

Because each write operation Query Cache will be emptied, in addition to very special circumstances (large-volume reading, a small amount of writing, but even if this should also be multi-memcached) absolutely no need to use this, the query_cache_size set to 0 off this function.

InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If the rollback of a thing causes incomplete rollback, it does not have atomicity. If executing a lot of select,myisam is a better choice.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.

For INNODB types that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance.

Myiasm is a new version of the Iasm table, with the following extensions:

1. Portability at the binary level.
2, NULL column index.
3. There are fewer fragments of variable-length rows than the ISAM table.
4, support large files.
5, better index compression.
6, better key code statistical distribution.
7, better and faster auto_increment processing.

InnoDB is the first engine on MySQL to provide foreign key constraints, in addition to providing transactional processing, InnoDB also supports row locks, providing consistent, non-lock reads like Oracle, which increases the number of concurrent read users and improves performance without increasing the number of locks.

InnoDB's design goal is to maximize performance when dealing with large volumes of data, and its CPU utilization is the most efficient of all other disk-based relational database engines.

InnoDB is a complete database system placed in the background of MySQL, InnoDB has its own buffer pool, can buffer the data and index, InnoDB also holds the data and index in the table space, may contain several files, this and MyISAM table is completely different, in MyISAM, The table is stored in a separate file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB.

InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.

MyISAM is the MySQL default storage engine.

Each of the MyISAM tables is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). The index file is an myi (myindex) extension.

Because MyISAM is relatively simple, it is better than innodb in efficiency. Using MyISAM for small applications is a good choice.

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle.

The following are some of the details and the specific implementation differences:

1. InnoDB does not support indexes of fulltext types.
2. InnoDB does not save the exact number of rows in the table, that is, when executing select COUNT (*) from table, InnoDB will scan through the entire table to calculate how many rows, but MyISAM simply reads out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields.
4. When you delete from table, InnoDB does not reestablish the table, but deletes one row at a time.
5, LOAD table from the master operation on InnoDB is not working, the solution is to change the InnoDB table to MyISAM table, import data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign keys) of the table does not apply.
6, InnoDB table row lock is not absolute, if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the whole table, such as the Update table set num=1 where name like "%aaa%"

In summary, any kind of table is not omnipotent, only appropriate for the business type to the appropriate table type, to maximize the performance advantage of MySQL.

The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.

I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

The reasons are as follows:

1. First of all, I am currently hosting most of the projects are read and write less projects, and MyISAM reading performance is much stronger than InnoDB.

The index and data of 2.myisam are separate, and the index is compressed, and the memory usage is improved a lot. Can load more indexes, and InnoDB is the index and data is tightly bound, no compression will cause innodb than MyISAM volume

3. From the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table is not normal use, this time the superiority of the MyISAM is reflected, casually from the day of the copy of the compressed package out of the corresponding table file, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with the export xxx.sql mechanism, because the smallest database instance on my platform has a basic amount of data of dozens of G

4. From the application logic I contacted, select COUNT (*) and order by are the most frequent, probably accounted for more than 60% of the total SQL statement operation, and this operation InnoDB actually lock table, a lot of people think InnoDB is row-level lock, that just Where the primary key is valid, the non-primary key will lock the full table.

5. There are often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.

6. If and MyISAM than insert write operation, InnoDB also can not reach MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.

7. If using MyISAM, the merge engine can greatly speed up the development of the application, as long as they do some select count (*) operations on the merge table, which is ideal for business tables of a certain type (such as logs, survey statistics) of a large project with a total of about hundreds of millions of.

Of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but

If I start from the database platform application, I will still prefer MyISAM.

PS: Some people may say that you myisam can not resist too much write operation, but I can make up by the architecture, say I have a database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million queries per day). My overall database server load averaged around 0.5-1.

MyISAM and InnoDB optimizations:

Key_buffer_size – This is very important for the MyISAM table. If you use only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load-remember, the MyISAM table caches the data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are exploited-. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.

Innodb_buffer_pool_size – This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some of the rules that apply to Key_buffer are-if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.

innodb_additional_pool_size – This option does not have much impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs to allocate.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server.

innodb_log_buffer_size default settings in the case of moderate-strength write loads and shorter transactions, server performance is also possible. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the less its value.

innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head big? You may have forgotten to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's also relatively insecure. When the-mysql server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.

Table_cache -The cost of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table) and increase its value if the number of connections is larger. I've seen cases set to 100,000.

Thread_cache -The cost of creating and destroying threads can be significant, as each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation.

Query_cache -This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it.

sort_buffer_size – If you have only a few simple queries, then you don't need to increase its value, even though you have 64GB of memory. Maybe it will degrade performance.

Part of content Source: https://soulogic.com/archives/347

MySQL myisam/innodb High concurrency optimization experience