Ich habe eine beschäftigte Datenbank mit ausschließlich InnoDB-Tabellen, die etwa 5 GB groß ist. Die Datenbank läuft auf einem Debian-Server mit SSD-Platten und ich habe max Verbindungen = 800, die manchmal sättigen und schleifen den Server zum Stillstand. Die durchschnittliche Abfrage pro Sekunde ist etwa 2,5K. Ich muss also die Speichernutzung optimieren, um Platz für die maximal möglichen Verbindungen zu schaffen.
Ich habe Vorschläge gesehen, dass innodb_buffer_pool_size bis zu %80 des gesamten Speichers betragen sollte. Andererseits erhalte ich diese Warnung vom tuning-primer-Skript:
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
Hier sind meine aktuellen innodb-Variablen:
| 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 |
Eine Nebenbemerkung, die relevant sein könnte: Wenn ich versuche, einen großen Beitrag (z. B. über 10 KB) von Drupal (das auf einem separaten Webserver liegt) in die Datenbank einzufügen, dauert es ewig und die Seite wird nicht korrekt zurückgegeben.
In Bezug auf diese, ich frage mich, was sollte meine innodb_buffer_pool_size für optimale Leistung sein. Ich schätze Ihre Vorschläge, um diese und andere Parameter optimal für dieses Szenario eingestellt.
Ihre innodb_buffer_pool_size ist enorm. Sie haben ihn auf 20971520000
eingestellt. Das sind 19,5135 GB. Wenn Sie nur 5 GB InnoDB-Daten und -Indizes haben, dann sollten Sie nur etwa 8 GB haben. Selbst das könnte zu hoch sein.
So sollten Sie vorgehen. Führen Sie zunächst diese Abfrage aus
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;
Damit erhalten Sie die RIBPS, die empfohlene InnoDB-Pufferpoolgröße auf der Grundlage aller InnoDB-Daten und -Indizes mit einem Zuschlag von 60 %.
Zum Beispiel
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>
Mit dieser Ausgabe würden Sie Folgendes in /etc/my.cnf einstellen
[mysqld]
innodb_buffer_pool_size=8G
Als Nächstes: "service mysql restart".
Nach dem Neustart lassen Sie mysql für ein oder zwei Wochen laufen. Führen Sie dann diese Abfrage aus:
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;
Das wird Ihnen zeigen, wie viele GB Speicher tatsächlich von InnoDB benutzt werden Daten im InnoDB-Pufferpool in diesem Moment belegt sind.
Ich habe schon einmal darüber geschrieben: https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181
Sie können diese `DataGB'-Abfrage auch gleich ausführen, anstatt neu zu konfigurieren, neu zu starten und eine Woche zu warten.
Dieser Wert DataGB
entspricht eher der Größe des InnoDB Buffer Pools + (Prozentsatz angegeben in innodb_change_buffer_max_size). Ich bin mir sicher, dass dies weit weniger als die 20000M sein wird, die Sie im Moment reserviert haben. Die Einsparungen an RAM können für das Tuning anderer Dinge verwendet werden, wie
Dies ist sehr wichtig zu beachten: Manchmal kann InnoDB zusätzliche 10% über dem Wert für innodb_buffer_pool_size verlangen. Hier ist, was die MySQL-Dokumentation zu diesem Thema sagt:
Je größer Sie diesen Wert setzen, desto weniger Festplatten-E/A ist erforderlich, um auf Daten in Tabellen zuzugreifen. Auf einem dedizierten Datenbankserver können Sie diesen Wert auf bis zu bis zu 80 % der physischen Speichergröße der Maschine. Seien Sie bereit, diesen Wert zu reduzieren diesen Wert zu reduzieren, wenn diese anderen Probleme auftreten:
Der Wettbewerb um physischen Speicher kann zu Paging im Betriebssystem führen. System verursachen.
InnoDB reserviert zusätzlichen Speicher für Puffer und Kontrollstrukturen, so dass der gesamte zugewiesene Speicherplatz etwa 10% größer ist als die angegebene Größe.
Der Adressraum muss zusammenhängend sein, was unter Windows ein Problem darstellen kann Systemen mit DLLs, die an bestimmten Adressen geladen werden, ein Problem darstellen kann.
Die Zeit zur Initialisierung des Pufferpools ist ungefähr proportional zu seiner Größe. Bei großen Installationen kann diese Initialisierungszeit erheblich sein. Zum Beispiel auf einem modernen Linux x86_64 Server, Die Initialisierung eines 10 GB großen Pufferpools dauert etwa 6 Sekunden. Siehe Abschnitt 8.9.1, "Der InnoDB-Pufferpool".
Ich sehe die folgenden Werte in Ihrer my.cnf
| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |
Diese Zahlen verhindern, dass InnoDB auf mehrere Kerne zugreifen kann
Bitte stellen Sie Folgendes ein:
[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
Ich habe bereits im DBA StackExchange über dieses Thema geschrieben
Mai 26, 2011
: https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance/2948#2948Sep 12, 2011
: https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-then-one-core/5670#5670Sep 20, 2011
: https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance/5968#5968Ich habe gerade beantwortet eine Frage wie diese in ServerFault mit einer prägnanteren Formel:
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;
Dein Titel fragt nach innodb_buffer_pool_size, aber ich vermute, dass das nicht das eigentliche Problem ist. (Rolando kommentierte, warum Sie es groß genug, sogar zu groß eingestellt haben.)
Ich habe max connections = 800 eingestellt, was manchmal zu einer Sättigung führt und den Server zum Stillstand bringt.
Das ist unklar. 800 Benutzer im "Sleep" Modus haben praktisch keinen Einfluss auf das System. 800 aktive Threads wären eine Katastrophe. Wie viele Threads werden ausgeführt?
Blockieren sich die Threads gegenseitig? Unter SHOW ENGINE INNODB STATUS finden Sie einige Hinweise auf Deadlocks usw.
Tauchen irgendwelche Abfragen im Slowlog auf? Lassen Sie uns diese optimieren.
Welche Version verwenden Sie? XtraDB (ein Drop-in-Ersatz für InnoDB) nutzt mehrere Kerne besser aus. 5.6.7 leistet sogar noch bessere Arbeit.
innodb_buffer_pool_instances -- ändern Sie dies auf 8 (unter der Annahme eines 20G buffer_pools); dies wird die Mutex-Konkurrenz etwas reduzieren.
Sind Sie I/O gebunden oder CPU gebunden? Abhängig von Ihrer Antwort sind die Lösungen völlig unterschiedlich.
SSD -- Es könnte besser sein, wenn alle Protokolldateien auf Nicht-SSD-Laufwerken liegen würden.
Mehr Speicher ist immer besser, aber meiner Erfahrung nach sollte die Größe des Pufferpools in den meisten Fällen nicht zur Datengröße passen. Viele Tabellen sind die meiste Zeit inaktiv, wie z.B. herumliegende Backup-Tabellen, so dass die Größe des innodb Pufferpools eher zu Ihrer aktiven Datengröße passen sollte.
Der Zeitrahmen, den Sie für aktive Seiten angeben, beeinflusst die Leistung, aber es gibt einen optimalen Punkt, an dem Sie nicht mehr Leistung für eine größere Puffergröße erhalten. Sie können das mit show engine innodb status
abschätzen/berechnen/messen