- 7.5 Optimierung des MySQL Servers
- 7.5.1 System/Kompilierzeitpunkt und Tuning der Startparameter
- 7.5.2 Serverparameter feineinstellen
- 7.5.3 Leistung des Abfragenoptimierers steuern
- 7.5.4 Wie Kompilieren und Linken die Geschwindigkeit von MySQL beeinflusst
- 7.5.5 Wie MySQL Speicher benutzt
- 7.5.6 Wie MySQL DNS benutzt
Mit dem folgenden Befehl können Sie die standardmäßigen Puffergrößen festlegen, die der Server mysqld verwendet:
shell> mysqld --verbose --help
Dieser Befehl erzeugt eine Liste aller mysqld-Optionen und konfigurierbarer Systemvariablen. Die Ausgabe enthält die Standardwerte der Variablen und sieht etwa so aus:
help TRUE abort-slave-event-count 0 allow-suspicious-udfs FALSE auto-increment-increment 1 auto-increment-offset 1 automatic-sp-privileges TRUE basedir /home/jon/bin/mysql/ bdb FALSE bind-address (No default value) character-set-client-handshake TRUE character-set-server latin1 character-sets-dir /home/jon/bin/mysql/share/mysql/charsets/ chroot (No default value) collation-server latin1_swedish_ci completion-type 0 concurrent-insert 1 console FALSE datadir /home/jon/bin/mysql/var/ default-character-set latin1 default-collation latin1_swedish_ci default-time-zone (No default value) disconnect-slave-event-count 0 enable-locking FALSE enable-pstack FALSE engine-condition-pushdown FALSE external-locking FALSE gdb FALSE large-pages FALSE init-connect (No default value) init-file (No default value) init-slave (No default value) innodb TRUE innodb_checksums TRUE innodb_data_home_dir (No default value) innodb_doublewrite TRUE innodb_fast_shutdown 1 innodb_file_per_table FALSE innodb_flush_log_at_trx_commit 1 innodb_flush_method (No default value) innodb_locks_unsafe_for_binlog FALSE innodb_log_arch_dir (No default value) innodb_log_group_home_dir (No default value) innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_status_file FALSE innodb_table_locks TRUE innodb_support_xa TRUE isam FALSE language /home/jon/bin/mysql/share/mysql/english local-infile TRUE log /home/jon/bin/mysql/var/master1.log log-bin /home/jon/bin/mysql/var/master1 log-bin-index (No default value) log-bin-trust-routine-creators FALSE log-error /home/jon/bin/mysql/var/master1.err log-isam myisam.log log-queries-not-using-indexes FALSE log-short-format FALSE log-slave-updates FALSE log-slow-admin-statements FALSE log-slow-queries (No default value) log-tc tc.log log-tc-size 24576 log-update (No default value) log-warnings 1 low-priority-updates FALSE master-connect-retry 60 master-host (No default value) master-info-file master.info master-password (No default value) master-port 3306 master-retry-count 86400 master-ssl FALSE master-ssl-ca (No default value) master-ssl-capath (No default value) master-ssl-cert (No default value) master-ssl-cipher (No default value) master-ssl-key (No default value) master-user test max-binlog-dump-events 0 memlock FALSE myisam-recover OFF ndbcluster FALSE ndb-connectstring (No default value) ndb-mgmd-host (No default value) ndb-nodeid 0 ndb-autoincrement-prefetch-sz 32 ndb-distibution KEYHASH ndb-force-send TRUE ndb_force_send TRUE ndb-use-exact-count TRUE ndb_use_exact_count TRUE ndb-shm FALSE ndb-optimized-node-selection TRUE ndb-cache-check-time 0 ndb-index-stat-enable TRUE ndb-index-stat-cache-entries 32 ndb-index-stat-update-freq 20 new FALSE old-alter-table FALSE old-passwords FALSE old-style-user-limits FALSE pid-file /home/jon/bin/mysql/var/hostname.pid1 port 3306 relay-log (No default value) relay-log-index (No default value) relay-log-info-file relay-log.info replicate-same-server-id FALSE report-host (No default value) report-password (No default value) report-port 3306 report-user (No default value) rpl-recovery-rank 0 safe-user-create FALSE secure-auth FALSE server-id 1 show-slave-auth-info FALSE skip-grant-tables FALSE skip-slave-start FALSE slave-load-tmpdir /tmp/ socket /tmp/mysql.sock sporadic-binlog-dump-fail FALSE sql-mode OFF symbolic-links TRUE tc-heuristic-recover (No default value) temp-pool TRUE timed_mutexes FALSE tmpdir (No default value) use-symbolic-links TRUE verbose TRUE warnings 1 back_log 50 binlog_cache_size 32768 bulk_insert_buffer_size 8388608 connect_timeout 5 date_format (No default value) datetime_format (No default value) default_week_format 0 delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 expire_logs_days 0 flush_time 0 ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (No default value) group_concat_max_len 1024 innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 8388608 innodb_concurrency_tickets 500 innodb_file_io_threads 4 innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_sync_spin_loops 20 innodb_thread_concurrency 20 innodb_commit_concurrency 0 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8388600 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 long_query_time 10 lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10 max_connections 100 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_join_size 4294967295 max_length_for_sort_data 1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 multi_range_count 256 myisam_block_size 1024 myisam_data_pointer_size 6 myisam_max_extra_sort_file_size 2147483648 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 optimizer_prune_level 1 optimizer_search_depth 62 preload_buffer_size 32768 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type 1 query_cache_wlock_invalidate FALSE query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 131072 read_only FALSE read_rnd_buffer_size 262144 div_precision_increment 4 record_buffer 131072 relay_log_purge TRUE relay_log_space_limit 0 slave_compressed_protocol FALSE slave_net_timeout 3600 slave_transaction_retries 10 slow_launch_time 2 sort_buffer_size 2097144 sync-binlog 0 sync-frm TRUE sync-replication 0 sync-replication-slave-id 0 sync-replication-timeout 10 table_open_cache 64 table_lock_wait_timeout 50 thread_cache_size 0 thread_concurrency 10 thread_stack 196608 time_format (No default value) tmp_table_size 33554432 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 updatable_views_with_limit 1 wait_timeout 28800
Wird gerade ein mysqld-Server ausgeführt, dann können Sie die aktuellen Werte seiner Systemvariablen anzeigen, indem Sie eine Verbindung mit ihm herstellen und die folgende Anweisung absetzen:
mysql> SHOW VARIABLES;
Mit der folgenden Anweisung können Sie statistische Informationen und Statusangaben für einen laufenden Server abrufen:
mysql> SHOW STATUS;
Angaben zu Systemvariablen und Status erhalten Sie auch mit mysqladmin:
shell>mysqladmin variablesshell>mysqladmin extended-status
Eine vollständige Beschreibung aller System- und Statusvariablen finden Sie in Abschnitt 5.2.2, „Server-Systemvariablen“, und Abschnitt 5.2.4, „Server-Statusvariablen“.
MySQL verwendet Algorithmen, die sehr gut skalierbar sind, d. h., Sie können MySQL gewöhnlich mit sehr wenig Speicher ausführen. Andererseits erhalten Sie eine umso bessere Performance, je mehr Speicher Sie MySQL zugestehen.
Wenn Sie einen MySQL Server optimieren, dann sind die beiden
wichtigsten zu konfigurierenden Variablen
key_buffer_size und
table_open_cache. Sie sollten erst ganz
sicher sein, dass Sie für diese Variablen geeignete Werte
eingestellt haben, bevor Sie andere Variablen zu konfigurieren
versuchen.
Die folgenden Beispiele zeigen einige typische Variablenwerte für verschiedene Laufzeitkonfigurationen.
-
Wenn Sie mindestens 256 Mbyte Speicher und viele Tabellen haben und für eine moderate Anzahl von Clients die optimale Leistung erzielen wollen, sollten Sie einmal Folgendes probieren:
shell>
mysqld_safe --key_buffer_size=64M --table_open_cache=256 \--sort_buffer_size=4M --read_buffer_size=1M & -
Haben Sie nur 128 Mbyte Speicher und nur ein paar Tabellen, die aber häufig sortiert werden müssen, dann können Sie Folgendes ausprobieren:
shell>
mysqld_safe --key_buffer_size=16M --sort_buffer_size=1MBei sehr vielen gleichzeitigen Problemen können Auslagerungsprobleme auftreten, sofern mysqld nicht für die Verwendung von sehr wenig Speicher für die jeweilige Verbindung konfiguriert wurde. mysqld arbeitet besser, wenn Sie genug Speicher für alle Verbindungen haben.
-
Bei sehr wenig Speicher und vielen Verbindungen verwenden Sie Folgendes:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \--read_buffer_size=100K &Oder sogar Folgendes:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \--table_open_cache=32 --read_buffer_size=8K \--net_buffer_length=1K &
Wenn Sie GROUP BY- oder ORDER
BY-Operationen an Tabellen durchführen, die
wesentlich größer sind als der verfügbare Speicher, dann
sollten Sie den Wert von read_rnd_buffer_size
erhöhen, um nach Durchführung von Sortieroperationen das
Auslesen von Datensätzen zu beschleunigen.
Wenn Sie MySQL installiert haben, enthält das Verzeichnis
support-files eine Anzahl verschiedener
my.cnf-Beispieldateien, nämlich
my-huge.cnf,
my-large.cnf,
my-medium.cnf und
my-small.cnf. Diese können Sie als Basis
zur Optimierung Ihres Systems verwenden. (Unter Windows schauen
Sie im MySQL-Installationsverzeichnis nach.)
Wenn Sie für mysqld oder mysqld_safe eine Option auf der Befehlszeile angeben, ist diese nur für den betreffenden Aufruf des Servers gültig. Um diese Option jedes Mal bei Ausführung des Servers zu verwenden, müssen Sie sie in einer Optionsdatei ablegen.
Um die Wirkung einer Parameteränderung zu prüfen, können Sie Folgendes tun:
shell> mysqld --key_buffer_size=32M --verbose --help
Die Variablenwerte sind kurz vor Ende der Ausgabe aufgeführt.
Vergewissern Sie sich, dass die Optionen
--verbose und --help am Ende
stehen. Andernfalls werden die Auswirkungen von Optionen, die
nach diesen beiden Optionen auf der Befehlszeile angegeben
wurden, in der Ausgabe nicht berücksichtigt.
Informationen zur Optimierung der
InnoDB-Speicher-Engine finden Sie in
Abschnitt 14.2.11, „Tipps zur Leistungssteigerung“.
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.
