Дано: локальный MySQL сервер, таблица INNODB с уникальным индексом по полю Code_ varchar(45). Запрос с параметром вида Select Id from MyTable where Code_ = :Code_, выполняющийся с периодичностью от полминуты. Клиент на Delphi2007 + MyDAC 7.1.4. Пока база жила на MySQL 5.1, запрос стабильно выполнялся за ~0,3с первый раз после старта MySQL, и менее 0,01с (т.е. практически мгновенно) все последующие, независимо от паузы между запросами. На 5.5 ситуация изменилась не в лучшую сторону: долгие запросы проскакивают уже после паузы в несколько минут. Очень сильно подозреваю, что связано это именно с кэшированием. Что на 5.1, что на 5.5 все настройки изначально стояли по умолчанию, запрос SHOW VARIABLES LIKE "query_cache_size" в обоих случаях возвращает 0, но поскольку на 5.1 проблем не было, особо не заморачивался этим вопросом. На 5.5 попробовал изменить в my.ini параметр query_cache_size на 128... после перезапуска сервера запрос по-прежнему возвращает 0.
Вопросов собственно два:
1. C чем связано разное поведение одного и того же запроса на разных версиях сервера, и в кэшировании ли здесь дело?
2. Как все-таки включить кэш запросов?

miksoftA-MaRЧто на 5.1, что на 5.5 все настройки изначально стояли по умолчаниюНу а чего вы тогда хотите?

Как переезжали на новую версию?
Как часто модифицируется таблица MyTable?
Покажите DDL таблицы MyTable и план запроса.
1. Выгнал базу в скрипт mysqldump-ом на 5.1, развернул из скрипта на 5.5
2. В течении рабочего дня таблица не модифицируется.
3.
CREATE TABLE cards (
CardId bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
CardCode varchar(45) DEFAULT NULL,
CardSource set ('A', 'F') DEFAULT '',
PRIMARY KEY (CardId),
UNIQUE INDEX UK_cards_CardCode (CardCode)
)
ENGINE = INNODB
AUTO_INCREMENT = 471795
AVG_ROW_LENGTH = 5461
CHARACTER SET cp1251
COLLATE cp1251_general_ci;

Не совсем представляю, как показать план в читабельном виде, пусть будет так:

miksoftA-MaR,

innodb_buffer_pool_size чему равен?
8M, это на 5.5. Посмотрел инишник от 5.1 - там 138. Интересно...

miksoft,

Памяти на компе 512. Попробовал выставить innodb_buffer_pool_size согласно рекомендациям 256М (50% от оперативки), заодно изменил innodb_log_file_size на 70. Вообще отказался запускаться, ошибка 1067. Начал уменьшать постепенно оба - бесполезно. Запустить удалось только вернувшись к исходным 8 и 10. Последние значения, которые пробовал перед этим - 100 и 50. Очень странно.

Upd: Причина ошибки 1067 таки в innodb_log_file_size, больше 10 ни в какую не дает ставить. Первый параметр оставил 256, посмотрим, как завтра в работе себя покажет.
Спасибо за подсказку.

miksoftA-MaR,

Если у вас в базе нет активной модификации таблиц, то innodb_log_file_size вообще можно было по дефолту оставить (5 Мбайт).
Модифицируется в базе от силы таблиц 10, но активно. И конкретно на этом рабочем месте вставка и редактирование более медленные с самого начала, даже когда база была почти пустая: в отдельные таблицы больше половины модификаций занимают около 0,2с, тогда как на соседнем компе (там innodb_log_file_size=24, структура базы абсолютно такая же, но объем больше раз в 10) менее 0,01с. Так что innodb_log_file_size как раз хотелось бы увеличить.

miksoft,
Полдня отработали, полет нормальный, во всяком случае тормоза на чтении ушли и стало также, как было на 5.1. Да, innodb_buffer_pool_size немного уменьшил до 160 (навскидку).

javajdbc https://mysqltuner.codeplex.com/
Спасибо, качнул, полезная штука. Почему-то показал, что все таблицы фрагментированы, хотя база только 2 дня назад была развернута из скрипта, и часть таблиц не модифицируется в принципе.

miksoftA-MaR,
Покажите что он показал, даже интересно.
Это с того POS-a, на котором я боролся с тормозами (попутал немного, MySQL действительно 5.6.3, переставил в ходе экспериметнов перед тем как написать первый пост в этой теме):
Assuming 512 MB of physical memory
Assuming the same amount of swap space as this computer
Currently running supported MySQL version 5.6.3-m6
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Not Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 11M (Tables: 112)
Total fragmented tables: 112
All database users have passwords assigned
Up for: 6h 42m 22s (33K q [1,000 qps], 2K conn, TX: 18M, RX: 5M)
Reads / Writes: 89% / 11%
Total buffers: 176,0M global + 852,0K per thread (100 max threads)
Maximum possible memory usage: 259,2M (50% of installed RAM)
Slow queries: 0% (0/33K)
System.FormatException: Input string was not in a correct format.
at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
at MySqlTuner.TuningCalculator.MySqlStats() in c:\Documents and Settings\Administrator\My Documents\Source\mysqltuner\MySQLTuner\TuningCalculator.cs:line 691
at MySqlTuner.TuningCalculator.Calculate(MySqlServer server) in c:\Documents and Settings\Administrator\My Documents\Source\mysqltuner\MySQLTuner\TuningCalculator.cs:line 154


Это с наиболее загруженного, на котором видимых проблем нет:
Assuming 1024 MB of physical memory
Assuming the same amount of swap space as this computer
Currently running supported MySQL version 5.1.53-community
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Not Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 205M (Tables: 112)
Total fragmented tables: 112
All database users have passwords assigned
Up for: 14h 38m 20s (47K q [0,000 qps], 8K conn, TX: 222M, RX: 5M)
Reads / Writes: 84% / 16%
Total buffers: 204,0M global + 896,0K per thread (100 max threads)
Maximum possible memory usage: 291,5M (28% of installed RAM)
Slow queries: 1% (1/47K)
System.FormatException: Input string was not in a correct format.
at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
at MySqlTuner.TuningCalculator.MySqlStats() in c:\Documents and Settings\Administrator\My Documents\Source\mysqltuner\MySQLTuner\TuningCalculator.cs:line 691
at MySqlTuner.TuningCalculator.Calculate(MySqlServer server) in c:\Documents and Settings\Administrator\My Documents\Source\mysqltuner\MySQLTuner\TuningCalculator.cs:line 154

miksoftA-MaRпропущено...
Откуда так много коннектов на POS-терминале?
2К - это надеюсь общее число коннектов с момента старта, а не активных в данный момент? Тогда все логично: параллельно с основным приложением работает еще сервис, который выполняет обмен данными с учетной системой и отключается от базы после каждого сеанса.