LINUX.ORG.RU

Тормозит Coping to tmp table


0

1

Помогите, плиз.

Структура:

CREATE TABLE `tez_spo_rows` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `id_spo` int(7) NOT NULL DEFAULT '0',
  `country` smallint(4) NOT NULL DEFAULT '1104',
  `sporegionset` int(9) NOT NULL DEFAULT '0',
  `number` int(4) NOT NULL DEFAULT '0',
  `hotelroomtype` mediumint(7) NOT NULL DEFAULT '0',
  `startdate` date NOT NULL DEFAULT '0000-00-00',
  `nightcount` tinyint(2) NOT NULL DEFAULT '0',
  `hotelpansion` mediumint(7) NOT NULL DEFAULT '0',
  `staytype` int(6) NOT NULL DEFAULT '0',
  `grouptypeage` int(7) NOT NULL DEFAULT '0',
  `hotelstop` enum('N','Y') NOT NULL DEFAULT 'N',
  `price` int(5) NOT NULL DEFAULT '0',
  `show` enum('N','Y') NOT NULL DEFAULT 'Y',
  `residence` int(7) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`startdate`),
  KEY `hotelpansion` (`hotelpansion`),
  KEY `nightcount` (`nightcount`),
  KEY `residence` (`residence`),
  KEY `hotelroomtype` (`hotelroomtype`),
  KEY `staytype` (`staytype`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY RANGE ( MONTH(startdate))
(PARTITION m_01 VALUES LESS THAN (2) ENGINE = MyISAM,
 PARTITION m_02 VALUES LESS THAN (3) ENGINE = MyISAM,
 PARTITION m_03 VALUES LESS THAN (4) ENGINE = MyISAM,
 PARTITION m_04 VALUES LESS THAN (5) ENGINE = MyISAM,
 PARTITION m_05 VALUES LESS THAN (6) ENGINE = MyISAM,
 PARTITION m_06 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION m_07 VALUES LESS THAN (8) ENGINE = MyISAM,
 PARTITION m_08 VALUES LESS THAN (9) ENGINE = MyISAM,
 PARTITION m_09 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION m_10 VALUES LESS THAN (11) ENGINE = MyISAM,
 PARTITION m_11 VALUES LESS THAN (12) ENGINE = MyISAM,
 PARTITION m_12 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ AUTO_INCREMENT=133836328 ;

Таблица содержит 100 млн. записей и весит с индексами 11Гб. Таблица сегментирована по месяцам, но особого прироста в скорости от этого я не заметил. Не могу даже проверить, работает это или нет. В show processlist почти всегда висит Copying to tmp table. Процентов 90 времени. Получается, что индексы работают, выбирает быстро, но копирует во временную таблицу очень долго. tmp_table_size выкручивал до 5 гигабайт - толку никакого. Сервер физический, с местом проблем нет. Памяти 8 Гб, проц 4 ядра какого-то ксеона.

Конфиг my.cnf.

#
# * Fine Tuning
#
key_buffer		= 512M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 200
table_cache            = 500
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 8M
query_cache_size        = 256M
tmp_table_size		= 1024M
max_heap_table_size	= 1024M
open_files_limit	= 1400
join_buffer_size	= 32M

Сделал временную директорию tmpfs и смонтировал её на /tmp/mysqltmp как описано в статье: http://www.gnuman.ru/stuff/mysql_and_tmpfs/ говорят помогает. Что еще можно сделать? В идеале нужно свести к минимуму этот Copying to tmp table. Так как это все преобретает лавинный характер. Когда один-два человека выбирают, запрос длится менее минуты и человек дожидается результатов. Когда же в это время еще народ заходит - запросы виснут намертво.

Запрос:

SELECT SQL_BUFFER_RESULT it.id, it.id_spo, it.number, DATE_FORMAT(it.startdate, '%d.%m.%Y') AS startdate,
it.nightcount, r1.name AS region1, r2.name AS region2, r1.airp_id AS airport_id, it.staytype as itstaytype, it.hotelpansion as ithotelpansion, it.hotelroomtype as ithotelroomtype,
ht.name AS hotel, htp.name AS stars, it.hotelroomtype as htrid, htr.roomname AS room, it.hotelstop AS ithotelstop, st.name AS staytype, p.name AS pansion, MIN(concat(LPAD(it.price, 5, '0'),it.id)) as price, it.startdate AS itstartdate, hd.description as hotel_descr, ht.id as hotelid
FROM `tez_spo_rows` it
LEFT JOIN it_hotelRooms htr ON (htr.id = it.hotelroomtype) LEFT JOIN it_stayTypes st ON (st.id = it.staytype)
LEFT JOIN it_hotelPansions hp ON (hp.id = it.hotelpansion) LEFT JOIN it_groupTypeAges gt ON (gt.id = it.grouptypeage) LEFT JOIN it_Hotel ht ON (ht.id = htr.hotel) LEFT JOIN it_hotelTypes htp ON (htp.id = ht.hoteltypeid)
LEFT JOIN hotels_description hd ON (hd.id=ht.id) LEFT JOIN it_pansions p ON (p.id = hp.pansion) LEFT JOIN it_regions r1 ON (r1.id = ht.regionid)
LEFT JOIN it_hotelResources hr ON (hr.id = it.residence) LEFT JOIN it_Hotel ht1 ON (ht1.id = hr.hotel) LEFT JOIN it_regions r2 ON (r2.id = ht1.regionid)
WHERE it.show='Y' and it.startdate between '2011-04-27' and '2011-05-02' and
it.nightcount between 2 and 21 and it.staytype='2' and ((r1.id='1285' and r2.id is NULL)) and p.id in(135181, 134164, 5737, 166571, 9004341, 9003550, 9003637, 9002096, 9001916, 30205, 21346, 30204,  5738, 8590, 59695, 60116, 113787, 60107, 30403, 62182, 61632, 32801, 125113, 60040, 60096, 61634, 60090, 48519, 52219, 30523, 115693, 27423, 9003509, 9002803, 177496, 249861, 189732, 9003789, 9001262, 172148, 9003396, 9003460, 9001915, 9005759, 9003648, 172685, 190482, 9001378, 152983, 248750, 145022, 3016698, 203911, 3013774, 3013785, 9002759, 3040106, 173141, 3013611, 9006510, 9006509, 166572, 3023141, 9007750, 3017960, 259265, 3014645, 9007655, 186620, 3016327, 188788, 269549, 7068490, 279543, 259264, 279149)  and ht.hoteltypeid in (2570) and gt.bigchildcount='0' and gt.smallchildcount='0' and gt.adultcount='2' and it.hotelstop='N'
GROUP BY ht.id, htr.id, it.startdate, it.nightcount, it.hotelpansion, it.sporegionset, it.residence ORDER BY price ASC limit 0, 100;

Explain запроса в скриншоте.

http://files.kdk-labs.ru/tez_explain.gif

Размещать tmpdir на tmpfs имеет смысл только тогда, когда создаваемые временные таблицы не влазят в tmp_table_size и max_heap_table_size. Так может происходить в данном случае, если у вас в запросе присутсвуют BLOB/TEXT-поля, или размер какого-либо столбца в GROUP BY больше 512 байт.

В любом случае, нужно обратить внимание на характер нагрузки при выполнении такого вот рода запросов, проследить за Created_tmp_tables/Created_tmp_disk_tables в SHOW STATUS.

Кроме того, неплохо было бы увидеть SHOW STATUS, SHOW VARIABLES, DESC используемых таблиц в запросе и SHOW INDEX FROM их же.

А вообще, с очень большой долей вероятности поможет перенос JOIN/GROUP BY на сторону приложения.

m0use ()

using filesort в первой строчке говорит, что таблица не помещается в оперативную память, а значит джоины будут работать очень медленно. Лучшие избавиться от джоинов и использовать несколько запросов.

Сделал временную директорию tmpfs и смонтировал её на /tmp/mysqltmp как описано в статье:

не поможет, лучше просто увеличить размер join буферов в мускуле.

anonymous ()
Ответ на: комментарий от anonymous

> using filesort в первой строчке говорит, что таблица не помещается в оперативную память

Бред. Using filesort говорит лишь о том, что при сортировке не может быть применен обычный обход B-дерева (индекса), и сортировка будет выполняться самим MySQL-ем.

m0use ()
Ответ на: комментарий от anonymous

> не поможет, лучше просто увеличить размер join буферов в мускуле.

Экспериментировать с join_buffer_size имеет смысл только в том случае, если для JOIN невозможно использовать индексы.

Нужно помнить, что выделяется как минимум join_buffer_size на каждый JOIN с FULL SCAN-ом. Причем, выделяется индивидуально для каждого JOIN-а в каждом потоке. Т.е. если, например, будет 100 одновременных запросов с полным сканированием для одного лишь JOIN-а, и join_buffer_size будет 32М, то получим выделение 3200M памяти.

Соответственно, в данном случае (нету FULL SCAN нигде) это никак не повлияет на выполнение запроса.

m0use ()
Ответ на: комментарий от m0use

SHOW STATUS; Created_tmp_disk_tables    0 Created_tmp_files    332 Created_tmp_tables    0

Запрос немного уменьшил. Убрал некоторе количество JOIN таблиц/ Но все равно узкое место - это Copying to tmp table. join_buffer_size и так установлен в 32 Мб.

Запрос уменьшился до такого состояния: [code] SELECT SQL_BUFFER_RESULT MIN(concat(LPAD(it.price, 5, '0'),it.id)) as price FROM tez_spo_rows it LEFT JOIN it_HotelPansion hp ON (hp.id = it.hotelpansion) LEFT JOIN it_Hotel ht ON (ht.id = hp.hotel) LEFT JOIN it_regions r1 ON (r1.id = ht.regionid) LEFT JOIN it_groupTypeAges gt ON (gt.id = it.grouptypeage) WHERE it.show='Y' and it.startdate between '2011-05-08' and '2011-05-10' and it.nightcount between 14 and 18 and (r1.id in (1285)) and hp.pansionid in(135181, 134164, 5737, 166571, 9004341, 9003550, 9003637, 9002096, 9001916, 30205, 21346, 30204, 5738, 8590, 59695, 60116, 113787, 60107, 30403, 62182, 61632, 32801, 125113, 60040, 60096, 61634, 60090, 48519, 52219, 30523, 115693, 27423, 9003509, 9002803, 177496, 249861, 189732, 9003789, 9001262, 172148, 9003396, 9003460, 9001915, 9005759, 9003648, 172685, 190482, 9001378, 152983, 248750, 145022, 3016698, 203911, 3013774, 3013785, 9002759, 3040106, 173141, 3013611, 9006510, 9006509, 166572, 3023141, 9007750, 3017960, 259265, 3014645, 9007655, 186620, 3016327, 188788, 269549, 7068490, 279543, 259264, 279149) and ht.hoteltypeid in (2570) and gt.bigchildcount='0' and gt.smallchildcount='0' and gt.adultcount='2' and it.hotelstop='N' GROUP BY ht.id, it.hotelroomtype, it.startdate, it.nightcount, it.hotelpansion, it.sporegionset, it.residence LIMIT 0, 100;[/code]

Его Explain: http://files.kdk-labs.ru/tez_explain1.gif

Но все равно долго подбирает. Может что с настройками mysql покрутить?

Artemiy117 ()
Ответ на: комментарий от Artemiy117

Ничего ужасного не случится, если вы покажите полный вывод SHOW STATUS, SHOW VARIABLES, DESC и SHOW INDEX FROM испрользуемых таблиц.

А по вашему EXPLAIN видно, что в процессе выполнения запроса MySQL выполняет перебор примерно 53039*67*2 = 7107226 строк.

m0use ()
Ответ на: комментарий от Artemiy117

Что-то значения расходятся с теми, которые в конфиге. Например, join_buffer_size    131072.

Кроме того,

max_heap_table_size    16777216

и

tmp_table_size    16777216

это мало в вашем случае. Но для полной картины хорошо бы еще SHOW GLOBAL STATUS.

Еще, из очевидного - key_buffer_size ставьте размера, примерно равного суммарному размеру всех MYI-файлов.

m0use ()
Ответ на: комментарий от m0use

Просто постоянно подкручиваю. Сколько все-таки нужно выставлять join_buffer_size? Сейчас взял конфиг от my-huge.cnf Подкрутил tmp_table_size до 256Mb. Но чувствую, нужно около 700.

Variable_name 	Value
Aborted_clients 	0
Aborted_connects 	1
Binlog_cache_disk_use 	0
Binlog_cache_use 	0
Bytes_received 	40085365
Bytes_sent 	87087429
Com_admin_commands 	3
Com_assign_to_keycache 	0
Com_alter_db 	0
Com_alter_db_upgrade 	0
Com_alter_event 	0
Com_alter_function 	0
Com_alter_procedure 	0
Com_alter_server 	0
Com_alter_table 	0
Com_alter_tablespace 	0
Com_analyze 	0
Com_backup_table 	0
Com_begin 	0
Com_binlog 	0
Com_call_procedure 	0
Com_change_db 	631
Com_change_master 	0
Com_check 	0
Com_checksum 	0
Com_commit 	0
Com_create_db 	0
Com_create_event 	0
Com_create_function 	0
Com_create_index 	0
Com_create_procedure 	0
Com_create_server 	0
Com_create_table 	0
Com_create_trigger 	0
Com_create_udf 	0
Com_create_user 	0
Com_create_view 	0
Com_dealloc_sql 	0
Com_delete 	1
Com_delete_multi 	0
Com_do 	0
Com_drop_db 	0
Com_drop_event 	0
Com_drop_function 	0
Com_drop_index 	0
Com_drop_procedure 	0
Com_drop_server 	0
Com_drop_table 	0
Com_drop_trigger 	0
Com_drop_user 	0
Com_drop_view 	0
Com_empty_query 	0
Com_execute_sql 	0
Com_flush 	0
Com_grant 	0
Com_ha_close 	0
Com_ha_open 	0
Com_ha_read 	0
Com_help 	0
Com_insert 	187986
Com_insert_select 	0
Com_install_plugin 	0
Com_kill 	0
Com_load 	0
Com_load_master_data 	0
Com_load_master_table 	0
Com_lock_tables 	0
Com_optimize 	0
Com_preload_keys 	0
Com_prepare_sql 	0
Com_purge 	0
Com_purge_before_date 	0
Com_release_savepoint 	0
Com_rename_table 	0
Com_rename_user 	0
Com_repair 	0
Com_replace 	0
Com_replace_select 	0
Com_reset 	0
Com_restore_table 	0
Com_revoke 	0
Com_revoke_all 	0
Com_rollback 	0
Com_rollback_to_savepoint 	0
Com_savepoint 	0
Com_select 	1841
Com_set_option 	870
Com_show_authors 	0
Com_show_binlog_events 	0
Com_show_binlogs 	82
Com_show_charsets 	0
Com_show_collations 	0
Com_show_column_types 	0
Com_show_contributors 	0
Com_show_create_db 	0
Com_show_create_event 	0
Com_show_create_func 	0
Com_show_create_proc 	0
Com_show_create_table 	0
Com_show_create_trigger 	0
Variable_name 	Value
Com_show_databases 	4
Com_show_engine_logs 	0
Com_show_engine_mutex 	0
Com_show_engine_status 	0
Com_show_events 	0
Com_show_errors 	0
Com_show_fields 	1
Com_show_function_status 	0
Com_show_grants 	0
Com_show_keys 	0
Com_show_master_status 	2
Com_show_new_master 	0
Com_show_open_tables 	0
Com_show_plugins 	0
Com_show_privileges 	0
Com_show_procedure_status 	0
Com_show_processlist 	74
Com_show_profile 	0
Com_show_profiles 	0
Com_show_slave_hosts 	0
Com_show_slave_status 	2
Com_show_status 	3
Com_show_storage_engines 	0
Com_show_table_status 	4
Com_show_tables 	18
Com_show_triggers 	0
Com_show_variables 	7
Com_show_warnings 	0
Com_slave_start 	0
Com_slave_stop 	0
Com_stmt_close 	0
Com_stmt_execute 	0
Com_stmt_fetch 	0
Com_stmt_prepare 	0
Com_stmt_reprepare 	0
Com_stmt_reset 	0
Com_stmt_send_long_data 	0
Com_truncate 	4
Com_uninstall_plugin 	0
Com_unlock_tables 	0
Com_update 	6
Com_update_multi 	0
Com_xa_commit 	0
Com_xa_end 	0
Com_xa_prepare 	0
Com_xa_recover 	0
Com_xa_rollback 	0
Com_xa_start 	0
Compression 	OFF
Connections 	894
Created_tmp_disk_tables 	235
Created_tmp_files 	11
Created_tmp_tables 	835
Delayed_errors 	0
Delayed_insert_threads 	0
Delayed_writes 	0
Flush_commands 	1
Handler_commit 	0
Handler_delete 	1
Handler_discover 	0
Handler_prepare 	0
Handler_read_first 	64
Handler_read_key 	4776913
Handler_read_next 	1551070132
Handler_read_prev 	0
Handler_read_rnd 	5590
Handler_read_rnd_next 	113363631
Handler_rollback 	0
Handler_savepoint 	0
Handler_savepoint_rollback 	0
Handler_update 	103002
Handler_write 	113113773
Innodb_buffer_pool_pages_data 	19
Innodb_buffer_pool_pages_dirty 	0
Innodb_buffer_pool_pages_flushed 	0
Innodb_buffer_pool_pages_free 	493
Innodb_buffer_pool_pages_misc 	0
Innodb_buffer_pool_pages_total 	512
Innodb_buffer_pool_read_ahead_rnd 	1
Innodb_buffer_pool_read_ahead_seq 	0
Innodb_buffer_pool_read_requests 	77
Innodb_buffer_pool_reads 	12
Innodb_buffer_pool_wait_free 	0
Innodb_buffer_pool_write_requests 	0
Innodb_data_fsyncs 	3
Innodb_data_pending_fsyncs 	0
Innodb_data_pending_reads 	0
Innodb_data_pending_writes 	0
Innodb_data_read 	2494464
Innodb_data_reads 	25
Innodb_data_writes 	3
Innodb_data_written 	1536
Innodb_dblwr_pages_written 	0
Innodb_dblwr_writes 	0
Innodb_log_waits 	0
Innodb_log_write_requests 	0
Innodb_log_writes 	1
Innodb_os_log_fsyncs 	3
Innodb_os_log_pending_fsyncs 	0
Innodb_os_log_pending_writes 	0
Variable_name 	Value
Innodb_os_log_written 	512
Innodb_page_size 	16384
Innodb_pages_created 	0
Innodb_pages_read 	19
Innodb_pages_written 	0
Innodb_row_lock_current_waits 	0
Innodb_row_lock_time 	0
Innodb_row_lock_time_avg 	0
Innodb_row_lock_time_max 	0
Innodb_row_lock_waits 	0
Innodb_rows_deleted 	0
Innodb_rows_inserted 	0
Innodb_rows_read 	0
Innodb_rows_updated 	0
Key_blocks_not_flushed 	0
Key_blocks_unused 	0
Key_blocks_used 	319666
Key_read_requests 	95301431
Key_reads 	2376351
Key_write_requests 	585520
Key_writes 	585520
Last_query_cost 	0.000000
Max_used_connections 	11
Not_flushed_delayed_rows 	0
Open_files 	932
Open_streams 	0
Open_table_definitions 	244
Open_tables 	294
Opened_files 	3484
Opened_table_definitions 	252
Opened_tables 	1043
Prepared_stmt_count 	0
Qcache_free_blocks 	4
Qcache_free_memory 	1118264
Qcache_hits 	8632
Qcache_inserts 	1121
Qcache_lowmem_prunes 	299
Qcache_not_cached 	726
Qcache_queries_in_cache 	493
Qcache_total_blocks 	1146
Queries 	201057
Questions 	201057
Rpl_status 	NULL
Select_full_join 	0
Select_full_range_join 	0
Select_range 	254
Select_range_check 	0
Select_scan 	313
Slave_open_temp_tables 	0
Slave_retried_transactions 	0
Slave_running 	OFF
Slow_launch_threads 	0
Slow_queries 	20
Sort_merge_passes 	3
Sort_range 	130
Sort_rows 	43893
Sort_scan 	668
Ssl_accept_renegotiates 	0
Ssl_accepts 	0
Ssl_callback_cache_hits 	0
Ssl_cipher 	 
Ssl_cipher_list 	 
Ssl_client_connects 	0
Ssl_connect_renegotiates 	0
Ssl_ctx_verify_depth 	0
Ssl_ctx_verify_mode 	0
Ssl_default_timeout 	0
Ssl_finished_accepts 	0
Ssl_finished_connects 	0
Ssl_session_cache_hits 	0
Ssl_session_cache_misses 	0
Ssl_session_cache_mode 	NONE
Ssl_session_cache_overflows 	0
Ssl_session_cache_size 	0
Ssl_session_cache_timeouts 	0
Ssl_sessions_reused 	0
Ssl_used_session_cache_entries 	0
Ssl_verify_depth 	0
Ssl_verify_mode 	0
Ssl_version 	 
Table_locks_immediate 	192436
Table_locks_waited 	9
Tc_log_max_pages_used 	0
Tc_log_page_size 	0
Tc_log_page_waits 	0
Threads_cached 	0
Threads_connected 	11
Threads_created 	12
Threads_running 	11
Uptime 	1293
Uptime_since_flush_status 	1293
Artemiy117 ()
Ответ на: комментарий от Artemiy117

Created_tmp_disk_tables 235

Created_tmp_tables 835

Почти треть временных таблиц создается на диске. Следовательно, tmpdir на tmpfs имеет смысл.

Не понятно, из-за чего так происходит. У вас в выборке присутствуют BLOB/TEXT? Или большие столбцы в GROUP BY?

Кроме того, совет «перенести JOIN/GROUP BY на сторону приложения» еще имеет смысл.

m0use ()

1. Почитайте вот это: http://dev.mysql.com/doc/refman/5.0/en/range-optimization.html#range-access-m...
2. Сделайте нормальные multi-part индексы (чтобы в индексе участвовали все const и range части которые встречаются в запросе).
3. Не надо совать startdate как часть PRIMARY индекса
4. Т.к. в данный момент у вас неоптимальная структура индексов, то в выборку попадают записи из абсолютно разных партишенов, соотв. никакого выигрыша и не будет (у вас разбивка по месяцам, а в эксплейне видно что используется hotelroomtype).

anonymous ()
Ответ на: комментарий от m0use

«перенести JOIN/GROUP BY на сторону приложения» - про это где можно почитать? Что-то я не слышал о таком. В выборке BLOB и TEXT полей нет. Но есть большие столбцы типа int(9) или mediumint(7). От них никуда не деться.

Artemiy117 ()
Ответ на: комментарий от anonymous

Спасибо за совет. Действительно думаю разбить партиции по hotelroomtype. Тогда при выборе одного региона запрос будет в одной партиции искать.

Artemiy117 ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.