MySQL use many factors to determine the amount of memory assigned to each
· Sort_buffer_size
· Join_buffer_size
· Read_buffer_size
· Tmp_table_size
· Max_heap_table_size
1. The System Variables
Sort_buffer_size: this variable determines the amount of memory to allocate for sourt operations. Each thread that needs to do a sort will allocates a buffer from the variable value. If there is too many records to be sorted in the memory, then a disk based sort takes place instead, Which take longer to perform. To ensure the ORDER BY and GROUP BY operation performance, the sort_buffer_size can be increase.
Join_buffer_size: this variable determines the buffer size for joins that don’t use indexes (use full table scan). Normally, the best way to get fast joins is to add indexes; however, if this is not possible, increase the values of this variable to get a faster full join.
Read_buffer_size: this variable determines the size of the buffer used for catching row data during full table scans. Each thread that does a sequential scn allocates a buffer tof this size(in bytes) fro each table it scans. The default is set to 131072.
Tmp_table_size: this system variable determines the maximum size allowed for a temporary table staored in memory tables before it is convertrd to an on-dis MyISAM table. This variable should be increased if GROUP BY queries involving many rows are performed and the is enough memory to support them.
Max_heap_table_size: This variable sets the maximum size to which MEMORY tables are allowed to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.
2. Getting Server Status and Setting the variable values
Each of the status variables below should be monitored to determine the size of the sort_buffer_size variabl:
· Sort_merge_passes: this status variable displays the number of merge passes that the sort alforithm has had to perform. Increase the sort_buffer_size values if the status value is large.
· Sort_range: this status variable show the number of sorts that were done using ranges
· Sort_rows: this status show the numberof sourted rows
· Sort_scan: this status show number of sourts that were done table scan.
The following stauts measure the number of joins that did not using indexes:
· Select_full_join
· Select_full_range_join
· Select_range
· Select_range_check
· Select_scan
Please note that one join buffer is allocated for each full join between two tables; and, complex joins between several tables for which indexes are not used should be avoided at all costs.
If many sequential scans rae carried out, it is best to increase the read_buffer_size values. According to MySQL documentation, the normal size for this variable should set to ! MB to 2 MB, but can be set up to 8 MB.
The following status need to be taken into account when setting tmp_table_size variable value:
· Created_tmp_disk_tables: this status shows the number of temporary tables on disk cread automatically by the serv while queries were executed. If BLOB/TEXT fields are requested in the queries, MySQL will use disk based temporary tables automatically. We need to determine how many BLOB/TEXT fields reuested statements were ran on the server before deciding change the tmp_table_size variable value.
· Created_ tmp_files: this status shows then number of temporary files has created.
· Created_tmp_tables: this status shows the number of in-memory temporary tables created automatically by the server while excuting queries. If the ratio of created_tmp_disk_tables to created_tmp_tables is hight, consider increasing the tmp_table_size variable value. One or two creates per second is reasonable.
Please note that the max_heap_table_size variable places a maximum limit on tmp_table_size because memory storageengine is used for implicit in-memory temporary tables. For example, if tmp_table_size is 32M and max_heap_table_size is 16M, then the implicit MEMORY temporary tables will convert to on-disk MyISAM tables where they grow past 15M. Therefore, both these variable need to be considered when dealing with implicit temporary tables.
Also, it is best to have the individual session to set the values of the variables if only some queries need lare values and to avoid larger than necessary allocations for the queries which don't need large size.
References:
- MySQL Manual: MySQL Server System Variables
- Understanding MySQL Internals: Chapter 5, Configuration Variables
- High Performance MySQL: Chapter 6, Optomizing Server Settings
- Other Reference On the Web
Posts
No comments:
Post a Comment