Search code examples
mysqlsqlquery-optimization

Speeding up mySQL query and avoiding 'copying to tmp table on disk'

I have a database server with over 60G of RAM and SSD drives in RAID10. I'm trying to get a query to run that'll return millions of records (3-6M, most likely). I'm using the following configuration for mySQL --

[mysqld]
max_connections = 500
skip-external-locking
key_buffer = 32M
open_files_limit = 65535
table_cache = 9552
thread_cache = 50
#table-definition-cache = 4096
#table-open-cache = 10240
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
query_cache_size = 512M
join_buffer_size = 1024M
max_heap_table_size = 20G
tmp_table_size = 20G
wait_timeout = 120
interactive_timeout = 120
#innodb-flush-method = O_DIRECT
#innodb-log-files-in-group = 2
#innodb-log-file-size = 512M
#innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 32G
innodb_autoextend_increment=512
innodb_thread_concurrency=18
innodb_locks_unsafe_for_binlog = 1
innodb_lock_wait_timeout=300
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
key_buffer_size = 10G
query_cache_limit = 256M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 16M
sort_buffer_size = 10M
read_buffer = 2M
write_buffer = 2M

The query I'm running is...:

SELECT DISTINCT
Import_AcesApplication.id,
Import_AcesApplication.sku,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
SubModel.SubModelName,
CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
Positions.position
FROM
Import_AcesApplication
STRAIGHT_JOIN BaseVehicle
ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
STRAIGHT_JOIN Parts 
ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
STRAIGHT_JOIN Make
ON BaseVehicle.MakeID=Make.MakeID
STRAIGHT_JOIN Model
ON BaseVehicle.ModelID=Model.ModelID
STRAIGHT_JOIN Vehicle
ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
STRAIGHT_JOIN SubModel
ON Vehicle.SubModelID=SubModel.SubModelID
STRAIGHT_JOIN VehicleConfig
ON Vehicle.VehicleID=VehicleConfig.VehicleID
STRAIGHT_JOIN EngineConfig
ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
STRAIGHT_JOIN EngineDesignation
ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID
STRAIGHT_JOIN EngineVIN
ON EngineConfig.EngineVINID=EngineVIN.EngineVINID
STRAIGHT_JOIN EngineBase
ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
STRAIGHT_JOIN Positions
ON Positions.PositionID=Import_AcesApplication.position_id

EDIT: I've altered the query a bit...

SELECT DISTINCT
Import_AcesApplication.id,
Import_AcesApplication.sku,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
SubModel.SubModelName,
CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
Positions.position
FROM
Import_AcesApplication
STRAIGHT_JOIN BaseVehicle
ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
STRAIGHT_JOIN Parts 
ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
STRAIGHT_JOIN Make
ON BaseVehicle.MakeID=Make.MakeID
STRAIGHT_JOIN Model
ON BaseVehicle.ModelID=Model.ModelID
STRAIGHT_JOIN Vehicle
ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
STRAIGHT_JOIN SubModel
ON Vehicle.SubModelID=SubModel.SubModelID
STRAIGHT_JOIN VehicleConfig
ON Vehicle.VehicleID=VehicleConfig.VehicleID
STRAIGHT_JOIN EngineConfig
ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
STRAIGHT_JOIN EngineBase
ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
STRAIGHT_JOIN Positions
ON Positions.PositionID=Import_AcesApplication.position_id

I'm using STRAIGHT_JOIN to force the order since SELECT EXPLAIN showed it to be incorrect. I've setup my indexes on the tables, but the query seems to be getting stuck on the state of 'copying to tmp table on the disk'. I've tried different tips online like increasing the tmp_table_size and whatnot but nothing has helped.

Can someone please help me figure this out so that the queries are faster?

EDIT: The EXPLAIN results can be seen here.

Solution

  • Have you tried a tmpfs? (Using a RAM disk as your temp table storage)

    Create the tmp directory

    # mkdir -p /var/mysqltmp

    Set permissions

    # chown mysql:mysql /var/mysqltmp

    Determine mysql user id

    # id mysql

    Edit /etc/fstab And add the following line, replacing your specific mysql user id and group id instead of the 105 and 114 below:

    tmpfs /var/mysqltmp tmpfs rw,gid=105,uid=114,size=10G,nr_inodes=10k,mode=0700 0 0

    Mount the new tmpfs partition

    # mount -a

    Change your MySQL configuration

    # vi /etc/mysql/conf.d/local.cnf

    Change, or add the following line:

    tmpdir = /var/mysqltmp

    Restart MySQL

    /etc/init.d/mysql restart

    http://2bits.com/articles/reduce-your-servers-resource-usage-moving-mysql-temporary-directory-ram-disk.html