Skip to main content

Site links

  • Agency for web development
  • Content Management with Primer
  • Open Source Leadership with Drupal
Language
DeutschEnglish

Secondary navigation

  • Contact
Agentur für Webentwicklung

Hauptnavigation

  • References
  • Services
  • Blog
  • About us

Site links

  • Agency for web development
  • Content Management with Primer
  • Open Source Leadership with Drupal
close

Breadcrumb

  1. Home
  2. Blog
  3. Techblog Archive

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…

 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.

About MD Systems

MD Systems, headquartered in Zurich, is a unique team of international open source initiative leaders for the Drupal content management system.

With our experts for software architecture and design and our industry solutions, you digitize your organization successfully and efficiently.

MD Systems GmbH

Hermetschloostrasse 77, CH-8048 Zürich

Schweiz

+41 44 500 45 95

[email protected]

Fußzeile

  • Contact
  • Impressum
  • Data protection
To top

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