Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Работа Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Добрый день,

Мы столкнулись с проблемой медленной работы MySQL на group by запросах, подробности тут https://stackoverflow.com/questions/60705283/slow-innodb-aggregation-queries-comparing-to-postgresql

Вкратце - 4 миллиона записей в 1 read-only таблице с индексами, дефолтный postgres справляется за 1-2 секунды, немного подтюненный MySQL за 12-14 секунд.

Мы бы хотели привлечь профессионала, который знает где подкрутить и как настроить ОСь/БД, чтобы заставить работать MySQL не медленнее, чем это делает Psql

Оцениваем эту помощь в 5000 рублей, готовы перевести сразу после демонстрации требуемой скорости на нашем удаленном сервере.

Какая процедура
- пишите на ezhulkov@gmail.com и присылаете публичный ключ
- я добавляю рутовому пользователю ваш ключ и говорю как попасть на сервер
- вы делаете магию, аккуратно записывая каждый шаг
- когда все готово - проверяем и если все нормально мы переводим половину денег, а вы сообщаете магию.
- мы дропаем сервер, с нуля его устанавливаем, применяем магию и заливаем данные
- если после этого скорость сохранилась - переводим остаток.
17 мар 20, 14:43    [22100678]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex Le
Member

Откуда:
Сообщений: 31
Просто проходил мимо и стало интересно. Нижеприведённый запрос выполняется быстрее?

select country, count(*) from dim_users group by country order by country asc;
17 мар 20, 16:14    [22100744]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 742
Требуется волшебник, алхимик, знаток белой и чёрной магии SQL - забавно :)
17 мар 20, 17:03    [22100785]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Alex Le,

да, скорость такая же, это аналогичные запросы

11 rows in set (10.43 sec)
17 мар 20, 17:08    [22100789]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Покажите план с postgres.
Что дает такой запрос

select country, count(1) from dim_users group by country order by country asc;
17 мар 20, 18:23    [22100854]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya,

marketplace=# explain analyze select country, count(1) from dim_users group by country order by country asc;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=81028.16..81030.69 rows=10 width=11) (actual time=2429.280..2429.355 rows=11 loops=1)
   Group Key: country
   ->  Gather Merge  (cost=81028.16..81030.49 rows=20 width=11) (actual time=2429.255..2437.897 rows=33 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=80028.13..80028.16 rows=10 width=11) (actual time=2423.216..2423.219 rows=11 loops=3)
               Sort Key: country
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Partial HashAggregate  (cost=80027.87..80027.97 rows=10 width=11) (actual time=2423.092..2423.100 rows=11 loops=3)
                     Group Key: country
                     ->  Parallel Seq Scan on dim_users  (cost=0.00..71219.91 rows=1761591 width=3) (actual time=0.076..682.000 rows=1409564 loops=3)
 Planning Time: 0.246 ms
 Execution Time: 2438.070 ms
(15 rows)

Time: 2439.751 ms (00:02.440)
17 мар 20, 18:31    [22100864]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16814
set local innodb_parallel_read_threads=4;
18 мар 20, 08:53    [22101054]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
ScareCrow,

mysql> set local innodb_parallel_read_threads=4;
Query OK, 0 rows affected (0.00 sec)

mysql> select country, count(*) from dim_users group by 1 order by 1 asc;
11 rows in set (11.65 sec)

mysql>


Нет, не ускорило
18 мар 20, 09:39    [22101087]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3055
ради эксперимента в конфиге
innodb_thread_concurrency=0

Сообщение было отредактировано: 18 мар 20, 10:31
18 мар 20, 10:31    [22101141]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
H5N1
Member

Откуда: Yo.! из "Сравнения субд"
Сообщений: 315
по мне выглядит, что главная разница PG читает двумя тредами по 1.7М строк в параллель и таблицу, а mysql одним тредом и индекс. я бы предложил грохнуть индексы на mysql и посмотреть на скорость чтения именно из таблиц.
по мне что-то не так с mysql, читать 10 секунд индекс на 4М крошечных строк, это перебор. у вас точно ванильный mysql в вашей виртуалке? не дребедень из облака ? я сталкивался с тормозами google mysql, после того как из таблицы что-то большое удалить.

ЗЫ.

+---------------+
| EXPLAIN
+---------------+
| -> Group aggregate: count(0) (actual time=241.091..14777.679 rows=11 loops=1)
| -> Index scan on dim_users using country (reverse) (cost=430171.90 rows=4230924) (actual time=0.054..11940.166 rows=4228692 loops=1)
|
+---------------+

еще вариант что я не умею читать планы mysql и это он не скан индекса делает, а в цикле долбит. тогда понятно откуда гигантское время

Сообщение было отредактировано: 18 мар 20, 11:29
18 мар 20, 11:24    [22101184]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Alex_Ustinov,

mysql> set local innodb_parallel_read_threads=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 1     |
+------------------------------+-------+
1 row in set (0.01 sec)

mysql> select country, count(*) from dim_users group by 1 order by 1 asc;
11 rows in set (9.81 sec)


Видимо в 0 не установить - минимальное значение 1, но на результат не влияет.
И, кстати, дефолтное значение 4.
18 мар 20, 12:08    [22101241]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Alex_Ustinov,

Извиняюсь, поторопился и пофиксил другой параметр.
Однако, с innodb_thread_concurrency=0 результат такой же:

