[MySQL] Медленный join по миллиарду записей

12 3
T
На сайте с 28.06.2007
Offline
82
4875

Помогите пожалуйста понять почему JOIN такой долгий.

1 таблица (основная): FULL - в ней 2 поля ID (primary key) и NAME, тип таблицы MyIsam, записей в таблице 2 миллиарда.

2 таблица (временная) TMP - поле ID (primry key), тип таблицы Memory. В эту таблицу я записываю ID и потом по ним джойню таблицу FULL, чтобы вытащить поле Name.

Запрос такой:

SELECT SQL_NO_CACHE name FROM tmp JOIN FULL WHERE tmp.id=FULL.id INTO OUTFILE 'C:/full.txt';

Время выполнения зависит от количества ID, вот например 263984 ID выполняется за 12минут.

Explain:

Конфиг my.cnf:

max_connections=128
max_allowed_packet=32M
query_cache_size=512M
table_cache=256
tmp_table_size=512M
thread_cache_size=128
thread_concurrency=64
myisam_max_sort_file_size=4G
myisam_sort_buffer_size=1000M
key_buffer_size=4000M
read_rnd_buffer_size=320M
sort_buffer_size=128M
ft_min_word_len =1

skip-innodb
skip-bdb

# См. коментарий выше
#enable-named-pipe

# Server ID.
server-id = 1

innodb_additional_mem_pool_size=36M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=1024M
innodb_log_file_size=18M
innodb_thread_concurrency=10
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
ft_min_word_len =1
[isamchk]
key_buffer = 4000M
sort_buffer_size = 128M
ft_min_word_len =1
[myisamchk]
key_buffer = 4000M
sort_buffer_size = 128M
ft_min_word_len =1
[mysqlhotcopy]
interactive-timeout

Ресурсы ПК:

Проц: Intel Core i7

ОЗУ: 12Gb

Винчестер: Seagate ST1000DM003

Уже как только не эксперементировал, и типы данных менял, MyIsam на InnoDB - одно и тоже. Пробовал на СУБД Postgresql - было ещё медленнее. Помогите пожалуйста решить проблему.

S
На сайте с 23.05.2004
Offline
290
#1

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

Насколько такой вариант изменит результат ?

Это просто мой личный сайт (https://phpdevs.com/) в моей подписи.
TF-Studio
На сайте с 17.08.2010
Offline
321
#2

2 миллиарда = какой размер, какие типы данных (text, varchar,...)

Попробуйте задачу объяснить, возможно есть и другое решение.

Что происходит с диском/памятью в момент выполнения? (munin или что-то такое должно быть)

Всё ещё лучший способ заработка для белых сайтов: GoGetLinks (https://www.gogetlinks.net/?inv=fahbn8).
edogs software
На сайте с 15.12.2005
Offline
719
#3

1) Поля ID в tmp и full должны быть строго одинаково объявлены - допустим bigint(20) - это даст работать индексам

2) Запрос должен быть таким

select sql_no_cache name from tmp left join full on tmp.id=full.id into outfile 'c:/full.txt';

в Вашем варианте Вы делаете декартово произведение таблиц - а с 2 миллиардами это офигенно много (как у Вас вообще сервер не лег), потом фильтруете результат... в нашем Вы присоединяете из full только нужные Вам данные, поэтому все должно быть быстро.

Разработка крупных и средних проектов. Можно с криптой. Разумные цены. Хорошее качество. Адекватный подход.
T
На сайте с 28.06.2007
Offline
82
#4
TF-Studio:
2 миллиарда = какой размер, какие типы данных (text, varchar,...)

Размер - 80Гб

Id - int (10)

Name - varchar (70)

TF-Studio:

Попробуйте задачу объяснить, возможно есть и другое решение.
Что происходит с диском/памятью в момент выполнения? (munin или что-то такое должно быть)

