Перейти до

Оптимизация MYSQL


Рекомендованные сообщения

Извините, если в в той теме. Вопрос по оптимизации работы mysql. Есть таблица, в которой хранится netflow. Любой запрос выполняется 10 мин. Как диагностировать проблему?

Ссылка на сообщение
Поделиться на других сайтах
  • Відповіді 54
  • Створено
  • Остання відповідь

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Индексами должны быть поля используемые в where. У вас это unix_secs и srcaddr. Добавьте, это сразу даст многократный буст к скорости обработки.

1) Выбрать правильный тип таблицы. Тут звучал неправильный совет сконвертировать в иннодб. Иннодб - это транзакционный движок, это очень хорошо, но не в данном случае, поскольку этот функционал только

Смотрите http://dev.mysql.com/doc/refman/5.7/en/explain.html Также смотрите чтобы были индексы на поля по которым происходит выборка в запросе.

Да. Запускал, выполнил рекомендации. Все равно выполнение запроса = 10 минутам. При том, что select COUNT(*) from 'table_name'; Делается быстро.

Ссылка на сообщение
Поделиться на других сайтах

Да. Запускал, выполнил рекомендации. Все равно выполнение запроса = 10 минутам. При том, что select COUNT(*) from 'table_name'; Делается быстро.

Смотрите http://dev.mysql.com/doc/refman/5.7/en/explain.html

Также смотрите чтобы были индексы на поля по которым происходит выборка в запросе.

Ссылка на сообщение
Поделиться на других сайтах

mysql> EXPLAIN SELECT * FROM raw;
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
|  1 | SIMPLE      | raw   | ALL  | NULL          | NULL | NULL    | NULL | 820510737 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------+
1 row in set (0.01 sec)
 

Ссылка на сообщение
Поделиться на других сайтах

Вот структура:

mysql> show create table raw;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| raw   | CREATE TABLE `raw` (
  `unix_secs` int(11) unsigned NOT NULL DEFAULT '0',
  `unix_nsecs` int(11) unsigned NOT NULL DEFAULT '0',
  `sysuptime` int(11) unsigned NOT NULL DEFAULT '0',
  `exaddr` varchar(15) NOT NULL DEFAULT '0',
  `dpkts` int(11) unsigned NOT NULL DEFAULT '0',
  `doctets` int(11) unsigned NOT NULL DEFAULT '0',
  `srcaddr` varchar(15) NOT NULL DEFAULT '0',
  `dstaddr` varchar(15) NOT NULL DEFAULT '0',
  `srcport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dstport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `prot` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

Ссылка на сообщение
Поделиться на других сайтах

я конечно не силен, в БД, но мне кажется, что если вы проведете партиционирование таблицы, и перепишите запросы чтобы они использовали все прелести партиционирования, то будет значительно легче. использование innodb так же предпочтительнее, разработчики говорят что myisam безнадежно устарел и в след 7м релизе  будет выпилен. (по заявлениям некоторых людей innodb показывает более лучшие результаты и в olap oltp )

Ссылка на сообщение
Поделиться на других сайтах

Первым делом нужно поменять движок таблицы на InnoDB, и задать для mysql здоровенный innodb_pool.

Сейчас у вас запросы вероятно вообще не кешируются и каждый раз выполняются с вычиткой всей таблицы с диска.

Ссылка на сообщение
Поделиться на других сайтах

 

 

Вот структура

1) как уже сказали - InnoDB вместо окаменелости MyISAM

2) индексы где? без индексов - мускул тупо сравнивает каждую из 820 млн строк с критериями where...

Ссылка на сообщение
Поделиться на других сайтах

SELECT FROM_UNIXTIME(unix_secs), srcaddr, (SUM(doctets)/1024)/1024 as 'doctets' FROM raw WHERE srcaddr = '".$ip."' AND DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d') between '".$startdate."' AND '".$enddate."' GROUP BY DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d')

 

 

Вот весь запрос

Ссылка на сообщение
Поделиться на других сайтах

Индексами должны быть поля используемые в where. У вас это unix_secs и srcaddr.

Добавьте, это сразу даст многократный буст к скорости обработки.

Ссылка на сообщение
Поделиться на других сайтах
Опубліковано: (відредаговано)

CREATE INDEX ip ON raw(srcaddr);

 

и

 

CREATE INDEX date ON raw(unix_secs)

ТАК?

 

А запрос поменяется от этого?

Движок таблицы уже уже сменил

Відредаговано Golthana
Ссылка на сообщение
Поделиться на других сайтах

Запрос не поменяется, индексы лишь упрощают движку поиск в таблице.

Теоретически 1 индекс на базе 2ух полей в вашем случае(с миллионами записей) будет еще эффективнее, но тут жена-архитектор БД сказала "хз, нужно тестить" :)

 

Добавьте индексы, дайте базе большой кэш innodb и будет все космически.

Ссылка на сообщение
Поделиться на других сайтах

 

 

DATE_FORMAT(FROM_UNIXTIME(unix_secs), '%Y-%m-%d') between '".$startdate."' AND '".$enddate."

эта херня принципиально неиндексируемая. уж не в абиллсе ли такой бред подсмотрели?

 

делайте нормальный запрос (да-да, startdate и enddate в unixtime, а не вырывание гланд через анус) и делайте индекс по полям, используемым в выборке.

Ссылка на сообщение
Поделиться на других сайтах

 

 

делайте нормальный запрос

Например?

 

 

 

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

 

Нет. В какой-то статье прочитал. Поскольку в mysql несилен, то не могу оценить правильность введенного запроса

Ссылка на сообщение
Поделиться на других сайтах

Индексы, движки - это конечно все возможно и даст небольшой прирост.

 

Судя по самому запросу и размеру БД, проблема в том, что создается временная таблица на диске (диски наверное не ssd?), причем не маленькая.

 

Посмотрите размер временной таблицы которая создается при выполнении запроса, возможно удастся засунуть её в tmpfs. 

Ссылка на сообщение
Поделиться на других сайтах

 

 

диски наверное не ssd?)

Нет

 

 

Посмотрите размер временной таблицы которая создается при выполнении запроса, возможно удастся засунуть её в tmpfs.

А ссылку на документацию по этому поводу можно? Или в двух словах?

Ссылка на сообщение
Поделиться на других сайтах
Опубліковано: (відредаговано)

Создаю два индекса. Процесс задумался, видимо время надо) Потом сравню результат.

 

SELECT FROM_UNIXTIME(unix_secs), srcaddr, (SUM(doctets)/1024)/1024 as 'doctets' FROM raw WHERE srcaddr = '".$ip."' AND FROM_UNIXTIME(unix_secs) between '".$startdate."' AND '".$enddate."' GROUP BY FROM_UNIXTIME(unix_secs);

Такой запрос имелся ввиду?

Відредаговано Golthana
Ссылка на сообщение
Поделиться на других сайтах

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| raw   | CREATE TABLE `raw` (
  `unix_secs` int(11) unsigned NOT NULL DEFAULT '0',
  `unix_nsecs` int(11) unsigned NOT NULL DEFAULT '0',
  `sysuptime` int(11) unsigned NOT NULL DEFAULT '0',
  `exaddr` varchar(15) NOT NULL DEFAULT '0',
  `dpkts` int(11) unsigned NOT NULL DEFAULT '0',
  `doctets` int(11) unsigned NOT NULL DEFAULT '0',
  `srcaddr` varchar(15) NOT NULL DEFAULT '0',
  `dstaddr` varchar(15) NOT NULL DEFAULT '0',
  `srcport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dstport` smallint(5) unsigned NOT NULL DEFAULT '0',
  `prot` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Ссылка на сообщение
Поделиться на других сайтах

Я бы ip загонял в ntoa.

Unixsec генерирует кучу неповторных значений что заставляет сканировать таблицу до поиска соответствий.

Ради интереса. Если сперва найти диапазон времени и потом ip?

Ссылка на сообщение
Поделиться на других сайтах

Создайте аккаунт или войдите в него для комментирования

Вы должны быть пользователем, чтобы оставить комментарий

Создать аккаунт

Зарегистрируйтесь для получения аккаунта. Это просто!

Зарегистрировать аккаунт

Вхід

Уже зарегистрированы? Войдите здесь.

Войти сейчас
  • Зараз на сторінці   0 користувачів

    Немає користувачів, що переглядають цю сторінку.

  • Схожий контент

    • Від felixio_01
      Здравствуйте. 
      Помогите разобраться. 
      Собрал stargazer 2.409
      ОС debian 11:  Linux gw1new 5.10.0-13-amd64 #1 SMP Debian 5.10.106-1 (2022-03-17) x86_64 GNU/Linux
      MySQL:  /usr/sbin/mysqld  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
      при первом запуске в в логах stargazer появилась ошибка:  Storage plugin: 'Couldn't create tariffs table list With error: Invalid default value for 'change_policy_timeout''  
      При этом создалась таблица:  ' admins'
      mysql> show tables;
      +---------------+
      | Tables_in_stg |
      +---------------+
      | admins        |
      | info          |
      +---------------+
      2 rows in set (0.00 sec)
       
      Других таблиц нет. 
      В чём может быть проблема? Может кто сталкивался?
      Спасибо. 
    • Від dormancygrace
      Добрый день. Скажите пожалуйста, можно ли развернуть бэкап бд mysql в mariadb? при попытке получаю ERROR 1071 (42000) at line 845: Specified key was too long; max key length is 1000 bytes.
      Можно это как-то вылечить? 
    • Від camchatix
      Привет!
       
      Начались необъяснимые глюки с базой ubilling
      сделали mysqldump на старой базе 5.6.36
      На свежей freebsd mysql server 5.6.51
      сделали импорт - и в карточке при обновлении страницы баланс показывает то одну цифру то другую
       
      подскажите как такое может быть и как вылечить ?
       
      вот -126 денег это правильно
       
      mysql> select login,D0,U0,Cash, LastCashAddTime   from users where login=65369051; +----------+------------+------------+---------+-----------------+ | login    | D0         | U0         | Cash    | LastCashAddTime | +----------+------------+------------+---------+-----------------+ | 65369051 | 3619373056 | 4680515584 | -126.07 |      1634677205 | +----------+------------+------------+---------+-----------------+ 1 row in set, 1 warning (0.00 sec)  
      через 3 минуты
      mysql> select login,D0,U0,Cash, LastCashAddTime from users where login=65369051; +----------+------------+------------+-------+-----------------+ | login | D0 | U0 | Cash | LastCashAddTime | +----------+------------+------------+-------+-----------------+ | 65369051 | 3555340288 | 3879243776 | 36.61 | 1634677203 | +----------+------------+------------+-------+-----------------+ 1 row in set, 1 warning (0.00 sec)  
      а тут 36 денег
      Как такое может быть ?
       
       
       
       
    • Від freehost
      В крупную хостинг-компанию требуется сотрудник службы технической поддержки.
       
      Обязанности:
      Отвечать на вопросы клиентов (работа с панелью управления, настройка POP3, SMTP, FTP, другие технические вопросы) по телефону, эл. почте, решать мелкие проблемы (неверно заполненные данные и настройки в контрольной панели, проблемы с доступом и т. п.), не сложные вопросы касающиеся администрирования, подключение IPKVM, перезагрузка серверов.
       
      Требования:
      Умение работать в Интернет с основными клиентами (браузеры: IE и Mozilla, почтовые клиенты: The bat, outlook, FTP-клиенты: IE, Far, Cute FTP; Базовые знания PHP, MySQL; Уметь читать и понимать логи Apache, Nginx, Exim Приветствуется опыт работы в Web-Дизайне, работа с Joomla, Wordpress Навыки работы в командной строке UNIX; Желателен опыт работы с VestaCP, ISPmanager Коммуникабельность, терпение, эмоциональная уравновешенность, способность к обучению.  
      Условия:
      Официальное трудоустройство 24 дня отпуска Обеды за счет компании Сменный график. Смена сутки, потом три дня выходных. Оплачиваемый больничный Возможность повышения до дежурного администратора. Работа в дата-центре (в случае локдаунов предусмотрена развозка сотрудников)
        Работа в дата-центре это возможность получить опыт работы с различными технологиями (apache, nginx, mysql, zabbix, wordpress, joomla, dns…), а так же опыт работы с железной частью серверов.
      Если нету опыта работы с Unix, резюме просьба не присылать.

      Резюме присылайте на hr@freehost.com.ua
    • Від zababaha
      Здравствуйте.
      В связи с миграцией с Nodeny 50.32 на Nodeny Plus необходимо перенести базу данных +возможно, написать скрипт для автоматического переноса её в будущем еще раз.

      Просьба откликнуться в личку.

×
×
  • Створити нове...