Mysql Тормозит сервер при UTF8 кодировке

Олег_78

Новичок
Здравствуйте!

Не уверен что в тот раздел написал. Если что переместите плз.

Есть Fedora 15 64бит. На ней mysql 5.5.23, PHP 5.3.13,апач 2.2.22. Недавно появилась необходимость использовать русские тексты в базе и редактировать их через вэб. Для этого в my.cnf добавил:
Код:
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
Все работает.
Но!!! Другая вэбморда для отображения статистики (Asternic Call Center Stats Lite) начала жутко тормозить после этого. Раньше страница выводилась за 1 секунду, сейчас за 15-20 сек. Как только убираю любую из этих 5ти строчек из конфига, статистика начинает летать, а в таблице с кириллицей - крякозябры.
Добавил лог медленных запросов. Туда попадают запросы из статистики типа:
Код:
# Query_time: 3.095832  Lock_time: 0.000088 Rows_sent: 391  Rows_examined: 387045
SET timestamp=1520273146;
SELECT  ac.event AS action,  qs.info1 AS info1,  qs.info2 AS info2,  qs.info3 AS info3 FROM  queue_stats AS qs, qevent AS ac, qname As q, qagent as ag WHERE qs.qevent = ac.event_id AND qs.qname = q.qname_id AND qs.datetime >= '2018-03-05 00:00:00' AND qs.datetime <= '2018-03-05 23:59:00' AND  q.queue IN ('911','bonus','magazin')  AND ag.agent in ('SIP/1201','SIP/1202','SIP/1203','SIP/1204','SIP/1205','SIP/1206','SIP/1207','SIP/1208','SIP/1209','SIP/1210','SIP/1211','SIP/1212','SIP/1213','SIP/1214','SIP/1215','SIP/1216','SIP/1217','SIP/1218','SIP/1219','SIP/1220','SIP/1221','SIP/1222','SIP/1224') AND  ac.event IN ('ABANDON', 'EXITWITHTIMEOUT', 'TRANSFER') ORDER BY  ac.event,  qs.info3;
# Time: 180305 20:05:48
# User@Host: root[root] @ localhost []
# Query_time: 2.828279  Lock_time: 0.000093 Rows_sent: 2182  Rows_examined: 5916876
SET timestamp=1520273148;
SELECT qs.datetime AS datetime, q.queue AS qname, ag.agent AS qagent, ac.event AS qevent, qs.info1 AS info1, qs.info2 AS info2, qs.info3 AS info3  FROM queue_stats AS qs, qname AS q, qagent AS ag, qevent AS ac WHERE qs.qname = q.qname_id AND qs.qagent = ag.agent_id AND qs.qevent = ac.event_id AND qs.datetime >= '2018-03-05 00:00:00' AND qs.datetime <= '2018-03-05 23:59:00' AND q.queue IN ('911','bonus','magazin') AND ag.agent in ('SIP/1201','SIP/1202','SIP/1203','SIP/1204','SIP/1205','SIP/1206','SIP/1207','SIP/1208','SIP/1209','SIP/1210','SIP/1211','SIP/1212','SIP/1213','SIP/1214','SIP/1215','SIP/1216','SIP/1217','SIP/1218','SIP/1219','SIP/1220','SIP/1221','SIP/1222','SIP/1224') AND ac.event IN ('COMPLETECALLER', 'COMPLETEAGENT') ORDER BY ag.agent;
Но когда я копирую любой из этих запросов в консоль мускула то он исполняется моментально.
В логах апача нет ошибок на эту тему. Есть только типа
Код:
PHP Notice:  Undefined index: 21 in /var/www/html/qstat/distribution.php on line 110,
но они всегда были и до того.
Аналогичный сервер на дибиане 7, с чуть более новыми версиями мускула пхп и апача работает с теми же настройками my.cnf в плане кодировки, прекрасно. Статистика летает.
Уже несколько дней гуглю. Как найти почему оно тормозит? Помогите плз.
 

WMix