Задача - полнотекстовый поиск по полю (с морфологией). Использую Sphinx, затем полученные id джойню, почему Join настолько долгий не понимаю ((

TF-Studio:

Что происходит с диском/памятью в момент выполнения? (munin или что-то такое должно быть)

процесс MySQL считывает с диска, но скорость чтения очень слабая, всего 1-1.5Мб в сек. Почему так слабо не понимаю, больше никаких процессов нет.

edogs, спасибо, формат был один и тотже, изменил запрос, стало в 2 раза быстрее, но всёравно долго :( Около 6 минут.

Или выше скорости не добиться? Если на SSD базу поставить, будет значительно быстрее?

edogs software
На сайте с 15.12.2005
Offline
719
#5
Trol:

Задача - полнотекстовый поиск по полю (с морфологией). Использую Sphinx, затем полученные id джойню, почему Join настолько долгий не понимаю ((
процесс MySQL считывает с диска, но скорость чтения очень слабая, всего 1-1.5Мб в сек. Почему так слабо не понимаю,

Потому что если данные разбросаны у Вас по всей таблице, то что бы получить 263 тысячи - ему нужно сделать кучу позиционирований по диску. Индексы подсказывают ГДЕ брать информацию, но что бы ее взять - мускул все должен спозиционироваться в нужную точку на диске.

Trol:

2 миллиарда = какой размер, какие типы данных (text, varchar,...)
Id - int (10)
varchar (70)

1) В int влезает 2 миллиарда и 150 миллионов записей примерно. Скоро упретесь в размер поля, учтите это. unsigned int на какое-то время поможет если упретесь.

2) char (70) может сильно ускорить выборку, но может и сильно замедлить. Зависит от Ваших данных, попробуйте оба варианта.

3) Если у Вас там чисто русский-английский но при этом Utf-8, то подумайте о переходе на cp1251 - размер базы на диске станет меньше - работать будет быстрее.

Trol:
edogs, спасибо, формат был один и тотже, изменил запрос, стало в 2 раза быстрее, но всёравно долго :(

Именно для Вашей задачи возможно лучше будет даже

select sql_no_cache name from full where full.id in ( select tmp.id from tmp ) into outfile 'c:/full.txt';


---------- Добавлено 04.03.2013 в 04:32 ----------

Trol:
Если на SSD базу поставить, будет значительно быстрее?
Да. У Вас ситуация, когда делается грубо говоря 260 тысяч случайных позиционирований при считывании таблицы, ssd тут очень сильные стороны имеет. Но все же сначала попробуйте альтернативные варианты, 6 минут долговато для такой ситуации, если диск не загружен другими процессами.
T
На сайте с 28.06.2007
Offline
82
#6

edogs, спасибо, у меня CP1251, база FULL весит 80Гб.

edogs:
Именно для Вашей задачи возможно лучше будет даже
select sql_no_cache name from full where full.id in ( select tmp.id from tmp ) into outfile 'c:/full.txt';

Этот запрос дольше, 12ая минута уже идёт, так и не выполнился.

А может через my.cnf можно повлиять? Как-то задействовать 12Гб памяти? Не понимаю в тюнинге MySQL, тестировал разные значения, но прироста в скорости не почувствовал (

TF-Studio
На сайте с 17.08.2010
Offline
321
#7

Мне SSD очень сильно помог.

Но вас не спасет, вам надо решить сначала проблему.

Без Джойна - совсем никак не переделать?

T
На сайте с 28.06.2007
Offline
82
#8
TF-Studio:
Без Джойна - совсем никак не переделать?

Если только через IN, но он работает значительно медленнее.

Сам алгоритм такой: получаю от сфинкс список id и записываю их во временную таблицу, затем её джойню с полной таблицей. Чем больше ID во временной таблице, тем дольше выполняется выборка в основную таблицу FULL.

260000 ID за 6 минут пока лучший результат, наверное если перенести БД на SSD, получится около 3ёх минут.

TF-Studio
На сайте с 17.08.2010
Offline
321
#9

А зачем за раз получать 260000 записей?

может как-то сам процесс разбить?

T
На сайте с 28.06.2007
Offline
82
#10

Пробовал разбивать, но быстрее не стало.

Может кто подскажет как такой запрос ещё оптимизировать можно:

select sql_no_cache name from tmp left join full on tmp.id=full.id into outfile 'c:/full.txt';

Или в конфигурации MySQL что-то поправить для ускорения?

12 3

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий