mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you. That being said, here's a little background information about temporary tables...
MySQL internally uses the MEMORY storage engine for creating implicit temporary tables. On disk temporary tables use the MyISAM storage engine.
Temporary tables are created on disk when:
- TEXT or BLOB fields are present (because MEMORY doesn't support these types)
- the size of the resulting implicit temporary table exceeds the lesser of
tmp_table_size
or max_heap_table_size
- If a column w/ more than 512 bytes is used with either a GROUP BY or UNION or ORDER BY
Read the MySQL Documentation on Internal Temporary Tables for more details.
What can you do about this? Presuming that it actually represents a performance problem (rather than just bothering you intelluctually):
- Avoid TEXT/BLOB fields and instead use appropriately sized VARCHAR or CHAR fields where possible.
- If TEXT/BLOB are unavoidable, sequester them to separate tables with a foreign key relationship and JOIN only when you need them.
- Treat large columns, more than 512 bytes as you would the above mentioned TEXT/BLOB fields.
- Make sure your queries are returning only the result set you need (appropriately selective WHERE clauses, avoid SELECT *)
- Avoid subqueries and replace them with joins, especially if they return a large result set
- Last resort - raise both
tmp_table_size
and max_heap_table_size
. Don't do this unless you find that your queries cannot be optimized.
If you are concerned about your MySQL configuration and are not comfortable with the available settings yourself, you might want to check out the Percona Configuration Wizard as a starting point.
Will changing the db engine from "myisam" to "memory" on tables using
"group by" fix this? as explained here
No, it won't and it will make it such that your tables are never persisted to disk. Don't do this.
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
What do you mean by a "26GB Heap"?
tmp_table_size = 256M
is dangerously high. If multiple connections decide to need tmp tables, you could run out of RAM. Swapping is worse for performance than lowering various settings.Tmp tables are necessary in many situations. Don't fear them. But do check on them.
DISTINCT
,GROUP BY
,ORDER BY
andUNION
often require a tmp table. If the tmp table fits inmin(tmp_table_size, max_heap_table_size)
, then the tmp table may be in RAM usingEngine=MEMORY
. If bigger than that, then the tmp table isEngine=MyISAM
and is slower. There are other reasons for using MyISAM, most notably is selecting aTEXT
field. More details.Another common "error" is to blindly use
VARCHAR(255)
and utf8. When using MEMORY, that becomes a 765-byteCHAR
, which hastens the conversion to MyISAM.The numbers you gave...
tmp_table_size
is not big enough (which I doubt), or MEMORY cannot be used (which I suspect). Above 20% is a red flag in my analysis.Set long_query_time = 1 and turn on the SlowLog, preferably to FILE. Wait a day, then use pt-query-digest on the slowlog to find the "worst" queries. If you can't figure out how to improve them, ask us.
I'll disagree with Rolando on two items:
OPTIMIZE TABLE
is rarely useful, and not very relevant for the question.