герр M:)ller
Партнер клуба
Даже на микросайтах советую использовать индексы. На самом деле ничего не понятно но врятли в utf8 замедляет в 15 раз
 

Олег_78

Новичок
Вот describe таблиц которые здесь используются:
mysql> describe qagent;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| agent_id | int(6) | NO | PRI | NULL | auto_increment |
| agent | varchar(40) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe qevent;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| event_id | int(2) | NO | PRI | 0 | |
| event | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe qname;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| qname_id | int(6) | NO | PRI | NULL | auto_increment |
| queue | varchar(40) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe queue_stats;
+----------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------------------+----------------+
| queue_stats_id | int(12) | NO | PRI | NULL | auto_increment |
| uniqueid | varchar(40) | YES | | NULL | |
| datetime | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| qname | int(6) | YES | | NULL | |
| qagent | int(6) | YES | | NULL | |
| qevent | int(2) | YES | | NULL | |
| info1 | varchar(40) | YES | | NULL | |
| info2 | varchar(40) | YES | | NULL | |
| info3 | varchar(40) | YES | | NULL | |
| number | varchar(40) | YES | | NULL | |
+----------------+-------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)
Да, точно, если 'но кеш' то тогда и в консоли тормозит такое же время.
Вот EXPLAIN с UTF8:
Код:
mysql> explain SELECT qs.datetime AS datetime, q.queue AS qname, ag.agent AS qagent, ac.event AS qevent, qs.info1 AS info1, qs.info2 AS info2, qs.info3 AS info3  FROM queue_stats AS qs, qname AS q, qagent AS ag, qevent AS ac WHERE qs.qname = q.qname_id AND qs.qagent = ag.agent_id AND qs.qevent = ac.event_id AND qs.datetime >= '2018-03-05 00:00:00' AND qs.datetime <= '2018-03-05 23:59:00' AND q.queue IN ('bonus','magazin','911') AND ag.agent in ('SIP/1201','SIP/1202','SIP/1203','SIP/1204','SIP/1205','SIP/1206','SIP/1207','SIP/1208','SIP/1209','SIP/1210','SIP/1211','SIP/1212','SIP/1213','SIP/1214','SIP/1215','SIP/1216','SIP/1217','SIP/1218','SIP/1219','SIP/1220','SIP/1221','SIP/1224') AND ac.event IN ('COMPLETECALLER', 'COMPLETEAGENT') ORDER BY ag.agent;
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | qs    | ALL    | NULL          | NULL    | NULL    | NULL             | 5908436 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ac    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qevent |       1 | Using where                                  |
|  1 | SIMPLE      | ag    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qagent |       1 | Using where                                  |
|  1 | SIMPLE      | q     | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qname  |       1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
4 rows in set (0.00 sec)
А вот без:
Код:
mysql> explain SELECT SQL_NO_CACHE qs.datetime AS datetime, q.queue AS qname, ag.agent AS qagent, ac.event AS qevent, qs.info1 AS info1, qs.info2 AS info2, qs.info3 AS info3  FROM queue_stats AS qs, qname AS q, qagent AS ag, qevent AS ac WHERE qs.qname = q.qname_id AND qs.qagent = ag.agent_id AND qs.qevent = ac.event_id AND qs.datetime >= '2018-03-05 00:00:00' AND qs.datetime <= '2018-03-05 23:59:00' AND q.queue IN ('bonus','magazin','911') AND ag.agent in ('SIP/1201','SIP/1202','SIP/1203','SIP/1204','SIP/1205','SIP/1206','SIP/1207','SIP/1208','SIP/1209','SIP/1210','SIP/1211','SIP/1212','SIP/1213','SIP/1214','SIP/1215','SIP/1216','SIP/1217','SIP/1218','SIP/1219','SIP/1220','SIP/1221','SIP/1224') AND ac.event IN ('COMPLETECALLER', 'COMPLETEAGENT') ORDER BY ag.agent;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
|  1 | SIMPLE      | qs    | range  | unico         | unico   | 8       | NULL             | 8247 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ac    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qevent |    1 | Using where                                  |
|  1 | SIMPLE      | ag    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qagent |    1 | Using where                                  |
|  1 | SIMPLE      | q     | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qname  |    1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
4 rows in set (0.01 sec)
Я так понимаю почему-то не работают ключи в таблице queue_stats... И что с этим делать?

