In my.cnf
use this option
tmpdir=/tmp
(this is the default anyway, just choose another folder)
Restart of mysql required
BTW since the location.ID
is '193'
why even involve the location table ???
Try the following :
SELECT DISTINCT
activity_seismo_info.ID,
activity_seismo_info.CREATED_AT,
activity_seismo_info.UPDATED_AT,
#....
activity_seismo_info.ASI_EXTRA_5,
seismo.ID,
#....
seismo.SEISMO_NOTES
FROM `activity_seismo_info`
JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID)
JOIN (SELECT ID from location WHERE ID='193') location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID)
;
For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.
Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:
Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab
none /var/tmpfs tmpfs defaults,size=32g 1 2
Next, create a folder called /mysqltmp and mount the RAM disk on it
mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs
Add this to my.cnf and restart mysql
[mysqld]
tmpdir=/mysqltmp
Now, any tmp table made via DDL lands in the RAM disk.
Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?
CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;
This will prevent any moving around of BLOB data when indexing.
From here, you would have to always join data_store using its name like this:
SELECT
A.uniqname,B.data
FROM
(SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
LEFT JOIN
data_store B USING (id)
;
Making these changes will sidestep this whole mess of dealing with keycache, RAM disks, and tmp tables.
Give it a Try !!!
Best Answer
If your new instance of MySQL is creating temporary tables with the InnoDB storage engine, the
innodb_io_capacity
could be throttling the rate at which it writes pages to disk.However, even throttled, I would expect a mere 400MB to be pretty quick to write to disk. Have you considered the possibility that the disk drives on your new server are simply faulty?
I would recommend running some benchmarks with sysbench or iozone to see if you can reproduce the slow I/O speed without MySQL.
Re your comment:
The best figures to look at for a database server are random read, random write, and mixed workload. Run a similar benchmark on your old database server, and compare them. If your new database server has significantly lower numbers, that would explain the difference you're seeing.