MySQL my.cnf tuning?

A

arhs

Well-Known Member
Jul 4, 2003
116
0
166
Hello,

Can any one recommend best my.cnf for Intel i7 CPU and 8GB ram server?

Thanks in advance.




Code: 
[[email protected] ~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >>  MySQLTuner 1.1.2 - 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.56-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 958M (Tables: 12802)
[--] Data in InnoDB tables: 217M (Tables: 2368)
[--] Data in MEMORY tables: 248K (Tables: 53)
[!!] Total fragmented tables: 2803

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 37m 31s (306K q [32.478 qps], 8K conn, TX: 682M, RX: 105M)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (17% of installed RAM)
[OK] Slow queries: 0% (2/306K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/213.4M
[OK] Key buffer hit rate: 97.4% (6M cached / 164K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 40K sorts)
[!!] Joins performed without indexes: 3699
[!!] Temporary tables created on disk: 27% (7K on disk / 27K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 88K opened)
[OK] Open file limit used: 3% (125/4K)
[OK] Table locks acquired immediately: 99% (370K immediate / 370K locks)
[!!] InnoDB data size / buffer pool: 217.3M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 217M)
Code: 
[[email protected] ~]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
local-infile=0
 
I

InterServed

Well-Known Member
Jul 10, 2007
273
17
68
cPanel Access Level
DataCenter Provider
arhs said:
Hello,

Can any one recommend best my.cnf for Intel i7 CPU and 8GB ram server?

Thanks in advance.




Code: 
[[email protected] ~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >>  MySQLTuner 1.1.2 - 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.56-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 958M (Tables: 12802)
[--] Data in InnoDB tables: 217M (Tables: 2368)
[--] Data in MEMORY tables: 248K (Tables: 53)
[!!] Total fragmented tables: 2803

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 37m 31s (306K q [32.478 qps], 8K conn, TX: 682M, RX: 105M)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (17% of installed RAM)
[OK] Slow queries: 0% (2/306K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/213.4M
[OK] Key buffer hit rate: 97.4% (6M cached / 164K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 40K sorts)
[!!] Joins performed without indexes: 3699
[!!] Temporary tables created on disk: 27% (7K on disk / 27K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 88K opened)
[OK] Open file limit used: 3% (125/4K)
[OK] Table locks acquired immediately: 99% (370K immediate / 370K locks)
[!!] InnoDB data size / buffer pool: 217.3M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 217M)
Code: 
[[email protected] ~]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
local-infile=0
Click to expand...

Try the following:
Code: 
[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=2M
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

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=4M
write_buffer=2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 4M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
S my.cnf and mysql auto shutdown Workarounds and Optimization 1
M Need help optimising my.cnf Mysql Workarounds and Optimization 5
M MySQL Optimization Request - my.cnf Tuning for Quad Core Processor - 6GB Ram Workarounds and Optimization 8
C MySQL my.cnf tuning Workarounds and Optimization 3
D MySQL my.cnf tuning, me too? Workarounds and Optimization 1
Similar threads
my.cnf and mysql auto shutdown
Need help optimising my.cnf Mysql
MySQL Optimization Request - my.cnf Tuning for Quad Core Processor - 6GB Ram
MySQL my.cnf tuning
MySQL my.cnf tuning, me too?
Top Bottom