What would cause all tmp tables to be created on disk instead of ram?

JGilbert · May 4, 2007, 10:29pm

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.

JGilbert · May 4, 2007, 10:34pm

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.

sterin · May 5, 2007, 4:43am

What settings do you have on the:
sort_buffer_size

Usually I connect a lot of temporary tables with the sort_buffer_size.

JGilbert · May 5, 2007, 8:32am

I have it set to 64M currently. For the sake of testing I’ll change this number to 512M and see if that produces any less tmp tables on disk. After 12 hours there have been roughly 31k tmp tables. I’ll let you know how it ends up. )

Initially the prognosis is the same. 100% of the tmp tables are still being written to disk from everything i can see. RAM usage is still very low. It’s quite frustrating.

sterin · May 5, 2007, 6:15pm

Ok, what are your settings for:
tmp_table_size
heap_table_size
?

The lower of these two will set the limit for the size of in memory tables.

kmike · May 6, 2007, 4:02am

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.

JGilbert · May 6, 2007, 10:03pm

[mysqld]# Server Configserver-id = 1port = 3306socket = /var/lib/mysql/mysql.sockuser = mysqllog-error = mysql-err.loginit-file = /var/lib/mysql/startup.sqlcharacter_set_server = utf8collation_server = utf8_general_cidefault-storage-engine = InnoDBlog-slow-queries = /var/lib/mysql/mysql-slow.logtmpdir = /var/tmp/skip-lockingskip-bdbskip-name-resolve#skip-networkingbig-tables# Miscellaneous Configopen_files_limit = 2048 # number of tables and threads in cachethread_stack = 128Kthread_concurrency = 8wait_timeout = 300interactive_timeout = 300max_delayed_threads = 200delay_key_write = OFFmax_connections = 100long_query_time = 3max_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 tablestable_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 412870query_cache_limit = 4M # defaults to 1Mquery_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 4Mquery_cache_type = 1thread_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 bethread_cache = 64 # 32-64 is recommended# InnoDB Settingsinnodb_data_home_dir = /var/lib/mysql/innodb/innodb_data_file_path = ibdata1:1000M:autoextendinnodb_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 = 20Minnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 4M # do not set over 2-8M, is flushed once a second anywayinnodb_lock_wait_timeout = 50innodb_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 differenceinnodb_support_xa = OFF # when off, reduces overhead. may cause out of sync binlogsinnodb_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_DIRECTinnodb_open_files = 2048innodb_file_per_table# Buffer Settingsread_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 @ 17dayskey_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 Settingsmax_heap_table_size = 1G # Used as needed, no adverse reactionstmp_table_size = 1G # Used as needed, no adverse reactionsmax_join_size = 1G # used to catch bad joins and disallow themjoin_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 eek:

JGilbert · May 6, 2007, 10:09pm

[B]kmike wrote on Sun, 06 May 2007 05:32[/B]

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.

sterin · June 9, 2007, 3:23am

Do you think that you can find out exactly which query/queries that create the temp tables to disk?

What does that query look like?

JGilbert · June 9, 2007, 11:37pm

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.

sterin · June 11, 2007, 4:37pm

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.

JGilbert · June 18, 2007, 6:39pm

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.

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

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.

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.

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 | | 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.

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.

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.

JGilbert · June 18, 2007, 6:40pm

[B]sterin wrote on Mon, 11 June 2007 18:07[/B]

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.