Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Работа Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Нужна платная консультация по настройке 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

Откуда: другая столица
Сообщений: 746
Требуется волшебник, алхимик, знаток белой и чёрной магии 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

Откуда:
Сообщений: 453
Покажите план с 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

Откуда: Белый город
Сообщений: 16816
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
Сообщений: 3061
ради эксперимента в конфиге
innodb_thread_concurrency=0

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

Откуда: Yo.! из "Сравнения субд"
Сообщений: 316
по мне выглядит, что главная разница 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

Откуда:
Сообщений: 453
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

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

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

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

Откуда:
Сообщений: 453
Покажите значение 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

Откуда:
Сообщений: 453
Попробуйте 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

Откуда:
Сообщений: 453
Тогда лучше вернуть назад.
Покажите все параметры.
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

Откуда:
Сообщений: 453
Попробуйте 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]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
Я не специалист по MySQL, мне просто интересно.
Вот сижу, читаю что с чем может быть связанно и предлагаю возможные пути.
18 мар 20, 16:06    [22101545]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Guzya
Я не специалист по MySQL, мне просто интересно.
Вот сижу, читаю что с чем может быть связанно и предлагаю возможные пути.


Специалистами становятся как раз через решение подобных задач, если получится - прокачаете скил и заработаете немного денег.
В любом случае спасибо за советы и ваше время.
18 мар 20, 16:08    [22101548]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
optimize table на крайний случай
18 мар 20, 16:18    [22101556]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov,

дело в том что с InnoDB и Count() старая беда. В виду версионности движка без where он может тормозить, поэтому врать вам не хочется.
можете проверить Select Count() from table и вы удивитесь
18 мар 20, 16:21    [22101557]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

дело в том что с InnoDB и Count() старая беда. В виду версионности движка без where он может тормозить, поэтому врать вам не хочется.
можете проверить Select Count() from table и вы удивитесь


Мне не принципиально InnoDB - транзакций и записей не планируется в эту базу, это readonly данные для последующего анализа. Я пробовал создавать MyISAM и разные типы индексов - результаты были те же.

Еще я сделал optimize таблицы, ничего не изменилось:

mysql> optimize table dim_users;
+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| marketplace.dim_users | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| marketplace.dim_users | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (8 min 8.21 sec)

mysql> select count(1) from dim_users;
+----------+
| count(1) |
+----------+
|  4228692 |
+----------+
1 row in set (0.79 sec)

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

mysql> select country,count(1) from dim_users where country is not null group by 1;
10 rows in set (10.06 sec)


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

Откуда: Nickel
Сообщений: 3061
обратитесь с вопросом в профильный форум MySQL, не все смотрят на текущие топики в общем списке.
По крайней мере хотя бы объяснят что возможно а что нет. Я с таким не сталкивался конкретно. Многие вешают Триггер-Инсерт-Делете на эту таблицу и и пишут в другую таблицу статистику.
Может в 8-ке оконные функции отрабатывают быстрее, нет под рукой 8-ки
18 мар 20, 16:39    [22101575]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
Alex_Ustinov
обратитесь с вопросом в профильный форум MySQL, не все смотрят на текущие топики в общем списке.
По крайней мере хотя бы объяснят что возможно а что нет. Я с таким не сталкивался конкретно. Многие вешают Триггер-Инсерт-Делете на эту таблицу и и пишут в другую таблицу статистику.
Может в 8-ке оконные функции отрабатывают быстрее, нет под рукой 8-ки


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

Откуда: Nickel
Сообщений: 3061
Пробуйте если есть время
select country,count(1) from dim_users OVER (PARTITION BY country)
18 мар 20, 16:45    [22101581]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
H5N1
Member

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

дело в том что с InnoDB и Count() старая беда. В виду версионности движка без where он может тормозить, поэтому врать вам не хочется.
можете проверить Select Count() from table и вы удивитесь

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

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

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

не, это не фулскан на 4млн записей за 0,8сек
фулскан это 10-11 сек
18 мар 20, 18:07    [22101661]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
H5N1
Member

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