Да и вот еще:
Код:
mysql> show create table queue_stats;

| queue_stats | CREATE TABLE `queue_stats` (
  `queue_stats_id` int(12) NOT NULL AUTO_INCREMENT,
  `uniqueid` varchar(40) DEFAULT NULL,
  `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `qname` int(6) DEFAULT NULL,
  `qagent` int(6) DEFAULT NULL,
  `qevent` int(2) DEFAULT NULL,
  `info1` varchar(40) DEFAULT NULL,
  `info2` varchar(40) DEFAULT NULL,
  `info3` varchar(40) DEFAULT NULL,
  `number` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`queue_stats_id`),
  UNIQUE KEY `unico` (`datetime`,`qname`,`qagent`,`qevent`)
) ENGINE=MyISAM AUTO_INCREMENT=31826095 DEFAULT CHARSET=latin1 |
 

WMix

герр M:)ller
Партнер клуба
для начала, пользуйся join'ом
Код:
SELECT
  qs.datetime AS datetime,
  q.queue AS qname,
  ag.agent AS qagent,
  ac.event AS qevent,
  qs.info1 AS info1,
  qs.info2 AS info2,
  qs.info3 AS info3
 
FROM queue_stats AS qs
join qname AS q ON qs.qname = q.qname_id
join qagent AS ag ON qs.qagent = ag.agent_id
join qevent AS ac ON qs.qevent = ac.event_id
  
WHERE
      qs.datetime >= '2018-03-05 00:00:00' AND
      qs.datetime <= '2018-03-05 23:59:00' AND
      q.queue IN ('bonus','magazin','911') AND ag.agent in ('SIP/1201','SIP/1202','SIP/1203','SIP/1204','SIP/1205','SIP/1206','SIP/1207','SIP/1208','SIP/1209','SIP/1210','SIP/1211','SIP/1212','SIP/1213','SIP/1214','SIP/1215','SIP/1216','SIP/1217','SIP/1218','SIP/1219','SIP/1220','SIP/1221','SIP/1224') AND ac.event IN ('COMPLETECALLER', 'COMPLETEAGENT') ORDER BY ag.agent;
Using where; Using temporary; Using filesort
и точно индексов не хватает
 

Олег_78

Новичок
Спасибо за запрос. Попробую поменять. Но эта статистика написана не мной. Она написана asternic.net и у меня почти без изменений. Вопрос же в том как кодировка влияет на индекс? Он же есть (индекс), но при ютф8 не работает. Может кодировку таблицы тоже поменять на ютф? Может индексы перестроить?
 

Фанат

oncle terrible
Команда форума
вообще фантастика. кодировка каким-то загадочным образом влияет на индекс в котором только дата и целые числа

но чисто в плане шаманства я бы индекс перестроил.
 

fixxxer

К.О.
Партнер клуба
Using where; Using temporary; Using filesort
Могу предположить, что с однобайтной кодировкой впритык помещалось в sort buffers, а с многобайтной уже не влезает, и сортировка идет через файл (using filesort - это такая вводящая в заблуждение формулировка, это далеко не всегда файл). Отсюда могут быть и последствия по построенному плану.

А что будет, если убрать ORDER BY ag.agent?

Еще, конечно, меня смущает DEFAULT CHARSET=latin1 (причем без указания utf8 в кодировках столбцов) в комбинации с set names utf8. Не то, чтобы это могло влиять (хотя фиг его знает), но просто это жесть какая-то же.
 
Последнее редактирование:

fixxxer

К.О.
Партнер клуба
Но possible_keys=NULL это вообще ненормальное что-то.
Ой, это же MyISAM. Хрен его знает, что в нем развалиться могло, с этим убожеством можно хоть о фазе луны догадки делать. Сделай фикс utf8 в таблице через дамп-рестор. А лучше вообще перелезть на InnoDB, если нет особых причин использовать MyISAM.
 

Олег_78

Новичок
А что будет, если убрать ORDER BY ag.agent?
Так же тормозит. Вот EXPLAIN:
Код:
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+-------------+
|  1 | SIMPLE      | qs    | ALL    | NULL          | NULL    | NULL    | NULL             | 5911290 | Using where |
|  1 | SIMPLE      | ac    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qevent |       1 | Using where |
|  1 | SIMPLE      | ag    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qagent |       1 | Using where |
|  1 | SIMPLE      | q     | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qname  |       1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+-------------+
Буду пробовать перезаливать...
 

fixxxer

К.О.
Партнер клуба
а, ну да, это ж myisam, там нет неявного индекса по rowId = pk. Но все равно, при таком where possible_keys NULL это какая-то фигня.

Хотя я не исключаю, что забыл, как myisam работает, я это трогал в последний раз больше 10 лет назад.
 

Олег_78

Новичок
В общем перезалил я базу через дамп полностью. Перед заливкой удалил в дампе из таблиц кодировку (как и в инсталляционном скрипте). После заливки таблица выглядит так:
Код:
 CREATE TABLE `queue_stats` (
  `queue_stats_id` int(12) NOT NULL AUTO_INCREMENT,
  `uniqueid` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `qname` int(6) DEFAULT NULL,
  `qagent` int(6) DEFAULT NULL,
  `qevent` int(2) DEFAULT NULL,
  `info1` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `info2` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `info3` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`queue_stats_id`),
  UNIQUE KEY `unico` (`datetime`,`qname`,`qagent`,`qevent`)
) ENGINE=MyISAM AUTO_INCREMENT=31828433 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Так же она, кстати, выглядит на дебиане 7, где все работает нормально. Тут на федоре версия мускула 5.5.23, а на дебиане - 5.5.58.
Проблема осталась.
Опять дропнул базу. В дампе везде заменил MyISAM на InnoDB. Залил. Проблема опять осталась!!!
Код:
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | qs    | ALL    | NULL          | NULL    | NULL    | NULL             | 5850637 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ac    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qevent |       1 | Using where                                  |
|  1 | SIMPLE      | ag    | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qagent |       1 | Using where                                  |
|  1 | SIMPLE      | q     | eq_ref | PRIMARY       | PRIMARY | 4       | qstats.qs.qname  |       1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
Убрал строчки для ютф8 из конфига - индексы на ИнноДБ заработали. Чудеса... не знаю что думать. Наверное нужно версию мускула поновее ставить, но тут на федоре она последняя из пакетов. Буду пробовать компилить...
 

Олег_78

Новичок
Код:
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_unicode_ci |
| collation_database       | utf8_unicode_ci |
| collation_server         | utf8_unicode_ci |
+--------------------------+-----------------+
10 rows in set (0.00 sec)
Убери эту помойку.
Убрал. Да, без этих строк также нет индекса и так же тормозит. Теперь:
Код:
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | utf8              |
| character_set_system     | utf8              |
| collation_connection     | utf8_unicode_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | utf8_unicode_ci   |
+--------------------------+-------------------+
10 rows in set (0.00 sec)
Если вообще без строчек с ютф8 то:
Код:
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';                                                                +--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+
10 rows in set (0.00 sec)
 

Фанат

oncle terrible
Команда форума
Вообще, вся эта джигурда имеет чисто рекомендательный характер и ни на что не влияет, если таблица у тебя уже создана.
это просто набор дефолтных значений на случай если ты не укажешь кодировку явно.

соотвтственно, если у тебя при создании таблицы написано DEFAULT CHARSET=latin1, то все эти переменные не имеют ни малейшего влияния
 

AnrDaemon

Продвинутый новичок
SET NAMES вообще не должно выполняться, кодировка должна указываться в параметрах подключения.
В PHP начиная с 5.3.6 с этим проблем быть не должно.
 
Сверху