Zabawy z tuningowaniem mysql

Wpis powiązany z Projektem: 

Od pewnego czasu zastanawiam się czy pod basoofkę potrzebny jest aż tak duży (i drogi) serwer. Chętnie wymieniłbym go na zwykły hosting, nad którym czuwa jakiś kompetentny admin. Przyzwyczaiłem się w sumie, że linuksa nigdy nie poznam tak głęboko, aby siłą woli zmieniać konfig Apache i MySQL. No ale zmiana hostingu to nie kaszka z mleczkiem, zatem na początek spróbuję zasięgnąć porady:

ciekaw jestem czy spoglądając w statystyki Webalizer - http://garbage.basoofka.net/webalizerstaty/basoofka/

oraz mając dane z MySQL Tunera oraz pliku my.cnf (oba poniżej) ktoś by coś mógł doradzić? Ja niestety niewiele kumam z tego wszystkiego:

MYSQL TUNER

Queries
Uptime in seconds: 3828771
Uptime: 44d 7h 32m 51s
Questions: 426514672
% slow queries: 0.159734715996
slow query rate: 0.00360457166596 per day
Long query time: 3
Slow query logging: ON
% reads: 88.2076593477
% writes: 11.7923406523
qps: 111.397279179
reads per sec: 0.0199049297219 per day
writes per sec: 0.00266105816294 per day
Queries: 111.397279179 per second
Connections: 1 Million
Bytes sent: 2 Billion
Bytes received: 2 Billion

versions
Supported Version: 5
Release Series: 5.0
version less then 5.1, upgrade!
(substr("version",0,3) ne "5.1")
(substr("5.0.51a-24+lenny3-log",0,3) '5.0' !== "5.1")
Minor Version: 51
Distribution: (Debian)
Distribution: (Debian)

MySQL Architecture: i486
MySQL is not compiled as a 64-bit package.
("version_compile_machine" !~ /64/)
("i486" dbtuner_strnistr('i486 ', array('64')))