не, это не фулскан на 4млн записей за 0,8сек
фулскан это 10-11 сек


а что вот это тогда ? "Table scan on dim_users (cost=403496.10 rows=3964166) (actual time=0.862..19819.260 "

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, 18:45
18 мар 20, 18:45    [22101696]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
я вижу эти цифры, бывает просто не доверяю, как и в этом случае.
а это выше
actual time=35981.909
тоже в секундах?)
18 мар 20, 19:11    [22101732]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
H5N1, а разве не так читается
actual time=0.862..19819.260


первые данные начинают извлекаться ч\з 0.862 , а все данные извлекаются ч\з 19819.260.
Т.е. операция Table scan полностью завершается ч\з 19 секунд.
18 мар 20, 19:38    [22101742]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
здесь другая беда - using temporary table
а не по индексу

можно проверить по одной стране
select country, count(1) from dim_users where country = 'Название'

и план бы EXPLAIN SELECT без ANALYZE
18 мар 20, 19:44    [22101748]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

Запустил:

mysql> explain select country, count(1) from dim_users where country = 'Fr' group by 1;
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | dim_users | NULL       | ref  | country_idx   | country_idx | 11      | const | 2104120 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select country, count(1) from dim_users group by 1;
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | dim_users | NULL       | index | country_idx   | country_idx | 11      | NULL | 4208241 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select country, count(1) from dim_users where country = 'Fr' group by 1;
+---------+----------+
| country | count(1) |
+---------+----------+
| FR      |  1399596 |
+---------+----------+
1 row in set (3.48 sec)
18 мар 20, 20:30    [22101777]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
А так

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

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

mysql> select country, count(1) from dim_users where true group by 1;
+---------+----------+
| country | count(1) |
+---------+----------+
| NULL    |   454765 |
| AT      |    58528 |
| BE      |    99894 |
| CH      |    83343 |
| DE      |   668124 |
| ES      |   234933 |
| FR      |  1399596 |
| GB      |   690288 |
| IT      |   328532 |
| NL      |   147834 |
| PT      |    62855 |
+---------+----------+
11 rows in set (9.87 sec)

mysql> explain select country, count(1) from dim_users where true group by 1;
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | dim_users | NULL       | index | country_idx   | country_idx | 11      | NULL | 4208241 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
18 мар 20, 20:39    [22101783]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov,

индекс как бы используется, толку нет.
у FR низкая избирательность индекса
1 row in set (3.48 sec)
1,3млн из 4млн

вот это бы еще проверить
select country,count(1) from dim_users OVER (PARTITION BY country)
и чтобы кэшированные данные не всплывали можно делать
SELECT SQL_NO_CACHE ..............

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

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

вот это бы еще проверить
select country,count(1) from dim_users OVER (PARTITION BY country)


Не очень понимаю синтаксис этого запроса - as is выдает ошибку.

А если я исправляю на select count(1) OVER (PARTITION BY country) from dim_users; то выдает очень много строк и работает больше минуты

Про Fr - да, там много записей, взял другую страну:

mysql> explain select country, count(1) from dim_users where country = 'At' group by 1;
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | dim_users | NULL       | ref  | country_idx   | country_idx | 11      | const | 113508 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select country, count(1) from dim_users where country = 'At' group by 1;
+---------+----------+
| country | count(1) |
+---------+----------+
| AT      |    58528 |
+---------+----------+
1 row in set (0.15 sec)


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

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov,

да, вы правы, я перепутал синтаксис
select country, count(*) OVER(PARTITION BY country) FROM dim_users
но должно быть ваши 11 строк, к сожалению опыта нет с оконными функциями

и даже не знаю что предпринять.
Там где избирательность маленькая - идет фуллскан хотя и по индексу,
с "АТ" все прекрасно
1 row in set (0.15 sec)
18 мар 20, 21:34    [22101798]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
Покажите план

explain analyze select country from dim_users;
18 мар 20, 21:52    [22101803]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
и этот

explain analyze select country,count(1) from dim_users where country is not null group by country;
18 мар 20, 22:11    [22101817]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

