# What would cause all tmp tables to be created on disk instead of ram? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 4, 2007, 10:29pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312 "2007-05-04T22:29:13Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [May 4, 2007, 10:29pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/1 "2007-05-04T22:29:13Z")
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. ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [May 4, 2007, 10:34pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/2 "2007-05-04T22:34:40Z")
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. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [May 5, 2007, 4:43am UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/3 "2007-05-05T04:43:58Z")
What settings do you have on the: sort\_buffer\_size Usually I connect a lot of temporary tables with the sort\_buffer\_size. ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [May 5, 2007, 8:32am UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/4 "2007-05-05T08:32:40Z")
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. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [May 5, 2007, 6:15pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/5 "2007-05-05T18:15:56Z")
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. ---
**Author:** ![kmike](https://avatars.discourse-cdn.com/v4/letter/k/58f4c7/32.png) [@kmike](https://forums.percona.com/u/kmike)\ **Post date:** [May 6, 2007, 4:02am UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/6 "2007-05-06T04:02:45Z")
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. ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [May 6, 2007, 10:03pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/7 "2007-05-06T22:03:46Z")
[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: ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [May 6, 2007, 10:09pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/8 "2007-05-06T22:09:33Z")
| [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. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [June 9, 2007, 3:23am UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/9 "2007-06-09T03:23:12Z")
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? ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [June 9, 2007, 11:37pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/10 "2007-06-09T23:37:45Z")
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. ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [June 11, 2007, 4:37pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/11 "2007-06-11T16:37:49Z")
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. ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [June 18, 2007, 6:39pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/12 "2007-06-18T18:39:34Z")
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 | | 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 | | 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 | ±—±------------±--------------±-----±--------------±-----±--------±-----±-------±---------------+ ---
**Author:** ![JGilbert](https://avatars.discourse-cdn.com/v4/letter/j/c89c15/32.png) [@JGilbert](https://forums.percona.com/u/JGilbert)\ **Post date:** [June 18, 2007, 6:40pm UTC](https://forums.percona.com/t/what-would-cause-all-tmp-tables-to-be-created-on-disk-instead-of-ram/312/13 "2007-06-18T18:40:58Z")
| [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. 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