Как ускорить работу mysql и снять нагрузку с дисковой подсистемы

4 ответа

25

ВАШ ВОПРОС

На первый взгляд этот запрос должен касаться только 1.1597% (62510 из 5390146) таблицы. Он должен быть быстрым, учитывая ключевое распределение threadid 51506.

ПРОВЕРКА РЕАЛЬНОСТИ

Независимо от того, какую версию MySQL (Oracle, Percona, MariaDB) вы используете, ни один из них не может сражаться с одним врагом, с которым они имеют общее: архитектура InnoDB.

КЛАСТЕРИРОВАННЫЙ ИНДЕКС

Пожалуйста, имейте в виду, что каждая запись потока содержит первичный ключ. Это означает, что при чтении из индекса он должен выполнять поиск первичного ключа внутри ClusteredIndex (внутренне названный gen_clust_index) . В ClusteredIndex каждая страница InnoDB содержит как данные, так и данные индекса PRIMARY KEY. Для получения дополнительной информации см. Мой пост .

ПОСЛЕДНИЕ УСЛОВИЯ

У вас много помех в таблице, потому что некоторые индексы имеют одни и те же ведущие столбцы. MySQL и InnoDB должны перемещаться по беспорядку индекса, чтобы добраться до необходимых узлов BTREE. Вы должны уменьшить этот беспорядок, выполнив следующее:

Зачем снимать эти индексы?

• Первые три индекса начинаются с threadid
• и начинаются с тех же трех столбцов
• не нужен postid, так как это PRIMARY KEY и встроенный

БУФЕР КАШИНГ

Только в этой области MyISAM не тратит время на кеширование данных. Это потому, что он не предназначен для кэширования данных. InnoDB кэширует каждую страницу данных и индексную страницу (и ее бабушку), к которой она прикасается. Если ваш буферный пул InnoDB слишком мал, вы можете кэшировать страницы, недействительные страницы и удалять страницы в одном запросе.

ТАБЛИЧ. LAYOUT

Вы могли бы сбрить некоторое пространство из строки, рассмотрев и . У вас их как BIGINT. Они берут 16 байтов в ClusteredIndex за строку.

Вы должны запустить этот

Это будет рекомендовать, какие типы данных должны быть для этих столбцов для данного набора данных.

Заключение

MyISAM имеет гораздо меньше препятствий, чем InnoDB, особенно в области кэширования.

Пока вы указали объем ОЗУ () и версию MySQL (), есть еще другие части этой головоломки, которые вы не обнаружили.

• Настройки InnoDB
• Число ядер
• Другие настройки из

Если вы можете добавить эти вещи к вопросу, я могу подробнее разобраться.

ОБНОВЛЕНИЕ 2014-08-28 11:27 EDT

Вы должны увеличить поток

Я бы рассмотрел возможность отключения кеша запросов (см. недавнее сообщение )

Я бы сохранил буферный пул

Увеличить (если вы сделать DML на нескольких таблицах)

ДАЙТЕ ЭТО ПОВРЕЖДЕНИЕ !!!

7

@RolandMySQLDBA дал правильный намек, чтобы ответить на вопрос. Проблема заключается в том, что проблема заключается в запросе и что для того, чтобы результаты были возвращены, каждое из этих полей должно быть прочитано (как-то из базы данных).

Я сбросил все индексы, но , и вставил этот новый индекс:

Эта ссылка объясняет, что происходит здесь ( покрытие index ): запрашиваемые поля запроса, которые теперь могут быть извлечены из самого ключа. Это экономит проверку реальных данных и использование ввода-вывода на жесткий диск.

Все запросы теперь выполняются с 0.00 секунд ..:)

Большое спасибо за вашу помощь.

Изменить . Фактическая основная проблема не решена, я просто обошел ее с помощью этой техники. InnoDB нуждается в серьезной фиксации в этой области.

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

Если эти две вещи верны, чем я могу предложить посмотреть в ScaleDB в качестве альтернативы? Вы все равно будете на MariaDB, просто (может быть) более подходящим движком.

Оба механизма быстрее выполнят запрос much с

Это потому, что он будет «охватывающим» индексом и будет работать практически одинаково (с использованием индекса BTree).

Кроме того, я скажу, что это невозможно для двигателя на «холодном» сервере:

Пожалуйста, используйте при выполнении таймингов — мы не хотим, чтобы кэш запросов загрязнял выводы.

Еще один быстрый подход (без кэширования ввода-вывода):

Используйте InnoDB и измените с на

Причина в том, что это приведет к смещению всех соответствующих строк, что потребует меньше ввода-вывода и т. д. должен содержать счастливый. Предостережение: это бесполезно со всеми вторичными ключами — некоторые будут быстрее, некоторые будут медленнее.

MyISAM

