MySQL my.cnf tuning

[c4carab]

Hello,

Can any one recommend best my.cnf for CENTOS 6.4 x86_64 / ntel(R) Xeon(R) CPU E31230 @ 3.20GHz / 8gb ram

was like that :

[mysqld]
innodb_file_per_table=1
innodb_buffer_pool_size=5G
key_buffer_size = 128M
max_allowed_packet=100M
max_connections=400
max_heap_table_size=256M
open_files_limit=5000
query_cache_size=4M
query_cache_size = 512M
join_buffer_size=128M
table_cache = 8195
thread_cache_size=8
tmp_table_size=256M
log-slow-queries=/var/lib/mysql/slow.log

im change it to be like that :

[mysqld]
max_connections=200
low_priority_updates=1
myisam-recover=backup,force
thread_concurrency=8
concurrent_insert=2
thread_cache_size=32
max_allowed_packet=8M

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT

interactive_timeout = 100
connect_timeout = 60
wait_timeout = 120

tmp_table_size = 256M
max_heap_table_size = 256M
join_buffer_size=32M
table_cache =2200
read_buffer_size=4M
sort_buffer_size=2M
read_rnd_buffer_size=2M
key_buffer_size=128M
max_allowed_packet=8M
max_connect_errors=10
myisam_sort_buffer_size=64M
query_cache_limit=3M
query_cache_size=64M
query_cache_type=1

[mysqldump]
quick
max_allowed_packet=16M

site run slow now with socialengine v4 and mysqltuner.pl give this

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.68-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 127M (Tables: 120)
[--] Data in InnoDB tables: 143M (Tables: 397)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 32

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3m 7s (32K q [172.364 qps], 743 conn, TX: 36M, RX: 6M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 971.0M global + 40.2M per thread (200 max threads)
[!!] Maximum possible memory usage: 8.8G (114% of installed RAM)
[OK] Slow queries: 0% (0/32K)
[OK] Highest usage of available connections: 7% (15/200)
[OK] Key buffer size / total MyISAM indexes: 128.0M/22.3M
[OK] Key buffer hit rate: 98.7% (4K cached / 67 reads)
[OK] Query cache efficiency: 74.4% (17K cached / 23K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 575 sorts)
[!!] Joins performed without indexes: 70
[!!] Temporary tables created on disk: 37% (331 on disk / 880 total)
[OK] Thread cache hit rate: 97% (15 created / 743 connections)
[OK] Table cache hit rate: 97% (252 open / 259 opened)
[OK] Open file limit used: 0% (37/4K)
[OK] Table locks acquired immediately: 99% (9K immediate / 9K locks)
[OK] InnoDB data size / buffer pool: 143.4M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 32.0M, or always use indexes with joins)

any help suggestion

 

[ES - George]

"Up for: 3m 7s"

You really should allow 24 hours before running the script to get a more accurate recommendation. Post the results here again now assuming MySQL hasn't been restarted since opening this topic.

 

[caisc]

Yes i agree with @HVH - George, you shud allow sometime to judge the performance after making changes to cnf file.

 

[chrismfz]

join_buffer_size=32M should be 1M, max 2.

No matter how much you going to increase it, mysqltuner will say you need more.
But that's not a problem.
That's why it says:
[!!] Maximum possible memory usage: 8.8G (114% of installed RAM)
It calculates all buffers x number of connections. It shouldn't be so high.

You have 3 times the same var, max_allowed_packet=8M
and max_allowed_packet=16M and max_allowed_packet=8M
again...

Remove them, and maybe you need to increase this to something bigger for example 32M
It depends if for example from a forum you upload a picture and it's larger than 8M.
Not a problem but still, "clean" my.cnf a little.

You should also lower these
read_buffer_size=4M
to 2M as well.

It's not "the bigger the better" here :D

And all these to something much smaller
interactive_timeout = 100
connect_timeout = 60
wait_timeout = 120

for example start with 20-30 seconds to all.


THEN let it run at least one full day and re run mysqltuner.
Again, ignore the join_buffer_size no matter what it says.
Paste the rest of them again.


You can always compile apache with xcache to cache queries and pages and see a difference.
You shouldn't have issues with RAM. Got enough.