ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Продолжение борьбы со скоростью выполнения запросов
33 сообщений из 33, показаны все 2 страниц
  все  
Продолжение борьбы со скоростью выполнения запросов
    #39715747
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
Здравствуйте. Недавно создавал тему http://www.sql.ru/forum/1303095-1/skorost-vypolneniya-zaprosa-bolshaya-tablica , где выяснил как лучше построить таблицу и оптимизировать скорость. Результат был достигнут.

Сейчас на сервере 2 основные использующиеся таблицы с одинаковой структурой и данными (разница только в названиях таблиц)
В таблице #1 214млн строк, ее вес 40ГБ (из них 25гб индексы)
В таблице #2 26млн строк, ее вес 5.5ГБ (из них 3.5гб индексы)

Первая проблема. Работает все как самолет ровно до того момента, как не очистишь кеш памяти, либо не перезагрузишь сервер. Стоит дебиан. Я так понимаю, что MySQL хранит индексы в памяти и берет ее под кеш. Потому что после некоторых манипуляций выборка сразу начинает летать. Какие манипуляции нужно сделать, чтобы сервер полетел:

Если выполнить дубликат таблицы #1, то во время его выполнения я так понимаю происходит операция чтения и в этот же момент кешируется в память информация. Вот скрин free -m в момент старта дубликата таблицы.

Код: powershell
1.
2.
3.
4.
5.
root@ns344370:~# free -m
             total       used       free     shared    buffers     cached
Mem:        128965      76802      52163         31         61      21714
-/+ buffers/cache:      55026      73939
Swap:        56141          0      56141


Вот, когда идентичная таблица таблице #1 создана.
Код: powershell
1.
2.
3.
4.
5.
root@ns344370:~# free -m
             total       used       free     shared    buffers     cached
Mem:        128965     126414       2551         31         49      65426
-/+ buffers/cache:      60938      68027
Swap:        56141          0      56141



Сожрало 50ГБ оперативной памяти под кеш.

Выполнение запроса до дубликата таблицы 105 секунд:

Код: powershell
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
# Query_time: 105.469931  Lock_time: 0.000180 Rows_sent: 41041  Rows_examined: 2097994
SET timestamp=1539135133;
SELECT SQL_CACHE `id`, `currency`, `handLimit`, `date`, `pp1` AS `profit`, `psd1` AS `isSD` FROM `ps_hands` WHERE `p1` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10') 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp2` AS `profit`, `psd2` AS `isSD` FROM `ps_hands` WHERE `p2` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp3` AS `profit`, `psd3` AS `isSD` FROM `ps_hands` WHERE `p3` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp4` AS `profit`, `psd4` AS `isSD` FROM `ps_hands` WHERE `p4` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp5` AS `profit`, `psd5` AS `isSD` FROM `ps_hands` WHERE `p5` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp6` AS `profit`, `psd6` AS `isSD` FROM `ps_hands` WHERE `p6` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp7` AS `profit`, `psd7` AS `isSD` FROM `ps_hands` WHERE `p7` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp8` AS `profit`, `psd8` AS `isSD` FROM `ps_hands` WHERE `p8` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp9` AS `profit`, `psd9` AS `isSD` FROM `ps_hands` WHERE `p9` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
ORDER BY `id` ASC;



А сразу после того, как завершен был дубликат - аналогичный запрос выполняется 0.2 секунды. И все похожие так же. (Кеш самих SQL запросов отключен).

Так же заметил ту же ситуацию с репейр. Если делать репейр таблицы, то после репейра она сразу бешенная, если память на сервере без кеша и репейр не делать, то GGWP как говорится, будешь ждать на каждый запрос миллион лет.

У меня будет таких таблиц по 50гб еще 4-5. На сервере сейчас SSHD диск. Думаю взять сервер с NVMe, чтобы ускорить работу масика раз в 5. Но мне все равно тупо не хватит оперативной памяти.
Сейчас оперативки на сервере 128гб. Помимо этого проекта на сервере есть еще один. На который выделено 50.
Остальное я бахнул для масика этого проекта.

Вопрос: как быть в таком случае? Можно ли обработку и хранение этой информации в памяти как-то перенести на диск, или будет медленнее? Использую MyISAM а не InnoDB , т.к. на сайте только выборка и инсерты. Иннодб в 20 раз медленнее, проверял на домашней машине тоже с M.2 диском. P.S. вот мой конфиг масика , который 100% я настроил криво.

