A quick one here, we often talk about effectively utilizing memory to get the most of your MySQL Server. I wanted to remind folks to not forget about allocating memory to a tmpfs for the tmpdir. Certainly this is not going to help everyone, but those who create lots of temporary table will find the performance boost most welcome. In fact in some cases you maybe better off allocating a little extra memory to a tmpfs then you would be the innodb buffer pool, but like everything it depends on your workload and environment.
What is tmpfs? In a nutshell a filesystem ontop of a ramdisk used for temporay file access. Read the wiki page for more. Sounds like a great thing for /tmp right? By the way It really urks me that most people leave /tmp as part of the root filesystem …. shameful, read my common mistakes post for more complaining about that. Anyways, back to the topic.
What brought this post on? I am working with a client who was having horrendous performance on several seemly simple queries. The worst query, which will remain hidden to protect the innocent was consistently finishing around 13 second each run…. see here:
1028 rows in set (13.75 sec)
1028 rows in set (13.95 sec)
1028 rows in set (13.64 sec)
looking at the explain for the query, it was using a temporary table. It is one of these queries that’s always going to build a temporary table on disk and then do a filesort. You know the ones where you need to rethink the business case for that data because their is not great way to execute it. So without a way to prevent the temp table, and with no way force it into memory within the database I forced it into memory outside the database by pointing the tmpdir to a tmpfs. This is a fairly dramatic difference ( and probably not typical ), but it really underscores that huge performance gains can be gained from small changes:
1028 rows in set (0.26 sec)
1028 rows in set (0.26 sec)
1028 rows in set (0.27 sec)
In summary, If your using lots of text fields, doing lots of sorting, group by’s, etc you may see a nice performance boost by pointing over your tmpdir to tmpfs.
You can create a new tmpfs by issuing the following:
mkdir -p /tmpmysql
mount -t tmpfs -o size=2048M,mode=0777 tmpfs /tmpmysql
then point tmpdir over to /tmpmysql
Don’t forget to add it to you fstab to make the change permanent.
I would like to use this for some queries. But my workloads have many long running queries with huge temp tables created implicitly to process order by and group by clauses. By huge I mean that 10+ GB is common and 100+ GB is used on occasion. I can’t make /tmpfs that big and I can’t set tmpdir per session. I can’t even limit the size of a temp table — although the next Google patch will have that feature.
A couple of Intel ssd’s devoted to the TMPDIR will not be as fast as tmpfs, but maybe a decent compromise.
If I buy the Intel SSDs, I will use them for InnoDB datafiles.
its a scale/cost thing. If your datasets are tb+ sized, it would not be cost effective to deploy the current generation of ssd’s, I mean even if you go with the 80GB mlc drives you still going to have to purchase 20+ drives ( raid, extra space ). Not only is their that cost, but to house that many drive your probably looking at an externally attached enclosure to house them all. If your temp files are < 160gb, two of these could be a viable cost effective solution… or maybe not just depends on the app.
Putting tmpdir on a tmpfs is a really good idea UNTIL you run out of space.
Suppose I have a 16G tmpfs, which is big enough for most queries. It could run for months with no problem until someone runs a particularly gnarly query (or perhaps several at once) which consumes it all, then boom! What happens?
My research suggests that it depends on what MySQL is doing at the time. If it’s a temporary *table*, then it waits indefinitely for space to become available. If it’s a filesort() buffer, it returns an error of some kind. If it’s an internal temporary table, you typically get one of a number of confusing errors saying “invalid index file for #TMP something” or somethimg.
It would be really good if the tmpdir for filesort(), internal temp tables and user temp tables could be set independently, but it can’t.
Oh yes – one other thing – it can’t be set per session, but that would be incredibly useful as well, as a client which knew its temp tables wouldn’t blow up tmpfs could set it there, and everyone else could have /var/tmp as usual.
Note MySQL bug: http://bugs.mysql.com/bug.php?id=30287
where tmpdir isn’t honored.
That’s a chicken and egg argument. Its not just a tmpfs issue, if you allocated 100gb to your /tmp you may run out of space eventually. Your always potentially chasing your tail. 100GB could be good enough for months, even years until someone does something wacky.
Bottom line You need to know your application. Obviously if you have the potential for a 100GB tmp table a tmpfs will not work, Unless you have lots of memory ). One potential fix is ssd for your tmpfs. As always mileage will very.
I want to say that about half my clients I have worked have /tmp as part of the root filesystem, which is bad when they do something and fill up root. Probably another 25% have /tmp as a separate ext3 filesystem with 2-4gb of memory. The remainder are mixed, some tmpfs, some other stuff.
Matt,
I don’t think it is possible for some of us to know our apps but we can know our servers. It would help if there were a way to determine the typical size of a large tmp table, but MySQL provides no way to monitor this. All that I have are:
Created_tmp_disk_tables 154177
Created_tmp_files 32022
Created_tmp_tables 18531868
I can’t keep runaway queries from filling up tmpdir and hurting others. I can’t determine the current size of tmp tables in use via SQL (I can login to the box for that). And I can’t determine the size of large tmp tables used in the past.
I agree with that;) their is a lot missing that I would like to see, its easier to ignore what you don’t need then not to have what you do need.
The challenge is going to be getting good statistics from the database. I don’t think I have ever checked what sort of stats are available during a tmp table build… hmmmmm, I will have to check.
What about LVM or fuse, unionfs, etc… have a tmpfs joined with regular disk. I wonder what the performance impact would be. Maybe interesting to test out.
As for runaway queries hurting others, am I the only one who implements a long running query monitor? I mean its pretty simple to grab the process list and check for anything over an expected value that’s not in sleep mode. I typically then use that to trigger something else, like a super dump of stats. I.e. query is taking over 10 minutes, grab an explain, grab a show innodb status, dump system stats… you could then use that to check your tmpdir and see how full it is, etc. its a little brute force, but sometimes its the only way to be sure you know why things happened a certain way at a certain time.
We monitor and kill queries that run for too long.
I could spend my days checking query plans for queries that take longer than X minutes for too many values of X. Also, InnoDB samples stats so many query plans don’t match reality. And that can only be done when the query doesn’t use temporary or tables.
I don’t mean to sound like a whiner, but I think that without too much effort from code changes in MySQL, the usage of temp tables could be much easier to manage. MySQL is in a great place to realize these changes should be made given that so many people with production experience work closely with developers. That isn’t true at many vendors.
Things that I might want include:
1) tmpdir changed at the session level to one of N known values
2) max temp table created listed per acccount in the information schema (or SHOW USER_STATISTICS)
3) max on-disk size per temp table enforced
I have put 3 in the next Google patch. I might do 2 eventually.
I do like the idea of a fuse filesystem that joins a tmpfs and, when tmpfs is full, a regular filesystem partition. I have already done some fuse coding, I will see what I can do.
[...] and that, using a ramdisk (tmpfs on Linux) can improve performance drastically as Matt has shown here. The problem with tmpfs is the limited size. You need just one query creating a huge temporary file [...]