MySQL memory usage and some of the considerations needed in optimization

1. Reading data from memory is a microsecond level. Read from disk is the millisecond level. The two differ by an order of magnitude. So to optimize the database, the first thing to do is to optimize the IO.
2.key_buffer_size[Global] Sets the size of the memory area to cache the index of the MyISAM table. Because MyISAM only caches the index in memory, it does not cache data in memory, so if memory permits, make this parameter sufficient to accommodate all indexes of all MyISAM to improve performance. Also, on the MyISAM table, try to limit all query conditions to the index so that the cache can improve the lookup efficiency for us.
3.bulk_insert_buffer_size[Thread] is used only in MyISAM to temporarily cache data when inserting data. When we use the following write statement, we use this memory region to help write the data file in bulk:
Insert ... select ...
Insert INTO ... values ...
Load data infile ... into ...
4.innodb_buffer_pool_size[GlobalWhen we use the InnoDB engine, this parameter may be one of the most critical parameters affecting performance. It is used to set the index of the cache InnoDB and the size of the memory area of the data block.
Simply put, when we manipulate the InnoDB table, all the data returned and all the indexes used in the process of finding the data will go through this memory block.
5.innodb_additional_mem_pool_size[Global] Sets the size of the memory area used by the InnoDB storage engine to hold data dictionary information and some internal data structures. So, when we're in a MySQL instance
When you include many database objects (such as many tables), you need to adjust the size of the parameter appropriately to ensure that all data is in memory to ensure efficiency. Whether the memory of this parameter is sufficient or is relatively easy to know. Because when I was a little boy,
MySQL logs warning to the error log.
6.innodb_log_buffer_size[GlobalMemory used by the]INNODB office. InnoDB write the transaction log, in order to improve performance, write the cache first, and then write to the logfile.
7.innodb_max_dirty_pages_pct[Global] to control the ratio of dirty page (which has been modified but not yet written to the data file) in the InnoDB buffer pool.
The larger the value, the less memory-to-disk write operations will be. So you can reduce disk IO to a certain extent. But when this value is very large, if the database crash, then the restart time may be very long. Because there will be
A large amount of transactional data needs to be recovered from the log file and written to the data file. At the same time, excessive proportional value will also cause the flush operation to write the data "too hard" after reaching the upper limit of the proportional setting, resulting in dramatic performance fluctuations.
8. The index scan is not necessarily better than a full table scan when we want to remove most of the data from the entire table.
9, MySQL is a row-based database, and the data read is based on page. Rows are stored in each page. If the amount of data in each row is reduced, the number of rows stored in each page increases. More rows can be accidentally taken out of each IO.
In turn, processing the same data will reduce the page processing. This is also the reduction in the number of IO. Improve performance directly. In addition, because our amount of memory is limited, the number of rows per page increases, which is equal to increasing the
The amount of cached data per block can also increase the hit rate.
10, we cannot change what data to store, but how to store the data we can spend a little thought.
1) Number type. As a last resort, do not use double type. In addition to the larger space, there is also the problem of precision. Similarly, fixed-precision decimals do not use decimal, which is recommended to be multiplied by a fixed multiple and converted to integers for storage.
Saves storage space without any additional maintenance costs. For the storage of integers, it is recommended to separate tinyint/int/bigint, they have a certain gap in the storage of data space.
2) Character type. As a last resort, do not use the text type. Its processing efficiency is lower than char and varchar. The fixed-length field suggests a char type. Variable length with varchar. varchar must not be arbitrarily given a large length. Because of the different length range, MySQL will be handled differently. One of the blogs is about how varchar is handled. Assuming that varchar (1000) is declared, then when MySQL stores this data on disk, assuming the data length is 45, then the disk occupies about 45 of the space. But when the data is in memory, it takes up to 1000 space. Wasted a lot.
3) Event type. Use timestamp as much as possible. Storage footprint is only half of the datetime type. For types that need to be accurate to a certain day, the date type is recommended. It requires three bytes because it is stored. Less than timestamp.
It is not recommended to use int to store a Unix timestamp, which is not intuitive and does not provide any benefit.
4) Properly redundancy the fields in the table. For example, put a summary of an article together with the article Information table, not with the article detail table.

MySQL memory usage and some of the considerations needed in optimization