I wanted to seek for help if someone wants to help with my my.cnf.
I don't have a clue with Databases...
It seems that I have lots of issues with the fine tuning of it.
Seeked a lot on the internet and read many tutorials but I just seem to understand that apart from the fact that it needes a very long time to learn how to fine tune it there are also too many different opinions...
So I turn to your fellow expertise !
My box is equiped with a dual processor with 2 giga ram running centos and plesk.
php version : 5.2.13 / mysql version : 5.0.90-log
Many thanks in advance !
Mysql Tuner output :
Code: Select all
[OK] Currently running supported MySQL version 5.0.90-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 52M (Tables: 943)
[--] Data in InnoDB tables: 5M (Tables: 141)
[--] Data in MEMORY tables: 3M (Tables: 7)
[!!] Total fragmented tables: 1
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 18d 7h 26m 7s (1M q [0.860 qps], 60K conn, TX: 14B, RX: 456M)
[--] Reads / Writes: 51% / 49%
[--] Total buffers: 90.0M global + 4.2M per thread (300 max threads)
[OK] Maximum possible memory usage: 1.3G (69% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 3% (9/300)
[OK] Key buffer size / total MyISAM indexes: 32.0M/19.5M
[OK] Key buffer hit rate: 99.7% (15M cached / 51K reads)
[OK] Query cache efficiency: 65.7% (496K cached / 754K selects)
[!!] Query cache prunes per day: 292
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 58K sorts)
[!!] Temporary tables created on disk: 27% (57K on disk / 209K total)
[OK] Thread cache hit rate: 99% (9 created / 60K connections)
[!!] Table cache hit rate: 0% (208 open / 43K opened)
[OK] Open file limit used: 5% (125/2K)
[OK] Table locks acquired immediately: 99% (615K immediate / 615K locks)
[OK] InnoDB data size / buffer pool: 5.2M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 1024)
my.cnf :
Code: Select all
[client]
default-character-set=utf8
[mysqld]
log-slow-queries=/etc/mysql.slow
skip-bdb
character-set-server=utf8
default-collation=utf8_unicode_ci
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
safe-show-database
query_cache_limit=128M
query_cache_size=32M
query_cache_type=1
max_user_connections=50
max_connections=300
interactive_timeout=40
wait_timeout=90
connect_timeout=20
thread_cache_size=128
key_buffer=32M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
thread_concurrency=2
myisam_sort_buffer_size=64M
server-id=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
Mysql status output :
Code: Select all
mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.90, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 60766
Current database:
Current user: admin@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.90-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 18 days 7 hours 29 min 21 sec
