Following entries of my.cnf  in my dedicated server of 24 GB Ram where  PHP MEMORY LIMIT I have set it to 128 M

 

[mysqld]
max_connections = 250
open_files_limit=32735
pid-file=/var/lib/mysql/myfavs.pid
safe-show-database
skip-external-locking
skip-locking
skip-name-resolve
thread_cache_size=128
sync_binlog=1
connect_timeout=10
interactive_timeout=30
query_cache_limit = 64M
query_cache_size = 512M
query_cache_type=1
net_buffer_length = 32K
query_cache_type=1
wait_timeout=30
read_buffer_size=8M
flush_time = 3600
read_rnd_buffer_size=8M
key_buffer=1300M
join_buffer=4M
bulk_insert_buffer_size=16M
myisam_sort_buffer_size = 8M
sort_buffer_size = 6M
myisam_max_sort_file_size=50G
max_allowed_packet=64M
thread_concurrency=8
tmp_table_size=256M
max_heap_table_size =256M;
table_cache=4700
table_definition_cache=4700
myisam_use_mmap = 1
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT
innodb_data_home_dir =
innodb_data_file_path = /var/lib/mysql/ibdata/ibdata1:100M:autoextend
innodb_buffer_pool_size=256M
innodb_lock_wait_timeout = 60
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 166
transaction-isolation=READ-COMMITTED

skip-federated
innodb_table_locks             = 0
innodb_log_buffer_size = 64M
innodb_file_io_threads = 4
innodb_log_files_in_group = 2
innodb_thread_concurrency=8
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
concurrent_insert=2
set-variable=long_query_time=3
open_files_limit=32735

[isamchk]
key_buffer = 24M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 24M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

 

 

and here is the output of mysqltuner.pl script .  I am looking to do further tuneup as some of the parms seems over allocated.  Please advise

 

[email protected] [~]# ./mysqltuner.pl

>>  MySQLTuner 1.0.1 – Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  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.65-cll
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 2877)
[--] Data in InnoDB tables: 141M (Tables: 386)
[!!] Total fragmented tables: 481

——– Performance Metrics ————————————————-
[--] Up for: 31m 26s (85K q [45.517 qps], 2K conn, TX: 348M, RX: 10M)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 2.3G global + 26.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 8.8G (37% of installed RAM)
[OK] Slow queries: 0% (3/85K)
[OK] Highest usage of available connections: 6% (15/250)
[OK] Key buffer size / total MyISAM indexes: 1.3G/1018.8M
[OK] Key buffer hit rate: 99.8% (7M cached / 15K reads)
[OK] Query cache efficiency: 69.8% (47K cached / 67K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 10K sorts)
[!!] Joins performed without indexes: 358
[!!] Temporary tables created on disk: 38% (1K on disk / 3K total)
[OK] Thread cache hit rate: 99% (15 created / 2K connections)
[OK] Table cache hit rate: 50% (3K open / 6K opened)
[OK] Open file limit used: 17% (5K/32K)
[OK] Table locks acquired immediately: 99% (29K immediate / 29K locks)
[OK] InnoDB data size / buffer pool: 141.0M/256.0M

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large – reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust: