Кеширование часто используемых объектов
Обработка запросов в веб-приложениях часто связана с извлечением данных, обычно из удаленных источников, таких как базы данных или веб-службы. Выборка данных — довольно дорогостоящая операция, нередко связанная с необходимостью ожидания ответа. Вместо извлечения данных для каждой отдельной операции, их можно извлечь сразу все и сохранить в памяти, применив какой-либо механизм кеширования. После этого, для обработки вновь поступающих запросов, данные можно извлекать из кеша, не обращаясь к удаленному источнику. Алгоритм кеширования часто описывается следующим образом:
-
Если данные уже присутствуют в кеше, используются эти данные.
-
Иначе извлечь данные, сохранить в кеше и использовать их.
Так как к одному и тому же объекту в кеше может обратиться сразу несколько запросов из разных потоков выполнения, необходимо предусмотреть непротиворечивое изменение объекта в кеше — либо интерпретировать его как неизменяемый (в этом случае, чтобы изменить объект в кеше, необходимо создать его копию, выполнить изменения в копии и сохранить измененную копию в кеше), либо использовать механизмы блокировки, чтобы исключить конфликты между разными потоками выполнения.
Многие разработчики используют в роли кеша коллекцию Application, потому что она обеспечивает кеширование в памяти, доступна всем пользователям из всех сеансов. Работать с коллекцией Application очень просто:
При использовании коллекции Application, сохраняемые объекты постепенно накапливаются в памяти, что может привести к ее исчерпанию и вызвать необходимость использования файла подкачки или даже вызвать ошибку нехватки памяти. Поэтому ASP.NET предоставляет специальный механизм кеширования, поддерживающий средства управления объектами в кеше и удаляющий неиспользуемые объекты из кеша при нехватке памяти.
Кеширование в ASP.NET доступно через класс Cache, реализующий обширный механизм кеширования, который помимо возможности хранить объекты также позволяет:
-
Определять предельное время хранения объектов в кеше, указывая либо значение типа TimeSpan (продолжительность), либо значение типа DateTime (конкретные дата и время). По истечении времени хранения объекты будут удаляться из кеша автоматически.
-
Определять приоритеты кешируемых объектов. При нехватке памяти, когда требуется освободить место в кеше, наличие приоритетов помогает механизму кеша решить, какие из объектов «менее важны».
-
Определять правила проверки допустимости хранения объектов в кеш, добавляя зависимости, такие как зависимости от SQL. Например, если кешируемый объект был получен в результате SQL-запроса, можно установить зависимость объекта от SQL, чтобы изменения в базе данных, влияющие на результат запроса, делали объект в кеше недействительным.
-
Присоединять к объектам в кеше функции обратного вызова, которые должны вызываться при удалении объектов из кеша. Использование функций обратного вызова позволит своевременно обновлять данные в кеше, как только истекает время их хранения или они становятся недействительными.
Добавление элементов в кеш выполняется так же, как добавление элементов в словарь:
При добавлении в кеш таким способом, элемент получит приоритет по умолчанию Normal и не будет иметь предельного времени хранения или зависимостей. Например, чтобы добавить элемент в кеш, который должен хранить некоторый интервал времени, используйте метод Insert:
Парадигма доступа к кешу с использованием класса Cache реализуется следующим образом:
Обратите внимание, что в первой строке в этом примере извлечение объекта из кеша выполняется без предварительной проверки его наличия там. Это обусловлено тем, что объекты могут удаляться из кеша в любые моменты времени другими запросами или самим механизмом кеширования, то есть объект может быть удален между проверкой его существования и операцией извлечения
Разделяйте большие запросы DELETE и INSERT
Если вам необходимо сделать большой запрос на удаление или вставку данных, надо быть осторожным, чтобы не нарушить работу приложения. Выполнение большого запроса может заблокировать таблицу и привести к неправильной работе всего приложения.Apache может выполнять несколько параллельных процессов одновременно. Поэтому он работает более эффективно, если скрипты выполняются как можно быстрее.Если вы блокируете таблицы на долгий срок (например, на 30 секунд или дольше), то при большой посещаемости сайта, может возникнуть большая очередь процессов и запросов, что может привести к медленной работе сайта или даже к падению сервера.Если у вас есть такие запросы, используйте LIMIT, чтобы выполнять их небольшими сериями.
Hotlink-защита
Hotlink защита относится к ограничению по заголовку HTTP Referer, для предотвращения размещения активов на других сайтах. Такая защита позволит сэкономить на трафике, запрещая другим сайтам отображать изображения с этого сайта.Пример: URL сайта является www.domain.com . Чтобы остановить хотлинкинг изображений с этого сайта на других сайтах и отображать изображения с заменой имени donotsteal.jpg от хоста изображения, необходимо поместить этот код в .htaccess файл:RewriteEngine OnRewriteCond% {HTTP_REFERER} ^ HTTP: (.. + \)?do main \ .com / RewriteCond% {HTTP_REFERER}! ^ $RewriteRule * \.. (JPE г |? GIF | BMP | PNG) $ http://i.imgur.com/donotsteal.gif KeyCDN также имеет легкий способ включения защиты Hotlink для защиты трафика CDN.
Совет 2: Табличные переменные и объединения
Когда у вас есть сложные запросы, такие как получение заказов для клиентов, вместе с их именами и датами заказа, вам нужно нечто большее, чем простой оператор выбора. В этом случае мы получаем данные из таблиц клиентов и заказов. Вот где вступают в силу объединения .
Давайте посмотрим на пример соединения:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Табличные переменные — это локальные переменные, которые временно хранят данные и обладают всеми свойствами локальных переменных. Не используйте табличные переменные в объединениях, как SQL видит их как одну строку. Несмотря на то, что они быстрые, табличные переменные плохо работают в соединениях.
Исправление ошибок 404
Любой отсутствующий файл генерирует ошибку 404. В зависимости от платформы обработка 404 ошибки может быть ресурсоемкой или нет. Например, Drupal имеет очень ресурсоёмкую обработку ошибки 404. На «среднем» сайте можно увидеть потребление памяти в районе 50 Mb на отдачу ошибки 404.Не рекомендуется устанавливать плагины или модули для проверки ошибок 404, вместо этого лучше время от времени тестировать веб-сайт внешним сервисом, таким, как » Online broken Link Checker” ; или пользоваться инструментом Screaming Frog . Это позволит отслеживать ненужные траты ресурсов на эту задачу. Также можно увидеть ошибки в Google Search Console.Масштабирование изображенийВсегда надо отдавать изображения в требуемом масштабе, а не полагаться на CSS или браузер клиента, которые занизят их размер. Лучше всегда загружать изображения в масштабе, а также использовать сразу несколько разрешений изображений и использовать правильное разрешение для правильного устройства.
Оптимизация шрифтов
Согласно HTTP Archive , 60% сайтов в настоящее время используют нестандартные шрифты, количество которых выросло на 850% с 2011 года. Недостатки веб — шрифтов, таких как Google шрифты,- это то, что они добавляют дополнительные запросы HTTP на внешние ресурсы. Веб — шрифты также блокируют отрисовку страницы. Ниже приведены некоторые рекомендации для улучшения производительности веб — шрифтов.1. Выбрать шрифт на основе поддержки браузера2. Выбрать только необходимые стили3. Держать минимальные наборы символов 4. Выбрать локальный хост шрифтов или предвыбор5. Хранить шрифты в LocalStorage с Base64 EncodingМожно также переместить шрифты, используемые сайтом, в CDN . На примере поддерживаемого авторами сайта, сравнение между CDN Google и KeyCDN показало результаты в пользу KeyCDN. Это может быть объяснено тем, что последний уменьшает количество HTTP — запросов, поиск DNS; позволяет воспользоваться преимуществами одного HTTP/2 подключения и иметь больший контроль над кэшированием.
|
СКОРОСТЬ |
Google CDN (MS) |
KEYCDN (MS) |
ПОБЕДИТЕЛЬ |
|
WebPagetest Время загрузки |
1871ms |
1815ms |
KeyCDN |
|
WebPagetest Fully Loaded |
1929ms |
1862ms |
KeyCDN |
|
Pingdom Время загрузки |
355ms |
324ms |
KeyCDN |
Оптимизация таблиц и баз данных в MySQL и MariaDB
Для оптимизации таблиц и баз данных рекомендуется их дефрагментировать. Убедитесь, что в базе данных есть таблицы, требующие дефрагментации.
Откройте консоль MySQL, выберите базу данных и выполните этот запрос:
select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;
Таким образом, вы отобразите все таблицы с не менее 50 МБ неиспользуемого пространства:
— общий размер стола
— неиспользуемое место в столе
Это таблицы, которые мы можем дефрагментировать. Проверьте, сколько места они занимают на диске:
# ls -lh /var/lib/mysql/innodb_test/ | grep b_
Чтобы оптимизировать эти таблицы, выполните следующую команду в консоли mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
После успешной дефрагментации вы увидите следующий результат:
Как видите, data_free_mb теперь равно 0, а размер таблицы значительно уменьшился (в 3-4 раза).
Вы также можете запустить дефрагментацию, используя mysqlcheck в консоли сервера:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Где innodb_test ваша база данных и b_workflow_file название таблицы.
Чтобы оптимизировать все таблицы в базе данных, запустите эту команду в консоли сервера:
# mysqlcheck -o innodb_test -u root -p
Где innodb_test — имя базы данных
Или запустите оптимизацию всех баз на сервере:
# mysqlcheck -o --all-databases -u root -p
Если вы проверите размер базы данных до и после оптимизации, вы увидите, что общий размер уменьшился:
# du -sh
2,5 г
# mysqlcheck -o innodb_test -u root -p
# du -sh
1,7 г
Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.
2: Проверка стандартных переменных кэша запросов
В MySQL кэшем запросов управляет ряд переменных. Сейчас нам нужно проверить значения этих переменных, которые поставляются с MySQL по умолчанию, и разобраться, что контролирует каждая из них.
Чтобы проверить эти переменные, используйте следующую команду:
В выводе вы увидите переменные:
Значение query_cache_limit определяет максимальный размер отдельных результатов запроса, которые могут быть кэшированы. Значение по умолчанию составляет 1 048 576 байт, что эквивалентно 1 МБ.
MySQL не обрабатывает кэшированные данные целиком; он обрабатывает их блоками. Минимальный объем памяти, выделяемый каждому блоку, определяется переменной query_cache_min_res_unit. Значение по умолчанию составляет 4096 байт, или 4 КБ.
Переменная query_cache_size контролирует общий объем памяти, выделенной для кэша запросов. Если в ней установлено значение 0, это означает, что кэш запросов отключен. В большинстве случаев по умолчанию может быть установлено значение 16 777 216 (около 16 МБ). Кроме того, следует иметь в виду, что query_cache_size требует как минимум 40 КБ для размещения своих структур. Выделенное здесь значение выравнивается по ближайшему 1024-байтовому блоку – то есть фактическое значение может немного отличаться от установленного вами.
MySQL определяет запросы к кэшу, изучая переменную query_cache_type. Значение 0 или OFF отключает кэширование или извлечение кэшированных запросов. Вы также можете установить значение 1, чтобы включить кэширование для всех запросов, кроме тех, которые начинаются с SELECT SQL_NO_CACHE. Значение 2 позволяет кэшировать только те запросы, которые начинаются с SELECT SQL_CACHE.
Переменная query_cache_wlock_invalidate решает, должен ли MySQL извлекать результаты из кэша, если используемая в запросе таблица заблокирована. Ее значением по умолчанию является OFF.
Примечание: Переменная query_cache_wlock_invalidate устарела с версии MySQL 5.7.20. Вполне возможно, что вы не увидите ее в своем выводе (в зависимости от версии MySQL, которую вы используете).
Изучив системные переменные, которые управляют кэшем запросов MySQL, мы можем проверить, как работает MySQL без кэша.
Очистка и уменьшение mysql базы zabbix
Начнем с очистки базы данных zabbix от ненужных данных. Рассмотрим по пунктам в той последовательности, в которой это нужно делать.
- Первым делом надо внимательно просмотреть все используемые шаблоны и отключить там все, что вам не нужно. Например, если вам не нужен мониторинг сетевых соединений windows, обязательно отключите автообнаружение сетевых интерфейсов. Оно само по себе находит десятки виртуальных соединений, которые возвращают нули при опросе и не представляют никакой ценности. Тем не менее, все эти данные собираются и хранятся, создавая лишнюю нагрузку. Если же вам нужен мониторинг сети в windows, зайдите в каждый хост и отключите руками лишние адаптеры, которые будут найдены. Этим вы существенно уменьшите нагрузку. По моему опыту, в стандартных шаблонах windows мониторинг всех сетевых интерфейсов дает примерно 2/3 всей нагрузки этих шаблонов.
- Отредактируйте в используемых стандартных шаблонах время опроса и хранения данных. Возможно вам не нужна та частота опроса и время хранения, которые заданы. Там достаточно большие интервалы хранения. Чаще всего их можно уменьшить. В целом, не забывайте в своих шаблонах ставить адекватные реальной необходимости параметры. Не нужно хранить годами информацию, которая теряет актуальность уже через неделю.
- После отключения лишних элементов в шаблонах, нужно очистить базу данных от значений неактивных итемов. По-умолчанию, они там остаются. Можно их очистить через web интерфейс, но это плохая идея, так как неудобно и очень долго. Чаще всего попытки очистить 50-100 элементов за раз будут сопровождаться ошибками таймаута или зависанием web интерфейса. Далее расскажу, как это сделать эффективнее.
Для очистки базы данных zabbix от значений неактивных итемов, можно воспользоваться следующими запросами. Запускать их можно как в консоли mysql сервера (максимально быстрый вариант), так и в phpmyadmin, кому как удобнее.
Данными запросами можно прикинуть, сколько данных будет очищено:
SELECT count(itemid) AS history FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS history_str FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS history_text FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS history_log FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS trends FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); SELECT count(itemid) AS trends_uint FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
Если запросы нормально отрабатывают и возвращают счетчик данных, которые будут удалены, дальше можете их удалять из базы следующими sql запросами.
DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0'); DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
Данными запросами вы очистите базу данных zabbix от значений неактивных элементов данных. Но реально размер базы данных у вас не уменьшится, потому что в дефолтной настройке базы данных используется формат innodb. Все данные хранятся в файле ibdata1, который автоматически не очищается после удаления данных из базы.
Администрировать такую базу неудобно. Нужно как минимум настроить хранение каждой таблицы в отдельном файле. Этим мы далее и займемся, а заодно обновим сервер базы данных до свежей версии mariadb и реально очистим базу, уменьшив ее размер.
Совет 4: используйте SET NOCOUNT ON
При выполнении операций , , и , используйте . SQL всегда возвращает соответствующее количество строк для таких операций, поэтому, когда у вас есть сложные запросы с большим количеством соединений, это может повлиять на производительность.
С SQL не будет подсчитывать затронутые строки и улучшить производительность.
В следующем примере мы предотвращаем отображение сообщения о количестве затронутых строк.
USE AdventureWorks2012; GO SET NOCOUNT OFF; GO -- Display the count message. SELECT TOP(5)LastName FROM Person.Person WHERE LastName LIKE 'A%'; GO -- SET NOCOUNT to ON to no longer display the count message. SET NOCOUNT ON; GO SELECT TOP(5) LastName FROM Person.Person WHERE LastName LIKE 'A%'; GO -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO
Используйте EXPLAIN для ваших запросов SELECT
Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:
После добавления индекса для поля group_id:
Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.
Как спланировать нагрузку на Zabbix
Под небольшой структурой, упомянутой в начале, я подразумеваю 50-100 узлов (не сетевое оборудование с десятками портов) сети на мониторинге и примерно 2000-4000 активных элементов данных, которые записывают 20-40 новых значений в секунду. Под такую сеть вам будет достаточно небольшой виртуальной машины с 2 ядрами и 4 гб памяти. База данных на преимущественно стандартных шаблонах будет расти примерно на 2-4 Гб в год. Дальше еще меньше, так как будет автоматически очищаться.
Для мониторинга такой сети можно вообще не выполнять никаких дополнительных настроек. Мониторинг будет вполне нормально работать. Если же нагрузка начнет расти, то первое, с чем вы столкнетесь — это с размером и производительностью базы данных. База zabbix будет расти пропорционально подключению к ней хостов. И с этим придется что-то делать.
Для решения вопроса производительности нужно будет двигаться в двух направлениях:
- Очистка базы от ненужных данных.
- Увеличение производительности сервера mysql.
Каждый из указанных вопросов многогранен. Далее мы частично их рассмотрим и выполним наиболее простые, очевидные и результативные изменения.
Как проверить скорость сайта? Пять инструментов
PageSpeed Insights — инструмент Google для мониторинга скорости загрузки сайта. Сервис формирует отчеты отдельно по мобильным и десктопным страницам. Помимо данных PageSpeed Insights дает персонализированные рекомендации, как и что исправить на сайте для улучшения скорости. Наблюдения показывают, что отчеты этого инструмента не отличаются абсолютной точностью, тем не менее, их можно брать за основу как рекомендованные параметры от Google. Существенный плюс — русскоязычный интерфейс.
GTMetrix — мощный бесплатный сервис для контроля скорости загрузки страниц по обширному перечню показателей. Для более точного анализа производительности предусмотрено множество тонких настроек: здесь можно выбирать локацию пользователя, тип используемого браузера и другие параметры. Помимо цифр GTMetrix также дает полезные рекомендации, указывающие на уязвимые места сайта и пути решения проблем. Освоение программы и анализ многочисленных метрик требуют времени, но это стоит того, если вы хотите добиться ощутимого улучшения скорости загрузки. Интерфейс на английском.
WebPageTest — еще один мощный инструмент для проверки скорости. Он оптимален для глубокого анализа, но требует времени, чтобы разобраться в подробных и на первый взгляд запутанных отчетах. Без знания английского это наверняка создаст сложности. Как и в GTMetrix здесь можно добавлять тонкие настройки: выбирать локацию, тип браузера и др. Глобальное SEO-сообщество сходится во мнении, что именно WebPageTest дает наиболее адекватные цифры скорости загрузки по всему домену.
Pingdom — более простой инструмент, чем WebPageTest или GTMetrix, но при этом не менее точный. Этот бесплатный сервис дает подробную статистику по производительности сайта и имеет менее запутанный интерфейс. Здесь все более наглядно и просто. Но из-за меньшего объема предоставляемой информации он не столь эффективен для глубокого анализа, как упомянутые аналоги.
Load Impact — профессиональный сервис для проведения нагрузочного тестирование сайта. В отличие от всех вышеописанных инструментов, которые тестируют только браузерную часть, Load Impact затрагивает бэкенд. Он позволяет проанализировать, насколько хорошо сервер приспособлен к повышенным нагрузкам, и как это отражается на скорости загрузки страниц. Первые 30 дней можно проводить 50 проверок в сутки бесплатно. Тем, кто планирует использовать инструмент постоянно, разработчик предлагает несколько вариантов тарифов.
5 ответов
-
122 рейтинг
MySQL не уменьшает размер ibdata1. Когда-либо. Даже если вы используете , чтобы освободить место, используемое для удаленных записей, он будет использовать его позже.
Альтернативой является настройка сервера на использование , но для этого потребуется создать резервную копию, удалить базу данных и восстановить. Положительной стороной является то, что. ibd файл для таблицы уменьшается после .
ответ дан Leonel Martins, с репутацией 2205, 14.08.2009
-
31 рейтинг
Просто у меня была такая же проблема.
Что происходит, так это то, что даже если вы удалите базу данных, innodb все равно не освободит дисковое пространство. Мне пришлось экспортировать, остановить MySQL, удалить файлы вручную, запустить MySQL, создать базу данных и пользователей, а затем импортировать. Слава богу, у меня было только 200 МБ строк, но это сэкономило 250 ГБ файла innodb.
Сбой по замыслу.
ответ дан gilm, с репутацией 3870, 25.12.2009
-
21 рейтинг
Если вы не используете innodb_file_per_table , восстановление дискового пространства возможно, но довольно утомительно и требует значительных простоев.
How To довольно глубокий — но я вставил соответствующую часть ниже.
Обязательно сохраните копию вашей схемы в вашем дампе.
ответ дан FlipMcF, с репутацией 9090, 19.01.2012
-
-
0 рейтинг
Другой способ решить проблему восстановления пространства — создать несколько разделов в таблице — на основе диапазона, на основе значения — разделы и просто удалить / усечь раздел, чтобы освободить пространство, которое освободит пространство, используемое целыми данными, хранящимися в конкретном разделе ,
Когда вы введете разделение для своей таблицы, потребуются некоторые изменения в схеме таблиц, например: уникальные ключи, индексы для включения столбца раздела и т. Д.
ответ дан Anand Immannavar, с репутацией 179, 10.02.2015
-
-1 рейтинг
Есть несколько способов восстановить дисковое пространство после удаления данных из таблицы для движка MySQL Inodb
Если вы не используете innodb_file_per_table с самого начала, дамп всех данных, удаление всех файлов, воссоздание базы данных и повторный импорт данных — это единственный способ (проверьте ответы FlipMcF выше)
Если вы используете innodb_file_per_table, вы можете попробовать
- Если вы можете удалить все данные, команда truncate удалит данные и освободит место на диске для вас.
- Команда «Изменить таблицу» удалит и заново создаст таблицу, чтобы можно было восстановить дисковое пространство. Поэтому после удаления данных выполните команду alter table, которая ничего не меняет, чтобы освободить жесткий диск (т. Е. У таблицы TBL_A есть кодировка uf8, после удаления данных запустите ALTER TABLE TBL_A charset utf8 — & gt; эта команда ничего не меняет в вашей таблице, но она заставляет mysql воссоздать вашу таблицу восстановить дисковое пространство
- Создайте TBL_B как TBL_A. Вставьте выбранные данные, которые вы хотите сохранить из TBL_A в TBL_B. Удалите TBL_A и переименуйте TBL_B в TBL_A. Этот способ очень эффективен, если TBL_A и данные, которые нужно удалить, большие (команда удаления в MySQL innodb очень плохая производительность)
ответ дан Khánh Bùi Đức, с репутацией 661, 25.04.2016
Избегайте использования SELECT *
Чем больше данных читается, тем медленнее запрос, что связано с операцией чтения с диска. Если сервер базы данных и веб-сервер физически находятся на разных машинах, у вас будет дополнительная задержка на передачу данных.
Не рекомендуется:
$r = mysqli_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d}";
Лучше:
$r = mysqli_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d}";
«*» ускоряет разработку, а также используется «один и тот кеш», если WHERE один и тот же, но выбираются разные поля. Сравните:
-- CASE 1 SELECT username FROM user WHERE user_id = 1; -- получаем имя, результаты запроса кешируется SELECT reg_date FROM user WHERE user_id = 1; -- получаем дату, результаты запроса кешируется -- CASE 2 SELECT * FROM user WHERE user_id = 1; -- получаем все, результаты запроса кешируется SELECT * FROM user WHERE user_id = 1; -- используется кеш первого запроса!
1: Проверка кэша запросов
Перед настройкой кэша запросов нужно проверить, поддерживает ли ваша версия MySQL эту функцию. Сначала подключитесь по ssh к серверу Ubuntu 18.04:
Затем выполните следующую команду, чтобы войти на сервер MySQL как пользователь root:
При появлении запроса введите root пароль сервера MySQL, а затем нажмите Enter, чтобы продолжить.
Используйте следующую команду, чтобы проверить, поддерживается ли кэш запросов:
Вы должны получить такой вывод:
Если в have_query_cache установлено значение YES, это означает, что кэш запросов поддерживается вашей версией СУБД. Если ваша версия не поддерживает кэш, можно использовать альтернативные сторонние инструменты для оптимизации производительности MySQL (такие как ProxySQL).
Убедившись, что версия MySQL поддерживает кэш запросов, мы можем приступать к изучению переменных, которые управляют этой функцией.
Оптимизация MySQL
Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:
wget http://mysqltuner.pl/ -O mysqltuner.pl wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.
Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:
Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.
Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.
query_cache_size=0 query_cache_type=0 query_cache_limit=1M
Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.
Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64
Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное
Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.
Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.
Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.
Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:
systemctl stop mariadb
Затем переместите файлы лога в /tmp:
mv /var/lib/mysql/ib_logfile /tmp
И запустите сервис:
systemctl start mariadb
Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:
systemctl status mariadb
Используте ORM
Используя ORM, можно получить определенную оптимизацию работы. Все, что можно сделать с помощью ORM, можно сделать и вручную. Но это требует дополнительной работы и более высокого уровня знаний.ORM замечателен для «ленивой» загрузки данных. Это означает выборку данных по мере необходимости. Но необходимо быть осторожным, т.к это может привести к появлению множества маленьких запросов, что приведет к снижению производительности.ORM также может объединять несколько запросов в пакеты, вместо отправки каждого отдельно.Моя любимая ORM для PHP — Doctrine. Я уже писал статью об установке Doctrine в CodeIgniter.
Вертикальное разделение
Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.
Первоначальная настройка
У MySQL достаточно сложная конфигурация, но оптимизация запросов не требует делать все вручную. Для устранения проблем со скоростью выполнения существует специальный скрипт — MySQLTuner. Он анализирует работу базы данных и выводит рекомендации, какие параметры с какими значениями требуется изменить.
Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через wget :
Первый файл — это скрипт написан на Perl. Два остальных — сведения о простых паролях и уязвимостях, которые позволяют найти проблемы с безопасностью.
Далее необходимо провести тест базы данных. Оптимизация производительности будет основана на выявленных проблемах. Тест запускается скриптом # perl ./mysqltuner.pl . Он выдает полную статистику работы базы. Все проблемные места обозначаются красным восклицательным знаком .
Следует помнить, что параметры изменяются согласно рекомендациям, которые выдает утилита. Нельзя бездумно копировать параметры из статьи и применять их к собственной базе данных. В ином случаем можно столкнуться с рядом практических проблем. Например, недостаточным размером буфера движка таблиц (InnoDB buffer pool).
