Yalnızca InnoDB tabloları içeren ve yaklaşık 5 GB boyutunda yoğun bir veritabanım var. Veritabanı SSD diskler kullanan bir Debian sunucusunda çalışıyor ve maksimum bağlantı = 800 olarak ayarladım, bu da bazen sunucuyu doyuruyor ve durma noktasına getiriyor. Saniye başına ortalama sorgu yaklaşık 2.5K. Bu yüzden mümkün olan maksimum bağlantıya yer açmak için bellek kullanımını optimize etmem gerekiyor.
innodb_buffer_pool_size'ın toplam belleğin %80'ine kadar olması gerektiğine dair öneriler gördüm. Öte yandan tuning-primer betiğinden bu uyarıyı alıyorum:
Max Memory Ever Allocated : 91.97 G
Configured Max Per-thread Buffers : 72.02 G
Configured Max Global Buffers : 19.86 G
Configured Max Memory Limit : 91.88 G
Physical Memory : 94.58 G
İşte mevcut innodb değişkenlerim:
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 20971520000 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 524288000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 4 |
Konuyla ilgili olabilecek bir yan not: Drupal'dan (ayrı bir web sunucusunda bulunan) veritabanına büyük bir gönderi (örneğin 10KB'den fazla) eklemeye çalıştığımda, bunun sonsuza kadar sürdüğünü ve sayfanın doğru şekilde geri dönmediğini görüyorum.
Bunlarla ilgili olarak, optimum performans için innodb_buffer_pool_size'ımın ne olması gerektiğini merak ediyorum. Bu senaryo için bu ve diğer parametreleri en iyi şekilde ayarlamak için önerilerinizi takdir ediyorum.
Sizin innodb_buffer_pool_size çok büyük. Bunu 20971520000
olarak ayarlamışsınız. Bu 19,5135 GB eder. Eğer sadece 5GB InnoDB veriniz ve indeksleriniz varsa, o zaman sadece 8GB'a sahip olmalısınız. Bu bile çok yüksek olabilir.
İşte yapmanız gerekenler. Önce bu sorguyu çalıştırın
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
Bu size tüm InnoDB Veri ve Dizinlerine dayalı olarak %60 ek bir oranla RIBPS, Önerilen InnoDB Arabellek Havuzu Boyutunu verecektir.
Örneğin
mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
-> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
-> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
| 8 |
+-------+
1 row in set (4.31 sec)
mysql>
Bu çıktı ile /etc/my.cnf dosyasında aşağıdaki ayarları yaparsınız
[mysqld]
innodb_buffer_pool_size=8G
Ardından, service mysql restart
Yeniden başlattıktan sonra mysql'i bir veya iki hafta çalıştırın. Sonra bu sorguyu çalıştırın:
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;
Bu size InnoDB tarafından kaç GB gerçek bellek kullanıldığını verecektir Şu anda InnoDB Arabellek Havuzundaki veriler.
Bu konuda daha önce yazmıştım: https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181
Yeniden yapılandırmak, yeniden başlatmak ve bir hafta beklemek yerine bu DataGB
sorgusunu hemen şimdi çalıştırabilirsiniz.
Bu DataGB
değeri InnoDB Arabellek Havuzunun ne kadar büyük olması gerektiğine daha çok benzemektedir + (innodb_change_buffer_max_size içinde belirtilen yüzde). Bunun şu anda ayırdığınız 20000M'den çok daha az olacağından eminim. RAM'deki tasarruf, aşağıdaki gibi diğer şeyleri ayarlamak için kullanılabilir
Şuna dikkat etmek çok önemlidir: InnoDB zaman zaman innodb_buffer_pool_size değerinin %10 üzerinde ek bir değere ihtiyaç duyabilir. İşte MySQL Dokümantasyonu bu konuda ne diyor:
Bu değeri ne kadar büyük ayarlarsanız, erişim için o kadar az disk I/O'su gerekir tablolardaki veriler. Özel bir veritabanı sunucusunda bunu şu şekilde ayarlayabilirsiniz makine fiziksel bellek boyutunun %80'ine kadar. Geri ölçeklendirmeye hazır olun bu diğer sorunlar ortaya çıkarsa bu değer:
Fiziksel bellek için rekabet işletim sisteminde sayfalamaya neden olabilir sistem.
InnoDB tamponlar ve kontrol yapıları için ek bellek ayırır, böylece tahsis edilen toplam alan aşağıdakilerden yaklaşık %10 daha fazla olur belirtilen boyut.
Adres alanı bitişik olmalıdır, bu da Windows'ta bir sorun olabilir belirli adreslere yüklenen DLL'lere sahip sistemler.
Tampon havuzunu başlatma süresi kabaca tampon havuzunun boyut. Büyük kurulumlarda, bu başlatma süresi önemli. Örneğin, modern bir Linux x86_64 sunucusunda, 10GB'lık bir tampon havuzunun başlatılması yaklaşık 6 saniye sürer. Bkz. Bölüm 8.9.1, "InnoDB Tampon Havuzu".
my.cnf` dosyanızda aşağıdaki değerleri görüyorum
| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |
Bu sayı InnoDB'nin birden fazla çekirdeğe erişmesini engelleyecektir
Lütfen aşağıdakileri ayarlayın:
[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
Bu konu hakkında daha önce DBA StackExchange'de yazmıştım
12 Eylül 2011
: https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-then-one-core/5670#567020 Eylül 2011
: https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance/5968#5968Az önce ServerFault'ta buna benzer bir soruyu daha kısa bir formül kullanarak yanıtladım:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1.25 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
Başlığınız innodb_buffer_pool_size hakkında soru soruyor, ancak asıl sorunun bu olmadığından şüpheleniyorum. (Rolando neden yeterince büyük, hatta çok büyük ayarladığınızı yorumladı).
Maksimum bağlantı = 800 olarak ayarladım, bu da bazen sunucuyu doyuruyor ve durma noktasına getiriyor.
Bu belli değil. 800 kullanıcının "Uyku" modunda olmasının sistem üzerinde neredeyse sıfır etkisi vardır. 800 aktif iş parçacığı bir felaket olur. Kaç tane iş parçacığı "çalışıyor"?
İplikler birbirini engelliyor mu? Kilitlenmeler vb. hakkında bazı ipuçları için SHOW ENGINE INNODB STATUS bölümüne bakın.
Slowlog'da herhangi bir sorgu görünüyor mu? Hadi onları optimize edelim.
Hangi sürümü kullanıyorsunuz? XtraDB (InnoDB'nin yerini alan bir sürüm) çoklu çekirdek kullanımı konusunda daha iyi bir iş çıkarıyor. 5.6.7 daha da iyi bir iş çıkarıyor.
innodb_buffer_pool_instances -- bunu 8 olarak değiştirin (20G buffer_pool varsayarak); Mutex çekişmesini biraz azaltacaktır.
I/O'ya mı bağlısınız yoksa CPU'ya mı bağlısınız? Cevabınıza bağlı olarak çözümler kökten farklıdır.
SSD -- Tüm günlük dosyalarının SSD olmayan sürücülerde olması daha iyi olabilir.
Daha fazla bellek her zaman daha iyidir, ancak deneyimlerime göre çoğu zaman tampon havuzu boyutu veri boyutunuza uymamalıdır. Yedek tablolar gibi birçok tablo çoğu zaman etkin değildir, bu nedenle innodb tampon havuzu boyutu, etkin veri boyutunuza uymalıdır.
Aktif sayfalar için belirlediğiniz zaman dilimi performansı etkiler, ancak daha büyük bir tampon boyutu için daha fazla performans elde edemeyeceğiniz optimum bir nokta vardır. Bunu show engine innodb status
ile tahmin edebilir/hesaplayabilir/ölçebilirsiniz.