Подсистема хранения данных MyISAM использовалась по умолчанию для MySQL вплоть до версии 5.5 1. В отличие от InnoDB, таблицы подсистемы хранения данных MylSAM не поддерживают ACID-совместитмость. Таблицы MylSAM поддерживают только блокировку уровня таблицы, поэтому таблицы MyISAM небезопасны для транзакций. Таблицы MyISAM оптимизированы для сжатия и скорости. MyISAM обычно используется, когда вам нужно иметь в основном операции чтения с ми­нимальными транзакционными данными. Максимальный размер таблицы My- ISAM может достигать 256 Тб, что помогает в таких случаях, как анализ данных.

Важные примечания относительно таблиц MyISAM

Подсистема хранения данных MyISAM поддерживает полнотекстовое индексиро­вание, которое может помочь в сложных операциях поиска. С помощью полно­текстовых индексов можно индексировать данные, хранящиеся в типах данных и . Мы подробно рассмотрим полнотекстовое индексирование в следующих статьях.

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

Таблица MyISAM не поддерживает транзакции и внешние ключи. Судя по все­му, из-за этих ограничений вместо таблиц MyISAM теперь системные таблицы схемы MySQL 8 используют таблицы InnoDB.

MyISAM vs InnoDB

Now that we have a basic understanding of MyISAM and InnoDB, let’s compare them on various factors:

1. MyISAM vs InnoDBStorage: Engine Type

MyISAM is a non-transactional storage type, and any write option needs to be rolled back manually (if needed).

InnoDB is a transaction storage type that automatically rollbacks the writes if they are not completed.

2. MyISAM vs InnoDBStorage: Locking

Locking is the mechanism in MySQL that prevents two users from modifying the duplicate rows at the same time by locking the row. The users can’t change the table when locking is enabled.

MyISAM uses the default method of table locking and allows a single session to modify the table. This means only one user at a time can alter the table. If another user tries to change the table, they will get a message saying that it is locked. The table locking method is helpful for read-only databases as it doesn’t require a lot of memory.

InnoDB uses row-level locking of the table. This method supports multiple sessions on the same row by only locking the rows in the modification process. Row-locking is helpful for databases that have multiple users. 

The only disadvantage of row-level locking is that it consumes a lot of memory, and querying and modifying data takes time.

3. MyISAM vs InnoDBStorage: Foreign Keys

A foreign key is a column in one table that links the data to another table. It prevents users from adding records that destroy the link between two tables.

MyISAM doesn’t support the Foreign key option.

InnoDB supports the Foreign Key option.

4. MyISAM vs InnoDBStorage: ACID Properties 

ACID stands for Atomicity, Consistency, Isolation, and Durability. MyISAM doesn’t support ACID properties whereas InnoDB supports ACID properties.

5. MyISAM vs InnoDBStorage: Performance

InnoDB supports transactional properties, i.e. rollbacks and commits, and has a higher speed of writing. The performance of InnoDB for large volumes of data is better as compared to MyISAM.

MyISAM doesn’t support transactional properties and is faster to read. As compared to InnoDB, the performance for a high volume of data is less.

6. MyISAM vs InnoDBStorage: Reliability

InnoDB uses a transactional log to log every operation and hence gives reliable operations. Thus, in case of failure, data can be recovered quickly by using those logs.

MyISAM offers no data integrity; hardware failures and canceled operations can cause data to become corrupt.

7. MyISAM vs InnoDBStorage: Caching and Indexing

InnoDB supports a large pool of buffers that caches both data and indexes. However, there is no support for a Full-text search.

The MyISAM key buffer is only meant for indexes, and a full-text search is supported in MyISAM.

Understanding MySQL

Image Source: AWS

MySQL is a popular open-source RDBMS (Relational Database Management System)

It is widely used by academics and professionals all around the world. MySQL is available for free under the GNU public license and is also known as a premium proprietary version. Michael Widenius originally developed MySQL at MySQL AB, a Swedish-based company. In 2012, Sun Microsystems acquired MySQL AB, and later Oracle acquired Sun Microsystems.

MySQL is used for various applications and is built around SQL (Structured Query Language). It is also being used by some of the popular websites, including Twitter, Facebook, Mediawiki, YouTube and Flickr, etc.

Журналирование

Предположим, вы хотите использовать MySQL для ведения в режиме реального времени журнала всех телефонных звонков, поступивших с центрального телефонного коммутатора. Или, возможно, вы установили утилиту для Apache и теперь можете хранить сведения обо всех посещениях сайта прямо в таблице. В та­ких приложениях, вероятно, самым важным является обеспечение быстродействия.

Вы же не хотите, чтобы база данных оказалась узким местом. Подсистемы хранения данных MyISAM и будут очень хорошо работать, поскольку характеризуют­ся небольшими издержками, и вы сможете осуществлять тысячи операций записи в секунду.

