Direkt zum Inhalt
  • Agentur für web development
  • Content Management mit Primer
  • Open Source Leadership mit Drupal
    Sprache
  • Deutsch
  • English
  • Kontakt
Logo der Website
Agentur für Webentwicklung
  • Referenzen
  • Angebot
  • Aktuelles
  • Über uns
  • Agentur für web development
  • Content Management mit Primer
  • Open Source Leadership mit Drupal
close
×

Info message

This content has not been translated to Deutsch yet. You are reading the English version.

Improve MySQL Performance

15. February 2012
Sascha Grossenbacher Porträt
Sascha Grossenbacher

Below is an example configuration file to tune MySQL a bit which you can use as a starting point. This will tell MySQL to use a lot more RAM than the default configuration but allows to keep a lot of the data in memory, which makes it much faster. On Debian/Ubuntu, I usually place this in /etc/mysql/conf.d/tuning.conf. On other systems, you can append it to the my.conf.

[mysqld]
# Amount of simultaneous connections
max_connections=100
# Total query cache size
query_cache_size=128M
# Size limit for a single query to be cached
query_cache_limit=1M
# Number of tables kept in cache
table_open_cache=3000
# Number of table defiinitions in cache
table_definition_cache=2500
# Size of temporary tables kept in memory
tmp_table_size=150M
# Max size of HEAP tables kept in memory
max_heap_table_size=150M
# Size of key/index caches
key_buffer_size=96M
# Size of Join caches
join_buffer_size=128K

# Log slow queries
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 5

# Size of InnoDB buffer. If you have a dedicated DB server with a lot of
# memory, you can make this as big as you can.. preferable big enough to fit your complete data.
innodb_buffer_pool_size=512M
# If you do not need full ACID compliance, this can be set 0. It means that you will loose data for
# the last 1-2 seconds but makes the system much faster.
innodb_flush_log_at_trx_commit=0
# Log file size. Careful when changing this, stop mysql first.
innodb_log_file_size=512M

# Various performance settings. See documentation for more information.
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

# Skips the slow DNS resolves.
skip-name-resolve

Important: When changing innodb_log_time_size, see http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely

 Note that this is just an example. You will need to fine-tune the exact settings based on the amount of data and amount of spare memory you have. A very helpful tool for this is https://github.com/rackerhacker/MySQLTuner-perl. Add the above example script, let your server run for a few days and then run the script which will tell you how much of the assigned resources are in use and will also give you a few tips on how to improve it.

Hol Dir den Newsletter

Jetzt für unseren Newsletter anmelden und monatlich wichtige Insights aus der Branche und MD Systems erhalten. 

Zur Anmeldung

Über MD Systems

MD Systems mit Firmensitz Zürich ist ein einzigartiges Team von internationalen Open Source Initiative Leadern für das Content Management System Drupal.

Wir begleiten Sie von der Idee und Konzeption über die Realisation bis hin zur Einführung, Betrieb und laufenden Optimierung.

MD Systems GmbH

Hermetschloostrasse 77, CH-8048 Zürich

Schweiz

+41 44 500 45 95

[email protected]

  • Kontakt
  • Impressum
  • Datenschutz
To top

© Copyright 2023 - 2024 MD Systems GmbH. Alle Rechte vorbehalten. Erstellt mit PRIMER - powered by Drupal.