да, вы правы, я перепутал синтаксис
select country, count(*) OVER(PARTITION BY country) FROM dim_users
но должно быть ваши 11 строк, к сожалению опыта нет с оконными функциями


Этот запрос выдал 4228692 записей ) правильный результат по группам для каждой строки в таблице.
А вот select distinct count(1) OVER (PARTITION BY country) from dim_users; я даже не дождался - там слишком много вычислений.
18 мар 20, 22:25    [22101822]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

Пожалуйста:

mysql> explain analyze select country from dim_users;
+-----------+
| EXPLAIN   
+-----------+
| -> Index scan on dim_users using country_idx  (cost=428631.85 rows=4208241) (actual time=0.035..8152.934 rows=4228692 loops=1)
+-----------+
1 row in set (9.27 sec)

mysql> explain analyze select country,count(1) from dim_users where country is not null group by country;
+-----------+
| EXPLAIN
+-----------+
| -> Group aggregate: count(1)  (actual time=188.681..12236.663 rows=10 loops=1)
|    -> Filter: (dim_users.country is not null)  (cost=425189.57 rows=2104120) (actual time=0.028..9545.753 rows=3773927 loops=1)
|        -> Index range scan on dim_users using country_idx  (cost=425189.57 rows=2104120) (actual time=0.027..8286.962 rows=3773927 loops=1)
+-----------+
1 row in set (12.24 sec)


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

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov
Alex_Ustinov
Eugene Zhulkov,

да, вы правы, я перепутал синтаксис
select country, count(*) OVER(PARTITION BY country) FROM dim_users
но должно быть ваши 11 строк, к сожалению опыта нет с оконными функциями


Этот запрос выдал 4228692 записей ) правильный результат по группам для каждой строки в таблице.
А вот select distinct count(1) OVER (PARTITION BY country) from dim_users; я даже не дождался - там слишком много вычислений.

мда, это все записи, не шарю я в "окнах" и голова не думает

+ Самый простой вариант перевести в MyIsam Aria

не знаю насколько у вас важна отказоустойчивость, суть в следующем
в Ubuntu 18 по умолчанию идет MariaDB это тот же MySQL (не вдаваясь в подробности)
в MariaDB вместо MyIsam используется движок Aria, не буду опять же заострять внимание в разнице джижков.
Я бы оставил MariaDB и эту таблицу держал бы на движке Aria. Выборки предположительно были бы не больше 2-3 сек. подтвердить не могу, доказать не могу. Тестовых данных нет. Завтра будет скучно - сгенерирую к вечеру.
В общем то можете пробовать и с MyIsam. У меня личная неприязнь к MySQL я работаю с MariaDB...психологически доверия больше)

---
+ 1. Вариант с InnoDB

- добавить индекс
country+ЕщеПоле
чтобы у него была хорошая избирательность.
Далее собирать по частям двойным запросом
Select country, SUM(N) FROM
(SELECT country, ЕщеПоле, Count(*) as N from dma_users group by country, ЕщеПоле) as t Group By country
костыль зависит от выбора составного индекса...

+ 2. Вариант с InnoDB
- триггер на табл dma_users на Insert/Delete. если данные в табл относительно статичные
В триггере пишем счетчик в другую таблицу dma_users_count(country, ccount). на Insert к стране +1, Delete -1
Может есть и другие варианты
18 мар 20, 22:35    [22101827]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Guzya
Member

Откуда:
Сообщений: 453
Eugene Zhulkov
Eugene Zhulkov,

Пожалуйста:

mysql> explain analyze select country from dim_users;
+-----------+
| EXPLAIN   
+-----------+
| -> Index scan on dim_users using country_idx  (cost=428631.85 rows=4208241) (actual time=0.035..8152.934 rows=4228692 loops=1)
+-----------+
1 row in set (9.27 sec)

mysql> explain analyze select country,count(1) from dim_users where country is not null group by country;
+-----------+
| EXPLAIN
+-----------+
| -> Group aggregate: count(1)  (actual time=188.681..12236.663 rows=10 loops=1)
|    -> Filter: (dim_users.country is not null)  (cost=425189.57 rows=2104120) (actual time=0.028..9545.753 rows=3773927 loops=1)
|        -> Index range scan on dim_users using country_idx  (cost=425189.57 rows=2104120) (actual time=0.027..8286.962 rows=3773927 loops=1)
+-----------+
1 row in set (12.24 sec)