Однако все становится гораздо интереснее, когда приходит время генерировать отче­ты на основе записанных в журнал данных. В зависимости от того, какие запросы вы используете, велика вероятность, что сбор данных для отчета значительно замедлит процесс добавления новых записей. Что можно сделать в этой ситуации?

Например, можно использовать встроенную функцию репликации MySQL для дублирования данных на второй сервер, где затем будут запущены запросы, активно потребляющие ресурсы центрального процессора (ЦП). Таким образом, главный сервер останется свободным для вставки записей и вы сможете делать любые за­просы, не беспокоясь о том, как создание отчета повлияет на ведение журнала в реальном времени.

Также вы можете запускать запросы в периоды низкой загрузки, правда, по мере развития приложения эта стратегия может стать неработоспособной.

Другой вариант — вести журнал в таблице, имя которой составлено из года и на­звания или номера месяца, например или . Если вы будете адресовать запросы к таблицам, в которые уже не производится запись, то приложение сможет непрерывно сохранять новые данные журнала в текущую таблицу.

Блокировка и Транзакции

• получает два, соединяет каждую таблицу если (значение по умолчанию). В дополнение к блокировке
  таблицы на уровне MySQL это также получает блокировка таблицы.
  Версии MySQL прежде 4.1.2 не получали блокировки таблицы; старое
  поведение может быть выбрано, устанавливая . Если нет
  блокировка таблицы получается, завершается, даже если некоторые записи таблиц
  блокируются другими транзакциями.

  В MySQL 5.7, не имеет никакого эффекта для таблиц,
  заблокированных явно с . Это действительно имеет эффект для таблиц,
  заблокированных для чтения, или пишет неявно (например, через триггеры) или .

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

• Невозможно заблокировать дополнительные таблицы в середине транзакции потому что
  выполняет неявное и .

• Предел на изменяющих данные транзакциях теперь 96 * 1023 параллельных транзакции,
  которые генерируют записи отмены. С MySQL 5.7.2, 32 128 откатов сегменты присваиваются нежурналам отката
  для транзакций, которые изменяют временные таблицы и связанные объекты. Это уменьшает максимальное
  количество параллельных изменяющих данные транзакций от 128 K до 96 K. 96 K предела предполагает, что
  транзакции не изменяют временные таблицы. Если все изменяющие данные транзакции также изменяют временные
  таблицы, предел составляет 32 K параллельных транзакций.

Есть и нормально работает MyISAM, но хочется, чтобы стало InnoDB

Перед всеми манипуляциями надо сделать резервный дамп и проверить, не используются ли у вас в каких-то таблицах FULLTEXT-индексы. Дело в том, что FULLTEXT поддерживается только в MyISAM.

Проверить можно таким запросом:

SELECT
  tbl.table_schema,
  tbl.table_name
FROM (
  SELECT
    table_schema,
    table_name
  FROM information_schema.tables
  WHERE 1=1
    AND engine = 'MyISAM'
    AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
) tbl
INNER JOIN
(
  SELECT
    table_schema,
    table_name
  FROM information_schema.statistics
    WHERE index_type = 'FULLTEXT'
) ndx
USING (table_schema, table_name);

В условии я исключаю системные базы (которые трогать вообще не надо). Если у вас MySQL версии меньше, чем 5.5, то база может отсутствовать. Заранее проверьте есть она или нет и, при необходимости, уберите её из запроса.

Если запрос вернёт пустой результат, то всё ОК, у вас нет FULLTEXT-индексов, можно все таблицы перевести на InnoDB. Если результат непустой, то вы увидите имена таблиц, которые на InnoDB переводить нельзя, чтобы ничего не поломалось.

Например, если среди баз у вас есть БД для CMS WordPress, то, скорее всего, вы обнаружите, что FULLTEXT-индексы применяются в таблице . Если очень хочется, то вы, конечно, можете остальные таблицы базы преобразовать в InnoDB руками, а — не трогать. Чтобы сменить движок для таблицы в базе надо сделать следующее:

USE wordpressdb;
ALTER TABLE wp_users ENGINE=InnoDB;

Но если вам повезло, и у вас нет таблиц с полнотекстовыми индексами (например, если вы используете для сайтов не WordPress, а Drupal), то можно одним запросом изменить движки сразу для всех таблиц во всех базах (кроме системных):

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'MyISAM'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Снова напомню, что системная база может отсутствовать. Проверьте заранее.

Теперь второй случай:

Зачем может понадобиться смена формата таблиц?

У каждой из таблиц есть свои преимущества и недостатки. В целом считается, что InnoDB более надежная база для больших структур, чем MyISAM.

Однако на деле, в привычной жизни рядового вебмастера таблицы с InnoDB приносят больше проблем, чем MyISAM. Потому что последние чинить гораздо проще.

