Помогите определиться с настройками

Есть машина (PIV-3Ггц, 1Гб ОЗУ), с установленным сервером 5.0.37. Есть 3 таблицы на 90 000, 1 500 000 и 1 500 000 записей. Таблицы приблизительно след. вида:таблица А (90000){ mediumint WordID; // primary varchar Sourceword;}таблица B(1500000){ mediumint GroupID; // primary varchar Keyword;}таблица C(1500000){ mediumint WordID; // primary mediumint GroupID; // primary}В процессе работы выяснилось, что скорость обработки запросов достаточно низкая(1200 селектов за 5 минут). Возникают вопросы: такая скорость и должна быть? Где может быть узкое место? Какой тип storage engine выбрать? Какие настройки сервера установить для продуктивной работы?зы. Если что-то забыл уточнить - спрашивайте.зыы. Идут только селекты, т.е. надо настроить на максимальную производительность по выборке данных. Причем селекты идут последовательно: select * from a where sourceword='blablabla', затем из результата извлекаем WordID и следующий селект: select *from c where wordid=1212. Ну если потребуется, то и ещё один селект: select *from b where groupid=2121
24 ответа

кстати, я не понял, как у вас получилось сделать таблицы MyISAM с типом индексов HASHчитаю сейчас справку MySQL 5.0 и вижу:Storage Engine Allowable Index TypesMyISAM BTREEInnoDB BTREEMEMORY/HEAP HASH, BTREE
Честно говоря, сам не знаю. Сам смотрел справку, так там сказано именно то, что вы процитировали. Но пробовал менять тип. Смотрю через Администратора - он пишет что стоит btree, а через show create table показывает именно то, что я выложил. ( Вы же не думаете, что я сам везде btree поменял на hash ).


еще, хотя это вряд ли как-то влияет на вашу проблему, заметил, что в таблицах с максимальной длиной строки 64+2 зачем-то прописано AVG_ROW_LENGTH=260


возможно проблема не в настройках сервера, а в криво написанном запросе или отсутствии индекса (по sourceword уже надо создать, раз вы выбираете данные по условию на этот столбец)
Исправлено.
еще, хотя это вряд ли как-то влияет на вашу проблему, заметил, что в таблицах с максимальной длиной строки 64+2 зачем-то прописано AVG_ROW_LENGTH=260
Исправлено.'psbinf', 'CREATE TABLE `psbinf` ( `GroupID` mediumint(8) unsigned NOT NULL auto_increment, `Keyword` varchar(64) NOT NULL, PRIMARY KEY USING HASH (`GroupID`)) ENGINE=MyISAM AUTO_INCREMENT=86840 DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=32 ROW_FORMAT=DYNAMIC''pseudobase', 'CREATE TABLE `pseudobase` ( `WordID` mediumint(8) unsigned NOT NULL auto_increment, `SourceWord` varchar(64) NOT NULL, `IsProcessed` *******(1) unsigned NOT NULL default '0', PRIMARY KEY USING HASH (`WordID`,`SourceWord`)) ENGINE=MyISAM AUTO_INCREMENT=1610184 DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=32 ROW_FORMAT=DYNAMIC'А что насчет джоина? Стоит ли переписывать запросы? Я вроде ещё видел, что можно блокировать таблицу для увеличении скорость. Так ли это?зы. Есть ещё идея создавать динамически временные таблицы на время работы программы(копии приведенных выше), только со storage engine=MEMORY. Ускорит ли это выборку данных?


зы. Есть ещё идея создавать динамически временные таблицы на время работы программы(копии приведенных выше), только со storage engine=MEMORY. Ускорит ли это выборку данных?
ускорит, только на мой взгляд это уже следующий этапсначала нужно сделать то, что я написал: по логам посмотреть, какие запросы выполняются, отдельно замерить скорость запроса каждого типа, если какие-то работают слишком медленно, то разбираться почемупотому что на глаз на таком железе запросы типа
select * from c where wordid=<b>1212</b>
должны выполняться не за 0.2 секунды (1200 запросов за 5 минут), а раз в 20 быстрее (если конечно правильно созданы индексы и т.п.)сначала разберитесь, почему тормозят текущие запросы, а потом уже занимайтесь дальнейшей оптимизацией


explain SELECT * FROM pseudobase WHERE sourceword = 'космический'
"id"
1 row fetched in 0,0110s (0,0010s)explain SELECT * FROM wordidgroupid WHERE wordidgroupid.WordID = 102339
"id"
1 row fetched in 0,0266s (0,0021s)explain SELECT * FROM base.b13 WHERE GroupID = 7164
"id"
1 row fetched in 0,0152s (0,0029s)explain SELECT * FROM psbinf WHERE psbinf.GroupID = 417
"id"
1 row fetched in 0,0161s (0,0023s)explain SELECT * FROM fullbase WHERE fullbase.GroupID = 83628
"id"
1 row fetched in 0,0165s (0,0024s)Помогите, пожалуйста, проанализировать данные :)зы. Ещё было замечено, что если повторно запустить программу(не выходя из неё) - процесс завершается за 1 секунду :) Я так подозреваю, что раз у меня query_cache_size = 32М, то просто все данные хранятся в памяти. Возникает вопрос: нельзя ли сразу все таблицы держать в памяти и как запретить серверу использовать своп?


SELECT * FROM pseudobase WHERE sourceword = 'космический'индекс не используется, судя по explain его вообще нет (вы точно его создали?)SELECT * FROM wordidgroupid WHERE wordidgroupid.WordID = 102339индекс не используется, просматриваются все 1,6 миллиона записейу вас создан для таблицы wordidgroupid составной индекс PRIMARY KEY (`GroupID`,`WordID`)MySQL не может его использовать для выборки по условию WordID = 102339нужно создать либо отдельный индекс по WordID (попробуйте сначала это), либо изменить порядок полей в составном индексе, чтобы WordID был первым, т.е. PRIMARY KEY (`WordID`,`GroupID`) (это я точно не помню, будет ли использоваться такой индекс, если выборка идет только по WordID, но вроде да)в буфере с размером query_cache_size хранятся не данные, а результаты запросовт.е., если вы сделали запрос SELECT * FROM wordidgroupid WHERE wordidgroupid.WordID = 102339, и он влезает в буфер, он запишется туда, и второй такой запрос отрабатывать не будет, MySQL выдаст готовый результат, но как только вы сделаете такой же запрос, но с другим WordID, он снова будет искать данные в таблицекак хранить именно данные по возможности целиком в памяти ищите по форуму (но опять же ПОСЛЕ решения проблемы с индексами)


SELECT * FROM pseudobase WHERE sourceword = 'космический'индекс не используется, судя по explain его вообще нет (вы точно его создали?)
Исправил.'pseudobase', 'CREATE TABLE `pseudobase` ( `WordID` mediumint(8) unsigned NOT NULL auto_increment, `SourceWord` varchar(64) NOT NULL, `IsProcessed` *******(1) unsigned NOT NULL default '0', PRIMARY KEY USING HASH (`WordID`), KEY `index_2` USING HASH (`SourceWord`)) ENGINE=MyISAM AUTO_INCREMENT=1610184 DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=32 ROW_FORMAT=DYNAMIC'explain SELECT * FROM pseudobase WHERE sourceword = 'космический'
"id"
SELECT * FROM wordidgroupid WHERE wordidgroupid.WordID = 102339индекс не используется, просматриваются все 1,6 миллиона записейу вас создан для таблицы wordidgroupid составной индекс PRIMARY KEY (`GroupID`,`WordID`)MySQL не может его использовать для выборки по условию WordID = 102339нужно создать либо отдельный индекс по WordID (попробуйте сначала это), либо изменить порядок полей в составном индексе, чтобы WordID был первым, т.е. PRIMARY KEY (`WordID`,`GroupID`) (это я точно не помню, будет ли использоваться такой индекс, если выборка идет только по WordID, но вроде да)
Исправил.'wordidgroupid', 'CREATE TABLE `wordidgroupid` ( `WordID` mediumint(8) unsigned NOT NULL, `GroupID` mediumint(8) unsigned NOT NULL, PRIMARY KEY USING HASH (`WordID`), KEY `index2` USING HASH (`GroupID`)) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED'explain SELECT * FROM wordidgroupid WHERE wordidgroupid.WordID = 102339
"id"
Большое спасибо за советы. Время работы снизолось до 11 секунд, что уже БОЛЕЕ приемлемо(хотя нет предела совершенству :) )


Время работы снизолось до 11 секунд
это с 5ти минут снизилось? неплоходалее, если скорость работы при повторном запуске уменьшается, и вам кажется, что это связано с тем, что таблицы при первом запуске просто долго считываются с диска, то читаем про CACHE INDEX, PRELOAD INDEX, можно топик посмотретьхотя я бы сначала еще через JOIN переписал запросыи еще мне не нравится:
CREATE TABLE `wordidgroupid` (
`WordID` mediumint(<b>8</b>) unsigned NOT NULL,
`GroupID` mediumint(<b>8</b>) unsigned NOT NULL,
PRIMARY KEY USING HASH (`WordID`),
KEY `index2` USING HASH (`GroupID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED
у вас был составной первичный ключ по полям GroupID, WordIDвы его удалили и сделали первичным ключом поле WordID, это означает, что в этой таблице у вас может быть только одна запись с заданным WordID, что, как я предполагаю из структуры всех таблиц, неверно


далее, если скорость работы при повторном запуске уменьшается, и вам кажется, что это связано с тем, что таблицы при первом запуске просто долго считываются с диска, то читаем про CACHE INDEX, PRELOAD INDEX, можно топик посмотретьхотя я бы сначала еще через JOIN переписал запросы
Спасибо за ссылку. С JOIN'ов и начну. :)
и еще мне не нравится:
CREATE TABLE `wordidgroupid` (
`WordID` mediumint(<b>8</b>) unsigned NOT NULL,
`GroupID` mediumint(<b>8</b>) unsigned NOT NULL,
PRIMARY KEY USING HASH (`WordID`),
KEY `index2` USING HASH (`GroupID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED
у вас был составной первичный ключ по полям GroupID, WordIDвы его удалили и сделали первичным ключом поле WordID, это означает, что в этой таблице у вас может быть только одна запись с заданным WordID, что, как я предполагаю из структуры всех таблиц, неверно
А откуда такие предположения? В этой таблице, по идее, должна быть только одна запись с заданным WordID. Есть какие-то неоднозначности?


А откуда такие предположения? В этой таблице, по идее, должна быть только одна запись с заданным WordID. Есть какие-то неоднозначности?
оттуда, что структура таблиц word, group и wordgroup выбирается, как правило, если между сущностями "слово" и "группа" связь типа многие-ко-многим (т.е. одно слово может быть в нескольких группах, и в одной группе может быть несколько слов), тогда все эти связи можно прописать в отдельной таблице wordgroupесли же для каждого wordid у вас однозначно определяется groupid, то зачем отдельная таблица?достаточно добавить поле groupid в таблицу pseudobase


оттуда, что структура таблиц word, group и wordgroup выбирается, как правило, если между сущностями "слово" и "группа" связь типа многие-ко-многим (т.е. одно слово может быть в нескольких группах, и в одной группе может быть несколько слов), тогда все эти связи можно прописать в отдельной таблице wordgroupесли же для каждого wordid у вас однозначно определяется groupid, то зачем отдельная таблица?достаточно добавить поле groupid в таблицу pseudobase
Именно так. Есть слово, оно может быть в нескольких группах, в группе может быть несколько слов. Именно для этого и была создана таблица развязки: wordidgroupid.


WID GrID1 21 32 3обратите внимание на количество одинаковых записей по каждому полю.


WordID GroupID 1 2 1 3 2 3
предпросмотр рулит... )


WID GrID1 21 32 3обратите внимание на количество одинаковых записей по каждому полю.
Извините, но с моей точки зрения я ничего неординарного не нахожу. Может поподробнее объясните?


Извините, но с моей точки зрения я ничего неординарного не нахожу. Может поподробнее объясните?
тут две записи с ОДНИМ WordID = 1про это я и говорил: если по WordID сделать уникальный индекс (что вы и сделали), то вы не сможете добавить две такие записи


тут две записи с ОДНИМ WordID = 1про это я и говорил: если по WordID сделать уникальный индекс (что вы и сделали), то вы не сможете добавить две такие записи
Т.е. мне надо вернуться к составному первичному ключу, правильно я понимаю???


нужно создать либо отдельный индекс по WordID (попробуйте сначала это), либо изменить порядок полей в составном индексе, чтобы WordID был первым, т.е. PRIMARY KEY (`WordID`,`GroupID`) (это я точно не помню, будет ли использоваться такой индекс, если выборка идет только по WordID, но вроде да)


CREATE TABLE `wordidgroupid` (
 `WordID` mediumint(<b>8</b>) unsigned NOT NULL,
 `GroupID` mediumint(<b>8</b>) unsigned NOT NULL,
 KEY `index_1` USING HASH (`WordID`),
 KEY `index_2` USING HASH (`GroupID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED
Исправил. Теперь с точки здравого смысла всё верно?


Исправил. Теперь с точки здравого смысла всё верно?
с точки здравого смысла - неттеперь у вас нету первичного ключа для таблицы, т.е. не указано, какие поля однозначно определяют строку, и вы сможете добавить две абсолютно ожинаковые строкиесли вы уверены, что этого не случится и хотите сэкономить место на лишних индексах, то можно оставить и так - работать будет, но в дальнейшем может вызвать некоторые проблемы


CREATE TABLE `wordidgroupid` (
 `WordID` mediumint(<b>8</b>) unsigned NOT NULL,
 `GroupID` mediumint(<b>8</b>) unsigned NOT NULL,
 PRIMARY KEY (`WordID`,`SourceWord`)
 KEY `BySrc` (`SourceWord`),
 ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED
IMHO так. Можно юзинг хеш втыкнуть.


А почему б не джойн использовать вместо такой акробатики?


а по source word индекс есть? покажите лучше show create table таблиц, а не на пальцах их структуру1200 селектов это именно 1200 или еще на 3 надо умножить?


А почему б не джойн использовать вместо такой акробатики?
Наверное не совсем правильный подход, но это было сделано для того чтобы узнать на каком этапе провалился запрос. В какой таблице чего отсутствует. Вроде под этим подразумевается целостность. Может я излишне параноидален?
а по source word индекс есть? покажите лучше show create table таблиц, а не на пальцах их структуру1200 селектов это именно 1200 или еще на 3 надо умножить?
Спасибо за "show create table", а то я новичек и всего не знаю :)Нет, по sourceword индекса нет. 1200 селектов это исходя из логов, так что я думаю на 3 умножать не стоит. Был не до конца честен - есть ещё 2 таблицы.'psbinf', 'CREATE TABLE `psbinf` ( `GroupID` mediumint(8) unsigned NOT NULL auto_increment, `Keyword` varchar(64) NOT NULL, PRIMARY KEY USING HASH (`GroupID`)) ENGINE=MyISAM AUTO_INCREMENT=86840 DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=260 ROW_FORMAT=DYNAMIC''pseudobase', 'CREATE TABLE `pseudobase` ( `WordID` mediumint(8) unsigned NOT NULL auto_increment, `SourceWord` varchar(64) NOT NULL, `IsProcessed` *******(1) unsigned NOT NULL default '0', PRIMARY KEY USING HASH (`WordID`)) ENGINE=MyISAM AUTO_INCREMENT=1610184 DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=260 ROW_FORMAT=DYNAMIC''wordidgroupid', 'CREATE TABLE `wordidgroupid` ( `WordID` mediumint(8) unsigned NOT NULL, `GroupID` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`GroupID`,`WordID`)) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED''b13', 'CREATE TABLE `b13` ( `GroupID` mediumint(8) unsigned NOT NULL, `Count` mediumint(8) unsigned NOT NULL, PRIMARY KEY USING HASH(`GroupID`)) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED'fullbase', 'CREATE TABLE `fullbase` ( `GroupID` mediumint(8) unsigned NOT NULL default '0', `Count` mediumint(8) unsigned NOT NULL, PRIMARY KEY USING HASH (`GroupID`)) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED'Ну вот, в эти таблицы поступает 1200 селектов. И всё это минимум за 5 минут. Наверное надо упомянуть, что эти запросы поступают из моей программы. Для обмена данными с базой пользуюсь MySQL++.


если лог есть, берите его и смотрите, какой именно запрос сколько выполняется, смотрите explain select ...возможно проблема не в настройках сервера, а в криво написанном запросе или отсутствии индекса (по sourceword уже надо создать, раз вы выбираете данные по условию на этот столбец)кстати, я не понял, как у вас получилось сделать таблицы MyISAM с типом индексов HASHчитаю сейчас справку MySQL 5.0 и вижу:
Storage Engine	Allowable Index TypesMyISAM		BTREEInnoDB		BTREEMEMORY/HEAP 	HASH, BTREE