MySQL my.cnf tuning, me too?

[DAdams982]

I am sure 900 people start new threads on this issue, so I will jsut use this one. i am attempting to tune mysq on my VPS now which has 1.5G of ram.. Here is the perl script output:

>>  MySQLTuner 1.2.0 - Major Hayden <[EMAIL="[email protected]"][email protected][/EMAIL]>
 >>  Bug reports, feature requests, and downloads at [URL]http://mysqltuner.com/[/URL]
 >>  Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 61M (Tables: 1263)
[--] Data in InnoDB tables: 347M (Tables: 1024)
[--] Data in MEMORY tables: 2M (Tables: 22)
[!!] Total fragmented tables: 1024

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 23h 45m 40s (11M q [16.926 qps], 284K conn, TX: 33B, RX: 3B)
[--] Reads / Writes: 45% / 55%
[--] Total buffers: 106.0M global + 11.2M per thread (75 max threads)
[OK] Maximum possible memory usage: 949.8M (70% of installed RAM)
[OK] Slow queries: 0% (104/11M)
[OK] Highest usage of available connections: 18% (14/75)
[OK] Key buffer size / total MyISAM indexes: 32.0M/13.1M
[OK] Key buffer hit rate: 99.2% (4M cached / 34K reads)
[OK] Query cache efficiency: 90.5% (8M cached / 9M selects)
[!!] Query cache prunes per day: 40648
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 57K sorts)
[!!] Joins performed without indexes: 8316
[OK] Temporary tables created on disk: 9% (37K on disk / 410K total)
[OK] Thread cache hit rate: 99% (14 created / 284K connections)
[!!] Table cache hit rate: 0% (512 open / 184K opened)
[OK] Open file limit used: 50% (560/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 347.6M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 512)
    innodb_buffer_pool_size (>= 347M)

And here is my.cnf

[mysqld]
safe-show-database
tmp_table_size = 64M
max_heap_table_size = 32M
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_connections=75
collation_server=utf8_unicode_ci
character_set_server=utf8
delayed_insert_timeout=40
interactive_timeout=10
wait_timeout=150
connect_timeout=20
thread_cache_size=8
key_buffer=32M ## 32MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=512
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=32M

Any recommendations you all can share?

 

[gnutoolbox]

Below config works well with sever having 2 GB of ram. If your mysql server is still hungry of using more cpu/memory, you may need to try data caching options like memcached.

/http://www.gnutoolbox.com/memcached-php-mysql/


My.cnf for 2 GB :

=================

[mysqld]
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
safe-show-database
back_log = 150
max_connections = 250
key_buffer_size = 16M
myisam_sort_buffer_size = 16M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
#table_cache = 1000
thread_cache_size = 256
wait_timeout = 10
connect_timeout = 10
tmp_table_size = 16M
max_heap_table_size = 16M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 15
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 8M
query_cache_limit = 20M
query_cache_size = 6M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 16
net-read-timeout = 5
[mysqld_safe]
nice = -10
open_files_limit = 8192
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 16M

===================