Код: powershell
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
query_cache_type = DEMAND

key_buffer_size = 55G
sort_buffer_size = 512M
read_buffer_size = 128M

max_allowed_packet = 32M
thread_stack		= 192K
thread_cache_size       = 4096

#MySQL Tuner
max_heap_table_size = 128M
tmp_table_size = 128M
table_open_cache = 4096

myisam-recover         = BACKUP
max_connections        = 2000
table_cache            = 2048
thread_concurrency     = 17
query_cache_limit	= 128M
query_cache_size  = 256M

log_slow_queries  = /var/log/mysql/mysql-slow.log
long_query_time   = 1
#log-queries-not-using-indexes

expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name

innodb_buffer_pool_size = 512M

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 256M
!includedir /etc/mysql/conf.d/
...
Рейтинг: 0 / 0
10.10.2018, 19:27
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715817
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Иннодб в 20 раз медленнееНе верю. В 2 раза и то сомнительно. Видимо, что-то очень криво было настроено.

8POWER-
Код: sql
1.
2.
3.
sort_buffer_size = 512M
read_buffer_size = 128M
max_connections  = 2000

Это ж терабайт с четверью памяти в пределе.


8POWER-
Код: powershell
1.
innodb_buffer_pool_size = 512M

Зачем, если вы его не используете?
8POWER-Работает все как самолет ровно до того момента, как не очистишь кеш памяти, либо не перезагрузишь сервер. Стоит дебиан. Я так понимаю, что MySQL хранит индексы в памяти и берет ее под кеш. Потому что после некоторых манипуляций выборка сразу начинает летать. Какие манипуляции нужно сделать, чтобы сервер полетел:Логично, ведь нужно прочитать с диска несколько гигабайт.
Чтобы загрузить индексы в кэш индексов MyISAM можно использовать LOAD INDEX INTO CACHE
...
Рейтинг: 0 / 0
10.10.2018, 22:01
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715819
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-,

