MySQL Forums
Forum List  »  MyISAM

Status "Copying to tmp table" for many hours
Posted by: R. M.
Date: February 22, 2006 04:09AM

I'm trying to run the following query, but it takes hours to run without progress. When using SHOW PROCESSLIST, I see the status "Copying to tmp table".

###

INSERT INTO
data_table
SELECT
site_available.av_id,
site_available.av_price,
site_available.av_bookingcosts,
site_available.av_rebate,
site_available.av_normalprice,
site_available.av_startdate,
site_available.av_enddate,
site_available.av_starttime1,
site_available.av_starttime2,
site_available.av_days,
site_available.av_day,
site_available.av_atrequest,
site_available.av_partner,
site_available.av_lastminute,
site_available.av_directlink,
site_houses.hs_houseid,
site_houses.hs_housecode,
site_houses.hs_housecode2,
site_houses.hs_description,
site_houses.hs_city,
SOUNDEX(site_houses.hs_city),
site_houses.hs_region,
site_houses.hs_ctid,
site_houses.hs_minpersons,
site_houses.hs_maxpersons,
site_houses.hs_stars,
site_houses.hs_pets,
site_houses.hs_imgsmall1,
site_houses.hs_img1,
NULL,
NULL
FROM
site_available,
site_houses
WHERE
site_available.av_housecode=site_houses.hs_housecode AND
site_available.av_partner=site_houses.hs_partner
GROUP BY
site_available.av_id

###

Running the SELECT query only works fine. It takes about 1 minute to execute (about 1.3 million records) and that's fast enough.

When all records from the SELECT statement are inserted in data_table, it will have a size of about 800 megabytes (600 MB data, 200 MB indexes).

Data_table looks like:

###

CREATE TABLE data_table (
av_id bigint(20) NOT NULL default '0',
av_price double NOT NULL default '0',
av_bookingcosts double NOT NULL default '0',
av_rebate double NOT NULL default '0',
av_normalprice double NOT NULL default '0',
av_startdate date NOT NULL default '0000-00-00',
av_enddate date NOT NULL default '0000-00-00',
av_starttime1 time NOT NULL default '00:00:00',
av_starttime2 time NOT NULL default '00:00:00',
av_days int(11) NOT NULL default '0',
av_day bigint(20) NOT NULL default '0',
av_atrequest tinyint(4) NOT NULL default '0',
av_partner varchar(100) NOT NULL default '',
av_lastminute tinyint(4) NOT NULL default '0',
av_directlink varchar(255) NOT NULL default '',
hs_houseid bigint(20) NOT NULL default '0',
hs_housecode varchar(100) NOT NULL default '',
hs_housecode2 varchar(30) NOT NULL default '',
hs_description varchar(255) NOT NULL default '',
hs_city varchar(100) NOT NULL default '',
hs_soundex varchar(4) NOT NULL default '',
hs_region varchar(100) NOT NULL default '',
hs_ctid bigint(20) NOT NULL default '0',
hs_minpersons int(11) NOT NULL default '0',
hs_maxpersons int(11) NOT NULL default '0',
hs_stars int(11) NOT NULL default '0',
hs_pets int(11) NOT NULL default '0',
hs_imgsmall1 varchar(100) NOT NULL default '',
hs_img1 varchar(100) NOT NULL default '',
tmp_order tinyint(4) NOT NULL default '0',
tp_show tinyint(4) NOT NULL default '0',
PRIMARY KEY (av_id),
KEY hs_maxpersons (hs_maxpersons,hs_ctid),
KEY av_days (av_days,hs_ctid),
KEY hs_stars (hs_stars,hs_ctid,av_startdate,hs_maxpersons),
KEY av_startdate (av_startdate,av_days,hs_ctid,tp_show,tmp_order,hs_housecode,av_price),
KEY hs_housecode (hs_housecode),
KEY av_partner (av_partner),
KEY hs_soundex (hs_soundex,av_startdate,av_days),
KEY hs_ctid (hs_ctid,av_startdate,av_days),
KEY hs_ctid_2 (hs_ctid,av_startdate,hs_maxpersons)
) TYPE=MyISAM;

###

I have a similar table without all those index keys, and when I'm running the same INSERT ... SELECT query, it works fine (takes about 20 minutes or something like that). So I think the delay is caused by those index keys on data_table and a configuration file which isn't optimal.

The MySQL configuration file looks like:

###

my.cnf

[mysqld]
query_cache_limit=8M
query_cache_size=128M
query_cache_type=1
thread_concurrency=2
max_connections=900
interactive_timeout=90
wait_timeout=100
connect_timeout=30
thread_cache_size=256
key_buffer = 256M
key_buffer_size = 128M
join_buffer=256M
join_buffer_size=128M
max_allowed_packet=32M
table_cache=4096
record_buffer=2M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=90
myisam_sort_buffer_size=32M
table_cache=2M
tmp_table_size=512M

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /home/var/db/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/var/db/mysql/innodblogs/
innodb_log_arch_dir = /home/var/db/mysql/innodblogsarchive/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 10M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 24M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

###

One note: we have 2 GB RAM on the server, MySQL should be allowed to take 1 GB RAM of it. I'm only using MyISAM tables.

Thanks in advance!

P.S. I hope this is the right forum, if not, some administrator should move it into the right direction.

Options: ReplyQuote


Subject
Views
Written By
Posted
Status "Copying to tmp table" for many hours
2986
February 22, 2006 04:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.