Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com
As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.
As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.
I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.
You should upgrade InnoDB in one of two ways:
Once you have done so, you can enhance InnoDB to
- access more CPUs and Cores
- increase read and write I/O threads
- scale the I/O capacity (this is especially needed for different storage media)
Here are my past posts on the settings you can change for 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
This is a complex topic which is very interesting - I/O, caching - i.e. what's really happening?
Percona always have good stuff about every aspect of MySQL performance - check here. Mark Leith (a senior MySQL devlelopment manager) is worth a look here. Be sure to follow the links to Baron Schwartz's and Brendan Gregg's stuff, both big hitters in the MySQL world. Morgan Tocker is a MySQL community manager and has this to say.
[EDIT - in response to comment by OP]
As I said - it's complex. What is i/o as far as Google/Amazon are concerned? See my own answer (interpretation) to this question below? How do they factor in caching? I'm sure they do it in the background. I do notice that
Amazon appear to charge by GB/month in/out, so you don't care about disk or queries with them.
Google likewise appear to charge by data in/out. So, many small queries will be equal to a few large ones - seems fair.
Both also charge for RAM, so a complex app will also increase price - again, not unreasonably.
My reading of the data/in out charge is that this only counts for bandwidth between your end users and Amazon or Google - I/O processing done within Amazon/Google is covered by your arrangement with them.
Get your calculator out! :-) IANAL!