Home » Performance » MySQL » What would cause all tmp tables to be created on disk instead of ram?
| What would cause all tmp tables to be created on disk instead of ram? [message #1207] |
Fri, 04 May 2007 23:59  |
|
I've been running the db for a few hours after my last attempts at solving this, and it is still creating all the tmp tables on disk. Is this something innodb does because of how it handles joins or something?
It has already created a few thousand tmp tables on disk. What would be some causes of this? I know what causes MySQL to create tmp tables, but I'm not sure why 100% of them are written to the hard drive instead of to the ram. I think I'm doing really well at optimizing things as best I can but this specific stat troubles me because it makes me think that the unused ram (almost 6 out of the 8 gigs available) is being wasted when it could easily satisfy the needs of those tmp table creation requests.
Any help would be warmly welcomed and greatly appreciated. Thanks in advance for any advice. Big fan of this blog.
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1208 is a reply to message #1207 ] |
Sat, 05 May 2007 00:04  |
|
|
Also, do you know if there is a size limit on the VARCHAR field regarding this situation because I changed my mediumtext fields to VARCHAR(17000) (longest body in the table) because I read that MySQL wont write to heap / memory if there is a TEXT / BLOB field in the table for the tmp table and I didn't know if that just made things worse / better / no difference at all.
|
|
| | | |
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1218 is a reply to message #1207 ] |
Sun, 06 May 2007 05:32 |
kmike
Messages: 6
Registered: February 2007 |
Junior Member |
|
|
From the documentation:
"To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently."
I.e. if you sort on a column which isn't a part of the index, or it's a part of the index which isn't used in the query conditions, or your query has something like ORDER BY col1 ASC, col2 DESC, a temporary table will be needed regardless of tmp_table_size setting.
Tracking such queries isn't easy, but most probably such queries will be slow so you can look for them in the slow query log.
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1220 is a reply to message #1214 ] |
Sun, 06 May 2007 23:33 |
|
[mysqld]
# Server Config
server-id = 1
port = 3306
socket = /var/lib/mysql/mysql.sock
user = mysql
log-error = mysql-err.log
init-file = /var/lib/mysql/startup.sql
character_set_server = utf8
collation_server = utf8_general_ci
default-storage-engine = InnoDB
log-slow-queries = /var/lib/mysql/mysql-slow.log
tmpdir = /var/tmp/
skip-locking
skip-bdb
skip-name-resolve
#skip-networking
big-tables
# Miscellaneous Config
open_files_limit = 2048 # number of tables and threads in cache
thread_stack = 128K
thread_concurrency = 8
wait_timeout = 300
interactive_timeout = 300
max_delayed_threads = 200
delay_key_write = OFF
max_connections = 100
long_query_time = 3
max_allowed_packet = 32M # (max of 1GB, should be the size of the largest blob.)
#ft_min_word_len = 3
#thread_concurrency = 4
# Cache Settings
# -- table cache is not used for innodb tables
table_cache = 1024 # default is 64, max is subject to OS. 1024 is recommended min. max open files limit is found by "cat /proc/sys/fs/file-max" which outputs 412870
query_cache_limit = 4M # defaults to 1M
query_cache_size = 16M # last checked it had 22M free (qcl was 2M then) so it was reduced from 32M to 16M and qcl bumped to 4M
query_cache_type = 1
thread_cache_size = 1024 # I'm going to set this = to the number of tables in the table cache but I don't know what it should be
thread_cache = 64 # 32-64 is recommended
# InnoDB Settings
innodb_data_home_dir = /var/lib/mysql/innodb/
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_buffer_pool_size = 2G # this can / should be 70% of the available ram for innodb only systems (4G totals for 32bit chips) so 3G would be recommended. This can be tuned.
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 4M # do not set over 2-8M, is flushed once a second anyway
innodb_lock_wait_timeout = 50
innodb_log_file_size = 256M # if you change this size, you must stop mysql, delete the log files for innodb, then start it to see a difference
innodb_support_xa = OFF # when off, reduces overhead. may cause out of sync binlogs
innodb_thread_concurrency = 4 # (2 processors + 3 disks) * 2 = 10 concurrent threads. lower is generally better. default is infinite and may result in "thrashing" and "bumping"
innodb_flush_method = O_DIRECT
innodb_open_files = 2048
innodb_file_per_table
# Buffer Settings
read_buffer_size = 4M # Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. (global / instant)
read_rnd_buffer_size = 4M # When reading rows for order bys following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. (global / instant)
sort_buffer_size = 512M # Each thread that needs to do a sort allocates a buffer of this size. Increase this value for Sort_merge_passes probs. This was 6M for 51k smps @ 17days
key_buffer_size = 1G # key cache (max 4G) (recommend 30%. 25%-50% but no more of total ram). This appears to be a MyISAM setting but also seems to be globally available
#myisam_sort_buffer_size = 6M # we dont use myisam anymore, so don't amp this up for performance anymore
# TMP Table Settings
max_heap_table_size = 1G # Used as needed, no adverse reactions
tmp_table_size = 1G # Used as needed, no adverse reactions
max_join_size = 1G # used to catch bad joins and disallow them
join_buffer_size = 256M # used for unindexed table joins (never or rarely ever)
#max_tmp_tables = 256 # (This option does not yet do anything.)
[mysqldump]
quick
[mysql]
no-auto-rehash
Those are the config options on this system. For reference, it's MySQL 5.0.37 on a dual 32bit Xeon system with 15k hard drives and 8 gigs of ram. Redhat only lets each chip address 4 gigs so most of the settings are tuned for a 4 gig setup not an 8 gig setup. I'll upgrade to 64 bit when I can afford it 
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1221 is a reply to message #1218 ] |
Sun, 06 May 2007 23:39 |
|
| kmike wrote on Sun, 06 May 2007 05:32 |
From the documentation:
"To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently."
I.e. if you sort on a column which isn't a part of the index, or it's a part of the index which isn't used in the query conditions, or your query has something like ORDER BY col1 ASC, col2 DESC, a temporary table will be needed regardless of tmp_table_size setting.
Tracking such queries isn't easy, but most probably such queries will be slow so you can look for them in the slow query log.
|
It's ok that MySQL creates temporary tables, but I don't understand why 100% of them are writing to disk.
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1223 is a reply to message #1221 ] |
Mon, 07 May 2007 00:42 |
kmike
Messages: 6
Registered: February 2007 |
Junior Member |
|
|
|
edit: scrap this
[Updated on: Mon, 07 May 2007 01:29]
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1224 is a reply to message #1223 ] |
Mon, 07 May 2007 11:03 |
|
|
Having tested sort buffer with a huge amount I've lowered it back down to something reasonable (64M) and dropped my max_connections to 50 from 100. With a really nicely performing database, I don't know that I'll need 100 connections. I'd rather spend those resources on buffer sizes and such
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1228 is a reply to message #1224 ] |
Tue, 08 May 2007 15:49 |
|
I ran a perl app for profiling memory usage just to see what it would come up with. If this helps shine a light on something I'm missing, I'll try something new.
http://www1.thedilly.com/pics/basics.htm
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1385 is a reply to message #1228 ] |
Fri, 08 June 2007 17:08 |
|
I'm still having some trouble with this. It looks like everything else is fine. My slow query time is set very low (3 seconds) so it's abnormally large. Really, the only thing that I can find which is slowing down the queries is the use the hard drive rather than the ram for every instance of tmp table creation. Here's a copy of my status page from myadmin for reference:
|
|
| | |
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1394 is a reply to message #1391 ] |
Sun, 10 June 2007 01:07 |
|
|
seems like its any query which would normally create a tmp table, only, none of them are registering in memory. i'll get back to you with some examples.
|
|
| |
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1446 is a reply to message #1403 ] |
Mon, 18 June 2007 20:09 |
|
Slow Queries:
112 SELECT name, photo.user_id, photo_id, user_name FR
Full Query:SELECT name, photo.user_id, photo_id, user_name FROM photo_newest_1000_cam AS photo INNER JOIN user_registry_active USING (user_id) WHERE is_hidden IS NULL AND photo.approved_by IS NOT NULL GROUP BY user_id ORDER BY cam_images DESC, photo_id DESC LIMIT 13
# took 4.0012 seconds.
+----+-------------+---------------+--------+---------------------+---------+---------+---------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------------+---------+---------+---------------+--------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | user_registry | eq_ref | PRIMARY,last_online | PRIMARY | 3 | photo.user_id | 1 | Using where |
| 2 | DERIVED | photo | ALL | NULL | NULL | NULL | NULL | 604631 | Using where |
+----+-------------+---------------+--------+---------------------+---------+---------+---------------+--------+----------------------------------------------+
106 SELECT user_name, user_id, avatar, age, (IF(NULLIF
Full Query:SELECT user_name, user_id, avatar, age, (IF(NULLIF(postal_code,"") IS NULL, country_name,CONCAT(city_name, ", ", state_name))) AS location, marital_status, sexuality, (avg_vote_received*hits_this_month) AS popularity FROM user_registry WHERE is_online IS NOT NULL AND avatar IS NOT NULL AND is_moderator IS NULL ORDER BY popularity DESC LIMIT 1
+----+-------------+---------------+-------+---------------+--------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+--------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | user_registry | range | avatar | avatar | 303 | NULL | 202501 | Using where; Using filesort |
+----+-------------+---------------+-------+---------------+--------+---------+------+--------+-----------------------------+
64 SELECT country_abbreviation, country_name, region,
Full Query:SELECT country_abbreviation, country_name, region, city, isp, latitude, longitude FROM ip2location_disk WHERE ip_first <= 1286851759 AND ip_last >= 1286851759
# took 3.136 seconds.
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | ip2location_disk | range | PRIMARY | PRIMARY | 4 | NULL | 1445944 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+---------+-------------+
23 SELECT latitude, longitude FROM ip2location_simple
Full Query:SELECT latitude, longitude FROM ip2location_simple WHERE ip_first <= 1286851759 AND ip_last >= 1286851759
# took 3.3463 seconds.
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | ip2location_simple | range | PRIMARY | PRIMARY | 4 | NULL | 986330 | Using where |
+----+-------------+--------------------+-------+---------------+---------+---------+------+--------+-------------+
12 SELECT ur.user_id, user_name, IFNULL(is_online,0)
Full Query:SELECT ur.user_id, user_name, IFNULL(is_online,0) AS is_online, status_id, IFNULL(moderator_power.show_moderator_status,0) AS moderator_flag, blue_names FROM user_registry AS ur LEFT JOIN moderator_power ON ur.user_id = moderator_power.user_id WHERE is_moderator IS NOT NULL ORDER BY user_name ASC
+----+-------------+-----------------+--------+---------------+-----------+---------+-----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+---------------+-----------+---------+-----------------------+--------+-------------+
| 1 | SIMPLE | ur | index | NULL | user_name | 62 | NULL | 405004 | Using where |
| 1 | SIMPLE | moderator_power | eq_ref | PRIMARY | PRIMARY | 3 | production.ur.user_id | 1 | |
+----+-------------+-----------------+--------+---------------+-----------+---------+-----------------------+--------+-------------+
8 SELECT user_name, avatar, user_id AS author_id FRO
Full Query:SELECT user_name, avatar, user_id AS author_id FROM forum_recently_posted LIMIT 15
# took 3.0349 seconds.
+----+-------------+------------+--------+----------------------------+----------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------+----------------+---------+-------------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | |
| 2 | DERIVED | ft | range | PRIMARY,last_post_time | last_post_time | 9 | NULL | 638 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | fp | ref | thread_id,author_id | thread_id | 3 | production.ft.thread_id | 15 | Using where |
| 2 | DERIVED | u | eq_ref | PRIMARY,last_online,avatar | PRIMARY | 3 | production.fp.author_id | 1 | Using where |
+----+-------------+------------+--------+----------------------------+----------------+---------+-------------------------+------+----------------------------------------------+
7 SELECT SQL_CALC_FOUND_ROWS *, (0.46947156278589 *
Full Query:SELECT SQL_CALC_FOUND_ROWS *, (0.46947156278589 * SIN(latitude * 0.017453292519943) + 0.88294759285893 * COS(latitude * 0.017453292519943) * COS((longitude * 0.017453292519943) - -1.4311699866354)) AS dist
FROM user_registry
ORDER BY dist DESC
LIMIT 0,48
# took 28.3571 seconds.
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | user_registry | ALL | NULL | NULL | NULL | NULL | 405004 | Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
4 SELECT SQL_CALC_FOUND_ROWS m.message_id, m.sender_
Full Query:SELECT SQL_CALC_FOUND_ROWS m.message_id, m.sender_id, m.recipient_id, m.created_datetime, m.modified_datetime, m.is_private, m.is_read, m.is_system_message, m.is_moderator_note, m.is_mass_message, m.is_sender_hidden, m.is_receiver_hidden, m.body, m.location, m.section, m.content_id, IFNULL(u.user_name,"Spooky Ghost") AS user_name, IFNULL(u.age,0) AS age, IFNULL(u.gender,0) AS gender, u.status_id, u.avatar, IFNULL(u.color_scheme,"grey") AS color_scheme, u.is_online ,(SELECT sub.user_name FROM user_registry AS sub WHERE sub.user_id=m.sender_id) AS sender_name, (SELECT sub.avatar FROM user_registry AS sub WHERE sub.user_id=m.sender_id) AS sender_avatar, (SELECT sub.is_online FROM user_registry AS sub WHERE sub.user_id=m.sender_id) AS sender_is_online, (SELECT sub.color_scheme FROM user_registry AS sub WHERE sub.user_id=m.sender_id) AS sender_color_scheme FROM message AS m LEFT JOIN user_registry AS u ON recipient_id=u.user_id WHERE (m.recipient_id=226091 OR (m.sender_id=226091 AND is_system_message IS NULL AND is_mass_message IS NULL)) AND ((m.is_receiver_hidden IS NULL AND m.is_sender_hidden IS NULL AND m.is_private IS NULL) OR ((m.sender_id=226091 AND m.is_sender_hidden IS NULL) OR (m.recipient_id=226091 AND m.is_receiver_hidden IS NULL)))AND (u.user_name LIKE "%fcukiingFABULOUS%" OR m.body LIKE "%fcukiingFABULOUS%") ORDER BY m.message_id DESC LIMIT 0, 30
# took 3.8387 seconds.
+----+--------------------+-------+-------------+------------------------+------------------------+---------+---------------------------+-------+------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------------+------------------------+------------------------+---------+---------------------------+-------+------------------------------------------------------------------+
| 1 | PRIMARY | m | index_merge | sender_id,recipient_id | recipient_id,sender_id | 3,3 | NULL | 25604 | Using union(recipient_id,sender_id); Using where; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 3 | production.m.recipient_id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | sub | eq_ref | PRIMARY | PRIMARY | 3 | production.m.sender_id | 1 | |
| 4 | DEPENDENT SUBQUERY | sub | eq_ref | PRIMARY | PRIMARY | 3 | production.m.sender_id | 1 | |
| 3 | DEPENDENT SUBQUERY | sub | eq_ref | PRIMARY | PRIMARY | 3 | production.m.sender_id | 1 | |
| 2 | DEPENDENT SUBQUERY | sub | eq_ref | PRIMARY | PRIMARY | 3 | production.m.sender_id | 1 | |
+----+--------------------+-------+-------------+------------------------+------------------------+---------+---------------------------+-------+------------------------------------------------------------------+
3 SELECT SQL_CALC_FOUND_ROWS thread_id, created_date
Full Query:SELECT SQL_CALC_FOUND_ROWS thread_id, created_datetime, topic_id, forum, title, body_preview, IF((is_confession IS NOT NULL OR is_anonymous IS NOT NULL),0,author_id) AS author_id, IF((is_confession IS NOT NULL OR is_anonymous IS NOT NULL),"Anonymous",author_name) AS author_name, is_sticky, forum_sticky, is_locked, is_hidden, is_author_moderated, is_nsfw, is_age_verified, is_18_and_over, is_18_and_under, is_21_and_under, is_21_and_over, is_moderator_only, is_registered_only, is_girls_only, is_boys_only, is_teens_only, is_premium_only, is_noobs_only, is_has_photo, is_saluted_only, is_unmoderated, is_seductive_only, is_working, is_ninjas_only, is_lovers_only, is_haters_only, is_article, is_feature, is_news, is_feed, is_popular, is_anonymous, is_confession, thread_type, IF(((is_confession IS NOT NULL AND posts=1) OR is_anonymous IS NOT NULL),0,last_author_id) AS last_author_id, IF(((is_confession IS NOT NULL AND posts=1) OR is_anonymous IS NOT NULL),"Anonymous",last_author_name) AS last_author_name, last_post_time, posts, hits FROM forum_thread WHERE forum=2 AND topic_id <> 58 AND is_nsfw IS NULL AND is_hidden IS NULL AND is_age_verified IS NULL AND is_18_and_over IS NULL AND is_21_and_over IS NULL AND is_moderator_only IS NULL AND is_registered_only IS NULL AND is_girls_only IS NULL AND is_boys_only IS NULL AND is_teens_only IS NULL AND is_premium_only IS NULL AND is_unmoderated IS NULL AND is_has_photo IS NULL AND is_saluted_only IS NULL AND is_seductive_only IS NULL AND is_lovers_only IS NULL AND is_haters_only IS NULL ORDER BY last_post_time DESC LIMIT 0,10
# took 5.9257 seconds.
+----+-------------+--------------+-------+---------------+----------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+----------------+---------+------+-------+-------------+
| 1 | SIMPLE | forum_thread | index | NULL | last_post_time | 9 | NULL | 61431 | Using where |
+----+-------------+--------------+-------+---------------+----------------+---------+------+-------+-------------+
2 SELECT p.user_id FROM photo AS p INNER JOIN user_r
Full Query:SELECT p.user_id FROM photo AS p INNER JOIN user_registry AS ur USING(user_id) WHERE p.approved_by IS NULL AND is_hidden IS NULL AND 1 AND in_sync=1 ORDER BY p.created_datetime ASC LIMIT 50
# took 6.2772 seconds.
+----+-------------+-------+--------+---------------+------------------+---------+----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------------------+---------+----------------------+--------+-------------+
| 1 | SIMPLE | p | index | user_id | created_datetime | 8 | NULL | 604631 | Using where |
| 1 | SIMPLE | ur | eq_ref | PRIMARY | PRIMARY | 3 | production.p.user_id | 1 | Using index |
+----+-------------+-------+--------+---------------+------------------+---------+----------------------+--------+-------------+
2 SELECT t.thread_id, t.forum, t.title, t.body_previ
Full Query:SELECT t.thread_id, t.forum, t.title, t.body_preview, t.posts FROM forum_thread t INNER JOIN forum_post p USING(thread_id) WHERE t.is_confession=1 AND t.is_locked IS NULL AND t.is_moderator_only IS NULL AND t.is_hidden IS NULL group by p.thread_id HAVING count(p.post_id) > 1 ORDER BY rand() LIMIT 10
# took 9.6543 seconds.
+----+-------------+-------+------+---------------+-----------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 61431 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | thread_id | thread_id | 3 | production.t.thread_id | 15 | Using index |
+----+-------------+-------+------+---------------+-----------+---------+------------------------+-------+----------------------------------------------+
1 SELECT SQL_CALC_FOUND_ROWS *, (0.62844868715656 *
Full Query:SELECT SQL_CALC_FOUND_ROWS *, (0.62844868715656 * SIN(latitude * 0.017453292519943) + 0.77785104461664 * COS(latitude * 0.017453292519943) * COS((longitude * 0.017453292519943) - -1.3449407208502)) AS dist
FROM user_registry
ORDER BY dist DESC
LIMIT 0,48
# took 23.515 seconds.
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | user_registry | ALL | NULL | NULL | NULL | NULL | 405004 | Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1447 is a reply to message #1403 ] |
Mon, 18 June 2007 20:10 |
|
| sterin wrote on Mon, 11 June 2007 18:07 |
The important part is that you make an estimation on how large the result set for the queries we are talking about are.
So that you have something to start with when it comes to estimating the size of what needs to be sorted.
But one more suggestion is that you increase the read_rnd_buffer_size. Because 4M seems to be a bit small if you have large results.
And together with sort_buffer_size, read_rnd_buffer_size are the most important variables for tuning sorting.
|
I upped the rnd buffer to 8M, then 16M, then 32M, and it had no affect on the tmp tables
|
|
|
| Re: What would cause all tmp tables to be created on disk instead of ram? [message #1795 is a reply to message #1447 ] |
Tue, 11 September 2007 13:31 |
|
for the record, i solved this by removing the "big-tables" setting which i probably added incorrectly some time ago without looking up what it did. it specifically stores all tmp tables on disk to safely handle large results which has been stable since some time after 3.x and i'm well into 5.0 now.
/noob
|
|
|
Goto Forum:
Current Time: Thu Jan 8 21:37:04 EST 2009
Total time taken to generate the page: 0.05429 seconds |
Members
Search
Help
Register
Login
Home
