[轉貼] my.ini: tmp_table_size, sort_buffer_size...
[轉貼] my.ini: tmp_table_size, sort_buffer_size...
If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important.
If the space required to build the temporary MyISAM table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory.
To determine a good value for tmp_table_size:
Compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. You want to increase tmp_table_size if Created_tmp_tables is not a lot larger then Created_tmp_disk_tables (like in the order of 20x: 5%).
Some aim for Created_tmp_disk_tables being 2% of Created_tmp_tables.
My Values:
Important: be careful not to run out of memory.
--
資料來源:
http://www.sitebuddy.com/Mysql/tmp_tabl ... uffer_size
If the space required to build the temporary MyISAM table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory.
To determine a good value for tmp_table_size:
Compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. You want to increase tmp_table_size if Created_tmp_tables is not a lot larger then Created_tmp_disk_tables (like in the order of 20x: 5%).
Some aim for Created_tmp_disk_tables being 2% of Created_tmp_tables.
My Values:
- tmp_table_size(default 32M) = 128M (Previous: 96M had 15% ratio Previous2:128M had 2% ratio)
- max_heap_table_size = default 16MB (same thing as tmp_table_size to the HEAP type tables)
- read_buffer_size (default 128KB) = 2M trying 4M (my-huge recommends 2M)
- sort_buffer_size = 6M (Previous value: 4M, my-huge recommends 2M)
Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. - read_rnd_buffer_size (default 126-256KB range) = 4M (Previous value: 2M)
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance considerably. - join_buffer_size (default 128KB) = 2M trying 4M
- max_tmp_tables (default 32) = 64
The maximum number of temporary tables a client can keep open at the same time. - myisam_sort_buffer_size = 64M
The buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
Important: be careful not to run out of memory.
--
資料來源:
http://www.sitebuddy.com/Mysql/tmp_tabl ... uffer_size