tmp_table_size sort_buffer_size read_buffer_size
Submitted by chris on January 8, 2006 - 14:43
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.
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.
Delicious
Digg
Reddit
Google
Technorati