MySQL: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
Rkr (Diskussion | Beiträge) |
Rkr (Diskussion | Beiträge) |
||
(4 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 2: | Zeile 2: | ||
== MySQL Performance Tuning == | == MySQL Performance Tuning == | ||
Die folgenden MySQL Parameter sollte man in | Die folgenden MySQL Parameter sollte man in ''/etc/my.cnf'' '''mindestens''' eintragen, wenn man 1-2 GB RAM hat. | ||
<pre> | <pre> | ||
Zeile 8: | Zeile 8: | ||
sort_buffer_size = 4M | sort_buffer_size = 4M | ||
read_buffer_size = 1M | read_buffer_size = 1M | ||
table_open_cache = 256 | |||
query_cache_size = 64M | query_cache_size = 64M | ||
thread_cache_size = 4 | |||
innodb_buffer_pool_size = 64M | |||
innodb_additional_mem_pool_size = 10M | |||
</pre> | </pre> | ||
Oder bei mehr RAM im System bzw. für größere Tabellen: | |||
<pre> | <pre> | ||
innodb_buffer_pool_size = | key_buffer_size = 256M | ||
sort_buffer_size = 32M | |||
read_buffer_size = 1M | |||
read_rnd_buffer_size = 512K | |||
table_open_cache = 256 | |||
query_cache_size = 32M | |||
thread_cache_size = 4 | |||
innodb_buffer_pool_size = 256M | |||
innodb_additional_mem_pool_size = 10M | |||
innodb_log_buffer_size = 8M | |||
</pre> | |||
Bei 8GB RAM wäre auch möglich: | |||
<pre> | |||
key_buffer_size = 2048M | |||
sort_buffer_size = 128M | |||
read_buffer_size = 5M | |||
read_rnd_buffer_size = 2M | |||
table_open_cache = 768 | |||
query_cache_size = 256M | |||
thread_cache_size = 4 | |||
tmp_table_size = 1024M | |||
max_heap_table_size = 1024M | |||
innodb_buffer_pool_size = 256M | |||
innodb_additional_mem_pool_size = 10M | innodb_additional_mem_pool_size = 10M | ||
innodb_log_file_size = 32M | |||
innodb_log_buffer_size = 8M | |||
</pre> | </pre> | ||
Referenz: | Referenz: | ||
*http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html | |||
*http://forums.cacti.net/viewtopic.php?t=4826 | *http://forums.cacti.net/viewtopic.php?t=4826 | ||
*http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html | *http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html | ||
*http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html | |||
*http://dev.mysql.com/doc/refman/4.1/en/memory-use.html | *http://dev.mysql.com/doc/refman/4.1/en/memory-use.html | ||
*http://dev.mysql.com/doc/refman/4.1/en/query-cache-configuration.html | *http://dev.mysql.com/doc/refman/4.1/en/query-cache-configuration.html | ||
===TCP connection settings=== | |||
<pre> | |||
max_connections = 250 | |||
wait_timeout = 3600 | |||
</pre> | |||
== Weblinks == | == Weblinks == |
Aktuelle Version vom 14. Januar 2014, 12:37 Uhr
MySQL ist eine populäre Open Source SQL Datenbank, welche z.B. für Mediawiki, Cacti und Wordpress benötigt wird.
MySQL Performance Tuning
Die folgenden MySQL Parameter sollte man in /etc/my.cnf mindestens eintragen, wenn man 1-2 GB RAM hat.
key_buffer_size = 64M sort_buffer_size = 4M read_buffer_size = 1M table_open_cache = 256 query_cache_size = 64M thread_cache_size = 4 innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 10M
Oder bei mehr RAM im System bzw. für größere Tabellen:
key_buffer_size = 256M sort_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 512K table_open_cache = 256 query_cache_size = 32M thread_cache_size = 4 innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 10M innodb_log_buffer_size = 8M
Bei 8GB RAM wäre auch möglich:
key_buffer_size = 2048M sort_buffer_size = 128M read_buffer_size = 5M read_rnd_buffer_size = 2M table_open_cache = 768 query_cache_size = 256M thread_cache_size = 4 tmp_table_size = 1024M max_heap_table_size = 1024M innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 10M innodb_log_file_size = 32M innodb_log_buffer_size = 8M
Referenz:
- http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
- http://forums.cacti.net/viewtopic.php?t=4826
- http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html
- http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
- http://dev.mysql.com/doc/refman/4.1/en/memory-use.html
- http://dev.mysql.com/doc/refman/4.1/en/query-cache-configuration.html
TCP connection settings
max_connections = 250 wait_timeout = 3600
Weblinks
Siehe auch
- Backup von MySQL Datenbanken mit rsnapshot