Wow, that is a doozie of a query. At this point it has little to do with your configuration variables and you should focus on how to optimize the query.
It is tough to give you a magic bullet, though, without knowing what question you are trying to determine from this monstrous query. At first glance, it appears it is trying to do too much.
I would start by removing these two problems from your EXPLAIN
plan:
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 397520 Using temporary; Using filesort
2 DERIVED <derived3> ALL NULL NULL NULL NULL 397520 Using temporary; Using filesort
The first I think is the query for 'order_stats' derived table, the second I am assuming is the 'main' derived table, though it might be flipped. Regardless, I would start at the 'order_stats' (leave a comment with the pastebin of the explain and running time of non-explain, please).
Depending on what this query is trying to accomplish, I would recommend storing the results in a temporary table to be used in the join. One question I would like to know from your developer is, what is the english of the package_tickets
and order_tickets
computed columns. And if it could be accomplished another way if it turns out to be the bottleneck.
Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.
I wrote posts on why not to do this:
However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.
For example
- Suppose the table to shrink is
mydata.mytable
datsdir
is /var/lib/mysql
Your procedure would be
STEP 01: You should create a database called mydatatemp
. Do not put any data in it just yet.
STEP 02: Move the /var/lib/mysql/mydatatemp
over to another disk.
STEP 03: Create a Symlink /var/lib/mysql/mydatatemp
over to the folder on the other disk
STEP 04: chown -R mysql:mysql
against the external folder and against the symlink.
STEP 05: Now perform the data shrinkage as follows:
CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;
To recap, do the following (assuming /tmpdata
is the external disk)
STEPS 01-04
chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp
then run STEP 05
CAVEAT
This would be rather high risk, but it should work. Please test this with an empty table first like this:
USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;
If these SQL command worked, go to OS and do this:
cd /tmpdata
ls -l
You should see two files:
Also, test if mrnorm can be moved
CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;
Go to the OS and run
cd /tmpdata
ls -l
The table should be gone. Now, do this:
cd /var/lib/mysql/helloworld
ls -l
The table should be there
END OF TEST
Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.
Best Answer
This is a really interesting question.
You could check the
But that only gives you the overall number of tables created (or the delta, if you subtract that value between two points of time), but not the actual size needed.
You could go the "logical" wayin MySQL 5.6, configuring
performance_schema
in order to log a certain amount of queries, enough to check how many temporary tables are being created. You can find those onOn the
CREATED_TMP_DISK_TABLES
andCREATED_TMP_TABLES
. That will give you more information about the type of temporary tables created, but still no information on the actual number or rows written or the size at a moment in time.I would probably check it the physical way (assuming a UNIX-like OS), by something like this:
Checking the total size of files that the mysqld process has open on /tmp along time may give you a better indication, assuming you monitor that at your peak time, or a significant amount of time.