Query cache
Query cache efficiency (%): 75.3804166758
% query cache used: 43.8676595688
The query cache is not being fully utilized.
(Qcache_free_memory / query_cache_size * 100 <80)
(14719544 / 33554432 * 100 43.8676595688<80)
Query cache low memory prunes: 12.9738012537 per second
Increase query_cache_size -- there are too many low memory prunes.
(&hr_bytime(Qcache_lowmem_prunes/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(49673714/3828771) dbtuner_stristr('12.9738012537 per second', array('second', 'minute')))
Query cache size: 32.0 Mb
Query cache min result size: 8.0 Mb

Sorts
Total sorts: 20249445
% sorts that cause temporary tables: 0.0339169789592
rate of sorts that cause temporary tables: 6.45763353306 per hour
sort_buffer_size: 2.0 Mb
read_rnd_buffer_size: 256.0 Kb
Sort rows: 232.052340294 per second
There are lots of rows being sorted. Consider using indexes in more queries to avoid sorting too often.
(&hr_bytime(Sort_rows/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(888475271/3828771) dbtuner_stristr('232.052340294 per second', array('second', 'minute')))

Joins,scans
rate of joins without indexes: 10.1494448219 per minute
There are too many joins without indexes -- this means that joins are doing full table scans.
(&hr_bytime((Select_range_check + Select_scan + Select_full_join)/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime((0 + 647665 + 0)/3828771) dbtuner_stristr('10.1494448219 per minute', array('second', 'minute')))
rate of reading first index entry: 1.06467897923 per second
The rate of reading the first index entry is high; this usually indicates frequent full index scans.
(&hr_bytime(Handler_read_first/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(4076412/3828771) dbtuner_stristr('1.06467897923 per second', array('second', 'minute')))
rate of reading fixed position: 209.262576947 per second
The rate of reading data from a fixed position is high; this indicates many queries need to sort results and/or do a full table scan, including join queries that do not use indexes.
(&hr_bytime(Handler_read_rnd/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(801218486/3828771) dbtuner_stristr('209.262576947 per second', array('second', 'minute')))
rate of reading next table row: 503.231692885 per second
The rate of reading the next table row is high; this indicates many queries are doing full table scans.
(&hr_bytime(Handler_read_rnd_next/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(1926758912/3828771) dbtuner_stristr('503.231692885 per second', array('second', 'minute')))

temp tables
tmp_table_size-max_heap_table_size: 16777216
tmp_table_size and max_heap_table_size are not the same.
(tmp_table_size-max_heap_table_size !=0)
(33554432-16777216 16777216!=0)
tmp_table_size: 32.0 Mb
max_heap_table_size: 16.0 Mb
% temp disk tables: 14.9096252394
temp disk rate: 44.9003766483 per minute
Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size.
(&hr_bytime(Created_tmp_disk_tables/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(2865221/3828771) dbtuner_stristr('44.9003766483 per minute', array('second', 'minute')))
temp table rate: 4.27083155404 per second
Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).
(&hr_bytime(Created_tmp_tables/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(16352036/3828771) dbtuner_stristr('4.27083155404 per second', array('second', 'minute')))

MyISAM index cache
MyISAM key buffer size: 32.0 Mb
max % MyISAM key buffer ever used: 88.4857177734
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
((Key_blocks_used)*key_cache_block_size/key_buffer_size * 100 <95)
((28995)*1024/33554432 * 100 88.4857177734<95)
% MyISAM key buffer used: 58.7066650391
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
((1-Key_blocks_unused*key_cache_block_size/key_buffer_size) * 100 <95)
((1-13531*1024/33554432) * 100 58.7066650391<95)
% index reads from memory: 99.9697494911

other caches
table open cache size (5.1+): table_open_cache
Size of the table cache
(table_open_cache >-1)
(table_open_cache table_open_cache>-1)
rate of table open: 27.4787914973 per hour
% open files: 9.44010416667
rate of open files: 13.0882729732 per day
Immediate table locks %: 99.9508790235
Table lock wait rate: 1.14917293304 per minute
Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
(&hr_bytime(Table_locks_waited/Uptime_since_flush_status) =~ /second|minute/)
(dbtuner_hr_bytime(73332/3828771) dbtuner_stristr('1.14917293304 per minute', array('second', 'minute')))
thread cache: 64
Total threads created: 86
thread cache hit rate %: 4.7972733637E-5
Threads that are slow to launch: 3
There are too many threads that are slow to launch
(Slow_launch_threads >0)
(3 3>0)
Slow launch time: 2

Connections
% connections used: 52.6666666667
Max connections used: 79
Max connections limit: 150
% aborted connections: 0.00345849940174
rate of aborted connections: 1.39909124886 per day
% aborted clients: 0
rate of aborted clients: 0 per day

InnoDB
Is InnoDB enabled?: YES
% innoDB log size: 62.5
InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. Consider changing eitherninnodb_log_file_size or innodb_buffer_pool_size
(innodb_log_file_size / innodb_buffer_pool_size * 100 >=0)
(5242880 / 8388608 * 100 62.5>=0)

other
MyISAM concurrent inserts: 1

my.cnf


# http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-...
# The MySQL database server configuration file.
#

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
table_cache = 1500

open_files_limit=6144

old_passwords = 1
bind-address = 127.0.0.1
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 32M
query_cache_type = 1
join_buffer_size = 512K
max_connections = 150

log_slow_queries = /var/log/mysql/mysql-slow.log

long_query_time = 3
log-queries-not-using-indexes

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M
skip-locking
skip-innodb
skip-name-resolve

key_buffer = 512M
sort_buffer = 16M
join_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 12M
max_allowed_packet = 128M

table_cache = 6144
thread_stack = 512K
thread_cache_size = 384
wait_timeout = 80
myisam_sort_buffer_size = 64M
tmp_table_size = 256M
binlog_cache_size = 65536
max_heap_table_size = 256M
tmp_table_size = 256M
thread_concurrency = 8

query_cache_limit = 3M
query_cache_size = 256M
query_cache_type = 1

Ja póki co zmieniam parę parametrów:


query_cache_size = 48M
max_heap_table_size = 512M
tmp_table_size = 512M
sort_buffer = 32M
read_rnd_buffer_size = 32M
read_buffer_size = 16M
max_connections = 70

restart i czekam parę dni na wyniki, przy okazji poczytam coś o indeksach i InnoDB, może coś poprawię.