Kurzes Howto um die Server Performance zu optimieren:
Wichtig: um die Werte zu erhöhen, sollte generell genügend Memory frei sein.. der CPU Load & die Disk Auslastung werden sich bedanken..
Als erstes braucht ihr das ShellScript: https://launchpad.net/mysql-tuning-primer/+download
Danach chmod 755 und das Script aufrufen ./tuning-primer.sh
Nun erhaltet ihr eine Ausgabe (wichtig: der mySQL Server sollte nicht frisch gestartet sein um verwendbare Werte zu liefern.. ich empfehle mind 24h Uptime)
Danach einfach nach Scriptausgabe optimieren und Werte vorsichtig anpassen in den my.cnf:
SLOW QUERIES Current long_query_time = 2 sec. You have 0 out of 40657 that take longer than 2 sec. to complete The slow query log is enabled. Your long_query_time seems to be fine WORKER THREADS Current thread_cache_size = 0 Current threads_cached = 0 Current threads_per_sec = 1 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 25 Current threads_connected = 1 Historic max_used_connections = 5 The number of used connections is 20% of the configured maximum. Your max_connections variable seems to be fine. MEMORY USAGE Max Memory Ever Allocated : 9 M Configured Max Per-thread Buffers : 26 M Configured Max Global Buffers : 4 M Configured Max Memory Limit : 31 M Total System Memory : 10 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 1 M Current key_buffer_size = 1 M Key cache miss rate is 1 : 190 Key buffer fill ratio = 6.00 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 512 K Current query_cache_used = 364 K Current query_cach_limit = 1 M Current Query cache fill ratio = 71.28 % MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 512 K Current record/read_rnd_buffer_size = 256 K Sort buffer seems to be fine JOINS Current join_buffer_size = 132.00 K You have had 0 queries where a join could not use an index properly Your joins seem to be using indexes properly OPEN FILES LIMIT Current open_files_limit = 1024 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 256 tables You have a total of 227 tables You have 50 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 32 M Of 1091 temp tables, 84% were created on disk Effective in-memory tmp_table_size is limited to max_heap_table_size. Perhaps you should increase your max_heap_table_size and/or tmp_table_size. to reduce the number of disk-based temporary tables TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 3409 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 424 You may benefit from selective use of InnoDB. If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1
