Comments on: Copying to tmp table http://danosipov.com/blog/?p=75 I will be posting my latest photos, as well as code snippets, useful tricks & techniques, and whatever else is on my mind on a particular day. Thu, 12 Aug 2010 15:41:47 +0000 http://wordpress.org/?v=2.9 hourly 1 By: Mike http://danosipov.com/blog/?p=75&cpage=1#comment-2038 Mike Sun, 23 Aug 2009 19:54:10 +0000 http://danosipov.com/blog/?p=75#comment-2038 Dan, that last comment is not true. I have plenty of queries that use group by or order by, and are saved in memory and say 'copying to tmp table' not 'copying to tmp table on disk' within profiler. I don't know the exact conditions that determine when something is stored on disk, but I know it's not true that everything that uses order by or group by is... Dan, that last comment is not true. I have plenty of queries that use group by or order by, and are saved in memory and say ‘copying to tmp table’ not ‘copying to tmp table on disk’ within profiler. I don’t know the exact conditions that determine when something is stored on disk, but I know it’s not true that everything that uses order by or group by is…

]]> By: Dan Osipov http://danosipov.com/blog/?p=75&cpage=1#comment-820 Dan Osipov Mon, 16 Mar 2009 20:48:26 +0000 http://danosipov.com/blog/?p=75#comment-820 Good point Dave - it doesn't, but like I said, if the table contains a TEXT/BLOB field, or there is GROUP BY/ORDER BY clause in the query, the table will have to be written to disk. To be sure, you can check the process list while running a query, and you will see the query enter into the "Copying to tmp table on disk" state. Good point Dave – it doesn’t, but like I said, if the table contains a TEXT/BLOB field, or there is GROUP BY/ORDER BY clause in the query, the table will have to be written to disk.

To be sure, you can check the process list while running a query, and you will see the query enter into the “Copying to tmp table on disk” state.

]]> By: David Abdemoulaie http://danosipov.com/blog/?p=75&cpage=1#comment-819 David Abdemoulaie Mon, 16 Mar 2009 19:26:43 +0000 http://danosipov.com/blog/?p=75#comment-819 In fact, here's an article that puts it much better than I have: http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ In fact, here’s an article that puts it much better than I have:

http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/

]]> By: David Abdemoulaie http://danosipov.com/blog/?p=75&cpage=1#comment-818 David Abdemoulaie Mon, 16 Mar 2009 19:25:20 +0000 http://danosipov.com/blog/?p=75#comment-818 Dan, "Using where; Using temporary; Using filesort" does *not* mean that the temporary table was written to disk. In fact, nothing in EXPLAIN can give you that information. It seems like you're working under the common assumption that 'filesort' means it was written to a file on disk, and then sorted. This is not necessarily the case, filesort is simply the name of the sorting algorithm used, and occurs for in-memory tables as well. It means that the results could not be sorted by an index alone, thus the data was copied to a temporary table, and then the "filesort" algorithm was applied to sort the results. Dan,

“Using where; Using temporary; Using filesort”

does *not* mean that the temporary table was written to disk. In fact, nothing in EXPLAIN can give you that information. It seems like you’re working under the common assumption that ‘filesort’ means it was written to a file on disk, and then sorted. This is not necessarily the case, filesort is simply the name of the sorting algorithm used, and occurs for in-memory tables as well. It means that the results could not be sorted by an index alone, thus the data was copied to a temporary table, and then the “filesort” algorithm was applied to sort the results.

]]> By: Dan Osipov http://danosipov.com/blog/?p=75&cpage=1#comment-542 Dan Osipov Sat, 21 Feb 2009 18:13:32 +0000 http://danosipov.com/blog/?p=75#comment-542 I see your point, but interestingly enough the DB is configured to use all the available memory - it just chooses not to for some reason... I see your point, but interestingly enough the DB is configured to use all the available memory – it just chooses not to for some reason…

]]> By: Ries http://danosipov.com/blog/?p=75&cpage=1#comment-541 Ries Sat, 21 Feb 2009 17:58:04 +0000 http://danosipov.com/blog/?p=75#comment-541 Dan, it is still very dangerous to let a DB write that stuff to a limited Disk space because you will never know what happens next. It's also a bit of a shame that if that ram disk is 'to large' you have a lot of memory in use you really don't use for the DB. Ries Dan,

it is still very dangerous to let a DB write that stuff to a limited Disk space because you will never know what happens next. It’s also a bit of a shame that if that ram disk is ‘to large’ you have a lot of memory in use you really don’t use for the DB.

Ries

]]> By: Dan Osipov http://danosipov.com/blog/?p=75&cpage=1#comment-403 Dan Osipov Tue, 03 Feb 2009 21:15:22 +0000 http://danosipov.com/blog/?p=75#comment-403 Ries, It depends on the Linux kernel version you're using. Older kernels will freeze when space runs out, but newer kernels are more safe. As for giving the DB all the memory it can use - sure, but in the case of a filesort MySQL will create a disk based table no matter what - and that's slow even if the table is small. You can also limit the size of a tmpfs partition to make sure it never goes into swap. FYI - we've been running it on a high traffic DB server with 8Gb of RAM, and usage never went into swap space so far. Ries,

It depends on the Linux kernel version you’re using. Older kernels will freeze when space runs out, but newer kernels are more safe.

As for giving the DB all the memory it can use – sure, but in the case of a filesort MySQL will create a disk based table no matter what – and that’s slow even if the table is small.

You can also limit the size of a tmpfs partition to make sure it never goes into swap.

FYI – we’ve been running it on a high traffic DB server with 8Gb of RAM, and usage never went into swap space so far.

]]> By: Ries http://danosipov.com/blog/?p=75&cpage=1#comment-400 Ries Tue, 03 Feb 2009 14:49:19 +0000 http://danosipov.com/blog/?p=75#comment-400 I agree that the on disk temporary storage is bad for performance and the first thing to look at is to 'filter' data as soon a possible so MySQL has less reason to create such a tmp table. However your solution posted is very dangerous on buzy databases because what if people run all queries in memory? Then you might end up with out of memory or usage of swap space which slows that down again. Also the solution you provided to store these temp tables on a separate memory HD is dangerous for the similar reasons, what if that space runs out? Will MySQL crash, or happily keep on running and give incorrect or no results back, does anybody know that behavior? Usually it's best to give the RDBM all the memory it can get from the system and let the OS handle disk caching which is more save then create your own tricks. Ries I agree that the on disk temporary storage is bad for performance and the first thing to look at is to ‘filter’ data as soon a possible so MySQL has less reason to create such a tmp table.

However your solution posted is very dangerous on buzy databases because what if people run all queries in memory? Then you might end up with out of memory or usage of swap space which slows that down again. Also the solution you provided to store these temp tables on a separate memory HD is dangerous for the similar reasons, what if that space runs out? Will MySQL crash, or happily keep on running and give incorrect or no results back, does anybody know that
behavior?

Usually it’s best to give the RDBM all the memory it can get from the system and let the OS handle disk caching which is more save then create your own tricks.

Ries

]]> By: Frank http://danosipov.com/blog/?p=75&cpage=1#comment-375 Frank Mon, 26 Jan 2009 21:43:15 +0000 http://danosipov.com/blog/?p=75#comment-375 Good to know! Good to know!

]]>