Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 5

Displaying posts with tag: TMPFS (reset)

MySQL and RAMdisk, or how to make tmpdir-usage queries faster
+1 Vote Up -0Vote Down

Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.

Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than

  [Read more...]
Unexpected mysqld crashing in 5.5
+3 Vote Up -2Vote Down

An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

Update: MySQL tmpdir on tmpfs
+0 Vote Up -0Vote Down

Followup on Experiment: MySQL tmpdir on tmpfs, about tmpdir=/dev/shm in my.cnf (it’s not a dynamic variable that can be set at runtime). It’s working well, also confirmed by comments from others that they’ve been using it for a while.

This particular setting is Linux specific. On Solaris, the default /tmp is already on a tmpfs so that’s fine too. Brian reminded me that this tweak is also useful if you’re stuck with a 32-bit OS as you can then utilise some more memory in a practical way.

Extra useful hint from Harrison: if you are using replication, you will also want slave_load_tmpdir=/tmp on your slave (real disk which survives a restart). The issue is that with statement based binary logging, there are many events which create a file for a

  [Read more...]
Experiment: MySQL tmpdir on tmpfs
+3 Vote Up -3Vote Down

In MySQL, the tmpdir path is mainly used for disk-based sorts (if the sort_buffer_size is not enough) and disk-based temp tables. The latter cannot always be avoided even if you made tmp_table_size and max_heap_table_size quite large, since MEMORY tables don’t support TEXT/BLOB type columns, and also since you just really don’t want to run the risk of exceeding available memory by setting these things too large.

You can see how your server is doing with temporary tables, how many of those become disk tables, and also other created temporary files, by looking at SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

So why might tmpfs be better? Well, not just any tmpfs, but specifically

  [Read more...]
A Question of Speed and Technological Progress
+0 Vote Up -0Vote Down
Follow up post to this one
At my company, we have some really old web servers that host our website. They are from late 2004 and early 2005. Now, at the time, I am sure they were quite expensive.

Recently, we wanted to move to newer web servers and we bought the basic Dell R200 rack servers. Now, arguably they are quite cheap, but thinking about it they will definitely be faster then 4 year old servers, right? Well, that’s what my predecessor thought when he bought those servers and I myself didn’t think about


  [Read more...]
Showing entries 1 to 5

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.