Bu yüzden dün bir MySQL sunucusunu optimize etmek için yardım isteyen bir PM aldım ve cevabımın ne olacağını herkesle paylaşabileceğimi düşündüm.
Her şeyden önce MySQL'in (en azından 5.6) veya MariaDB'nin (10.3 veya daha yeni) yeni bir sürümünü kullandığınızdan emin olun çünkü bunlar genellikle eski sürümlerden daha iyi performans göstermelidir.
MySQL'inizi yükseltmek kolaydır - sadece mevcut sürümü kaldırın (yani: pkg delete mysql55-server) ve yenisini kurun (pkg install mysql57-server). Şu anda yüklü olan sürümü pkg bilgisi ile bulabilirsiniz. İstemciyi ayrı olarak kaldırmanız ve yüklemeniz de gerekebilir.
MySQL'i kaldırdığınızda, verilerinizin silinmediğini ve bu nedenle çok farklı bir sürüme geçmediğiniz sürece yükseltilmiş sunucunuzu hemen başlatabilmeniz gerektiğini unutmayın - bu durumda mysql_upgrade'i daha yeni sürümler üzerinden çalıştırmanız gerekebilir. MySQL ve MariaDB bunu kendileri yapacak. Her şeye rağmen, önce bir yedekleme yapmak iyi bir fikirdir: ya fiziksel bir kopya (yani, dosyaları / var / db / mysql içindeki dosyaları başka bir yere kopyalayın, ancak tablo bozulmasını önlemek için önce mysql hizmetini durdurun!) - hala desteklenen sürümü, bir sorun olması durumunda tekrar kurabiliriz - veya yoksa mysqldump ile yedekleme.
Kendi my.cnf dosyanızı uzun süredir kullanıyorsanız, yeni bir sürüm yüklemek onu yeni varsayılanlarla değiştirmeyecektir, bu da mysql'nin başlamasını önleyecek eski ayarları burada kullanıyor olabileceğiniz anlamına gelir - sadece MySQL günlüğünüzü kontrol edin /var/db/mysql/<yourhostname>.err hizmeti başlattıktan sonra her şeyin yolunda olduğundan emin olmak için.
Artık yeni bir sürümü çalıştırdığımıza göre, optimizasyona doğru bir şekilde geçelim. Kendinize neyi optimize edeceğinizi nasıl bulacağınızı açıklamayacağım - Veritabanı Yönetimi dereceleri bunun için. Size sadece bazı yararlı araçlara işaret edeceğim. İlk olarak, InnoDB'ye olabildiğince çok tabloyu taşımanız önerilir. Navicat kullanıyorsanız ve kullanmasanız bile, bu çok önemsiz bir görevdir.
MySQL için ZFS ayarı
İyi miktarda InnoDB tablonuz varsa ve ZFS dosya sistemini kullanıyorsanız (örneğin OVH, varsayılan olarak ZFS'yi yükler) InnoDB tablolarının blok boyutuyla eşleşmeleri için belirli dosya sistemleri kurmak isteyebilirsiniz. Dikkat, bu prosedür yeni başlayanlar için tavsiye edilmez:
İlk olarak, bu ayarları [mysqld] bölümünün altındaki my.cnf'ye ekleyin (bu ayarlar zaten mevcutsa, uygun şekilde değiştirin)
innodb_data_home_dir = /var/db/mysql-innodb
innodb_log_group_home_dir = /var/db/mysql-innodb-logs
Çalışıyorsa mysql-server hizmetini durdurun ve ardından mysql dizininizi taşıyın:
mv / var / db / mysql / var / db / mysql-bak
Daha sonra kabukta InnoDB için özel dosya sistemlerimizi oluşturmak için şu komutları yürütün:
zfs create -o recordsize=16k -o primarycache=metadata zroot/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata zroot/var/db/mysql-innodb-logs
zfs create -o recordsize=8k zroot/var/db/mysql
Şimdi aşağıdaki dosyaları uygun konumlarına kopyalayalım (bunların hepsine sahip olmayabilirsiniz - sorun değil, o zaman atlayın)
cp /var/db/mysql-bak/ib_logfile* /var/db/mysql-innodb-logs/
cp /var/db/mysql-bak/ib_buffer_pool /var/db/mysql-innodb/
cp /var/db/mysql-bak/ibdata* /var/db/mysql-innodb/
cp /var/db/mysql-bak/ibtmp* /var/db/mysql-innodb/
cp -r /var/db/mysql-bak/* /var/db/mysql/
Şimdi mysql sunucusunu başlatın ve hata olmadığından emin olmak için günlükleri kontrol edin; sonra / var / db / mysql-bak klasörünü silebileceğinizden emin olduğunuzda.
Tuning araçları
Her şey sırayla. MySQL ile ilgili tüm araçlar için ana bilgisayar, kullanıcı ve şifre belirlemenin en kolay yolu, /root/.my.cnf adlı bir dosya oluşturmak ve diğer kullanıcıların şifremizi okuyamaması için ona chmod 600 vermektir.
[client]
user=root
password=whatever
port=3306
socket=/tmp/mysql.sock
Bu şekilde, aynı seçenekleri defalarca belirtmeye gerek kalmadan otomatik olarak oturum açacağız.
MySQLTuner
MySQLTuner, mysql sunucunuzun performansını artırmak için size uygulanabilir öneriler sunan popüler bir perl betiğidir. Şahsen ben Percona Toolkit'i tercih ederim, ancak çıktıyı okumak çok kolay olduğu için bu yeni başlayanlar için iyi bir başlangıç noktasıdır.
cd /root
fetch http://mysqltuner.pl
perl mysqltuner.pl
MySQL sunucumuzun kullanıcı ve şifresini girdikten sonra, bir dizi istatistik ve öneride bulunacaktır.
Daha fazla bilgi ve komut satırı seçenekleri için: https://github.com/major/MySQLTuner-perl
Percona Araç Seti
Percona Sunucusu, aslında, tıpkı MariaDB gibi, MySQL'in yerini alan bir düşüştür. Şahsen ben test etmedim çünkü FreeBSD desteği yarım yamalak görünüyor. Yine de, yardımcı araç paketi "percona-toolkit" için bir bağlantı noktası bulabiliriz, bu da MySQLTuner'dan daha kapsamlı öneriler sunar.
Bazıları linux komutlarına güvendiği ve hiç kimse bunları FreeBSD'ye uyarlamayı umursamadığı için (desteğin yarım yamalak olduğunu söylemiştim!), Setteki tüm araçlar çalışmıyor, ancak en kullanışlı olanlardan bazıları:
pt-duplicate-key-checker MySQL tablolarında yinelenen dizinleri ve yabancı anahtarları bulun.
pt-index-usage Günlükten sorguları okuyun ve dizinleri nasıl kullandıklarını
analiz edin pt-query-Digest MySQL sorgularını günlüklerden, işlem listesinden veya tcpdump'dan analiz edin
pt-table-usage Sorguların tabloları nasıl kullandığını analiz edin.
pt-variable-advisor MySQL değişkenlerini analiz edin ve olası problemler hakkında tavsiyede bulunun (MySQLTuner önerilerine benzer)
Yeni başlayanlar için en kullanışlı bulduğum iki araçla birlikte Percona Toolkit'in kullanım örnekleri. Dizin kullanımı yavaş bir sorgu günlüğünü okur ve kaldırılması gereken dizinleri önerirken, değişken danışmanı MySQLTuner'a benzer öneriler gösterir:
pt-index-usage -F /root/.my.cnf /var/db/mysql/slow.log
pt-variable-advisor "S=/tmp/mysql.sock"
Şimdilik bu kadar. Otomatikleştirilmiş araçları çalıştırmak için mucizeler beklemeyin - bu araçlar donanımınızın özelliklerini, hedeflerinizi ve ihtiyaçlarınızı veya makinede başka bir yazılım çalıştırıp çalıştırmadığınızı bilmiyor. Bunların hepsi önemli hususlardır - örneğin Metin sunucunuz aynı makinedeyse MySQL'e tüm belleği vermek iyi bir fikir değildir. Bunları, neyin değiştirilebileceğini ve sahip olduğu etkiyi anlamanıza yardımcı olması için kullanın ve önerileri körü körüne uygulamayın. Ve MySQL'deki bazı ayarları hatırlayın
Performans sorunlarınız varsa ve bunları çözemiyorsanız, varsayılan bir my.cnf kullanmayı veya yaptığınız tüm optimizasyonları silmeyi deneyin - bazen varsayılanlara geri dönmek en iyi seçenektir.
Her şeyden önce MySQL'in (en azından 5.6) veya MariaDB'nin (10.3 veya daha yeni) yeni bir sürümünü kullandığınızdan emin olun çünkü bunlar genellikle eski sürümlerden daha iyi performans göstermelidir.
MySQL'inizi yükseltmek kolaydır - sadece mevcut sürümü kaldırın (yani: pkg delete mysql55-server) ve yenisini kurun (pkg install mysql57-server). Şu anda yüklü olan sürümü pkg bilgisi ile bulabilirsiniz. İstemciyi ayrı olarak kaldırmanız ve yüklemeniz de gerekebilir.
MySQL'i kaldırdığınızda, verilerinizin silinmediğini ve bu nedenle çok farklı bir sürüme geçmediğiniz sürece yükseltilmiş sunucunuzu hemen başlatabilmeniz gerektiğini unutmayın - bu durumda mysql_upgrade'i daha yeni sürümler üzerinden çalıştırmanız gerekebilir. MySQL ve MariaDB bunu kendileri yapacak. Her şeye rağmen, önce bir yedekleme yapmak iyi bir fikirdir: ya fiziksel bir kopya (yani, dosyaları / var / db / mysql içindeki dosyaları başka bir yere kopyalayın, ancak tablo bozulmasını önlemek için önce mysql hizmetini durdurun!) - hala desteklenen sürümü, bir sorun olması durumunda tekrar kurabiliriz - veya yoksa mysqldump ile yedekleme.
Kendi my.cnf dosyanızı uzun süredir kullanıyorsanız, yeni bir sürüm yüklemek onu yeni varsayılanlarla değiştirmeyecektir, bu da mysql'nin başlamasını önleyecek eski ayarları burada kullanıyor olabileceğiniz anlamına gelir - sadece MySQL günlüğünüzü kontrol edin /var/db/mysql/<yourhostname>.err hizmeti başlattıktan sonra her şeyin yolunda olduğundan emin olmak için.
Artık yeni bir sürümü çalıştırdığımıza göre, optimizasyona doğru bir şekilde geçelim. Kendinize neyi optimize edeceğinizi nasıl bulacağınızı açıklamayacağım - Veritabanı Yönetimi dereceleri bunun için. Size sadece bazı yararlı araçlara işaret edeceğim. İlk olarak, InnoDB'ye olabildiğince çok tabloyu taşımanız önerilir. Navicat kullanıyorsanız ve kullanmasanız bile, bu çok önemsiz bir görevdir.
MySQL için ZFS ayarı
İyi miktarda InnoDB tablonuz varsa ve ZFS dosya sistemini kullanıyorsanız (örneğin OVH, varsayılan olarak ZFS'yi yükler) InnoDB tablolarının blok boyutuyla eşleşmeleri için belirli dosya sistemleri kurmak isteyebilirsiniz. Dikkat, bu prosedür yeni başlayanlar için tavsiye edilmez:
İlk olarak, bu ayarları [mysqld] bölümünün altındaki my.cnf'ye ekleyin (bu ayarlar zaten mevcutsa, uygun şekilde değiştirin)
innodb_data_home_dir = /var/db/mysql-innodb
innodb_log_group_home_dir = /var/db/mysql-innodb-logs
Çalışıyorsa mysql-server hizmetini durdurun ve ardından mysql dizininizi taşıyın:
mv / var / db / mysql / var / db / mysql-bak
Daha sonra kabukta InnoDB için özel dosya sistemlerimizi oluşturmak için şu komutları yürütün:
zfs create -o recordsize=16k -o primarycache=metadata zroot/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata zroot/var/db/mysql-innodb-logs
zfs create -o recordsize=8k zroot/var/db/mysql
Şimdi aşağıdaki dosyaları uygun konumlarına kopyalayalım (bunların hepsine sahip olmayabilirsiniz - sorun değil, o zaman atlayın)
cp /var/db/mysql-bak/ib_logfile* /var/db/mysql-innodb-logs/
cp /var/db/mysql-bak/ib_buffer_pool /var/db/mysql-innodb/
cp /var/db/mysql-bak/ibdata* /var/db/mysql-innodb/
cp /var/db/mysql-bak/ibtmp* /var/db/mysql-innodb/
cp -r /var/db/mysql-bak/* /var/db/mysql/
Şimdi mysql sunucusunu başlatın ve hata olmadığından emin olmak için günlükleri kontrol edin; sonra / var / db / mysql-bak klasörünü silebileceğinizden emin olduğunuzda.
Tuning araçları
Her şey sırayla. MySQL ile ilgili tüm araçlar için ana bilgisayar, kullanıcı ve şifre belirlemenin en kolay yolu, /root/.my.cnf adlı bir dosya oluşturmak ve diğer kullanıcıların şifremizi okuyamaması için ona chmod 600 vermektir.
[client]
user=root
password=whatever
port=3306
socket=/tmp/mysql.sock
Bu şekilde, aynı seçenekleri defalarca belirtmeye gerek kalmadan otomatik olarak oturum açacağız.
MySQLTuner
MySQLTuner, mysql sunucunuzun performansını artırmak için size uygulanabilir öneriler sunan popüler bir perl betiğidir. Şahsen ben Percona Toolkit'i tercih ederim, ancak çıktıyı okumak çok kolay olduğu için bu yeni başlayanlar için iyi bir başlangıç noktasıdır.
cd /root
fetch http://mysqltuner.pl
perl mysqltuner.pl
MySQL sunucumuzun kullanıcı ve şifresini girdikten sonra, bir dizi istatistik ve öneride bulunacaktır.
Daha fazla bilgi ve komut satırı seçenekleri için: https://github.com/major/MySQLTuner-perl
Percona Araç Seti
Percona Sunucusu, aslında, tıpkı MariaDB gibi, MySQL'in yerini alan bir düşüştür. Şahsen ben test etmedim çünkü FreeBSD desteği yarım yamalak görünüyor. Yine de, yardımcı araç paketi "percona-toolkit" için bir bağlantı noktası bulabiliriz, bu da MySQLTuner'dan daha kapsamlı öneriler sunar.
Bazıları linux komutlarına güvendiği ve hiç kimse bunları FreeBSD'ye uyarlamayı umursamadığı için (desteğin yarım yamalak olduğunu söylemiştim!), Setteki tüm araçlar çalışmıyor, ancak en kullanışlı olanlardan bazıları:
pt-duplicate-key-checker MySQL tablolarında yinelenen dizinleri ve yabancı anahtarları bulun.
pt-index-usage Günlükten sorguları okuyun ve dizinleri nasıl kullandıklarını
analiz edin pt-query-Digest MySQL sorgularını günlüklerden, işlem listesinden veya tcpdump'dan analiz edin
pt-table-usage Sorguların tabloları nasıl kullandığını analiz edin.
pt-variable-advisor MySQL değişkenlerini analiz edin ve olası problemler hakkında tavsiyede bulunun (MySQLTuner önerilerine benzer)
Yeni başlayanlar için en kullanışlı bulduğum iki araçla birlikte Percona Toolkit'in kullanım örnekleri. Dizin kullanımı yavaş bir sorgu günlüğünü okur ve kaldırılması gereken dizinleri önerirken, değişken danışmanı MySQLTuner'a benzer öneriler gösterir:
pt-index-usage -F /root/.my.cnf /var/db/mysql/slow.log
pt-variable-advisor "S=/tmp/mysql.sock"
Şimdilik bu kadar. Otomatikleştirilmiş araçları çalıştırmak için mucizeler beklemeyin - bu araçlar donanımınızın özelliklerini, hedeflerinizi ve ihtiyaçlarınızı veya makinede başka bir yazılım çalıştırıp çalıştırmadığınızı bilmiyor. Bunların hepsi önemli hususlardır - örneğin Metin sunucunuz aynı makinedeyse MySQL'e tüm belleği vermek iyi bir fikir değildir. Bunları, neyin değiştirilebileceğini ve sahip olduğu etkiyi anlamanıza yardımcı olması için kullanın ve önerileri körü körüne uygulamayın. Ve MySQL'deki bazı ayarları hatırlayın
Performans sorunlarınız varsa ve bunları çözemiyorsanız, varsayılan bir my.cnf kullanmayı veya yaptığınız tüm optimizasyonları silmeyi deneyin - bazen varsayılanlara geri dönmek en iyi seçenektir.
