[轉貼] my.ini: tmp_table_size, sort_buffer_size...

主題已鎖定
頭像
心靈捕手
默默耕耘的老師
默默耕耘的老師
文章: 8223
註冊時間: 2003-01-01, 09:01
來自: Taiwan

[轉貼] 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:
  • 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.
Note: These are "per connection" values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL needs to do a sort.
Important: be careful not to run out of memory.

--
資料來源:
http://www.sitebuddy.com/Mysql/tmp_tabl ... uffer_size
主題已鎖定

回到「架站技術」