Ниже ошибка в базе данных InnoDB, которая не чинится встроенными инструментами SQL, phpMyAdmin или WordPress.

Вы будете получать уведомления:

the storage engine for the table doesn’t support repair wordpress

Также в логах можно увидеть ошибки от InnoDB такого рода:

171027 15:11:53 InnoDB: The InnoDB memory heap is disabled 171027 15:11:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins 171027 15:11:53 InnoDB: Compressed tables use zlib 1.2.7 171027 15:11:53 InnoDB: Using Linux native AIO 171027 15:11:53 InnoDB: Initializing buffer pool, size = 128.0M 171027 15:11:53 InnoDB: Completed initialization of buffer pool 171027 15:11:53 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 171027 15:11:53  InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer… 171027 15:11:54  InnoDB: Waiting for the background threads to start 171027 15:11:55 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 402087117

Для починки таких ошибок нужно заходить через SHH, создавать DUMP InnoDB и затем его восстанавливать. А это несколько замороченее, чем починка таблиц MyISAM.

Приложения на CD

Если вам когда-нибудь потребуется распространять приложения, использующие файлы данных MySQL, на CD или DVD, подумайте о применении таблиц типа MyISAM или сжатых таблиц MyISAM, которые можно легко изолировать и скопи­ровать на другой носитель. Сжатые таблицы MyISAM занимают значительно меньше места, чем несжатые, но они предназначены только для чтения. В некоторых прило­жениях это может стать проблемой, но, поскольку данные все равно предназначены для записи на носитель, поддерживающий только чтение, нет оснований избегать использования сжатых таблиц для этой конкретной задачи.

Federated

Подсистема интегрированного хранения данных FEDERATED позволяет создавать одну базу данных на нескольких физических серверах. Она открывает клиентское соединение с другим сервером и выполняет запросы к таблице, получая и от­правляя строки по мере необходимости. Первоначально она рекламировалась как конкурентная функциональность, которая поддерживала многие корпоративные проприетарные серверы баз данных, такие как Microsoft SQL Server и Oracle, но это всегда было натяжкой, мягко говоря. Хотя казалось, что в ней задействовалось много гибкости и хитрых приемов, она оказалась источником многих проблем и по умолчанию деактивирована. Однако мы можем ее активировать, запустив двоичный файл сервера MySQL с параметром .

Давайте создадим таблицу FEDERATED.

 В поле содержится следующая ниже информация для вашей справки:

• : имя пользователя удаленного сервера MySQL;
• : пароль удаленного сервера MySQL;
• : имя хоста удаленного сервера;
• : номер порта удаленного сервера;
• : имя базы данных удаленного сервера;
• : имя таблицы базы данных удаленного сервера.

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

Сценарий такой:

1. Делаем дамп!!!
2. Конвертируем все таблицы всех баз в MyISAM
3. Удаляем файлы и логи InnoDB
4. Перезапускаем MySQL, чтобы она создала новые пустые файлы и логи
5. Конвертируем все таблицы всех баз обратно в InnoDB из MyISAM

Будем исполнять.

Делаем дамп (это делаем не в mysql, а в bash):

Переводим все таблицы всех несистемных баз на MyISAM:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'InnoDB'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Удаляем файлы и логи InnoDB и перезапускаем MySQL (это мы делаем не в mysql, а в обычной системной консоли, в bash короче):

service mysqld stop \
  && rm -f /var/lib/mysql/ibdata1 \
  && rm -f /var/lib/mysql/ib_logfile* \
  && service mysqld start

Простой будет минимальным: после установки сервера мы лишь удалим несколько файлов, а это быстро, и снова запустим сервер.

Переводим все таблицы назад на InnoDB:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
  AND engine = 'MyISAM'
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

Готово.

Ещё можно решить проблему с помощью дампа. То есть в дампе меняем тип движка, а потом этот дамп вливаем обратно. Хитрость в том, чтобы не лезть с заменой подстроки MyISAM на InnoDB в данные, меняя только в схемах:

Лучшая производительность и стабильность

Из моего исследования о MyISAM против InnoDB я узнал, что InnoDB способен использовать преимущества нескольких ядер, тогда как MyISAM может использовать только одно ядро. Это означает, что загружается в 4-ядерный сервер, как мой Linode. InnoDB также имеет другие функции, такие как способность лучше восстанавливаться после сбоя и в целом более стабильный характер.

После того как я переключил формат всех таблиц базы данных на InnoDB, моя общая загрузка ЦП на сервере снизилась, сайты стали значительно быстрее, и ни одного сбоя еще не было. Я все еще в поиске, но сейчас я принял, что InnoDB – лучший формат для баз данных WordPress.

Имейте в виду, что WordPress не решает, какой формат будут использовать базы данных. По умолчанию ваш сервер определяет формат таблицы базы данных.