MySQL optimization (2)-number of tables and temporary tables

Author: skate
Time: 2012/06/30

MySQL optimization (2) --- number of tables and temporary tables

 

1. How to open and close tables in MySQL

How to open a table in MySQL
The MySQL Open table is implemented through a file descriptor. MySQL is multi-threaded. When a concurrent session Open table is opened, MySQL opens the table for each session separately, this part of information is cached in the memory for later open tables, which can greatly improve the speed of open tables. for opening a MyISAM table, MySQL must allocate two file descriptors at the same time, one is the data file descriptor (which cannot be shared between threads) and the other is the index file descriptor (which can be shared between threads ), for example, if two threads access the same table or a thread accesses a table twice in a query, the table is opened twice, while other open tables only need one file descriptor.

Which parameters affect the number of open tables?
Table_open_cache and max_connections affect the number of opened tables in MySQL. How can these two parameters be set?

Max_connections: The maximum number of concurrent connections supported by MySQL
Table_open_cache: This parameter is very important for faster open tables. For example, for 200 concurrent connections, table_open_cache = 200 × n (the maximum number of tables involved in join in a query, it may also be a partition table). If it is a MyISAM table, table_open_cache = 200 × (n × 2), you also need to consider the file descriptor of the burst temporary table; when the OS supports the number of opened files, if table_open_cache is set to a relatively high value, MySQL uses all file descriptors, and the connection is denied and an error occurs in the query.

How much is table_open_cache suitable? In addition to the theoretical reasoning above, we can also monitor "opened_tables" based on state variables. This state variable indicates the number of open tables starting from the server service, you can accurately set table_oprn_cache based on this status variable.

How does MySQL colse table
MySQL opens a table and puts it in Table cache. When will MySQL close a non-existing table and delete it from E cache in tabl? When the following conditions are met, you are ready to delete the table in Table cache.
A. When the table cache is full, another thread needs to open a table not in the table cache.
B. Table cache has more entries than table_open_cache, and tables in Table cache are no longer used by any thread.
C. When a table has a flush operation, such as a flush TABLE statement, mysqladmin flush-tables, or mysqladmin refresh

Which tables are deleted in Table cache?
A. When the table cache is fullAlgorithmDelete
B. when the table cache is full and a new table needs to be open, the cache will be temporarily extended. When the cache is temporarily extended, the table will change from available to unavailable, the table can be colse and deleted from the table cache.

2. Number of tables in a database
The number of tables in the same database should not be too large (-is recommended). If there are too many tables, open, close, and create operations will be very busy, probably because the MySQL system tables are all MyISAM, the reason why concurrency is not supported.

 

3. Use of MySQL internal temporary tables
When the system needs an internal temporary table, it first initializes the table of the memory type in the memory. As the data increases, MySQL automatically converts the memory type table in the memory to the MyISAM table on the disk, the temporary table in the memory is the smallest of the two variables tmp_table_size and max_heap_table_size.

When MySQL server uses temporary tables, it can be monitored through the state variables created_tmp_tables and created_tmp_disk_tables. When a temporary table is created, the variable created_tmp_tables increases, and the value of created_tmp_disk_tables increases. By monitoring these two state variables, you can easily set the variable tmp_table_size to reduce disk sorting.

 

Under what conditions will a temporary disk table be used?
A. Blob and text columns appear in the table.
B. Group by or distinct with more than bytes
C. When union and Union all are used, the select list exceeds 512 bytes.

How do I determine whether a query uses a temporary table?
You can check the "extra" column through the explain command to see if it has "using temporary"

 

 

---- End ----

 

Reference: http://dev.mysql.com/doc/refman/5.5/en/optimize-multi-tables.html