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

Question

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.

Answer 1

InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.
: Error: Table "mysql"."innodb_table_stats" not found.

Follow the below steps:

mysql> `select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';`
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

Physical files:

innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd

1.

drop table innodb_index_stats;
drop table innodb_table_stats;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;

2.

delete all .frm & .ibd of the tables above.
rm -rf innodb_index_stats.frm
rm -rf innodb_index_stats.ibd
...

3. Create below tables :

   CREATE TABLE `innodb_index_stats` (
     `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `stat_value` bigint(20) unsigned NOT NULL,
     `sample_size` bigint(20) unsigned DEFAULT NULL,
     `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
     PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
   
   CREATE TABLE `innodb_table_stats` (
     `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `n_rows` bigint(20) unsigned NOT NULL,
     `clustered_index_size` bigint(20) unsigned NOT NULL,
     `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
     PRIMARY KEY (`database_name`,`table_name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
   
   CREATE TABLE `slave_master_info` (
     `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
     `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
     `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
     `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
     `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
     `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
     `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
     `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
     `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
     `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
     `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
     `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
     `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
     `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
     `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
     `Heartbeat` float NOT NULL,
     `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
     `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
     `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
     `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
     `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
     `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
     `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
     PRIMARY KEY (`Host`,`Port`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
   
   CREATE TABLE `slave_relay_log_info` (
     `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
     `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
     `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
     `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
     `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
     `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
     `Number_of_workers` int(10) unsigned NOT NULL,
     `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
     PRIMARY KEY (`Id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
   
   CREATE TABLE `slave_worker_info` (
     `Id` int(10) unsigned NOT NULL,
     `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `Relay_log_pos` bigint(20) unsigned NOT NULL,
     `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `Master_log_pos` bigint(20) unsigned NOT NULL,
     `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
     `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
     `Checkpoint_seqno` int(10) unsigned NOT NULL,
     `Checkpoint_group_size` int(10) unsigned NOT NULL,
     `Checkpoint_group_bitmap` blob NOT NULL,
     PRIMARY KEY (`Id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
   

Hope this solves your issue too.

Answer 2

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