Я использовал MySQLTuner, который указал, что некоторые таблицы фрагментированы. я использовал
mysqlcheck --optimize -A
оптимизировать все таблицы. Он исправил некоторые таблицы, но MySQLTuner все еще находит 19 таблиц фрагментированными. как я могу увидеть, какие таблицы нуждаются в дефрагментации? Может быть, OPTIMIZE TABLE будет работать там, где mysqlcheck не работает? Или что еще попробовать?
краткий ответ:
select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;
Ответ "Вы должны знать"
Во-первых, вы должны понять, что таблицы Mysql фрагментируются при обновлении строки, так что это нормальная ситуация. Когда создается таблица, скажем, импортированная с использованием дампа с данными, все строки сохраняются без фрагментации на многих страницах фиксированного размера. Когда вы обновляете строку переменной длины, страница, содержащая эту строку, делится на две или более страниц для хранения изменений, и эти новые две (или более) страницы содержат пустые пространства, заполняющие неиспользуемое пространство.
Это не влияет на производительность, если, конечно, фрагментация не вырастет слишком сильно. Что такое слишком большая фрагментация, давайте посмотрим на запрос, который вы ищете:
select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;
DATA_LENGTH и INDEX_LENGTH - это пространство, используемое вашими данными и индексами, а DATA_FREE - это общее количество байтов, неиспользованных на всех страницах таблицы (фрагментация).
Вот пример реального производственного стола
| ENGINE | TABLE_NAME | data_length | index_length | data_free |
| InnoDB | comments | 896 | 316 | 5 |
В этом случае у нас есть таблица, в которой используется (896 + 316) = 1212 МБ, а для данных есть свободное пространство 5 МБ. Это означает «коэффициент фрагментации»:
5/1212 = 0.0041
... Это действительно низкий "коэффициент фрагментации".
Я работал с таблицами с коэффициентом около 0,2 (то есть 20% пустых пространств) и никогда не замечаю замедления запросов, даже если я оптимизирую таблицу, производительность такая же. Но применение таблицы оптимизации к таблице размером 800 МБ занимает много времени и блокирует таблицу на несколько минут, что практически невозможно в производственной среде.
Итак, если вы считаете, что вы выигрываете в производительности, и время, потраченное на оптимизацию таблицы, я предпочитаю НЕ ОПТИМИЗИРОВАТЬ.
Если вы считаете, что это лучше для хранения, посмотрите свое соотношение и посмотрите, сколько места вы можете сэкономить при оптимизации. Обычно это не слишком много, поэтому я предпочитаю НЕ ОПТИМИЗИРОВАТЬ.
И если вы оптимизируете, следующее обновление создаст пустые места, разделив страницу на две или более. Но быстрее обновлять фрагментированную таблицу, чем нефрагментированную, потому что, если таблица фрагментирована, обновление строки не обязательно приведет к разделению страницы.
Я надеюсь, это поможет вам.
Просто чтобы добавить к ответу от Фелипе-Рохас вы можете рассчитать коэффициент фрагментации как часть запроса:
select ENGINE,
concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,
round(DATA_LENGTH/1024/1024, 2) as data_length,
round(INDEX_LENGTH/1024/1024, 2) as index_length,
round(DATA_FREE/1024/1024, 2) as data_free,
(data_free/(index_length+data_length)) as frag_ratio
FROM information_schema.tables
WHERE DATA_FREE > 0
ORDER BY frag_ratio DESC;
Если таблица фрагментирована на небольшой процент (менее 5%?), Вы, вероятно, можете оставить ее в покое.
Что-нибудь большее, и вам нужно будет оценить, основываясь на вашем использовании db, таблицах блокировки и т. Д., Насколько важна дефрагментация таблицы.
Оптимизация таблицы действительно решит вашу проблему.
Если у вас всего несколько баз данных, вы можете использовать PHPMyAdmin для просмотра всех своих баз данных. Выберите таблицы с накладными расходами, а затем выберите оптимизацию.
Если у вас много баз данных, вероятно, будет предпочтительнее другой метод.
Я использую следующую настройку PHP-скрипта в cron для запуска каждый час.
$DB = new mysqli ('localhost', 'DbUser', 'DbPassword');
$results = $DB->query('show databases');
$allDbs = array();
while ($row = $results->fetch_array(MYSQLI_NUM))
{
$allDbs[] = $row[0];
}
$results->close();
foreach ($allDbs as $dbName)
{
if ($dbName != 'information_schema' && $dbName != 'mysql')
{
$DB->select_db($dbName);
$results = $DB->query('SHOW TABLE STATUS WHERE Data_free > 0');
if ($results->num_rows > 0)
{
while ($row = $results->fetch_assoc())
{
$DB->query('optimize table ' . $row['Name']);
}
}
$results->close();
}
}
$DB->close();
Я наткнулся на эту страницу и нашел, что запросы Фелипе-Рохаса и сисадмирала очень полезны. Но в моем случае я выполнял запрос в phpMyAdmin WHM, и получение только TABLE_NAME не было столь полезным, поскольку база данных не была указана, а несколько баз данных имеют одинаковые имена таблиц. Итак, просто добавив TABLE_SCHEMA также предоставит этот столбец.
select ENGINE, TABLE_SCHEMA, TABLE_NAME, Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables where DATA_FREE > 0 order by frag_ratio desc
Показывает БД
ENGINE | TABLE_SCHEMA | TABLE_NAME | data_length | index_length | data_free | frag_ratio
InnoDB | db_name | db_table | 0 | 0 | 8 | 170.6667
Чтобы «исправить», я использовал ссылку на таблицу дефрагментации в phpMyAdmin для каждой из таблиц, что привело к высокому «frag_ratio», для которого выполняется phpMyAdmin:
ALTER TABLE `table_name` ENGINE = InnoDB;
Таблицу, использующую MySQL InnoDB Engine, по сути, никогда не нужно OPTIMIZEd.
Значение Data_free из любого information_schema.tables или SHOW TABLE STATUS очень часто отличен от нуля, даже если вы думаете, что сделали все, что могли, дефрагментировать свою таблицу (таблицы). Более того, эта метрика - только одна из несколько фрагментации, которые могут и происходят. (Кроме того, потраченное впустую пространство в блоках, списках отмены, индексных BTrees и данных BTrees и т. Д.
И innodb_file_per_table усложняет использование Data_free. Если стол в ibdata1, затем Data_free относится ко всему табличному пространству; довольно бесполезное число. Если таблица сама по себе .ibd файла, скорее всего, он будет составлять несколько МБ или несколько процентов от размера таблицы, в зависимости от того, что больше.
Только если вы удалили лоты рядов и не собираюсь пополнять стол, мощь стоит бежать OPTIMIZE TABLE.
PARTITIONs также показывают тревожное количество Data_free, поскольку каждый раздел обычно показывает 4-7МБ "свободно". И это никуда не денется.
Зачем дефрагментировать?
innodb_file_per_table=1. Но по мере добавления строк вы заберете их из ОС.Data_free.Историческая справка. Когда я помогал администраторам баз данных в основном с таблицами MyISAM, я обнаружил, возможно, 2 из 1000 таблиц, которым помогал ежемесячно OPTIMIZE. С тех пор я работал с тысячами таблиц InnoDB, но до сих пор обнаружил проблему с производительностью, которой, вероятно, поможет OPTIMIZE. (Конечно, были проблемы с дисковым пространством, для которых OPTIMIZE может помочь, но это становится непросто - обычно администратору баз данных не хватает места на диске для запуска OPTIMIZE!)