Получается основной затык это чтение индекса, т.е. чтение с диска.
У Вас точно тот же диск в разделе с postgres, может там другой диск смонтирован?
18 мар 20, 22:41    [22101832]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

Спасибо за советы! Попробую поставить для начала версию MySQL 5.7, может там не будет таких проблем. Если не получится, то попробую на MariaDB, но мне надо почитать их описание функций, поддерживаются ли некоторые из тех, что мы используем при аналитике.

Прикладная проблема еще в том, что нужны adhoc-запросы, то есть group by country это один из многих примеров того, как будут гонять этот датасет. Тут на всю возможную комбинацию колонок индексы уже не построишь.
18 мар 20, 22:42    [22101833]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

Абсолютно тот же диск, даже так - стоял постгрес в виде пакета, результаты были быстрые.
Сейчас я поставил постгрес в докере, чтобы быстрее развернуть для тестирования.

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

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov,

не, в 5,7 тоже самое. И нет оконных функций. В идеале вам бы они нужны, если группировки различные. Разобраться бы с ними.
это старый недостаток InnoDB, не тратьте время
18 мар 20, 23:10    [22101841]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

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

Я могу перезалить в MyISAM, не принципиально
Плюс попробую MariaDB
18 мар 20, 23:18    [22101842]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
mayton
Member

Откуда: loopback
Сообщений: 45483
Статистика пользователей по странам - вещь не сильно оперативная.

Ее можно материализовать. И обновлять по времени. Или по событию.
19 мар 20, 01:42    [22101863]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
mayton
Статистика пользователей по странам - вещь не сильно оперативная.

Ее можно материализовать. И обновлять по времени. Или по событию.


База полностью read-only, там никогда не будет вставок или обновлений
19 мар 20, 08:11    [22101897]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
mayton
Member

Откуда: loopback
Сообщений: 45483
Тогда ещё лучше.
19 мар 20, 08:48    [22101903]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Eugene Zhulkov
Member

Откуда:
Сообщений: 29
[quot Alex_Ustinov#22101827]
Eugene Zhulkov
пропущено...


Самый простой вариант перевести в MyIsam Aria
не знаю насколько у вас важна отказоустойчивость, суть в следующем
в Ubuntu 18 по умолчанию идет MariaDB это тот же MySQL (не вдаваясь в подробности)
в MariaDB вместо MyIsam используется движок Aria, не буду опять же заострять внимание в разнице джижков.
Я бы оставил MariaDB и эту таблицу держал бы на движке Aria. Выборки предположительно были бы не больше 2-3 сек. подтвердить не могу, доказать не могу. Тестовых данных нет. Завтра будет скучно - сгенерирую к вечеру.
В общем то можете пробовать и с MyIsam. У меня личная неприязнь к MySQL я работаю с MariaDB...психологически доверия больше)


Для информации - поставил последнюю MariaDB и залил таблицы в Aria-движок. Время улучшилось с 12 до 6 секунд.
20 мар 20, 18:36    [22103159]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 3061
Eugene Zhulkov,

план запроса бы показали.
там похоже надо тюнить настройки ДБ
с дефолтовыми настройками может тормозить
21 мар 20, 19:17    [22103680]     Ответить | Цитировать Сообщить модератору
 Re: Нужна платная консультация по настройке MySQL 8  [new]
mayton
Member

Откуда: loopback
Сообщений: 45483
А я предлагал 0 секунд.

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

Откуда:
Сообщений: 29
mayton
А я предлагал 0 секунд.

Ох консерваторы....


Нам нужна просто быстрая база для выполнения различных adhoc-запросов
То, что я привел в качестве примера, все-лишь один из десятков аналогичных и более сложных аналитических запросов, которые будут выполняться на базе.
22 мар 20, 08:49    [22103854]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Работа Ответить