There are some options that can cause temp tables to materialize as MyISAM tables or can be configured to delay it. Keep in mind that for disk-based temp tables, there are no .frm
files, but only .MYD
and .MYI
files (of course. the .MYI file is never used since it is impossible index an internal temp table).
Here are the options:
You should also consider the MySQL Documentation on Internal Temp Table Usage
The situations where in-memory temp tables are made are
- If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
- DISTINCT combined with ORDER BY may require a temporary table.
- If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
When an in-memory temp table exceeded the minimum of (tmp_table_size or max_heap_table_size), mysqld does the following:
- Suspends the query
- Copies the in-memory table's contents into a MyISAM temp table
- Discards the in-memory table
- Continues the query, sending the temp data into the MyISAM temp table
The situations where in-memory temp tables are bypassed in favor of disk are
- Presence of a BLOB or TEXT column in the table
- Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
- Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used
Some due diligence is required to reduce temp table creation on disk
- Setting join_buffer_size bigger
- Setting sort_buffer_size bigger
- Setting tmp_table_size and max_heap_table_size bigger
- Tuning queries to minimize or even prevent temp tables
- Creating indexes to create presorted view of data from individual tables
- Installing additional RAM to accommodate large in-memory temp tables
If after such due diligence, there are still temp tables being formed on Disk, here is one desperate move: Mapping disk-based temp table creation to memory.
Here is a quick-and-dirty way to set up a 16GB RAM Disk using tmpdir
STEP01) Create RAM Disk Folder
mkdir /var/mysql_tmpfs
STEP02) Add this to my.cnf
[mysqld]
tmpdir=/var/mysql_tmpfs
STEP03) Add this to /etc/fstab
echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab
STEP04) Reload /etc/fstab
mount -a
STEP05) service mysql restart
After this, all temp table that become MyISAM are written to the RAM Disk. This should speed disk-based temp table creation.
Give it a Try !!!
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
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.
Best Answer
After alot of digging I found the answer.
Inside %AppData%..\MySQL I found "server_instances.xml". For some reason it listed MySQL server version as 5.5 even though workbench AND command line both showed it is 8.0, and 5.5 was never installed in my machine.
I searched for all mentions of 5.5 and swapped them for 8.0. Saved, rebooted machine (just to be sure) and opened workbench. Created a new config file via the Options File under the Administration in workbench. Everything works :)