As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the data which needs to be put to temporary tables – BLOB/TEXT columns are not supported with MEMORY storage engine so must use on disk MyISAM temporary table.
What i decided to do is to see how much overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can be presented both in MEMORY and MyISAM storage engine.
The benchmarks are done with MySQL 5.0.45 on 32bit Fedora Core 6 on Pentium 4 box – something I had available for testing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE `gt` ( `i` int(10) unsigned NOT NULL, `c` char(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> select * from gt limit 5; +-------+------------------------------------------+ | i | c | +-------+------------------------------------------+ | 25451 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 48063 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 84146 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 66197 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 432 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | +-------+------------------------------------------+ 5 rows in set (0.00 sec) |
The table contains 1.000.000 rows with mostly unique “c” column.
1 2 3 4 5 6 7 | mysql> explain select count(*) cnt,c from gt group by c order by null limit 10; +----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+ | 1 | SIMPLE | gt | ALL | NULL | NULL | NULL | NULL | 1000000 | Using temporary | +----+-------------+-------+------+---------------+------+---------+------+---------+-----------------+ 1 row in set (0.01 sec) |
As you can see I’m using ORDER BY NULL clause as otherwise MySQL will sort the data after performing group by which is overhead we’re not looking to measure. This is actually good trick to use for cases when you do not need GROUP BY results sorted – it can significantly improve performance in some cases.
First I run the query with default key_buffer_size which is not large enough to fit all key blocks from temporary table index, so we get a lot of key writes which kill performance.
The problem with writes is OS can delay writes only for fixed amount of time and when it has to perform them anyway which can cause IO bound load even with small data which can fully fit in OS cache as in this case. In perfect world it should not happen and we should have seen 50MB sequentially written once per certain amount of time which takes fraction of second to do, but it is not how it happens in practice.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show global status like "key%"; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | Key_blocks_not_flushed | 6516 | | Key_blocks_unused | 0 | | Key_blocks_used | 7248 | | Key_read_requests | 2051451 | | Key_reads | 198208 | | Key_write_requests | 469220 | | Key_writes | 185808 | +------------------------+---------+ 7 rows in set (0.00 sec) |
So how long does it take:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select count(*) cnt,c from gt group by c order by null limit 10; +-----+------------------------------------------+ | cnt | c | +-----+------------------------------------------+ | 1 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 1 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 1 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | | 1 | dc960ee027a9d741ccd22ae0ee32d0c8915f158b | | 1 | 68d91f476ff40116c9c9ba0b85037496ededa1ef | | 2 | 9bc66fb6426222e3a94c43a64ed701cf197f8cb8 | | 1 | 9c2a671310c810e1f4a9aa4763ce5e76d28e3af4 | | 1 | 2e282a3aeb832b8b5861f703041acba5a4285a35 | +-----+------------------------------------------+ 10 rows in set (9 min 5.40 sec) |
I repeated the run a few times with very similar results.
OK so what if we set key buffer to 128M ?
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show global status like "key%"; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 | | Key_blocks_used | 49361 | | Key_read_requests | 4007329 | | Key_reads | 49361 | | Key_write_requests | 873177 | | Key_writes | 0 | +------------------------+---------+ 7 rows in set (0.00 sec) |
As you can see we have no writes because data fits in key buffer. Note however there is still significant amount of reads (the stats were flushed between query runs) which does not make sense as there no data to be read from the disk as nothing was written. So I filled bug on this.
So what was performance
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select count(*) cnt,c from gt group by c order by null limit 10; +-----+------------------------------------------+ | cnt | c | +-----+------------------------------------------+ | 1 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 1 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 1 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | | 1 | dc960ee027a9d741ccd22ae0ee32d0c8915f158b | | 1 | 68d91f476ff40116c9c9ba0b85037496ededa1ef | | 2 | 9bc66fb6426222e3a94c43a64ed701cf197f8cb8 | | 1 | 9c2a671310c810e1f4a9aa4763ce5e76d28e3af4 | | 1 | 2e282a3aeb832b8b5861f703041acba5a4285a35 | +-----+------------------------------------------+ 10 rows in set (34.47 sec) |
Wow this is over 15 times better so you surely need to make sure your key_buffer_size is set to accommodate needs of your temporary table. Do not forget about it especially if running only Innodb tables so you may think there is no use for it.
Looking at this number I found it to be too good to be true as I’ve seen significantly worse performance for large data sets. So I decided to see what happens if I create a larger table – 4M rows. I sized key_buffer to 256M so it would fit everything needed and there was still enough memory for OS cache to keep temporary table. Results:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +-----+------------------------------------------+ | cnt | c | +-----+------------------------------------------+ | 2 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 2 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 1 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | | 1 | dc960ee027a9d741ccd22ae0ee32d0c8915f158b | | 1 | 68d91f476ff40116c9c9ba0b85037496ededa1ef | | 2 | 9bc66fb6426222e3a94c43a64ed701cf197f8cb8 | | 1 | 9c2a671310c810e1f4a9aa4763ce5e76d28e3af4 | | 1 | 2e282a3aeb832b8b5861f703041acba5a4285a35 | +-----+------------------------------------------+ 10 rows in set (27 min 5.51 sec) |
VMSTAT:
1 2 3 4 5 | procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 4 116 60064 9084 547160 0 0 5 44 96 103 2 0 94 3 0 0 3 116 56248 9104 549212 0 0 2 2023 1518 498 5 2 10 83 0 1 3 116 56316 9104 549228 0 0 0 2121 1539 497 0 1 24 75 0 |
In this case as you can see there is a lot of dirty blocks flushing going on with pretty random IO. Clearly OS write caching is not effective for relatively large area (which still fits in memory) which is getting a lot of random writes.
OK. Now lets move to testing MEMORY tables for the same queries:
1 2 3 4 5 6 7 8 | mysql> set global key_buffer_size=8000000; Query OK, 0 rows affected (0.03 sec) mysql> set global max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set global tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) |
Ahh… I alway forget GLOBAL only sets global value but it does not affect current session. A little gotcha which still catches me.
For 1M rows we have
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> set max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt group by c order by null limit 10; +-----+------------------------------------------+ | cnt | c | +-----+------------------------------------------+ | 1 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 1 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 1 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | | 1 | dc960ee027a9d741ccd22ae0ee32d0c8915f158b | | 1 | 68d91f476ff40116c9c9ba0b85037496ededa1ef | | 2 | 9bc66fb6426222e3a94c43a64ed701cf197f8cb8 | | 1 | 9c2a671310c810e1f4a9aa4763ce5e76d28e3af4 | | 1 | 2e282a3aeb832b8b5861f703041acba5a4285a35 | +-----+------------------------------------------+ 10 rows in set ( 3.88 sec) |
Wow. This is great difference even from our best MyISAM results.
For 4M of rows
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +-----+------------------------------------------+ | cnt | c | +-----+------------------------------------------+ | 2 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e | | 1 | afdf7dbdf7a71dbeac553a0cccce4ab3b1ca3bdb | | 2 | a26f4ce41df9b79f8ce73f8a41c207c4b67f827d | | 1 | 5f912d13f2d415c2c7cb325e1ebbdf468b37bc91 | | 1 | dc960ee027a9d741ccd22ae0ee32d0c8915f158b | | 1 | 68d91f476ff40116c9c9ba0b85037496ededa1ef | | 2 | 9bc66fb6426222e3a94c43a64ed701cf197f8cb8 | | 1 | 9c2a671310c810e1f4a9aa4763ce5e76d28e3af4 | | 1 | 2e282a3aeb832b8b5861f703041acba5a4285a35 | +-----+------------------------------------------+ 10 rows in set ( 16.41 sec) |
So MEMORY table scales pretty well with query execution time being close to linear dependence of table size, and it can get 100 times faster than MyISAM on disk table even in case temporary table is small enough to fit in OS cache and key_buffer.
My next intension was to test placing table on tmpfs as this should avoid write overhead we’ve observing (and which is indeed very good production practice, if your temporary tables are moderately sized).
Unfortunately I ran into another bug which means I will have to postpone this part of test to another time.
Summary:
If this was too many details for you to read through here is the summary:
- key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes
- OS Write cache is not as efficient as I would hope, at least on Linux
- Performance of Disk MyISAM tables may not scale proportionally with table size, even when all data fits in memory
- MEMORY temporary tables can be 10-100 times faster than disk based MyISAM tables
Hopefully MySQL will implement support of dynamic rows for MEMORY tables some time in the future and so we will be able to use MEMORY table for all cases when data set fits in memory as it is really worth it.
You may also want to try tmpdir on ext2 (or a journaling file system with external journal on a ramdisk or /dev/null). This has the benefit of supporting larger than free memory files… and writes could be better than if in the case of tmpfs, being swapped out.
“As you can see I’m using ORDER BY NULL clause as otherwise MySQL will sort the data after performing group by which is overhead we’re not looking to measure. This is actually good trick to use for cases when you do not need GROUP BY results sorted – it can significantly improve performance in some cases.”
I find this very interesting. When I got to know MySQL I learned how MySQL commits itself to delivering results ordered according to the GROUP BY clause (unless an explicit ORDER BY was included), and I always assumed that this was because MySQL implements GROUP BY by sorting the data.
Apparently, I was dead wrong. ORDER BY NULL cannot help performance in any way if MySQL would use sorting to resolve GROUP BY…so, no I am puzzled:
How is GROUP BY implemented, and why does MySQL commit itself to returning the results in the order specified by the GROUP BY clause?
This latter commitment turns out to be something that is pretty common, but it hampers optimization…would it not be better if MySQL would not commit itself to returning the results in order, and let the user specify it explicitly with ORDER BY?
Thanks. This helped alot. I speed up two of my greylist sql commands from around 15 min to under 2min, mainly from the order by null.
I kind of agree with the previous comment, mysql seems to return everything else unordered unless you tell it to, seems strange it would default to some kind of order method for group by.
Ronald,
This is indeed interesting. MySQL has 2 ways of doing group by via filesort and with temporary table. filesort returns result in sorted order just by nature.
I’m not sure why Monty decided to sort results by default even in case temporary table was used. May be this was his interpretation of how it should behave but basically it was done this way. Later one of the clients performing group bys on tens of millions of rows complained and so ORDER BY NULL was added not to break users expectations as a lot of them could be already relying on GROUP BY being sorted.
When this was being decided I was for making users writing bad SQL to suffer instead of forcing to use ORDER BY NULL if you want results unsorted but Monty took another decision 🙂
Stewart surely there are some more things to try. I’m not sure how wise is tmpfs in writes (I assume it manages swap io directly rater than being swapped out by OS) and I could try different file systems and options.
These are just numbers for typical configuration.
What is the benefit of storing temp tables on tmpfs instead of increasing tmp_table_size? Maybe that we can store tables with BLOB/TEXT in memory? But is there any limit on the max. size of disk based temp table? Because I don’t want big temp table to eat all my tmpfs & memory causing everything to be swapped out…
Hi Peter,
thanks for your swift reply and explanation of the filesort GROUP BY implementation
“Later one of the clients performing group bys on tens of millions of rows complained and so ORDER BY NULL was added not to break users expectations as a lot of them could be already relying on GROUP BY being sorted.”
“When this was being decided I was for making users writing bad SQL to suffer instead of forcing to use ORDER BY NULL if you want results unsorted but Monty took another decision :)”
mmm…what a pity…I was thinking about this when looking at a query like this:
SELECT e.description
, e.name
, e.id
, sum(q.score) score
FROM exam e
INNER JOIN question q
ON e.id = q.exam_id
GROUP BY e.description
, e.name
, e.id
We want to calculate the SUM of the question score for the group of questions corresponding to each exam. Technically, we need to GROUP BY only on the primary key of exam which is defined only on the id column. However, the GROUP BY clause contains also the name and description columns because the person (or reporting tool!) that wrote the query does not know MySQL is one of the few databases that will allow you to SELECT name and description without including these also in the GROUP BY. So, we get a SQL92 compliant full group by clause. Unfortunately, the description column is included first in the group by clause….and MySQL is determined to sort on that column (and then on name should description not be unique). So ironically, although MySQL could in principle use the index on the id column to do a pretty fast GROUP BY, it is all for nothing because of an inefficient sort that needs to take place because of the non-standard commitment to returning the rows in the order implied by the GROUP BY clause.
ORDER BY NULL is a nice trick…to work around this problem if you know you have it. Unfortunately, reporting tools probably don’t know this either.
BTW Peter, does it have to be ORDER BY NULL? Or will any constant do instead of NULL?
Roland Bouman.
Peter – maybe add a comment for this feature request? http://bugs.mysql.com/bug.php?id=30477
Ronald,
ORDER BY 1 means ordering by the first expression in SELECT list so it would not work. I have not tried with string constants.
Marki,
Right if data fits in memory using in memory table is better in most cases.
I mainly did this comparison keeping in mind cases when BLOB/TEXT can’t be used because I expect them to have similar overhead (though I can try converting this column to TEXT to see how long time changes)
There is another reason – MySQL only allows you to set tmp_table_site per thread – there is no global limit which causes problems with resource management – as you do not know how many queries are running concurrently and what resources they are using you can’t say how many resources you can allocate to given query.
Dear Peter,
I’d never considered putting tmpdir on /dev/shm, but that seems to have made a small (
Just wondering if you ever tried this again since they fixed the temp table storage bug that you pointed out in your article. We have our tmpdir set to a tmpfs partition on a big, busy server.. and it seems to have smoothed out some heavy query loads.. but I am curious to see what you’ve found.
Clint,
Note the bug I described in the blog post was only added in 5.0.44 and fixed promptly in next versions so I just was not too lucky. In general using tmpfs indeed helps a lot especially for moderately sized temporary tables which fit in the cache.
Another interesting development is Maria which would be very good for temporary tables as it caches data not only indexes.
The MySQL bug with tmpdir was fixed in v5.0.48 (if somebody didn’t know). The current (at the time of writing) stable Debian package has v5.0.32 so if you’re using tmpfs for MySQL’s tmpdir, keep an eye out for the Debian upgrades of the MySQL package (it might be possible that an upgrade will take you to a MySQL server version that has this bug).
Btw. using tmpfs for tmpdir helped me a lot. Our server (used for a news portal) was totally overloaded with I/O when MySQL started to write out several n*100MB temp tables to disk (a tmp table was mostly less than 200MB, but several tmp tables were created in a row resulting in heavy I/O). Sometimes this took a minute or so and the portal came down to it’s knees (eg. it slowed down and started to give “Internal server error” messages in the end). Using a 500MB tmpfs fixed this and everything seems to be just fine since then. However this is just a quick fix, because those large tmp tables should not even be created in the first place.
Right. I should again check tmpfs vs heap table to see how it works.
In general of course it would be great if MySQL would have the option to use tmpfs only up to certain space and when switch to the drive – this would allow to use it safer.
Though I guess it is going in different direction – there is a patch from Ebay to support BLOB/TEXT in memory tables and there is Maria comming which will cache the data in pagecache avoiding disk IO for temp tables if they are small enough.
Hi!
I’m using a tmpdir on the tmpfs for about 14 days and it performs very well (5.0.51a). The only problem I have run into is creating temporary InnoDB table (file per table setting), because tmpfs cannot do IO_DIRECT. It prints a warning to the error log every time I create a tmp innoDB table. However, despite these warnings, I think the result is OK and the table is created (see http://bugs.mysql.com/bug.php?id=26662)
krteQ
When i retrieve the data from mysql tables using PHP, the output is in a single XSL file. if my web page accessing for download the xsl file many users simultaneously, the little data retrieved . but many rows in my tables using joins. what is the problem? any memory problem or key buffer limit problem, plz let me know as soon as possible, im in hurry.
thanking u
In my webpage, I want to get a output in a XSL file. The data are retrieved from Mysql db. Retrieving the bulk records in a single file. When I access that download file page simultaneously multi systems in the remote, I couldn’t get the file also my data are crashed. What is the problem? PHP.ini or DB? Plz tell me clearly. And how to solve this problem?
Urgent. Thanks in advance.
Great article here. A lot of MySQL optimization articles don’t even bother looking at disk i/o, so that was a refreshing change. Also the ORDER BY NULL was something I never knew about! Cheers.
Correction!
You said, “Which one will be used depends on the allowed tmp_table_size and also by the data which needs to be put to temporary tables – BLOB/TEXT columns are not supported with MEMORY storage engine so must use on disk MyISAM temporary table.” but I guess temporary tables also depend upon MAX_HEAP_TABLE_SIZE.
You said “make sure your key_buffer_size is set to accommodate needs of your temporary table. Do not forget about it especially if running *only Innodb tables* so you may think there is no use for it.”
What effect does key_buffer_size have on innodb?
As far as I knew the key_buffer_size is only for MyISAM tables and. InnoDB uses innodb_buffer_pool_size setting for the memory allocated to indexes. Of course the system tables are still MyISAM, so if you query those it may help there, but other than that why allocate memory via key_buffer_size when all you have are InnoDB tables??