Кстати, покажите новый DDL таблицы.
...
Рейтинг: 0 / 0
10.10.2018, 22:03
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715823
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-,
Кстати, покажите новый DDL таблицы.
DDL на данный момент:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
CREATE TABLE `ps_hands` (
  `id` bigint(14) NOT NULL DEFAULT '0',
  `currency` tinyint(1) NOT NULL,
  `handLimit` smallint(5) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `maxPlayers` tinyint(1) NOT NULL,
  `p1` mediumint(7) NOT NULL DEFAULT '0',
  `p2` mediumint(7) NOT NULL DEFAULT '0',
  `p3` mediumint(7) NOT NULL DEFAULT '0',
  `p4` mediumint(7) NOT NULL DEFAULT '0',
  `p5` mediumint(7) NOT NULL DEFAULT '0',
  `p6` mediumint(7) NOT NULL DEFAULT '0',
  `p7` mediumint(7) NOT NULL DEFAULT '0',
  `p8` mediumint(7) NOT NULL DEFAULT '0',
  `p9` mediumint(7) NOT NULL DEFAULT '0',
  `pp1` mediumint(7) NOT NULL,
  `pp2` mediumint(7) NOT NULL,
  `pp3` mediumint(7) NOT NULL,
  `pp4` mediumint(7) NOT NULL,
  `pp5` mediumint(7) NOT NULL,
  `pp6` mediumint(7) NOT NULL,
  `pp7` mediumint(7) NOT NULL,
  `pp8` mediumint(7) NOT NULL,
  `pp9` mediumint(7) NOT NULL,
  `psd1` tinyint(1) NOT NULL,
  `psd2` tinyint(1) NOT NULL,
  `psd3` tinyint(1) NOT NULL,
  `psd4` tinyint(1) NOT NULL,
  `psd5` tinyint(1) NOT NULL,
  `psd6` tinyint(1) NOT NULL,
  `psd7` tinyint(1) NOT NULL,
  `psd8` tinyint(1) NOT NULL,
  `psd9` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `maxPlayers` (`maxPlayers`),
  KEY `p1_handLimit` (`p1`,`handLimit`),
  KEY `p2_handLimit` (`p2`,`handLimit`),
  KEY `p3_handLimit` (`p3`,`handLimit`),
  KEY `p4_handLimit` (`p4`,`handLimit`),
  KEY `p5_handLimit` (`p5`,`handLimit`),
  KEY `p6_handLimit` (`p6`,`handLimit`),
  KEY `p7_handLimit` (`p7`,`handLimit`),
  KEY `p8_handLimit` (`p8`,`handLimit`),
  KEY `p9_handLimit` (`p9`,`handLimit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
...
Рейтинг: 0 / 0
10.10.2018, 22:08
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715824
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-Иннодб в 20 раз медленнееНе верю. В 2 раза и то сомнительно. Видимо, что-то очень криво было настроено.

8POWER-
Код: sql
1.
2.
3.
sort_buffer_size = 512M
read_buffer_size = 128M
max_connections  = 2000

Это ж терабайт с четверью памяти в пределе.


8POWER-
Код: powershell
1.
innodb_buffer_pool_size = 512M

Зачем, если вы его не используете?
8POWER-Работает все как самолет ровно до того момента, как не очистишь кеш памяти, либо не перезагрузишь сервер. Стоит дебиан. Я так понимаю, что MySQL хранит индексы в памяти и берет ее под кеш. Потому что после некоторых манипуляций выборка сразу начинает летать. Какие манипуляции нужно сделать, чтобы сервер полетел:Логично, ведь нужно прочитать с диска несколько гигабайт.
Чтобы загрузить индексы в кэш индексов MyISAM можно использовать LOAD INDEX INTO CACHE

1. show profiles (1 таблица - myisam, 2 таблица - innodb) , тестирую пока что на домашнем компе на M2 диске, 3000/2500 мб.
Немного ошибся, не в 20 раз медленее, а 10. Конфиг не настроен ни для myisam, ни для innodb.
Код: powershell
1.
2.
3.
4.
|       12 |  0,1877075 | SELECT `id`, `currency`, `handLimit`, `date`, `pp1` AS `profit`, `psd1` AS `isSD` FROM `888_hands` WHERE `p1` = '443' AND (`date` >= '2018-01-01' AND `date` <= '2018-10-10')
 UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp2` AS `profit`, `psd2` AS `isSD` FROM `888_hands` WHERE `p2` = ' |
|       13 | 2,14406275 | SELECT `id`, `currency`, `handLimit`, `date`, `pp1` AS `profit`, `psd1` AS `isSD` FROM `888_hands_copy` WHERE `p1` = '443' AND (`date` >= '2018-01-01' AND `date` <= '2018-10-10')
 UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp2` AS `profit`, `psd2` AS `isSD` FROM `888_hands_copy` WHER |


2. На счет терабайта с четвертью памяти - да, согласен, но ведь mysql эту память не использует всегда? Он же ее добавляет по количеству коннекшенов вроде как. Пока что max_used_connections за неделю было 227. Могу снизить до 400-500. Не знаю, даст ли это что-то.
3. innodb_buffer_pool_size = 512M , упс. уберу.
...
Рейтинг: 0 / 0
10.10.2018, 22:13
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715828
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
Я так понимаю, что индексы всегда будут храниться в памяти? И единственный для меня выход при появлении новых больших таблиц, чтобы это все быстро работало - либо уменьшать размер индексов (порезав индексы и потеряв скорость), либо искать сервер с большей оперативкой, что практически неподъемные деньги.
...
Рейтинг: 0 / 0
10.10.2018, 22:22
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715844
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Конфиг не настроен ни для myisam, ни для innodb.Тогда это ни о чем. Умолчательный конфиг в MySQL никогда, пожалуй, не был адекватным.
8POWER-На счет терабайта с четвертью памяти - да, согласен, но ведь mysql эту память не использует всегда? Он же ее добавляет по количеству коннекшенов вроде как. Пока что max_used_connections за неделю было 227. Могу снизить до 400-500. Не знаю, даст ли это что-то.Пока памяти хватает - ничего не даст. Когда памяти не будет хватать, лучше получить явную ошибку о нехватке коннекшенов новым клиентам, нежели вывалить MySQL в своп и парализовать работу многих (или даже всех) уже подключенных клиентов.
8POWER-
Код: powershell
1.
`p1` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')

Идексы вида (`p1`,`date`) не пробовали?
...
Рейтинг: 0 / 0
10.10.2018, 23:16
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715845
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Я так понимаю, что индексы всегда будут храниться в памяти? И единственный для меня выход при появлении новых больших таблиц, чтобы это все быстро работало - либо уменьшать размер индексов (порезав индексы и потеряв скорость), либо искать сервер с большей оперативкой, что практически неподъемные деньги.Тут надо разделять разные виды кэша.
MyISAM в своем кэше (который key_buffer_size) умеет хранить только индексы, но не умеет таблицы.
Файловая система хранит в своем кэше любые файлы, которые кто-либо читает на этом сервере (при условии, что читающий это явно не запретил). В том числе файлы с индексами и таблицами.

Есть еще варианты.
Например, как говорилось раньше, перейти на вертикальную структуру и InnoDB. Таблицы в InnoDB по внутреннему устройству фактически являются индексами с дополнительными полями. Поэтому выборка по первичном ключу вполне может быть быстрее выборки по обычному индексу в MyISAM, т.к. не будет необходимости отдельно читать содержимое таблицы.
Или, например, сделать покрывающий индекс. Т.е. поместить в него все поля, используемые в запросе, чтобы не читать содержимое таблицы. Но при текущей структуре таблицы, возможно, не хватит памяти, чтобы закэшировать ваши 9 индексов.
...
Рейтинг: 0 / 0
10.10.2018, 23:23
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715847
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-Конфиг не настроен ни для myisam, ни для innodb.Тогда это ни о чем. Умолчательный конфиг в MySQL никогда, пожалуй, не был адекватным.
8POWER-На счет терабайта с четвертью памяти - да, согласен, но ведь mysql эту память не использует всегда? Он же ее добавляет по количеству коннекшенов вроде как. Пока что max_used_connections за неделю было 227. Могу снизить до 400-500. Не знаю, даст ли это что-то.Пока памяти хватает - ничего не даст. Когда памяти не будет хватать, лучше получить явную ошибку о нехватке коннекшенов новым клиентам, нежели вывалить MySQL в своп и парализовать работу многих (или даже всех) уже подключенных клиентов.
8POWER-
Код: powershell
1.
`p1` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')

Идексы вида (`p1`,`date`) не пробовали?

Uueerdo, 786340 rows in set - its query SELECT date FROM ps_hands WHERE date = '2018-05-05'; That is, in one day so many entries (786k). And now imagine the index date .. A player is supposed to have a total of 300 thousand entries per year (as example). And we have to process so many dates. It will be very hard for the server.

786340 rows in set - это запрос
Код: sql
1.
SELECT date FROM ps_hands WHERE date = '2018-05-05'


Получается так много строк в среднем за один день (по дате). Поэтому представьте какой будет индекс, если проиндексировать дату и сколько строк придется обработать серверу. А у игрока в среднем будет 30к строк, конечно может быть и 20 а может быть и 2 млн. Смотря, что за игрок. Это как пример. Это будет сильно сложно для сервера, если индексировать по дате.
...
Рейтинг: 0 / 0
10.10.2018, 23:30
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715848
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-Я так понимаю, что индексы всегда будут храниться в памяти? И единственный для меня выход при появлении новых больших таблиц, чтобы это все быстро работало - либо уменьшать размер индексов (порезав индексы и потеряв скорость), либо искать сервер с большей оперативкой, что практически неподъемные деньги.Тут надо разделять разные виды кэша.
MyISAM в своем кэше (который key_buffer_size) умеет хранить только индексы, но не умеет таблицы.
Файловая система хранит в своем кэше любые файлы, которые кто-либо читает на этом сервере (при условии, что читающий это явно не запретил). В том числе файлы с индексами и таблицами.

Есть еще варианты.
Например, как говорилось раньше, перейти на вертикальную структуру и InnoDB. Таблицы в InnoDB по внутреннему устройству фактически являются индексами с дополнительными полями. Поэтому выборка по первичном ключу вполне может быть быстрее выборки по обычному индексу в MyISAM, т.к. не будет необходимости отдельно читать содержимое таблицы.
Или, например, сделать покрывающий индекс. Т.е. поместить в него все поля, используемые в запросе, чтобы не читать содержимое таблицы. Но при текущей структуре таблицы, возможно, не хватит памяти, чтобы закэшировать ваши 9 индексов.

1. - возможно ли вообще перегнать файлы с индексами и таблицами (если уж и то и то) в кеш файловой системы вместо памяти? и чтобы это так же быстро работало. если нет, тогда придется мне с масика 5.6 обновляться на 8 и юзать иннодб.

2. горизонтальную таблицу вы еще тогда упоминали, но я сразу испугался и до сих пор не понимаю, как ее реализовать. т.к. по моему она только больше весить будет.
...
Рейтинг: 0 / 0
10.10.2018, 23:32
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715852
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Получается так много строк в среднем за один день (по дате). Поэтому представьте какой будет индекс, если проиндексировать дату и сколько строк придется обработать серверу.В индексе хранится столько же записей, сколько и во всей таблице. Так что если текущий `handLimit` заменить на `date`, то прирост размера одного индекса составит всего 1 байт на запись.
И, заметьте, я говорю не об отдельном индексе по полю `date`, а об индексе по двум полям. В этом случае выборка по второму полю осуществляется уже по той ветке индекса, которая была найдена по первому полю.
...
Рейтинг: 0 / 0
10.10.2018, 23:48
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715855
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-возможно ли вообще перегнать файлы с индексами и таблицами (если уж и то и то) в кеш файловой системы вместо памяти?Слово "вместо" не имеет смысла, т.к. кэш файловой системы и располагается в памяти. И да, как показали ваши эксперименты, при каких-либо операциях с таблицами (с точки зрения ФС - с файлами) кэш ФС наполняется содержимым этих файлов.
8POWER-горизонтальную таблицу вы еще тогда упоминали, но я сразу испугался и до сих пор не понимаю, как ее реализовать. т.к. по моему она только больше весить будет.Вертикальную.
Сама таблица весить будет примерно столько же. Чуть больше накладных расходов, но и чуть экономии за счет отказа от хранения пустых мест в игре (когда играют меньше 9 человек).
Но зато полностью пропадет необходимость в индексах, т.к. в InnoDB таблица сама будет своим индексом по первичному ключу.

Наверное, можно такое и в рамках MyISAM провернуть. Особенно, если сделать покрывающий индекс. Но это надо тестить.
8POWER-с масика 5.6 обновляться на 8Обновляться-то зачем?
Тут вроде не нужно никаких фич 8 версии.
...
Рейтинг: 0 / 0
10.10.2018, 23:57
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715860
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft,

Спасибо вам за все советы, сейчас буду долго и нудно тестить, потом отпишусь о результатах. И наконец-то попробую все-таки перевернуть таблицу. Покажу допустим 30 строк, как она выглядела до и как выглядит после.
...
Рейтинг: 0 / 0
11.10.2018, 00:14
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39715878
вадя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вадя
Участник
8POWER-,
обновиться стоит , как минимум до 5.7.хх
...
Рейтинг: 0 / 0
11.10.2018, 05:06
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39716370
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft, попытался развернуть таблицу.
Как было:
http://i.piccy.info/i9/fad2f983861769a1a25feae798150d4b/1539278550/8136/1271065/screenshot17.png

и DDL:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
CREATE TABLE `888_hands_copy1` (
  `id` bigint(14) NOT NULL DEFAULT '0',
  `currency` tinyint(1) NOT NULL,
  `handLimit` smallint(5) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `maxPlayers` tinyint(1) NOT NULL,
  `p1` mediumint(7) NOT NULL DEFAULT '0',
  `p2` mediumint(7) NOT NULL DEFAULT '0',
  `p3` mediumint(7) NOT NULL DEFAULT '0',
  `p4` mediumint(7) NOT NULL DEFAULT '0',
  `p5` mediumint(7) NOT NULL DEFAULT '0',
  `p6` mediumint(7) NOT NULL DEFAULT '0',
  `p7` mediumint(7) NOT NULL DEFAULT '0',
  `p8` mediumint(7) NOT NULL DEFAULT '0',
  `p9` mediumint(7) NOT NULL DEFAULT '0',
  `pp1` mediumint(7) NOT NULL,
  `pp2` mediumint(7) NOT NULL,
  `pp3` mediumint(7) NOT NULL,
  `pp4` mediumint(7) NOT NULL,
  `pp5` mediumint(7) NOT NULL,
  `pp6` mediumint(7) NOT NULL,
  `pp7` mediumint(7) NOT NULL,
  `pp8` mediumint(7) NOT NULL,
  `pp9` mediumint(7) NOT NULL,
  `psd1` tinyint(1) NOT NULL,
  `psd2` tinyint(1) NOT NULL,
  `psd3` tinyint(1) NOT NULL,
  `psd4` tinyint(1) NOT NULL,
  `psd5` tinyint(1) NOT NULL,
  `psd6` tinyint(1) NOT NULL,
  `psd7` tinyint(1) NOT NULL,
  `psd8` tinyint(1) NOT NULL,
  `psd9` tinyint(1) NOT NULL,
  KEY `maxPlayers` (`maxPlayers`),
  KEY `p1_handLimit` (`p1`,`handLimit`) USING BTREE,
  KEY `p2_handLimit` (`p2`,`handLimit`) USING BTREE,
  KEY `p3_handLimit` (`p3`,`handLimit`) USING BTREE,
  KEY `p4_handLimit` (`p4`,`handLimit`) USING BTREE,
  KEY `p5_handLimit` (`p5`,`handLimit`) USING BTREE,
  KEY `p6_handLimit` (`p6`,`handLimit`) USING BTREE,
  KEY `p7_handLimit` (`p7`,`handLimit`) USING BTREE,
  KEY `p8_handLimit` (`p8`,`handLimit`) USING BTREE,
  KEY `p9_handLimit` (`p9`,`handLimit`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;


Как стало: создал 2 таблицы
1) Хранит данные о руке, имеет уникальный ID.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE `v888_hands` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `handId` bigint(14) NOT NULL DEFAULT '0',
  `currency` tinyint(1) NOT NULL,
  `handLimit` smallint(5) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `maxPlayers` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;


2) Содержит игроков и что они выиграли, связана по внешнему ключу ID в первой таблице.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE `v888_players` (
  `handId` int(10) unsigned NOT NULL,
  `p` mediumint(7) NOT NULL,
  `pp` mediumint(7) NOT NULL DEFAULT '0',
  `psd` tinyint(1) NOT NULL,
  KEY `id` (`handId`) USING BTREE,
  CONSTRAINT `id` FOREIGN KEY (`handId`) REFERENCES `v888_hands` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;



Правильно ли я всё сделал?
...
Рейтинг: 0 / 0
11.10.2018, 20:59
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39716431
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft, подскажите пожалуйста, я просто хочу уточнить, потому что данные мне заливать придется минимум день.. в сумме 300 млн строк будет(
...
Рейтинг: 0 / 0
12.10.2018, 01:14
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39716471
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-,

Нет, не совсем правильно. В эту структуру заливать данные не стоит.

Я на бегу, не могу с телефона полноценно ответить :(
...
Рейтинг: 0 / 0
12.10.2018, 07:56
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39716676
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-,

Нет, не совсем правильно. В эту структуру заливать данные не стоит.

Я на бегу, не могу с телефона полноценно ответить :(
Хорошо, спасибо, буду ждать.
...
Рейтинг: 0 / 0
12.10.2018, 14:09
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717316
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
8POWER-,

Я так понимаю это выходные, но если что, все еще жду :)
...
Рейтинг: 0 / 0
14.10.2018, 20:34
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717320
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE `hands` (
  `handId` bigint NOT NULL,
  `currency` tinyint NOT NULL,
  `handLimit` smallint(5) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `maxPlayers` tinyint NOT NULL,
  PRIMARY KEY (`handId`,`date`) 
) ENGINE=InnoDB

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE `players` (
  `p` mediumint NOT NULL,
  `handId` bigint unsigned NOT NULL,
  `pp` mediumint NOT NULL DEFAULT '0',
  `psd` tinyint NOT NULL,
  PRIMARY KEY (`p`,`handId`),
  CONSTRAINT `id` FOREIGN KEY (`handId`) REFERENCES `hands` (`handId`)  -- FK лучше убрать, чтобы не снижать производительность вставки данных
) ENGINE=InnoDB


Как-то так, если не требуется знать порядок игроков в рамках одной игры.
Возможно, что-то напутал в синтаксисе, но идея должна быть понятна - в каждой из таблиц в PK ведущим полем является именно то поле, по которому будет вестись поиск.
Учтите, что структура приспособлена под конкретный запрос. Для других условий она может быть неподходящей.

В таблицу players не надо писать все 9 записей на каждую игру, а только по числу фактических участников.

Запрос будет как-то так:
Код: sql
1.
2.
3.
4.
5.
6.
SELECT SQL_NO_CACHE p.`handId`, h.`currency`, h.`handLimit`, h.`date`, p.`pp` AS `profit`, p.`psd` AS `isSD` 
FROM `players` p
  JOIN `hands` h ON p.handId=h.handId
WHERE p.p=274606  -- лучше без кавычек, чтобы тип данных был одинаковый с обоих сторон сравнения
  AND h.`date` >= '2018-10-01' AND h.`date` <= '2018-10-10'
ORDER BY p.handId

SQL_NO_CACHE указал специально, чтобы результаты из кэша результатов запросов не брались.

Залейте немного данных, например 1 миллион игр, и покажите план запроса после этого.
...
Рейтинг: 0 / 0
14.10.2018, 21:08
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717322
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
miksoft
Код: sql
1.
PRIMARY KEY (`handId`,`date`)

Тут, наверно, все же PRIMARY KEY (`handId`), т.к. по факту поле `handId` будет уникальным, добавлять второе поле смысла нет.
...
Рейтинг: 0 / 0
14.10.2018, 21:16
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717324
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft,

Ооо, ура)) здравствуйте. Сейчас все прочитаю, скажите пожалуйста, а можно ли еще как-то связаться с вами, кроме форума? Например скайп. Быстрее бы разговор прошел и я бы отблагодарил :)
...
Рейтинг: 0 / 0
14.10.2018, 21:20
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717329
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-а можно ли еще как-то связаться с вами, кроме форума? Например скайп.Скайпа у меня нет, как-то не нужен был никогда.
И, честно говоря, я не очень люблю личные контакты, поскольку они налагают определенную личную ответственность, а в текущее время я к этому не готов. Прошу понять.
...
Рейтинг: 0 / 0
14.10.2018, 21:27
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717331
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft, Я вас понял, ничего) тогда у меня сразу к вам парочка вопросов, если не против.
#1
Решил попробовать LOAD INDEX INTO CACHE, как вы советовали. Очистил снова кеш дебиана.
Запрос (на выборку 200к строк) начал грузиться 60 сек. Затем загрузил все индексы в кеш. Запрос стал грузиться 15 сек. Думаю блин, попробую теперь сделать дубликат таблицы. Сделал дубликат. В таблице, которая задублирована - этот же запрос выполняется 1сек.
Как такое происходит? :(

#2
Поставил на домашний комп 8 масик, залил эту же таблицу в 216 млн строк. Делаю аналогичный запрос без всяких загрузок индексов в кеш - 1 секунда времени занимает. Учитывая, что оперы на компе всего 16гб. А на сервере аж 128 и из них кешируется 50, если индексы загружать.
...
Рейтинг: 0 / 0
14.10.2018, 21:34
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717335
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Очистил снова кеш дебиана.
Запрос (на выборку 200к строк) начал грузиться 60 сек. Затем загрузил все индексы в кеш. Запрос стал грузиться 15 сек.Даже несколько раз подряд 60 секунд?
И потом несколько раз подряд по 15 секунд?

При замерах времени выполняйте запросы несколько раз, чтобы лучше понимать влияние кэшей.
...
Рейтинг: 0 / 0
14.10.2018, 22:09
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717336
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Решил попробовать LOAD INDEX INTO CACHE, как вы советовали. Очистил снова кеш дебиана.
Запрос (на выборку 200к строк) начал грузиться 60 сек. Затем загрузил все индексы в кеш. Запрос стал грузиться 15 сек. Думаю блин, попробую теперь сделать дубликат таблицы. Сделал дубликат. В таблице, которая задублирована - этот же запрос выполняется 1сек.
Как такое происходит? :(Если это было для MyISAM, то могу предположить, что дело в том, что файловая система закэшировала содержимое таблицы. Сам MyISAM не умеет кэшировать содержимое таблиц, а только индексов. А для выполнения вашего запроса требуется сначала по индексу определить нужные записи, а потом получить их содержимое из самой таблицы.

Если бы было достаточно оперативки, чтобы разместить все данные в памяти, то можно было бы попробовать покрывающий индекс - индекс, в котором есть все выбираемые поля. Тогда кэша индексов MyISAM было бы достаточно.
Правда, в этом случае (когда все данные влезают в оперативку) возникает выбор - а не стоит ли задействовать какую-нибудь key-value СУБД. Для класса задач выбора данных по единственному полю они могут дать меньшее врем выполнения, чем РСУБД. Но эта тема мне знакома очень слабо, так что подробностей никаких предложить не могу.
...
Рейтинг: 0 / 0
14.10.2018, 22:18
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717338
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Поставил на домашний комп 8 масик, залил эту же таблицу в 216 млн строк. Делаю аналогичный запрос без всяких загрузок индексов в кеш - 1 секунда времени занимает. Учитывая, что оперы на компе всего 16гб. А на сервере аж 128 и из них кешируется 50, если индексы загружать.А что именно не так?
"кешируется 50" - это речь про результат free -m ? Так там кэшируются все файлы, к которым было обращение, а не только таблицы MySQL. Мало ли чем там еще сервер занят...
...
Рейтинг: 0 / 0
14.10.2018, 22:21
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717343
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-Решил попробовать LOAD INDEX INTO CACHE, как вы советовали. Очистил снова кеш дебиана.
Запрос (на выборку 200к строк) начал грузиться 60 сек. Затем загрузил все индексы в кеш. Запрос стал грузиться 15 сек. Думаю блин, попробую теперь сделать дубликат таблицы. Сделал дубликат. В таблице, которая задублирована - этот же запрос выполняется 1сек.
Как такое происходит? :(Если это было для MyISAM, то могу предположить, что дело в том, что файловая система закэшировала содержимое таблицы. Сам MyISAM не умеет кэшировать содержимое таблиц, а только индексов. А для выполнения вашего запроса требуется сначала по индексу определить нужные записи, а потом получить их содержимое из самой таблицы.

Если бы было достаточно оперативки, чтобы разместить все данные в памяти, то можно было бы попробовать покрывающий индекс - индекс, в котором есть все выбираемые поля. Тогда кэша индексов MyISAM было бы достаточно.
Правда, в этом случае (когда все данные влезают в оперативку) возникает выбор - а не стоит ли задействовать какую-нибудь key-value СУБД. Для класса задач выбора данных по единственному полю они могут дать меньшее врем выполнения, чем РСУБД. Но эта тема мне знакома очень слабо, так что подробностей никаких предложить не могу.

Тогда наиболее вероятно то, что при дубликате MyISAM таблицы - она кешируется полностью в память, т.е. не только индексы..
...
Рейтинг: 0 / 0
14.10.2018, 22:56
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717344
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft8POWER-Очистил снова кеш дебиана.
Запрос (на выборку 200к строк) начал грузиться 60 сек. Затем загрузил все индексы в кеш. Запрос стал грузиться 15 сек.Даже несколько раз подряд 60 секунд?
И потом несколько раз подряд по 15 секунд?

При замерах времени выполняйте запросы несколько раз, чтобы лучше понимать влияние кэшей.
Каждый запрос по одному разу, т.к. дальше он кешируется. SQL_NO_CACHE нельзя было на продакшене выполнить.
...
Рейтинг: 0 / 0
14.10.2018, 22:56
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717345
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft, и последний вопрос. Стоит ли нам вообще переходить на InnoDB? Я вот сейчас смотрю на то, какой он вялый - это ужас. MyISAM делает дубликат в 20 раз быстрее, заливает данные намного быстрее, и селектит тоже намного быстрее.
Может просто создать эти 2 таблицы в MyISAM ? У нас происходит только SELECT абсолютно всегда и то по индексам.
И раз в день около 5-10 минут инсерт. Апдейтов никогда не бывает, делетов тоже.
...
Рейтинг: 0 / 0
14.10.2018, 22:58
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717350
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Стоит ли нам вообще переходить на InnoDB? Я вот сейчас смотрю на то, какой он вялый - это ужас. MyISAM делает дубликат в 20 раз быстрее, заливает данные намного быстрее, и селектит тоже намного быстрее.Есть у меня подозрение...
Проверьте, в какое значение установлена настройка innodb_api_trx_level в конфиге.
Если в 1 или ни в какое, то поставьте 2.
...
Рейтинг: 0 / 0
15.10.2018, 00:22
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717351
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
8POWER-Может просто создать эти 2 таблицы в MyISAM ? У нас происходит только SELECT абсолютно всегда и то по индексам.Можно и в MyISAM. Пробуйте.
Как дополнительный вариант для тестов - создать покрывающие индексы на все нужные поля. Первыми должны идти поля по которым идет отбор, т.е. те, которые я вынес в PK.
...
Рейтинг: 0 / 0
15.10.2018, 00:24
| Ответить | Цитировать | Написать  
Продолжение борьбы со скоростью выполнения запросов
    #39717352
8POWER-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8POWER-
Гость
miksoft, Спасибо большое, уже тестирую.
...
Рейтинг: 0 / 0
15.10.2018, 00:56
| Ответить | Цитировать | Написать  
33 сообщений из 33, показаны все 2 страниц
  все  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Продолжение борьбы со скоростью выполнения запросов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=47&tid=1829546]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 14ms
total:  158ms
созд. / загр.: 158ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]