mysql> show global variables like 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> select country, count(*) from dim_users group by 1 order by 1 asc;
11 rows in set (9.81 sec)
18 мар 20, 12:11    [22101245]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
H5N1,

Я ставил MySQL по этой инструкции https://www.tecmint.com/install-mysql-8-in-ubuntu/
Вроде не какой-то мусор поставился

Про индекс - я грохнул его, теперь план такой:

mysql> explain analyze select country, count(1) from dim_users group by country order by country asc;
+-----------+
| EXPLAIN
+-----------+
| -> Sort: <temporary>.country  (actual time=35981.962..35981.965 rows=11 loops=1)
|    -> Table scan on <temporary>  (actual time=0.002..0.009 rows=11 loops=1)
|        -> Aggregate using temporary table  (actual time=35981.909..35981.919 rows=11 loops=1)
|            -> Table scan on dim_users  (cost=403496.10 rows=3964166) (actual time=0.862..19819.260 rows=4228692 loops=1)
|
+-----------+
1 row in set (36.02 sec)
18 мар 20, 12:16    [22101251]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Guzya
Покажите план с postgres.
Что дает такой запрос

select country, count(1) from dim_users group by country order by country asc;


Я имел ввиду план Вашего запроса в Postgres/
И план моего запроса в MySQL.
18 мар 20, 13:33    [22101367]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Только индекс для Country верните.
18 мар 20, 13:36    [22101371]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
H5N1
Member

Откуда: Yo.! из "Сравнения субд"
Сообщений: 315
Eugene Zhulkov,

да, спец по mysql нужон. по мне это не нормально прочесть за 0.8 миллисекунды крошечный набор и 35981 миллисекунды агрегировать. "using temporary table" это запись лишь 11 строк, т.е. ощущение что или с mysql что-то случилось или проц работает на частоте 1Mhz (тротлит?)
18 мар 20, 13:37    [22101373]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Покажите значение sort_buffer_size.
18 мар 20, 13:43    [22101378]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya,

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)


По планам запроса, если я правильно вас понял:

MySQL:
mysql> explain analyze select country, count(1) from dim_users group by country order by country asc;
+-------------+
| EXPLAIN                                                         
+-------------+
| -> Group aggregate: count(1)  (actual time=1051.587..11210.593 rows=11 loops=1)
|    -> Index scan on dim_users using country_idx  (cost=403496.10 rows=3964166) (actual time=0.046..8393.460 rows=4228692 loops=1)
+-------------+
1 row in set (11.21 sec)


Postgresql
marketplace=# explain analyze select country,count(*) from dim_users group by 1 order by 1 desc;
                                                                      QUERY PLAN
-----------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=81028.16..81030.69 rows=10 width=11) (actual time=2421.006..2421.083 rows=11 loops=1)
   Group Key: country
   ->  Gather Merge  (cost=81028.16..81030.49 rows=20 width=11) (actual time=2420.993..2425.148 rows=33 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=80028.13..80028.16 rows=10 width=11) (actual time=2415.909..2415.912 rows=11 loops=3)
               Sort Key: country DESC
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Partial HashAggregate  (cost=80027.87..80027.97 rows=10 width=11) (actual time=2415.829..2415.836 rows=11 loops=3)
                     Group Key: country
                     ->  Parallel Seq Scan on dim_users  (cost=0.00..71219.91 rows=1761591 width=3) (actual time=0.048..696.210 rows=1409564 loops=3)
 Planning Time: 0.455 ms
 Execution Time: 2425.300 ms
(15 rows)


Это один и тот же сервер, просто по очереди запустил одну базу, потом выключил и запустил другую. Данные в базах идентичные.

Сообщение было отредактировано: 18 мар 20, 14:02
18 мар 20, 14:01    [22101398]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
H5N1,

Да согласен про спеца, поэтому и повесил в раздел "Работа" и жду его. 
Про троттлинг можно было бы поверить, если бы не postgres, который из коробки делает все очень быстро на этой же виртуалке.
18 мар 20, 14:05    [22101402]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Попробуйте sort_buffer_size=2621440
18 мар 20, 15:07    [22101491]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya,

Nope (

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2621440 |
+------------------+---------+
1 row in set (0.01 sec)

mysql> select country, count(*) from dim_users group by 1 order by 1 asc;
11 rows in set (10.08 sec)
18 мар 20, 15:11    [22101496]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Тогда лучше вернуть назад.
Покажите все параметры.
18 мар 20, 15:20    [22101502]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya,

залил сюда https://gist.github.com/ezhulkov/6bab95e39ea1e6a72fef8747f8db6ade
18 мар 20, 15:35    [22101513]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 450
Попробуйте innodb_sort_buffer_size=2097152
18 мар 20, 15:57    [22101537]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya,


без результатов, с сортировкой не очень связано, потому что без неё тормозит так же:

mysql> show variables like 'innodb_sort_buffer_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 2097152 |
+-------------------------+---------+
1 row in set (0.01 sec)

mysql> select country, count(*) from dim_users group by 1 order by 1 asc;
11 rows in set (10.10 sec)

mysql> select country, count(*) from dim_users group by 1;
11 rows in set (9.98 sec)


Беритесь за задачу :) я сделаю доступ к серверу, сможете там сделать все что посчитаете нужным с базой.
18 мар 20, 16:03    [22101543]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Работа Ответить