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