MySQL жутко тормозит

NightFlash · 21 Апр 2008

MySQL жутко тормозит

Суть такая:
есть проект, сайт турфирмы. Есть таблица с турами.
структура такая:
country_id, resort_id, hotel_id .... и т.д. там еще очень очень много чего.

соотв в поиске приходится делать GROUP BY по тому или иному столбцу. Но если записей много, то серверу наступает абзац. Запрос выполняется 10-15 секунд, что неприемлемо.

Индексы созданы по каждому полю и еще один уникальный определяющий уникальность тура. В структуре таблицы присутствуют как INT, так и VARCHAR с ограниченным кол-вом символов.

ЗАпросы максимально упрощены. JOIN'ов нет вообще.

Вот переменные:
MySQL не забирает себе больше 500Мб памяти. (смотрено по top'у)

Во время этих плохих запросов WCPU растет до 80%.

У кого какие предположения?

PHP:

Variable_name	Value
auto_increment_increment	1
auto_increment_offset	1
autocommit	ON
automatic_sp_privileges	ON
back_log	50
basedir	/usr/local/
big_tables	OFF
binlog_cache_size	32768
binlog_format	
bulk_insert_buffer_size	8388608
character_set_client	cp1251
character_set_connection	cp1251
character_set_database	cp1251
character_set_filesystem	binary
character_set_results	cp1251
character_set_server	cp1251
character_set_system	utf8
character_sets_dir	/usr/local/share/mysql/charsets/
collation_connection	cp1251_general_ci
collation_database	cp1251_general_ci
collation_server	cp1251_general_ci
completion_type	0
concurrent_insert	1
connect_timeout	5
datadir	/var/db/mysql/
date_format	%Y-%m-%d
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
delay_key_write	ON
delayed_insert_limit	100
delayed_insert_timeout	300
delayed_queue_size	1000
div_precision_increment	4
engine_condition_pushdown	ON
error_count	0
event_scheduler	OFF
expire_logs_days	0
flush	OFF
flush_time	0
foreign_key_checks	ON
ft_boolean_syntax	+ -><()~*:""&|
ft_max_word_len	84
ft_min_word_len	4
ft_query_expansion_limit	20
ft_stopword_file	(built-in)
general_log	OFF
general_log_file	/var/db/mysql/
group_concat_max_len	1024
have_compress	YES
have_crypt	YES
have_csv	YES
have_dynamic_loading	YES
have_geometry	YES
have_innodb	YES
have_ndbcluster	NO
have_openssl	NO
have_partitioning	YES
have_query_cache	YES
have_rtree_keys	YES
have_ssl	NO
have_symlink	YES
hostname	
identity	0
init_connect	SET NAMES cp1251
init_file	
init_slave	
innodb_additional_mem_pool_size	1048576
innodb_autoextend_increment	8
innodb_buffer_pool_size	8388608
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	
innodb_doublewrite	ON
innodb_fast_shutdown	1
innodb_file_io_threads	4
innodb_file_per_table	OFF
innodb_flush_log_at_trx_commit	1
innodb_flush_method	
innodb_force_recovery	0
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_arch_dir	
innodb_log_buffer_size	1048576
innodb_log_file_size	5242880
innodb_log_files_in_group	2
innodb_log_group_home_dir	./
innodb_max_dirty_pages_pct	90
innodb_max_purge_lag	0
innodb_mirrored_log_groups	1
innodb_open_files	300
innodb_rollback_on_timeout	OFF
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	8
innodb_thread_sleep_delay	10000
insert_id	0
interactive_timeout	28800
join_buffer_size	268431360
key_buffer_size	268435456
key_cache_age_threshold	300
key_cache_block_size	1024
key_cache_division_limit	100
language	/usr/local/share/mysql/english/
large_files_support	ON
large_page_size	0
large_pages	OFF
last_insert_id	0
lc_time_names	en_US
license	GPL
local_infile	ON
locked_in_memory	OFF
log	OFF
log_bin	OFF
log_bin_trust_function_creators	OFF
log_bin_trust_routine_creators	OFF
log_error	/var/db/mysql/
log_output	FILE
log_queries_not_using_indexes	OFF
log_slave_updates	OFF
log_slow_queries	ON
log_warnings	1
long_query_time	10
low_priority_updates	OFF
lower_case_file_system	OFF
lower_case_table_names	0
max_allowed_packet	8387584
max_binlog_cache_size	4294967295
max_binlog_size	1073741824
max_connect_errors	10
max_connections	151
max_delayed_threads	20
max_error_count	64
max_heap_table_size	16777216
max_insert_delayed_threads	20
max_join_size	4294967295
max_length_for_sort_data	1024
max_prepared_stmt_count	16382
max_relay_log_size	0
max_seeks_for_key	4294967295
max_sort_length	1024
max_sp_recursion_depth	0
max_tmp_tables	32
max_user_connections	0
max_write_lock_count	4294967295
multi_range_count	256
myisam_data_pointer_size	6
myisam_max_sort_file_size	2147483647
myisam_recover_options	OFF
myisam_repair_threads	1
myisam_sort_buffer_size	134217728
myisam_stats_method	nulls_unequal
myisam_use_mmap	OFF
net_buffer_length	16384
net_read_timeout	30
net_retry_count	1000000
net_write_timeout	60
new	OFF
old	OFF
old_alter_table	OFF
old_passwords	OFF
open_files_limit	11095
optimizer_prune_level	1
optimizer_search_depth	62
pid_file	/var/db/mysql/
plugin_dir	/usr/local/lib/mysql
port	3306
preload_buffer_size	32768
protocol_version	10
pseudo_thread_id	50876
query_alloc_block_size	8192
query_cache_limit	8388608
query_cache_min_res_unit	4096
query_cache_size	134217728
query_cache_type	ON
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192
rand_seed1	
rand_seed2	
range_alloc_block_size	2048
read_buffer_size	68153344
read_only	OFF
read_rnd_buffer_size	520192
relay_log_purge	ON
relay_log_space_limit	0
rpl_recovery_rank	0
secure_auth	OFF
secure_file_priv	
server_id	1
skip_external_locking	ON
skip_networking	OFF
skip_show_database	OFF
slave_compressed_protocol	OFF
slave_load_tmpdir	/var/tmp/
slave_net_timeout	3600
slave_skip_errors	OFF
slave_transaction_retries	10
slow_launch_time	2
slow_query_log	ON
slow_query_log_file	/var/log/mysql-slow.log
socket	/tmp/mysql.sock
sort_buffer_size	4194296
sql_auto_is_null	ON
sql_big_selects	ON
sql_big_tables	OFF
sql_buffer_result	OFF
sql_log_bin	ON
sql_log_off	OFF
sql_log_update	ON
sql_low_priority_updates	OFF
sql_max_join_size	4294967295
sql_mode	
sql_notes	ON
sql_quote_show_create	ON
sql_safe_updates	OFF
sql_select_limit	4294967295
sql_slave_skip_counter	
sql_warnings	OFF
ssl_ca	
ssl_capath	
ssl_cert	
ssl_cipher	
ssl_key	
storage_engine	MyISAM
sync_binlog	0
sync_frm	ON
system_time_zone	MSD
table_definition_cache	128
table_definition_cache	128
table_lock_wait_timeout	50
table_lock_wait_timeout	50
table_open_cache	2048
table_type	MyISAM
thread_cache_size	512
thread_handling	one-thread-per-connection
thread_stack	196608
time_format	%H:%i:%s
time_zone	SYSTEM
timed_mutexes	OFF
timestamp	1207855144
tmp_table_size	268435456
tmpdir	/var/tmp/
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
tx_isolation	REPEATABLE-READ
unique_checks	ON
updatable_views_with_limit	YES
version	5.1.19-beta-log
version_comment	FreeBSD port: mysql-server-5.1.19
version_compile_machine	i386
version_compile_os	portbld-freebsd6.2
wait_timeout	28800
warning_count	0

Beavis · 21 Апр 2008

а где explain запроса?

NightFlash · 21 Апр 2008

Там нечего Explain'ить
SImple запрос, using where, ключи по всем Where

fixxxer · 21 Апр 2008

и все же show create table + сам запрос + explain

Beavis · 21 Апр 2008

Автор оригинала: NightFlash
Там нечего Explain'ить

тогда нам нечего ответить... разобрались?

NightFlash · 21 Апр 2008

SHOW CREATE TABLE

PHP:

mysql>  travels | CREATE TABLE `travels` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `country_id` int(11) NOT NULL,
    ->   `resort_id` int(11) NOT NULL,
    ->   `hotel_id` int(11) NOT NULL,
    ->   `hotel_stars` varchar(80) DEFAULT NULL,
    ->   `tgroup_name` varchar(80) DEFAULT NULL,
    ->   `tgroup_info` varchar(80) DEFAULT NULL,
    ->   `date` date DEFAULT NULL,
    ->   `fdate` date DEFAULT NULL,
    ->   `ftype` varchar(80) DEFAULT NULL,
    ->   `rtype` varchar(80) DEFAULT NULL,
    ->   `city` varchar(80) DEFAULT NULL,
    ->   `ttype` varchar(80) DEFAULT NULL,
    ->   `days` int(11) DEFAULT NULL,
    ->   `nights` int(11) DEFAULT NULL,
    ->   `price` double DEFAULT NULL,
    ->   `price_type` int(1) DEFAULT '1',
    ->   `com` varchar(80) DEFAULT NULL,
    ->   `exc_id` int(11) DEFAULT NULL,
    ->   `pdate` date DEFAULT NULL,
    ->   `source` varchar(200) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `country_id` (`country_id`),
    ->   KEY `resort_id` (`resort_id`),
    ->   KEY `hotel_id` (`hotel_id`),
    ->   KEY `hotel_stars` (`hotel_stars`),
    ->   KEY `price` (`price`),
    ->   KEY `ftype` (`ftype`),
    ->   KEY `rtype` (`rtype`),
    ->   KEY `city` (`city`),
    ->   KEY `ttype` (`ttype`),
    ->   KEY `date` (`date`),
    ->   KEY `days` (`days`),
    ->   KEY `nights` (`nights`),
    ->   KEY `exc_id` (`exc_id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=2445380 DEFAULT CHARSET=cp1251

EXPLAIN

PHP:

mysql> EXPLAIN SELECT exc_id FROM travels WHERE tgroup_name='1/2 DBL' AND country_id=50 AND exc_id>0 AND ttype=1 GROUP BY exc_id;
+----+-------------+---------+------+-------------------------+------------+---------+-------+--------+----------------------------------------------+
| id | select_type | table   | type | possible_keys           | key        | key_len | ref   | rows   | Extra                                        |
+----+-------------+---------+------+-------------------------+------------+---------+-------+--------+----------------------------------------------+
|  1 | SIMPLE      | travels | ref  | country_id,ttype,exc_id | country_id | 4       | const | 583439 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+-------------------------+------------+---------+-------+--------+----------------------------------------------+
1 row in set (0.00 sec)

САМ ЗАПРОС

PHP:

mysql> SELECT exc_id FROM travels WHERE tgroup_name='1/2 DBL' AND country_id=50 AND exc_id>0 AND ttype=1 GROUP BY exc_id;
+--------+
| exc_id |
+--------+
|   1811 |
|   1845 |
|   1847 |
|   1848 |
|   1849 |
|   1851 |
|   2114 |
+--------+
7 rows in set (5.61 sec)

И таких запросов по 6 секунд на странице 3-4, соотв. тормоза дикие.
Я так понимаюю основная проблема в Using temporary т.е. просто не помещается в оперативную память? т.е. надо увеличивать tmp_table_size ?

Breeze · 22 Апр 2008

Using temporary означает то, что mysql создает временную таблицу для оперирования с данными.

что есть ttype и почему оно в запросе как int, а в структуре как varchar?
ты что-нибудь слышал про составные индексы ?

Beavis · 22 Апр 2008

а если на tgroup_name повесить индекс?

NightFlash · 22 Апр 2008

сейчас создал индекс по exc_id, tgroup_name, country_id и ttype
получилось 3,16 секунды
уже лучше.

по сути я запихал в индекс все, что есть в Where + по чему группируем, это правильная тактика? или можно что-нить выкинуть из индекса?

Beavis · 22 Апр 2008

лучше не бездумно пихать всё вподряд в индексы, а почитать информацию о том, что такое индексы, что такое составные индексы, как их правильно составлять и т.д.

Breeze · 22 Апр 2008

Beavis

а чем ему этот индекс поможет?
те же поллимона будут только сбоку, если только 5.1 не научился по группе одиночных индексов ползать.

Beavis · 22 Апр 2008

ну видишь, в 3 раза быстрее стало

Breeze · 22 Апр 2008

NightFlash

кроме тебя никто не знает, каково распределение данных у тебя в таблице. какие поля содержат наименьшее число повторений одних и тех же значений.

так что читай внимательнее и пробуй различную комбинацию.

ЗЫ: и про ttype ты не ответил, нафига оно varchar, если значение int

-~{}~ 22.04.08 01:45:

Beavis

стало, да..
*а уж если это поле еще int сделать*

NightFlash

и что сейчас говорит explain?

NightFlash · 22 Апр 2008

ну я насильно сказал ему USE INDEX и теперь запрос
Use where, Use index

-~{}~ 22.04.08 01:58:

Breeze
ты гений!!! изменил ttype на INT и стало Using index for groop by !!!

забыл сказать: 0,07 СЕКУНДЫ!!! ^_^

Breeze · 22 Апр 2008

в общем над этой таблицей и запросом работать и работать еще.
ну хоть какой-то прогресс.

NightFlash · 22 Апр 2008

только я не понял почему включилось это:
Using index for groop by

видимо оно включается если все что в where и group by есть в индексе. Не больше и не меньше

MySQL жутко тормозит

NightFlash

Новичок

Beavis

Banned

NightFlash

Новичок

fixxxer

К.О.

Beavis

Banned

NightFlash

Новичок

Breeze

goshogun

Beavis

Banned

NightFlash

Новичок

Beavis

Banned

Breeze

goshogun

Beavis

Banned

Breeze

goshogun

NightFlash

Новичок

Breeze

goshogun

NightFlash

Новичок