Soru Parçalanmış MySQL tablolarını nasıl bulur ve düzeltir?


MySQLTuner kullanarak bazı tabloların parçalara ayrıldığını işaret ettim. kullandım

mysqlcheck --optimize -A

tüm tabloları optimize etmek. Bazı tabloları düzeltti, ancak MySQLTuner hala 19 tabloyu parçalı buluyor. Hangi tabloların birleştirilmeye ihtiyacı olduğunu nasıl görebilirim? Belki de OPTIMIZE TABLO mysqlcheck'in çalışmadığı yerde çalışacak mı? Ya da başka ne denemeliyim?


22
2017-11-15 14:38


Menşei


Benzer bir sorunum var. MySQL 5.5 ile yeni bir DB kuruyorum ve bazı InnoDB tabloları asla parçalanmıyor. Data_free kontrolünün (KayakJim cevabında gösterilen) InnoDB tabloları ile yanlış olup olmadığını merak ediyorum. - docwhat


Cevaplar:


kısa cevap:

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;

"Bilmelisin" cevabı

İlk olarak, bir satır güncellendiğinde Mysql tablolarının parçalanmış olduğunu anlamanız gerekir, bu yüzden normal bir durumdur. Bir tablo oluşturulduğunda, verileri içeren bir dökümü kullanarak içe aktarılsın, tüm satırlar birçok sabit boyutlu sayfada parçalanma olmadan depolanır. Değişken uzunluk satırını güncelleştirdiğinizde, bu satırı içeren sayfa değişiklikleri depolamak için iki veya daha fazla sayfaya bölünür ve bu yeni iki (veya daha fazla) sayfa kullanılmayan alanı dolduran boş alanlar içerir.

Bu, parçalanma çok fazla gelişmedikçe, performansı etkilemez. Çok fazla parçalanma nedir, bakalım aradığınız sorguyu görelim:

  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 ve INDEX_LENGTH verileriniz ve dizinlerinizin kullandığı alanlardır ve DATA_FREE, tüm tablo sayfalarında kullanılmayan toplam bayt miktarıdır (parçalanma).

İşte gerçek bir üretim tablosu örneği

| ENGINE | TABLE_NAME               | data_length | index_length | data_free |
| InnoDB | comments                 |         896 |          316 |         5 |

Bu durumda (896 + 316) = 1212 MB kullanan bir Veri Tablosuna ve 5 MB boş alana sahip bir veriye sahibiz. Bu, "parçalanma oranı" anlamına gelir:

5/1212 = 0.0041

... Bu gerçekten düşük "parçalanma oranı" dır.

Tabloyu eniyileyip 0,2 (boş alanların% 20'si) olan bir tablo ile çalışıyorum ve tabloyu optimize etsem bile, sorgularda yavaşlama fark etmiyorum, performans aynı. Ancak, 800MB'lık bir masa üzerinde bir optimizasyon tablosu uygulamak çok zaman alır ve tabloyu birkaç dakika boyunca engeller; bu da üretimde uygulanamaz.

Yani, performansta kazandığınız şeyi ve bir tabloyu optimize etmek için harcanan zamanı düşünürseniz, OPTİMİZE DEĞİL.

Depolama için daha iyi olduğunu düşünüyorsanız, oranınızı görün ve optimize ederken ne kadar alan kaydedebileceğinizi görün. Genellikle çok fazla değil, bu yüzden OPTIMIZE DEĞİL.

Ve optimize ederseniz, sonraki güncelleme iki veya daha fazla sayfada bir sayfa ayırarak boş alanlar oluşturacaktır. Ancak, parçalanmış bir tabloyu parçalanmamış bir tablodan güncellemek daha hızlıdır, çünkü eğer tablo parçalanmışsa, bir satırdaki bir güncelleme mutlaka bir sayfayı bölemez.

Umarım bu sana yardımcı olur.


35
2018-05-03 15:30



Bu birkaç yıl önce bir cevap olmasına rağmen, data_free dosyasının ilgili tablo için değil, tüm tablo alanı için bir statü olduğunu belirttim. Birden fazla tabloyu tek bir tablo alanında bir arada saklarsanız, data_free, tablo alanında birleştirilmesinin gerekip gerekmediğini düşünerek, tablo alanında serbest uzantılar olduğu anlamına gelir. En iyi duruma getirme tablosu, serbest uzantıları azaltmaz. Tabloyu birleştirmek bile artırmak serbest alanlar. - Bill Karwin


Sadece cevabı eklemek için Felipe-Rojas Parça oranını, sorgunun bir parçası olarak hesaplayabilirsiniz:

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, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables  where  DATA_FREE > 0 order by frag_ratio desc;

Bir tablo küçük bir yüzdeyse (% 5'ten az?) Parçalanmışsa muhtemelen onu yalnız bırakabilirsiniz.

Daha büyük bir şey varsa ve db kullanımınıza, masaları birleştirmenin ne kadar önemli olduğuna dair kilit tablolara göre değerlendirmeniz gerekecektir.


10
2018-02-27 13:49





Optimize Tablosu gerçekten yaşadığınız sorunu çözecektir.

Sadece birkaç veritabanınız varsa, tüm veritabanlarınızı gözden geçirmek için PHPMyAdmin'i kullanabilirsiniz. Tabloları baş üstü ile seçin ve en iyileştirmek için seçin.

Çok fazla veritabanınız varsa, başka bir yöntem tercih edilebilir.

Her saatte çalışmak için cronda aşağıdaki PHP betiği kurulumunu kullanıyorum.

$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();

2
2017-11-15 14:43



oldukça eminim mysqlcheck --optimize -A SQL ile aynı OPTIMIZE TABLE <tablename>; - docwhat


Bu sayfaya rastladım ve Felipe-Rojas ve sysadmiral'in sorgularını çok yararlı buldum. Ama benim durumumda, WHM'nin phpMyAdmin'de sorguyu çalıştırıyordum ve veritabanı listelenmediğinden TABLE_NAME uygulamasının sadece yardımcı olması pek işe yaramazdı ve birkaç veritabanı aynı tablo adlarına sahipti. Yani, sadece ekleyerek TABLE_SCHEMA bu sütunu da sağlayacaktır.

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

DB'yi gösterir

ENGINE  | TABLE_SCHEMA  | TABLE_NAME    | data_length   | index_length  | data_free | frag_ratio

InnoDB  | db_name       | db_table      | 0             | 0             | 8         | 170.6667

"Düzelt" için phpMyAdmin'in çalıştırdığı yüksek "frag_ratio" ile sonuçlanan tabloların her biri için phpMyAdmin'de Birleştirme tablosu bağlantısını kullandım:

ALTER TABLE `table_name` ENGINE = InnoDB;

0
2017-09-18 20:51