MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in Mysql Workbench server administration and the number increases with roughly 50 tables/s. After a days usage, created_tmp_disk_tablesis >100k. Also, the memory does not seem to be released. The usage keeps increasing until the system becomes pretty much unusable and we have to re-start MySQL. I need to re-start it almost every day and it begins with using about 30-35% of available memory and finishing the day with 80%.

I have no blobs in the database and no control over the queries either so I can't attempt to optimise them. I have also used the Percona Confirguration Wizard to generate a configuration file but that my.ini didn't solve my problem either.
Questions

-
What should I change to stop MySQL from creating temporary tables on disk? Are there settings I need to change? Should I throw more memory at it?

-
How can I stop MySQL from eating up my memory?

Edit

I enabled slow_queries log and discovered that the query SELECT GET_LOCK() was logged as slow. A quick search revealed that I had allowed persistent connections in the PHP configuration (mysqli.allow_persistent = ON). I turned this off. This reduced the rate at which MySQL consumes memory.It is still creating temporary tables though.

I also checked that the key_buffer size is large enough.
I looked at the variable key_writes. This should be zero. If not, increase the key_buffer_size.I have zero key_reads and zero key_writes so I assume that the key_buffer_size is large enough.

I increased the tmp_table_size and max-heap-table-size to 1024M as an increase in created_tmp_disk_tables may indicate that the tables can't fit in memory. This didn't solve it.

Ref:
http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/
Edit 2

If you see

Looking at the my.ini, I have two suggestions

SUGGESTION #1

I would bump up the following settings in your my.ini

sort_buffer_size=4M
join_buffer_size=4M

This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table.

If you cannot login as root@localhost, then restart mysql with

C:\> net stop mysql
C:\> net start mysql

If you can login as root@localhost, you do not have to restart mysql to use these settings.

Just run this in the MySQL client:

SET @FourMegs = 1024 * 1024 * 4;
SET GLOBAL sort_buffer_size = @FourMegs;
SET GLOBAL join_buffer_size = @FourMegs;

SUGGESTION #2

Since your Data is on Drive D:, you may have Disk I/O on Drive C:.

Please run this query:

mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)

Since I run mysql on my Desktop with defaults, my temp tables are being written to Drive C:. If Drive D is a better disk than Drive C:, perhaps you can map temp tables to Drive D: by setting tmpdir in my.ini as follows:

tmpdir="D:/DBs/"

You will have to restart mysql since tmpdir is not a dynamic variable.

Give it a Try !!!

UPDATE 2013-11-29 10:09 EST

SUGGESTION #3

Given the fact that MySQL is running in Windows and you cannot touch the queries in the core package, I have two ideas tat must be done together.

IDEA #1 : Move the Database to a Linux Machine

You should be able to

• Setup a Linux machine

• Install MySQL on the Linux machine

• Enable Binary Logging for MySQL in Windows

• mysqldump the database to a text SQL file

• Load SQL file to MySQL running in Linux

• Setup replication from MySQL/Windows to MySQL/Linux

IDEA #2 : Reconfigure Moodle to point to the Linux Machine

Moodle was designed for LAMP in the first place. Just change the config files to point to the Linux machine instead of localhost.

Here is a link to an old Moodle 2.3 doc on setting up MySQL : http://docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database

I am sure the latest docs are available as well.

What is the Point of Moving the Database to Linux ???

How does this help the temp table situation ???

I would then suggestion setting up a RAM disk as the target folder for your temp tables

• Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

• Dec 17, 2012 : Why does MySQL produce so many temporary MYD files? (Actual instructions)

• Nov 30, 2012 : Is it bad to create many mysql temporary tables simultaneously?

Temp table creation will still happen, but it will be written to RAM rather than disk. reducing Disk I/O.

UPDATE 2013-11-29 11:24 EST

SUGGESTION #4

I would suggest revisiting SUGGESTION #2 with a fast RAID-0 disk (32+ GB), configuring it as Drive T: (T for Temp). After installing such a disk, add this to my.ini:

[mysqld]
tmpdir="T:\"

MySQL restart would be required, using

net stop mysql
net start mysql

BTW I said RAID-0 on purpose so that you can get good write performance over a RAID-1, RAID-10. A tmp table disk is not something I would make redundant.

Without optimizing the queries as @RaymondNijland has been commenting on, you cannot reduce the temp table creation count in any way. SUGGESTION #3 and SUGGESTION #4 offer speeding up temp table creation and temp table I/O as the only alternative.

StackExchange Database Administrators Q#53201, answer score: 16