MySQL Forums
Forum List  »  Performance

Slow Query too much time in Copy to Tmp Table
Posted by: Gustavo Mateos
Date: October 24, 2013 05:49AM

Hello,

I have this query which is taking too long to execute most of the times. When I check the "show profile" for the query I can see it spends most of the time (several seconds) in "Copy to tmp table".

+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000060 |
| checking query cache for query | 0.000120 |
| Opening tables | 0.000031 |
| System lock | 0.000018 |
| Table lock | 0.000070 |
| init | 0.000057 |
| optimizing | 0.000030 |
| statistics | 0.000135 |
| preparing | 0.000037 |
| Creating tmp table | 0.000099 |
| executing | 0.000015 |
| Copying to tmp table | 2.344832 |
| Sorting result | 0.333453 |
| Sending data | 0.359093 |
| end | 0.000037 |
| removing tmp table | 0.000976 |
| end | 0.000013 |
| query end | 0.000007 |
| freeing items | 0.000021 |
| removing tmp table | 0.000018 |
| freeing items | 0.000029 |
| storing result in query cache | 0.000355 |
| logging slow query | 0.000008 |
| logging slow query | 0.000049 |
| cleaning up | 0.000009 |
+--------------------------------+----------+


It's running in a server with RHEL, 16GB RAM and 2 QuadCom (8 processors total).

This is the query:

SELECT `visit_client`, COUNT(DISTINCT `sid`) as 'impr'
FROM `track_visits_store` FORCE INDEX (`visit_unique`), `track_acts_store`
WHERE `visit_unique`=`act_unique` AND `act_time`>=1382511600 AND `act_time`<1382515200
GROUP BY `visit_client`


And the info about the tables involved:

track_visits_store STATUS:

Name: track_visits_store
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 44791389
Avg_row_length: 41
Data_length: 1836446949
Max_data_length: 11540474045136895
Index_length: 1708906496
Data_free: 0
Auto_increment: NULL
Collation: latin1_general_ci

track_visits_store CREATE:

CREATE TABLE `track_visits_store` (
`visit_client` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_unique` bigint(20) unsigned NOT NULL,
`visit_ip` int(10) unsigned NOT NULL DEFAULT '0',
`visit_actions` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_land` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
`visit_traffic_extra1` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_extra2` int(10) unsigned NOT NULL DEFAULT '0',
`visit_traffic_keyword` int(10) unsigned NOT NULL DEFAULT '0',
`visit_page` int(10) unsigned NOT NULL DEFAULT '0',
`visit_country` smallint(5) unsigned NOT NULL DEFAULT '0',
`visit_bot` tinyint(1) unsigned NOT NULL DEFAULT '0',
KEY `visit_client` (`visit_client`),
KEY `visit_unique` (`visit_unique`),
KEY `visit_land` (`visit_land`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

***************************
track_acts_store STATUS:

Name: track_acts_store
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 159676779
Avg_row_length: 25
Data_length: 3991919475
Max_data_length: 7036874417766399
Index_length: 6656314368
Data_free: 0
Auto_increment: 159676780
Collation: latin1_general_ci

track_acts_store CREATE:

CREATE TABLE `track_acts_store` (
`sid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`act_unique` bigint(20) unsigned NOT NULL,
`act_time` int(10) unsigned NOT NULL DEFAULT '0',
`act_page` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`sid`),
KEY `page_unique` (`act_unique`),
KEY `act_time` (`act_time`)
) ENGINE=MyISAM AUTO_INCREMENT=159676780 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

And this is my server configuration:

--datadir=/var/lib/mysql
--tmpdir=/dev/shm
--socket=/var/lib/mysql/mysql.sock
--skip-locking
--table_cache=4096
--table_definition_cache=4096
--thread_cache_size=150
--back_log=2048
--max_connect_errors=99999
--open-files-limit=65535
--interactive_timeout=30
--wait_timeout=30
--max_connections=500
--max_user_connections=520
--log-slow-queries=mysql-slow.log
--long_query_time=2
--log-queries-not-using-indexes
--max_allowed_packet=16M
--tmp_table_size=64M
--max_heap_table_size=64M
--query_cache_size=0
--query_cache_min_res_unit=1536
--query_cache_type=0
--sort_buffer_size=1M
--read_buffer_size=1M
--read_rnd_buffer_size=8M
--join_buffer_size=10M
--key_buffer_size=3072M
--myisam_sort_buffer_size=64M
--innodb_log_file_size=100M
--innodb_buffer_pool_size=16M

This is the MySQL configuration:

--datadir=/var/lib/mysql
--tmpdir=/dev/shm
--socket=/var/lib/mysql/mysql.sock
--skip-locking
--table_cache=4096
--table_definition_cache=4096
--thread_cache_size=150
--back_log=2048
--max_connect_errors=99999
--open-files-limit=65535
--interactive_timeout=30
--wait_timeout=30
--max_connections=500
--max_user_connections=520
--log-slow-queries=mysql-slow.log
--long_query_time=2
--log-queries-not-using-indexes
--max_allowed_packet=16M
--tmp_table_size=64M
--max_heap_table_size=64M
--query_cache_size=0
--query_cache_min_res_unit=1536
--query_cache_type=0
--sort_buffer_size=1M
--read_buffer_size=1M
--read_rnd_buffer_size=8M
--join_buffer_size=10M
--key_buffer_size=3072M
--myisam_sort_buffer_size=64M
--innodb_log_file_size=100M
--innodb_buffer_pool_size=16M


Is there any way to reduce the time for this query?

Thanks in advance,

Gustavo Mateos,
SkyYield,
Madrid, Spain

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Query too much time in Copy to Tmp Table
45624
October 24, 2013 05:49AM


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.