Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737 Форумы портала PHP.SU :: Mysql. Много данных. Нужна оптимизация
`country` enum('BRAZIL','NETHERLANDS','SWITZERLAND','SWEDEN','AUSTRIA','NEWZELAND','ITALY','SPAIN','GERMANY','USA','CANADA','ALBANIA','BELARUS','BELGIUM','BULGARIA','CROATIA','CYPRUS','CHECH REPUBLIC','DENMARK','DEUTSCHLAND','ESPANA','ESTONIA','FINLAND','FRANCE','GREECE','HUNGARY','ICELAND','IRELAND','ITALIA','LATVIA','LITHUANIA','LUXEMBOURG','MACEDONIA','MALTA','MOLDOVA','NEDERLAND','NORWAY','OSTERREICH','POLAND','PORTUGAL','ROMANIA','SCHWEIZ','SLOVAKIA','SLOVENIA','SVERIGE','TURKEY','UKRAINE','UK','RUSSIA','ALGERIA','ANGOLA','ARMENIA','AZERBAIJAN','BAHRAIN','BENIN','BOTSWANA','BURKINA FASO','CAPE VERDE','CHAD','CONGP','EGYPT','GAMBIA','GHANA','GUINEA-BISSAU','INDIA','ISRAEL','JORDAN','KENYA','KUWAIT','LEBANON','LIBERIA','MADAGASCAR','MALAWI','MALI','MAURITANIA','MAURITIUS','MOZAMBIQUE','NAMBIA','NIGER','NIGERIA','OMAN','QATAR','SAO TOME AND PRINCIPE','SAUDI ARABIA','SENEGAL','SEYCHELLES','SIERRA LEONE','SOUTH AFRICA','SWAZILAND','TANZANIA','TUNISIA','UAE','UGANDA','YEMEN','ZIMBABWE','AUSTRALIA','BHUTAN','BRUNEI DARUSSALAM','CAMBODIA','FIJI','HONG KONG','INDONESIA','KAZAKHSTAN','KYRGYZSTAN','LAO PEOPLE’S DEMOCRATIC REPUBLIC','MACAU','MALAYSIA','MICRONESIA','MONGOLIA','NEPAL','NEW ZEALAND','PAKISTAN','PALAU','PAPUA NEW GUINEA','PHILIPPINES','SINGAPORE','SOLOMON ISLANDS','SRI LANKA','TAIWAN','TAJIKISTAN','THAILAND','TURKMENISTAN','UZBEKISTAN','VIETNAM','KOREA','CHINA','JAPAN','ANGUILLA','ANTIGUA AND BARBUDA','ARGENTINA','BAHAMAS','BARBADOS','BELIZE','BERMUDA','BOLIVIA','BRASIL','BRITISH VIRGIN ISLANDS','CAYMAN ISLANDS','CHILE','COLOMBIA','COSTA RICA','DOMINICA','DOMINICA, REPUBLICA','ECUDOR','EL SALVADOR','GRENADA','GUATEMALA','GUYANA','HONDURAS','JAMAICA','MEXICO','MONSTSERRAT','NICARAGUA','PANAMA','PARAGUAY','PERU','ST. KITTS AN NEVIS','ST. LUCIA','ST. VINCENT ANDTHE GRENADINES','SURINAME','TRINIDAD AND TOBAGO','TURKS AND CAICOS','RUGUAY','VENEZUELA')DEFAULTNULL,
AND country IN('USA','UK','CANADA','AUSTRALIA','GERMANY','NEWZELAND')
GROUPBY appId, country, category, platform
HAVING DATEDIFF(maxD, minD)> 10
AND min(position)> 10
AND max(position)<= 100
ORDERBY diff
выполняется он очень прилично по времени 30+ сек, иногда до 2 минут, и вполне логично при увеличении количества данных запросы будут выполняться медленнее по неидексируемым полям
explain говорит так
Цитата:
+----+-------------+-----------------+-------+---------------------------+---------------------------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------------------+---------------------------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | store_top | range | i_country_store_top | i_country_store_top | 2 | NULL | 7935166 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+-------+---------------------------+---------------------------+---------+------+---------+----------------------------------------------+
какие мысли у меня:
добавить еще один индекс по created и/или по position
наверное имеет смысл удалить индекс i_category_store_top, так как он же дублируется в индексе i_ccp_store_top
что смущает - добавление индекса в прошлый раз длилось пол дня, поэтому добавление нужно производить взвешенно
что еще можно оптимизировать, думаю можно уменьшить величино поля для appId - там обычно 9 цифр (кто знает может в будущем будет 10 цифр), какой оптимальный тип нужно использовать?
далее enum филды пришли на замену варчарам, нt знаю будет ли производительнее использовать числа, и уже в коде обрабатывать то что в этом поле, но работать с числовыми значениями вместо строковых будет на порядок сложнее
вобщем если это поможет производительности, то можно будет сменить поле на какой-нибудь unsigned tinyint
короче нуждаюсь в конструктивной критике и дельным предложениям и замечаниям, чтобы подготовить бд к длительным изнурительным запросам (Добавление)
и еще вопрос, как правильнее запускать длительный альтер по ссш? я запускал просто в терминале, в итоге у меня рвалась сессия, но альтер вроде заканчивался успешно, вобщем хочется нормально накатывать миграции без проблем.
есть мысль запустить миграцию разово по крону, или есть предложения получше?
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
Я бы посоветовал отказаться здесь от enum'ов: изменить enum - это перестроить таблицу, для большой таблицы это будет долго.
Разумеется, это гораздо лучше, чем varchar, но лучше было бы 1:М сделать.
Panoptik пишет:
наверное имеет смысл удалить индекс i_category_apple_store_top, так как он же дублируется в индексе i_ccp_apple_store_top
Да, индекс лишний.
Я правильно понимаю, что данные в таблице носят исторический характер? Т.е. меняется что-то только за последнее время?
Простое партицирование по месяцам даст уже огромный выигрыш.
Стоит попробовать индекс по country & created
Panoptik пишет:
что смущает - добавление индекса в прошлый раз длилось пол дня
Сделайте копию таблицы с желаемой структурой и индексами, insert .. select'ом перепишите в неё часть данных (чтобы не ждать до завтра каждый индекс), посмотрите, какие индексы лучше использовать, какие добавить. Потом удалите данные, перепишите исторические данные, остановите сервис, допишите оставшиеся данные, сделайте RENAME TABLE (доли секунды, вне зависимости от размера таблиц), запустите сервис. (Добавление)
Panoptik пишет:
и еще вопрос, как правильнее запускать длительный альтер по ссш?
Длительные вещи лучше делать через screen.
-----
Panoptik
Отправлено: 21 Октября, 2014 - 16:17:39
Постоянный участник
Покинул форум
Сообщений всего: 2493
Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина
Помог: 131 раз(а)
Мелкий пишет:
это гораздо лучше, чем varchar, но лучше было бы 1:М сделать
что есть 1:М ?
Мелкий пишет:
Я правильно понимаю, что данные в таблице носят исторический характер? Т.е. меняется что-то только за последнее время?
да, данные просто накапливаются, никаких апдейтов нет
Мелкий пишет:
Простое партицирование по месяцам даст уже огромный выигрыш.
что под этим подразумевается? бекапить данные в другие таблицы и удалять из основной?
почитал тут http://habrahabr[dot]ru/post/66151/ терь прояснилось.
спасибо за наводку. будем открывать для себя расширенные возможности СУБД
если делать инсерт-селект как будут мапиться строковые (enum) данные в инт?
за screen спасибо. буду пробовать
и еще что на счет bigint? я посчитал что 3 байта мне с головой хватит, можно ли ограничить величину памяти для целочисленного типа? или пользовать unsigned integer и всё?
решено MEDIUMINT
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
Panoptik пишет:
что есть 1:М ?
Один-ко-многим, связь с отдельной таблицей.
Panoptik пишет:
если делать инсерт-селект как будут мапиться строковые (enum) данные в инт?
Select может быть любой, джойните с таблицей по значению, пишете ключ. Можно case нагородить, если значений не очень много.
-----
Panoptik
Отправлено: 21 Октября, 2014 - 17:06:43
Постоянный участник
Покинул форум
Сообщений всего: 2493
Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина
Помог: 131 раз(а)
еще момент с движком.
думаю что MyISAM в данном случае будет более производительным решением, как минимум для выборки. а вставка как?
у меня вставка идет по 200 значений в одном инсерте раз в 1-2 секунды (Добавление)
ди и стоит ли менять enum для типа платформ (iphone, ipad) если там всего 2 значения?
-----
Мелкий
Отправлено: 21 Октября, 2014 - 17:12:35
Активный участник
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
Даже думать не сметь. Только innoDB.
Транзакционность. Я не о возможности сказать rollback, я о сохранности данных. (Добавление)
Panoptik пишет:
ди и стоит ли менять enum для типа платформ (iphone, ipad) если там всего 2 значения?
Можно и страны не менять, не так уже часто они меняются.
Но если надо что-то изменить - имейте в виду, сколько на это понадобится времени.
-----
Panoptik
Отправлено: 21 Октября, 2014 - 17:14:32
Постоянный участник
Покинул форум
Сообщений всего: 2493
Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина
Помог: 131 раз(а)
Мелкий пишет:
Даже думать не сметь. Только innoDB.
еще раз спасибо. тем более я щас подумал что будет неплохо добавить внешние ключи (Добавление)
всплывает еще одна проблема
почитал что в конфиге mysql
очень желательно включить опцию
innodb_file_per_table
сейчас таковая отсутствует там и соответственно файлик ibdata1 уже равен 10Гб
что мне не особо нравится
учитывая что к концу года у меня будет пол милиарда записей в таблице мне не очень нравится перспектива разгребать тормоза на запущенном сервере.
щас там еще крутится десяток сайтов и имеется пару десятков баз, которые как я понимаю мне нужно будет бекапить.
попутный вопрос связанный с этим, нужно ли будет после восстановления бекапа восстанавливать привелегии и создавать пользователей для каждой базы заново?
-----
3d_killer
Отправлено: 21 Октября, 2014 - 19:10:15
Участник
Покинул форум
Сообщений всего: 1916
Дата рег-ции: Апр. 2011 Откуда: Ростов-на-Дону
Помог: 21 раз(а)
думаю при таком объеме БД стоит переходить на другую БД, mysql на сколько мне известно поддерживает 1 проц и 1 гб памяти больше для производительности кушать он не будет.
Мелкий
Отправлено: 21 Октября, 2014 - 19:24:24
Активный участник
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
3d_killer, у вас очень странные данные.
Память - сколько угодно. В принципе сколько угодно памяти. Сотня-другая гигов - значения не имеет, лишнее уйдёт на кеши, которых много бывает только если ВСЁ поместилось в памяти.
Процессоры - один запрос может исполняться только в одном потоке. Ну так запросы можно исполнять параллельно, пару десятков ядер можно использовать спокойно. Загрузить их - да, надо постараться. Но это для СУБД общая черта, сначала упираться в io и очень редко - в CPU.
Всего 50 лямов строк, даже для тупого mysql это немного.
Panoptik пишет:
тем более я щас подумал что будет неплохо добавить внешние ключи
Идея очень хорошая, но партицированные таблицы не умеют FK. (хотя, возможно уже добавили поддержку, как-то не пробовал давно)
Panoptik пишет:
очень желательно включить опцию
innodb_file_per_table
сейчас таковая отсутствует там и соответственно файлик ibdata1 уже равен 10Гб
что мне не особо нравится
Проверьте на виртуалке, но по-моему эта опция влияет только на создание таблиц...
Да, точно:
Цитата:
The --innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.
Покинул форум
Сообщений всего: 1916
Дата рег-ции: Апр. 2011 Откуда: Ростов-на-Дону
Помог: 21 раз(а)
как то запустил я 1С с БД около 50 гигов на mysql. сервер DELL 6850 - 32 ГБ оперативы и 16 ядер, и параметры в нем менял ничего не помогло 10 человек он тянул с ооочень большими тормозами, даже MS SQL Express справился с задачей лучше, но все же запустил на постгри (так как была задача экономии денег)
MiksIr
Отправлено: 21 Октября, 2014 - 19:49:56
Забанен
Покинул форум
Сообщений всего: 378
Дата рег-ции: Сент. 2014
Помог: 10 раз(а)
[+]
3d_killer пишет:
как то запустил я 1С с БД около 50 гигов на mysql. сервер DELL 6850 - 32 ГБ оперативы и 16 ядер, и параметры в нем менял ничего не помогло 10 человек он тянул с ооочень большими тормозами, даже MS SQL Express справился с задачей лучше, но все же запустил на постгри (так как была задача экономии денег)
Кто знает что вы там наконфигурили по памяти.
Мускуль (а вернее Мария) все еще проигрывает Постгресу на большом числе конкурентов, но уже не так сильно. Да и проявляется это особо сильно когда все в память влезает.
-----
Panoptik
Отправлено: 22 Октября, 2014 - 11:11:19
Постоянный участник
Покинул форум
Сообщений всего: 2493
Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина
Помог: 131 раз(а)
сервер у меня тоже хорош. 8 ядер и 32 Гб памяти
с 50 млн записей справляется отлично при выборках по индексам менее секунды
буду внедрять партицирование
Мелкий пишет:
Идея очень хорошая, но партицированные таблицы не умеют FK. (хотя, возможно уже добавили поддержку, как-то не пробовал давно)
вот это действительно не прокатит, у меня пока версия 5.5, а ключи с партицированием ввели с 5.6
с утра переконфигурировал субд
в любом случае работать станет легче
результаты отпишу позже
-----
Panoptik
Отправлено: 22 Октября, 2014 - 16:02:43
Постоянный участник
Покинул форум
Сообщений всего: 2493
Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина
Помог: 131 раз(а)
пока тестирую таблицу с новыми типами получилась такая проблемка
mysql> EXPLAIN SELECT AVG(position) position,DATE_FORMAT(created,"%Y.%m.%d %H:00") created FROM `apple_store_top` `t` WHERE (platform="1") AND (((appId=499200558) AND (country=6)) AND (category=4)) GROUP BY DATE_FORMAT(created,"%Y.%m.%d %H:00") ORDER BY created ASC;
короче куда копатЬ? (Добавление)
изза партицирования пришлось добавить дату в первичный ключ, может проще сделать партицирования по id, потому как для меня не принципиально чтобы вся инфа хранилась сугубо помесячно
Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург
Помог: 618 раз(а)
Panoptik пишет:
может проще сделать партицирования по id, потому как для меня не принципиально чтобы вся инфа хранилась сугубо помесячно
Самая полезная фишка партицирования - partition pruning, когда СУБД вообще не смотрит в разделы, в которых подходящих данных не может быть.
Если резать по id - то заранее обычно неизвестно, каким он может быть, чтобы указать условие в where.
? Сколько времени и строк?
explain сам по себе неплохой.
const,const,const - это значит, что поиск по ключу производится по константному значению.
А вот key_len маленький, используется не весь составной индекс.
-----
Васин друган
Отправлено: 22 Октября, 2014 - 16:49:58
Новичок
Покинул форум
Сообщений всего: 8
Дата рег-ции: Окт. 2014
Помог: 0 раз(а)
прошу прощения, что встреваю. предыдущие подсказки (Мелкий) абсолютно верны. судя по условиям, основная нагрузка идет по полю криетед. проиндексировав его Вы должны почувствовать разницу. и кантри я бы убрал в отдельную таблицу и вязал бы через айдишники, исключив строковую обработку полей. country in ['aa','bb','ss'] - не хорошо. другое дело насколько проблематично будет перелопатить коды приложения, если они уже привязаны к структуре имеющейся.
и третье, периодическое развертывание бэкапа - тоже будет полезно, файлы бд будут более монолитнее.
что касается долгого построения индекса, то оно вполне логично при таких объемах. если транзакционный механизм предполагает короткие инсерты и апдейты, то ничего страшного происходить не будет. если же идут массовые апдейты, то лучше предварительно отключать все индексы, а после перестроить их заново.
что касается ресурсов сервера бд, память все же там не настолько критична как скорость дисков. на своей шкуре почувствовал (оракл порядка 300 млн записей).
а вообще, самый лучший выход - отослать запрос сисадминам, и пусть у них голова болит.
Все гости форума могут просматривать этот раздел. Только зарегистрированные пользователи могут создавать новые темы в этом разделе. Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.
Главная
Помощь
Поиск
Пользователи
Форумы портала PHP.SU » PHP » Программирование на PHP » Работа с СУБД (Модераторы: OrmaJever, Саныч)
Без описания
